September 9, 2019 at 4:22 pm
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!
September 10, 2019 at 12:59 pm
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.
September 10, 2019 at 1:02 pm
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
September 10, 2019 at 3:43 pm
Sorry, but I don't even know if MD supports many-to-many relationships.
September 11, 2019 at 12:34 pm
If it is not supported, why is it allowed to have several fact tables in place and define linkages :-(?
Is there a workaround?
September 11, 2019 at 4:20 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply