Modeling 1-n Relationships as 1-1 Relationships wth Relational Modeling

[Penny]

Currently reviewing a Best Modeling Guide that states "If a 1-n relationship is modeled as a 1-1 relationship, SQL traps cannot be avoided because the information provided by the metadata to IBM Cognos is insufficient.  Common problems - double counting for multiple-grain queries, multiple-fact queries are not automatically detected. "

Most of the documentation is particular to dimensional modeling not relational modeling.

We are building a relational model against an Oracle relational database, and are considering modeling 1-n as 1-1. If we do this, Cognos doesn't automatically summarize/aggregate which is what we want.  We work mainly with non-numeric data (student information) where we almost always want to see the detail.   

My question is....is this the best approach? Why or why not? 

Thank-you in advance for any insights.

[cognostechie]

The cardinality in Framework Manager 1..1 , 1..n, 0..1 and 0..n has nothing to do with the number of records in the tables in Oracle. If you have a Student Master table (Student Dimension) where you have only one record for every student then we have to tell Framework Manager to treat that table as a Dimension and not a Fact. The table that has transactional data (ex: data for every exam) should be treated as a Fact table by Framework Manager. In Framework Manager 1..1 and 0..1 means that table is a Dimension whereas 1..n and 0..n means that table should be considered as a Fact table. If you have a dimension table in Oracle which has 2 records for every student (Type 2 Dimension) , that table should still have the cardinality of 1..1 o 0..1 regardless of how many records are there for one student.

Ex: If you have a Student Dimension that has 2 records for every student then this should be the join.

Student Dimension                               Examination Fact

    1..1                                                      1..n

The 0..1 and 0..n work the same way and the only time they should be used is when you have to create outer joins.

In the above case if you create 1..1  <-->  1..1 relationship on both sides then FM will treat both the tables as Dimensions which would mess up the rollup of the data. Pl. also read about determinants and how they control the rollup of the data. I posted a lot of info on Determinants in this forum earlier.

[Penny]

Thank-you for your response, however...

We will only be creating relational packages for use with Query and Report Studios and Business Insight Advanced.  We are not using dimensions or facts, and do not want Cognos to auto aggregate or roll-up.   We will carefully control the join path of tables in our model for reporting.  Is it still necessary to model 1-n?

Still not clear, but thank-you.

[cognostechie]

Modelling 1..n or 1..1 has nothing to do with whether you want to use a relational or dimensional model. It is only to signify
which table is a dimension and which is a fact and whether or not the join is inner or outer.

[blom0344]

Thank-you for your response, however...

We will only be creating relational packages for use with Query and Report Studios and Business Insight Advanced.  We are not using dimensions or facts, and do not want Cognos to auto aggregate or roll-up .   We will carefully control the join path of tables in our model for reporting.  Is it still necessary to model 1-n?

Still not clear, but thank-you.

You mean that you have absolutely no measures defined (by usage type fact) and that every report will simply show the grain of the stored data? Pardon me, but is a premium BI tool not a very 'heavy' solution in that case? You might as well define views in the database and hook Excel by ODBC to view the rows

[cognostechie]

Even if there is no measure, the Fact table can still be presented as a Fact table. If there is no measure, Cognos will not roll up anything but at least they can do a distinct count of students which is something their business would need.

It is best for the OP to provide example of the fields in the tables.

[Penny]

Hi again

I appreciate the responses and realize that we are using a 'cadillac' where it may not be needed, but such as it is at our site currently.    We (my company not me) have been using Cognos 7 for years, and in 7 we can force the join path that Cognos takes.  My understanding is that Cognos 7 had a very basic query engine where more burden was on the report builder whereas 10 is more advanced and it is expected that the modeller manage the logic that will enforce the correct join strategy for the report writer.  (This since I posted my question yesterday).
I plan on doing some experimentation with models and the resulting SQL in the studios, and if I am not convinced I will come back to this discussion.  I will also research the query engine in Cognos 10 so that I have a better understanding of how it works. 

I am sure you can tell that I am new to Cognos...but not for long.  I have picked up quite a few helpful tips since joining this community.

Thank-you

[cognostechie]

Just so you know there are ways in Framework Manager to control the generation of SQL (to make sure the same SQL is generated every time by the report instead of letting it choose another path/another join to reach from Table A to Table C) Check the 'alias shortcuts' for joins and 'Determinants'.

It is definately a more advanced tool but it lets you manage the strategy for relationships and rollups. You can even make models which will not do double counting even if the granularities of data are different. It's very smart, we just have to tell the model how the data is organized so it knows how to roll it up properly.

[Penny]

Hi again...not sure what you mean but certainly something I want to review.   Where do I find this in Framework Manager?
Thank-you!

[cognostechie]

It's all in the user guide. I can't write so much here. It is best to go thru the training instead of learning and realizing bit-by-bit.

[MFGF]

Hi Penny,

Just to add a little clarification on the dimensional vs relational model topic...

When generating queries against any relational source, Cognos 10 makes some assumptions about the structure of the underlying tables. The most important one to be aware of is that it assumes the tables are star (or snowflake) schema tables in a data warehouse. There are two types of table found in these schemas - fact table and dimension table. When Cognostechie refers to dimensions and facts, this is what he is alluding to. The query engine will try to identify which tables are fact tables and which tables are dimension tables using the cardinalities defined on the relationships it uses in the query:

If a table (query subject) is at the many end of every relationship used, it will be identified as being a Fact table.
If a table (query subject) is at the one end of any relationship used, it will be identified as being a dimension table.

If a query is deemed to contain more than one Fact table, the query engine conjures up a stitch query, assuming it is reporting across multiple star schemas which may be at different levels of granularity (eg sales at the day level and targets at the month level). If you are really reporting across multiple fact tables, this is the correct thing to do, but if you are not, then it will produce very odd and misleading results.

The key to success in modelling with Framework Manager is to model your structures as star schemas - even if you are pointing at normalized transactional tables.

Cheers!

MF.

[Lynn]

Another thought to consider is whether you have any data that users might want to count up. Even non-numeric data is sometimes worth counting. For example, how many students registered for a certain class or achieved a certain grade might be valuable in your reporting.

If you look at your tables and identify those where a count of things might be useful, then you've identified your fact tables. Things like registrations/rosters, attendance, and scores all spring to mind as potential facts whereas student lists, teacher lists, score look up tables, curriculum requirements, etc lend themselves more as dimensions.

Modeling a data item that will allow an author to easily get a count of things by various dimensions can greatly simplify the authoring process.

Of course if you don't have any summary analysis now or on the horizon then you can disregard my comment  :)