Many-to-many Relationship Issue/Question

Hello All,

Here's what I got so far:

- Employee fact table with 10 dimensions connected to it

- A Factless Bonus table and the Employee fact table are connected through a common dimension of Time.

- The Factless Bonus table connects to a Bonus Dimension

- added a many-to-many relationship with the Bonus Dimension to the Employee fact table

How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?

Thank you.

-Gumbatman

"How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?" - but aren't you already able to slice and dice the measures from the Employee fact table this way? If not, could you explain what the problem is in more detail? It seems strange that Bonus should only be a function of time, and not of any other dimension; but maybe that's in the nature of your problem.|||

Deepak,

I apologize for not expressing this as well as I could. I just used the Time dimension as an example. Sorry about that...

Here's the situation:

I have a fact table that has one row per employee, per month. I now need to add bonuses that were received by the employees. The issue is that employees can received more than one bonus per month and sometimes those bonuses can be the same type.

So the question is: How can I have multiple bonuses for an employee in a single month when they only have one row in that month?

When I tried connecting the Fact tables, I didn't know what made the most logical sense to have as that intermediate dimension. When I tried using Time, I could get that they got a bonus and when they got it, but I could not do any additional slicing across other dimensions (the 10 I mentioned) that the Employee Fact table is connected to.

My results would be that Employee John Doe got five bonuses this month, but it would not show that he was in the Southeast. It would place those bonuses only in the Midatlantic area.

Thank you so much for the help. This has been driving me crazy.

-Gumbatman

p.s. Another thing I need to do is show which employees when to which school. Again, employees may have attended more than one school.

|||So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension. But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)? In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension. The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).|||

Deepak,

I couldn't get the quoting to work, but here are my answers:

So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension.

- Yes

But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)?

- The other 10 dimensions all directly related to EmployeeMonthly,

In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension.

- I haven't tried that yet, but I will. I think that is one of the points I am missing.

The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).

- That makes perfect sense, but I am still confused on the exact relationship between EmployeeMonthly and BonusType. How do they connect to each other? Is that with a composite key or with BonusMonthly? Or is BonusMontly a table with the composite key?

When you are referring to a composite key, do you mean selecting the EmployeeKey, BonusKey, Date, etc. and make that a key? Or concatenating those keys into a single field for the row, which is now a Primary Key?

The way I picture this is:

EmployeeMonthly Table

Employee Monthly Key Employee Key Date Key Bonus Composite Key 1 1 20061001 0 2 2 20061001 2_20061001 3 1 20061101 0 4 2 20061101 0

BonusMonthly Table

Employee Key DateKey Bonus Key Bonus Composite Key 2 20061001 1 2_20061001 2 20061001 1 2_20061001 2 20061001 2 2_20061001

Here Employee #2 got three bonuses on 10/1/2006. Two of the bonuses were the same type and that is what confuses me about the composite keys, don't I need a Primary key in BonusMontly?

Also, I may be thinking in terms of a relational database, but when I connect EmployeeMonthly and BonusMonthly (in the setup above) I would expect three rows of duplicated data. Is that correct?

Thank you so much for your help with this.

-Gumbatman

|||Based on the sample data, EmployeeMonthlyKey could be added as a Foreign Key to BonusMonthly, allowing the EmployeeMonthly fact dimension to directly relate to BonusMonthly measure group. Then BonusType could have a many-to-many relation to EmployeeMonthly measure group, with BonusMonthly being the intermediate measure group. You might not need a primary key in BonusMonthly, unless you're also setting it up as a fact dimension (eg: for drillthrough).|||

Deepak,

I got this to work, almost...

The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table.

For example,

- EmployeeMonthy is related to BonusType dimension through BonusMonthly measure group.

- EmployeeMonthy also has dimensions directly related to it, such as a Geographic dimension, JobLevel dimension, Product dimension, etc.

When I want to see Bonus data by Geographic Area, I am getting strange results. The numbers are correct, but the bonuses are not properly breaking down by Geographic Area. It appears that it is only taking the first member of the Geographic dimension.

I tried setting them (BonusType and Geographic) as many-to-many, but that didn't work.

How is it supposed to work? Is there a way around this? I am thinking of just duplicating all the dimensions on the EmployeeMonthly onto the BonusMonthly fact table.

I really appreciate the time and energy you've put into helping me with this.

-Gumbatman

|||"The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table" - as I mentioned earlier, you could configure these dimensions with referenced (not many-to-many) relationships to the BonusMonthly measure group, via the EmployeeMonthly fact dimension (which you should have already configured as directly related to the BonusMonthly measure group). These referenced relationships could be materialized, for better performance. Or you could, as you suggest, directly relate these dimensions to the BonusMonthly measure group, but this might involve adding a join to the fact table named query.|||

Deepak,

Thank you so much, I finally got it to work!

I didn't really catch it when you spoke about referenced dimensions. Plus, my lack of understanding made me gloss over it.

