IBM Business Analytics Proven Practices: IBM Cognos Insight - Import and Join Multiple Excel Worksheets Together

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

In this document, you will learn how to import multiple data sets from a Microsoft Excel file into IBM Cognos Insight through an ODBC connection, edit the structure of the data, and then view your results in an IBM Cognos Insight workspace.

Business Analytics Proven Practices Team, Business Analytics Proven Practices Team, IBM

Business Analytics Proven Practices Team



09 January 2014

Introduction

Purpose of Document

In this document, you will learn how to import multiple data sets from a Microsoft Excel file into IBM Cognos Insight through an ODBC connection, edit the structure of the data, and then view your results in an IBM Cognos Insight workspace.

Applicability

The techniques in this document were written and tested with IBM Cognos Insight 10.2.1, but apply to earlier versions as well.

Assumptions

This document assumes knowledge of basic IBM Cognos Insight modeling.


Overview

Although importing from an Excel file is nothing new for IBM Cognos Insight, in this case you will take advantage of multiple datasets on separate worksheets within the Excel file and use IBM Cognos Insight to join these separate datasets together.

In this scenario, the IT department has provided you with a Microsoft Excel file containing datasets from various data sources (a file called GO Master Data.xls has been included with this document). Each of these datasets is on a separate worksheet in the Excel file. The data contains all the required fields to link the datasets together. You will use an ODBC connection to this Excel file and then import from that connection using IBM Cognos Insight. During the import, you will add structure to the data so that you can analyze your sales and targets numbers for products over time.

Note about working with ODBC drivers

As of Cognos Insight 10.2.1, there are both 32-bit and 64-bit versions of the product for Windows. For the 64-bit version of the product, you can currently only leverage 64-bit ODBC drivers. If the Cognos Insight 32-bit version is installed on a 64-bit Windows operating system, then by default, the data component will run in 64-bit mode. However, it can be configured to run in 32-bit mode if required.

For example, if you install Cognos Insight on a 64-bit version of Microsoft Windows, but are using 32-bit ODBC drivers to connect to your data source, you will need to configure your ODBC connection using the 32-bit ODBC Data Source Administrator console (<system_drive>\Windows\SysWoW64\odbcad32.exe) and then configure your Cognos Insight data component to run in 32-bit mode.

To do so, go to the Cognos Insight installation location which can be located by looking at the Target property in the Properties dialog of the Cognos Insight shortcut located on your desktop.

  • Open the configurations folder, and then open the config_10.2.xxxx.x folder.
  • Open the file config.ini in a text editor such as Notepad and add the following line to the end of the file:
    DTM1_DISABLE_64BIT=true
  • Save and close the file.
  • Start Cognos Insight and proceed to import the relational data source using the 32-bit ODBC connection you defined.

To connect to 64-bit ODBC connections, in the config.ini file, either delete the line that reads DTM1_DISABLE_64BIT=true or change the value to false (recommended).

For more information refer to the IBM Technote titled IBM Cognos Insight Cannot Connect to 32-bit ODBC Driver on a 64-bit Microsoft Windows Platform at http://www.ibm.com/support/docview.wss?uid=swg21616205.


Import Data

