Star Schema Problem

[tracer1]

Hi All,

I was hoping someone could help me with a problem I am having creating star schemas.  I am trying to create 2 star schemas linked with a conformed dimension.  After I published the Framework Manager model and created a report it works fine if I select fields from tables in 1 star schema but when I try to add a field from a dimension table in the 2nd star it does an outer join

According to the course on Framework Manager when you don't se coalesce and you see RSUM as well as Full Outer Join with sc this could be a problem.

Here is a part of the sql that I get when I join 2 star schemas with a conformed dimension

*************************
...
D14
RSUM(1  at Office.Description  order by Office.Description asc  local)  as  sc
...     

D15
RSUM(1  at Application_Type.ApplicationTypeDesc  order by Application_Type.ApplicationTypeDesc asc  local)  as  sc
...

select
       D15.ApplicationTypeDesc  as  ApplicationTypeDesc,
       D15.ApplicationID  as  ApplicationID,
       D14.Description  as  Description
from
       D14
        full outer join
       D15
        on (D14.sc = D15.sc)
       
       
*****************

I have tried creating star schemas from other tables as well to keep it simple and I still get the same results.  I created them by hand as well as using the group by star functionality in FM.

I know its probably difficult to debug with what is above but does anyone have any advice of what to look for specifically in debugging star schema problems.  When I was on the FM course early last fall, they had a very simple and small database and it worked as advertised but now that I am trying it on a much larger database the concepts don't seem to work that well.   I wish I still had access to the database files we used. 

I'm probably missing something very obvious but just can't see it.

Thanks for any help

Brian

[JGirl]

Hi,

If you arrange your tables into star schemas in your FM (through 1..1 on the Dim Side and 1..n on the Fact Side), when you attempt to report off more than one star schema, Cognos executes what is called a 'stitch query'.

What this means is that it executes the query on each star schema separately on the database server and attempts to 'stitch' the two queries together on the cognos application server through the levels in your conformed dimensions (this is where the full outer join comes in).

The 'stitch query' and the 'full outer join' are actually used to ensure that rows don't drop off your multi fact queries from either side.   A classic example is actuals & budgeted sales.  If these are kept in 2 separate fact tables (F_ACTUALS and F_BUDGET) and share conformed dimensions (D_STORE, D_TIME and D_PRODUCT), and are modeled as star schemas you have the following scenario:
* A store can have actual sales records for a product that has no budget record
* A store can have budget records for products that have no sales records
* If you want a report that shows budget vs actual, you would want to include not only products that have actual and budget records, but also products that have only actuals records as well as products that have only budgets records.

There used to be a document available on this from the Cognos website (titled something like 'building FM models for predictable results') which explained the stitch queries, and impacts of cardinality choices in FM, but I was unable to find the link to it.  If I can find the URL I will post it later.

Hope this helps
J