Using SQL Database as a Data Source for the Indicators
Use an external database as a data source for your KPIs. Setup SQL Indicators to access remote database.
Setup SQL Indicator
- Open a scorecard, go to the KPIs tab
- Select an indicator that will be a recipient of data
- Switch to the Data tab, click on the Data source button next to the Value field
- Click on the SQL Query button in the More data sources section
- Enter server name. For a locally installed BSC Designer the server name will most likely be localhost
- Enter port number. For a locally installed MySQL database the default port number is 3306.
- Enter database name, access login, and password. Click the Next button.
- Formulate required SQL query (see some examples below).
- Click on the Next button to preview the results. Click on the Finish button.
Examples of SQL Queries
BSC Designer expects to receive one value as a response to the SQL query. The received data will be used for the Value field of the indicator.
- %%UpdatePeriodStart%% and %%UpdatePeriodEnd%% - respective starting date of the update interval and ending date
- %%ItemName%% - the name of the KPI as specified in the Name field
- %%date%% - current date selected in the calendar in BSC Designer
Case 1: Indicator is updated daily. There is one value for each date.
select value from datatable where date = %%date%%
Case 2 (most used): Indicator is updated monthly. There are several values for this period in the database that need to be summarized.
select sum(value) from datatable where date between %%UpdatePeriodStart%% and %%UpdatePeriodEnd%%
There are several ways to update data for SQL indicators:
- Click on any date in the calendar - BSC Designer will try to read data for that day; these requests are cached on the level of web browser session.
- Manual update - if you need to refresh data for certain date, use Refresh imported option in Tools menu
- Specify update interval - specify an update interval for an indicator. BSC Designer will update SQL indicator automatically using specified schedule.