Dimensional modeling with IBM InfoSphere Data Architect, Part 3: Transformations

Transform Cognos/Cubing model to Cubing/Cognos model

Starting with IBM® InfoSphere® Data Architect V7.5.3, you can create both relational data model and multiple dimensional data models by using InfoSphere Data Architect. This series introduces the following three user scenarios that demonstrate how InfoSphere Data Architect V7.5.3 helps you to accelerate dimensional data modeling: Dimensional data modeling by forward engineering, dimensional data modeling by reverse engineering an existing data source, and transforming dimensional data models that you can use with InfoSphere Data Warehouse and Cognos® Framework Manager. Part 3 introduces the transformation from a Cognos model to a cubing model, or from a cubing model to a Cognos model using InfoSphere Data Architect.

Yun Feng Bai (baiyunf@cn.ibm.com), Staff Software Engineer, IBM

Yun Feng BaiYun Feng Bai is a staff software engineer in China Development Lab, Beijing, China. He is currently focused on InfoSphere Data Architect QA area. Previously, he worked on DB2 Data Warehouse (renamed InfoSphere Warehouse), focusing on OLAP modeling and SQL warehousing.



Zhen Xing Li (lzxlyy@hotmail.com), Software Engineer, HiSoft

Li author photoZhen Xing Li is a software engineer at HiSoft in Bei Jing, China. He is focused on InfoSphere Data Architect QA. At the same time, he has a board knowledge and development experience in the automation test field.



22 March 2012

Also available in Chinese Spanish

Introduction

In Part 1 and Part 2 of the series, you read about how the retail company designed their data models, including both relational data models and dimensional data models. These models are used to manage their sales transactions and sales analysis. Then the company developed new requirements, and the modeling team updated the models that they created in Part 1 by reverse-engineering from an existing data source.

The data modeling team knows that they have existing Cognos and cubing models to perform sales analysis. The data modeling team wants to consolidate those existing models, either use only Cognos models or only cubing models for sales analysis. Also, as base logical and physical data models change, they need to use InfoSphere Data Architect to maintain the models more efficiently. To help maintain these models and enforce consistency as the base logical and physical models develop, the data development team must transform their models to Cognos or cubing models, and also update the models as the changes are introduced from other teams. The data modeling team knows that they can use InfoSphere Data Architect to easily transform models for these various purposes.

To fulfill this requirement, the data modeling team can import existing Cognos or cubing models to the workspace, then transform to the various formats that they need at all stages of data modeling. Follow the steps that are outlined in this article to learn how to transform models from a Cognos model to a cubing model, or from a cubing model to a Cognos model, and then see how the data modeling team updates the models for consolidation.

The data modeling team performs the following steps to transform a Cognos model to a cubing model.

  1. The team transforms a Cognos model to a dimensional-logical data model.
  2. Then the team transforms the dimensional-logical data model into a new dimensional-physical data model.
  3. They transform the dimensional-physical data model into a new cubing model.
  4. Finally, the team imports the cubing model to InfoSphere Warehouse Design Studio, and update the model before the report design team can use it to create their report.

To transform a cubing model into a Cognos model, the steps are exactly the same except for some differences in details.

Transforming a Cognos model into a Cubing model

In this section, the modeling team is going to use Cognos Framework Manager and InfoSphere Data Architect to transform a Cognos model into a cubing model, and then they will update the cubing model before it can be used for deployment and reports.

Understanding the Cognos model

First, take a look at the Cognos model that the modeling team will work with.

As you can see in Figure 1, the team knows that they have a star schema. The STORE_BILLING_DETAILS fact is surrounded by the CUSTOMERS, EMPLOYEES, PRODUCTS, STORE, and TIME dimensions. The fact stores the measures that measure information about the business, such as unit price, profit rate, or labor cost of each item.

Figure 1. Dimensional View diagram of the Cognos model
shows store_billing_details connected to customers table, employees table, products table, and store table

Exporting the Cognos model from Cognos Framework Manager

