|
|
SQL Mass Import |
|
|
|
||
SQL Mass Import |
|
|
|
|
|
|
SQL Mass Import |
|
|
|
||
SQL Mass Import |
|
|
|
|
|
|
||
From the Integration View, you can perform a SQL mass import. In other words, you can import many values at once using this feature. You can import values for one measure and all series, for all measures and one specific series or for all measures and all series.
This is where element identifiers and series identifiers become crucial. These identifiers are used during the mass importing process. For example, if in the database the value "3.2" is associated with the element identifier "MEA168", then this value will be imported into the element with the identifier "MEA168" in your model. For this reason, it is important that a mass import query produces results which contain columns for element identifier and series identifier.
Importing Values
To import values from an SQL database, do the following:
| 1. | Click the  Integration View button on the View tab of the ribbon. The Integration View window opens. |
| 2. | Select SQL Mass Imports from the drop-down list. |
| 3. | Select the New... button. The SQL Mass Imports window opens. |
| 4. | Define a name for the import into the Task name field. |
| 5. | If you wish to import from a local data source rather than the one located on the server, select the Local Data Source check box. |
| 6. | 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. |
| 7. | Login to the database with the user name and password. The Data source and User Name fields are automatically completed. |
| 8. | 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. |
| 9. | In the Values in column drop-down list, select the column from which the value will be imported. |
| 10. | 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. |
| 11. | In the Scorecard drop-down list, you can define a scorecard identifier that will be used with the import. If the scorecard identifier is defined, the import target will be defined by the Scorecard + Element combination. The scorecard identifier can also be left undefined, in which case the import target will be defined solely based on the element identifier. However, you can also define the scorecard identifier even if all rows in the imported data do not contain a scorecard identifier. In those cases rows with an empty scorecard identifier are matched only by the element identifiers. |
| 12. | In the Element drop-down list, select the column containing the element identifiers which serve as the element ID's. It is also possible to select an element from the current model by choosing the Select element from model option in the drop-down list. |
| 13. | In the Series drop-down list, select the column containing the series identifiers which serve as the series ID's. It is also possible to select a series from the current model by choosing the Select series from model option in the drop-down list. |
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: Values cannot be imported to series that have a formula as their input type. In the case you try such an import, an error message is written to the log file.
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 Mass SQL Import
It is also possible to delete values with the SQL mass 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