IBM Business Analytics Proven Practices: IBM Cognos BI as a Data Source for Transformer

Nature of Document: Guideline; Product(s): IBM Cognos Transformer; Area of Interest: Modeling

This document is intended as a guideline on how to use IBM Cognos BI as a data source to build IBM Cognos Transformer PowerCubes.

Share:

Introduction

Purpose

This document is intended as a guideline on how to use IBM Cognos BI as a data source to build IBM Cognos Transformer PowerCubes.

Applicability

The guidelines described in this document were tested against IBM Cognos 8.4.x and IBM Cognos 10.1. When referring to IBM Cognos BI in this document, it includes all IBM Cognos versions listed in this applicability section.

Assumptions

This document assumes knowledge and experience of modeling concepts and techniques in IBM Cognos Transformer, as well as experience authoring basic reports in IBM Cognos BI Report Studio and analysis in IBM Cognos BI Analysis Studio.

Exclusions and Exceptions

This document will not illustrate the use of Framework Manager generated IQD files or CSV files created from IBM Cognos BI report outputs as data sources in Transformer.


Overview

In versions earlier than IBM Cognos 8.4, Transformer modelers accessed IBM Cognos BI content by either saving report outputs as comma separated values (CSV) files and using those CSV files as data sources in Transformer, or they could generate Impromptu Query Definition (IQD) files through Framework Manager.

In IBM Cognos 8.4 and higher, it is possible to use existing IBM Cognos BI packages and reports found in the IBM Cognos BI Content Store as a data source in IBM Cognos Transformer. This additional feature has made building data sources reflecting a "single version of the truth" that much easier. Any existing work done in Framework Manager modeling and exposed as a package, or any work done in IBM Cognos BI reports can be leveraged in Transformer, improving development time and ensuring that the data is all coming from the same underlying data source.

This document will provide guidelines on using IBM Cognos BI packages, whether they are relational, dimensionally modeled relational (DMR), or other OLAP sources such as another PowerCube, as well as IBM Cognos BI reports.


Guidelines for Efficient Cube Builds

When using IBM Cognos BI as a data source for Transformer, the following are some guidelines regarding efficient cube build times.

  • If filtering and/or calculations are required, consider using IBM Cognos BI reports as the data source.
  • Create separate data sources or data source queries in Transformer for each required dimension as well as for measures.
  • Expose foreign keys found in fact tables to Transformer modelers.
  • Avoid unnecessary local data processing on IBM Cognos BI servers.

Use IBM Cognos BI Reports when Filters and/or Calculations are Required

While you can request that the IBM Cognos Framework Manager modeler include certain filters and calculations in an IBM Cognos BI package, this requires extra maintenance on the Framework Manager modeler's part. Instead, Transformer modelers can use list reports that incorporate filters/prompts and calculations as needed. If prompts are used in the report, Transformer modelers can control which filters are applied when the PowerCubes are built.

Figure 1.
IBM Cognos BI report prompt dialog in Transformer

Regardless of whether the work is done in the Framework Manager model or in a report, the net gain remains the same; where supported, processing will be pushed to the database and the load on Transformer will be reduced. If filters/prompts are used, then Transformer will deal with a reduced record set thereby decreasing build times.

Using reports allows for self service for a greater degree of control to meet requirements and removes the need for additional development support.

Create Separate Queries in Transformer

When using a package as a data source in Transformer, individual queries should be created based on the data sources for the cube; one for each required dimension and one or more for the measures depending on how many underlying fact tables there are and what the business need is.

When using a report as a data source, either use separate list reports, again, one for each dimension and one or more for the measures, or create separate list report queries in one report. Then in Transformer, a query is created for each of the separate list reports or separate list queries within a single report.

To understand why this is recommended, consider the following two record sets.

This first record set is a simple "select * from" the Order Method table in the database.

Figure 2.
Simple Order method query with unique values

In this case, Transformer will deal with a simple record set in which only one value per order method is returned making category generation efficient.

