IBM Business Analytics Proven Practices: IBM Cognos Insight - Sourcing Data from Macro-Enabled Spreadsheets

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

This document will illustrate steps to source data for IBM Cognos Insight from a macro-enabled Microsoft Excel spreadsheet.

Share:

Prakash Dewan, Business Analytics Professional, IBM

photo of Prakash DewanPrakash Dewan is a member of IBM Business Analytics team at IBM Software Labs, in Pune, India. He works on identification and execution of cross-product, system level, customer centric tests for IBM Business Analytics offerings. Prakash holds a masters degree in Computer Applications and has deep skills in developing automation frameworks.


developerWorks Contributing author
        level

11 December 2012

Introduction

Purpose of Document

This document will illustrate steps to source data for IBM Cognos Insight from a macro-enabled Microsoft Excel spreadsheet.

Applicability

IBM Cognos Insight 10.x.

Assumptions

This document assumes that the IBM Cognos Insight client machine has Microsoft Excel 2007 or 2010 installed.


Overview

Most organizations use macros heavily in their Microsoft Excel spreadsheets. When such spreadsheets, with the .xlsm file extension, are dropped into an IBM Cognos Insight workspace, a message saying “Unable to open the file because either the file format is not supported or Cube Import is not enabled” appears as shown in Figure 1.

Figure 1: IBM Cognos Insight error when importing an xlsm file
Figure 1: IBM Cognos Insight error when importing an xlsm file

This is because IBM Cognos Insight does not support macro-enabled Excel spreadsheets (*.xlsm) as a data source. In such cases, one option is to save the workbook as an .xls or .csv file and import that into IBM Cognos Insight. Another option is to create a name range in the spreadsheet (similar to defining a table structure) and then import the data in the name range through an ODBC connection. This second option is what we will look at in this document.


Define Name Range(s) in Microsoft Excel Spreadsheets

The steps in this section specify the steps to create one defined name range, however any number of name ranges can be defined. Please refer to the Microsoft Excel documentation for version specific instructions on defining name ranges. In this example, Microsoft Excel 2007 is used.

  1. Open the Microsoft Excel spreadsheet containing the macros.
  2. Select the cell range that you want to import data from, select the Formulas tab, and then click Define Name in the Defined Names group as illustrated in Figure 2.
    Figure 2: Select data for name range
    Figure 2: Select data for name range
  3. In the Name box, type All_Data (this can be any name you would like) as depicted in Figure 3, and then click OK.
    Note: The first character must be a letter, an underscore, or a backslash. No spaces are allowed in a range name. Also, the name should not be similar to a cell address (i.e. A11, AB10 etc.).
    Figure 3: New Name range dialog
    Figure 3: New Name range dialog
  4. To ensure that the name range is defined, click the name drop-down box on the left end of the formula bar as illustrated in Figure 4, and verify that ALL_Data is one of the entries in the list.
    Figure 4: Verify name range creation
    Figure 4: Verify name range creation
  5. Save the file.

Create ODBC Connection for Microsoft Excel Spreadsheet

