?
Solved

SSA Many-To-Many relationships between slowly changing dimensions

Posted on 2011-09-14
8
Medium Priority
?
1,223 Views
Last Modified: 2016-02-14
In SSAS it is possible to create many-to-many relationships by creating an intermediate fact table. Problem is what to do if the intermediate fact table is also a slowly changing dimension?
Example:
I have a regular  fact table called FactSales which has a foreign key to Clients called ClientID. DimClients is a slowly changing dimension.
My company's employees can have many to many relationships with clients as they can be involved in various roles with the client.
So we have a DimEmployee with employees (also SCD) and a link table between clients called RelClientsEmployees. This of course is a perfect candidate to be the required intermediate fact table allowing me to link Employees to clients to FactSales.
Problem is that RelClientsEmployees is also slowly changing as over time roles to the same clients can switch employees.
As the intermediate fact table is not a dimension I can't indicate through the attribute type properties the SCD fields (SCDStatus, SCDEndDate etc.)
Without this however the results of the cube are incorrect, how do I solve this?
I have attached an image with the table layout.
Would be grateful for any help?
SCD.bmp
0
Comment
Question by:VoiceOver
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 36541632
Hi,

Your Sales fact is immutable and will not change, so why not add a foreign key to employee to the sales fact? In this way the employee that has the current relationship with the client is recorded.
If you're implementing SCDs and wish to preserve history, if the employe role changes you will have a new employee dimension member and future facts will not alter the original fact, the associated employee or the role they were in.
It also obviates the need for RelClientEmployee (in the data source view), I think.

Tim
0
 

Author Comment

by:VoiceOver
ID: 36998747
Thanks Tim, sorry to respond so late, hadn't noticed someone had responded. I understand the proposed solution. Thing is client vs employees is N:M relationship, so each client can have multiple employees linked to him in any period as there can be various roles employees can have with a client and a particulare employee can have different roles with different clients, so it would take an employeeid attribute  for each possible role in the fact table. And it would entail that for each new role the fact table structure would need to change, which is unacceptable of course.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 36998804
Hi, I don't think your fact table structure would have to change.

Why not add a unique ID to RelClientEmployee and link your fact table to that and 'star' our to DimClient and DimEmployee?

Would that work for you?

Tim
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:VoiceOver
ID: 36998843
Hi Tim,

Problem is relationship between the fact table and relclientemployee is 1:N, meaning each fact can be
linked to many employee/role combinations

0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 2000 total points
ID: 36999516
Hi - sorry - been a while since I looked at this and forgot that aspect of the problem.

Seems that what you really need to do is to snaphot the state of the Client at the point of writing the fact - in other words, the Client comprises not just the attributes in DimClient, but also the associated contents of RelClientEmployee and DimEmployee. You can't denormalise all this into one Client row because of the M:M relationships, but you could build a hierarchy under Client, something like:

Client (existing attributes) -> Role -> Employee

Note that these are not relationships to the existing Employee dimension, but child levels within the Client dimension. The query you use to determine whether a Client has changed would need to look at the full relationships from the Client and its 'children', so any change to the roles or the employees attributes would trigger the creation of a new active Client row in the dimension.

This may not be a practical approach depending on how frequently all this stuff changes.

Not an easy one!

Tim

p.s. this question would be better inthe OLAP or SSAS zone, rather than SSIS.

0
 

Assisted Solution

by:VoiceOver
VoiceOver earned 0 total points
ID: 37005508
Hi Tim,

Thanks for the respons.
I like the idea of the hierarchy you're suggesting, I'll give it some thought as indeed roles change pretty often, or more precise more or less the same roles are linked to a client, but the employees linked to them change quite often, but I think you're solution at least points to the right direction, so thank you.

Regards,
Vincent

p.s. I tagged the question under SSAS, so obviously I need to figure out how to post something in a particular "zone"
0
 

Author Closing Comment

by:VoiceOver
ID: 37035236
I can work with the idea of Tim's solution, he put me on the right track.
0
 

Expert Comment

by:Akarsh Vijay Kumar
ID: 40756442
Hi ,

I understand this a old post, however just wanted to  try and reach out. I have a similar situation, the problem I face is, I have Cube which displays a user activity in a room, the DWH design for the cube is one fact table recording user activity with date, it has 2 Dimension tables, the first one is a Room dimension and 2nd is Group dimension, of course a date dim is also present. The fact does not have both room and group key, it just has RoomKey. The dimension are tied together with an intermediate fact table (many to many).

We are now introducing Type 2 in our dimensions and in my case it applies to the Group dimension only, however the intermediate fact table will change when a room is moved from one group to the other making it a type 2 as well.

I'm tracking changes in both the tables with a column named effectiveDate and Activeflag. How can I implement my SSAS cube in this situation, I have tried adding a effective date in the intermediate dimension table and then linking that with the dateDim, this date will change whenever a room is moved from one group to another but that does not work as expected. I'm trying to understand if this approach would of creating hierarchies help me, should the  hierarchy be created on the Group dimension, would that then honour dates when I filter by bedgroups to get room activities .

Please advice what would be the best and better approach.

Thanks
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question