FM Model

[ghostraider]

Hi All, i have a model which is based on a Relational Database. It has close to 250 tables and all the joins are made based on the E-R diagram. The same model worked well in Impromptu, however in Cognos 8.2 we are facing a lot of problems. The problems are mainly with 1..n and 0..n joins. Because the n side tables are treated as facts i am having problems when more than two items are queried from the n side tables along with a common item from the 1 side table. I do not have any experience in Impromptu Catalog modeling and i am not sure if there are stitched query concepts in Impromptu? Could someone tell me how Impromptu handles outer joins and if Cognos 8 has a different way of generating SQL for these kind of 1..n and 0..n joins? Please let me know the major differences in modeling from Cognos 8 and Impromptu.

Thank you for your time.

[blom0344]

Not at all familiar with impromptu, but Cognos 8 will expect you to use a second layer 'above' the database layer to remodel into 'stars' . Your relational source may not be the best starting point, but to get consistent result and optimized queries you have little choice.
Each fact would end up as the base of the star. Cognos uses stitch queries to resolve the multifact occasions

[ghostraider]

Hi blom0344, thank you very much for the response. This is a migrated catalog and there are joins everywhere. For example, i have 4 tables relating to the business idea Invoice. 1) Invoice 2) Invoice Status 3) Invoice Funds 4) Invoice Type. All these tables are joined together just as they are in the ER diagram. Could you please let me know if i should merge these subjects to form dimension and fact? I have lot of tables like this with a common business idea but since they come from relational database they are divided into multiple tables and joined on 1..n and 0..n methods. The outer joins are hampering performance and there are stitched queries in many of my reports. Please let me know how to go about this problem.

Thanks a lot for your time.

[blom0344]

The concept within Cognos8 is to have a transformation layer based on the database layer where you re-model into stars.
Among others issues, you need to take care of snowflaking (creating model query subjects that merge dimensional data into 1 object) and identify which objects (that can be merged tables or otherwise) that will serve as facts.
Just following the E-R diagram of the source is not good enough.

If your source is an OLTP system (as opposed to a DWH or datamart) then there is a pretty good chance that you CANNOT do without outer-joins.
Stitch queries are not by nature a problem. With proper modelling they are the way to enable multifact queries..

[ghostraider]

Blom0344, thank you for the explanation. If i am going to remodel into stars using transformation layer do i have to create a time dimension? I mean we will be reporting off the OLTP system, so if i am going to create stars do we need to have time dimension and if yes do we have to create it on our database? Please let me know.

Thanks!!

[blom0344]

Let me put it this way, I have never worked without at least 1 date/time table for adding dimensional data.
And, yes you will need this (and possibly aliasses - shortcuts - ) to boot in your database / FM.

typical columns:

Sequence (identity)
Date
Week (ISO or other)
Month (name)
Monthnumber (1-12)
Quarter (1-4)
Year
Day_of_year (1-366)
Day_of_week (1-7)
Day (Monday ----- Sunday)
Indication working day (1 / 0)
Julian date (integer for easy calculations)

etc..

[ghostraider]

Blm0344, Thank you for the explanation. So, if i am going to create star schemas in the  Transformation Layer in FM, my dimensions like Invoice or Customer will not have surrogate keys. How do i link them to the fact tables that i try to mimic? Should i just use the natural keys? And similarly for time dimension do i just join on the dates? Please let me know. Sorry, if my question is confusing.

Thanks!!

[blom0344]

I assume that your customer and invoice numbers are integers, which will give the same performance as surrogate keys. Anyway, you are directly working on an OLTP system, so you need to work with the data as is.
Dates can be a hassle depending on your database. If the factdates are storing a time component, then you need to make adjustments (like joining over an object that has lost the time part)
Some databases (Oracle) tend to store time-components in seperate fields, making it quite easy to apply joins through dates. Obviously, using dates is less efficient than integer surrogate keys.