Model design and presentation
You should always strive to present the metadata to users in a clear and concise manner, logically grouped and easy to read and understand.
Sometimes you might need to consolidate multiple tables into one view for a logical grouping. For example, if you have three tables that represent product data, you can present the data as one logical business table that simplifies query authoring. This new table transparently pulls the data from the underlying tables.
You might also want to implement calculations, filters, or prompts to meet the business query requirements. However, when doing this, consider precomputing columns in the source data versus continually reevaluating the logic in the model.
However, precomputing columns in the source data might not always be possible. For example, in some cases the source system stewards might not allow changes to the system. As a result, the Cognos® Analytics application must continually compute expressions that you define in the model. Similarly, an application team might want to be able to rapidly adapt to changes in business requirements, and cannot wait for the warehouse to change. This can be a balancing act, however, in each situation performance should be considered first.
Framework Manager and data modules have a slightly different approach to the model design.
In Framework Manager, it's recommended to model multiple layers that include:
- The database layer for the imported tables.
This layer is considered the metadata cache.
- The business layer for model enhancements and presentation.
This layer also acts as an insulation layer for reports to protect them from changes to the underlying database layer.
- The presentation layer that groups facts and related dimensions together by using star schema
The Star Schema Grouping feature shows users which dimensions are shared across facts because they see the same dimension name in multiple star schema groupings. This helps the users to understand the scope of each dimension and its related fact tables. By understanding this one, simple rule, users who want to cross multiple fact tables in a query know that they must include a column from at least one shared dimension. For more information, see Creating star schema groups.
The following screen capture shows the different modeling layers in Framework Manager:
And the following screen capture shows a presentation view with star schema groupings. Shared dimensions between them can be identified based on the same name. For example, Time and Products appear in both Sales Target and Sales star schema groupings.
You might need to create and present more than one presentation layer. For example, one layer can be focused on business users who want to create their own dashboards, and another layer on professional report authors who need to address more complex requirements.
In data modules, the metadata cache is managed for you, bypassing the need for the database layer. You can focus on items to consolidate and the business value that you want to add for users. Although Star Schema Grouping is not a feature in data modules, users can be given read-only permissions for the data module to view its scope and examine the relationships between dimensions and facts. Unlike a Framework Manager model, a data module is available in the Cognos Analytics portal for all users with access to see.
The following screen capture shows an example of a data module in the modeling user interface:
In both modeling tools, you can create reusable objects and layers of abstraction. For example, you can create calculations or filters that can be reused in multiple locations, or copies of tables to be used in different business contexts, such as with role playing dimensions. You can develop objects in one model, and link to them from other models for reuse. This way of working enables a multi-modeler paradigm or simply reduces maintenance. For example, you might create and maintain all of your common dimensions in one model, and reference them in other models where they are joined to applicable fact tables. Changes that are made to the dimensions in the source model are propagated to the models that reference them.
Cognos Analytics supports multiple data sources in its query tools, such as Reporting or Dashboarding. In Framework Manager, you can create smaller, more manageable models rather than one large model that contains everything. Then, you can publish multiple, smaller packages that represent different aspects of the business, and visualize them in the reports or dashboards.
You can use multiple data modules as data sources in Dasboarding. However, in Reporting only one data module can be used as a source. If you need to use multiple data modules in Reporting, add these modules to a "parent" data module, and then use this module as a source.
The smaller your models are, the more manageable they are. Unless you need to query across multiple fact tables, you don't need to include all of those tables in one model. You can separate different aspects of the business into different models and reuse common dimension tables in each of them. And while you can layer model upon model, keep in mind that the more layers there are, the more difficult it is to manage, maintain, and troubleshoot the metadata.