[closed]Catalog with Multi Star DW Schema

[forsasi]

Hi,
In my catalog i have two fact tables namily SALES_THIS_YEAR, SALES_LAST_YEAR. table structure is same in both tables. my dimensions are PRODUCT_DIM, LOCATION_DIM, both dimensions are joined with all the facts( it creates LOOP, i have not resoved it. My end user not interestd to have different set of dimension for this year and last year because user going to analyise Sale_Qty of this year and last year in the same report).

if i am trying to create a report with Sale_Qty from SALES_THIS_YEAR and SALES_LAST_YEAR table with dimension PRODUCT_DIM, LOCATION_DIM. i am getting wrong res1ult. how to solve this issue.

in general if we have more than one fact with common dimensions how to resolve the loop issue with out creating alias.

Please refer my attached document. it has the table structure, join condition and sample report.
for more information about the DB design and report requirement please refer attached document

Regards
Sasikumar M.

[cognosfreelancer]

Sasi

I can think of atleast two approaches.

1) Since you are viewing facts by the same dimensional attributes i.e. metrucs from 'sales this year' and 'sales last year' for the same product and location you will need to join the two facts to one another ( something that I personally dislike) and then modify the joins between the dimensional tables and fact tables such that the dimensional tables join only to one of the two fact tables.

This join unfortunately needs to be an outer join on the fact table ( performance penalty!) since there may or may not be sales for the particular product and location in the fact table being joined to the dimension table.

Under the above circumstances you will not see metrics in other fact table unless you have the outer join as described.

2) Since both fact tables have structurally identical columns, you could do a UNION set on the two fact tables to get just one fact table.

You will need to create a new column to distinguish which fact table each is originating from. You could use a simple decode statement such decode (1=1,'Current Year Fact') within SQL.

I like the second approach much better. You will need to write the SQL withing the report which means you will lose the formatting features of Impromptu. Also changes made to the caralog will no longer reflect in this report.

You should convince your DBA to write a database view of the above UNION statement so that you will have the best of both.

HTH
NKT

[cognosfreelancer]

I have not heard from you. I assume you have found your solution