IBM Business Analytics Proven Practices: IBM Cognos Insight - Map Members from Different Sources to the Same Dimension

Product(s): IBM Cognos Insight; Area of Interest: Modeling

Experienced data modelers can use the techniques in this article to map members from different data sources to the same dimension during an import into Cognos Insight 10.1 or 10.2. They may be used when measures are taken from two data sources and the members from the dimensions bringing context to the measures do not match.

Share:

Sean McDowell, Software Developer, IBM

Sean McDowell is a Software Developer working for the IBM Cognos BI team. He is focused on Cognos Insight, Transformer and TM1.



09 May 2013

Introduction

Purpose of Document

This document will show techniques in IBM Cognos Insight used to map members from different data sources to the same dimension during an import. This is beneficial in scenarios where measures are taken from two data sources and the members from the dimensions bringing context to the measures do not match.

Applicability

This document applies to IBM Cognos Insight 10.1.x and 10.2.x.

Assumptions

This document assumes experience importing and modeling data using Cognos Insight.


Overview

Member mapping techniques need to be used in IBM Cognos Insight when you work with data sources that identify the same data element in different ways.

For example, you may have a data source you import into Cognos Insight that has Region values in English and Revenue measures as seen in Table 1 below.

Table 1 – Data source with Region and Revenue values
RegionRevenue
Americas977,087,880.8
Northern Europe369,539,150.96
Asia Pacific859,254,278.14
Southern Europe337,399,674.39
Central Europe2,143,494,784.56

A second data source, shown in Table 2 below, may contain Quantity measures that you are interested in but the Region field’s values are in French and do not match those of the values in the English version of Region above.

Table 2 – Data source with French Region values and Quantity values
RegionQuantity
Europe septentrionale7,131,713
Asie-Pacifique16,788,543
Europe centrale39,694,604
Amériques18,944,382
Europe méridionale6,677,849

In Cognos Insight, you can use Aliases to map the French members to the original English members before importing the second data source. This will tell Cognos Insight which member to apply the Quantity measures to during import.

In another scenario, one data set may contain information at a different grain than another.

For example, consider the same data set shown initially, the English version of Region with Revenue measures, and compare it with the following data set shown in the table in Table 3 below.

Table 3 – Data source with Regions and Quantity values
RegionQuantity
Americas18,944,382
Asia Pacific16,788,543
Europe53,504,166

Region in this data set consolidates Northern, Central, and Southern Europe into one consolidated item called Europe. This level of granularity may be what you are interested in, but if you had to import data from the set that has Europe broken out into three separate regions into this data set, you would need to map the three Europe members to the singular Europe member.

This scenario can be dealt with in Cognos Insight by using Aliases or a lookup dimension.

This document will show both techniques using the very simplified data sets shown above which are also included for download in .csv format.


Handling Alternate Members Using Aliases

The following step-by-step will illustrate how to use aliases to map members in one data source to members from another data source during an import.

To begin, we will first load the English dataset from the provided English-Region and Revenue.csv file.

  1. In Cognos Insight, from the Get Data menu, click Import Data.
  2. Click the Browse button and navigate to the English-Region and Revenue.csv file you downloaded.
  3. Click Advanced, click Show Properties, and then under Target Items, click English-Region and Revenue Measures.
  4. In the Properties pane, deselect Generate count measure. We don’t need this measure generated for the purposes of this demonstration.
  5. Click Import. A cube is created and the data is displayed in the workspace as shown in Figure 1.
    Figure 1 - Region and Revenue values displayed in a crosstab in the workspace
    Figure 1 - Region and Revenue values displayed in a crosstab in the workspace
  6. In the Data pane on the right, expand All Dimensions, right-click Region, and then click on Edit.
    Figure 2 - Right-click menu for Region dimension with Edit highlighted
    Figure 2 - Right-click menu for Region dimension with Edit highlighted
    We will now create an alias column for the Region dimension and add the French aliases for each of the members in the dimension so that the French version of Region maps correctly when we import the Quantity measures.
  7. Right-click on the Name column header, and then click Add a new attribute.
  8. In the text box enter Region French, and then from the Attribute type drop down, select Alias.
    Figure 3 - Add new attribute dialog
    Figure 3 - Add new attribute dialog
  9. Click OK.
  10. In the Region French column, edit the values to their French equivalents.
    • Americas becomes Amériques
    • Northern Europe becomes Europe septentrionale
    • Asia Pacific becomes Asie-Pacifique
    • Southern Europe becomes Europe méridionale
    • Central Europe becomes Europe centrale
    Figure 4 - Edit Dimension dialog with French aliases completed
    Figure 4 - Edit Dimension dialog with French aliases completed
  11. Click Close.
    Every time the system looks for a member it will consult both names and aliases, so no extra work needs to be done in the import to leverage the alias we just created.
    Next, we will import the Quantity values from the French data source.
  12. In the Data pane, right-click on the English-Region and Revenue cube and then click Import into cube English-Region and Revenue.
  13. Click the Browse button, navigate to the provided French-Region and Revenue.csv file, and then click Open.
  14. Click Advanced, and then click Show Properties.
  15. Under Target Item, ensure the Region dimension is selected, and then in the Properties pane, click on Advanced.
    Figure 5 - Properties pane displaying properties for the Region dimension
    Figure 5 - Properties pane displaying properties for the Region dimension
  16. From the Member Update Behavior drop down, select Do not update. This ensures that only existing members in the dimension are referenced and no new ones created.
  17. Under Target Item, click English-Region and Revenue Measures, and then in the Properties pane, deselect Generate count measure.
  18. Click Import, click OK to the import warning and then click Import again. As shown in Figure 6, the Quantity values have been imported and mapped correctly to the Region members using the Region French aliases defined earlier.
    Figure 6 - Region, Revenue, and Quantity values displayed in a crosstab in the workspaces
    Figure 6 - Region, Revenue, and Quantity values displayed in a crosstab in the workspaces

