Search code examples
sqlssasdimensional-modeling

Service Request fact and SR fact Actions - how to slice and drill thru

I have a FactServiceRequest (FctSR) table and a FactSRActions (FctSRA) table, the FctSR table is linked using Regular dimensions to

  • create date
  • completed date
  • dimcontact

The FctSRA has regular dimensions to

  • Action date
  • Action code

Each of the fact tables works well in isolation, however I am struggling to combine the results into one set.

So, I am looking to get:

FctSR.ID,FctSR.Details, FctSRA.Actions,FctSRA.ActionCode,FctSRA.ActionDate for each FctSR.ID

The tables have a degeneratekey in common if that helps.

Can anyone suggest how to put these on the SSAS DimensionUsage tab please?

I am using VS 2015 on a SQL Server 2016.

Thank you.

edit:

dimension usage tab Hope this makes sense.

Solution

  • Fact Service Request Action measure group currently joins to the Fact Service Request dimension via a many-to-many relationship. I believe you need to add a new column (if it doesn't already exist) to FctSRA which is the foreign key to FctSR. Then change that relationship in the Dimension Usage tab to a regular relationship. Frankly I'm not sure how the other many-to-many relationship is working properly now with no common dimensions.