Conformed Dimensions ; Star Schema Grouping ; Best Practices

[Ravisha]

Hi,

I'd love to know the best practices of modelling the scenario below.

While modelling the Data Warehouse, I came across the scenario where we have many fact tables joined by many conformed (also acting as role playing) dimensions.

For discussion's sake, let's say we have 3 Fact tables (Fact 1, Fact 2, Fact 3); all together joined by 3 different conformed dimensions (CD 1, CD 2, CD 3) which are also a role playing dimensions (remains at the same granular level).

My question is if I create a "Star Schema Grouping" on the aforementioned Fact tables ( by choosing the options -> Create shortcuts only for objects used outside the star schema & Create a new namespace for this grouping), I'll end up having 3 different namespaces and the conformed dimensions (CD 1, CD 2, CD 3) repeats in every namespace.

Is there any way to avoid these repetitions?

With this approach, how can I report information against 3 different star schemas? Since the conformed dimensions repeat in every namespace, I'll end up pulling identical columns from every conformed dimension across 3 different namespaces.

Kindly, please guide me through this mystery :O

Thank you

[bdbits]

If you need to report across the multiple fact tables, use a single namespace with shortcuts to all the facts and dimensions. If your users are typically focused on a single fact table, you can either keep multiple namespaces in a single package, or create separate packages for different user groups, all from one model.

You are not restricted to what the wizards do for you. They generally are meant to work with simple scenarios.

[Ravisha]

Thank a lot for your time to answer my question.

Even if I create a shortcuts to all facts & dimensions, I'll end up having the dimensions repeated in the business layer (since you've to pull the query item from the shortcuts corresponding to its respective Fact table). The dimensions are conformed but not the facts. I wonder why we have to create shortcuts for Fact tables?

The end product must be feasible to report across all the fact tables. Kindly please suggest.

Thanks

[MFGF]

Thank a lot for your time to answer my question.

Even if I create a shortcuts to all facts & dimensions, I'll end up having the dimensions repeated in the business layer (since you've to pull the query item from the shortcuts corresponding to its respective Fact table). The dimensions are conformed but not the facts. I wonder why we have to create shortcuts for Fact tables?

The end product must be feasible to report across all the fact tables. Kindly please suggest.

Thanks

Hi,

You can create whatever shortcuts you need. If you don't need specific ones, you don't have to create them. If all you need is a single namespace containing shortcuts to all facts and their relevant dimensions, then just create this. If you have multiple package requirements where each package shows different facts or different selections of facts and their dimensions, then create however many namespaces you need to support these packages, each containing the appropriate shortcuts to make package production simple.

The "Create Star Schema Grouping" option is simply a quick way to create a namespace and shortcuts for a single fact and its dimensions. You don't have to use it though. You can create a namespace manually and add whatever shortcuts you need manually if that makes more sense. Or you could start with the Create Star Schema Grouping option to give you a head start, then manually add shortcuts to the other fact and dimension query subjects into that namespace - as required. Just do whatever makes the most sense and gives you the least work :)

MF.

[Ravisha]

Hi,

Thanks a lot for your response. I totally concur with you.

I'm really sorry If I didn't do a good job in explaining my question. I've attached an example diagram image with this post; which clearly explains (hopefully) my question. Kindly, please take a look at it and help me to improve my skills.

Thank you all for your unending support ! Really means a lot to me :)

[dougp]

Looking at your diagram...

If what you want is everything in a single namespace, don't bother with the star schema grouping.  Just create query subjects in your business layer.  This allows you to reference the objects in the database layer and change names and column order and to hide columns or create additional computed columns or use calculations to change data types -- whatever will be convenient for the users.  You may want to create a separate namespace for this with query subject shortcuts and relationship shortcuts.  You don't need to use the "star schema grouping" feature to do this.

It may be because I'm still relatively new to Cognos Framework Manager, but I have found that including multiple facts in a namespace can create unpredictable results based on many user behaviors.  This is because Cognos will figure out how to join two query subjects based on what it finds anywhere in the model.  If it can't join based on relationships in your Business layer, it will try to follow the relationships in your Database layer.  If a path -- any path -- is found, Cognos goes on its merry way and produces some really strange results.  If there are multiple relationships that can be used, Cognos chooses the the one where the first intermediate query subject in the join path is alphabetically first, but it may also choose the path that has the fewest query subjects.  So it seems random until you dig really deep into the problem.

I use a 3-layer approach:

