Defining relational queries in Excel

This capability was removed in v2.0.8, documentation provided for customers on v2.0.7 and prior. Connect to Microsoft SQL, IBM® Db2®, and Oracle databases in Excel to define queries that will run in TM1® Web.

Before you can run a relational query in TM1 Web, you must author the query in Microsoft Excel.

For more information about querying a relational database using Microsoft Excel, see the documentation that was supplied with the Excel software.

Creating a query of MS SQL data

Note: You do not need to install MS SQL Server OLE drivers; they are already installed with Microsoft Office.
  1. In Microsoft Excel, go to the Data tab > Get External Data > From Other Sources > From SQL Server.
  2. Enter the URL for the MS SQL Server database and then enter the user name and password.
  3. Select a database and then select a table from the list.
  4. Create the query.
    1. Click Properties > Definition tab.
    2. Change the command type to SQL.
    3. Enter SQL commands in the Command text box.
    4. Optionally, add parameters to your query.

      For more information, see Creating a parameterized query in Excel.

      Note: You cannot validate the SQL query while you are creating it.
  5. Optionally, modify and format the data in Excel.
    Note: Most formatting will be retained when you upload the file to TM1 Web. However, table formatting is not retained.
  6. Save the Excel worksheet.

Creating a query of Db2 data

Important: Before you can connect to an IBM Db2 database from Excel, you must install the latest Db2 OLE drivers. For more information, see the IBM Support Portal (http://www.ibm.com/support/entry/portal/support).
  1. In Microsoft Excel, go to the Data tab > Get External Data > From Other Sources > From Data Connection Wizard.
  2. Click Other/Advanced and then click Next.
  3. Select the Db2 OLE driver that you previously installed and then click Next.
  4. Select Direct server connection.
  5. Enter the server name and ODBC port number as follows:

    server_name:ODBC_port_number

  6. Select a database from the list and then enter a user name and password.
  7. Select a table from the list and re-enter your user name and password, if required.
  8. Create the query.
    1. Click Properties > Definition tab.
    2. Change the command type to SQL.
    3. Enter SQL commands in the Command text box.
    4. Optionally, add parameters to your query.

      For more information, see Creating a parameterized query in Excel.

      Note: You cannot validate the SQL query while you are creating it.
  9. Optionally, modify and format the data in Excel.
    Note: Most formatting will be retained when you upload the file to TM1 Web. However, table formatting is not retained.
  10. Save the Excel worksheet.

Creating a query of Oracle data

Important: Before you can connect to an Oracle database from Excel, you must install the latest Oracle OLE drivers. For more information, see the Oracle web site (http://www.oracle.com).
  1. In Microsoft Excel, go to the Data tab > Get External Data > From Other Sources > From Data Connection Wizard.
  2. Click Other/Advanced and then click Next.
  3. Select the Oracle OLE driver that you previously installed and then click Next.
  4. Select Direct server connection.
  5. Enter the server name, ODBC port number, and net service ID as follows:

    server_name:ODBC_port_number/net_service_id

    Note: An error message may appear that ends with the following line:

    IO Error: Invalid connection string format, a valid format is: "host:port:sid"

    Despite this error message, the original connection was successful and the only syntax that will work is host:port/sid.

  6. Select a database from the list and then enter a user name and password.
  7. Select a table from the list and re-enter your user name and password, if required.
  8. Create the query.
    1. Click Properties > Definition tab.
    2. Change the command type to SQL.
    3. Enter SQL commands in the Command text box.
    4. Optionally, add parameters to your query.

      For more information, see Creating a parameterized query in Excel.

      Note: You cannot validate the SQL query while you are creating it.
  9. Optionally, modify and format the data in Excel.
    Note: Most formatting will be retained when you upload the file to TM1 Web. However, table formatting is not retained.
  10. Save the Excel worksheet.

You can now work with your relational data in TM1 Web. For more information, see Uploading a relational query to TM1 Web.