How to query on multiple fact tables ?
Hello all,
I know this is a recurring subject around here. I have read various topics and tried many thing but I couldn't reach my goal :
I want to query a BMM with 6 fact tables that all have common dimensions.
For instance, I have Invoices facts, Stocks facts, and a common SpareParts dimension.I am currently using a prewiew of my target BMM, which contains only these 2 fact tables and this single dimension. They are joined both at physical and logical level.
I'd like to build a report showing for a given spare part both stock and invoice information, but Answers replies it can't link Stocks with Invoices (even when I use the spare part dimension in the report).
* I can't afford to put both fact data in a single table as I'm most likely to report on the 4 other fact tables the same way.
* I can't either alias my dimension table, as I'd like :
- to avoid having 6 spare part dimensions for my 6 fact table (imagine the user's face in front of that)
- to have the filter on a spare part filtering on both tables at once (as I talk about the same spare part on both sides)
Unless you tell me I can create an alias and then say somewhere it's actually the same dimension...
* A colleague tried to alias the dimension and then build a 1-1 join between the dimension and its alias, but it didn't work.
* I have tried to build a hierarchy for the spare parts with all the fact data at the lowest level, but it didn't do. I guess hierarchies are not intended to be used that way.
* I have tried the bridge table to link the two fact tables but i get a circular path. Furthermore, as the fact tables then turn to dimensions, I'm pretty sure my aggregates won't work... I guess bridge tables are not intended to be used that way.
* I have tried singing, dancing, jumping and bouncing around my desktop but all I got was rain.
Can anybody catch me before I jump across the window ? I wouldn't like to get wet...
Many thanks in advance.
Ced.