Modeling on OLTP database

[mazelx]

Hi there,

I've been searching around the web for my problem and I've found some very interesting discussions out there about data modeling in cognos 8 framework manager.

We've migrated from Impromptu to C8 through the migration tool, which create a tons of packages migrated from catalogs. We've decided to build a clean "Company Framework", one framework answering almost all business needs (HR, Sales, etc...) through multiples packages.

The problem is : we're directly working on operational database (Movex), which means no star schema. My feeling here is that Cognos 8 will never deliver neither accurate result nor good performance when developing complex reports on non star schema model, right?

So what to do in such case, model a virtual star schema (and how to do that?) or ignore the cardinalities and joins all tables together in 1-1/0-1 just like in Impromptu Catalog ?

We're facing huge questions on that point and we really appreciate your help! Thanks in advance.

[MFGF]

Hi,

Modelling as virtual star schemas is definitely the preferred approach.  If you just fudge the relationship cardinalities, you are still likely to encounter blind spots, connection traps etc, and you will find it very difficult to report off measures held at different granularities (eg daily sales vs monthly targets).

To model as virtual star schemas, the best approach is to add a new namespace, then begin to create new model query subjects within this namespace - one for each "dimension" and one for each "fact".

Once this process is complete, the reporting objects included in the packages are based on these new structures.

It's worth reading the Best Practices document here too.

Regards,

MF.

[mazelx]

Thanks MGMF for your reply.

I've already read the best practices, this document is very interesting indeed.

If I am correct, I import the operational tables in the first layer, the "data foundation", and then rebuild totally the model, taking some fields from multiple tables and regroup them into new query subjects.

My question is where does I make my relationships? In the data foundation or in the second layer, the "consolidated layer"? Maybe both?

I think it will be really complicated to rebuild a star schema from a whole oltp db? Maybe I should build multiple framework, with limited number of tables?

Imagine I build up a query subject with two tables, let's say a the Query Subject "Orders" from "Orders headers" and "Orders lines" (headers/lines are very common organization is such oltp db). When I create a report using only one field from this table, for example total price from order header, it always generate a sql with the two tables joined. In a simple query that's not a big point, but when we do complex queries, multiple facts tables, multiple grain, etc... the sql consists in joins between many many tables, and often not even useful in that case, leading in huge performance issues...

[MFGF]

Thanks MGMF for your reply.

I've already read the best practices, this document is very interesting indeed.

If I am correct, I import the operational tables in the first layer, the "data foundation", and then rebuild totally the model, taking some fields from multiple tables and regroup them into new query subjects.

My question is where does I make my relationships? In the data foundation or in the second layer, the "consolidated layer"? Maybe both?

I think it will be really complicated to rebuild a star schema from a whole oltp db? Maybe I should build multiple framework, with limited number of tables?

Imagine I build up a query subject with two tables, let's say a the Query Subject "Orders" from "Orders headers" and "Orders lines" (headers/lines are very common organization is such oltp db). When I create a report using only one field from this table, for example total price from order header, it always generate a sql with the two tables joined. In a simple query that's not a big point, but when we do complex queries, multiple facts tables, multiple grain, etc... the sql consists in joins between many many tables, and often not even useful in that case, leading in huge performance issues...

MGMF - is he my evil twin brother? :)

I'd try to keep your relationships in the foundation layer wherever possible.  I'd recommend an overall approach whereby you only model the data required to answer the business issues, rather than modelling the whole database on the assumption "if it's there, people can report on it".  This will save you time and effort, and avoid confusion later on in the packages.

The issue you describe with multiple tables (Order Header and Order Details) will only be relevant if you merge them in a new query subject then add a relationship to that query subject.  If you don't add a relationship, the query will be minimized to a single table if you bring in a single item.

Regards,

MF.

[Lynn]

There was another thread recently that might be of interest if you didn't see it.

http://www.cognoise.com/community/index.php/topic,11191.0.html

[mazelx]

Thanks Lynn, Thanks MFGF (sorry for the misspelling, seems I mixed your name and the music band MGMT lol)

Concerning the minimized SQL and the "virtual star schema", I think there's still some confusion. To make a virtual star schema, I'll need to do some relationships on the data foundation layer, merge some of them in single query subjects and then make new relationships between the merged items?

Let's take my example (orders headers and lines) :
- in the data foundation layer, I join these two tables (1-1/1-n).
- I make a query subject Orders referring to fields from the header and fields from lines

Ok so here relationships only exist in the data foundation layer, am I correct?

Now, I need to make a relationship between this "merged" query subject to another query subject, for example "Contracts" query subject (contract headers and contract lines). The relationship between them will me made in the second layer (the "consolidated" layer), right?


Thank's for the link to the discussion, this is helpful. I've also found some very interesting articles on some best practices for modeling with FM on that website http://www.ironsidegroup.com/tag/framework-manager/