Now examine this second record set in which date and fact data were added to the query.

Figure 3.
Query in which Order method values repeat due to additional level of granularity

The Order method values now repeat due to the introduction of another level of granularity in the query, in this case, the Date data. With the results from this query, Transformer has many more rows of data to deal with when generating the Order Method dimension categories. This example illustrates why it is better to use independent focused queries as opposed to larger consolidated queries.

Expose Fact Table Foreign Keys in the Package

Typically, foreign keys found in the fact table are not presented in the fact query subjects in an IBM Cognos BI model as seen below.

Figure 4.
Fact query subject with no keys exposed

Either they are not included in the model query subject, or they are hidden from the user.

For most ad hoc query users or report authors, they provide little benefit and can cause confusion. "Where should I take Product key from; the Products query subject or the Sales fact query subject?". To prevent confusion to the larger authoring audience, security can be used to expose the keys to a select group of authors only. For example, only Transformer modelers would see the following in the Sales fact query subject.

Figure 5.
Fact query subject with keys exposed

In certain cases such as with Transformer modelers, there is a clear benefit to exposing these foreign keys to the users. It will be more efficient to create a measures data source in Transformer that takes its keys from the actual fact table rather than from its related dimension tables. In this way, the query will go against one table in the data source rather than several. The database may rewrite the query to be more efficient before executing it, but as a general rule of thumb, the cleaner the SQL IBM Cognos submits to the database, the more likely an efficient query will be run at the database layer which takes advantage of database optimization.

This method applies to relational models since DMR models do not allow the addition of non-measures, such as keys, to measure dimensions in Framework Manager.

If any modifications to the Framework Manager model are required, contact the designated Framework Manager modeler.

Avoid Unnecessary Local Data Processing on IBM Cognos BI Servers

Certain query scenarios may require data processing on IBM Cognos BI servers. In some of these cases performance is not an issue and may even be required to produce a result set where a database vendor does not support the query. However, there are cases where local processing can adversely affect query performance and in turn affect PowerCube build times. A typical example of this is when a function that is not supported by the database vendor is used in a calculation. This will cause a retrieval of the entire data set from the database and then perform the calculation locally on the IBM Cognos BI servers. To rectify the situation, use an equivalent function that is supported by the database vendor.

To see if local processing is occurring for a slow performing query, there are a couple of troubleshooting techniques. One is to look at the Cognos SQL generated for the query and compare it to the Native SQL. This can be done in Framework Manager or in Report Studio. Actions being performed in the Cognos SQL and not in the Native SQL indicate that processing is done locally. Another technique is to set query processing to Database Only. In Framework Manager this setting is found in the properties of the Data Source objects and is called Query Processing.

Figure 6.
Framework Manager - Properties pane showing properties for a Data Source

In Report Studio, the setting is found in the properties of the query and is called Processing.

Figure 7.
Report Studio - Properties pane showing properties for a query

When the setting is set to Database Only, an error will be generated in cases where the query can not be fully pushed to the database. The error will typically identify the offending item preventing the push to the database.

In the following sections, examples will be provided on how to effectively use IBM Cognos BI as a data source for Transformer. As with any development, the business requirements may affect the methods used to achieve the end goal and require thorough testing to ensure accurate and optimal performance.


Using IBM Cognos BI Packages as a Data Source

This section will illustrate how individual queries in Transformer are created from an IBM Cognos BI package and then used to create a PowerCube. In the following example, a modified version of the GO Data Warehouse (query) package will be used in which foreign keys are exposed in the fact query subjects.

To perform the steps in this document, the modified version of the model has been supplied in the great_outdoors_warehouse-modified.zip file. Extract the contents and publish the GO Data Warehouse (query) package using Framework Manager.

Basic Single-Fact Model Example

When using IBM Cognos BI as a data source for Transformer, in its simplest form, a PowerCube can consist of a few measures from one underlying fact table in a database, and two or more dimensions. This kind of scenario can be easily accomplished using an IBM Cognos BI package as a data source.