This section describes step-by-step instructions for defining an ODBC DSN for the Microsoft Excel spreadsheet in the previous task.

  1. In Windows, open Control Panel, click Administrative Tools, click Data Sources (ODBC) to open the ODBC Data Source Administrator as illustrated in Figure 5.
    Figure 5: The ODBC Data Source Administrator
    Figure 5: The ODBC Data Source Administrator
    If you are on a 64-bit version of Microsoft Windows and are using a 32-bit version of Microsoft Office, by default, you will not be able to connect to Excel through ODBC. This is because the IBM Cognos TM1 portion of IBM Cognos Insight will, by default, run in 64-bit mode on a 64-bit OS and a 64-bit application cannot connect to a 32-bit ODBC driver. Currently, there are two remedies to this situation. The first is to use a 64-bit version of Microsoft Office, which was introduced in Office 2010. You can also configure the IBM Cognos TM1 portion of IBM Cognos Insight to run in 32-bit mode thereby allowing you to connect to 32-bit ODBC drivers. To do this:
    • Go to the IBM Cognos Insight install location (can be located by looking at the Target property in the Properties dialog of the IBM Cognos Insight shortcut located on your desktop).
    • Open the configurations folder and then open the config_10.2.xxxx.x folder.
    • Open 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.
    You will then need to define the ODBC connection using the 32-bit version of ODBC Data Source Administrator located at %systemdrive%\Windows\SysWoW64\odbcad32.exe. Please refer to Microsoft documentation for more information on ODBC Data Source Administrator.
  2. In the ODBC Data Source Administrator, click the User DSN tab if you want the data source to be visible only to you on this machine, or click the System DSN tab if you want the data source to be visible to others on this machine and on the network. Click Add.
    The Create New Data Source dialog box appears as shown in Figure 6.
    Figure 6: Create New Data Source dialog
    Figure 6: Create New Data Source dialog
  3. In the Create New Data Source dialog box, click Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb), and then click Finish.
    The ODBC Microsoft Excel Setup dialog box is displayed.
  4. In the Data Source Name: field, type a data source name, in this case we will use ExcelODBCForCI as shown in Figure 7, and then click Select Workbook to browse to and select the required workbook.
    Figure 7: ODBC Microsoft Excel Setup dialog
    Figure 7: ODBC Microsoft Excel Setup dialog
  5. From the Version drop down list, select the appropriate Microsoft Excel version, and then click OK.
  6. Ensure that the new DSN is created, and then click OK to close the ODBC Data Source Administrator dialog.

Import ODBC Microsoft Excel Data into IBM Cognos Insight

This section describes the step-by-step process of importing data from a macro-enabled Microsoft Excel spreadsheet into IBM Cognos Insight through ODBC.

Once name range(s) are defined in the spreadsheet and the ODBC DSN pointing to the spreadsheet is created, users can import data from the spreadsheet into IBM Cognos Insight with the following steps.

  1. Launch IBM Cognos Insight.
  2. Click Get Data, and then click Import Data..., as shown in Figure 8, to open Import Data wizard.
    Figure 8: Import Data option under Get Data menu item
    Figure 8: Import Data option under Get Data menu item
  3. From the Type drop-down list, select Relational Data Source (ODBC) as shown in Figure 9.
    Figure 9: Import Data wizard with Relational Data Source (ODBC) selected
    Figure 9: Import Data wizard with Relational Data Source (ODBC) selected
    The Connection Editor dialog appears.
  4. From the Data Source Name drop-down list, select the ODBC DSN created in the previous section, in this case ExcelODBCForCI, as shown in Figure 10.
    Figure 10: Connection Editor dialog with ExcelODBCForCI DSN selected
    Figure 10: Connection Editor dialog with ExcelODBCForCI DSN selected
  5. You may test the connection using the Test Connection button, and then click OK to close the Connection Editor dialog.
    Please note, Query Builder cannot be used for such ODBC sources, and the SQL Query needs to be written manually.
  6. For our example, enter the SQL query SELECT * FROM ALL_Data and then click the Refresh button to preview the data.
    The data appears in the Data Preview pane as shown in Figure 11.
    Figure 11: Data Preview pane showing data retrieved from the macro-enabled Microsoft Excel workbook
    Figure 11: Data Preview pane showing data retrieved from the macro-enabled Microsoft Excel workbook
    One can also import selected columns as well. For example, the SQL query SELECT Year, "Product line", Revenue FROM ALL_Data will return data for the specified columns only, as shown in Figure 12.
    Figure 12: SQL Query returning only Year, Product line and Revenue in the Data Preview pane
    Figure 12: SQL Query returning only Year, Product line and Revenue in the Data Preview pane
    Once the data has been previewed, you can proceed with modeling and importing data into the IBM Cognos Insight workspace as usual.

Conclusion

Leveraging the ODBC connection to macro-enabled excel spreadsheet (*.xlsm), IBM Cognos Insight users can not only import data from such unsupported spreadsheets, but can define multiple name ranges in Microsoft Excel to simply import different sets of data from the same spreadsheet.

The steps described in this document are not restricted to the macro-enabled spreadsheets (*.xlsm). The same can also be leveraged with standard Excel spreadsheets (*.xls, *.xlsx) as well.

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=850515
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos Insight - Sourcing Data from Macro-Enabled Spreadsheets
publish-date=12112012