Examples Using DI-ODBC

Here are some examples using DI-ODBC.

NOTE: When an application tries to connect over ODBC, it supplies a connection string. The connection string must have SERVER, USERNAME, PASSWORD, PROJECT, and at least one (CBASE, CPLAN, or DIVE) to connect. All of these elements must be present or it cannot connect.

The following procedure was tested using Microsoft Excel 2013 and Excel 2016.

Before you can use DI-ODBC in Excel, you must create a DSN for the DI-ODBC driver (see Creating a User DSN).

Excel can save data connections as odc files, in the user's Documents\My Data Sources folder . To import data into Excel, first create the ODC file, and then insert the data into the worksheet.

To use DI-ODBC with Excel:

  1. Open Excel and select the Data ribbon.

  2. Select From Other Sources > From Data Connection Wizard.

    Excel data sources

    The Data Connection Wizard opens.

    Excel's Data Connection Wizard

  3. On the first page of the Data Connection Wizard, select ODBC DSN, and then click Next.

    The second page shows a list of DSNs. For example:

    ODBC Data Sources

    The DSN you created earlier should be in the list.

  4. Select your DSN and click Next.

    Depending on your user DSN settings, a Diveline Login dialog box might appear.

  5. If prompted for a password, enter it and click OK.

    The Select Database and Table page displays:

    Select database and table

  6. There is only one table in this connection, click Next to choose it.

    The Save Data Connection File and Finish page displays.

    Save Data Connection File

  7. Enter a File Name and a Friendly Name for the odc file, and then click Finish.

    TIP: If you do not want to use the default location, use the Browse button to position your file.

    The Import Data dialog displays.

    Import Data dialog

  8. Click OK, select the data source, and click OK to insert the table at the default location. For example:

    cBase data in Excel

When the connection is saved as an odc file, you can use it again later by selecting Existing Connections on the DATAribbon.

NOTE: When the data on the server changes, you must to refresh the data in the worksheet. On the DATA ribbon , click Refresh all.

The following procedure was tested in Microsoft Excel for Microsoft 365.

Before you can use DI-ODBC in Excel, you must create a DSN for the DI-ODBC driver (see Creating a User DSN).

You can save data connections as odc files using Excel. These files are saved in the user's Documents\My Data Sources folder by default. To import data into Excel, first create the odc file, and then insert the data into the worksheet by clicking Data > Existing Connections.

To use DI-ODBC with Excel:

  1. Open Excel and select the Data ribbon.

  2. In the Get & Transform Data section, select Get Data > From Other Sources > From ODBC.

  3. The From ODBC window opens.

  4. Select your DSN.

  5. If you have a connection string or SQL statement to define, click Advanced options, and then enter the connection string or SQL statement.

  6. Click OK.

    Depending on your user DSN settings, an ODBC driver dialog box might appear.

  7. If prompted, enter a username and password, and then click Connect.

    The Navigator page displays.

  8. There is only one table in this connection, which is contained within multiple folders. You can choose to:

    • Load—This option loads the data directly into the Excel spreadsheet.

    • Transform Data—This option opens the Power Query Editor, which allows you to transform the data before is loads into the Excel spreadsheet.

      TIP: For more information about the Power Query Editor, see Microsoft's Power Query for Excel Help page.

  9. Upon loading the data, whether by clicking Load or after using the Power Query Editor, the ODBC data populates your Excel spreadsheet:

The ODBC connection continues to be accessible for editing in this spreadsheet by clicking the Query ribbon. To make the connection accessible to other Excel spreadsheets, click Query > Export Connection File. This saves an odc file, which you can load into a spreadsheet file by clicking Data > Existing Connections.

NOTE: When the data on the server changes, you must refresh the data on the worksheet. On the Data ribbon, Queries & Connections section, click Refresh All.

The following was tested in R v3.5.2.

First, install the libraries that R needs to connect to ODBC data sources. Run the following commands:

Copy 
install.packages("DBI")
install.packages("odbc")

The following script can fetch data from the DI-ODBC driver:

Copy 
# prepare the odbc library
library(odbc)
# connect to the driver (be sure to use the right bit width for your R installation)
con <- dbConnect(odbc(), dsn="<My DSN>")
# load the data
data <- dbGetQuery(con, "SELECT * FROM data")

The following was tested in Python v3.10 with pyodbc v4.0.32.

This script can fetch data from the DI-ODBC driver:

Copy 
import pyodbc
connStr = 'DSN=<My Dsn>'
conn = pyodbc.connect(connStr)
cursor = conn.cursor()
cursor.execute('SELECT * FROM data')
rows = cursor.fetchall()
cursor.close()
conn.close()

Staring with version 7.1(7), DI-ODBC supports Tableau.

