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.
- 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 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
- 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 an 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 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.
- Click the "Build" button for "SQL statement"
- 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.
- 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.
- 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.
- 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:
( `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:
- :FileName - will pass the file name of the current .bsc
- :Date - will pass the current selected date in BSC Designer
- :UpdatePeriodStart - the first date of the selected period (applies when update interval is specified). For the details, see the example below.
- :UpdatePeriodEnd - the last date of the selected period (applies when update interval is specified). For the details, 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 :UpdatePeriodStart and :UpdatePeriodEnd parameters
Update SQL query appropriately:
( `Date` BETWEEN :UpdatePeriodStart and :UpdatePeriodEnd )
- Use "Document Properties" command in the "File" menu to access
the "SQL" tab. You will find there additional settings that specify how the
BSC Designer will access data using SQL indicators.