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.

Create a Datasource

  1. Click > Settings > Integrations tab
  2. In the Datasources section click Add to add a new datasource

Setup SQL Indicator

  1. Open a scorecard, go to the KPIs tab
  2. Select an indicator that will be a recipient of data
  3. Switch to the Data tab, click on the Data source button next to the Value field

    4. Data source button

  4. Click on the SQL Query button
  5. Formulate required SQL query (see some examples below).
  6. Click on the Next button to preview the results. Click on the Finish button.
Examples:

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.

Supported parameters:

  • %%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%%

Right to Create SQL Indicators

By default, power users don't have a right to create new SQL indicators. To grant this right:

  1. Go to the > Users section
  2. Click the Edit button for a user
  3. Use the Allow use external datasources checkbox
  4. Press Save button

Updating Data

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