in the modeling scenario John Walker is the company modeler who is creating a basic sales model based on the Great Outdoors data warehouse. John’s requirements are to create a reporting package for IBM Cognos BI that allows authors and analysts to query the data source for sales information by product, time, and order methods and to compare the sales figures to sales target values.
John has already looked at various report samples and interviewed users to better understand how he needs to present the data to the authors. Some authors want to perform only basic relational queries against the data source, and other authors, particularly analysts, want the ability to navigate through the data to better understand how the business is doing and where it is being affected positively and negatively.
To that end, John will deliver the following packages, both based on the relational data source:
Import metadata using Model Design Accelerator
The Model Design Accelerator is a graphical utility that is designed to guide both novice and experienced modelers through a simplified modeling process. The Model Design Accelerator applies IBM Cognos leading practices to produce single star schemas quickly.
You can create multiple star schemas using the Model Design Accelerator several times. Then, you can link the results together. You can add features to the model using standard IBM Cognos Framework Manager functionality. The following steps describe how to import metadata using the Model
IBM Cognos Framework Manager welcome panel
New Project dialog box
Metadata wizard: SelectData Source dialog box
Model Accelerator pane
Model Design Accelerator: Explorer Tree
Model Design Accelerator with query items added to Sales Fact
a.Expand the SLS_PRODUCT_LINE_LOOKUP table, and drag the PRODUCT_LINE_EN data item into the Products query subject.
b.Expand the SLS_PRODUCT_TYPE_LOOKUP table, and drag the PRODUCT_TYPE_EN data item into the Products query subject.
c.Expand the SLS_PRODUCT_LOOKUP table, and drag the PRODUCT_ NAME data item into the Products query subject.
The Relationship Editing Mode for: Products dialog box opens.
Model Design Accelerator Relationship Editing dialog box
This dialog box opens because IBM Cognos Framework Manager cannotdetermine the relationship between the SLS_PRODUCT_LOOKUP table andthe SLS_SALES_FACT table. You need to establish the relationship yourself.
Modify the Relationship dialog box
The SLS_PRODUCT_ LOOKUP table has an entry for each product for each language, which results in a many-to-many relationship with the PRODUCT table. After you generate the basic model, you will eventually add a filter to filter out all non-English product names, thus creating a one-to-many relationship.
New Relationship and added query items to the Products query subject
New relationship and newly added query items to the Time query subject
New relationship and added items to the Order Methods query subject
IBM Cognos Framework Manager with newly created model from Model Design Accelerator
For IBM Cognos BI, it is recommended to model in layers. The typical approach is to have three root namespaces:
The namespace layers can use any naming convention that makes sense to you. You might see naming conventions such as Physical View, Business View, and Presentation View, which represent the views, or such as Foundation ObjectsLayer, Consolidation Layer, and Presentation Layer, which represent each of the layers..
The Model Design Accelerator follows the layered modeling approach and creates a three-layered model automatically of the model that we created.
Project Viewer illustrating multiple layer approach
The Physical View, contains the data source query subjects as they were when imported from the data source. It is best to leave these subjects as untouched as possible so that IBM Cognos BI has all the metadata that it requires to generate efficient queries at run time. However, you might have to make some exceptions. After data source query subjects are modified, extra calls to the database for metadata might be required at run time. This rule of thumb does not mean you cannot model in this layer. For example, you might choose to create a model query subject to act as an alias for a dimension table that relates back to a fact query subject.
Physical View layer
The Business View, typically consists of model query subjects that are used either to remodel the Physical View to meet reporting requirements or to consolidate information from the Physical View for a cleaner presentation of the metadata to the user.
The Model Design Accelerator uses the consolidation methodology. This layer is the ideal location to implement filters, calculations, and any other business logic that you might require. It might not always be the case, but as a general rule, this is the layer to apply logic. This layer also acts as an insulation layer for reports. Reports are based on query items that are found in this layer. If the underlying data source changes, you can remap the Business View to the correct items in the Physical View without affecting reports.
In our model, all query items in this layer are given user-friendly names for report authors and analysts.
Business View layer
The Presentation View, typically contains star schema groupings, which are logical groupings of fact and related dimension query subject shortcuts. When more than one star schema grouping is involved namespaces are used to contain the shortcuts because facts can share one or more dimensions. Because namespaces provide uniqueness, a dimension shortcut with the same name can exist in multiple namespaces, allowing authors to query across multiple fact tables using a shared dimension.
You can easily create separate packages for different reporting needs based on this layer.
Presentation View layer
Verify query item properties and relationships
After importing metadata into IBM Cognos Framework Manager, it is recommended that you verify and edit certain property settings and determine if the correct relationships are in place to meet the reporting requirements.
Examine query item properties
Object properties allow you to add additional information, such as descriptions and screen tips, and query item properties let you edit the behavior of the query items, such as configuring the data format. For example, should numeric values be displayed as currency, percentage, or a just a number with comma separators?
Query items properties
You need to examine two properties closely after import to ensure that they are set as expected:
For example, SALES_ORDER_KEY, was imported as an Int32 data type and had its Usage property set to Fact. This item is not a fact, but rather is a key. Thus, you expect to see the Usage property set to Identifier. This issue occurs because the item is not indexed in the database. Examining this setting allows you to have relevant conversations with the database administrator. If this key is to be used in a relationship or filter, it is better that the field is indexed in the database.
The following rules explain how the Usage property is applied during import:
The Regular Aggregate property for numeric facts (measures) describes how measures should be aggregated and defaults to SUM. Sum is correct for most measures (such as SALE_TOTAL and QUANTITY); however, in some instances, you might want to set the property to Average, Minimum, Maximum, and so on.
Another set of important properties to take advantage of are the Prompt Info properties,
Prompt Info properties
You can specify the type of prompt that you want generated in the studios or the default prompt type for IBM Cognos Report Studio. The default is for the server to determine the prompt type based on data type.
The remaining properties are used to improve performance by causing automatic retrieval through indexes while still displaying user-friendly selection values.
Here is a brief description of the remaining properties:
Filter Item Reference property
Relationships are maintained in the Object Diagram or Context Explorer.
As a quick side topic, Context Explorer, is another useful UI element in IBM Cognos Framework Manager.
You can launch this window by selecting one or more query subjects, right-clicking one of the selected items, and then clicking Launch ContextExplorer.
For larger models, this feature is useful when working with a subset of the model. You can edit items directly in this window.
Now, back to relationships. When verifying relationships, you must ensure that the appropriate relationships exist to meet your reporting needs, and you must decide if you require optional or mandatory cardinalities. Optional cardinalities require more processing but might be needed to return the desired results.
Optional cardinality, which generates an outer join in the SQL, is represented by a 0, as in 0..n or 0..1. Mandatory cardinality, which generates an inner join in the SQL, is represented by a 1 as in 1..n or 1..1.
Cardinality is used by IBM Cognos BI to determine which query subjects are facts and which are dimensions in the context of a query. This determination is important, in particular, when querying from multiple fact tables through a shared dimension. By identifying which query subjects are facts, IBM Cognos BI can aggregate the facts properly and not lose records from either fact table.
It is important to model as a star schema so that there is no ambiguity about the nature of a query subject. In short, fact query subjects have only 1..n or 0..n cardinalities attached, and dimension query subjects have only 1..1 or 0..1 cardinalities attached,
Unpredictable results can occur when query subjects have a mix of cardinalities, with the exception of when dealing with snowflake dimensions. Snowflake dimensions are where the hierarchy for the dimension is split out into two or more tables, as
The product hierarchy is split out into the following tables:
In this case, there is a clear path from the highest level all the way down to the fact table. The hierarchy does not branch of at higher levels such as product type level or product line level.
To edit a relationship, double-click the relationship line in the Diagram pane or the Context Explorer to open the Relationship Definition dialog box,
Relationship Definition dialog box
Here, you can change the cardinality on either end of the relationship, change the query items the join is based on, or create a compound definition with additional business logic by editing the expression located at the bottom of the dialog box.
Import additional metadata
Our modeler, John Walker, has created a model that contains sales facts along with its related dimensions (Products, Time, and Order Methods). However, this model also requires sales target information. So, he will use the manual import process to bring in this additional table as metadata in the model. Products and Time are both shared (conformed) dimensions for this fact table; therefore, using the Model Design Accelerator does not make sense in this case.
To import additional metadata manually, follow these steps:
Run Metadata wizard
Metadata wizard: Select Objects dialog box
Metadata wizard: Generate Relationships dialog box
Let us take a moment to discuss this dialog box. In the first section, in general, use the “Use primary and foreign keys” option for relational data from the same database. Typically the database will have primary and foreign keys defined. The other two options in this section are often used when importing from a different database.
In the second section, you can request to create relationships among either the objects being imported, the objects being imported and the existing objects in the model, or both. When importing several additional tables that have relationships to each other and to objects already in the model, select the “Both” option in the wizard. By selecting this option, you do not need to create the relationships manually after import.
In the third section, by default, an import converts outer joins to inner joins for performance reasons. You can choose to generate outer joins if that meets your business needs, or you can edit specific relationships after import to meet your needs.
Another option is to enable or disable fact detection. If this option is disabled, all relationships will be 1..1 to 1..1.
New Sales Target query subject with no relationships defined
Notice that no relationships were generated. In this case, the model should have the following relationships for sales targets:
The reason there is no relationship to the product dimension is that PRODUCT_KEY is not indexed in the database, and it is not a primary key in the table. The same issue applies to the time dimension. MONTH_KEY is not a primary key, and it is not indexed in the database. You can create the relationships manually in IBM Cognos Framework Manager, but the keys should be indexed in the database to improve performance.
Relationship Definition dialog box
The Diagram pane now shows the new relationship
New relationship in Diagram pane
Now that the new sales target object is imported and relationships are created, you need to update the Business View and Presentation View manually. The Presentation View is organized to have separate namespaces for each fact shortcut and its related dimensions. You can use the star schema groupings feature to create the grouping for the new Sales Target Fact query subject that will be created.
New relationship in Diagram pane
Query Subject Definition window for new Sales Target Fact model query Subject
Presentation View with star schema groupings
To this point, we used the Model Design Accelerator to create a one star schema grouping, examined query item properties and relationships, and imported additional metadata which was then organized into an additional star schema for presentation purposes. Next, we describe using techniques to validate the model
Verify the model
You can verify independent model objects and their children, or you can verify the entire model by verifying the model’s root namespace. In this example, we verify the entire model. To verify the model:
Verify Model - Options dialog box
In this window, you can select the types of items that you want to include in the validation process.
Verify Model Results dialog box
You can verify the objects in the model, and you can also use Model Advisor, which is an automated tool that applies rules based on current modeling guidelines and identifies areas of the model that you need to examine. To assist you in understanding the nature of the highlighted issue and some possible actions, you are provided with links to the appropriate sections of the documentation.
To run the Model Advisor, use the following steps:
Model Advisor dialog box, Options tab
Here you can select or deselect items to be tested.
Model Advisor results
In the results, you can identify items that fit certain categories and click the icon under the Action column to view the object or objects in the Context Explorer. You can also click the more information links to read the documentation regarding specific modeling recommendations.
Verify the data
It is also critical that you test each of your query subjects alone and in conjunction with other query subjects to ensure that authors will receive predictable results.
In this next scenario, our modeler John Walker tests Sales Targets against the Time dimension and tests the Products dimension against Sales to ensure that the numbers come back correctly.
John Walker renamed all the query items in the Business View to user-friendly names and organized them in a logical manner. To verify the data:
Test Results dialog box
John Walker knows that the overall total for Sales Target for all time and products is 4,205,368,540 (over four billion), yet the results show several results all adding up to well over four billion dollars. This issue is the result of double counting that occurs because the Month Key in the Time dimension repeats once for every day in the month. Because there is a relationship on the Month Key, determinants must be defined for the underlying query subject in the Physical View layer to ensure that this double counting does not occur.
Test results dialog box
Again, these results are questionable, because overall summaries in the studios double count Revenue values once for each product language in the data source.
Validating the data is an important process. Validate your data often and as new items are imported or modeled to ensure predictable results.
As discussed in the previous section, double counting can occur in scenarios where a relationship based on a key that is not unique and repeats in the data is used in a query. Specifying determinants resolves this issue.
Determinants are a feature of IBM Cognos BI that are typically used to provide control over granularity when aggregating. Determinants are required for dimensions that are connected to facts at levels of granularity that have repeating keys. You can also use them to prevent the distinct clause on unique keys, for BLOB data types in the query subject, and to improve performance on dimensionally modeled relational (DMR) metadata. In this section, we discuss double counting, which is the most common of the issues. For more detailed information about determinants, see the IBM Cognos Framework Manager UserGuide.
To specify determinants to prevent double counting:
Query Subject Definition dialog box, Determinants tab
During import, only one determinant was detected based on the table’s primary key. Because this key is unique, all items in the query subject are an attribute of the determinant key. However, there are other levels in this data, such as the MONTH_KEY, that require a group by clause to prevent double counting of facts. To that end, we create the appropriate determinants for this query subject and apply the Group by setting.
Attributes are any items that are associated with the determinant key. When used in a report, the determinant configuration is implemented in the generated SQL. For example, if CURRENT_MONTH is used in a report, you find a Group By clause on the MONTH_KEY in the SQL.
Determinants tab with multiple determinants specified
To test this change, conduct the same test from the previous section to see if the correct Sales Target values are returned.
The values now appear correctly and double counting is prevented.
This model also includes an SLS_PRODUCT_DIM dimension that has a relationship on PRODUCT_TYPE_KEY, which is also a repeating non-unique key just like the MONTH_KEY is in the GO_TIME_DIM. If you are feeling adventurous, try applying determinants for the SLS_PRODUCT_DIM query subject. The results should display
Determinants specified for SLS_PRODUCT_DIM
IBM Cognos Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|IBM DB2 Interview Questions||Data Warehousing Interview Questions|
|SQL Interview Questions||IBM Cloud Computing Infrastructure Architect V1 Interview Questions|
|Framework7 Interview Questions||IBM BPM Interview Questions|
|IBM Cognos TM1 Interview Questions||Cognos ReportNet (CRN) Interview Questions|
|Structured Query Report (SQR) Interview Questions||IBM DataPower Interview Questions|
Ibm Cognos Tutorial
Introduction To Ibm Cognos Business Intelligence
Overview Of The Ibm Cognos Business Intelligence Architecture
Business Scenario And Personas Used In This
Create Reporting Packages With Ibm Cognos Framework Manager
Business Intelligence Simplified: An Overview
Individual And Collaborative User Experience
Self Service Interface For Business Users
Actionable Analytics Everywhere
Enterprise Ready Performance And Scalability
Ibm Cognos System Administration
Integrating Ibm Cognos Bi With Ibm Cognos Business Analytics Solutions
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.