Modeling in Framework Manager

[cognosguru]

Dear Cognoids,

Due to Framework Manager's "interesting" makeup I find it difficult to model transactionally in it.  Has anyone had success doing it?  Everything I do, FM tries to force me to model dimensionally and then I run into bugs with stich queries for instance. 

Also, how do you force FM not to try to join based on indexes and primary keys but instead listen to you and the join strategy you create?  It seems to keep the indexes and primary keys on file (I assume in one of it's XML files) and if I don't use them it creates a cross join.

Any best practices would be most appreciated.

Thanks,

[MFGF]

Hi cognosguru,

Hmm - thorny topic this! :-) You're right about Framework Manager forcing you to model dimensionally.  It makes inherent assumptions about what is a Fact Table and what is a Dimension Table based on the cardinality of the relationships defined.  Within the context of a query, any Query Subject that is only at the many end of a relationship will be treated as a Fact Table, and any that are at the one end of a relationship will be treated as Dimension Tables.  Unless you model dimensionally, you could inadvertently end up with a query containing two "Fact Tables", which would result in a stitch query being generated (full outer join using first-non-null).

Stitch queries are not bugs - they are the right thing to do when querying two fact tables which share common dimensions, possibly linking at different levels.  The art of modelling in Framework Manager is to rearrange the data into virtual fact and dimension query subjects where stitch queries would never be used within a package (ie all your measures are within the fact, and all your descriptive data is within conformed dimensions).  You will need to do this by merging query subjects together, then rather than leaving the merged query subjects as Model Query Subjects, convert them back to data query subjects with their own self-contained SQL.  For the dimensional query subjects, you will then need to set up dimensionality to specify the hierarchical levels and their attributes within the dimension.

Best practice is to define an import view where you model things transactionally (exactly as in the underlying database).  Then create a "star schema" view in a different namespace where you merge query subjects from the import view into new Fact and Dimension data query subjects, and set up the relationships between these, then define dimensionality for the dimensions, then create star schema groupings.  Lastly create a business view in another namespace, where you select relevant parts from a star schema and put them into model query subjects as per the requirements of the end users.  Do not create relationships at this level - FM will use the relationships from the star schema level.  Publish your package(s) using query subjects from this view.

If you don't do this, you will have problems, one of which you've already encountered in that the underlying key definitions are stored in model.xml when the import is done from the database.  You could edit this if you wanted to, but probably best not to! :-)

Best regards,

MF.

[cognosguru]

Thanks MF,

To clarify I know stich queries are not bugs, but we have found at least 2 bugs with them (on DB2 for certain).

Why is it better to convert the model query subjects into data query subjects? 

Thanks,

 

[MFGF]

Hi cognosguru,

If you don't convert your Model Query Subjects into Data Query Subjects, ReportNet will resolve how to code the SQL for report queries by looking back at the underlying Data Query Subjects upon which the Model Query Subjects are based.  This means your queries will still use the SQL and relationships defined in your import view, and you're no better off.

Best,

MF.

[cognosguru]

MF,

I thought that if I define the relationship at the Model Query subject level, ReportNet will use that join structure not the underlying one.  Am I mistaken?

Thanks,

[MFGF]

Hi cognosguru,

In simplistic terms, that's correct, but there's a bit more to it than that. If each Model query subject is built over one single Data query subject, and you specify the relationships between the Model query subjects, then what you are saying is true.  However, your Model query subjects will be based on multiple underlying Data query subjects.  In order to retrieve the data you have asked for in any Model query subject, ReportNet will have to run the queries in the underlying Data query subjects, using the relationships you have defined between those Data query subjects to figure out how to build the data set to return into the Model query subject.  Once this has been done, the relationships between the model query subjects are used in queries that span more than one model query subject.  Net result of all this is that the "merging" you thought you'd done to prevent unwanted stitch queries hasn't done so, as your query still ends up making use of the relationships in your Import View.

If you convert the Model query subjects into Data query subjects, this changes things.  Since each merged query subject now contains a SQL query, there's no need to go back to the Import View to find out how to retrieve data, so only the relationships you define between these merged Data query subjects are used.

Regards,

MF.

[cognosguru]

MF,

Thank you very much for your detailed responses.  I will try your suggestion.  Unfortunately, it is very difficult to find someone who really understands what is going on with Framework Manager thus I appreciate it all of your help. 

Cognoise rocks!

[COGNOiSe administrator]

Thanks MFGF - the COGNOiSe team appreciates your insight and the assistance you provide us all (especially on ReportNet!)

Thanks for the feedback too, cognosguru!

[MFGF]

You're welcome!  Happy to be of help where I can. :-)

[cognosfreelancer]

Dear Cognosguru

I had a similar problem with FM ignoring my joins and going in for cross joins when I did not use all indexes that made up the composite key in the underlying table.

My workaround was to import database tables one at a time instead of importing all of them as one normally does. This approach worked fine for me.

Note we do not have to do this all the time only in cases where underlying database tables use composite primary keys.

HTH

Regards
NKT

[cognosguru]

Thanks Freelancer,

Another method that seemed to work was to choose the last option when importing tables (matching column names only).

Can't wait till Cognos 8.

[Darek]

What do you think about the Cognos FM Best Practices document?

http://support.cognos.com/supported/docs/en/reportnet/1.1/bestpractice_modeling_fm.pdf

[cognosguru]

Hi Darek,

I have read through it and it helps clarify some issues.  In my opinion, it does not however go into the real crux of it.  I was able to pick up a number of good things but it left me with more questions then answers.