RegisterLog In to Jazz.net dW

Integrating a Microsoft Excel spreadsheet with Rational Insight (live data model)

Introduction

This article will show you how to add a Microsoft® Excel spreadsheet as a data source and integrate it with IBM Rational Insight via a live data model. In this example, we are using Excel 2007 and Windows 2003 Server. Note: There is no official Microsoft Excel ODBC driver for Linux platforms.

Scenario

We need to report on some "Request" data that is currently stored in an Excel spreadsheet. We will walk through the steps needed to modify the Excel spreadsheet to allow Insight to read its data in table form. Then we will add that spreadsheet as a data source on our Insight server, expose that data and create/publish a package in IBM Cognos Framework Manager to make the data available to report designers.

  1. Before you can integrate the data from a Microsoft Excel spreadsheet, you must have:
    • An Excel Spreadsheet saved locally on your IBM Rational Insight Report Server (we'll call our spreadsheet "DataSheet1")
    • Defined tables (names) in that spreadsheet (Note: This is so Insight can read the data in table form)
      1. Highlight the necessary data and select Define Name

      2. Enter a table name for this data (we'll call our table DataSheet1_table)

  2. The Excel ODBC driver allows Insight to treat an Excel spreadsheet as an ODBC data source. Here, you need to define the Excel ODBC driver on your Insight report server:

    1. Choose the System DSN tab, then click Add

    2. Select the Excel Driver and click Finish

    3. Give your new data source a name, and then click Select Workbook

    4. In the right window, drill to the directory of your Excel sheet's location and you will see the spreadsheet in the left box (DataSheet1)

    5. You will now see your new data source for your Excel spreadsheet listed in the System DSNs list. This means it is now accessible to Insight as an ODBC data source.

  3. Now launch the IBM Cognos Framework Manager tool (Note: Insight requires exclusive rights to the Excel file, so make sure that the Excel file is not open and is not being used by any other process)
    1. Create a new Project (you can optionally include this new exposed "Request" data in an existing Framework Project (by selecting Open a Project), but in this example, we walk you through creating a new Project)
      1. File > New Project

      2. Give a name for your new Project and click Ok
      3. Select your Language of choice and click Ok

      4. Select Data Sources (as this is the type of metadata source we are accessing) and click Next

      5. Because this is a new data source that we just created, we need to add this data source to IBM Cognos 8's list of data sources
      6. Click New

      7. Click Next

      8. Give this data source a name and click Next

      9. Select ODBC as the Type of connection and click Next

      10. Now you must enter the name of the ODBC data source (this needs to be the name that you defined in the System DSNs, which in this example is ExcelTest)

      11. You can test the connection here by clicking the Test the connection link at the bottom of this window

      12. Make sure your dispatcher is set to your local machine (default) and click Test

      13. If everything has been done correctly, then you should see the Succeeded screen below. Once you see this, you may click Close

      14. On the next screen, click Close again, and then on the next screen, click Finish. You should now see this screen

      15. Click Close
    2. Select Your 'Data Source'
      1. This will take you back to the Select Data Source screen and you will now see your new data source in the list. Select My Excel Source then Click Next

      2. Expand the Tables node and you will find your defined tables here. In our example, we only have one table defined (DataSheet1_table) which contains our "Request" data. If you expand this node, you will see all of the columns in this table. Select the DataSheet1_table and click Next

      3. On this next screen, click Import at the bottom

      4. You should see the screen below, then click Finish

      5. Once in Framework Manager, right-click on Packages and select Create then Package

      6. Give the package a name and click Next

      7. Ensure that all of your wanted objects are selected and click Finish

      8. You should see this screen below. Now click Yes to publish your package to Insight

      9. Select where you want to save your package in Insight (we selected Public Folders) and click Next

      10. On the next screen, simply click Next

      11. On the next screen, click Publish

      12. When it has completed publishing, click Finish then Save your Project

Summary

You now have added your Excel spreadsheet as a datasource, integrated it with Rational Insight and published a package so that you can report off of your "Request" data in Rational Insight's Report Studio & Query Studio tools. You should treat these packages like business as usual and can go into the reporting tools and start to report off of this data.

Feedback
Was this information helpful? Yes No 2 people rated this as helpful.