The following steps illustrate how to create a basic PowerCube from an IBM Cognos BI package.

  1. Create a new model in IBM Cognos Transformer and name it GO Revenue.
  2. Click Next, in the Name box, type GO Data Warehouse, and then under Data source type, select Package.
    Figure 8.
    Transformer - New Data Source dialog
  3. Click Next, and then navigate to the desired IBM Cognos BI package, in this case, GO Data Warehouse (query) is used.
    Figure 9.
    Transformer - New Model dialog
  4. Click Next, and then in the Query name box, enter an appropriate name, in this case Sales Measures will be used.
  5. Under Source, expand the metadata tree to navigate to the required items, select them and then add them to the Columns tab. In this case, Revenue and Product key from the Sales fact query subject have been added.
    Figure 10.
    Transformer - Query Definition dialog
    For this simple PowerCube, a date field will also be added.
  6. From Time dimension, add Date.
    Figure 11.
    Transformer - Query Definition dialog
  7. Click on the SQL tab to see what the projection list looks like and how many tables are included in the query.
    Figure 12.
    Transformer - Query Definition dialog showing the SQL tab
    In this case, two tables will be queried at the database layer, the SLS_SALES_FACT and the GO_TIME_DIM tables. As stated earlier, it is typically better to use keys from the fact table, but in order to take advantage of automatically generated time categories in Transformer, a date field is required. In this case, there is no date field in the fact table, and therefore must be retrieved from the time dimension table.
  8. Click OK, deselect Run Auto Design, and then click Finish.
  9. Click Yes when prompted to add another query from the data source.
  10. In the Query name box, type Products, and then add the following items.
    • Product line
    • Product type
    • Product name
    • Product line code
    • Product type key
    • Product key
    Figure 13.
    Transformer - Query Definition dialog
  11. Click OK, and then click No when prompted to add another query from the data source.
    The Data sources pane now has two queries based on the IBM Cognos BI package.
    Figure 14.
    Transformer - Data Sources pane
    Notice the package icon at the root. This allows for easy identification of the data source type.
    Now a model will be created based on the metadata found in these queries. First a Time dimension will be created.
  12. From Sales Measures, drag Date to the grey bar in the Dimension Map pane. A time dimension is automatically created.
  13. Double click Date in the dimension map, rename it Time dimension, and then click OK.
    Figure 15.
    Transformer - Dimension Map pane
  14. From the Products data source, build a Products dimension using the keys as the source values and the string values as the labels as shown in the table below.
    Source ValueLabel
    Product line codeProduct line
    Product type keyProduct type
    Product keyProduct name
  15. Rename the dimension and levels appropriately and in the properties of the lowest level, in this case Product name, ensure it is marked as having unique categories. The dimension appears as shown below.
    Figure 16.
    Transformer - Dimension Map pane
  16. From the Sales Measures data source, add Revenue to the Measures pane.
    Figure 17.
    Transformer - Data Sources and Measures panes
  17. In the PowerCubes pane, insert a new PowerCube, in this case the name is GO Revenue, and then right-click the PowerCube and click Create Selected PowerCube.
    The PowerCube can now be published as a data source and package to IBM Cognos BI for use in any of the studios.
  18. Right-click the PowerCube and select Publish PowerCube as Data Source and Package.
  19. Click OK to start the Publish Wizard.
  20. Follow the instruction in the Publish Wizard to make the PowerCube available in IBM Cognos BI.
  21. Once finished, open IBM Cognos BI and then open the package just published in Analysis Studio and create an analysis.
    Figure 18.
    Analysis Studio - Insertable Objects pane
    Figure 19.
    Analysis Studio - crosstab analysis
    The metadata and data appear as expected.

Multi-Fact Model Example

The previous example only contained a measure that was taken from one fact table in the underlying database. As facts are taken from multiple fact tables, the modeling process can become more complex.

