List Report with two sets of facts

[hem852]

Ok, I've now been using report studio for a couple of weeks and coming from a BO environment to this is very frustrating! It seems achieving even the simplest things are overly complex in report studio :(

The problem I have now is in my package I have two query subjects containing facts, one contains actuals the other budget figures. All facts within the two query subjects are identically named and even appear in the same order!

What I want to produce is a simple report with the name of each fact down the left, the Actuals facts in the first column, the Budget facts in the second column and a final column giving the variance. Could anyone give me some pointers on how to produce this please? It seems impossible to get the two sets of facts into a useable report. 

TIA

John

[Stejawh]

Have they got a common dimension?

[hem852]

Yes they have 4 common dimensions.

John

[Stejawh]

Have you tried a Joined query (joining on one of the common dimensions?)

[hem852]

Tried it buit can't get the thing to work! Very frustrating! How hard can it be to take one number away from another!

[bobj]

What is the output you receive when trying to do the above? i.e. do you get high numbers (double counting), do you get an error or do you get duplicates etc.

If you are receiving high numbers then check the deteminants you have set in the Query Subjects.
Are the fact tables and query subjects at the same granularity?

Stejawh's solution should work but if the DW and FM are correctly structured then there would be no need for Stejawh's solution. Correct me if I am wrong Stejawh, but there should be limited need to perform joins from within the reports because if the DW and FM are structured correctly you can achieve the creation of more-or-less any report.
On a side note, you will find that joining two fact tables directly breaks all the laws of good data warehouse design, and more importantly, the best practices of Framework Modeling.

If you want to know more about ETL, Data Warehouses and Star Schemas, I recommend the web site and books by Ralph Kimball.
It is important to follow DW best practices. I have found them very counterintuitive and sometimes even in violation of the best practices I've used for years in DBMS. But, when you follow the DW best practices, and set things up the correct way, it provides great ability to produce just about any report you want against your measures quickly and easily. The leverage return on the investment of setting up the DW correctly is well worth the effort.

[hem852]

Thanks, I modelled out the problems in framework manager in the end, combining the fact tables and adding some keys.

This data isn't coming from a data warehouse, it is straight from an OLTP system which we have no control of.

[Stejawh]

Bobj,

I agree.  We should really be aiming to set the joins in the the Framework rather than in reports.

Steve

[bobj]

I'm glad you agree Steve because otherwise what really should be done in FM is being done in RS.
To create joins in RS may have an impact on the processing. The general rule is to put heavy processing on the most powerful resource, this goes for joins aswell as calculations/formulas etc.

Thanks,
Bobj BI.

[kristielee]

I agree that the joins should be established in FM, but have you tried a union?