In the following section, the modeling team exports the Cognos model from Cognos Framework Manager.

  1. Open Cognos Framework Manager, then open the Project Viewer page.
  2. Open the Export Wizard by selecting the model in the Project Viewer, then clicking Action > Export Model.
  3. Complete the Export Wizard to export the Cognos model as follows.
    1. On the Select Export Target page of the Export Wizard, select CWM 1.0 to 1.1(*.xml), as shown in Figure 2.
      Figure 2. Completing the export wizard - Select an Export Target
      shows CWM 1.0 to 1.1 selected
    2. On the Framework Manager Specific Export Options page of the wizard, do not change any settings, as shown in Figure 3.
      Figure 3. Completing the export wizard - specifying export options
      shows reverse engineer relationships, table design level, ignore usage property, and consistency check
    3. On the Third Party Specific Export Options page, specify a file path for the file, including a file name, in the File * field, as shown in Figure 4.
      Figure 4. Completing the export wizard - specifying model-specific options
      shows file path highlighted

      When you click Next, the model is exported to the file path that you specified.

    4. On the Export Results page, verify that the model exported successfully, as shown in Figure 5, then click Next.
      Figure 5. Completing the export wizard - verifying an exported model
      Export Wizard to Verify Export Result
    5. Click Finish.

The team exported the model. Now, the team can import the model to a data design project in InfoSphere Data Architect.

Transform a Cognos model into a dimensional-logical data model

In the following section, the modeling team imports the Cognos model, then transforms the Cognos model into a dimensional-logical data model.

  1. Open InfoSphere Data Architect.
  2. Create a data design project in InfoSphere Data Architect by completing the New Data Design Project Wizard, as follows.
    1. Open the wizard by clicking File > New > Data Design Project.
    2. Specify a name for the project, then click Finish, as shown in Figure 6.
      Figure 6. New Data Design Project
      shows RETAIL_SALES_PROJECT as the new project name
    3. The project is created in the Data Project Explorer view as shown in Figure 7.
      Figure 7. The new data design project
      shows RETAIL_SALES_PROJECT in explorer view
  3. Import the Cognos model file to the data design project as follows.
    1. Open the Import wizard by clicking File > Import.
    2. On the Select page of the wizard, select General > File System, as shown in Figure 8.
      Figure 8. Import from the file system
      Select import from file system from explorer view
    3. On the File system page, specify the following information.
      • Browse for the directory in which the Cognos model is stored.
      • Select the Cognos model type.
      • Select the file name of the Cognos model.
      • Browse for the data design project that you created. View an example of this step in Figure 9.
        Figure 9. Importing the Cognos model from the file system
        Import Cogous model from file system
      • The file is imported into the XML Schemas folder under the data design project, as shown in Figure 10.
        Figure 10. The Cognos model is imported into data design project
        Cognos model imported to Data Design Project in InfoSphere project explorer
  4. Transform the Cognos model into a new dimensional-logical data mode as follows.
    1. Start the transformation configuration wizard by clicking File > New > Transformation Configuration.
    2. On the Specify a Configuration Name and Transformation page of the wizard, specify a file name, then select the Cognos/Cubing Model to Dimensional-Logical Data Model configuration type, as shown in Figure 11.
      Figure 11. Specifying the configuration file name and configuration type
      Shows Specify a Configuration and Transformation wizard
    3. On the Source and Target page of the wizard, select the Cognos model file as the selected source model, then select the data design project as the selected target, as shown in Figure 12.
      Figure 12. Specify a source data model and target data design project
      Shows source and targe in the new transformation configuration
    4. On the Properties page of the wizard, specify that the source dimensional model is a Cognos model, as shown in Figure 13, then click Finish.
      Figure 13. Specify values for the properties of this transformation
      Overwrite files without warning is set to false, and source dimensional model is set to Cognos Model

      The transformation configuration file is created in the Other Files folder, and the transformation configuration file opens in the editor view. You must run the file to generate the dimensional-logical data model.

    5. In the editor view, click Run to generate the dimensional-logical data model, as shown in Figure 14.
      Figure 14. Running the configuration file to transform the Cognos model into a dimensional-logical data model
      shows name and protocol for forward transformation
    6. The new dimensional-logical data model is created in the Data Project Explorer view under the Data Models folder. As you can see in Figure 15, a star schema is created.
      Figure 15. Overview diagram of the dimensional-logical data model
      shows diagram of the data model with customers at the top, connected to store_billing_details, which is connected to time, store, products and employees