In the next example, the model will include two facts, Revenue and Sales targets, each coming from a separate underlying fact table and each relating to the Time dimension and Products dimension at different levels of granularity. The Sales target values are stored at the month level for Time dimension and the Product type level for Products. There is no date value available for the Sales target facts, which would be required for automatically generated time categories. Therefore another approach can be used for the time dimension. In this case, manually building a time dimension would make sense.

The following steps will illustrate how to build a simple multi-fact model that incorporates a manually created time dimension based on a time dimension table found in the underlying database.

  1. In Transformer, create a new model called GO Revenue and Sales Targets, and then insert a data source based on a package, in this case, the GO Data Warehouse (query) package.
  2. Name the first data source Sales Measures and add the following items from Sales fact.
    • Revenue
    • Order day key
    • Product key
    Figure 20.
    Transformer - Query Definition dialog
  3. Click OK, deselect Run Auto Design, and then click Finish.
  4. Click Yes when prompted to add another query from the data source.
  5. In the Query name box, type Sales Target Measures, and then add the following items.
    • Sales targets
    • Month key
    • Product type key
  6. Click OK and then create two more data sources with the following items.
    Products
    • Product line
    • Product type
    • Product name
    • Product line code
    • Product type key
    • Product key
    Time
    • Year
    • Quarter key
    • Quarter
    • Month key
    • Month
    • Day key
    • Date
    The results appear as shown below.
    Figure 21.
    Transformer - Data Sources pane
    In order to relate the Revenue measure to the Time dimension, the related column names must match. In this case, Order day key from Sales Measures does not match Day key from Time.
  7. Rename Order day key in Sales Measures to Day key.
  8. Create a Time dimension and a Product dimension with the following items as source values and labels.
    Time dimension
    Source ValueLabel
    YearN/A (same as source value)
    Quarter keyQuarter
    Month keyMonth
    Day keyDate
    Products
    Source ValueLabel
    Product line codeProduct line
    Product type keyProduct type
    Product keyProduct name
  9. Rename the dimensions and levels appropriately.
    Figure 22.
    Transformer - Dimension Map pane
    Because Sales targets are related to Time dimension on Month key and to Products on Product type key, these levels will also need to be marked as having unique category values in the properties of the level.
  10. Mark the following levels as having Unique category values.
    Time dimension
    • Month
    • Day
    Products
    • Product type
    • Product name
  11. Double-click Time dimension to edit its properties. Currently this dimension is identified as a Regular Dimension. Using the properties, it will be marked as a Time dimension.
  12. Under Dimension type, select Time.
    Figure 23.
    Transformer - Dimension dialog
  13. Click OK.
  14. Drag Revenue from Sales Measures and Sales target from Sales Target Measures to the Measures pane. To see the scope of the measures as well as the data sources, use the Show Scope feature.
  15. In the Measures pane, right-click on Revenue, and then select Show Scope.
    Figure 24.
    Transformer - Show Scope Dimension Map pane
    The Revenue measure is in scope with the lowest level in both dimensions.
  16. From the drop down list, select Sales target.
    Figure 25.
    Transformer - Show Scope Dimension Map pane
    The Sales target values are in scope with the Month level from Time dimension and Product type from the Product dimension, which is what is expected. The scope of the data sources can be viewed in this pane as well.
  17. Select Data Source, and then from the drop down list, select Sales Measures.
    Figure 26.
    Transformer - Show Scope Dimension Map pane
    The Sales Measures data source query is in scope at the lowest level for both dimensions based on the key links between the data source queries. In this case the links are based on Day key for Time and Product key for Products.
  18. From the drop down list, select Sales Target Measures.
    Figure 27.
    Transformer - Show Scope Dimension Map pane
    As expected, the Sales Target Measures data source query is only in scope at the higher levels in the dimensions based on the key links between the data source queries. In this case the links are based on Month key and Product type key.
    If the Month level and the Product type level had not been marked as unique, the dimension map would highlight those levels in red, indicating an issue with the link relationship between the data sources.
    Figure 28.
    Transformer - Show Scope Dimension Map pane
  19. In the PowerCubes pane, create a new PowerCube and name it GO Revenue and Sales Targets.
  20. Right-click the PowerCube and then click Create Selected PowerCube.
  21. Publish the PowerCube to IBM Cognos BI, open it in Analysis Studio and create an analysis.
    Figure 29.
    Analysis Studio - crosstab analysis
    The metadata and data appear as expected and the cube allows for Revenue and Sales target comparisons.
    This particular PowerCube allows you to drill down to the day level since the Day key was required in the Time dimension in order to properly roll up Revenue from Sales Measures.
    Figure 30.
    Analysis Studio - crosstab analysis showing blanks for out-of-scope measure
    Since Sales target is not in scope for the Day level, no values are returned. In this case, the cube only needs to go to the month level in order to compare revenue with sales targets. One option to have the cube go to the Month level only is to suppress the Day level in the Time dimension. However, this still stores the Day level data in the cube but hides it from the users. For data sets that contain millions of records, this may not be an optimal option.

