Dimensional Modeling of Relational Data Sources
Dimensional modeling of relational data sources is a capability made available by Cognos 8
Framework Manager. ReportNet 1.x provided some dimensional capabilities to enable
multiple-fact querying and prevented double-counting, and Cognos 8 introduces features designed explicitly for dimensional modeling. It is now possible to model dimensions with hierarchies and levels and to have facts with multiple measures. You can then relate the dimensions to the measures by setting scope in the model. You must dimensionally model a relational data source when you want to do one or more of the following:
• use Analysis Studio
• enable drill functionality in reports
• access member functions in the report authoring tools
We recommend that you follow this workflow when you dimensionally model relational data
sources:
❑ Import the metadata.
❑ Verify the imported metadata (p. 7).
❑ Customize the metadata.
❑ Simplify the model using dimensional concepts (p. 11).
❑ Resolve ambiguous relationships (p. 12).
❑ Define dimensions (p. 17) and determinants (p. 16).
❑ Organize the model by creating business views.
❑ Create star schema groups (p. 23).
❑ Apply security.
❑ Create packages and publish metadata.
For information about the topics not covered here, see "Designing a Project", "Preparing
Relational Metadata for Use in Reports", and "Making Metadata Available to Report Authors"
in the Framework Manager User Guide.
All examples in this document use the database view of the gosales_goretailers normalized model or the import view of the go_data_warehouse model, which are included with the Cognos8 samples.
Verifying Imported Metadata
After importing metadata, you must check the imported metadata in these areas:
• relationships and cardinality
• determinants
• the Usage property for query items
• the Regular Aggregate property for query items
Relationships and cardinality are discussed here. For information on the Usage and Regular
Aggregate properties, see "Modifying the Properties of Query Items" in the Framework Manager User Guide.
Cardinality is combined with dimensions to control how queries are generated so that you can
• prevent double-counting
• automatically resolve loop joins
• enable cross-fact querying for reporting and analysis
You can create model dimensions and data source dimensions. Model dimensions are built on a
foundation of query subjects that use determinants and relationships with cardinality. Data source dimensions contain their own SQL and use hierarchy and level information as well as
relationships with cardinality to define query granularity.
Cardinality drives query behavior by allowing rules to be applied regarding the granularity of data that is returned by an individual object and the consequence of joins between objects. The
cardinality specified in the relationship between query subjects or dimensions determines how and when Cognos 8 generates stitched queries. Stitched queries are needed for multiple-fact querying across conformed dimensions and across different levels of granularity.
Detecting Cardinality from the Data Source
When importing from a relational data source, cardinality is detected based on a set of rules that
you specify. The available options are
• use primary and foreign keys
• use matching query item names that represent uniquely indexed columns
• use matching query item names
The most common situation is to use primary and foreign keys as well as matching query items
that represent uniquely indexed columns. The information is used to set some properties of query items as well as to generate relationships.
To view the index and key information that was imported, right-click a query subject or regular
dimension and click Edit Definition. For a query subject, you can change the information in the
Determinants tab.
Note: All regular dimensions begin as query subjects. If you converted a query subject to a regular dimension, note that determinant information for the query subject is leveraged as a starting point to define the levels of a single hierarchy. We recommend that you review the levels and keys created in the hierarchy of the dimension.
Optional relationships, full outer joins, and many-to-many relationships can be imported from
your data source. Framework Manager will execute them as queries.
Reflexive relationships can be imported from your data source and appear in the model but
Framework Manager does not execute them as queries. Although you can view the metadata that defines the reflexive relationship, you cannot edit a reflexive relationship in Framework Manager.
Cardinality in Generated Queries
Framework Manager supports both minimum-maximum cardinality and mandatory-optional
cardinality.
0:1 - 0 is the minimum cardinality, 1 is the maximum cardinality
1:n - 1 is the minimum cardinality, n is the maximum cardinality
A relationship with cardinality 1-n can be specified as 1:1 to 1:n when reading the maximum
cardinalities. The minimum cardinality of 1 is set to 0 for optional data. Therefore a 1-n
relationship can also be specified as 0:1 to 0:n, 0:1 to 1:n, or 1:1 to 0:n.
The basic rules for how cardinality is used are the following:
• Cardinality is applied in the context of a query, meaning that only the cardinalities of items
explicitly included in the query are evaluated.
• 1-n cardinality implies fact on the n side and implies dimension on the 1 side.
• A query subject can behave as a dimension in one query and as a fact in another.
• Queries on more than one fact will result in a stitched query.
Modeling 1-n Relationships as 1-1 Relationships
If a 1-n relationship exists in the data but is modeled as a 1-1 relationship, SQL traps cannot be
avoided because the information provided by the metadata to the query engine is insufficient.
The most common problems that arise if 1-n relationships are modeled as 1-1 are the following:
• Double-counting for multiple-grain queries is not automatically prevented.
Cognos 8 cannot detect facts and then generate a stitched query to compensate for
double-counting, which can occur when dealing with hierarchical relationships and different
levels of granularity across conformed dimensions.
• Multiple-fact queries are not automatically detected.
Cognos 8 will not have sufficient information to detect a multiple-fact query. For
multiple-fact queries, an inner join is performed and the loop join is eliminated by dropping
the last evaluated join. Dropping a join is likely to lead to incorrect or unpredictable results
depending on the dimensions and facts included in the query.
You can deal with these issues in Report Studio by creating an inner or outer join between queries or creating calculations that compensate for differences in granularity. This workaround requires the report author to have detailed knowledge of the data. There is no workaround available in Query Studio or Analysis Studio.
Detecting Determinants in the Data Source
Determinants are detected in your relational data source by using index and key information. This information is useful when performing automatic aggregation to determine the appropriate level of granularity to assign to a particular item in the query. We recommend that you review the determinants that were created on import and modify them where appropriate to align with your reporting requirements.
For normalized data sources, not all determinants may be relevant for or aligned with your
reporting requirements. We recommend that you remove any unnecessary determinants,
particularly if they conflict with relationships being created for that query subject.
For denormalized data sources, you may want to add determinants to represent non-unique key
segments that represent sets within the data. This is relevant if joins will be made on keys other
than the unique key of the query subject.
Analyzing the Model for Facts and Dimensions
To dimensionally model a relational data source for the purpose of cross-fact querying and
analysis, you must examine the underlying schema and address areas where the role of an object
as a fact or a dimension is not clear. This examination can result in creating a logical layer to
present the data as a star schema. Or you may decide to make changes to the physical data source to deliver a higher performance application. Physical changes may include consolidating tables by using extract-transform-load tools or creating materialized views for frequently-used
aggregations.
In a relational data source that was not de-normalized using star schema concepts, you may need to analyze the cardinality of the imported query subjects before creating dimensions or converting query subjects to dimensions. You must first understand the business applications of the underlying data as well as understand how Cognos 8 generates queries.
Within the context of a query, a query subject with a cardinality of n on all its 1-n relationships to other query subjects can be regarded as a fact. This implies that there are more rows of data in the fact query subject than in the related query subject on the 1 side of the relationship. Any query subject on the 1 side of a 1-n relationship to another query subject is treated as a dimension.
page 10 Reportnet
