Framework Modelling Question

[geethkal12]

Hello All,

We now have this fantastic star schema, with a neat fact in the middle, and dimensions on either side. All a good 0-to-N relationship and all working great, till users starting working with it....

What they are now requesting (users....) is show ALL the values in the dimensions, even if they would report "over the fact", all the time, every where, for every combination...

So for example:
Customers <--- 0 to N ---> Sales <--- N to 0 ---> Sales Offices

•Dimension 1 is "Customers"
•Fact is "Sales"
•Dimension 2 is "Sales Office"

They would like to see ALL Customers every time you drag the Customer Item in, in every report, but also ALL Sales Offices, regardless if either of them have a sale.

Example could be: Please refer Excel Attachment

So all sales for Melbourne should be shown as 0 (although there are no sales for Melbourne), in the present solution Melbourne will be shown, however as soon as you use "Customers" it will force the join over the fact and exclude Melbourne).

All sales for Rogers should be shown as 0 (although there are no sales to Rogers in the fact table), same as previous problem, works well till you drag another dimension in and start joining over the fact.

Users...

Any idea how to resolve this IN COGNOS Framework manager?

SQL is quite simple ((Customers Full Outer Join to Sales Office), than Outer Join to Sales), but Cognos does not do this this way...

Any clever suggestions? Please advise

Thanks a lot

 

[blom0344]

You cannot meet these type of requirements with one neat starschema model. The next demand would be issues like : "Which customers had NO sales in the first 2 months of 2009 , but became active in the second half of 2009."
(And every possible other report after that)
Those are typical reports for a prof. report builder. It would include creating joins within reports and using such nice set based operators as union and excepts.
Again, there is no simple model solution that can cope with your scenario. Reporting on what has not been is so much more complicated than positive results.



Ties Blom