Framework Modelling question

[divyachalla]

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

[OLAPBPMguy]

What Cognos products are at your disposal? This would be trivial to achieve with TM1 or a PowerCube.

[johnck]

If I've read this correctly what they want to do report on data that does not exist. Framework Manager is doing exactly what it should; because the combination of Melbourne and Jones does not exist it can't show it to you. In database terms it's like the difference between 0 and NULL. 0 exists, and is a value, even though its 0, NULL just doesn't exist. I once spent a evening in the pub discussing how wired a concept that was, and how to explain it, with a friend of mine, until we realised how geeky we were sounding.

If you have it you could make PowerPlay do it, having Customers and Sales Offices as different dimensions as it would fill in the NULL values with 0. It would also give you a very big and mostly useless report/cube. 10 sales offices and 500 customers will give you a report with 5,000 data points, add in 12 months and it becomes 60,000 data points.

The way you said you could do it in SQL, is pretty much they way to do it in Framework Manager. Create a dimension that is a full outer join of Customers and Sales Offices and link that dimension to the Sales fact. The combined Customers/Sales Offices dimension will be very big and sadly won't be of much use outside of this report, unless they come up with other reports where they want to see all customers against all sales offices.

It could be done using several queries in report studio, but that would be slow to run, and not something that I would ask a user to create, and it would have to be done from scratch each time it was needed.

The only other way I can think of doing it would be to change the source data, but I'm guessing that's totally out

Sorry I can't be of more help