Loading data in SQL Server
Preparing input data
You can load data into the ActivityHistory table, using Raedfast stored procedures.
The data to be loaded must be in a table in the Raedfast database. If your data is held in an external database you can create a SQL view of it in the Raedfast database and load that.
The input table or view should have columns of which the names correspond to Dimensions and Measures of the ActivityHistory table. Note that the Dimension columns should have the name of the Dimension itself, not the name of the Dimension key column on the ActivityHistory table — e.g. Specialty, not SpecialtyNo.
You may supply values for all or any of the Dimensions and Measures.
Only the Version Dimension is mandatory: all others may be omitted.
Measure columns should be of a numeric type: tinyint, smallint, int, bigint, decimal, float, smallmoney, money, numeric, float, real.
Dimension columns should be of a text type: char, varchar, text, nchar, nvarchar or ntext. The values in Dimension columns should be the Elements of the corresponding Dimension, or new Elements to be added to that Dimension.
Raedfast will load any numeric column which bears the name of a Measure, and any text column which bears the name of a Dimension. Other columns will be ignored.
Replacing existing data, and adding new data
Before loading the data, Raedfast will optionally delete any existing data for any Version with at least one record in the input table or view.
So to replace existing data simply tag the new data with the Version of the existing data, and ask the Raedfast load process (see below) to replace the existing records.
To add new data, tag the data with a new Version, or tell the Raedfast load process not to delete existing records for incoming Versions.
Listing new Dimension Elements
Before loading the data you may wish to establish whether the data contains any Dimension values which do not currently exist in the corresponding Dimension Base Table. You may wish to confirm that there are none. Or you may wish to identify them, to confirm that they are as expected.
Having identified them, you may then choose to add them manually into the Dimensions before loading the data, to set any relevant Rollup values. Or you can let the data load process add them automatically with default rollup values, and adjust them afterwards.
To list new Dimension Elements, execute the following command in SQL Server Management Studio:
exec rb.DataTableListNewElements ‘ActivityHistory’, ‘<your input data table>’
For example:
exec rb.DataTableListNewElements ‘ActivityHistory’, ‘ab.[Theatre Data]’
This stored procedure will return a list of new Dimension Elements in the Results pane. If there is a long list, you may wish to copy and paste them into Excel. From there you can easily select the Elements which relate to a given Dimension, and paste them into the relevant Dimension table in the Raedfast front-end.
If you choose to allow the load function to add the new dimension elements automatically, you can identify newly added elements in any dimension by opening the dimension base table in the Raedfast front end and clicking on the key column (e.g. SpecialtyNo) to sort by that column. New elements will be those with the highest key values.
Loading your data
To load the data, execute the following command in SQL Server Management Studio:
exec rb.DataTableLoad ‘ActivityHistory’, ‘<your input data table>’
You may supply two optional boolean parameters. The first tells the load process to add any new Dimension Elements automatically. The second tells the load process to delete existing data for any Versions present in the input table.
So for example:
exec rb.DataTableLoad ‘ActivityHistory’, ‘ab.[Theatre Data]’, 1, 1
tells the procedure to load the ab.Theatre Data table into the ActivityHistory table, generating any new Dimension Elements and deleting existing data for the incoming Versions.