Example 10-1 Level-Based Measure Calculations 基于层级的指标计算
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.
基于层级的指标的值总是在特定层级上聚合。例如，一个公司要度量分别基于国家，地区，城市上的收入。你可以设置三列去度量国家收入，地区收入，城市收入。
When a query containing a presentation hierarchy includes a level-based measure column, and the query grain is higher than the level of aggregation specific to the column, the query results return null. Note that if the request only contains ordinary columns and no hierarchical columns, the level-based measure is not replaced with null.
当一个查询包含一个展现层级，该层级包含一个基于层级的指标列，而且这个查询粒度大于该列的聚合层级，那么改查询返回null。需要注意的是，如果请求仅仅包含原始列，并没有层级结构列，那么基于层级的指标不会显示为null。
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.
指标“所有产品收入”是基于Grand Total 层级上的一个指标举例。使用基于层级的指标，可以在一个单独的查询中返回基于多个层级聚合的数据。这在创建占比指标时非常有用，占比指标是用“普通指标”除以“基于层级的指标”计算出一个百分比而得出的。举例，你可以用“销售员收入”除以“地区收入”计算出地区收入中每个销售人员所占的份额。
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 contains 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.
然后，你要创建三个逻辑列（国家收入，地区收入，城市收入）。每列使用逻辑列收入作为它的源。收入的默认聚合规则是SUM，它的源是最根本的数据库。
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 returns the revenue aggregated to its associated level.
然后你拖动国家收入，地区收入，城市收入到相应的层级。每次查询返回基于层级聚合后的收入。
Figure 10-3 shows what the business model in the Business Model and Mapping layer looks like for this example.
Figure 10-3 Example of Business Model in the Business Model and Mapping Layer
Description of "Figure 10-3 Example of Business Model in the Business Model and Mapping Layer"
In the Geography Dimension, the CountryRevenue and RegionRevenue columns are attributes of the Country and Region levels. In the Sales Facts table, the Revenue column has a default aggregation rule of SUM and is mapped to physical detail data or physical aggregate data. CountryRevenue and RegionRevenue columns use the Revenue column as their source.
Example 10-2 Grand Total Dimension Hierarchy
You might have a product dimensional hierarchy with levels TotalProducts (Grand Total level), Brands, and Products. Additionally, there might be a column called Revenue that is defined with a default aggregation rule of Sum. You can then create a logical column, AllProductRevenue, that uses Revenue as its source (as specified in the General tab of the Logical Column dialog). Now, drag AllProductRevenue to the Grand Total level. Each query that includes this column returns the total revenue for all products. The value is returned regardless of any constraints on Brands or Products. If you have constraints on columns in other tables, the grand total is limited to the scope of the query. For example, if the scope of the query asks for data from 1999 and 2000, the grand total product revenue is for all products sold in 1999 and 2000.
If you have three products, A, B, and C with total revenues of 100, 200, and 300 respectively, then the grand total product revenue is 600 (the sum of each product's revenue). If you have set up a repository as described in this example, the following query produces the results listed:
SELECT product, productrevenue, allproductrevenue
FROM sales_subject_area
WHERE product IN 'A' or 'B'
The results are as follows:
PRODUCT PRODUCTREVENUE ALLPRODUCTREVENUE
A 100 600
B 200 600
In this example, the AllProductRevenue column always returns a value of 600, regardless of the products on which the query constrains.