SQL indicators with BSC Designer – step by step

One of the success factors 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 a user can query an 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 an appropriate query might be complicated, so the help of a 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 the “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 the “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 an 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 the 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:\\tmp\db indicators.xlsx in our example);

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

Click the “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;

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 the 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”

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 it with an 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:

DateValue
03.06.2011Item1
03.06.2011Item2
03.06.2011Item3
04.06.2011Item1
04.06.2011Item2

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 that 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.

Related Articles

Posted in Features
Tags: , , , ,

Balanced Scorecard Software

Balanced Scorecard Articles

Find here more articles...

BSC Lessons

Download BSC Designer and we will follow up with you with lessons about the Balanced Scorecard:

Trending Diagrams and Articles

Find here more articles...

Follow us on Facebook

BSC Designer Authors

Aleksey Savkin Performance Management and Balanced Scorecard

Kazim Ladimeji HR KPIs and Performance Expert

Levi Newman Effective KPIs and social metrics

Oana Boteanu Performance Management

Oleg Tumarkin Business Measurements

Thank you for sharing!

Whether you are looking for a professional Balanced Scorecard software, or just researching information about Balanced Scorecard and business strategies, we recommend you to download and try our BSC Designer software (no credit card is required).

We will follow up with you with lessons about the Balanced Scorecard and will keep you informed about the trending articles on bscdesigner.com

Follow us in Social Media

More in Features
Import Indicators with BSC Designer

This article will present a few common ways of data management with BSC DesignerRead more ›

BSC Designer Online – introductional video

BSC Designer Online is a web-based online KPI system that provides business professionals with balanced scorecard concept support from with...

Close