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.
- Creating SQL requests might require technical knowledge,
so if you encounter difficulties, please consult with IT professionals
experienced in SQL / ADO.
- We have put together an example of how to use the scorecard
in "\BSC Designer\Samples\Data via SQL," check
readme-sql.txt in that folder before opening the samples.
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
- Launch BSC Designer
- Create an indicator
- Click "KPI Data Source" button
- Select "Link to SQL Database"
- Select a database with local or web access
- Click the "Build" button to create a connection string.
- 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.
- As an example, you can select ODBC Drivers and click "Next"
- 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").
- 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.
- 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.
- 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.
- Click "Build" button for "SQL statement"
- 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.
- 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.
- 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.
- 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:
( `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:
- :FileName - will pass the file name of the current .bsc
- :Date - will pass the current selected date in BSC Designer
- :UpdatePeriodEnd - the first date of the selected period (applies when update interval is specified). For the detais, see the example below.
- :UpdatePeriodEnd - the last date of the selected period (applies when update interval is specified). For the detais, see the example below.
- :ItemName - will pass the name of SQL indicator into SQL query
- :Max - passes the MAX value of an indicator
- :Min - passes the MIN value of an indicator
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:
- Create a new indicator "Monthly sales, $"
- Change its update interval to "Update monthly"
- Now, instead of using :Date parameter in your SQL query, use :UpdatePeriodEnd and :UpdatePeriodEnd parameters
Update SQL query appropriately:
( `Date` BETWEEN :UpdatePeriodEnd and :UpdatePeriodEnd )
- Use "Document Properties" command in "File" menu to access
"SQL" tab. You will find there additional settings that specify how the
BSC Designer will access data using SQL indicators.