Modeling layers

Proven Practice

This chapter covers ideas for best practices in creating your Framework Manager model.

The topics covered in this chapter include:

• Using namespaces to divide the model into layers
• Using folders within namespaces to provide logical grouping of data
• Renaming columns within tables, and use of shortcuts to allow the report designer to know where the data originates from
• Using Model Advisor to diagnose potential design problems

By the end of this chapter, you will have an understanding of the proven practices to use while designing a new data model in the Framework Manager.

Framework Manager is the modeling design tool used to model the metadata of your database to allow report authors to produce reports using the available reporting studios on the IBM Cognos BI reporting server.

Modeling layers

One of the basic principles of a good model design is to divide the model into a series of layers, each layer having a specific function within the model and a related set of modeling activities.

Originally IBM Cognos recommended the use of two layers, a data layer and a modeling layer. This was later refined by the inclusion of a Presentation layer, and a separate Dimensional layer if theDimensional Modeled Relational (DMR) data is also used in the model.

The layers are built upon one another, with the lowest level being the data layer, the modeling layer as the middle layer, and the top layer being the presentation layer. The dimensional layer can be used to replace the presentation layer, or can be used in addition to the presentation layer.

Data layer

The data layer, also called the import layer, or the physical layer, contains the data source query subjects. This is directly based on the underlying database objects.

In order to retrieve the table information, you should use an unmodified query in SQL, similar to the following:

Select * from Table

This is the SQL command that the import process will generate for you. You can also modify this SQL command if required by adding filters or calculations. You will usually add joins, cardinality, and determinants at this level. For more information on joins, cardinality, and determinants see Chapter 4,Modeling Relational Data.

TIP

If you do not add any determinants, then the Framework Manager will add them automatically during the import process using the primary keys of the database table being imported.

Although there is nothing to prevent you from adding joins, cardinality, and determinants in any layer of the model, you should only add them in one layer of the model.

Some people recommend adding them in the data layer while others in the logical layer, so it's a matter of personal choice as to where these should be added.

Logical layer

The logical layer, sometimes also referred to as the modeling layer or the business layer, is where most of the modeling is likely to occur. It is used to provide business context and an understanding of the data objects in the data layer. Some of the required tasks include, but are not limited to the following:

• Combining elements from multiple tables to make more logical structures; for example, if you have an invoice header table and an invoice line table, you may want to combine elements from both tables into a single query subject called invoices.
• Renaming elements; for example, if you have a column called "City" in many different tables, it will be better to rename the column so that it is related to the corresponding data.
• Assigning standard names and business names to database columns; for example, you may have a database column ORDDATE, which is better to be renamed as Order Date so that it will be understood more easily by the report authors.
• Making use of folders to group similar items.
• Arranging query subject items in a more user-friendly manner.

• TIP

• Sometimes it can be useful to arrange the contents of folders alphabetically, and this can be achieved by using the Reorder command for this purpose.
• Right-click on the namespace or on the folder to be reordered, and from the pop-up menu select Reorder.
• Adding calculations, filters, and prompts.
• Assigning output formats and usages to the reporting elements.

• TIP

• If you create a small folder containing simple items with formats, then they can easily be copied to target items by multiselecting and dragging the item's format through the list.

Presentation layer

The presentation layer is what the report author sees when the published package is used to create reports.

This layer usually only includes shortcuts to the existing items in the logical layer, plus organization items such as folders and namespaces.

For organizing major groupings, you can either use folders or namespaces. However, namespace names must be unique, and items within a namespace must likewise be unique.

If you use folders for your major groupings, then you cannot have similarly named shortcuts in more than one folder.

It is likely that many of the commonly used query subjects will occur in more than one folder, and this can be easily achieved by creating shortcuts to the items and renaming them within the individual folders used to group the subjects.

Dimensional layer

The dimensional layer is required only for models which include Dimensionally Modeled Relation data (DMR) rather than only the relational data.

Specifically, this is for creating regular and measure dimensional query subjects. Much like the presentation layer, this layer is also built upon the logical layer.

This layer is usually created from items in the modeling layer, and can be used to replace the presentation layer if this layer is not required by your report authors.

TIP

If there is a dimensional layer in the model, it is usual to include the presentation layer since report authors can then have objects from both layers, which can be combined in the reports.