The Spectre Build engine allows you to build a single cBase with all of your data. Build scripts support the creation of new columns of data by using calculations. On the other hand, cPlan scripts also allow you to add columns by using calculations. How do you decide where to locate your calculations?
If you need detail rows, do it in the Build. If you need summarized values, do it in the cPlan. If either works (for instance, just adding or subtracting numbers) then either location will work.
In general, cPlan calculations work on summary data, and have a summary function around any referenced field—for example: sum(), average(), max(), min(), info(), any(), first(), last(). Build calculations work on individual records, and cannot use summary functions. Whether or not you want summary operations will tell you where the calculation should go.
Consider these examples:
Average Length of Stay: this is a division between two summarized values. It cannot be done in a Build, since it requires summarized values:
sum(value("Discharge Days"))/sum(value("Discharges"))
Discharge Days: this needs to look at admit date and discharge date, and do operations on them, on every individual row. It should be done in a Build—it will not work in a cPlan:
if(value("Discharge Date")=value("Admit Date"),1,value("Discharge Date")-value("Admit Date"))
Profit: the simplest of calc examples works in either location, although it looks different.
In a Build, value("Revenue")-value("Cost")
In a cPlan, calc("Revenue")-calc("Cost")
Other considerations:
- String operations that represents fixed info columns—string operations are among the slowest run-time operations and are hard to tune for speed; putting them in the Build is the simplest way to ensure good performance
- Diving on the result of a calc is required—adding this type of column in the Build is the most efficient; making use of expression dimensions in a cPlan is another alternative.
There are trade-offs when you consider what columns should be built into the cBase. Columns take up space on disk, but do not incur additional processing. Once you build your cBase and validate it, you want to avoid going back and reconfiguring the Build. Calculations in cPlans are easy to adjust without impacting the cBase.
NOTE: With Spectre, a cPlan can have over 8 million calculations.
See also: