Editing dimensions and rollups
Raedfast dimensions
The video below covers opening and editing a dimension table, adding a new rollup table, and browsing the dimension hierarchy. Watch the video, then read the following sections, which expand on the video content.
Scroll down the page for an introduction to editing dimension data in Excel.
Opening a dimension
Click on Dimensions in the Main Menu screen to show the list of dimensions. Double click on any dimension, or click the Open button, to open it.
A Raedfast dimension consists of one or more dimension tables, arranged in a hierarchy.
The table with the same name as the dimension itself is the dimension base table. Every dimension has one of these. A dimension may also have any number of rollup tables, which group the records of the dimension base table in various ways.
When you open a dimension, you see the dimension hierarchy, showing the base table and the rollup tables if any.
In the example used in the video, Specialty rolls up to SpecialtyName, and to Theatre Group.
Browsing the dimension hierarchy
Click on the Browse Dimension button to open the dimension browser. This shows the dimension hierarchy, in two windows. The upper window lists the dimension tables which form the dimension. The lower window shows the dimension hierarchy, beginning from the table highlighted in the upper window.
In the lower window, rollup elements are shown with a plus sign against them. Clicking on a rollup element expands the element into its children.
Searching the dimension
At the top of the dimension browser there is a search box. Type any string of characters into the search box, and the lower window changes to list the elements, from any table in the dimension, which contain the entered string.
Editing a dimension table
To edit the content of a dimension table, double-click on the table in the hierarchy diagram, or click on the table and click the Open button.
Raedfast displays the content of the table in a new tab.
Every dimension table has the same set of basic fields:
Key field, e.g. SpecialtyNo
This is the primary key, unique to each record. It is a number which automatically increments with each new record. When a new record is entered into the dimension table editor screen in Raedfast, that new record is shown with a negative key value until the changes are saved, when a permanent positive key is assigned by the database.
The name of the key field is the name of the dimension table, suffixed with No.
Element field, e.g. Specialty
The element field is a text field, with the same name as the dimension table itself. Values in the element field must be unique in the table.
Sort field field, e.g. SpecialtySeqno
Each dimension table has a sort field, used to determine the default order in which elements are listed, both within the Raedfast front-end and in the Analysis Services pivot. The sort field is a number. It can be positive or negative. There can be gaps between successive numbers, and the values do not have to be unique.
When a new record is inserted into a dimension table the system gives the Sort field a value of 0, which you can subsequently change.
If two records in a Dimension table have the same sort field value, the Raedfast front-end and the Analysis Services pivot will sort them using the element values.
The name of the sort field is the name of the dimension table, suffixed with Seqno.
Rollup fields
In addition to the always-present key, element and sort fields, a dimension table can have any number of rollup fields, one for each of the immediate parents of the table in the dimension hierarchy. So the Specialty table in the video has a field for SpecialtyName, and a field for TheatreGroup.
On the dimension table editor screen, you see in each rollup field the element value of the corresponding rollup table record. In reality it is the key of the rollup record that is held against the child record — so that if you change the rollup element value in the parent table then the new element value will automatically be shown against corresponding records in the child table. For example, if a TheatreGroup element were changed in the TheatreGroup table from Surgical to Surgery then this new value would be displayed automatically in the Specialty table, against all the Specialties which are members of that TheatreGroup.
You can edit rollup field values in the child table either by typing the value in (in which case the value must exist in the rollup table), or by double-clicking in the cell and selecting from a list.
Adding a rollup table to a dimension
Adding a table
To add a rollup table, right-click on the dimension table in the dimension hierarchy screen which is to be the child table of the new Rollup and click Add new rollup table.
Renaming a table
To rename a rollup table, right-click on the table, click Rename and enter the new name. You cannot rename the dimension base table.
Deleting a table
To delete a rollup table, right-click on the table and click Delete. You cannot delete the dimension base table. You cannot delete a child table: you must delete or move its parents first.
Moving a table
You can move a rollup table to a new position in the hierarchy by clicking on its name and dragging it to the new position. If the moved table has parent tables, these are moved too.
Setting the default element of a dimension table
When you enter a new record in a dimension table, the system assigns to it the default element for each parent rollup table. The default element for a dimension table is the top-sorting element, i.e. the element with the lowest sort field value. So you should always ensure that the lowest-sorting element in a dimension table is the element you want to use as a default — for example, Theatre Group NA.
When you create a new rollup table, Raedfast creates it with a single record: the element field value is the name of the rollup table plus NA (e.g. TheatreGroup NA), and the sort field value is -1. This record will be the default element of the dimension table, unless you create a new lower-sorting record.
Using rollups to handle codes and descriptions
Rollup tables are usually groupings of child table records. The TheatreGroup rollup groups specialties into Medical, Surgical and Maternity. But rollup table records and child table records can also correspond one-to-one, when the rollup table represents another way of describing the same entities as the child table — as when the child table contains code values, and the parent table contains the descriptions.
So the Specialty table in the video holds Specialty codes, e.g. 100, and the SpecialtyName table holds the corresponding description, e.g. 100 - General Surgery.
This is done so that when data is loaded into Raedfast the data need only hold the code of an entity such as Specialty, which is fixed and known, rather than the description which is variable and might be different in detail from the value held in Raedfast, or which might have to be derived via a lookup, thereby complicating the load process.
Separating codes and descriptions in this way also makes it easier to enter Raedfast rules. One can easily type in the Specialty code if one knows it, but to enter the description would require laborious typing or selection of the value from a list.
Ragged hierarchies
Normally each element in a dimension should be unique — i.e. its value should not recur in another dimension table in the same dimension.
The system does not enforce this however, as there are instances where you may want an element to roll up to itself. This is known as a ragged hierarchy. In the example below, the All Inpatients rollup in the PodLevel2 table breaks down into All Emergency, EL and Maternity. All Emergency in turn breaks down into Emergency and SDEC. But for EL and Maternity there is no further breakdown applicable, so the same Element names are repeated at the lower levels, in both the Pod and PodLevel1 tables.
Element names in a Dimension should only be repeated in this way when there is a one-to-one correspondence between the two — i.e. one is the parent of the other and the other the sole child of the one. If you reuse Element names with different meanings, as rollups of different sets of child Elements, then you risk confusion about which meaning is intended when you enter Rules, and when you report on Model output.
Measure dimensions
There are two special dimensions different from the others: ActivityHistoryMeasure and ActivityModelMeasure.
These are pseudo-dimensions intended to allow the user to create rollups of measures for use in defining rules.
The base elements of these dimensions are the measures of the ActivityHistory table and the ActivityModel table. If you open one of these dimensions you will be able to add rollup tables to them, and you can use the elements from these rollup tables to define rules.
For instance, you might create a rollup called Cases and BedDays to distinguish the Cases and BedDays measures from all the others. You would then be define a rule applying to the Cases and BedDays measures only.
You cannot add, rename or delete elements in the dimension base tables. To do this, you need to use the relevant Task from the Tasks menu to add, rename or delete a measure.
Changing dimension data used in data tables or rules
Renaming elements which are in use
You can freely change element names which are in use. The new element will automatically replace the old element wherever the old element occurred — in a child dimension table, in a data table or in a rule.
Deleting elements which are in use
The system will not permit you to delete an element from a dimension table which occurs in a child dimension table, in a data table or in a rule.
Changing the definition of a rollup used in rules
You should be careful when changing the definition of a rollup in case you inadvertently change the meaning of rules in which that rollup occurs. In particular, when changing a rollup in order to make it what you want it to be for reporting purposes, remember that the rollup may also be used in rules.
Editing dimension data in Excel
Dimension data can be edited in Excel. There are two reasons to do this:
you have existing dimension data in Raedfast which you want to edit, using tools available in Excel but not in the Raedfast front end
you have new dimension data in Excel which you want to load into Raedfast.
To edit existing data in Excel, open the relevant dimension table and click on the Copy all to Clipboard button. Then paste the data into Excel.
Alternatively you can prepare data for a dimension table in Excel from scratch.
When you have finished developing the data in Excel, place the data on the clipboard — use Ctrl + A to select the whole table, then CTRL + C to copy. Then in the Raedfast front end click on the Past all from Clipboard button.
Raedfast will look through the clipboard data for columns with names matching field names in the dimension table. The order of matching columns on the clipboard need not match the order on the dimension table. Columns on the clipboard data with names not present in the fields of the dimension table are ignored.
All existing data in the dimension table is replaced.
Ensure dimension columns containing numbers (for example a column of numeric Specialty codes) are formatted as Text in Excel
If you are editing existing dimension table data, ensure that the original key column is retained in Excel and returned on the clipboard. If you do not return the original key value, the system will treat the record as a new one.
For new records leave the key column blank. If all the records are new you can omit the key column.
The element column is mandatory. The key column is mandatory or optional as described above. All other columns, including rollup elements, are optional.