ODBC Data Sources

ODBC is an open program interface for accessing various databases: Oracle, MS SQL, MS Access, MS Excel, etc.

It allows importing KPI values from external data sources to ELMA.

Requirements for working with ODBС

To successfully create and work with ODBC data sources, the following prerequisites must be met:

the ODBC source (database or files, you are going to import data from) must be on the same computer, as the ELMA server;
the ODBC data source and the ELMA server must be started by the same PC user;
the KPI names in the data source must exactly match the KPI names in ELMA;
If you import data to the values of a personal KPI, then in the file you must specify the ELMA user login next to each respective personal KPI value;
when creating an ODBC data source in ELMA, the data source file must be closed.

Attention!

ODBC data source is available only to the user, who created it.

Importing data from an external source to ELMA includes two steps:

Consider the following example: import data from the "Revenue.xls" file to the values of the "Revenue (Products)" and "Revenue (Services)" KPIs (fig. 1).

Fig. 1. "Revenue.xls" file.

Creating an ODBC data source in Windows

To create an ODBC data source in Windows:

go to Start – Control Panel – Administrative Tools – Data Sources (ODBC). A window with the list of ODBC data sources (fig. 2) will open. On the User DSN tab, click Add...

Fig. 2. Window with the list of ODBC data sources.

in the opened window select the driver of the application, where the data source was created (fig. 3). Since the data will be imported from an xls file, select Microsoft Excel Driver (*.xls). Click Finish.

Fig. 3. List of drivers.

in the opened window (fig. 4), enter the data source name, which will be displayed in the list of ODBC data sources (fig. 2). Use the Select Workbook... button to select the file with the required KPI data and click OK.

Fig. 4. Selecting a file.

Click ОК. Thr ODBC data source will be displayed in the ODBC Data Source Administrator window.

Fig. 5. Created data source.

Creating an ODBC data source in ELMA Designer

To create a data source in ELMA Designer, open the KPI tab, go to the Collect Data section, right click on ODBC Data Sources and select Create (fig. 6).

Fig. 6. Creating an ODBC data source

The Create ODBC Data Source window will open (fig. 7). It consists of three tabs. In this window, you need to fill in the required fields and click OK.

Fig. 7. Creating an ODBC data source in ELMA Designer.

Settings Tab

Name – name of the data source.

Data Source – select a data source. In this example, the "Revenue.xlsx" file must be selected. Click

and in the opened window select the required data source. This window displays the list of ODBC data sources created in Windows (fig. 2).

Query – here you can enter an SQL query, which selects data from the data source.

- checks if the query is correct. If the query contains errors, the Errors unit displays them. When checking a query, the selected data source must be closed.

- start emulation of the query. A query will be started, but the database and the KPI data will not be changed. During the emulation the console displays import details.

- import data. The query is executed and data are imported to the KPI values.

Loading Tab

Fig. 8. Loading tab.

Indicator

Select in the drop down list:

Loading one KPI – select this option if you want to load data to one KPI. Select the KPI from the list with the

button.

Loading multiple KPIs – select this option if you want to load data to several KPIs. In the Select Column field, select the column of the data sources that contains the KPI names.

In this example, data will be imported in several KPIs.

Data

Plan – select the column from the data source that contains the planned KPI values.

Fact – select the column from the data source that contains the fact KPI values.

Date – select the column from the data source that contains the values dates.

Responsible – select the column from the data source that contains the users responsible for KPIs (for personal KPIs).

The Plan and Fact fields cannot be empty at the same time. You have to enter the value for at least one of them.

Table Tab

This tab shows the data that will be imported in ELMA.

Fig. 9. Table tab.

Editing an ODBC data source

An ODBC data source is edited on its page. This page has three tabs that are similar to the tabs in the source creation window.

There are two ways to open an ODBC data source page:

Go to the Data Collection section on the KPI tab. Open Data Source – ODBC Data Sources in the context menu of the data source and click Edit Properties (fig. 10);

Fig. 10. Editing an ODBC source.

Deleting an ODBC data source

To delete an ODBC data source, right click on the required data source in the tree and select Delete in the context menu.

Fig. 11. Deleting an ODBC source.