I really appreciate all the time and effort you put into helping me with this. I would never of have gotten to the solution without your expertise.

-Gumbatman

many-to-many parent-child relationship in the dimension hierarchy

This novice would like your assistance to solve a problem. The goal is to build a Data mart for use in a cube. The cube would have a Service Fact related to the Dimension of Personnel and Assets. The Measures would be:

Provision of Assets by Departments

Availability of Assets by Departments or Service

Volume of Support Calls by Departments

The model has one major problem: a many-to-many parent-child relationship in the hierarchy of the Asset Dimension. The hierarchy within the Dimension is

Bundle

Service

Assets

And the many-to-many relationship resides between Assets & Service.I wish to know how best to handle this situation; create a bridge table or create a Service Dimension. All advice is welcomed.

What is the business process you are modeling in your fact table? The description of the measures is a little confusing to me.

Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?

Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?

Thanks,
Bryan

|||The main objective is to create a cube on IT Support for performance reporting against SLAs, with a secondary objective being to expose the use of IT within the organisation. The high level categories of measures are: provisioning, availability, volume, and minutes. These would translate into measurement exposing the aggregated number of Assets provisioned; the percentage an Asset was available; the aggregated number of support calls received for an Asset; the minutes taken to close a support call on an Assest. Basically, sliced, diced and rolled up and down by organisation section, business unit, IT bundle and service. This is datamart design based on this information:

Code Snippet

Assets Dim -> Support Fact <- Personnel Dim
AssetID AssetID PersonnelID
Class PersonnelID Job
Service %Available Business Unit
Bundle Provisioned Assets Section
Call volume
Call minutes

The Dimensions have been model to the lowest level of granularity. The Personnel Dimension offers no problems with the hierarchy; a staff member only belongs to one job, business unit & section. This is not the case for Assets. All Assets are assigned to a member of staff. An Asset is a member of a class --mobile, pc, server,...--, one or many services and those services are member of one bundle.

"Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?"

Yes. The IT Business Unit offers 5 bundles of services. Each bundle is comprise of mutually exclusive services. An example would be the End User Computing Bundle comprising of Desk Support, Printing & Fax, etc.

"Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?"

One or multiple through the asset. Each Asset is tired to at lest one service. Assets such as servers offer more than one service.

I appreciate your response and am happy to supply additional information.
|||

According to Kimball methodology, a fact table should represent a single business process. So in this data mart, you would have a fact table for the handling of service tickets, another one for provisioning, another one for asset uptime/availability, and so on and so on. You might bring all this data together in a report using conformed dimensions between the fact tables. I'd recommend taking a look into that. It will make your maintanance of the data much, much easier. (You can easily tie all this data together in your olap cube so your users won't have to jump between fact tables aka measure groups.)

When you do that, I think you will find that some facts relate directly to assets while others relate directly to the services.

Still, you will have assets providing services and need to model that relationship. I'd recommend building a service group table that ties together the services being offered. Then associate an asset with a service group. Here's a rough example:

Asset (AssetID, ServiceGroupID, ..., StartDate, EndDate)

ServiceGroup (ServiceGroupID, ....)

ServiceGroupServiceJunction (ServiceGroupID, ServiceID, ....)

Service (ServiceID, ....)

So, if you had a fact associated with an asset, you would be able to identify what services are provided by that asset. If you are working with Asset as a Type 2 slowly changing dimension, there would be different records for an asset in the Asset table based on the ServiceGroups it was associated with across time.

If you have a copy of "The Microsoft Data Warehouse Toolkit", this is covered on page 60. (I think this is covered in "The Data Warehouse Toolkit" as well but someone has borrowed by copy.)

Hope that helps,
Bryan

|||Bryan,

Thanks for your suggestions and insight. I will have a read of the books mentioned and most likely return with a few questions...hopefully your generosity will continue to provide answers.

Ian
|||

Assuming that you're using AS 2005, the many-many relationship between Assets and Services could be modelled, if separate Asset and Service dimensions are set up. There is 1 measure that isn't just additive, and hence may need further fields in the fact table: "the percentage an Asset was available". Generally, availability is a ratio like available to total time. Even in the simplest scenario, the numerator and denominator would be separately summed (say across Assets in this case), then their ratio taken. So, if the fact table has either "%Available" or "AvailableTime", and "TotalTime", the aggregate "%Available" could be computed.

The schema for AS 2005 could then be something like:

PersonnelDim SupportFact AssetDim Asset<->ServiceBridge ServiceDim

AssetID --> AssetID <-- AssetID

PersonnelID <-- PersonnelID Asset ServiceID --> ServiceID

Job AvailTime Class Service

Business Unit TotalTime Bundle

Section Provisioned

Call Volume

Call Minutes

There would be a Measure Group on SupportFact, with "sum" measures: AvailTime, TotalTime, Provisioned, Call Volume, Call Minutes.

