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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy