Oracle® Business Intelligence Server Administration Guide > Creating and Administering the Business Model and Mapping Layer in an Oracle BI Repository > Process of Creating and Administering Dimensions >
Creating Dimension Levels and Keys
A dimension contains two or more logical levels. The recommended sequence for creating logical levels is to create a grand total level and then create child levels, working down to the lowest level. The following are the parts of a dimension:
- Grand total level. A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A grand total level does not contain dimensional attributes and does not have a level key. However, you can associate measures with a grand total level. The aggregation level for those measures will always be the grand total for the dimension.
- Level. All levels, except the Grand Total level, need to have at least one column. However, it is not necessary to explicitly associate all of the columns from a table with logical levels. Any column that you do not associate with a logical level will be automatically associated with the lowest level in the dimension that corresponds to that dimension table. All logical columns in the same dimension table have to be associated with the same dimension.
- Hierarchy. In each business model, in the logical levels, you need to establish the hierarchy (parent-child levels). One model might be set up so that weeks roll up into a year. Another model might be set up so that weeks do not roll up. For example, in a model where weeks roll up into a year, it is implied that each week has exactly one year associated with it. This might not be true for calendar weeks, where the same week could span two years. Some hierarchies might require multiple elements to roll up, as when the combination of month and year roll up into exactly one quarter. You define the hierarchical levels for your particular business so that results from analyses conform to your business needs and requirements.
- Level keys. Each logical level (except the topmost level defined as a Grand Total level) needs to have one or more attributes that compose a level key. The level key defines the unique elements in each logical 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 logical level may have more than one level key. When that is the case, specify the key that 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 logical level should have one level key that will be displayed when an Answers or Intelligence Dashboard user clicks to drill down. This may or may not be the primary key of the level. To set the level key to display, select the Use for drill down check box on the Level Key dialog box.
Be careful using level keys such as Month whose domain includes values January, February, and so on—values that 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, for example, Year. To add Year, click the Add button in this dialog and select the logical column from the dialog that is presented.
- Time dimensions and chronological keys. You can identify a dimension (for example, Year) as a time dimension. At least one level of a time dimension must have a chronological key. The following is a list of some guidelines you should use when setting up and using time dimensions:
- At least one level of a time dimension must have chronological key. For more information, see Selecting and Sorting Chronological Keys in a Time Dimension.
- All time series measures using the Ago and ToDate functions must be on time levels. Ago and ToDate aggregates are created as derived logical columns. For information, refer to About Time Series Conversion Functions.
- Any physical table that is part of a time logical table cannot appear in another logical table. This prevents using date fields from calendar table as measures.
- Physical tables in time sources, except the most detailed ones, cannot have joins to table outside their source. The join has to be between the time table and the fact table. The join can only be based on foreign key; it cannot be a complex join.
- Ago or ToDate functionality is not supported on fragmented logical table sources. For more information, refer to About Time Series Conversion Functions.
To create and administer dimension hierarchy levels, perform the following tasks:
Creating a Logical Level in a Dimension
When creating a logical level in a dimension, you also create the hierarchy by identifying the type of level and defining child levels. For more information about creating hierarchies for a multidimensional data source, refer to Creating the Business Model Layer for a Multidimensional Data Source.
To define general properties for a logical level in a dimension
- In the Business Model and Mapping layer, right-click a dimension and choose New Object > Logical Level.
- In the Logical Level dialog box, in the General tab, specify a name for the logical level.
- Specify the number of elements that exist at this logical 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.
This number is used by the Oracle BI Server when picking aggregate sources. The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate.
- When the following criteria is met, perform the specified action:
- To define child logical levels, click Add.
- In the Browse dialog box, select the child logical levels and click OK.
The child levels appear in the Child Levels pane.
- To remove a previously defined child level, select the level in the Child Levels pane and click Remove.
The child level and all of its child levels are deleted from the Child Levels pane.
- (Optional) Type a description of the logical level.
- Click OK.
Associating a Logical Column and Its Table with a Dimension Level
After you create all logical levels within a dimension, you need to drag and drop one or more columns from the dimension table to each logical level except the Grand Total level. The first time you drag a column to a dimension it associates the logical table to the dimension. It also associates the logical column with that level of the dimension. To change the logical level to be associated with that logical column, you can drag a column from one logical level to another.
NOTE: The logical column(s) comprising the logical key of a dimension table must be associated with the lowest level of the dimension.
After you associate a logical column with a dimension level, the tables in which these columns exist appear in the Tables tab of the Dimensions dialog box.
To verify tables that are associated with a dimension
- In the Business Model and Mapping layer, double-click a dimension.
- In the Dimensions dialog box, click the Tables tab.
The tables list contains tables that you associated with that dimension. This list of tables includes only one logical dimension table and one or more logical fact tables (if you created level-based measures).
- Click OK or Cancel to close the Dimensions dialog box.
Identifying the Primary Key for a Dimension Level
Use the Keys tab in the Logical Level dialog box to identify the primary key for a level.
To specify a primary key for a dimension level
- In the Business Model and Mapping layer, expand a dimension and then expand the highest level (grand total level) of the dimension.
- Double-click a logical level below the grand total level.
- In the Logical Level dialog box, click the Keys tab.
- In the Keys tab, from the Primary key drop-down list, select a level key.
NOTE: If only one level key exists, it will be the primary key by default.
- To add a column to the list, perform the following steps:
- In the Logical Level dialog box, click New.
- In the Logical Level Key dialog box, type a name for the key.
- In the Logical Level Key dialog box, select a column or click Add.
- If you click Add, in the Browse dialog box, select the column, and then click OK.
The column you selected appears in the Columns list of the Logical Level Key dialog box and the check box is automatically selected.
- If the level is in a time dimension, you can select chronological keys and sort the keys by name.
- (Optional) Type a description for the key and then click OK.
- Repeat Step 2 through Step 7 to add primary keys to other logical levels.
- In the Logical Level dialog box, click OK.
Selecting and Sorting Chronological Keys in a Time Dimension
At least one level of a time dimension must have a chronological key. For any level, you can select one or more chronological keys and then sort keys in the level, however, only the first chronological key is used at this time.
NOTE: To use a dimension as a time dimension, you must select the Time Dimension check box in the Dimension dialog box.
To select and sort chronological keys for a time dimension
- In the Business Model and Mapping layer, expand a time dimension and then expand the highest level (grand total level) of the dimension.
NOTE: For a dimension to be recognized as a time dimension, you need to select the Time Dimension check box in the Dimension dialog box.
- Double-click a logical level below the grand total level.
- In the Logical Level dialog box, click the Keys tab.
- To select a chronological key, in the Keys tab, select the Chronological Key check box.
- To sort chronological keys, in the Keys tab, double-click a chronological key.
- In the Chronological Key dialog box, select a chronological key column, click Up or Down to reorder the column, and then click OK.
Adding a Dimension Level to the Preferred Drill Path
You can use the Preferred Drill Path tab to identify the drill path to use when Oracle BI Presentation Services users drill down in their data requests. You should use this only to specify a drill path that is outside the normal drill path defined by the dimensional level hierarchy. It is most commonly used to drill from one dimension to another. You can delete a logical level from a drill path or reorder a logical level in the drill path.
To add a dimension level to the preferred drill path
- Click the Add button to open the Browse dialog box, where you can select the logical levels to include in the drill path. You can select logical levels from the current dimension or from other dimensions.
- Click OK to return to the Level dialog box.
The names of the levels are added to the Names pane.
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
Grand Total Dimension Hierarchy Example
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 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
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.
Creating Dimensions Automatically
You can set up a dimension automatically from a logical dimension table if a dimension for that table does not exist. To create a dimension automatically, the Administration Tool examines the logical table sources and the column mappings in those sources and uses the joins between physical tables in the logical table sources to determine logical levels and level keys. Therefore, it is best to create a dimension in this way after all the logical table sources have been defined for a dimension table.
The following rules apply:
- Create Dimensions is only available if the selected logical table is a dimension table (defined by 1:N logical joins) and no dimension has been associated with this table.
- An automatically created dimension uses the same name as the logical table, adding Dim as a suffix. For example, if a table is named Periods, the dimension is named Periods Dim.
- A grand total level is automatically named [name of logical table] Total. For example, the grand total level of the Periods Dim table is Periods Total.
- When there is more than one table in a source, the join relationships between tables in the source determine the physical table containing the lowest level attributes. The lowest level in the hierarchy is named [name of logical table] Detail. For example, the lowest level of the periods table is Periods Detail.
- The logical key of the dimension table is mapped to the lowest level of the hierarchy and specified as the level key. This logical column should map to the key column of the lowest level table in the dimension source.
- If there are two or more physical tables in a source, the columns that map to the keys of those tables become additional logical levels. These additional level names use the logical column names of these key columns.
- The order of joins determines the hierarchical arrangement of the logical levels. The level keys of these new logical levels are set to the logical columns that map to the keys of the tables in the source.
- If there is more than one logical table source, the tool uses attribute mappings and physical joins to determine the hierarchical order of the tables in the physical sources. For example, you might have three sources (A, B, C) each containing a single physical table and attribute mappings for 10, 15, and 3 attributes, respectively, (not counting columns that are constructed from other logical columns). The following is a list of the results of creating a dimension for this table automatically:
- The Administration Tool creates a dimension containing 4 logical levels, counting the grand total and detail levels.
- The key of the table in source B (that has the greatest number of columns mapped and contains the column mapping for the logical table key) should be the level key for the detail level.
- The parent of the detail level should be the logical level named for the logical column that maps to the key of the physical table in source A.
- Any attributes that are mapped to both A and B should be associated with level A.
- The parent of level A should be the logical level named for the logical column that maps to the key of the physical table in source C.
- Any columns that are mapped to both A and C should be associated with level C.
- Table joins in a physical source might represent a pattern that results in a split hierarchy. For example, the Product table may join to the Flavor table and a Subtype table. This would result in two parents of the product detail level, one flavor level and one subtype level.
- You cannot create a dimension automatically in the following situations:
- If a dimension with joins and levels has already been created, Create Dimension will not appear on the right-click menu.
- If the table is not yet joined to any other table, the option is not available because it is considered a fact table.
- In a snowflake schema, if you use a table with only one source, and create the dimension automatically the child tables will automatically be incorporated into a hierarchy. The child tables will form intermediate levels between the grand total level and detail level. If more then one child table exists for a dimension table, while creating dimension automatically hierarchy will be split hierarchy.
To create a dimension automatically
- In the Administration Tool, open a repository.
- In the Business Model and Mapping layer of a repository, right-click a logical table.
- From the right-click menu, choose Create Dimension.
A dimension appears in the Business Model and Mapping layer.