Unwanted stitch query due to data model

[bi.user]

Dear All

I have created a Snowflake FWM model based on our business model. We have a Factless Fact table in center and multiple hierarchical parent directly related to it and child tables related to parent. As per the model, few parent tables will have 1-N cardinality with child table and fact table. This is causing Full outer join issue when we try to pull data from Child, Parent and Fact table.  I have attached the scenario and Sample SQL generated. Please let me know how to modify the FWM model with out affecting the business model.

TIA..

[bi.user]

..Sample SQL generated

[bus_pass_man]

I can't remember where it is (my fm course books are on the shelf at work where I am currently not as it is both nearly midnight and I'm on holiday) but there's a section in the fm course about dealing with queries that should not be split.  There's also a section called, interestingly enough, 'Resolving Queries That Should Not Have Been Split' which is in the FM user guide. Searching Cognoise using the term RSum should return some results you might want to look at too.

Actually Parent is in the centre, if you mean 'is a dimension not a fact'.  FACTLESS_FACT_TABLE and CHILD each have a 1.n relationship to PARENT.  So you're telling the query engine that they ought to be considered to be fact tables, which is why you're getting the results which you're getting.

If by 'We have a Factless Fact table in [the] center (sic)' you mean 'is a fact not a dimension' and that PARENT and CHILD are snowflakes in a dimension then you might want to consider option 2 from the user guide. 

If there was a relationship (i.e. a conceptual relationship which you could model not necessarily one that existed in the database) between CHILD and FACTLESS_FACT_TABLE then (provided the relationship between PARENT and FACTLESS_FACT_TABLE did not exist ) you might also get the results which you are expecting.   I'm assuming this is not the case and that the grain of the fact is at the parent level.

Just out of curiosity, are you sure that your key query items have their usage set to identifier?  Judging from the bitmap you don't have any query items which are marked as facts but it never hurts to check to make sure your query items' usage property matches your intent.

[bi.user]

Hi bus_pass_man

Thanks a lot for your reply. I did check the Guide and other threads with 'Rsum'. All the query items are assigned with correct usage i.e dates -> identifier and dims -> attributes, no facts.

I figured out that CHILD and PARENT are both at same granular level (both are dimension details at product level with no facts). So I joined the CHILD with FACT table and it is producing expected results.

Though I was lucky, I am very curious to know what to do just in case these tables doesnot comply with Option 1 or Option 2. Imagine CHILD and PARENT at two different granular levels, how to handle that scenario?

[bus_pass_man]

i.
If parent and child are truly of the same grain then why is there a 1.n relationship between them? Are you really sure? For any key in the parent table how many records are there in the child table? If there is truly a 1.1 relationship why don't you change it to be that?

What does the model fragment look like now?  Which relationships exist now?

ii.
I'm not quite sure what you mean by 'both are dimension details at product level with no facts'.

iii.
So, just to clarify, the hypothetical situation in your question is the same as the jpg: Parent has a 1.n relationship to child and a 1.n relationship to factless_fact_table. Parent and child are a snowflake dimension. Is that a correct understanding?

If so, the answer is the same as any multi-grain scenario. You need determinants. You need to understand that child is below the fact grain of your fact table and including it in a query will produce double counting.  Determinants attempt to prevent double counting.

If you open the great outdoors sales sample model, you can see a similar situation with the products snowflake and the product forecast and sales target fact tables. The dimension grain extends below the fact grains. Fiddle around there. I can't remember if the sample model has determinants in both the model query subjects and the data source query subjects or not but check both. Try to create a query with dimension stuff below the fact grain and see what happens.  Edit the determinants to see what happens.

In DMR, the concept of scope relationship is similar and attempts to achieve the same result.

iv.
Another thing you need to understand is that a query subject which is on the N-end of a relationship and is at the end of a query node will be always understood by the query engine to be a fact table and the query engine will attempt to treat it as such. This is logical as what the heck else can it interpret it to be? The only other option is if the query subject's a bridge table, and the fragment of your model isn't big enough for me to say if this is a bridge table scenario.

If you run the model advisor this sort of situation is one of the things which can be identified.

v.
You were going on about not wanting to alter the business model. Why not? You need to layout a query plan which matches what is really going on otherwise you'll get rubbish results.

vi.
OK this is where I repeat myself again:

Have you had a chance to get some Cognos training? It would be very useful. In addition, a grounding with industry knowledge is a sine qua non to understand why you do certain things in Cognos and why certain topics get raised. Kimball's works are quite helpful to understand Cognos in the context of the industry.

Being able to recognize common patterns and know how to model for them is important.


Hope that helps.

Decide, Deploy, and have a nice day.

[bi.user]

Hi bus_pass_man,

Issue is resolved with couple of changes, which should have been done way back by data team.

Thanks a lot for your detailed reply. Appreciate your time,patience and knowledge  :)