In the next section, reports will be used as data sources to create a multi-fact cube that is aggregated at the database layer before being processed in Transformer. Unlike the previous example, this will allow for smaller cube builds since lower levels of granularity will not exist in the PowerCube.


Using IBM Cognos BI Reports as a Data Source

This section will illustrate how individual queries in Transformer are created from IBM Cognos BI reports and then used to create a PowerCube.

Using reports as a data source instead of packages provides additional flexibility. For example, measure aggregation and calculations can be passed to the database layer for processing rather than having Transformer process them. This can improve PowerCube build times while also providing flexibility in creating model appropriate queries.

A report can be created for each dimension required in one or more Transformer models. Groups of measures can be created in separate reports as well or consolidated in one query depending on the Transformer model requirements. Alternately, all dimensions and measures can be created in one report as separate queries within the report. The key is to ensure that the queries are separate so that the SQL generated is as minimized as possible. Whether they are in separate reports or separate queries within one report comes down to personal preference and how users feel comfortable maintaining and organizing their objects.

In this example, one report will be used with multiple queries. For the measures query, both Revenue and Sales target will be combined in one measures query, which will also include a calculation to show the variance between Revenue and Sales target. The two measures come from different underlying database tables, but IBM Cognos BI will be used to create a query that will aggregate the measures to a common level of granularity, in this case, Month level from Time dimension and Product type level from Products. This method will push the aggregation and calculation operations to the database rather than having Transformer process them.

