Solved

SSA Many-To-Many relationships between slowly changing dimensions

Posted on 2011-09-14
8
1,100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 46
Show RTF format in an SSRS report 3 39
Can I skip a node in XML? 9 31
sql query 5 43
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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