Database Reference
In-Depth Information
data related to the sales quantity will be required to answer this question. The
measure
provides information on
a given process such as the process of selling something. The measure value, such as 15 items sold, for example,
provides information about a specific event within that process.
Often when one question is asked, another question is suggested, such as, “How much was the standard
retail price for that title?” Another question might be, “How much were the total sales for that title on that specific
sale?” For the first question, the answer could be obtained by using the actual sales price, but for the second
question, you would need to multiply the actual sales price by the sales quantity taken from specific dates to
achieve a new value.
Looking at the Titles table, you see that there is a standard retail price (Figure
4-4
). It would be tempting
to assume that this was a measure as well. It could be used as a measure at some point in time by performing a
calculation. But that might not be a mainstream calculation. Instead, it may be applicable to only a few reports.
Figure 4-4.
A typical fact table
To understand this, take a closer look at the StandardRetailPrice column. It defines information about
the title and not about an actual sale. Therefore, although the ActualSalesPrice column describes something
that occurred as part of a sales event, the StandardRetailPrice column describes an additional attribute of
an individual title. In essence, this means that the standard retail price is more descriptive of a title, and
not something that would be moved into the measures category directly. Any items that provide additional
descriptions of a process are dimensional attributes and not measures.
Granularity
Measure values typically represent the lowest level of detail tracked by a given process. For example, you could
store only a grand total for the sales of a given day by categories of products, but more likely you would instead
record the individual sales event throughout the day and by the individual product itself and not solely by a
product's category.
When you create a data warehouse, you can choose the level of detail you would like to report against. If you
want to report only on the daily totals, you can do so by aggregating the values you stored in the OLTP data as they are
turned into measures. Most of the time you will use the lowest level of detail you can obtain because it gives you the
maximum amount of reporting options. Your chosen lowest level of detail defines the
granularity
of that measure.