The following steps will illustrate how to build a simple multi-fact model that uses queries from an IBM Cognos BI report as data sources in a Transformer model.

  1. In IBM Cognos BI, open Report Studio with the desired package, in this case the GO Data Warehouse (query) package.
  2. Create a list report and add the following items to create a measures query. From Time dimension
    • Month Key
    • Date
    From Products
    • Product type key
    From Sales fact
    • Revenue
    From Sales target
    • Sales target
    The results appear as shown below.
    Figure 31.
    Report Studio - list report
    The Month key, date and product type key are taken from conformed dimensions so that IBM Cognos BI can correctly aggregate and stitch together the results from the two separate underlying fact tables.
    In order to generate a date that can be used to automatically generate time categories in Transformer, the Date query item will be modified to retrieve the minimum date for each Month key and Product type key grouping.
  3. Double-click the Date column and modify the expression as shown below.
    minimum([Sales (query)].[Time dimension].[Date])
  4. From the Toolbox tab, add a query calculation to the list report, name it Target Variance, and create the following expression.
    [Revenue] - [Sales target]
  5. Add a new List object to the report and add the following items to create a Products dimension query.
    • Product line
    • Product type
    • Product name
    • Product line code
    • Product type key
    • Product key
    The result appears as shown below.
    Figure 32.
    Report Studio - showing two list reports
    Remember to name the queries in the report with meaningful names to make data source creation easier in Transformer. In this case, the names Measures and Products will be used.
  6. Save the report and name it Transformer C8 Report Data Source.
  7. In Transformer, create a new model and name it GO Revenue and Sales Targets - Report Data Source.
  8. Click Next, in the Name box, type Transformer C8 Report Data Source, and then under Data source type, ensure Report is selected.
    Figure 33.
    Transformer - New Data Source dialog
  9. Click Next, and then navigate to the desired IBM Cognos BI report, in this case, Transformer C8 Report Data Source.
  10. Click Next, and then in the Query name box, enter an appropriate name, in this case Measures will be used.
  11. Under Source, add all the items from Measures except Month key to the Columns tab.
    Figure 34.
    Transformer - Query Definition dialog
    Month key was used in the report query to allow for a common aggregation roll-up level. It is not required in Transformer in this instance as the Date query item will be used for automatically generated time categories.
  12. Click OK, deselect Run Auto Design, and then click Finish.
  13. Click Yes when prompted to add another query from the data source.
  14. In the Query name box, type Products, and then add all items from Products to the Columns tab.
    Figure 35.
    Transformer - Query Definition dialog
  15. Click OK, and then click No when prompted to add another query from the data source. The Data sources pane now has two queries based on the IBM Cognos BI report.
    Figure 36.
    Transformer - Data Sources pane
    Notice the report icon at the root. This allows for easy identification of the data source type.
    Now a model will be created based on the metadata found in these queries. First a Time dimension will be created.
  16. From Measures, drag Date to the grey bar in the Dimension Map pane. A time dimension is automatically created.
  17. Double click Date in the dimension map, rename it Time dimension, and then click OK.
    Figure 37.
    Transformer - Dimension Map pane
  18. From the Products data source, build a Products dimension using the Ids as the source values and the string values as the labels as shown in the table below.
    Source ValueLabel
    Product line codeProduct line
    Product type keyProduct type
  19. Rename the dimensions and levels appropriately and in the properties of the lowest level, in this case Product type, ensure it is marked as having unique categories. The dimension appears as shown below.
    Figure 38.
    Transformer - Dimension Map pane
  20. From the Measures data source, add Revenue, Sales target, and Target Variance to the Measures pane.
    Figure 39.
    Transformer - Data Sources and Measures panes
  21. In the PowerCubes pane, insert a new PowerCube, in this case the name is GO Revenue and Sales Targets – Report Data Source, and then right-click the PowerCube and click Create Selected PowerCube.
    The PowerCube can now be published as a data source and package to IBM Cognos BI for use in any of the studios.
  22. Right-click the PowerCube and select Publish PowerCube as Data Source and Package.
  23. Click OK to start the Publish Wizard.
  24. Follow the instruction in the Publish Wizard to make the PowerCube available in IBM Cognos BI.
  25. Once finished, open IBM Cognos BI and then open the package just published in Analysis Studio and create an analysis.
    Figure 40.
    Analysis Studio - crosstab analysis
    The data appears as expected.

Working with DMR and OLAP Sources

Leverage Existing Dimensions

In instances where dimensions have already been created in a Dimensionally Modeled Relational (DMR) model or an OLAP source such as another PowerCube, that work can be leveraged in Transformer. This can be done using the Insert Dimension from Package feature in the Dimension Map pane. In these cases, it makes sense to use DMR/OLAP as a data source in Transformer since it can reduce development time, providing the dimensions are conformed. In other words, the source values (business keys) and labels (member captions) are consistent throughout the data sources used for reporting in IBM Cognos BI. For example, Product line codes in one Product dimension should be the same as Product line codes in other Product dimensions or relational sources. This type of conformance is important where drill-though and master/detail relationships are concerned.

Importing dimensions into Transformer, however, may require some additional work. For example, some data items may need to be renamed to ensure the measures data source query can link to the imported dimension. This scenario will be shown later in this section.

The following steps will illustrate how to insert a dimension from an IBM Cognos BI package. In this example, a Measures data source has already been created based on an IBM Cognos BI report and the measures and Time dimension have already been modeled.

