Import Data from External Database with SQL Query

Learn how to power performance metrics on the scorecard by connecting to a database using SQL indicators.

Creating SQL Indicator

  1. Go to the KPI tab
  2. Select any indicator
  3. Switch to the Data tab below
  4. Click the Data source buttonKPI Data Source button
  5. Select Link to SQL Database
  6. Select a database with local or web access

Creating Connection String

  1. Click the Build button to create a connection string
  2. Select a data source (available data sources depend on the ADO drivers installed), for example ODBC drivers:
  3. Click Next
  4. Switch to Use connection string in Specify the source of data section
  5. Click the Build button
  6. Go to the Machine data source tab
  7. Select data source (for example, "Excel Files")
  8. Double-click on it
  9. Select an MS Excel file, click OK
  10. Click the Test button

Creating SQL Query

  1. Click the Build button for SQL statement
  2. Select a table and click the Next button
  3. Select a column from the Result field drop list
  4. Select "Date" from the available field list, select "=" as a condition, and "Date" in the drop list
  5. Click "Finish" to apply changes
The example SQL query: 
SELECT `Value`,`Date`
FROM `all`
WHERE
 ( `Date` = :Date )

Parameters Supported in Request

  • :Date - the date selected in BSC Designer
  • :UpdatePeriodStart - the first date of the current update interval
  • :UpdatePeriodEnd - the last date of the current update interval
  • :ItemName - the name of indicator
  • :Max - MAX value of indicator
  • :Min - MIN value of indicator
  • :FileName - the file name of the current scorecard

Getting Aggregated Values from a Data Source

  1. Specify update interval for an indicator
  2. Replace :Date parameter in SQL query with :UpdatePeriodStart and :UpdatePeriodEnd parameters
The example SQL query that aggregates data: 
SELECT sum(Value) as AggregatedValue,`Date`
FROM `all`
WHERE
 ( `Date` BETWEEN :UpdatePeriodStart and :UpdatePeriodEnd )

Additional ideas

  • Use the File > Document Properties > SQL tab to find additional settings for SQL indicators
  • Find the Data via SQL scorecard in the samples folder as an example of SQL indicator setup