The Raedfast database

Database schemas

 

The Raedfast database uses different SQL Server Schemas to classify and manage database objects. It will be helpful to you in managing the database to understand the purpose of each.

List of Schemas

dbo

User objects: Dimension and Data Tables.

rb, rbf, rbapp

System objects: metadata tables, stored procedures, functions

star

Denormalised views of objects in the dbo Schema

rbstar

Denormalised views of objects in the rb Schema

wk1, wk2

Transient objects used in processing

local

Objects which have been created specifically for your site, and which are not part of the standard Raedfast application

Creating your own objects

If you create your own objects — tables, views, etc. — please put them either in the dbo Schema or in a new Schema which you create, in order to preserve the meaning and significance of the existing Schemas.

Database design

 

Dimension and Data Tables

Raedfast data is organised in dimensions and data tables.

Dimensions are business entities such as Specialty, Site, Age, etc.

Data tables include measures such as Cases, Beds, etc. and a set of dimensions applicable to the measures. The Raedfast application contains two data tables:

  • ActivityHistory

  • ActivityModel

Each dimension contains a base table, which has the name of the dimension itself, and may also contain any number of rollup tables, arranged in one or more hierarchies.

Normal form

Data, dimension and rollup tables are fully normalised.

Surrogate keys

All tables use a surrogate integer key for high performance, auto-generated by SQL Server.

Referential integrity

Referential integrity is enforced by foreign keys from data table to dimension base table, and from dimension base table to rollup table, and from child rollup table to parent rollup table. It is impossible to have a child record which relates to a non-existent parent record.

Partitions

If your version of SQL Server supports partitioning, the ActivityHistory and ActivityModel tables are partitioned — ActivityHistory on the Version Dimension (i.e. on the VersionNo column) and ActivityModel on the ActivityScenario Dimension (i.e. on the ActivityScenarioNo column). This ensures that versions and scenarios can be replaced efficiently. In particular it enables entire versions and scenarios to be deleted using the truncate command instead of the delete command, avoiding long delays as delete operations are logged.

Star views

 

Every Raedfast data, dimension and rollup table has an associated star view. This is a SQL Server view of the table, held in the star schema, which presents the table and its parent and higher ancestor tables in a single SQL view. For example, star.Specialty would include the Specialty base table, but also the columns of any rollup tables such as Division or TheatreGroup, including rollups of rollups, all the way to the top of each hierarchy.

Data Tables have star views too, and these include the columns of the star views of all the dimensions to which the data table is linked, including all the rollup table columns.

These views are automatically maintained by Raedfast, so that as rollups are added, renamed and deleted the star views are automatically adjusted.

When looking at a data table in SQL Server, or at a dimension table with rollups, you will normally want to use the star view, e.g. star.ActivityModel rather than dbo.ActivityModel.

Analysis Services integration

 

Raedfast optionally includes an interface to Analysis Services Tabular.

Automatic update of Analysis Services

The linked database is maintained automatically in the course of using the Raedfast front-end application.

  • When model processing is complete, data for the processed scenarios is written out to the Analysis Services database.

  • When a rollup table is added, renamed, moved or deleted, this change is immediately replicated in the Analysis Services database.

  • When dimension data is changed, the related Analysis Services table is automatically updated.

  • When data is loaded into the ActivityHistory table using the Excel Add-In, the data is automatically pushed into the Analysis Services database.

Rebuilding the Analysis Services database using a Task

Some changes however are not automatically replicated — specifically and most frequently the adding, renaming and deleting of measures, which is done through Raedfast Tasks. When measures are changed, the Analysis Services database must be rebuilt, using the Task Rebuild Analysis Services Database.

Database name

The linked database has the same name as the related Raedfast SQL Server database.

Database structure

The Analysis Services database uses a Star Schema view of the SQL Server database. Each data table is loaded separately from its dimension tables, but each dimension table is loaded in the form of its star view, as a single table, including all the dimension’s rollup columns.

Thus the tables loaded include dbo.ActivityModel, but star.Specialty, star.Age, etc.

Partitioning

If your version of SQL Server supports Analysis Services partitioning, the ActivityHistory and ActivityModel tables in Analysis Servicesare are partitioned — ActivityHistory on the Version dimension (i.e. on the VersionNo column) and ActivityModel on the ActivityScenario dimension (i.e. on the ActivityScenarioNo column). This ensures that Versions and Scenarios can be reprocessed in Analysis Services, without the need to reprocess the entire ActivityHistory or ActivityModel table.

Implementing the Analysis Services interface

Refer to the Installation section to see how to implement the Analysis Services interface.

Manually refreshing the Analysis Services database

To refresh the Analysis Services database manually, execute stored procedure rb.AsBuildDatabase.

This will

  • delete the existing Analysis Services database

  • create a new database containing the ActivityHistory and ActivityModel tables and their constituent measures, dimensions and rollups

  • rebuild the System Admin and Reader roles in the Analysis Services database (see the Security section for more details)

Turning off the Analysis Services interface

You can turn off the integration with Analysis Services by executing Task SetAutomaticallyUpdateAnalysisServices On or Off in the Raedfast front end.

Rules meta-data tables

 

Raedfast holds modelling rules in metas-data tables in the SQL Server database.

These are in the rb schema.

To analyse the content of rules, you can use the denormalised view rbstar.ModelRuleValue. This contains a record for every field value for every rule, with the associated step and model function data.

You can use this view to answer questions such as Which Steps is Pod x used in?

This view is available to the end-user through the Raedfast front-end, by using the Task Analyse Rules.