In this section you will set up an ODBC connection to the provided Excel file containing multiple worksheets of data and then work with the contents of that file in IBM Cognos Insight using the import wizard. During the import wizard, you will join data from the multiple worksheets together with relationships defined in the Query Builder.

  1. Download and save the GO Master Data.xls file accompanying this document to your local machine.
  2. Create an ODBC connection to this file and name the connection GO Master Data. Please refer to Microsoft ODBC help documentation if required.
  3. In IBM Cognos Insight, from the Get Data menu, click Import Data.
    Figure 1: Get Data Menu displaying import options including Import Data
    Figure 1: Get Data Menu displaying import options including Import Data
  4. In the Import name box, change New Import 1 to GO Master Data.
  5. From the Type drop down list, select Relational Data Source (ODBC). The Connection Editor dialog box appears.
  6. From the Data source name drop down list, select GO Master Data (this is the ODBC connection that you just created).
  7. Click Test connection to verify connectivity, and then click OK twice.
    Figure 2: Import Data menu displaying options and configuration just performed
    Figure 2: Import Data menu displaying options and configuration just performed
  8. Click the Open Query Builder button.
  9. In the Metadata Explorer pane, expand the location for the GO Master Data.xls file, and then expand Tables.
    Here each worksheet in the Excel file is represented as a table as shown in Figure 3.
    Figure 3: Query Builder window displaying Products$, Sales$, Time$, and Sales Target$ under Tables
    Figure 3: Query Builder window displaying Products$, Sales$, Time$, and Sales Target$ under Tables
  10. Drag the Products$ and Time$ tables to the Data View pane.
  11. In the Metadata Explorer pane, expand Sales$ and ‘Sales Targets$’, and then drag Revenue and Sales Target to the Data View pane.
    The columns from the tables appear in the Data View pane and the tables appear in the Query Diagram pane as shown in Figure 4.
    Figure 4: Query Builder displaying selected columns in the Data View pane and tables in the Query Diagram pane
    Figure 4: Query Builder displaying selected columns in the Data View pane and tables in the Query Diagram pane
    Notice the red x on the Issues tab stating there are 4 issues.
  12. Click the Issues tab.
    There are four messages indicating the tables do not have joins to any other tables.
    You will create relationships between the tables in the Query Diagram and then test the data. Do not click the Refresh button until you have defined your joins otherwise the query will create a large cross join between all the tables that takes a long time to return.
  13. Click the Data View tab, in the Query Diagram, click Products$, ctrl-click Sales$, right-click one of the selected objects, and then click Create Join.
  14. In the Create a new join dialog, click the green plus button to add a new join.
  15. Click in the left column, and then from the drop down list, select Product Type Key.
  16. Click in the right column, and then from the drop down list, select Product Type Key.
    Figure 5: Create new join dialog with newly configured join
    Figure 5: Create new join dialog with newly configured join
  17. Click OK, and then create the following relationships between the other tables.
    • Products$ to SalesTargets$ on Product Type Key
    • Time$ to Sales$ on Month Key
    • Time$ to SalesTargets$ on Month Key
    You can arrange the diagram as you wish.
    Figure 6: Diagram pane showing tables with the join relationships defined between them
    Figure 6: Diagram pane showing tables with the join relationships defined between them
  18. Click the Refresh button to verify the data.
    The data appears as expected as shown in Figure 7.
    Figure 7: Data view pane displaying data retrieved from the Excel file
    Figure 7: Data view pane displaying data retrieved from the Excel file
    There are two new issues noted on the Issues tab. These are warnings about the cardinality potentially bringing back more rows than expected for the Products$ and Time$ tables. In this case, the cardinality is correct.
    You can also click on the SQL View tab to see the SQL that will be used to retrieve the data.
  19. Click OK.
    The SQL also appears in the SQL Query pane in the Import Data dialog. Here you can customize the SQL to add filters or calculations if required.
    Figure 8: Import Data window SQL Query pane showing SQL used to retrieve data from the Excel file
    Figure 8: Import Data window SQL Query pane showing SQL used to retrieve data from the Excel file

Add Structure to the Data

