Framework Manager Modeling Relational Data

[xyphos]

I have a few questions that I hope to find the answers to. First of,
Is it wise to model relational data in Framework Manager?(Not dimensionally)
Secondly,
Are there any repercussions to doing so?
If not, are there any best practices to doing so?
Thanks for any help provided.

 

[yphilogene]

Hello,

I'm not sure I undestand your question as "dimensional" modeling in Cognos 8 can refer to the DMR technology.

I guess your talking about "relational" modeling such as tables linked with each others without any star schema pattern.

I don't think it is a good idea to use Framework Manager to design such a relational model, as you may be confused by some of the specific (and very useful) behaviors of the tool.

For example, when you define a relationship between two query sybjects with a 1..1<-->1..n cardinality, you expect the generated SQL query to contain an inner join. However, it is not always the case! Defining a 1..1<-->1..n cardinality tells the engine to consider the table with 1..1 as a dimension table, and the table with 1..n as a fact table. Therefore, if you have a dimension table DIM1 and two fact tables FACT1 and FACT2, with relationships "DIM1 (1..1) <--> (1..n) FACT1" and "DIM1 (1..1) <--> (1..n) FACT2" defined, then requesting elements from those 3 tables will generate a SQL query containing an OUTER join in order to display indicators from FACT1 and FACT2 independently.

This Framework Manager feature is very useful and perfectly matchs the star schema design philosophy. But it may interfere with your idea of "relational" design.

Framework Manager also warns the designer when loops appears in the model, while loops are quite common in "relational" modeling.

I would not recommend "relational" modeling with Framework Manager.

 

[sln007]

xyphos,

It is perfectly valid to join non-dimensional, de-normalized, reporting tables in FM.

Just be accurate with your join columns between tables.

Left-Outer-Join is allowed ( 1..1 ---> 0..n )
Inner (Equi) Join also ( 1..1 ---> 1..n (or 1..1) )

Hopefully, your denormalized tables are subject area specific enough so there won't be needs for more than 1 join to another table.

Steve N.
State of Ohio, MIS