Handling Alternate Levels of Granularity with Aliases or a Lookup Dimension

In our second scenario, we will import Quantity and Region data at a higher level for Europe. Region in this data set consolidates Northern, Central, and Southern Europe into one consolidated item called Europe. We will then import Revenue data from the set of data that has Europe broken out into three separate regions.

To do so, we will need to map the three Europe members in one data set to the singular Europe member in the other, which can be accomplished in Cognos Insight by using Aliases or a lookup dimension. Both methods will be shown.

First method: Aliases

  1. In Cognos Insight, from the Actions menu, click New.
  2. From the Get Data menu, click Import Data, click Browse, navigate to the English-Region and Quantity-Europe Consolidated.csv file provided, and then click Open.
  3. Click Advanced, click Show Properties, under Target Items, click English-Region and Quantity-Europe Consolidated Measures, and then deselect Generate count measure. We don’t need this measure generated for the purposes of this demonstration.
  4. Click Import. As shown in Figure 7, the data appears in a crosstab showing the quantity values for Americas, Asia Pacific, and Europe.
    Figure 7 - Region and Quantity values displayed in a crosstab in the workspace
    Figure 7 - Region and Quantity values displayed in a crosstab in the workspace
    We now want to import data from English-Region and Revenue.csv into our cube but need to group the various rows that reference different Europe regions into one Europe member in our Region dimension. We will use Aliases to achieve this.
  5. In the Data pane on the right, expand All Dimensions, right-click Region, and then click on Edit.
    We will now create an alias for each region of the three European regions.
  6. Right-click on the column header, and then click Add a new attribute.
  7. In the text box enter Alias 1, and then from the Attribute type drop down, select Alias.
  8. Click OK and repeat to create an alias called Alias 2 for Central Europe and an alias called Alias 3 for Southern Europe.
  9. For the Europe member, edit each of the new alias values just created. The Alias 1 column should have Northern Europe as the alias text for the Europe member, the Alias 2 column should have Central Europe as the alias text for the Europe member, and the Alias 3 column should have Southern Europe as the alias text for the Europe member.
    Figure 8 - Edit Dimension – Region dialog box with new aliases for each European region
    Figure 8 - Edit Dimension – Region dialog box with new aliases for each European region
  10. Click Close.
  11. In the Data pane, right-click the English-Region and Quantity-Europe Consolidated cube, and then click Import into cube English-Region and Quantity-Europe Consolidated.
  12. Click Browse, navigate to the English-Region and Revenue.csv file provided, and then click Open.
  13. Click Advanced, and then click Show Properties.
  14. Click English-Region and Quantity-Europe Consolidated Measures, and the in the Properties pane, deselect Generate count measure.
  15. Click Import, click OK to the warning, and then click Import again. As shown in Figure 9, the Revenue values are imported and the Europe members are mapped correctly into the single Europe member in the target Region dimension.
    Figure 9 - Region, Quantity, and Revenue displayed in a crosstab in the workspace
    Figure 9 - Region, Quantity, and Revenue displayed in a crosstab in the workspace