• Physical layer (database layer):  Contains the object imported using the metadata wizard.  Relationships may be helpful but are not necessary.
• Business layer:  All of the logic is here.  Relationships I want used for producing joins, data type casts, computed query items, custom query subjects, human-readable names for query subjects and query items.
• Presentation layer:  Multiple namespaces - one for each fact.  Namespaces contain only shortcuts to query subjects and relationships in the business layer.

[Ravisha]

Hi,

Thanks a lot for your response. But, I strongly believe that it's definitely do-able (multiple facts in a single namespace). I kind of having a hard time to figure out the approach. I kind of constantly staring at the diagram to figure out the little missing piece (logic) but no luck.

Thanks

[MFGF]

Hi,

Thanks a lot for your response. I totally concur with you.

I'm really sorry If I didn't do a good job in explaining my question. I've attached an example diagram image with this post; which clearly explains (hopefully) my question. Kindly, please take a look at it and help me to improve my skills.

Thank you all for your unending support ! Really means a lot to me :)

Hi,

Looking at your diagram, it appears you have all required relationships defined in the database layer. I'm assuming that your fact query subjects are defined with cardinality 1..n and your dimensions with 1..1 for each relationship?

Based on this, you don't need (and shouldn't use) any relationships in your business layer. You'd just have one model query subject for each fact and one for each dimension, and you'd change names to make them friendly and add any calculations etc.

For your package production, you'd create a namespace containing shortcuts to Project, Contract, Sales Fact and Receivable Fact (pointing to the model query subjects in your business layer). Again, there would be no relationships defined here.

Cheers!

MF.

[bdbits]

The 3-layer approach is most common and generally considered "best practice" (maybe 4 if you also have DMR). Database with relationships, business layer pointing at database layer but no relationships, and a presentation layer that is usually just shortcuts to the business layer, organized in whatever way works best for those who will be using your package(s). Make sure you set cardinality, and determinants if needed. Cardinality in particular is crucial to making sure the package works properly with multiple fact tables and conformed dimensions.

Whether to include multiple facts in a single presentation namespace is highly dependent on your model and user sophistication. It can be risky if your audience is not particularly experienced or quite familiar with querying their data. On the flip side, since a report is tied to a package, depending on your reporting you may actually need multiple facts in a single package. It just depends, a standard is not realistically possible, in my opinion.

[magicksol13]

Good morning to all, we are fa ing similar situation with Cognos (versus BusinessObjects where you can use CONTEXTS).


We have been asked to add a new join between a factTable and a DimTable that clearly visually shows a LOOP.


This is before:


        TblFactA
        /.           \
      /               \
TbleDimB  TblDimC



This after:

            TblFactA
               /.       \
             /.           \
           /               \
TbleDimB-----TblDimC

All these related tables also have their own relations with other tables that are not show in here as for not impacted in the loop join.

QUESTION: I ALREADY CREATED AN ALIAS SHORCUT TO TblDimC BUT was wondering if I still needed to reproduce all other links that TblFactA or TblDimC might already have but not concerned on this LOOP

[bdbits]

In my opinion, you should never be joining dims directly like that. If there is a demonstrable relationship, you probably either are missing a fact table or you need to collapse the dims (e.g. there is a natural hierarchy). Or you need multiple views of the table relationships, e.g. using an alias.

[magicksol13]

HI bdbits

I forgot to mention the cardinalities of the joined tables:

TblFactA(1..n)---(0..1)TbleDimB(1..n)---(1..1)TblDimC(0..1)---(1..n)TblFactA

And sorry as for the joins looked like this BEFORE the loop join:
           TblFactA
         /           
      /               
TbleDimB----TblDimC


AFTER:
         TblFactA
          /       \   
       /            \
TbleDimB----TblDimC

[Ravisha]

Hi All,

Thank you all for your valuable suggestions.

Basically I found an approach to mitigate the issue I was facing.

I've created shortcuts for the conformed dimension joining to two or more fact tables. I have used these shortcuts to join to their respective fact tables. I didn't worry to use the columns from these shortcuts in the business layer. Instead, I grabbed the query items from the original conformed dimension. Since a shortcut is a pointer to an original conformed dimension, different fact tables or start schemas relate to each other through these shortcuts.

The key thing to remember here is to place the shortcuts in a different folder. In doing so, it acts as a pointer. If the shortcuts are placed in the same parent level, it acts as an Alias shortcut.

So with this approach, I ended up with just one namespace in the business layer and I could be able to report across different star schemas. I've tested the SQL generation and works perfectly. I've successfully completed the whole model today. Thought of sharing with you guys.

Thanks