Transforming a dimensional-logical data model into a dimensional-physical data model

Before the team can create a cubing model, they must transform the dimensional-logical data model into a dimensional-physical data model. Not only does this get the team one step closer to a cubing model, but it also creates the corresponding physical model that the team can update whenever they update the base logical model.

  1. Right-click the dimensional-logical data model, then select Transform to Physical Data Model. Complete the Transform to Physical Data Model wizard as follows.
    1. On the Target Physical Data Model page, select Create new model, as shown in Figure 16.
      Figure 16. Transform into a new physical data model
      Create new model is selected
    2. On the Physical Data Model file page, specify the following options.
      • Select the data design project as the destination folder.
      • Specify a file name.
      • Specify that you want to create a DB2 for Linux, UNIX, and Windows version 9.7 data model, as shown in Figure 17.
      Figure 17. Specify the database, version, and location of the new model file
      Shows folder, file name, database, and version selected
    3. On the Transform to Physical Data Model page, do not change any options, as shown in Figure 18.
      Figure 18. Specify logical-to-physical data model transformation options
      Shows default options selected
    4. On the Output page, verify that the model transformed successfully, then click Finish, as shown in Figure 19.
      Figure 19. Verify the transformation
      Verify the transformation
    5. A new dimensional-physical data model is created in the Data Models folder of the data design project, and the overview diagram opens in the editor view. As you can see in Figure 20, the model looks similar to the logical data model.
      Figure 20. Overview diagram of the dimensional-physical data model
      customers connected to store_billing details, which is connected to time, store, products, and employees

Now that the data model has been transformed to a dimensional-physical data model, the team can create a cubing model that they will share with the data warehousing team.

Transforming a dimensional-physical data model into a cubing model

To create a cubing model from an existing dimensional-physical data model, you must create and run a transformation configuration, as follows.

  1. Start the New Transformation Configuration wizard.
  2. Create a new transformation configuration file as follows.
    1. On the Specify a Configuration Name and Transformation page, specify a file name, then select the Dimensional-Physical Data Model to Cognos/Cubing Model configuration type.
    2. On the Source and Target page, select a source dimensional-physical data model, then select a target data design project.
    3. On the Properties page, specify that the source dimensional model is a Cubing model.

    The transformation configuration file is created and opened in the editor view.

  3. Run the transformation configuration by clicking the Run button in the editor view.
  4. The new cubing model is created and stored in the XML Schemas folder of your data design project. You can use InfoSphere Warehouse to update the model.

Importing the data model into InfoSphere Warehouse

Now that the data model has been transformed, the data modeling team shares the transformed model with the data warehousing team. The data warehousing team downloads the transformed model, then works to import the model into the InfoSphere Warehouse workspace.

  1. Create a new data design project in InfoSphere Warehouse Design Studio.
  2. Create a blank physical data model with OLAP in the data design project.
  3. Import the cubing model as OLAP Metadata to the physical data model, as shown in Figure 21 and Figure 22.
    Figure 21. Importing OLAP metadata
    OLAP Metadata selected in the explorer view
    Figure 22. Importing the cubing model to InfoSphere Warehouse Design Studio
    shows OLAP objects imported successfully
  4. After you import the cubing model, you can navigate the cube model that is created, as shown in Figure 23.
    Figure 23. The structure of the new transformed cubing model
    shows explorer view of the model with store_billing_details
  5. Update the cubing model before deploying it to the server.

    Note: This article does not explain how to update the objects or create new objects in InfoSphere Warehouse Design Studio. However, this article specifies the objects that the team needs to update before it will be ready to deploy on the server.

    The team performs the following steps to update the cubing model.

    1. Specify that the following measures are calculated measures:
      • TOTAL_PRICE
      • TOTAL_COST
      • PROFIT_RATE
      The team updates these measures because these measures were defined as query items that are calculated from other columns in Cognos. To keep it consistent, the team must specify that these are calculated measures in a cubing model.
    2. Specify that the FULL_ADDRESS attribute of the CUSTOMERS table is a calculated attribute. The team updates this attribute because the attribute was defined as a query item that was calculated from other columns in Cognos.
    3. Create cubes in the STORE_BILLING_DETAILS cubing model.
    4. Add cube facts and cube hierarchies to the cubes. In the source Cognos model, the CUSTOMERS, EMPLOYEES, and PRODUCTS hierarchies are defined as default measures in each dimension. You can specify them as the cube hierarchies.

    The data warehousing team may need to update more in the cubing model based on the source Cognos model. For example, Cognos supports some query items that cubing models do not directly support. However, the warehousing team can create an MDX calculated member or measure in the cubing model to model this requirement. These objects are not covered in this article.