["%Available] could then be defined as: AvailTime / TotalTime.

The Personnel and Asset dimensions would have a regular relation to the SupportFact Measure Group.

An intermediate Measure Group is then defined on the Asset<->ServiceBridge table, to which Asset and Service dimensions are related.

This would allow the Service dimension to have a many-many relation to the SupportFact Measure Group, via this intermediate MG.

Many to many relationship in MSAS

Hi,

I ve got a fact table called project_fact and a dimension table called technology_dim.

project_fact:

proj_id time_id tech_id revenue cost

technology_dim:

tech_id tech_name

Now there is a many to many relation between fact and dimension table. one project can be done on many technologies. How to resolve this, do i need to create a third table?

Please tell me in details...

Thanks in advance,

Siddharth Rai.

Create a table that represents the combination of technologies exploited by your project. Let's call this the TechGroup table:

create table TechGroup (

TechGroupID int not null identity(1,1)

)

alter table TechGroup add

constraint PK_TechGroup primary key (techgroupid)

Create another tables that relates the TechGroup to the Technologies that comprise it:

create table TechGroupTechnologyBridge (

TechGroupID int not null,

TechnologyID int not null

)

alter table TechGroupTechnologyBridge add

constraint PK_TechGroupTechnologyBridge primary key (techgroupid, technologyid),

constraint FK_TechGroupTechnologyBridge_TechGroupID foreign key (techgroupid) references TechGroup (techgroupid),

constraint FK_TechGroupTechnologyBridge_TechnologyID foreign key (technologyid) references Technology (technologyid)

In your fact table, reference the TechGroup table.

When you set up the cube, create a TechGroup dimension. Add the TechGroupTechnologyBridge as a measure group with a single measure (using COUNT). Make the measure not visible (which will hide the bridge measure group).

In your dimension usage tab, set the relationships between TechGroupTechnologyBridge measure group and TechGroup and Technology dimensions. Relate your fact table measure group to the TechGroup dimension as well.

Now, set a many-to-many relationship between your fact table and the Technology dimension using the TechGroupTechnologyBridge measure group as the intermediate measure group.

You will probably want to make the TechGroup dimension not visible within your cube.

Good luck,
Bryan

|||

Hey thanks Bryan...sql

Many to many relationship in MSAS

Hi,

I ve got a fact table called project_fact and a dimension table called technology_dim.

project_fact:

proj_id time_id tech_id revenue cost

technology_dim:

tech_id tech_name

Now there is a many to many relation between fact and dimension table. one project can be done on many technologies. How to resolve this, do i need to create a third table?

Please tell me in details...

Thanks in advance,

Siddharth Rai.

Create a table that represents the combination of technologies exploited by your project. Let's call this the TechGroup table:

create table TechGroup (

TechGroupID int not null identity(1,1)

)

alter table TechGroup add

constraint PK_TechGroup primary key (techgroupid)

Create another tables that relates the TechGroup to the Technologies that comprise it:

create table TechGroupTechnologyBridge (

TechGroupID int not null,

TechnologyID int not null

)

alter table TechGroupTechnologyBridge add

constraint PK_TechGroupTechnologyBridge primary key (techgroupid, technologyid),

constraint FK_TechGroupTechnologyBridge_TechGroupID foreign key (techgroupid) references TechGroup (techgroupid),

constraint FK_TechGroupTechnologyBridge_TechnologyID foreign key (technologyid) references Technology (technologyid)

In your fact table, reference the TechGroup table.

When you set up the cube, create a TechGroup dimension. Add the TechGroupTechnologyBridge as a measure group with a single measure (using COUNT). Make the measure not visible (which will hide the bridge measure group).

In your dimension usage tab, set the relationships between TechGroupTechnologyBridge measure group and TechGroup and Technology dimensions. Relate your fact table measure group to the TechGroup dimension as well.

Now, set a many-to-many relationship between your fact table and the Technology dimension using the TechGroupTechnologyBridge measure group as the intermediate measure group.

You will probably want to make the TechGroup dimension not visible within your cube.

Good luck,
Bryan

|||Hey thanks Bryan...

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

many to many dimension

Hi all,

Is there a way to create a dimension property based on fact table used to connect a dimension to another measure group. I found a example where such an attibute is used for a measure expression(http://msdn2.microsoft.com/en-us/library/ms345139.aspx) but I really need a property that I could display to the end user.

thx

Fred

Hi again...just to be a bit more clear on what I'm trying to do...if you look at the example I mentioned....how could the value of the 'ownership' be displayed instead of used in a calculation?

Fred

|||Well, in this example, the user could browse the value of the 'ownership' measure in the intermediate measure group, for a given selection of Store and Organization. If that's not adequate, you could define a fact dimension for the intermediate measure group fact table, and add 'ownership' as an attribute (with the appropriate type).|||

Thanks Deepak! The fact dimension will do the trick

Fred