OLAP and Modelling with Multiple Fact tables with different granularity

[dinos]

Hi,

I have two sets of data stored in what you would call a galaxy schema; 1 star schema that contains real data at a daily level and 1 star schema with benchmark data at the year level.

I have joined the two star schemas via the time dimension.

When I use OLAP on just one data set at a time, I am fine.  When I try to look at say actuals vs benchmarks across the two sets of data, I get inconsistent and incorrect results.  The only way I can make it work is if I use Report Studio.
 
Does OLAP support multiple fact tables?

[bdbits]

This is generally called a snowflake schema.

The rest of your post is too vague. What does "use OLAP" mean? You say you can "make it work" in RS - what are you doing there that is different?

Snowflake schemas, if correctly modeled, can work fine.

[Lynn]

Have you set determinants so that the different levels of granularity are addressed properly? Have you consulted the FM user guide for information on modeling multi-fact multi-grain queries? Their example is actually quite similar as it compares actual data at a daily level with forecast data at a monthly level.

I agree with bdbits that the rest of your post is lacking enough information.

[MFGF]

I'm with Lynn on this one - if I was a betting person I'd wager you are missing determinants on your Time dimension. You need at least two - one for the Year level (defined first, with the Group option selected) and one for the date (defined second, with Unique selected)

MF.


Sent from my iPad using Tapatalk HD