Build a model with IBM Cognos Framework Manager - IBM Cognos

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:

  • A package for basic relational queries
  • A package for OLAP-style queries

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

Design Accelerator.

  1. Open IBM Cognos Framework Manager, and click Create a new project using Model Design Accelerator,
  2. IBM Cognos Framework Manager welcome panel

    IBM Cognos Framework Manager welcome panel

  3. Enter an appropriate project name, in this example GO Sales, and specify the location for the project. Click OK. If the specified folder does not exist, you are prompted with a message asking if you want to create one.
  4. New Project dialog box

    New Project dialog box

  5. Select the design language for the project, in this example English, and then click OK
  6. Select the GOSALESDW data source which was already created by the IBM Cognos BI administrator, and then click Next.
  7. Metadata wizard: SelectData Source dialog box

    Metadata-wizard-Select-Data-Source-dialog-box

  8. In the list of objects, expand GOSALESDW  Tables, and then select the following tables:
    • GO_TIME_DIM
    • SLS_ORDER_METHOD_DIM
    • SLS_PRODUCT_DIM
    • SLS_PRODUCT_LOOKUP
    • SLS_PRODUCT_TYPE_LOOKUP
    • SLS_PRODUCT_LINE_LOOKUP SLS_SALES_FACT
  9. Click Continue. The IBM Cognos Framework Manager User Guide window opens, displaying information about the Model Design Accelerator. The information in this window explains the steps to create a model using the Model Design Accelerator. You can close this window
  10. In the Model Accelerator pane, right-click the Fact Table query subject in the center of the pane, and click Rename.
  11. Type Sales Fact to rename the fact query subject, and then press Enter. The result displays.
  12. Model Accelerator pane

    Model-Accelerator-pane

  13. In the Explorer tree pane, expand gosalesdw  SLS_SALES_FACT, select the measures that follow, and then drag those measure to the Sales Fact query subject
  14. Model Design Accelerator: Explorer Tree

    Model Design Accelerator: Explorer Tree

    Model Design Accelerator with query items added to Sales Fact

    Model Design Accelerator with query items added to Sales Fact

  15. Rename New Query Subject 1 to Products.
  16. In the Explorer tree pane:
  17. 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

    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.

  18. Ctrl-click
  19. Click the Create a Model Relationship icon in the top-left corner of the dialog box. The Modify the Relationship dialog box opens.
  20. Modify the Relationship dialog box

    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.

  21. Click OK, and then click OK again to close the Relationship Editing Mode dialog box.
  22. In the Explorer Tree, expand SLS_PRODUCT_DIM, and add the following items to the Products query subject,
    • PRODUCT_KEY
    • PRODUCT_LINE_CODE
    • PRODUCT_TYPE_KEY
    • PRODUTCT_TYPE_CODE
    • PRODUCT_NUMBER
    • PRODUCT_IMAGE
    • INTRODUCTION_DATE
    • DISCONTINUED_DATE
  23. New Relationship and added query items to the Products query subject

    New Relationship and added query items to the Products query subject

  24. Rename New Query Subject 2 to Time.
  25. In the Explorer Tree pane, expand the GO_TIME_DIM table, click DAY_KEY and then Shift-click WEEKDAY_EN, and then drag the selected items to the Time query subject. The results displays.
  26. New relationship and newly added query items to the Time query subject

    New Relationship and added query items to the Products query subject

  27. Rename New Query Subject 3 to Order Methods.
  28. In the Explorer tree pane, expand the SLS_ORDER_METHOD_DIM table, and add the following items to the Order Methods query subject,
    • ORDER_METHOD_KEY
    • ORDER_METHOD_CODE
    • ORDER_METHOD_EN
  29. New relationship and added items to the Order Methods query subject

    New relationship and added items to the Order Methods query subject

  30. Click Generate Model, and then click Yes to the message. The Model Design Accelerator creates a model based on your selections. When complete, the model is visible in the IBM Cognos Framework Manager UI.

IBM Cognos Framework Manager with newly created model from Model Design Accelerator

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:

  • One for the data source objects
  • One for model query subjects that are used to either model or consolidate metadata that is found in the data source query subjects
  • One for the final presentation view, which typically consists of shortcuts

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

Project Viewer illustrating multiple layer approach

