Solved

SSA Many-To-Many relationships between slowly changing dimensions

Posted on 2011-09-14
8
1,070 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 15

Accepted Solution

by:
Tim Humphries earned 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now