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 re-evaluating the logic in the model.

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 grouping.

  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 Create Star Schema Grouping is not a feature in data modules, as of Cognos Analytics 11.2.3, modelers can use shortcuts to dimension tables and create relationships between the shortcuts and the fact tables (there is no need to create a shortcut for the fact tables) to manually create a star schema grouping. The fact tables and shortcuts can be placed in folders that represent the business area such as Sales and Sales Target, as seen in the following screen capture.

In this way, star schema presentations are possible for data modules. The guidelines for authors are the same as when using star schema groupings for Framework Manager packages. For more information on shortcuts in data modules, see Custom tables.

In addition to supporting multi-fact queries, the ability to create the star schema groupings in Framework Manager and Data Modules also allows you to remove join ambiguity and resolve the issue of multiple join paths. For example, in the Sales namespace or folder, if a query contains only Month from the Time dimension and Product from the Products dimension, those tables' shortcuts would be joined through the Sales fact table in the same folder. The query, in this case, would answer the question "What products did I sell this month?" By using the same items from the Sales Target namespace or folder, the query would answer the question "What products should I have sold this month?" because the Time and Product dimension table shortcuts would be joined through the Sales Target fact table. If you did not address the join path ambiguity using star schema groupings with shortcuts, query planning would choose the alphabetically first fact table where multiple join paths exist. For example, if you had the Time and Product tables joined directly to the Sales and Sales Target fact tables, as seen in the figure below, and then chose Month and Product from the dimension tables, they would be joined through the Sales fact table because it is alphabetically before Sales Target.

In earlier versions of Cognos Analytics, 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.

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 or alias shortcuts that can 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. Likewise, you can also use multiple data modules as data sources in Dashboarding and Reporting.

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.