The most successful business intelligence applications are designed with well planned models. This includes an analysis of how the data in the models will be used by report and analysis users. Consider the following concepts when designing PowerCube models for use in IBM Cognos 8.
Dimensions are conformed when the data values that come from the original data sources use the same business keys, or source data, that is used in other packages or models in your IBM Cognos 8 environment. Conformed dimensions allow your users to combine or cross data sources successfully when their business needs require that they do so.
For example, consider that your goal is to drill through to product line information between two reports. The first report is based on a PowerCube package, and the second report is based on a relational package. Each product line in the relational package should include a business key, or unique identifier. In the PowerCube model, the Source value for each category in the Product Line dimension should reference the same data value as the business key in the relational package.
When the same business keys and source values are used throughout your IBM Cognos 8 application data, end user success with reporting and analysis will increase substantially.
Conformed dimensions are also key in successful data analysis using multiple PowerCubes. When two cubes are to be used together, as with drill through, ensure that the dimensional structure and the category source values are the same in each cube model. Changes in the structure of a dimension in one cube, for example, by adding another level, will impact both the reports and drill-through applications that use the two cubes.
In IBM® Cognos® 8 Business Intelligence Reporting, PowerCube categories, or members, have business keys that can be used for advanced reporting or in drill-through scenarios. During Transformer model design, you can determine the IBM Cognos 8 business keys by setting the level source values.
Tip: Report Studio report authors can determine PowerCube business keys using a calculation such as
roleValue('business key',[mycube].[Product Dimension].[All
Products].[Product
Line])In the IBM Cognos 8 Web studios, the Member Unique Name (MUN) is the unique identifier for locating the category or member in the data source. The MUN is much like the business key in a table.
IBM Cognos 8 metadata uses the Transformer model category codes when defining the MUN of a PowerCube category or member.
Member Unique Names are used
as data item references for categories or members in any IBM Cognos 8 report specification
as the value passed in PowerCube to PowerCube drill through in any IBM Cognos 8 report
as identifiers for categories or members used in filters, expressions, parameters, or calculations in IBM Cognos 8
to return categories or members to IBM Cognos 8 applications
Any time an IBM Cognos 8 application requests the category or member, the MUN ensures that the unique category or member is returned.
Tip: You can view the Member Unique Name for a category or member in Report Studio. In Report Studio, open a PowerCube package, select a category, and view the category properties.
Each time a PowerCube category code changes, the MUN reflects the change. When categories or members are directly referenced in expressions, filters, or reports, and the MUN changes, the category or member is no longer found. This is because the original MUN is contained in the report specification.
Member Unique Names can change for different reasons:
Changes in the hierarchy and level structures can result in changes to MUNs.
Relative time categories may change, for example, when the current quarter moves from one to the next.
If a source value changes, the category code used in the MUN also changes, unless the category code is specifically set to use a unique data item in the model design.
The production environment may have more categories or members than the test environment.
The category or member may no longer exist in the data source.
To avoid these problems, we recommend the following practices:
Use unique codes and keys within a dimension for the category or member keys.
Ensure that your Transformer model source values have unique values throughout the levels of each dimension. This ensures that the model category codes, and therefore the MUNs, are more stable.
Use unique conformed source values for similar dimensions between the target and source environments when enabling drill through.
Ensure that the business keys and dimension metadata structure are the same between the production and test environments.
If the data source is an IBM Cognos 8 package or report, do not change the business keys in after going into production.
Resolve the non-unique keys within a dimension in the data source.
Tildes are not recommended in the category codes, as they can produce unstable MUN values.
If you have tildes within your category codes, do not use the Clean House feature.
Using the Clean House feature will most likely change the category codes.
We strongly recommend that you keep a backup of your .mdl file and revert to the backup .mdl model file if the current model file becomes corrupt and requires a Clean House action.
To avoid ambiguity problems in your reports, we recommend that you design your models so that no two categories in a level represent identically-named distinct categories, such as cities with the same name in two or more regions.
When you create models in Transformer, multiple non-unique categories imported into the same level are made unique by appending ~### to the duplicate codes, where ### represents an ascending numeric sequence.
The mappings between these assigned codes and their associated source values are stored in the Transformer model for use in subsequent cube build operations. However, errors may arise if the model is not saved after a cube refresh, or if the processing order changes for any reason.
For example, IBM Cognos 8 report specifications reference categories or members of an OLAP package, including PowerCubes, using a unique identifier referred to as Member Unique Name (MUN). This MUN is generated for each category in a PowerCube and is based on the fully-qualified path of category codes in the dimension, according to where the category exists within the dimension. If the category codes change for any reason, the report specification can no longer locate the original MUN. The report author must modify the report to point to the updated category or member.
If your source data contains columns that populate levels that are not unique, an error message warns you of the potential problem when you attempt to generate categories. However, this prompting occurs only if the data source contains all the columns required for the levels in question. If categories for some levels have values derived from other transactional data sources, uniqueness conflicts may arise but remain undetected. Also, if you select an optimization setting that maximizes query speed, Transformer does not check your model for uniqueness conflicts. For this reason, we recommend that you save your model after every cube build.
To ensure successful business intelligence applications using IBM Cognos 8, we strongly recommend that the data sources that feed your Transformer models have unique business keys or source values through the levels of each structural dimension that you model. In addition, source values that conform with the business keys in other applications used in IBM Cognos 8 will have the best success rates when used with drill-through applications and other business intelligence applications.
You can use Transformer calculated columns, Framework Manager, or a query tool to render the level values unique, or simply redefine the data in your source so that it does provide unique values. Correlate the settings in the appropriate property sheets of your model so that your data is correctly mapped in the relevant IBM Cognos 8 components.
Ensure that the data sources that feed your Transformer models account for this uniqueness. Provide conformed values to any applications that you want to use with IBM Cognos 8 Business Intelligence products.