Physical View

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

Physical View layer

Business View

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

Business View layer

Presentation View

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

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

Query items properties

You need to examine two properties closely after import to ensure that they are set as expected:

  • Usage property
  • Regular Aggregate properties

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:

  • Numeric, time-interval, or non-indexed columns are set as Facts
  • Key, index, date, datetime, or any indexed columns are set as Identifiers
  • Strings and BLOBs are set as Attributes

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

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:

  • Cascade on Item Reference is for cascading prompts in Report Studio, for example, where the list of Product Type choices is restricted to those within a selected Product Line.
  • Display Item Reference identifies the default value that a manually-created Report Studio prompt displays for a particular query item. For example, to see a list of ORDER_METHOD_EN names when using ORDER_METHOD_CODE in a prompt, set the Display Item Reference property for ORDER_METHOD_CODE to ORDER_METHOD_EN. If this field is left blank, it defaults to the values that are returned by the query item to which it belongs.
  • Use Item Reference identifies the default value that a manually-created Report Studio prompt uses in the query’s filter. For example, for a Report Studio generated prompt on ORDER_METHOD_EN to display a list of ORDER_METHOD_EN names but retrieve data through the ORDER_METHOD_CODE, set the Use Item Reference property in ORDER_METHOD_EN to ORDER_METHOD_CODE.
  • Filter Item Reference identifies the value that an IBM Cognos generated prompt uses to filter a query. For example, for Query Studio to display ORDER_METHOD_EN values but use ORDER_METHOD_CODE in the query’s filter, set the Filter Item Reference property to ORDER_METHOD_CODE,

Filter Item Reference property

Filter Item Reference property

Examine relationships

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.

Context Explorer

Context Explorer

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

Snowflake dimension

Snowflake dimension

The product hierarchy is split out into the following tables:

  • Product line
  • Product type
  • Product

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

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:

  1. In IBM Cognos Framework Manager, in the Project Viewer, right-click the gosalesdw namespace under the Physical View, and then click RunMetadata Wizard
  2. Run Metadata wizard

    Run Metadata wizard

  3. Ensure that Data Sources is selected, and click Next.
  4. Select the GOSALESDW data source, and click Next.
  5. Locate and expand the Tables folder, and select SLS_SALES_TARG_FACT Click Next
  6. Metadata wizard: Select Objects dialog box

    Metadata-wizard-Select-Objects-dialog-box

  7. Select Between each imported query subject and all existing query subjects in the model
  8. Metadata wizard: Generate Relationships 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.

  9. Click Import, and then click Finish.
  10. Double-click the gosalesdw namespace, and click the Diagram tab to view the new query subject in the diagram, and to examine any relationships.
  11. New Sales Target query subject with no relationships defined

    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:

    • One to the time dimension
    • One to the product dimension

    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.

  12. In the Project Viewer, selectMONTH_KEY from GO_TIME_DIM, and select MONTH_KEY from SLS_SALES_TARG_FACT.
  13. Right-click over the selected items, point to Create, and then click Relationship
  14. Create Relationship

    Create Relationship

  15. The Relationship dialog box opens, The relationship is configured as desired. The dimension is on the 1..1 side, and the fact on the 1..n side. Click OK.
  16. Relationship Definition dialog box

    Relationship Definition dialog box

    The Diagram pane now shows the new relationship

    New relationship in Diagram pane

    New relationship in Diagram pane

  17. Using the same process, create a relationship between
  18. 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.

  19. In the Project Viewer, right-click the Business View namespace, point to Create, and then click Query Subject.
  20. In the Name box, type Sales Target Fact, and click OK.
  21. A Query Subject Definition window for a new model query subject opens. Under Available Model Objects, expand Physical View gosalesdw SLS_SALES_TARG_FACT.
  22. Drag SALES_TARGET to the Query Items and Calculations pane, and click OK.
  23. New relationship in Diagram pane

    New relationship in Diagram pane

    Query Subject Definition window for new Sales Target Fact model query Subject

  24. Click the new Sales Target Fact query subject in the Business View namespace, Ctrl-click Products and Time (these are related dimensions), right-click one of the selected items, and then click Create Star SchemaGrouping.
  25. In the Create Star Schema Grouping dialog box, type Sales Target in the Namespace field, and then click OK.
  26. Drag the Sales Target namespace to the Presentation View.
  27. Right-click the Presentation View namespace, point to Create, and then click Namespace.
  28. Name the namespace Sales, and then drag the Sales Fact, Products, Time, and Order Methods shortcuts into the Sales namespace,

