SSA Many-To-Many relationships between slowly changing dimensions

Posted on 2011-09-14
Medium Priority
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?
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?
Question by:VoiceOver
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
LVL 15

Expert Comment

by:Tim Humphries
ID: 36541632

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.


Author Comment

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!


Author Comment

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

LVL 15

Accepted Solution

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!


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


Assisted Solution

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.


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

Author Closing Comment

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

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.


Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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