Transforming a cubing model into a Cognos model

In the previous steps, the modeling team transformed a Cognos model into a cubing model, then passed along the cubing model to the warehousing team. The warehousing team updated the cubing model to ensure that the same data that is modeled in the Cognos model is also modeled in the cubing model. The warehousing team can now deploy the model and use it to create reports. The warehousing team can follow similar processes to transform the cubing model into a Cognos model.

Understanding the cubing model

First, look at the cubing model the modeling team will work on, as shown in Figure 24.

Figure 24. The structure of the transformed cubing model
explorer view of all the OLAP objects

Click to see larger image

Figure 24. The structure of the transformed cubing model

explorer view of all the OLAP objects

Exporting the cubing model from InfoSphere Warehouse Design Studio

Before the warehousing team can share the cubing model with the other teams, the team must export the model from Design Studio, as follows.

  1. Open InfoSphere Warehouse Design Studio.
  2. Open the Export wizard by clicking File > Export.
  3. Complete the Export wizard as follows.
    1. On the Select page, expand the Data folder, then select OLAP Metadata, as shown in Figure 25.
      Figure 25. Selecting an export type in the Export wizard
      Shows OLAP Metadata selected
    2. On the Specifying Cubing Services objects and Destination File page, specify a file path and file name, then select to export all objects of the cubing model, as shown in Figure 26.
      Figure 26. Select OLAP objects and destination in Export wizard
      Shows Data Projects, Database (project1/RETAIL_SALLES/dbm/). amd STORE_BILLING_DETAILS selected
    3. When you click Finish, the exported model is saved to the path that you specified.

Now that the warehousing team has exported the updated cubing model, the team can share the model with the data modeling team. The data modeling can use InfoSphere Data Architect to transform the cubing model into a format that can be used for Cognos reporting team.

Transforming a cubing model into dimensional-logical data model

  1. Open InfoSphere Data Architect.
  2. If a data design project does not exist, create one by completing the New Data Design Project wizard.
  3. Import the cubing model by completing the Import wizard.
  4. Create a transformation configuration as follows.
    1. Open the New Transformation Configuration wizard.
    2. On the Specify a Configuration Name and Transformation page, specify a file name, then select the Cognos/Cubing Model to Dimensional-Logical Data Model configuration type.
    3. On the Source and Target page, select the cubing model as the source, then select the data design project as the target project.
    4. On the Properties page, select Cubing model as the source dimensional model.

    The transformation configuration is created, and the file opens in the editor view.

  5. Run the transformation configuration by clicking the Run button in the editor view. A new dimensional-logical data model is created in the Data Models folder of the data design project. Figure 27 shows the transformed model.
    Figure 27. Overview diagram of the dimensional-logical data model
    customers connected to store_billing_details, connected to time, store, products, and employees

Transforming a dimensional-logical data model into a dimensional-physical data model

Now that the data modeling team has the transformed dimensional-logical data model, they can transform it to a dimensional-physical data model. This step is necessary before they transform the model into a Cognos-compatible model.

  1. Right-click the dimensional-logical data model and select Transform to Physical Data Model.
  2. Complete the Transform to Physical Data Model wizard and create a new DB2 for Linux, UNIX, and Windows V9.7 physical data model.

The transformed model is created in the Data Models folder of the data design project. Figure 28 shows the transformed dimensional-physical data model.

Figure 28. Overview diagram of the dimensional-physical data model
customers connect to store_billing details, connected to time, store, products, and employees

Transforming a dimensional-physical data model into a Cognos model