The following was tested in Tableau Desktop version 2019.3.

  1. In the Connect panel, under To a Server, select More > Other Databases (ODBC).
  2. In the DSN pull-down, select your DSN, and then click Connect.
  3. If the connection attributes look correct, click Sign In.
  4. In the Databaselist , select master.

  5. When working with a cPlan, you need a custom SQL query—see Tableau and cPlans below.

    When working with a cBase or Dive script, you can access the table directly:

    In the Table box, type data, and then press Enter.

  6. Double-click the data table.

  7. In the toolbar on the bottom of the window, switch to Sheet1.

    A dialog appears telling you to Create a Tableau data extract to use the full functionality of Tableau.

    NOTE: The Tableau data extract does not work against the data table of a cPlan.

  8. Click OK.

    You can now create a Tableau worksheet using the data collected from ODBC.

Tableau's data extract feature is not compatible with the "data" table of cPlan source types in DI-ODBC. Tableau expects to extract a single flat table from a cPlan, but because a cPlan can have multiple inputs, there is no single table of data to send.

Also, summary calcs in a cPlan (and Measures in a Measure Factory) are not stored in tables. They are summary operations and are implemented by run-time expressions that operate on the cBase columns (or Rules). DI-ODBC exposes these through the calc() function, but the calc function is incompatible with Tableau, and there is no way to set up a measure in Tableau to use that function. This means that Tableau cannot find these calculations or measures.

In order to get calculation or measure data from a cPlan or Measure Factory, you must use a custom SQL query. After selecting the master database, double-click New Custom SQL and enter a query into the dialog. For example:

Copy 
-- Total Admissions, Rolling 12 Months
SELECT CALC("Total Admissions") AS "Total Admissions" 
FROM data 
WHERE EVAL('rolling(12, value("Date"), date("2018/05/28"))')

or

Copy 
-- Total Admissions, by Facility and Month
SELECT Facility, 
       "Admit Year-Month", 
       CALC("Total Admissions") AS "Total Admissions" 
FROM data 
GROUP BY Facility, "Admit Year-Month"

You can extend this to select a large number of measures over a large number of dimensions, and then use Tableau's engine to resummarize them. Note however, that some calculations cannot be easily resummarized—For example measures that compute a ratio or average.

Starting with version 7.1(7), DI-ODBC supports Power BI.

The following was tested in Power BI Desktop 2.74.5619.862 64-bit.

  1. Click Get Data. In the Other tab, choose ODBC.

  2. When working with a cPlan, expand Advanced options and create a custom SQL query—see Power BI and cPlans below. If you have not set all connection properties in the DSN, be sure to set the connection string properties here in the Advanced section.

    When working with a cBase or Dive script, select your DSN in the DSN pulldown and click OK.

  3. Enter your logon credentials in the next page. Under master > public, select the data table.

    You can now create a Power BI page using the data collected from DI-ODBC.

Summary calcs in a cPlan (and Measures in a Measure Factory) are not stored directly in tables. They are summary operations and so are implemented by run-time expressions that operate on the cBase columns (or Rules). DI-ODBC exposes these through the calc() function, but Power BI does not know about that function and there is no way to set up a measure in Power BI to use that function. This means that Power BI cannot find these calculations or measures.

In order to get calculations or measure data from a cPlan or Measure Factory, you need to use a custom SQL query. After choosing the DSN, expand Advanced options and enter a query into the dialog. For example:

Copy 
-- Total Admissions, Rolling 12 Months
SELECT CALC("Total Admissions") AS "Total Admissions" 
FROM data 
WHERE EVAL('rolling(12, value("Date"), date("2018/05/28"))')

or

Copy 
-- Total Admissions, by Facility and Month
SELECT Facility, "Admit Year-Month", 
       CALC("Total Admissions") AS "Total Admissions" 
FROM data 
GROUP BY Facility, "Admit Year-Month"

You can extend this to select a large number of measures over a large number of dimensions, and then use Power BI's engine to re-summarize them. Note however, that some calculations cannot be easily re-summarized—for example measures that compute a ratio or average.

If the DI-ODBC configuration is missing parameters, you can use the advanced settings to supply the remaining parameters.

  1. Create a DSN for DI-ODBC. Fill in as many fields as you reasonably can.

    For instance, suppose you want to use the DSN to connect to a specific PROJECT, but you do not want to set the FILE property (because you want to use the same DSN for different files in that project). You can also omit USERNAME and PASSWORD and let Power BI manage the credentials for you. For example, you set the SERVER, PORT, and PROJECT fields.

  2. In Power BI, click Get data > More > Other > ODBC > Connect.

  3. In the From ODBC dialog, select the DI-ODBC DSN from the list. Do not click OK yet.

  4. Open Advanced options. Under Connection string, enter any fields you did not specify in the DSN.

    In this case you did specify PROJECT, but you did not choose a FILE. The connection string property for FILE depends on the file type. If you are going to open a cBase, set the CBASE property. For example:

    CBASE=/path/to/my.cbase

    If you also need to specify PROJECT, for example:

    PROJECT=My Project;CBASE=/path/to/my.cbase

    IMPORTANT: The key is to specify here any "non-credential" properties that were not specified in the DSN. You could also set SERVER and PORT here. If the DSN sets all of the properties, then you do not need to set anything here.

  5. Click OK. It will now ask for your credentials.

  6. Once you log in, the Navigator dialog displays. Select a table to import.