Why go with 3 layers in Framework Manager Model?

[alliejoy]

I've always followed the 3 layer approach when designing a FM model. Physical, Logical, and Business layers. This is pretty standard however there are several developers around me that are questioning the need for 3 layers when they can do it in 2.

I'm looking for benefits for using the 3 layer approach because just saying that it is 'best practice' and 'what Cognos says to do' is not a good enough argument for convincing the group to follow this approach.

[MFGF]

Hi,

If you are modelling a normalized source, then three layers are really the minimum you can get away with.  The foundation layer will reflect the underlying tables and joins in the database(s), the logical layer will typically be used for resolving traps and conforming to structures providing accurate, consistent query generation at runtime, plus providing isolation within the model from underlying database changes, and the business layer is typically for end-user presentation.

It is of course possible to amalgamate the foundation and logical layers into one namespace, but to be honest this has two drawbacks - it makes the model horribly complex to maintain, and it takes away the isolation from database changes.

In reality I often use a four-layer approach - Physical, Logical, Dimensional and Business.  The Dimensional layer is used for DMR objects where OLAP-type functionality is required.

So, you can add another argument to pass to your group - "Because MF says so!"  :D

MF.

[scull]

So alliejoy, I guess you have a bastard simmilar to me in your group :D

Fasten your seatbelts!

If I have some freedom in the DB, I would always try to get the data into the shape of business layer and store it that way in the database. Afterwards have only one layer in the FM (or no layers to that matter, just direct links to the tables; no renaming of fields, no folders in FM; NOTHING; just straight off views of the DB plus joins if there are any).

The only reason for doing this is performance. No matter how good cognos optimizer supposingly is, it does sometimes mess up when going through 3 layers (even 2!).

This is especially visible if you create a report that selects 2 columns out of 10 in a table. The underlying query might be selecting all columns and then doing a subselect on the 2 required ones. Obviously this drives value of DB indexes (read performance) to nothing.

So... If you are a controll freak and want the cognos query optimizer to interfere as little as possible, 1 layer FM model rules.

Downsides? Sure there are. Redundancy, storage space overkill, might be more clumsy to maintain, crazy dataload procedures...  I guess that other folks that are into working by the book can provide you with all of them. But if you care about performance and end user experience you just do what ever works :) And this way you have all the power of tweaking you can get!

At this point in time it is worth saying, that I don't dismiss what MF said. Or was it too late already?...

;)

M.

[blom0344]

Hi Scull, these are pretty hefty thoughts. In many cases a modeller will have limited or no control over the structure of the database Cognos is up against. If he can both determine the underlying structure and the modelling it MAY be possible to use a single layer, but using a well designed 2 layer model (top level in model query subjects) will give plenty of advantages (like being able to make the model RDBMS independable and use Cognos functions everywhere)
Cognos has gone through some trouble to provide minimized SQL , but it will only work if you adhere to strict best practice rules. (And I think you are confusing indexes with IO use in your example)

I have inherited a few models that are based on a 1 layer model. These are a mayor pain to work with during rework with every new release (adapting the model to the changed DB) My advice: Do not go for a single layer!!

[CognosPaul]

This is especially visible if you create a report that selects 2 columns out of 10 in a table. The underlying query might be selecting all columns and then doing a subselect on the 2 required ones. Obviously this drives value of DB indexes (read performance) to nothing.

This behavior tends to indicate a problem with the model. The most common cause of this is due to having the database queries as:

Code Select Expand

select table.field1, table.field2, table.field3 from schema.table
Instead of:

Code Select Expand

select * from schema.table

On extremely large databases where using the index is absolutely essential you can use this to force obedience to the index.

The next likely cause is joins between the business layer. When you have all of the fields grouped into individual queries, you don't need to make another join between them. You can include fields any of those queries and Cognos will generate the SQL based on the underlying joins.

[tupac_rd]

I always use 3 layers, the third layer being only shortcuts, and I have never had issues with Cognos generating the wrong SQL, when it is a star schema.. The trouble I had is when trying to go across 2 fact tables and trying to get the coalesece statement to work correctly.