SQL indicators with BSC Designer
Intro
Big company might have lots of data stored in different places; even each department might have its own database. But in order to optimize business processes and improve effectiveness it is highly important to organize the data linking process between all the databases.
If you would like to set BSC Designer as the main control tool it actually is the way to import all the data into it. The program collects the data from wide range of sources via SQL indicators function. This function is a bit harder to set up oppositely to basic functions aiming to create and manage KPIs but in some cases (for example, when the data source is too big to input information manually or if some department has no need to set up BSC tool but operates with its own data source) it is the only way. Also SQL indicators have an advantage – after it set up its no need to manage it somehow – all changes appeared in data source will be presented in manager’s dashboard (BSC Designer’s project) so the only thing is to create SQL indicator and then control and report its performance.
What is SQL?
Structured Query Language (SQL) is the type of database computer language used to manage data in relational database management systems. SQL is most widely used language for relational databases in the world with makes it very useful instrument. One of its important functions is exporting and importing data from different sources (for example charts with logical structure).
What files could be imported to BSC Designer?
Since SQL is universal tool any database source might be imported via SQL indicator function. The only thing is syntax of database should be converted into the format suitable with the software product. The easiest files to be imported are MS Excel and MS Access files. This article will show the example on how to create SQL indicator linking to MS Excel file. As for other database sources it is possible to import them into Balanced Scorecard Designer’s project as well but the algorithm will be a little bit different. That is why we recommend our clients to consult with our support (the support form could be found on this page) about the source which are needed to be imported. Also company’s IT department might find the algorithm for any file to import it using SQL.
Recent update
Now BSC Designer contains SQL query builder! With this tool even non IT professional (those one who does not know SQL computer language) could easily create a proper SQL query and import data. The following instruction on how to create SQL indicator using MS Excel file will work for the new BSC Designer’s version so we recommend users to update the software product. Also we want to inform our users that SQL indicators function is available only in BSC Designer Pro version but not in Standard one.
How to update Balanced Scorecard Designer program
Current users need to download BSC Designer installer and overwrite the software. No system’s reboot is needed. After the installation is done run the program and use all new functions and features!
Not a user yet? Well join us by downloading a full function software product and use it 30 days long for free! After that the program’s license might be bought.
Instruction on how to create SQL indicator linking to MS Excel file
Preparing MS Office Excel file
First of all we will need to be sure MS Excel file (*.xls or *.xlsx) has suitable format. Let’s try to create a simple example to understand the logic of proper structure. Open MS Office Excel and input “Date” into “A1” cell and “Value” into “B1” cell. Then fill the column under “Date” by dates using format of DD.MM.YYYY (let it be 26.12.2009 – 30.12.2009). Also fill the column under Value by random indicators values (as an example let it be 48, 46, 39, 47 and 52). Then name the data by selecting all the cells (including “Date” and “Name”) and inputting “all” into “Name” field just like on the image below. Save the file (for example in C:\tmp category) and close MS Excel. Now we have the source of proper format.
Naming the data in MS Excel file
As for logic of format – it might be other names of “Value” cell (like “indicator 1” or “percentage” and so on) as well as values in this column. By default BSC Designer sets the new indicator’s parameters of “0” as min, “100” as max and maximization direction of optimization but after SQL indicator will be created all those settings could be changed as they are needed to the user so numbers like 48392934 in value cells could fit into the project.
There is also no rule like one indicator for one MS Excel file. Other indicators could be placed as columns under C, D, E, F, G and others. But sure the information each of those columns contains will be exported into different indicators. How to chose proper values to use will be described in the next parts of this article.
Creating SQL indicator
After the source (MS Excel file in our case) is prepared it is time to make an SQL indicator linking to it. Launch BSC Designer Pro and open one of the projects (it might be “default.bsc” file). It is important to select one of the dates in calendar field (read more about calendar in the article “How to manage time points with BSC Designer”) that presented in source file to avoid errors while importing data. Select any category, click on Strategy Tree button on top menu and select New SQL indicator sign. A new dialog window will appear where importing parameters will need to be set. Follow the algorithm below to transport data from *.xls* file into BSC Designer project’s indicator.
Step 1: Click on “Build” button under Set Connection String field;
Step 2: Select “Microsoft OLE DB Provider for ODBC Drivers” – the right choice to operate with MS Excel files;
Step 3: Select “Use connection string” and click on “Build” button right near;
Step 4: Select Excel files in new window;
Step 5: Select the file’s path in a new dialog window (C:\tmp\datasource1.xlsx in our example);
Step 6: Try if everything works properly using the “Test Connection” button. No more things are needed to be set in this window. After the test is successful press “OK” button;
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 8: Select the table (the table called “all” in our case). That is why we named the data in MS Excel file. After “all” is selected click on “Next” button;
Step 9: Select resulted field – the right column of values which are needed to be used. If there are several columns in data source (MS Excel file in our case) this step will be the only difference between indicators. So if it’s needed to create indicators for each column the first one might be multiplied (read more on how to multiply the indicators in the first part of the article “How to manage KPIs using BSC Designer’s functions”) and then edited (right click on the indicator -> “Edit SQL Properties”) to show other column as a result field. Also be sure that “value” column is selected but not “date” one;
Step 10: The condition to “Date” field must be set as “=”. Be sure the condition is set for “Date” field but not to “Value” one;
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.
That is it
Now new SQL indicator (underlined one) is created and could be managed as well as imported indicator. When any changes appeared in data source (datasource1.xlsx in our case) it also will be viewed and calculated in SQL indicator. We understand that our clients might have some other data sources than MS Excel files so we recommend to consult with our support (the support form could be found on this page) about the source which are needed to be imported. Also company’s IT department might find the algorithm for any file to import it using SQL indicators. Advanced users might create queries and connection strings without BSC Designer’s builder just typing it into proper fields.
Congratulation! New SQL indicator had been created and is ready to be used
