SQL indicators with BSC Designer – step by step

One of the success factor of using the Balanced Scorecard concept is making it easy to access data from company databases.

Video Tutorial 3 Different Ways to Import Data in BSC Designer

Use “SQL Indicator” in BSC Designer for this purpose. With SQL Indicator user can query external data source and get the date-specific results.

What data can BSC Designer access?

It is possible to access any database with SQL query and SQL indicator. Normally, no additional development is needed. The process of designing appropriate query might be complicated, so the help of professional from your IT might be required.

Creating SQL indicator step-by-step

Create SQL Indicator

  1. Run BSC Designer Pro or open existent project.
  2. Select any category
  3. Select “New SQL indicator” in Strategy Tree menu.

Learn more about SQL Indicators in BSC Designer manual.

Modify existent SQL Indicator

  • If you already have some SQL Indicator in your project you can change its settings. Right-click on this indicator and select “Edit SQL Properties” command.

Creating SQL Indicator step-by-step

Follow the algorithm below:

Step 1: Click “Build” button under "Set Connection String" field;

Step 1: Click “Build” button under the "Set Connection String" field;

Step 2: Select “Microsoft OLE DB Provider for ODBC Drivers”

Step 2: Select “Microsoft OLE DB Provider for ODBC Drivers”. Click "Next >>".

Step 3: Select “Use connection string” and click on “Build” button right near;

Step 3: Select “Use connection string” option and click on “Build...” button.

In this example we will show how to access data from MS Excel file.

  • If you need to access another data source your steps might be different. For instance, on Step 2 you might need to select something different than “ODBC”. You might need to contact your IT specialist with this question.
  • In order to make the data in Excel file accessible some changed are needed. The step-by-step instruction on how to make these changes together with original Excel file are located below.
Step 4: Switch to the "Machine Data Source" tab. Select Excel files in new window;

Step 4: Switch to the "Machine Data Source" tab. Select "Excel files" in new window. Click OK.

Step 5: Select the file’s path in a new dialog window (C:\tmp\datasource1.xlsx in our example);

Step 5: Select the file’s path in a new dialog window (C:\tmpdb indicators.xlsx in our example);

Click “Test Connection” button to check if everything works properly.

Click “Test Connection” button to check if everything works properly.

Step 7: After connection string is ready let’s move down to SQL query. Click on “Build” button to create statement. A new dialog window will appear;

Step 7: The connection string is now ready. Click "Build" button to create SQL statement.

Select the table (the table called ALL in our case). That is why we named the data range in MS Excel file.

Select the table (the table called ALL in our case). That is why we named the data range in MS Excel file.

Step 9: Select the field that should be returned as a result. In our case the data is in Value field.

Step 9: Select the field that should be returned as a result. In our case the data is in the Value field.

The condition to “Date” field must be set as “=”. Be sure the condition is set for “Date” field but not to “Value” one;

The condition to the “Date” field must be set as “=”. Be sure the condition is set for “Date” field (it should be selected on the left) but not to “Value” one;

Learn about all possible parameters for SQL Indicators in BSC Designer manual.

Step 11: After all settings had been built apply the query, try to perform a test (using “Test” button) and click “OK” to create SQL indicator.

Step 11: Try to perform a test (using “Test” button) and click “OK” to create SQL indicator.

Congratulation! The new SQL indicator had been created and is ready to use.

Congratulation! The new SQL indicator was created. Select 30.12.2009 in the calendar to make sure the SQL Indicator is working.

How to make MS Excel file accessible for SQL indicators

Follow these steps to prepare Excel file:

  1. Open MS Office Excel and input “Date” into “A1” cell and “Value” into “B1” cell.
  2. Fill the column “Date” by dates (in this example the format DD.MM.YYYY is used, but you can use your local date format).
  3. Fill the Value column by some test values.
  4. Name the data range by selecting all the cells (including “Date” and “Name”) and inputting “all” into “Name” (check the screenshot below).
  5. Save the file and close MS Excel.
Name the data range by selecting all the cells (including “Date” and “Name”) and inputting “all” into “Name”
Name the data range by selecting all the cells (including “Date” and “Name”) and inputting “all” into “Name”

Download the example XLS file

Count values in the source file

What if it is necessary to count values in the source file and return them as a value for the indicator? As long as it is possible to do with SQL query, it is possible to do this with BSC Designer.

For instance, the source Excel file (or any other database) contains data in the format:

Date Value
03.06.2011 Item1
03.06.2011 Item2
03.06.2011 Item3
04.06.2011 Item1
04.06.2011 Item2

Our goal is to return 3 for the 03.06.2011 (as there are 3 items for this date) and 2 for 04.06.2011 (as there are 2 items for this date).

We will use the following SQL request to get necessary information (assuming the you have named the data range as explained above):

SELECT COUNT(`Value`) as “ITEMS”, `Date`
FROM `all`
WHERE
( `Date` = :Date )
GROUP BY `Date`;

as a result if we will select this SQL indicator and select 03.06.2011 in the BSC Designer’s calendar, we will see 3 in the indicator’s value field. As for 04.06.2011 we see 2.