Siebel Analytics Server Administration Guide > Working in a Repository's Business Model and Mapping Layer > Business Model and Mapping Layer Objects >

Working with Levels

A dimensional hierarchy contains two or more levels. The recommended procedure for creating levels is to begin at the top of the hierarchy, creating a grand total level and then creating new child levels, working down the hierarchy to the lowest level. Each dimension can have just one level that is specified as the Grand Total level.

This section includes the following topics:

Level Attributes

Once all levels within a dimension have been created, you need to drag and drop one or more columns from the logical table to each level except the Grand Total level. Logical columns associated with a level are called level attributes. All levels, except the level specified as the Grand Total level, need to have at least one level attribute. However, it is not necessary to associate all of the columns from a table explicitly with levels. All columns not associated with a level will be assumed to be associated with the lowest level of the dimension corresponding to that dimension table. All logical columns from the same logical dimension table have to be associated with the same dimension.

Attributes can also be associated with a level by selecting the dimensional level on the Level tab of the Logical Column dialog box.

Level Keys

Each level (except the topmost level defined as a Grand Total level) needs to have one or more attributes that compose a level key. Specify the level key in the Level Key dialog box. The level key defines the unique elements in each level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.

A level may have more than one level key. When that is the case, specify which key is the primary key of that level. All dimension sources which have an aggregate content at a specified level need to contain the column that is the primary key of that level. Each level should have one level key that will be displayed when a Siebel Answers user double-clicks to drill down. This may or may not be the primary key of the level. To set the level key to display, check the `Use for drill down' check box on the Level Key dialog box.

Be careful with level keys such as Month whose domain includes values January, February, and so on—values which are not unique to a particular month, repeating every year. To define Month as a level key, you also need to include an attribute from a higher level, Year, as shown. To add Year, click the Add button in this dialog and select the logical column from the dialog that is presented.

Grand Total Levels

A grand total level is a special level representing the grand total for a dimension. To specify a level as a grand total level, check the option `Grand Total Level' in the General tab of the Level dialog box. Grand total levels do not require level keys.

You can associate measures with a grand total level. For example, consider a product dimensional hierarchy with levels TotalProducts (grand total level), Brands, and Products. Consider also that there is a column called Revenue which is defined with a default aggregation rule of Sum. You can then create a logical column, AllProductRevenue, which 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 will return 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'

PRODUCT  PRODUCTREVENUE   ALLPRODUCTREVENUE 

A        100              600 

B        200              600 

In this example, the AllProductRevenue column will always return a value of 600, regardless of the products the query constrains on.

Level-Based Measure Calculations

A level-based measure is a column whose values are always calculated based on a particular level. 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 each of these—for example, CountryRevenue, RegionRevenue, and CityRevenue. The measure AllProductRevenue discussed in the previous example 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, which 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 is to create the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City; that is, 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.

You then need to create three logical columns—CountryRevenue, RegionRevenue, and CityRevenue. Each of these columns uses the logical column Revenue as its source (in the General tab of the Logical Column dialog). The Revenue column has a default aggregation rule of SUM (in the Aggregation tab of the Logical Column dialog) 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 based on the share specified by the level. Figure 13 shows what the business model in the Business Model and Mapping layer would look like to model this example.

Figure 13.  Example Business Model in the Business Model and Mapping Layer

Click for full size image

You can also set the level of a measure (or any attribute) by using the Levels tab of the Logical Column dialog box.

Creating or Editing a Level

Use the General tab to create or edit a level in the Business Model and Mapping layer.

To define general properties for the level

  1. Specify a name for the level, or accept the default.
  2. Specify the number of elements that exist at this level. If this level will be the Grand Total level, leave this field blank. The system will set to a value of 1 by default.
  3. If this level:
  4. To define child levels, click Add.
  5. Select the child levels and click OK to return to the General tab of the Level dialog box.

    6. The child levels appear in the Child Levels pane.

  6. To remove a previously defined child level, select the level in the Child Levels pane and click Remove.

    7. The child level and all of its child levels are deleted from the Child Levels pane.

  7. (Optional) Enter a description of the level.
  8. Select and complete the Keys tab. Grand Total levels are not required to have keys, although you may specify one if you wish (for example, a column returning the constant 'All Countries').

Using the Level Dialog Box—Keys Tab

Use the Keys tab to identify the primary key for a level.

To add a key

  1. Click New to open the Level Key dialog box for levels or the Logical Key dialog box for tables.
  2. Select the columns that comprise the key.
  3. Click OK to return to the Level dialog box or Logical Table dialog box.

    4. The key is added to the Primary Key drop-down list.

To edit a key

  1. Select the key you want to edit and either double-click it or click the Edit button to open the Level Key dialog box for levels or the Logical Key dialog box for tables.
  2. Make your changes, and then click OK.

Using the Level Dialog Box—Preferred Drill Path Tab

You can use the Preferred Drill Path tab to identify the drill path to use when Siebel Analytics Web users drill down in their data requests. You should use this feature only to specify a drill path that is outside the normal drill path defined by the dimensional hierarchy. This feature is most commonly used to drill from one dimension to another.

To add a level to the preferred drill path

  1. Click the Add button to open the Browse dialog box, where you can select the levels to include in the drill path. You can select levels from the current dimension or from other dimensions.
  2. Click OK to return to the Level dialog box. The names of the levels are added to the Names pane.

To delete a level from the drill path

  1. Select the level you want to delete.
  2. Click Remove, or press the Delete key.

To reorder a level in the drill path

  1. In the Names list, select the level you want to reorder.
  2. Use the drag-and-drop feature to reposition the level, or click the Up and Down buttons.

 Siebel Analytics Server Administration Guide 
 Published: 23 June 2003