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 datesbases 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 datasource 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 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 "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 "Build" button for "SQL statement"
  2. On 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 "Next" button.
  3. From the "Result field" droplist select the column that you want to use for the value of your KPI. In our example we choose "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 droplist.
  5. On the final step click "Finish" to apply changes and query the datasource 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 BSC Designer calender. In our example, we can select 28 April, 2015 in the calendar, the value for a KPI will be taken from 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