Level-Based Measure Calculations Example
A level-based measure is a column whose values are always
calculated to a specific level of aggregation. For example, a company might
want to measure its revenue based on the country, based on the region, and
based on the city. You can set up columns to measure CountryRevenue,
RegionRevenue, and CityRevenue.
The measure AllProductRevenue is an example of a level-based
measure at the Grand Total level. Level-based measures allow a single query to
return data at multiple levels of aggregation. They are also useful in creating
share measures, that are calculated by taking some measure and dividing it by a
level-based measure to calculate a percentage. For example, you can divide
salesperson revenue by regional revenue to calculate the share of the regional
revenue each salesperson generates.
To set up these calculations, you need to build a
dimensional hierarchy in your repository that contains the levels Grandtotal,
Country, Region, and City. This hierarchy will contain the metadata that
defines a one-to-many relationship between Country and Region and a one-to-many
relationship between Region and City. For each country, there are many regions
but each region is in only one country. Similarly, for each region, there are
many cities but each city is in only one region.
Next, you need to create three logical columns
(CountryRevenue, RegionRevenue, and CityRevenue). Each of these columns uses
the logical column Revenue as its source. The Revenue column has a default
aggregation rule of SUM and has sources in the underlying databases.
You then drag the CountryRevenue, RegionRevenue, and
CityRevenue columns into the Country, Region, and City levels, respectively.
Each query that requests one of these columns will return the revenue
aggregated to its associated level.
Figure 13
shows what the business model in the Business Model and Mapping layer would
look like for this example.
Figure 13. Example Business Model in the Business Model
and Mapping Layer
  |
No comments:
Post a Comment