Presentation View with star schema groupings

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:

  1. Right-click the root model namespace, in this case called Model, and click Verify Selected Objects. The Verify Model - Options dialog box opens a
  2. Verify Model - Options dialog box

    Verify the model

    In this window, you can select the types of items that you want to include in the validation process.

  3. Click Verify Model. In this case, the model is quite simple and presents no issues as In other instances where issues are found, you can choose to repair the objects, if applicable, or open the items in the model to edit their definitions to resolve any issues.
  4. Verify Model Results dialog box

    Verify Model Results dialog box

  5. Click Close.

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:

  1. Right-click the namespace that you want to examine. In this example, we use gosalesdw. Click Run Model Advisor. The Model Advisor dialog box opens,
  2. Model Advisor dialog box, Options tab

    Model Advisor dialog box, Options tab

    Here you can select or deselect items to be tested.

  3. click Analyze
  4. Model Advisor results

    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.

  5. Click Close.

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:

  1. In the Project Viewer, in the Business View, select Year (formerly CURRENT_YEAR), select Month (formerly MONTH_EN) from the Time dimension, and select Sales Target (formerly SALES_TARGET) from Sales Target Fact.
  2. Right-click one of the selected items, and click Test. Then, in the Test Results dialog box, select Auto Sum to emulate the default IBM Cognos BI behavior, which is to auto group and summarize in the studios.
  3. Click Test Sample, and examine the results,
  4. Test Results dialog box

    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.

  5. Click Close.
  6. In the Project Viewer, in the Business View namespace, select Product Line (formerly PRODUCT_LINE_EN), select Product Name (formerlyPRODUCT_NAME) from Products, and select Revenue (formerlySALE_TOTAL) from Sales Target.
  7. Right-click one of the selected items, and click Test. In the Test Results dialog box, select Auto Sum, and click Test Sample. The results display
  8. Test results dialog box

    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.

  9. Click Close.

Validating the data is an important process. Validate your data often and as new items are imported or modeled to ensure predictable results.

Specify determinants

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:

  1. In the Project Viewer, in the Physical View namespace, double-click the GO_TIME_DIM query subject, and then click the Determinants tab,
  2. Query Subject Definition dialog box, Determinants tab

    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.

  3. Right-click pk, click Rename, type Day, and then press Enter.
  4. Under the Determinants pane, click Add. New Determinant displays below Day in the Determinants pane.
  5. Right-click New Determinant, click Rename, type Year, and then press Enter.
  6. Click the Up Arrow key on the right to move Year above Day.
  7. With the focus still on Year, from the Available items pane, drag CURRENT_YEAR to the Key pane.
  8. Select the Group By check box beside Year.
  9. Repeat the steps to a create Quarter determinant, with Key = QUARTER_KEY and select the Group By check box beside Quarter.
  10. With the focus still on Quarter, drag CURRENT_QUARTER into the Attributes box.
  11. 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.

  12. Repeat the steps again to create a Month determinant, with Key = MONTH_KEY, and select the Group By check box beside Month.
  13. With the focus still on Month, drag the following items into the Attributes box:
    • CURRENT_MONTH
    • MONTH_NUMBER
    • MONTH_EN
  14. shows the results.

    shows the results.

    Determinants tab with multiple determinants specified

  15. Click OK.
  16. To test this change, conduct the same test from the previous section to see if the correct Sales Target values are returned.

  17. In the Project Viewer, in the Business View, select Year (formerly CURRENT_YEAR), select Month (formerly MONTH_EN) from the Time dimension, and select Sales Target (formerly SALES_TARGET) from Sales Target Fact.
  18. Right-click one of the selected items, click Test, and then in the Test Results dialog box, select Auto Sum.
  19. Test Results after determinants are specified

    Test Results after determinants are specified

  20. Click Test Sample, and examine the results,
  21. The values now appear correctly and double counting is prevented.

  22. Click Close.

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

Determinants specified for SLS_PRODUCT_DIM


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics