Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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?


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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