Figure 41.
Transformer - Dimension Map, Data Sources and Measures panes

Using what you have learned in this document, you can create the above model before proceeding with the following steps.

  1. In Transformer, right-click in the Dimension Map pane, and then click Insert Dimension from Package.
  2. In the Insert from Dimension dialog, browse to the desired package, in this case GO Data Warehouse (analysis) will be used, which is a DMR package.
  3. Click Finish, and then select the dimension, hierarchy or levels required. In this case, only the Product line and Product type levels will be used.
    Figure 42.
    Transformer - Insert Dimension dialog
  4. Click OK. A warning dialog appears in to indicate any unneeded items that were dropped from the query.
  5. Click OK.
    Figure 43.
    Transformer - Dimension Map and Data Sources panes
    The Product dimension appears in the Dimension Map pane and a GO Data Warehouse (query) package data source appears in the Data Sources pane. Notice the naming convention for the Product type key item. It is preceded with a (2) due to a naming conflict during the import process. This item is what will be used to link the measures to the Product dimension. There are two options to resolve this naming issue. The Product type key item in the Measures query can be renamed to match, or the (2) can be removed from the Product query. In this case, the latter will be done.
  6. In the Product query, double-click (2) Product type key, rename the item to Product type key, and then click OK. An error message appears indicating that the item being renamed is used in a dimension.
    Figure 44.
    Transformer - error message
    The Product dimension will be remapped to use the new column name of Product type key.
  7. Click Yes, and then in the Dimension Map, in the Product dimension, double-click the Product type level.
    Figure 45.
    Transformer - Level dialog
  8. Click the ellipsis for the Source role's Column Name, and then map to the Product type key column.
    Figure 46.
    Transformer - New Association dialog
  9. Click OK for all open dialogs.
  10. Insert and create a PowerCube called Go Revenue – Leverage Dimension.
  11. Publish the PowerCube as a data source and package to IBM Cognos BI, and then create an analysis with the new package in Analysis Studio.
    Figure 47.
    Analysis Studio - crosstab analysis
    The data appears as expected.

Working with DMR and OLAP Measures

If you wish to leverage measures from a DMR or OLAP source from IBM Cognos BI in a Transformer model, it is recommended to create a report based on the measures. For DMR you can use the report as a data source. For OLAP sources, use the report to generate a CSV output for use in Transformer because OLAP sources are not supported when using reports as a metadata source. In fact, with the exception of SAP BW, you cannot create measure based queries from OLAP sources in Transformer. This is because retrieving measures from an OLAP source to build a PowerCube requires cross joins for the lowest level of each related dimension, which can cause excessively large queries greatly affecting performance.

For SAP BW as a data source in Transformer, please see the Transformer User Guide for detailed information.

As with the other examples shown in this document, the measures report should contain the required business keys as well as a date field, if required, to link to the related dimensions in the model.

In cases where DMR or OLAP packages are used to create measure reports, dimensional functions can be used to extract the business keys from members in order to present the required fields. For example, extracting Product type key from a PowerCube for use in a sales measure query would use a Query Calculation with the following syntax.

rolevalue('_businessKey',[great_outdoor_sales_en].[Products].[Products].[Product type])
Figure 48.
Report Studio - Query Calculation and Data Item Expression dialog

Conclusion

With the introduction of IBM Cognos BI packages and reports as a data source in Transformer, creating conformed data sources representing a "single version of the truth" has been made easier. When planning a Transformer model, take into account the business requirements and then use the most appropriate solutions to build the Transformer queries. In some cases, a package will make sense as a data source, while in other instances reports will provide more flexibility and potentially reduce build times on the Transformer machine. In cases where dimensions already exist, they can be inserted and leveraged to reduce modeling efforts in Transformer.


Download

DescriptionNameSize
Sample XML scripts for this articleIBM_Cognos_8_as_a_Data_Source_for_Transformer.zip1403KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics
ArticleID=501306
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos BI as a Data Source for Transformer
publish-date=06052013