I have a situation in Universe desining and the situation is that I have 5 Fact tables with different grains and these fact tables have huge volumne of data and I have 10-12 dimension tables.
As per the requriement I will need to provide this tabels for ad hoc reproting and also the query will go through different fact table.
Now the issue is how do I query the data from these facts as I cannot join the fact tables to each other as they are on differet grain and will also hamper the performance. I cannot go with the context as it will limit the data to get the results on common dimenions and it will not be the true ad hoc reportng as if the user will choose different dimesions than it will not give the results in the report.
I am thinking the concept of having factless fact table and than joining the facta and diemsions to it but I am not much savvy on fact less fact table.
Reconsider this option. Using contexts is a good way to properly construct a universe with multiple fact tables and conformed dimensions. Why do you think it would not be true ad-hoc reporting?
Some dimension will be conformed and be used across all involved fact tables, some dimensions will be unique to only some of the fact tables. This is totally normal when modelling data including universe design.
I appreciate all of yours inputs. The reason I think context may not allow true ad hoc reproting as when we use context for two different fact tables than we can only use the dimesions which are common.
We cannot use the dimenions which are not common to the fact tables so what will be suggestion on this.
What you guys think of creating factless fact table and joining the facts to it. Will it help to avoid context.
That’s not true – if you select objects from two fact tables, and a dimension that is common to one but not another, WebI will build two queries and construct two independent report blocks.
(BOB member since 2002-08-29)
(BOB member since 2002-06-20)
(BOB member since 2010-12-03)