Databases Reference
In-Depth Information
the appropriate people for follow-up. The discovery and resolution of each
issue should be included in the issue log. This provides a record of the
reasoning behind modeling decisions.
Building the Business Measures Worksheet
While developing the dimensional model, many different facts may be iden-
tified. Some are found as data elements that are stored in the source systems.
Sometimes the facts are the result of a calculation. These calculated facts are
called
derived facts
. Facts that can not be derived but need to be stored are called
base facts
. The complete set of facts, base and derived, is referred to as
business
measures
. The base facts are typically what are included in the dimensional
model and stored in the database. Derived facts are typically calculated
on-the-fly, when a report is created.
It is helpful to begin to compile the list of all the business measures that will
be needed while the dimensional model is being created. The list can be used
to do the following:
Review the derived facts to ensure that all of the base facts needed for
these calculations have been included in the model.
Determine whether a derived fact should be stored in the database, if
the calculation is extremely complex or time consuming. It may be more
efficient to perform the calculation during the ETL process.
Set up the data access environment later, as described in Chapter 11.
Table 7-14 shows a sample of a business measures worksheet. The definition
of base columns must match the definition of that fact in the fact group
documentation. Note that the derived fact Average Minutes per Call must be
recalculated each time. It is not mathematically valid to apply an average to a
measure that is already an average.
These business measures can be reported by any attribute in any dimension
that applies to the base facts used in the calculation. It is not necessary
to document specific ways that each of these measures is to be reported.
For example, Total Hours Worked can be reported by any attribute in the
Date, Time, Customer, Employee, or Employee Task dimensions. The business
dimensional model and the Business Measures Worksheet can be used to
ensure that the required reports can be produced.
The business community is responsible for determining what business
measures they need. Members of the project team may be able to do some
background work to pull together a starter list of measures based upon reports
that have been requested. The most time-consuming part of developing this
worksheet is for the business community to agree on the official formulas
that are to be used. It can take several months for different groups across the
