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

  • svenflock

    Old Hand

    Points: 366

    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

    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

    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

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

  • svenflock

    Old Hand

    Points: 366

    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

    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.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply