Reporting strategies
Introduction
In the section Viewing model output we looked at how the modeller analyses Raedfast output as they build a model
In this section we look at the ways in which analysts might interact with Raedfast model output, once a model has been built and published for wider discussion.
Before you read this section, it will be helpful to have read the section on The Raedfast Database.
Open access
Raedfast gives you unlimited access to the model output data, allowing you to pull whatever analyses you need into your package of choice, be that Qlik®, Tableau®, Microsoft Power BI®, Python®, R, Microsoft Access®, etc.
Avoiding contention with the live Raedfast database
Bear in mid that interactive querying of the live database may give rise to contention, as reporting users attempt to access database tables which the modeller is changing.
Also, if database rules are being changed and the model reprocessed, the database will be a moving target for reporting users.
We therefore recommend that for repeated interactive querying, in which you are taking Raedfast data directly from the database to the report canvass, you use a stable copy of the live database.
If you are taking occasional extracts of data from the Raedfast database into an external package for repeated querying there, it may be fine to take the data from the live database, but you should co-ordinate with the modeller to avoid contention.
Querying the Analysis Services database
If you are accessing Analysis Services from an external package, the training already given in the section Viewing Model Outputs should have given you the understanding of the structure you need.
To summarise — the Raedfast Analysis Services database is a single Analysis Services Model, comprising two sets of Measures (ActivityHistory and ActivityModel), and a set of related Dimensions (Age, Specialty, etc.).
You will need to link your analytical package to the Analysis Services database, and define the content you want in your query using DAX, MDX or SQL, according to the capacities of the tool.
Analysis Services and data export
It is likely that your use of the Analysis Services database will be interactive, taking Raedfast data directly from the database to the report canvass.
If you wish to extract Raedfast data into an external package for repeated querying there, you will normally want to take the data from SQL Server and not Analysis Services. SQL Server is more generally accessible and you are able to use familiar SQL skills rather than DAX or MDX, which are in any case not designed to support data extraction.
Querying the SQL Server database
Interactive querying versus data export
In querying the SQL Server database, your purpose may be …
Interactive querying of SQL Server, with data passing directly from the Raedfast SQL Server database to the reporting canvass
Extraction of data from SQL Server into another package, for subsequent querying there
For the first of these we recommend a “flat file” approach. For the second we recommend a “star schema” approach, unless the volume of data extracted is low.
The two approaches are described in the following sections.
The “flat file” approach
If you want to query the SQL Server database interactively, you should normally find everything you need in the single view star.ActivityModel.
This contains the entire model output, in a denormalised form with all Measures, Dimension values and Rollup values in a single record.
The view also contains Dimension and Rollup key fields, and Dimension and Rollup sort fields which you may wish to use to sort your reporting output. For example, the view contains a Site column, and also a SiteNo column (the key field) and a SiteSeqno column (for sorting).
The Microsoft Acces example below shows how the star.ActivityModel view is used:
Click on the image to enlarge it.
In the image above we see that the star.ActivityModel view has been linked to Access, and a query built to summarise Measures Cases, Beds and Places by ActivityScenario, ActivityStep, AgeAdultChild, Pod, SpecialtyName and Year.
We can call this approach — building a query using star.ActivityModel alone — the “flat file” approach.
The “star schema” approach
The question of volume
The flat file approach described above is ideal for interactive querying.
But when you adopt this approach — using the single view star.ActivityModel — you are inevitably taking the text value for every Dimension or Rollup field you need (specialty, site, etc.), for every ActivityModel record extracted. The text value is of course long, so that each record you are extracting is relatively long.
This will not matter of the volume of records is low, as it is in the case of interactive querying direct to the report canvass.
And even if you are extracting data into an external package for repeated querying there, the flat file approach may be fine if the volume of data extracted is low.
Extracting a star schema
But you are likely to run into trouble if the volume of data you wish to extract is high.
To meet this case you should use the “star schema” approach.
This involves three steps:
Extract the records you want from star.ActivityModel, taking required Measures, and Dimension or Rollup keys, but ignoring the Dimension and Rollup text values
Extract the required star Dimension or star Rollup tables complete
For each Dimension or Rollup, create a relationship in your package between the Dimension or Rollup key on ActivityModel and the key field of the related Dimension or Rollup table — e.g. between ActivityModel.SiteNo and Site.SiteNo, or between ActivityModel.AgeAdultChildNo and AgeAdultChild.AgeAdultChildNo.
With this approach the volume of data extracted is much shorter than it would be using the flat file approach, because for each record in the high-volume ActivityModel table you are taking only the short Dimension or Rollup integer keys, and not the long text fields.
An example
The process is illustrated in the Microsoft Access example below. This repeats the beds summary example used in the section on the flat file approach above, but this time the data is extracted using the star schema approach.
Step 1
Click on the image to enlarge it.
In the Access query shown above the star.ActivityModel view has been linked, and used as the basis for a make-table query, which extracts the required ActivityModel measures and keys into the local Access table BedsSummaryFact.
Note that the text fields are not taken so the extracted record is short.
Step 2
You can see in the Access Objects list on the left hand side of the image above that all the required Dimension or Rollup star views (star.ActivityScenario etc.) have also been separately extracted from SQL Server into Access.
Note that if you are summarising data to a rollup level then it is the relevant Rollup star view you want. For example, here we are taking not star.Age but star.AgeAdultChild and not star.Specialty but star.SpecialtyName.
Note also that the star views for Dimensions and Rollup tables contain all of the related rollup columns. So for example star.Site includes the rollup columns SiteName, SiteHigh and so on. You do not need to extract these separately.
Step 3
Click on the image to enlarge it.
In this second Access query shown in the image above, we see that relationships have been defined between the Dimension or Rollup keys in the BedsSummaryFact table, and the related keys in the Dimension or Rollup tables. So BedsSummaryFact.ActivityScenarioNo is linked to ActivityScenario.ActivityScenarioNo, and so on.
We can now use this second query within Access just as we would use star.ActivityModel itself — as a single denormalised table containing all the fields we need to create a report, or indeed many reports.
Extracting the entire Raedfast model output
The effect then of the star schema approach in general is to recreate (all or part of) the star.ActivityModel view inside your analytical tool, but in an efficient form, as a basic “fact” table of measures and integer keys, and a set of related dimension tables complete with rollups.
Using this efficient star-schema approach you can if you wish extract the entire Raedfast model output to another package.