Now that the modeling team has a dimensional-physical data model, they can transform to a Cognos-compatible model so that they can share a consistent model with the reporting team.

  1. Start the New Transformation Configuration wizard.
  2. Create a new transformation configuration as follows.
    1. On the Specify a Configuration Name and Transformation page, specify a file name, then select the Dimensional-Physical Data Model to Cognos/Cubing Services Data Model configuration type.
    2. On the Source and Target page, select the dimensional-physical data model as the source, then select the data design project as the target project.
    3. On the Properties page, select Cognos model as the target dimensional model.

    The transformation configuration is created and opens in the editor view.

  3. Run the transformation configuration to generate the Cognos model by clicking the Run button.

The Cognos model is generated under the XML Schemas folder in the data design project. The file name is the same as the database name of the dimensional-physical data model.

Importing the Cognos model to Cognos Framework Manager and updating the model

The reporting team knows that they should update this Cognos-compatible model to ensure that all reported information is still made available to report readers.

  1. Open Cognos Framework Manager.
  2. Create a new project, then import the model as follows.
    1. Click File > New. The New Project window opens.
    2. Specify RETAIL_SALES as the project name, then specify a location.
    3. Click OK.
    4. In the Select Language window, select your preferred language. The data modeling team selects English from the list of options and click OK.
    5. On the Metadata Wizard - Select Metadata Source page, select IBM Cognos model and click Next.
    6. On the Metadata Wizard - Select Model page, browse and locate the transformed model in your data design project and click Next.

      Note: By default, Cognos Framework Manager looks for Framework Manager projects with the .cpf file extension. In order to locate your file, you should select the All Files (*.*) file type.

    7. On the Select Objects page, select the root object, such as RETAIL_SALES.
    8. Click Finish to complete the project creation and import the model into Cognos Framework Manager. The model import summary is shown in Figure 29.
      Figure 29. Summary of the imported Cognos model
      shows import process completed, with list of objects created
  3. You can open the dimensional view diagram to verify that most of the information that report readers need is in the model, as shown in Figure 30.
    Figure 30. Dimensional view diagram of the Cognos model
    store_billing_details to the left, connected to customers, employees, products, store, and time entities on the right
  4. Update the Cognos model before deployment.

    Note: This article does not describe how to update these objects in detail. The information is here to help you understand what objects must be modified before you can generate reports and why those objects are modified.

    1. Update the TOTAL_PRICE, TOTAL_COST, and PROFIT_RATE measures to query items which are calculated from other columns. These measures must be updated because the current expression definitions point to PhysicalView individual column.
    2. Update the FULL_ADDRESS attribute to query item which is calculated from other columns. This attribute must be updated because the expression of the attribute points to column FULL_ADDRESS while it does not exist.
    3. Create a new query item, [GroupCustomers], using a dimensional function within the CUSTOMER_TYPE hierarchy. This item is required because one MDX calculated member with the [GroupCustomers] name was defined in the source cubing model, but was not created during the transformation process.
    4. Specify the measures order for the STORE_BILLING_DETAILS fact. The TOTAL_PRICE measure was the default cube measure for the STORE_BILLING cube, but the UNIT_PRICE measure is the first measure for the STORE_BILLING_DETAILS fact.
    5. Specify default hierarchies for the CUSTOMERS, EMPLOYEES, and PRODUCT dimensions. The CUSTOMER_TYPE, EMPLOYEE_TERRITORY, and PRODUCT_SUPPLIER hierarchies are available in the STORE_BILLING cube in cubing model, so these hierarchies can be used as the default hierarchies for the dimensions.

Note: The cubing model that is used in this article has limited objects, but the team can update the model if more dimensional objects are added, such as MDX calculated measures, virtual cubes, time dimensions and levels, and other objects. However, these objects are not covered in this article.


Conclusion

Throughout the course of this article, you followed the reporting, data modeling, and data warehousing teams as they transformed Cognos and Cubing Services models from one format to another.

You learned how to transform a Cognos or a cubing model into another using InfoSphere Data Architect, then learned what objects must be updated before each team can deploy models for use or reporting.

Throughout this article, you learned how the relational and dimensional data modeling capabilities of InfoSphere Data Architect accelerate model migration from Cognos or Cubing Services to each other, reducing the time needed to migrate and deploy models.


Acknowledgement

Thanks to Erin Wilson's great effort for the review of the paper.

Resources

Learn

Get products and technologies

Discuss

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 Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=806206
ArticleTitle=Dimensional modeling with IBM InfoSphere Data Architect, Part 3: Transformations
publish-date=03222012