How to join several dimensions and fact tables correctly to get data filtered

• svenflock

  Old Hand

  Points: 366

  September 9, 2019 at 4:22 pm

  #3678541

  Hi all,

  first of all, I am new on Analysis Services and I did the official tutorial from Microsoft (AdventureWorks) to get started. So apologies first, if the questions is very basic.

  My use case is a little bit out of the box compared to most of the fact tables I have seen. So, here is my problem. My fact table "fact_XYZ" stores data not point in time but historically with VALID_FROM and VALID_UNTIL. To filter on specific dates, I had to introduce additional dimensions and factless tables due to the fact that a date can be within more than one date range and a date range has several dates (n:m correlation). So, dimension "dim_Datum" stores all calendar dates since my first record, dimension "dim_Spell" stores all distinct date intervalls needed, "fact_Datum" is the factless fact table (n:m) and fact table "fact_XYZ" (in the picture it stops after the underscore) ist the VALID_FROM/VALID_UNTIL based fact table.

  I have created the proper foreign key constraints, checked them in SSAS and from an SQL point (INNER JOIN and going the way manually from dim_Datum to fact_XYZ): If I pick a specific date, I get the required fact table entries as expected.

  But having a look at the cube browser, somehow it does not work! I just made a little test and I wanted to count the fact rows per year but I always get the total count of the fact table. Filtering doesn't work either.

  What have I missed in the configuration?

   

  Thanks for any input!

  Attachments:

  You must be logged in to view attached files.

• Brian Carlson

  SSCarpal Tunnel

  Points: 4158

  September 10, 2019 at 12:59 pm

  #3678733

  If this is tabular, you can set up a two-way relationship on fact_datum. Off the top of my head I'm pretty sure it's to dim_Spell but it could be dim_Datum. If you're working in MD, I can't help you there.

• svenflock

  Old Hand

  Points: 366

  September 10, 2019 at 1:02 pm

  #3678758

  It's multi-dimensional by model.

  Do you think dim_Spell is the problem in MD, too? Why do you think it is the cause?

   

  Thanks

• Brian Carlson

  SSCarpal Tunnel

  Points: 4158

  September 10, 2019 at 3:43 pm

  #3678820

  Sorry, but I don't even know if MD supports many-to-many relationships.

• svenflock

  Old Hand

  Points: 366

  September 11, 2019 at 12:34 pm

  #3679096

  If it is not supported, why is it allowed to have several fact tables in place and define linkages :-(?

  Is there a workaround?

• svenflock

  Old Hand

  Points: 366

  September 11, 2019 at 4:20 pm

  #3679192

  Ok, I found a way to define the many-to-many relationships but the results are not explainable :-(. As the original thread title does not match anymore, I will open a new topic.