In this section you will add structure to the data before import by defining dimensions and measures.

  1. At the bottom of the dialog, click the Advanced button.
    On this screen, you see a Data Preview section and a Mapping section as shown in Figure 9. In the Mapping section, you can customize the structure and properties of the data before it is imported.
    Figure 9: Import Data window Data Mapping screen
    Figure 9: Import Data window Data Mapping screen
    In this case, you will clear the default mappings and start from scratch as you wish to use the business keys as the invariant values and the user friendly strings, such as Product Line, as the captions.
  2. Click the Clear All Mappings button at the bottom of the Source Items pane.
  3. Click the Show properties link in the bottom right corner, and then rename the Target cube name to GO Sales and Targets.
    Figure 10: Data Mapping screen showing cleared mappings and new cube name just entered
    Figure 10: Data Mapping screen showing cleared mappings and new cube name just entered
  4. From the Source Items pane, drag Product Line Code onto the GO Sales and Targets cube in the Target Items pane.
    Figure 11: Mapping pane showing Product Line Code as a new dimension name as well as a level name
    Figure 11: Mapping pane showing Product Line Code as a new dimension name as well as a level name
  5. In the Properties pane, change the Level name from Product Line Code to Product Line.
  6. From the Source Items pane, drag Product Line onto the Product Line level in the in the Target Items pane.
    This item becomes the caption for the Product Line level as shown in Figure 12.
    Figure 12: Mapping pane showing Product Line as the caption for the Product Line level
    Figure 12: Mapping pane showing Product Line as the caption for the Product Line level
  7. Repeat this process to create a Product Type level using Product Type Key as the level mapping and Product Type as the caption. You will start by dragging Product Type Key onto the Product Line Code dimension.
    Figure 13: Mapping pane showing the Product Type level and caption
    Figure 13: Mapping pane showing the Product Type level and caption
    Next, you will edit some properties for this dimension.
  8. In the Target Items pane, click on the Product Line Code dimension, and then in the Properties pane, change the Dimension name from Product Line Code to Products.
  9. Under Advanced, deselect Include the names of parent elements, from the Element sorting drop down, select By Name, and then from the Component sorting drop down, select By Name.
    Now you will create the Time dimension.
  10. From the Source Items pane, drag Year onto the GO Sales and Targets cube in the Target Items pane.
    Figure 14: Mapping pane showing Year dimension and level
    Figure 14: Mapping pane showing Year dimension and level
  11. Drag Quarter Key onto the Year dimension, and then drag Quarter onto the Quarter Key level.
    Figure 15: Mapping pane showing Quarter Key level and caption
    Figure 15: Mapping pane showing Quarter Key level and caption
  12. Rename the Quarter Key Level to Quarter.
  13. Drag Month Key onto the Year dimension, and then drag Month onto the Month Key level.
  14. Rename the Month Key Level to Month.
    Figure 16: Mapping pane showing the full hierarchy of the Year dimension starting with year, followed by Quarter, and ending in Month
    Figure 16: Mapping pane showing the full hierarchy of the Year dimension starting with year, followed by Quarter, and ending in Month
  15. Select the Year dimension, and then in the properties pane rename the dimension to Time.
  16. Deselect Include the names of parent elements.
  17. From the Dimension type drop down list, select Time, and then for Element sorting and Component sorting, select By Name.
    You will now map the measures for your cube.
  18. Drag Revenue and Sales Target onto GO Master Data Measures in the Target Items pane.
  19. Select GO Master Data Measures, and then in the Properties pane, change the Dimension name to GO Sales and Targets Measures and deselect Generate count measure.
    The Target Items pane appears as shown in figure 17.
    Figure 17: Mapping pane showing Revenue and Sales Target added to the GO Sales and Targets Measures
    Figure 17: Mapping pane showing Revenue and Sales Target added to the GO Sales and Targets Measures
  20. Click the Import button.
    The data appears in the workspace.
    Figure 18: Workspace showing the imported data in a crosstab as well as a chart
    Figure 18: Workspace showing the imported data in a crosstab as well as a chart

Using an ODBC connection to an Excel spread sheet with multiple worksheets, you were able to import and join multiple data sets and add dimensional structure to it.


Download

DescriptionNameSize
Code samplehttp://public.dhe.ibm.com/software/dw/dm/cognos/modeling/insight/GO_Master_Data.zip32KB

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=959667
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos Insight - Import and Join Multiple Excel Worksheets Together
publish-date=01092014