Import Data for an Indicator From External Database with SQL Query

In BSC Designer users can setup indicators and access 3rd party data sources (like databases of CRM or ERP systems) using SQL-queries. This part of the manual explains how to do it step-by-step and provides some examples.

Note:

Use SQL query as a data source for an indicator

Here are the steps that one can follow to create a scorecard using external data for KPI:

Creating connection string

  1. Launch BSC Designer
  2. Create an indicator
  3. Click Data source button "KPI Data Source" button
  4. Select "Link to SQL Database"
  5. Select a database with local or Web access
  6. Click the "Build" button to create a connection string.
  7. The first required step is to specify a data source. Please note: the number of available data sources depends on the ADO drivers installed on your system.
  8. As an example, you can select ODBC Drivers and click "Next"
  9. On the next tab, you will see "1. Data source." Use the radio button there to select "Use connection string." Please note: this dialog is a Windows dialog, not a dialog of BSC Designer, so it may be different in other Windows versions. Click the button to the right of this radio box (in Windows 7 it is "Build").
  10. In the new dialog, go to the "Machine data source" tab. To demonstrate the function we'll use an Excel-based data source, so select "Excel Files" as a data source.
  11. Double-click on it. Then select an MS Excel file (*.xls); you will find some in the BSC Designer installation (in the folder "\Samples\Data via SQL"). Click OK.
  12. Click the "Test" button to make sure that the connection string settings are correct.

Creating SQL query

Now you will need to create an SQL query.

  1. Click the "Build" button for "SQL statement"
  2. In the first step BSC Designer will get a list of available tables (in our example it is a named range "all" in Excel file). Select a table and click the "Next" button.
  3. From the "Result field" drop list select the column that you want to use for the value of your KPI. In our example we choose the "Value" column.
  4. Now, add a condition that will tell a database to return data for the date selected in BSC Designer's calendar. To do so, we need to select "Date" from available field list, and then select "=" as a condition, and "Date" in the drop list.
  5. On the final step click "Finish" to apply changes and query the data source for the first time.
The resulted SQL query is as simple as:

SELECT `Value`,`Date`
FROM `all`
WHERE
( `Date` = :Date )

To make sure that BSC Designer actually reads the data from the data source select some date in the BSC Designer calendar. In our example, we can select 28 April, 2015 in the calendar, and the value for a KPI will be taken from the data source and it will be 39.

Parameters in request

:Date is not the only parameter supported by BSC Designer, here are some other parameters that you might want to use in your query:

Getting aggregated values from a data source

In some cases you need to read from a database not just a single value, but get an aggregated value over a period. For example, in the project you might have an indicator "Monthly sales, $," but in the database there might be records for each order that your customers placed. Here is what you need to do in BSC Designer in this case:

  1. Create a new indicator "Monthly sales, $"
  2. Change its update interval to "Update monthly"
  3. Now, instead of using :Date parameter in your SQL query, use :UpdatePeriodEnd and :UpdatePeriodEnd parameters
Update SQL query appropriately:

SELECT `Value`,`Date`
FROM `all`
WHERE
( `Date` BETWEEN :UpdatePeriodEnd and :UpdatePeriodEnd )

Additional settings