Multiple STAR Dimensional Data Warehouse and Framework manager

[edcognoise]

We have a dimensional design data warehouse with multiple facts and dimensions. If we have a report within one STAR, the reports are fine, but as soon as we get data from multiple STARs, we get the full outer joins stuff and filters do not work. It looks like we need a STAR to STAR relationship. We tried to put FACT to FACT relationship, but the performance was horrible. FACT tables have different data with different number of the shared dimensions. Some have 1, some have 2 or some have 3 shared dimensions.  Is there anything in the Framework manager that we need to set to let the Cognos know about relations between STARS? For example, we need to have a report with courses, sections, student enrollments and section instructors. The shared dimensions are term and section. How we can accomplish communications between STARs in the Framework?

[norkos]

Hi,

There is no problem, it's the normal behavior of Cognos. If your report use more than one fact table, than the Cognos query engine will create subqueries from each stars and join them with full outer joins along the comformed dimensions - not to loose any data from the fact tables. This method is called stitch query.

I recommend to read this guide from IBM to get more familiar with the Cognos query engine and multiple fact queries.
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605-pdf.pdf

[MFGF]

Can you elaborate on the "filters do not work" part? As norkos says, stitch queries are intended when reporting off multiple stars - they are designed to return accurate, consistent results.

Did you remember to define determinants on your conformed dimensions where facts join at different levels of granularity?

MF.

[cognostechie]

The filters do not work because you are filtering the Fact table, not the dimension ! There is no need to create Fact to Fact relationship. Joins on conformed dimensions would work just fine if you applied the filter to the Dimension, not the Fact !

Cognos would create one query involving Dim1 and Fact1, another query with Dim1 and Fact2. Both of these will have inner joins . The outer join would be on the query that sticthes both of these queries so it does not matter for the purpose of the filter. Assume you have inner joins between Dimension and Facts so both the queries will get filtered if you filter the Dimension. The resulting outer query which has the full outer join does NOT need to get filtered because both the queries are already filtered before they get stitched !

I made a report with 6 Facts and all of them get filtered when I apply the filter to the shared dimension.