Second Method: Lookup Dimension

  1. In Cognos Insight, from the Actions menu, click New.
  2. From the Get Data menu, click Import Data, click Browse, navigate to the English-Region and Quantity-Europe Consolidated.csv file provided, and then click Open.
  3. Click Advanced, click Show Properties, under Target Items, click English-Region and Quantity-Europe Consolidated Measures, and then deselect Generate count measure. We don’t need this measure generated for the purposes of this demonstration.
  4. Click Import. As shown in Figure 10, the data appears in a crosstab showing the quantity values for Americas, Asia Pacific, and Europe.
    Figure 10 - Region and Quantity values displayed in a crosstab in the workspace
    Figure 10 - Region and Quantity values displayed in a crosstab in the workspace
    We now want to import data from English-Region and Revenue.csv into our cube but need to group the various rows that reference different Europe regions into one Europe member in our Region dimension. We will create a lookup dimension.
  5. From the Get Data menu, click Import Dimensions, click Browse, navigate to the English-Region and Revenue.csv provided, and then click Open.
  6. Under Select the columns to import, deselect Revenue, and then click Advanced.
  7. Click Show Properties, under Target Items, click the Region dimension, and then in Properties, rename the Region dimension to Region_Lookup.
  8. Click Import.
  9. In the Data pane, expand All Dimensions, right-click Region_Lookup, and then click Edit.
  10. In the Edit Dimension dialog, right-click the Name column header, and then click Add new attribute.
  11. In the name text box, type Lookup, and then from the Attribute type dropdown box, select Text.
    Figure 11 - Add new attribute dialog
    Figure 11 - Add new attribute dialog
  12. Click OK.
  13. In the Lookup column, enter the value Europe for the following rows,
    • Northern Europe
    • Southern Europe
    • Central Europe
    Figure 12 - Edit Dimension dialog
    Figure 12 - Edit Dimension dialog
    This will map any of the three Europe regions to the consolidated Europe member in the Region Dimension using the Lookup column from this dimension.
  14. Click Close.
    We can now import the Revenue values into the cube and map the Revenue values to the higher Europe level.
  15. In the Data pane, right-click the English-Region and Quantity-Europe Consolidated cube, and then click Import into cube English-Region and Quantity-Europe Consolidated.
  16. Click Browse, navigate to the English-Region and Revenue.csv file provided, and then click Open.
  17. Click Advanced.
    To perform the lookup we will create a calculated column.
  18. Click Add Calculated Column, in the Properties pane under Source Item, change the name from Expression to Region_Lookup_Calc, and the in the Expression box, define the following expression:
    lookupValue = ATTRS('Region_Lookup', Region, 'Lookup');
    IF(lookupValue @='');
       Region_Lookup_Calc = Region;
    ELSE;
       Region_Lookup_Calc = lookupValue;
    ENDIF;

    The expression defines a new calculated column called Region_Lookup_Calc. First the value from the Lookup text attribute for the element in the Region column is fetched into the lookupValue variable using the ATTRS function. If it is empty (@=''), there is no lookup value defined so the original element from the Region column is used; otherwise the lookupValue is used.
    Figure 13 - Properties pane with Region_Lookup_Calc expression defined
    Figure 13 - Properties pane with Region_Lookup_Calc expression defined
  19. Click the Preview link to see how the new Region_Lookup_Calc maps the new Europe value for each of the Europe regions in the Region column of the Data Preview pane. This is shown in Figure 14.
    Figure 14 - Data Preview pane showing each of the Europe regions being mapped to the single value Europe
    Figure 14 - Data Preview pane showing each of the Europe regions being mapped to the single value Europe
  20. Drag the Region_Lookup_Calc column onto the Region level in the Target Items pane.
    Figure 15 - Target Items pane with Region_Lookup_Calc mapped to the Region level
    Figure 15 - Target Items pane with Region_Lookup_Calc mapped to the Region level
    We will be using this column rather than the original Region column.
  21. Click English-Region and Quantity-Europe Consolidated Measures, and in the Properties pane, deselect Generate count measure.
  22. Click Import, click OK to the warning, and then click Import again. As shown in Figure 16, the Revenue values are imported and the Europe members are mapped correctly into the single Europe member in the target Region dimension.
    Figure 16 - Region, Quantity, and Revenue displayed in a crosstab in the workspace
    Figure 16 - Region, Quantity, and Revenue displayed in a crosstab in the workspace

Download

DescriptionNameSize
CSV sample filesCSV-Sample-Files.zip900B

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=Big data and analytics
ArticleID=910059
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos Insight - Map Members from Different Sources to the Same Dimension
publish-date=05092013