Framework Question

[UcfMike]

In FM, I have my first layer, which is I believe what we call the transactional layer. When I'm making the second, business presentation layer, should I creat shortcust of the query subjects, or copies of the query subjects?

 

[YamaCog56]

There is certainly more than one technique that can be used to set up your FrameWork Manager model, similar to the Impromptu catalog, however this is how I like to do it:

The first layer (namespace) I call the Database Layer (or View), and this contains the raw tables and columns as they appear in the back-end database. Normally I create the relationships in this layer also.

The second layer I call the Model View, and this should be query subjects created from the objects in the Database View (essentially all Model query subjects and query items). In this layer I rename the columns (query items) in the query subjects to their business names (what a report author will see in Report or Query Studio). I will also add common calculations, string concatenations, etc. in this layer

Typically the final layer (or namespace) will be the Presentation or Business Layer, and this layer will contain the shortcuts to the Model View query subjects. On rare occasions I have had to create an interim layer before the final layer in order to create query subjects that consist of query items from two database query subjects and then create relationships in this layer.

I hope this information helps...

 

[UcfMike]

If I try to report off the database layer I end up with full outer joins and Coalesce in my SQL statements. Should I expect to resolve these problems in my Model view layer?

 

[blom0344]

Yes, you should. Bear in mind that THE Cognos FM dogma is that the true model is a starschema. Literally, everything can be traced back to this principle.
The coalesce/full outer join effect is Cognos' reaction on how to solve multi-fact queries. However, this is meant to be employed when you build a query over 2 stars that have dimensions in common.


Ties Blom

 

[CognosProfessional]

I would say the answer to your first question is "Shortcuts". The DB Layer should be the only one with the joins. This way, you re-use the joins in all your queries. You don't have to copy them unless it is a special situation.

CP

 

[blom0344]

In most cases I expect that - unless the DB layer is already a perfect starschema - you will need to create new model query subjects and add joins in the transformation layer. So, the special situation should be quite common then?

Ties Blom

 

[UcfMike]

So then we're looking at a database/import layer, then a transformation layer, then a business/presentation layer.

When Cognos describes the modeling process they talk abot a database/import layer and then the business/presentation layer. Of course Cognos seems to always assume that your data is in a data warehouse.

I wish there was a framework manager, data warehouse modeling book.

 

[blom0344]

Such a book would be VERY helpful. I think the main reason for not providing such a valuable asset is the fact that it would interfere with the rather profitable cognos courses

Ties Blom

 

[UcfMike]

That, and the profitable consulting.

If I had that expertise, would I make more from consulting with other companies or writing a book.

 

[blom0344]

C8 consulting = good money, in my country C8 experience is much in demand, but all experienced consultants are either unaffordable or just too busy.

Ties Blom

 

[misscognos]

ensure that you use conformed dimensions. should help get rid of the outer joins. though i am not a modeling guru.