Loading baseline data
The Raedfast Excel add-in
Raedfast includes an Excel add-in to enable the load of spreadsheet data into the ActivityHistory table. You should find the add-in on the Add-Ins ribbon in Excel. If you can’t see it, please ask your system administrator to install it, or refer to the section on installing the add-in in Raedfast online help here.
Data can also be loaded into Raedfast using SQL Server directly. Please refer to relevant documentation here.
Loading data through the add-in
Watch the video below to see how to load data through the add-in, then read the following sections which provide more detail.
Worksheet format
When you use the add-in to load the data in the current worksheet, Raedfast assumes that the worksheet is a single table of data, with field names in the top row.
The system checks the top row of the worksheet for field names corresponding to the dimensions and measures of the ActivityHistory table.
Columns with field names which do not so match are ignored.
Matching columns can be in any order.
Missing dimensions and measures
You do not need to include a column for every dimension of the ActivityHistory table. If you miss out a dimension, the load process will take the default element (the top-sorting element) for that dimension’s base table, and apply that to every incoming record.
Similarly you do not need to supply column for every measure. If you miss out a measure, the load process will supply a zero.
Excluding records from load
Your spreadsheet may contain records you do not wish to load — birth records for example. To exclude them, you can either delete them from the workbook, or ensure that the value of all measure columns for these records is zero. Raedfast will not load records with all-zero measure values.
Note that if you take the second approach, Raedfast will still list any new dimension elements for the excluded records, but will not actually create these new elements on load (unless of course they also appear in records which are not excluded).
Level
Data is loaded at the bottom level of each dimension: you cannot load data at a rollup level.
Case
Dimension names, measure names and element names must precisely match the Raedfast database values, including matching their case.
Dates
Avoid using the Excel date format. If you have a value such as Mar 20 which Excel will naturally format as a date, put a single quote in front of it so that Excel formats it as text.
Use of the XLSB type
Large workbooks will open and save considerably more quickly if they are saved as the xlsb format, rather than xlsx.
Versions
Before loading the data, Raedfast will delete existing data in ActivityHistory for the version or versions in the version field of the worksheet table.
For this reason, there must be a column called version and it must be populated on every row.
A version can be anything you need it to be in order to identify where data in the ActivityHistory table came from, and in order to be able to apply data refreshes without deleting existing data you want to keep.
A version may be a source system. If more than one type of data is supplied by the same source system then it may be a source system and a type of data — e.g. Beckhampton Hospital Inpatients, Lockeridge Hospital Outpatients.
If data is supplied in period increments then the version may also identify the period — e.g. Beckhampton Hospital Inpatients, Apr 2021.
It may be that you wish to load more up-to-date or corrected data from a given feed but keep the data already loaded, as this has been used in existing published scenarios. In this circumstance the new version should distinguish the new data from the existing data, so that the existing data is not deleted on load — e.g. Beckhampton Hospital Inpatients, Apr 2021, Increment 1.
Preparing the data
In preparing the data you may wish to bear in mind the following considerations:
do not load data for dimensions you do not need, either for writing rules or for analysis of output.
do not specify dimension elements at a lower level of detail than you need, either for writing rules or for analysis of output. For example, do not load individual ages unless you need to: group them into say 5 year bands. If there is a set of local commissioners which you need to identify separately, group the others into say Other Commissioner before loading the data.
remember that you can always go back and reload at a lower level of detail if it transpires that you need to.
be consistent across versions: if you have defined Age Bands as 0-15 Years and so on for your inpatient data, do not load your outpatient data against e.g. 0 to 15 Yrs.
consider using codes only, in data for load. Use a rollup table to add descriptions. E.g. load data against Specialty 100 not 100 - General Surgery. This is probably how the data naturally arrives in the worksheet, and avoids problems caused by misspelling and different forms of the same descriptive term.
Deleting data for a version
To clear data for a given version out of the ActivityHistory table without replacing it, open a blank worksheet. Enter Version in cell A1, and in cell A2 type the name of the version you want to delete. Then load the worksheet to the ActivityHistory table.
Existing data for the version will be deleted prior to load. No new data will be loaded, as Raedfast does not load records with all-zero measure values.
Connecting to the Raedfast database
To connect to the Raedfast database, click the Load Data button on the add-in’s ribbon, and click the Connect to Database tab in the panel which appears to the right.
Enter your Server name and click Connect to Server.
Select the Raedfast database you want to load data into, and click the Save button.
Now return to the Load Data tab.
Listing new dimension elements
Before loading the data you may wish to review new dimension elements. These are values in your Excel worksheet which are not currently present in the corresponding Raedfast dimension table.
When you load the data, Raedfast will automatically add these.
To review the new elements which will be added first, click on the List New Dimension Elements button.
You will need to save the workbook first. A new workbook will open, containing a worksheet for each dimension for which new elements are present.
Review these, and confirm that you are happy for them to be added — that they are not for example mis-spellings of existing elements.
You may want to paste them into the Readfast dimension tables in advance of loading the data, so that you can specify the rollup mappings for the new elements. Alternatively you can load the data first, have the system apply default mappings, and adjust the later.
Loading
When you are ready, click the Load Data button.
You will need to save the workbook first.
Reviewing
When processing is complete, you can view the new data in the ActivityHistory table in the Raedfast pivot.
If the system has added new dimension elements automatically on load, these will have default mappings. You can identify the newly added elements as these will have the highest values in the key field: open the dimension base table and click on the key column twice to sort high-to-low. The new elements will be at the top. Adjust the mappings as required.