Forum Discussion
interdimentional reporting
I have following facts (allReservations) and dimentions model. I uses a factless fact table (allObjectReservations) to properly manage the n-m relations between a reservation (the fact) and its objects (dimentions) of different types. So 1 reseration is related to 1 or more object types and per object type there are 0 or more related objects. Each dimention uses a distinct extid format , so the object type in the allObjectReservations is not used to make the link. It's there a match, then then match is de facto correct.
So far so good.
I now try to make a report which is interdimentional. Now my reports are always empty.
Note:
- the factless fact<-->dimention relation have properties: *:1, actif, Both (filtering direction), No (apply security to both)
- the factless fact<--> fact relation has properties: *:1, actif, Both (filtering direction), No (apply security to both)
Is this inter dimention reporting possible or not? What is wrong in the model?
BR
C.
You are right. I should have done that right from the start as part of my learning cycle.
Anyway: I finally made an 'identical' model, but scaled down to 2 dimentions and a handfull of records/rows.
Thanks to your tip.
I identified the reason why it didn't work:
I used a single factless facts table for linking all dimentions to the facts table.
When splitting that table into several factless fact tables per dimention all reporting started to work.
So with a single DAX call I split objectReservations into roomReservations and personReservations.
Looking back onto it, it is very logical why using a single factless fats table doesn't work .. but again .. it's a learning cycle
Hope it helps someone else
BR
Christof
3 Replies
There are alot of bi-directional relationship I can see in the model view. What I suggest, is start with a smaller model with just the one fact and one dimension. Do not use bi-directional and see if you can get a report from that one relationship. Next, add a second dimension and test. Keep builder (and not use bi-directional) until you get to a point where the last dimension is added that affects report, then add bi-directional to just that one dimension relationship.
You are right. I should have done that right from the start as part of my learning cycle.
Anyway: I finally made an 'identical' model, but scaled down to 2 dimentions and a handfull of records/rows.
Thanks to your tip.
I identified the reason why it didn't work:
I used a single factless facts table for linking all dimentions to the facts table.
When splitting that table into several factless fact tables per dimention all reporting started to work.
So with a single DAX call I split objectReservations into roomReservations and personReservations.
Looking back onto it, it is very logical why using a single factless fats table doesn't work .. but again .. it's a learning cycle
Hope it helps someone else
BR
Christof
I have several report pages , one page per object/dimention type, which are just 1-dim visuals/reports. These work fine.
One thing I didnt mention: I have a dateintelligence table. Initially I thought I had to magage that through a relation between this Date table and the allReservartions facts table:
I could make the reports work when setting the filters birectional. Even before using the formal 'set this table for dateintelligence' on the Date table. I now seem to understand that that relation is not needed: just identifying it as a dateintelligence table is sufficient.
Maybe this impacts the model ... or should I even delete that relation and just set is as a date intellligence table?
