Viewing model output
Raedfast’s built-in interface to Excel
Raedfast can be interrogated using any industry-standard reporting tool.
The system however comes with a built-in, automatic interface to Microsoft Analysis Services (Tabular), and through Analysis Services to Excel Pivot Tables. Whenever the Raedfast model is reprocessed, the output is automatically passed to Analysis Services, and is available instantly in Excel.
So while you may use other tools to distribute Raedfast model output within your organisation, or to carry out specialised analytical tasks, it is recommended that you use the built-in Analysis Services interface and Excel as your routine analytical environment in day-to-day work with your Raedfast model.
Connecting Excel to the Readfast database
Connecting for the first time
Begin by connecting an Excel workbook to the Raedfast Analysis Services database.
Follow the steps in the slideshow below to —
select Analysis Services as the type of source data
enter your server name
choose your Readfast database
create a pivot table from the Analysis Services source
Screens shown are for Excel 365 for enterprise.
Click on Data, then From Database, then From Analysis Services
Enter the name of your Analysis Services instance
Choose the Raedfast database you want to connect to
Click OK to creat a Pivot Table based on this connection
The resultant Pivot Table
Connecting a second Pivot Table
You need only set up this connection once.
Thereafter it will be available instantly to a new pivot table in the same Excel workbook or in any workbook on the same PC. To use the connection to create a new pivot table, click on Data and Existing Connections, and choose the relevant connection.
Refreshing the view
If the data in the Raedfast database has changed, you can refresh the data shown in Excel by right-clicking on the pivot table and clicking Refresh. If you have more than one pivot table in your workbook, all the pivot tables which use this same connection will be refreshed at the same time.
The Raedfast Pivot Table
This Analysis Services-based pivot table is like an ordinary Excel Pivot Table built from a data table in Excel, but with some differences.
The data resides not in the Excel workbook but on the Raedfast Analysis Services server.
This means that you do not need to wait for data to be loaded into the workbook, data is not duplicated in different workbooks, and workbooks do not become large and inconvenient to open and manage.
Where an ordinary pivot table has a single table of data, this Analysis Services pivot table contains a number of different tables, which stand in relation to one another.
If you are a user of Excel PowerPivot then you will find this perfectly familiar. The pivot table is more powerful than an ordinary pivot table, but demands a little more understanding.
The ActivityHistory and ActivityModel tables
At the top of the pivot table field list you will see two tables with measures. One is called ActivityHistory. The other is called ActivityModel.
The ActivityHistory table contains historical data which provides the Baseline for the Raedfast model.
The ActivityModel table contains the Raedfast model itself, or more precisely the model output.
The content of the ActivityModel table is derived by taking (selected) baseline data from ActivityHistory, and applying assumptions about change through time, to build a picture of future activity and resource requirements.
The pivot table allows you then to interrogate not only model output but also the historical data available within the ActivityHistory table. This has a value most obviously because this historical data provides the baseline data for the model, and it is important to understand that baseline. But it has a value beyond that, in providing a historical record of actual activity which may (if you wish) extend into the past beyond the recent data most likely to be used as a model baseline, so that historical analysis can be used to support the selection of an appropriate baseline (avoiding periods which are outliers and not representative), and the development of assumptions about change.
The ActivityHistory table
Watch the video below, and refer to the following text for more detailed commentary.
Measures
The measures of the ActivityHistory table are those supplied on the historical data.
Typically these are:
Cases
BedDays
The meaning of each measure is determined by the needs of each Raedfast implementation. Raedfast does not mandate the use of any particular measure in any particular way.
The general term Cases is units of work, of which the meaning can generally be read off the related patient POD — for example, if the POD is First Outpatient Attendances, then Cases will mean just that. In respect of inpatients, Cases will be either provider spells or FCEs.
BedDays is self-explanatory.
Dimensions
The remaining tables below ActivityHistory and ActivityModel are Dimension tables.
Dimension tables do not contain measures, but are used to “slice-and-dice” the measures in the ActivityHistory and ActivityModel tables.
As standard the Dimensions available to the ActivityHistory table comprise:
Age
Commissioner
DiagnosticGroup (locally defined)
Disposal (where the patient went at the end of treatment)
Hrg
GpPractice
Los (Length of Stay)
Month
Pod (point of delivery)
Site
Specialty
SubPod (a finer division of Pod, locally defined)
Version (identifies the source of the data)
The use of these Dimensions is defined by each Raedfast implementation. Raedfast does not mandate either the use or the meaning of any Dimension. The Dimension can remain unused, or it can be filled freely as the project requires.
The measures on the ActivityHistory table can be viewed by all or any of these dimensions, as you would expect of a pivot table field list.
Dimension rollups
Raedfast dimensions are hierarchical. They have a base level, and may also have any number of rollup levels, of which some may be immediate rollups of the base level and some may be rollups of other rollups, forming a hierarchy of several levels.
Each of the dimension tables listed in the pivot table field list has then one or more fields. One field has the same name as the dimension table, and this is the dimension base level. Other fields are rollups in the hierarchy above the base level.
Versions
The Version dimension on the ActivityHistory table is used to identify the source of the data, in terms of dataset (Inpatients say, or A&E), or in terms of the source system. If say inpatient data were coming from more than one source database, the Versions stamped on the data might be Royal Hospital Inpatients and City Infirmary Inpatients.
The ActivityModel table
Watch the video below, and refer to the following text for more detailed commentary.
Measures
The ActivityModel table has the measures of the ActivityHistory table, plus a number of others derived within the model.
Note that e.g. Cases (Historical) on ActivityHistory and Cases on ActivityModel are really the same measure. Analysis Services will not permit a measure to be used on two different tables with the same measure name, so the measures on ActivityHistory are suffixed with (Historical).
Additional measures derived within the model may vary from one implementation to another but typically include:
Beds
Theatres
OpRooms
EdRooms
ImagingRooms
TariffValue
These measures quantify the resource implications of the modelled activity.
Dimensions
The ActivityModel table has available the same dimensions as the ActivityHistory table, with the following exceptions:
the time-related dimension on ActivityModel is Year where on ActivityHistory it is Month. This reflects the assumption that your historical data will be held at a lower level of time-granularity than your model
the Version dimension is not relevant on the ActivityModel table, and is omitted
the ActivityModel table has two additional system dimensions — ActivityScenario and ActivityStep — which hold the model scenario and the model step through which output was generated. These are discussed below.
Scenarios and steps
The Raedfast Model includes any number of scenarios.
Model output is written out against the scenario in which it was generated, and the ActivityScenario Dimension identifies that scenario. So you can view by ActivityScenario to compare different model scenarios.
Each scenario is composed of a number of steps.
A Step is a set of model assumptions relating to a particular modelling move — Demographic Change perhaps, or Step Down to Community Care. Model output is written out against the step which generated it, and the ActivityStep dimension identifies that step. So the total of any measure under a given scenario can be shown broken down by the steps which make up the total.
In Figure 1 below you can see medical beds modelled over 5 years, for scenarios, Do Nothing and Mid Range. Within each scenario, in column A, you can see the steps which make up each Scenario, and the impact of each step on the Beds measure for each year.
In the future you see that the Baseline step pulls baseline data in from ActivityHistory. The next few steps, applied to the baseline year itself, carry out some cleaning of the baseline data — deriving Critical Care activity, and assigning BedDays to inpatients who did not stay overnight. The remaining steps model changes to the baseline through future years.
A scenario is a unique set of steps.
So some steps appear in the Mid Range scenario which do not appear in the Do Nothing scenario.
Cumulative steps
Raedfast steps are cumulative, so that the output of one step feeds into the next.
Roll forward
The Raedfast model rolls forward the total of each measure from the previous year to form the baseline of each current year.
So you can see in Figure 1 above a step called Baseline Roll Forward. The value for this step will always be the total of all steps for the preceding Year (unless exceptionally you choose to make certain steps non-recurring).
Groups of steps
You may wish to create a rollup in the ActivityStep dimension, called say ActivityStepGroup, to associate steps in groups which are more meaningful for general analytical purposes than the detailed and sometimes technical individual steps. Such groups might be, for example, Baseline, Growth, Efficiencies, Service Changes and so on.
Fig 1 — Medical beds over 5 years, by step, for the Do Nothing and Mid Range scenarios
Click on the image to enlarge it.
Drill through
You can double-click on any number in the pivot table to view in a table the detailed records which underlie that number, including the values for every dimension in the Pivot Table.
Drill-through will return a maximum of 1,000 rows.
Figure 2 below shows an example.
Fig 2 — Drill through, showing details of inpatients staying more than 50 days.
Click on the image to enlarge it.
Building formatted Excel reports with cube formulae
As well as using a pivot table to interrogate the Raedfast Analysis Services database, you can also use cube formulae. These allow you to point an individual cell in an Excel spreadsheet at a cell in the Analysis Services database, to retrieve either a number, or a text element from a dimension.
Using cube formulae you can lay out worksheet reports freely, no longer constrained by the conventions of the pivot table.
The easiest way to get started with cube formulae is to take an existing pivot table and convert it to cube formulae. To do this, click anywhere on the pivot table, then click on Pivot Table Analyze, then on OLAP tools, and on Convert to Formulas, as shown below.
You can also choose to convert report filters to cube formulae, or leave them as drop-downs.
Once the pivot table has been converted, you will see that the numbers in the table have been changed to CUBEVALUE formula, and the text dimension elements to a CUBEMEMBER formula, as shown below:
The table of data initially looks exactly as it did before conversion, but whereas this format was fixed before, after conversion to cube formulae you can move any individual cell around anywhere you please.
Nor is it necessary to begin with a pivot table and convert it: you can lay out cube formulae directly on the worksheet to build reports from scratch.
This is not the place for a full explanation of the use of these and other related formulae. You will readily find many good introductions online.