QPR Knowledge Base 2017.1

SQL Import

SQL Import

 Previous topic Next topic No directory for this topic  

SQL Import

 Previous topic Next topic Topic directory requires JavaScript Mail us feedback on this topic!  

You can import element values from SQL databases. To do this, you need to write an SQL query that retrieves the data.

Note: To produce an SQL query you need to have basic skills in SQL.

 

The imported data is displayed in columns in the Imported Values tab, where you can specify which column you want to assign with the value and the date of your element. This value will be specified for the current element and series. In addition, this value can be specified for periods as well.

 

Once you have written the query, you can choose to execute it interactively or scheduled. A scheduled query will be executed later at regular intervals of your choice, for example, on a weekly or monthly basis.

 

To import more than one value at a time, SQL Mass Importing is also available.

 

 

Before the SQL Import

Before you start an SQL import, you need to make sure that there is an SQL driver installed on your system. You also need to know the database alias for the database from which you are importing, as well as the user name and password for it. Contact your system administrator for this information.

 

Local and Server Imports

You can choose to import data from a local SQL data source or a server SQL database. If you choose a local import, the import function uses the ODBC drivers saved in your local workstation.

 

The schedule function is only available for server imports.

 

SQL Script

You can define the SQL query script in the Element Properties window. The following is an example of the SQL query syntax.

 

The SQL query field accepts all SQL commands and does not perform any checks, so make sure you are using the appropriate statements in order not to inadvertently remove data from the database.

 

Required syntax

SELECT * FROM MODELS

An example

SELECT totals, date FROM SALES

 

The asterisk (*) stands for the variable names. Separate them with a comma. MODELS stands for the database table name.

 

 

Importing a Value from SQL Databases

To import a value from an SQL database, do the following:

 

1.Open the Element Properties window and select the Values tab.
2.In the Values tab, select the series for which you would like to import the value.
3.Right-click over the column of the selected series to open a pop-up menu and select the SQL Import option in the Input Type submenu.
4.Define a name for the import into the Task name field.
5.Select the Define... option from the pop-up menu described in the previous tab. The SQL Import window opens with the Import Settings tab displayed.
6.If you wish to import from a local data source rather than one located on the server, select the Local Data Source check box.
7.Click the Connect... button to open the Select Database Alias window. Select the database from the list and click OK. The Database login dialog opens.
8.Login to the database with the user name and password. The Datasource and User Name fields are automatically completed.
9.In the SQL Query field, enter the query (or queries) to select the desired values and then click the Execute button. The Imported Values tab, containing the selected values, is displayed.
       
       
Note: The values which have been selected from the database and displayed in the Imported Values tab can be overwritten within the Imported Values tab.
       

 

10.In the Values in column drop-down list, select the column from which the value will be imported.
11.In the Periods in column drop-down list, select the column that holds the period to be used. You can define the period in three ways: By period index, by period name, or by date. To define a period index, prefix the value with a #, i.e. "#5" would make the value to be imported to the fifth period. To define the target period by the period name, prefix the value with a $, e.g. "$Q3/2009". To define the period by a date, input the date without any special control characters. If you leave this field undefined, then by default, the current period is assigned as the period.
12.The Element and Series fields display the name of the current element and series into which the value is being imported.
13.Select the Save button to complete the import. The value is now displayed in the Value field, for the specified Period.

Note: If the value column that is displayed in the Imported Values tab contains more than one row for the same measure and series, then the last value in the column is the one that is used.

 

Note: If you encounter invalid period related errors in the import or the values are saved to a wrong period, it may be due to date and time format mismatch between the data source and the local computer. In that case, check that the date and time in the Regional Settings of the data source match the ones used on the local computer.

 

 

Deleting Values with SQL Import

It is also possible to delete values with the SQL import. A value is deleted if the following requirements are met:

•Value is clearly identified by the name of the scorecard, series, measure and period

•The value in the source database is:

•Null (when importing from a database)

•Empty (when importing from an Excel sheet)

 

Note: Special characters and letters in the source database's fields do not cause values to be deleted, i.e. deletion occurs only if the preconditions above are met.