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!