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
- Click > Settings > Integrations tab
- In the Datasources section click Add to add a new datasource
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
- 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%%
Right to Create SQL Indicators
By default, power users don't have a right to create new SQL indicators. To grant this right:
- Go to the > Users section
- Click the Edit button for a user
- Use the Allow use external datasources checkbox
- Press Save button
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.