Data from two facts in a report table

[fml55555]

Hi All,

I have a requirement where I need to show data from two facts in a table(list). I have attached my attempts to model the FM to satisfy the requiredment. I would truely appriciate your inputs as it seem to be a lot of work and any insight would grately help me.

Thank you

[MFGF]

Hi All,

I have a requirement where I need to show data from two facts in a table(list). I have attached my attempts to model the FM to satisfy the requiredment. I would truely appriciate your inputs as it seem to be a lot of work and any insight would grately help me.

Thank you

Hi,

It's not entirely clear from your attachment what you are doing. I'm partly guessing, but it looks to me like you have two fact query subjects and four dimension query subjects?

You need to model the cardinality of the relationships between the dimensions and the facts so that the dimensions are at the 1 end and the facts are at the many end. If the facts link to a dimension using different keys, you also need to define determinants in the dimension query subject to specify how the facts should be aggregated differently.

Other than this, simply bringing in the desired dimension query items and the necessary measures from each fact should result in a stitch query, giving you accurate results.

Cheers!

MF.

[fml55555]

Hi Mr. Muppet! Thank you so much for your answer. Just to be sure that I unserstand what you are saying...you are suggesting a loop? for e.g. a two fact two dimension list in report studio with the structure

Years                 |   City                              |       Revenue     | Direct Cost
(time dimension)|(Geography Dimension)  |    (from fact 1)  |(from fact 2)
                          |                                       |                         |
                       
(This whole thing is grouped by year)

To achieve this, FM model should look something like this(in attachment)

Will this not cause a loop which is something we need to avoid?

[MFGF]

Hi Mr. Muppet! Thank you so much for your answer. Just to be sure that I unserstand what you are saying...you are suggesting a loop? for e.g. a two fact two dimension list in report studio with the structure

Years                 |   City                              |       Revenue     | Direct Cost
(time dimension)|(Geography Dimension)  |    (from fact 1)  |(from fact 2)
                          |                                       |                         |
                       
(This whole thing is grouped by year)

To achieve this, FM model should look something like this(in attachment)

Will this not cause a loop which is something we need to avoid?

Hi,

Yes, this is exactly what I am suggesting - it's not a loop per se. The query engine recognises that the two query subjects at the "many" ends of the relationships should be treated as facts, and because it is a query spanning multiple facts, a query split (stitch query) occurs.

Loops are an issue that needs to be resolved before further modelling takes place when dealing with operationally-modelled table structures (ie normalized tables from a transaction system), but the end goal is to have structures in your model that look (and behave) like star schemas. The query engine is designed to work with star schema structures and to be able to produce accurate, consistent results across multiple facts (at different granularities) linking to the same common set of conformed dimensions.

Cheers!

MF.

[fml55555]

Thank a ton Mr.Muppet! It worked like a charm. I was afraid of trying it as I thought it creates a loop