| Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10540-01 |
|
 Previous |
 Next |
| Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10540-01 |
|
Previous |
Next |
In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object. Common dimensions might be time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Note that dimensions exist in the Business Model and Mapping (logical) layer and in the Presentation layer.
In each dimension, you organize logical columns into the structure of the hierarchy. This structure represents the organization rules and reporting needs required by your business. It also provide the metadata that the Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data.
There are two types of logical dimensions: dimensions with level-based hierarchies (structure hierarchies), and dimensions with parent-child hierarchies (value hierarchies). Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level. In parent-child hierarchies, members all have the same type. Oracle Business Intelligence also supports a special type of level-based dimension, called a time dimension, that provides special functionality for modeling time series data.
Because dimensions for multidimensional data sources are defined in the source, they do not require as much work compared with dimensions in other data sources. For example, you do not create dimension level keys. A dimension is specific to a particular multidimensional data source (it cannot be used by more than one) and cannot be created and manipulated individually. Additionally, each cube in the data source should have at least one dimension and one measure in the Business Model and Mapping layer.
You can expose logical dimensions to Oracle BI Answers users by creating presentation hierarchy objects that are based on particular logical dimensions. Creating hierarchies in the Presentation layer enables users to create hierarchy-based queries. See "Working with Presentation Hierarchies and Levels" for more information.
Note that you can also expose dimension hierarchies by adding one or more columns from each hierarchy level to a subject area in the Presentation layer. Oracle BI Answers supports drill-down on these hierarchical columns.
This chapter contains the following topics:
Creating and Managing Dimensions with Level-Based Hierarchies
Creating and Managing Dimensions with Parent-Child Hierarchies
Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.
The following sections explain how to create dimensions:
Manually Creating Dimensions, Levels, and Keys with Level-Based Hierarchies
Automatically Creating Dimensions with Level-Based Hierarchies
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 is 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. Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level and a common root (all) level.
For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. Day has two named parent levels called Fiscal Year and Calendar Year, which are both children of the All root level.
In the Business Model and Mapping layer, logical hierarchies are not defined as independent metadata objects, unlike hierarchies in the Presentation layer. Rather, logical hierarchies exist implicitly through the relationships between levels.
Level keys. Each logical level (except the topmost level defined as a Grand Total level) must 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 can have multiple level keys. 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 is displayed when an Oracle BI Presentation Services 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 display option in the Level Key dialog.
Be careful using level keys such as Month whose domain includes the values January, February, and so on (or in other words, 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 Add in this dialog and select the logical column from the dialog that is presented.
Level keys should be meaningful business keys (like Month_name='2010 July') rather than generated surrogate keys (like time_key='1023793'), because surrogate keys are physical artifacts that only apply to a single instance of a source table. The business name, in contrast, can map to any physical instance for that logical column. For example, month_name might map to a detailed table, an aggregate table from an aggregate star, and a column in a federated spreadsheet. Note that the Physical layer still uses the surrogate keys in the joins, so there is no performance or flexibility penalty for using business keys in the business model.
Time dimensions and chronological keys. You can identify a dimension 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 a chronological key. See "Selecting and Sorting Chronological Keys in a Time Dimension" for more information.
All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns. See "About Time Series Functions" for more information.
AGO, TODATE, and PERIODROLLING functionality is not supported either on fragmented dimensional logical table sources, or on fact sources fragmented on the same time dimension. Fact sources may be fragmented on other dimensions.See "About Time Series Functions" for more information.
Unbalanced (or ragged) hierarchy. An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.
User applications can use the ISLEAF function to determine whether to allow drilldown from any particular member. See "ISLEAF" for more information.
A missing member is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all missing nodes together.
Note that unbalanced hierarchies are not necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature, but level-based hierarchies can be unbalanced also.
Skip-level hierarchy. A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city 'Washington, D.C.' does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.
In a query, skipped levels are not displayed, and do not affect computations. When sorted hierarchically, members appear under their nearest ancestors.
A missing member at a particular level is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all skip-level nodes together.
Figure 9-1 shows a hierarchy with both unbalanced and skip-level characteristics. For example, A-Brand 4, B-LOB 3, and Type 5 are unbalanced branches, while skips are present between A-Brand 2 and Type 3, B-LOB 2 and Product 6, and others.
Figure 9-1 Hierarchy with Unbalanced and Skip-Level Characteristics
Dimension hierarchical levels can be used to perform the following actions:
Set up aggregate navigation
Configure level-based measure calculations (refer to Example 9-1).
Determine what attributes appear when Oracle BI Presentation Services users drill down in their data requests
To create and manage dimension hierarchy levels in level-based hierarchies, perform the tasks described in the following sections:
Associating a Logical Column and Its Table with a Dimension Level
Selecting and Sorting Chronological Keys in a Time Dimension
After creating a dimension, each dimension can be associated with attributes (columns) from one or more logical dimension tables and level-based measures from logical fact tables. After you associate logical columns with a dimension level, the tables in which these columns exist appear in the Tables tab of the Dimension dialog.
To create a dimension with a level-based hierarchy:
In the Business Model and Mapping layer of the Administration Tool, right-click a business model and select New Object > Logical Dimension > Dimension with Level-Based Hierarchy.
Note that this option is only available when there is at least one dimension table that has no dimension associated with it.
In the Logical Dimension dialog, in the General tab, type a name for the dimension.
The Default root level field is automatically populated after you associate logical columns with a dimension level.
If the dimension is a time dimension, select Time.
If the dimension is an unbalanced dimension, select Ragged.
If the dimension is a skip-level dimension, select Skipped Levels.
(Optional) Type a description of the dimension.
Click OK.
When creating logical levels in a dimension, you also create the hierarchy by identifying the type of level and defining child levels. See "Automatically Creating Business Model Objects for Multidimensional Data Sources" for more information about creating hierarchies for a multidimensional data source.
To define general properties for a logical level in a dimension:
In the Business Model and Mapping layer of the Administration Tool, right-click a dimension and select New Object, then select Logical Level.
In the Logical Level dialog, in the General tab, specify a name for the logical level.
For Number of elements at this 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.
The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate. For relational sources, you can retrieve the row count for the level key and use that number as the number of elements. For multidimensional sources, you can use the number of members at that level.
The Oracle BI Server uses this number when selecting which aggregate source to use. For example, when aggregate navigation is used, multiple fact sources exist at different grains. The Oracle BI Server multiplies the number of elements at each level for each qualified source as a way to estimate the total number of rows for that source. Then, the Oracle BI Server compares the result for each source and selects the source with the lowest number of total elements to answer the query. The source with the lowest number of total elements is assumed to be the fastest.
Choose one of the following options, if appropriate:
If the logical level is the Grand Total level, select Grand total level. There should be only one Grand Total level for a dimension.
If measure values at a particular level fully constitute aggregated measures at its parent level, select Supports rollup to higher level of aggregation.
To define child logical levels, click Add.
In the Browse dialog, 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.
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.
The logical column or columns that comprise 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.
For time dimensions, ensure that all time-related logical columns in the source table are defined in the time dimension. For example, if a time-related logical table contains the columns Month Name and Month Code, you must ensure that both columns are dragged to the appropriate level within the dimension. Figure 9-2 shows how to associate logical columns with a logical level.
Figure 9-2 Associating Logical Columns with a Logical Level
To verify tables that are associated with a dimension:
In the Business Model and Mapping layer of the Administration Tool, double-click a dimension.
In the Dimensions dialog, 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.
Example 9-1 and Example 9-2 show how to associate measures to different levels of level-based dimension hierarchies.
Example 9-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 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 in previous releases, results were returned for this situation, but they were not deterministic.
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 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.
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 9-3 shows what the business model in the Business Model and Mapping layer looks like for this example.
Figure 9-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 9-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.
Use the Keys tab in the Logical Level dialog to identify the primary key for a level.
To specify a primary key for a dimension level:
In the Business Model and Mapping layer of the Administration Tool, 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, click the Keys tab.
In the Keys tab, from the Primary key list, select a level key.
If only one level key exists, it is the primary key by default.
To add a column to the list, perform the following steps:
In the Logical Level dialog, click New.
In the Logical Level Key dialog, type a name for the key.
In the Logical Level Key dialog, select a column or click Add.
If you click Add, in the Browse dialog, select the column, and then click OK.
The column you selected appears in the Columns list of the Logical Level Key dialog and is automatically selected.
|
Note: You cannot use a derived logical column that is the result of aLOOKUP function as part of a primary logical level key. This limitation exists because the LOOKUP operation is applied after aggregates are computed, but level key columns must be available before the aggregates are computed because they define the granularity at which the aggregates are calculated.
You can use a derived logical column that is the result of a |
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, click OK.
At least one level of a time dimension must have a chronological key. Although you can select one or more chronological keys for any level and then sort keys in the level, only the first chronological key is used.
To select and sort chronological keys for a time dimension:
In the Business Model and Mapping layer of the Administration Tool, 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 must select Time on the General tab of the Dimension dialog. |
Double-click a logical level below the Grand Total level.
In the Logical Level dialog, click the Keys tab.
To select a chronological key, in the Keys tab, select the Chronological Key option. You may need to scroll to the right to see this option.
To sort chronological keys, in the Keys tab, double-click a chronological key.
In the Chronological Key dialog, select a chronological key column, click Up or Down to reorder the column, and then click OK.
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 Add to open the Browse dialog, then 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.
The names of the levels are added to the Names pane.
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 logical_table_name Total. For example, the Grand Total level of the Periods Dim table is Periods Total.
When there are multiple tables 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 logical_table_name 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 are multiple logical table sources, 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 four 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 can 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 does 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 are automatically incorporated into a hierarchy. The child tables form intermediate levels between the Grand Total level and detail level. If more then one child table exists for a dimension table, the hierarchy is a split hierarchy.
To create a dimension automatically:
In the Administration Tool, open a repository.
In the Business Model and Mapping layer, right-click a logical dimension table that is not associated with any dimension .
From the right-click menu, select Create Logical Dimension, then select either Dimension with Level-Based Hierarchy or Dimension with Parent-Child Hierarchy.
The new dimension is displayed in the Business Model and Mapping layer.
Estimate Levels enables administrators to automatically populate level counts for one or more dimension hierarchies. Level counts are utilized by the query engine to determine the most optimal query plan and optimizes overall system performance.
The repository must be opened in online mode and the business model must be available for queries. Then, in the Business Model and Mapping layer, you can select any of the following logical layer elements, and then execute the Estimate Levels command:
Business model. Must be available for queries. If you select this object, the Administration Tool attempts to check out all objects in the business model.
Dimension. You should run a consistency check on dimensions to ensure that the dimension is logically sound.
A combination of business models and dimensions. You can select multiple dimensions and multiple business models individually.
When run, the Estimate Levels command also launches a consistency check on the level counts as described in the following list:
Checks that a level key is valid. Columns in levels have referential integrity.
Checks the parent-child relationship. If the parent level count is greater that the child level count, an error is returned.
Generates a run report that lists all the counts that were estimated and any errors or consistency warnings.
The queries and errors are logged to nqquery.log on the Oracle BI Server.
Set the log level at 4 or higher to write this information to the log file. For more information about logging, see "Diagnosing and Resolving Issues in Oracle Business" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
To populate logical level counts automatically:
In the Administration Tool, open a repository in online mode.
Right-click one or more business models and dimension objects, and select Estimate Levels.
In the Check Out Objects dialog, click Yes to check out the objects that appear in the list.
If you click No, the action terminates because you must check out items to run Estimate Levels.
In the Administration Tool dialog, a list of the dimension level counts and any errors or warning messages appear.
When you check in the objects, you can check the global consistency of your repository.
A parent-child hierarchy is a hierarchy of members that all have the same type. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy.
This section contains the following topics:
The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:
Each individual in the organization is an employee.
Each employee, apart from the top-level managers, reports to a single manager.
The reporting hierarchy has many levels.
These conditions illustrate the basic features that define a parent-child hierarchy, namely:
A parent-child hierarchy is based on a single logical table (for example, the "Employees" table)
Each row in the table contains two identifying keys, one to identify the member itself, the other to identify the "parent" of the member (for example, Emp_ID and Mgr_ID)
Figure 9-4 shows an example of a multi-level parent-child hierarchy.
Figure 9-4 Multi-Level Parent-Child Hierarchy
The following table shows how this parent-child hierarchy could be represented by the rows and key values in an Employees table.
| Emp_ID | Mgr_ID |
|---|---|
| Andrew | null |
| Barbara | Andrew |
| Carlos | Andrew |
| Dawn | Barbara |
| Emre | Barbara |
You can expose logical dimensions with parent-child hierarchies to Oracle BI Answers users by creating presentation hierarchies that are based on particular logical dimensions. Creating hierarchies in the Presentation layer enables users to create hierarchy-based queries. See "Working with Presentation Hierarchies and Levels" for more information.
Unlike the situation with level-based hierarchies, all the dimension members of a parent-child hierarchy occur in a single logical column. In a parent-child hierarchy, the parent of a member is in another row in the same logical column, pointed to by the parent key. This is unlike a level-based hierarchy, where the parent of a member is in a different logical column in the same row. In other words, navigation in a parent-child hierarchy follows data values, while navigation in a level-based hierarchy follows the metadata structure.
In level-based hierarchies, each level is named, and occupies a position in the hierarchy that corresponds to a real-word attribute or category that is deemed useful for analysis. Unlike level-based hierarchies, where the number of levels is fixed at design time, there is no limit to the depth of a parent-child hierarchy, and the depth can change at run time due to new data."
Every Oracle BI Server parent-child hierarchy has two system-generated entities, "Total" and "Detail," that are automatically defined for each parent-child hierarchy when the logical dimension is created. The "Detail" entity contains all the hierarchy members.
These two system-generated entities are different from the implicit, inter-member levels between ancestors and descendants in a parent-child hierarchy. These implicit levels are referred to as parent-child hierarchical levels in this section.
Closely associated with levels is the concept of distance in parent-child hierarchies. The distance of one member from another is the number of parent-child hierarchical levels from the member to an ancestor or to a descendant. For example, the distance from a member to its parent is always 1, and the distance from Andrew to Emre in Figure 9-4 is 2.
In relational tables, the relationships between different members in a parent-child hierarchy are implicitly defined by the identifier key values in the associated base table.
However, for each Oracle BI Server parent-child hierarchy defined on a relational table, you must also explicitly define the inter-member relationships in a separate parent-child relationship table.
The parent-child relationship table must include four columns, as follows:
A column that identifies the member
A column that identifies an ancestor of the member
|
Note: The ancestor may be the parent of the member, or a higher-level ancestor. |
A "distance" column that specifies the number of parent-child hierarchical levels from the member to the ancestor
A "leaf" column that indicates if the member is a leaf member (1=Yes, 0=No)
The column names can be user defined. The data types of the columns must satisfy the following conditions:
The member and ancestor identifier columns have the same data type as the associated columns in the logical table that contains the hierarchy members.
Note that the example shown in Table 9-1 uses text strings for readability, but you normally use integer surrogate keys for member_key and ancestor_key, if they exist in the source dimension table.
The "distance" and "leaf" columns are INTEGER columns.
Note the following about the rows in a parent-child relationship table:
Each member must have a row pointing at itself, with distance zero.
Each member must have a row pointing at each of its ancestors. For a root member, this is a termination row with null for the parent and distance values.
Table 9-1 shows an example of a parent-child relationship table with rows that represent the inter-member relationships for the employees shown in Figure 9-4.
Table 9-1 Example Parent-Child Relationship Table
| Member_Key | Ancestor_Key | Distance | Isleaf |
|---|---|---|---|
|
Andrew |
Andrew |
0 |
0 |
|
Barbara |
Barbara |
0 |
0 |
|
Carlos |
Carlos |
0 |
0 |
|
Dawn |
Dawn |
0 |
0 |
|
Emre |
Emre |
0 |
0 |
|
Andrew |
null |
null |
0 |
|
Barbara |
Andrew |
1 |
0 |
|
Carlos |
Andrew |
1 |
1 |
|
Dawn |
Barbara |
1 |
1 |
|
Dawn |
Andrew |
2 |
1 |
|
Emre |
Barbara |
1 |
1 |
|
Emre |
Andrew |
2 |
1 |
Typically, you generate scripts to create and populate the parent-child relationship table through a wizard that you can invoke when you define the parent-child hierarchy. Note the following about the create and load scripts:
You run the create script only once, to create the parent-child relationship table in the data source.
You must run the load script after each time the data changes in the dimension table. Because of this, you typically call the load script in your ETL processing. The load script reloads the entire parent-child relationship table; it is not incremental.
If you do not choose to use the wizard, then you must create the parent-child relationship table manually and then import it into the Physical layer before associating it with the parent-child hierarchy. In this latter case, it is also your responsibility to populate the table with the data required to describe the inter-member relationships in the parent-child hierarchy.
The key elements that you must define for a parent-child hierarchy are the identifier columns for the member and the parent of the member. This basic principle applies to all parent-child hierarchies, regardless of the data source from which the hierarchy is derived.
Parent-child hierarchies that are based on relational tables must have an accompanying parent-child relationship table. See "About Parent-Child Relationship Tables" for more information.
To create dimensions with a parent-child hierarchy:
In the Business Model and Mapping layer of the Administration Tool, perform one of the following steps:
Right-click a business model and select New Object > Logical Dimension > Dimension with Parent-Child Hierarchy. Note that this option is only available if there is at least one logical dimension table in the business model that has no dimension associated with it.
Right-click a dimension table that is not associated with any dimension and select Create Logical Dimension, then select Dimension with Parent-Child Hierarchy.
In the Logical Dimension dialog, in the General tab, type a name for the dimension.
Click Browse beside the Member Key field.
The Browse window shows the logical dimension tables in the business model, each with their primary keys.
Select a Member Key for the parent-child hierarchy and click OK.
Click Browse beside the Parent Key field.
The Browse window shows the columns, other than the primary key, in the logical table that you selected in step 4.
Select a column that will be the Parent Key for the parent-child hierarchy and click OK.
If the logical table that you selected in step 4 is not from a relational table source, click OK to finish the process of creating the dimension.
If the logical table you selected in step 4 is from a relational table source, you must continue the dimension definition process by setting up the parent-child relationship table for the hierarchy. See "Defining Parent-Child Relationship Tables" for details.
For parent-child hierarchies based on relational tables, you must define a parent-child relationship table. See "About Parent-Child Relationship Tables" for more information.
When you create the parent-child relationship table, you must choose one of the following options:
Select a previously-created parent-child relationship table
Use a wizard that will generate scripts to create and populate the parent-child relationship table
To define parent-child relationship tables:
In the Logical Dimension dialog, click Parent-Child Settings.
The Parent-Child Relationship Table Settings windows appears, with the Logical Table and Logical Table Source values filled in.
Figure 9-5 shows the Parent-Child Relationship Table Settings dialog.
Figure 9-5 Parent-Child Relationship Table Settings Dialog
You can either manually define the parent-child relationship table for the hierarchy, or you can start a wizard that will perform the definition for you (recommended).
Click the Select Parent-Child Relationship Table button to start the manual process of defining the parent-child relationship table for the parent-child hierarchy.
Select the physical table that acts as the parent-child relationship table for your parent-child hierarchy. The table must already exist in the Physical layer.
The parent-child relationship table must have at least four columns that describe how the inter-member relationships are derived in the logical table selected for the hierarchy. See "About Parent-Child Relationship Tables" for more information.
Map the four columns from the physical parent-child relationship table to the fields in the Parent-Child Table Column Details area, as follows:
Select the Member Key column
Select the Parent Key column
Select the Relationship Distance column
Select the Leaf Node Identifier column
Click OK, then click OK again to finish the manual process of defining the parent-child relationship table.
Click the Create Parent-Child Relationship Table button to start the wizard.
The Generate Parent-Child Relationship Table Wizard generates SQL scripts for creating and populating the parent-child relationship table. At the end of the wizard, the Oracle BI Server stores the scripts into directories chosen during the wizard session. The scripts, when executed, will make the parent-child relationship table available to the metadata repository.
The wizard contains the following three main windows:
Script Location
Parent-Child Relationship Table Details
Preview Script
In the Generate Parent-Child Relationship Table - Script Location screen, enter the Name for the DDL Script to Generate the Parent-Child Table, and select the Location where the Generate script will be placed.
Enter the Name for the DDL Script to Populate the Parent-Child Table, and select the Location where the Populate script will be placed.
Click Next.
In the Parent-Child Relationship Table Details screen, enter the Name for the parent-child relationship table.
Click Browse beside the Catalog/Schema field to select the catalog or schema for the parent-child relationship table.
Click Next.
In the Preview Script window, you can view either or both of the scripts.
Click Finish.
In the Parent-Child Relationship Table Settings window, click OK.
In the Logical Dimension window, click OK.
If you used the Generate Parent-Child Relationship Table Wizard to generate create and load scripts, run the scripts to create and load the parent-child relationship table in your data source.
After you have created a parent-child relationship table and imported it into the Physical layer (either manually or using the Generate Parent-Child Relationship Table Wizard), you must edit Physical layer joins to include the parent-child relationship table. You also need to add the parent-child relationship table to the appropriate logical table source.
To add the parent-child relationship table to the model:
In the Administration Tool, in the Physical layer of the repository, open the Physical Diagram so that it shows the parent-child relationship table and associated dimension table and fact tables. To do this, right-click the appropriate physical tables and select Physical Diagram > Selected Object(s) Only.
Delete the direct joins from the dimension table to each of the fact tables.
Create joins from the dimension table to each of the fact tables through the parent-child closure table, as follows:
Create a join from the dimension table to the parent-child relationship table using the ancestor key.
Create joins from the parent-child relationship table to the fact tables using the member key.
Figure 9-6 shows joins from a dimension table to fact tables that go through a parent-child relationship table.
Figure 9-6 Physical Layer Joins Through a Parent-Child Relationship Table
In the Business Model and Mapping layer, double-click the logical table source for the logical dimension table that is used in your parent-child hierarchy.
In the General tab of the Logical Table Source dialog, click the Add button.
Browse to locate the parent-child relationship table in the Physical layer and click Select.
Click OK in the Logical Table Source dialog.
For parent-child hierarchies based on relational tables, you must ensure that the data in the parent-child relationship table accurately reflects the inter-member relationships in the dimension.
You may have created scripts manually to create and populate the parent-child relationship table, or you may have used the Generate Parent-Child Relationship Table Wizard to create the scripts. You must run these scripts, adapting them if necessary, as often as required to guarantee the integrity of the parent-child relationships in the hierarchy. You typically want to add the Populate script to your ETL process so that it runs after the dimension table is updated.
Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods. For example, time series functions enable comparisons between current sales and sales a year ago, a month ago, and so on.
Because SQL does not provide a direct way to make time comparisons, you must model time series data in the Oracle BI repository. First, set up time dimensions based on the period table in your data warehouse. Then, you can define measures that take advantage of this time dimension to use the AGO, TODATE, and PERIODROLLING functions. At query time, the Oracle BI Server then generates highly optimized SQL that pushes the time offset processing down to the database whenever possible, resulting in the best performance and functionality.
This section contains the following topics:
Time series functions operate on time-oriented dimensions. To use these functions on a particular dimension, you must designate the dimension as a Time dimension and set one or more keys at one or more levels as chronological keys. These keys identify the chronological order of the members within a dimension level.
Time series functions include AGO, TODATE, and PERIODROLLING. These functions let you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically. The time series functions calculate AGO, TODATE, and PERIODROLLING functions based on the calendar tables in your data warehouse, not on standard SQL date manipulation functions.
Figure 9-7 shows a sample report that includes several measures derived using time series functions.
Figure 9-7 Example Measures Derived Using Time Series Functions
Several different grains may be used in the time query, such as:
Query grain. The lowest time grain of the request. In the report example shown in Figure 9-7, the query grain is Month.
Time Series grain. The grain at which the aggregation or offset is requested, for both AGO and TODATE functions. In the report example shown in Figure 9-7, the time series grain is Quarter. Time series queries are valid only if the time series grain is at the query grain or longer. Note that the PERIODROLLING function does not have a time series grain; instead, you specify a start and end period in the function.
Storage grain. The report example shown in Figure 9-7 can be computed from daily sales or monthly sales. The grain of the source is called the storage grain. A chronological key must be defined at this level for the query to work, but performance is generally much better if a chronological key is also defined at the query grain.
Note that queries against time series data must be an exact match to hit the query cache. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about the Oracle BI Server query cache.
The following sections describe the time series conversion functions:
The AGO function offsets the time dimension to display data from a past period. This function is useful for comparisons, such as Dollars compared to Dollars a Quarter Ago. Note that the value of "Dollars Qago" for month 2008/08 equals the value of "Dollars" for month 2008/05.
Figure 9-8 shows example values for the Dollars and Dollars Qago measures.
Figure 9-8 Example Dollars and Dollars Qago Measures
In the example shown in Figure 9-8, the Dollars Qago measure is derived from the Dollars measure.
In Expression Builder, the AGO function has the following template:
Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)
<<Measure>> represents the logical measure column from which you want to derive. In this example, you would select the measure "Dollars" from your existing logical fact tables.
<<Level>> is the optional time series grain you want to use. In this example, you would select "Quarter" from your time dimension.
<<Number of Periods>> is the size of the offset, measured in the grain you provided in the <<Level>> argument. For example, if the <<Level>> is Quarter and the <<Number of Periods>> is 2, the function displays dollars from two quarters ago.
Using this function template, you can create an expression for a One Quarter Ago measure, as follows:
Ago("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" , 1)
The <<Level>> parameter is optional. If you do not want to specify a time series grain in the AGO function, the function uses the query grain as the time series grain.
For example, you could define Dollars_Ago as Ago(Dollars, 1). Then, you could perform the following logical query:
SELECT Month, Dollars, Dollars_Ago
The result is the same as if you defined Dollars_Ago as Ago(Dollars, Month, 1). Alternatively, you could perform the following logical query:
SELECT Quarter, Dollars, Dollars_Ago
The result is the same as if you defined Dollars_Ago as Ago(Dollars, Quarter, 1).
See "AGO" for additional information about the AGO function syntax.
The TODATE function accumulates the measure from the beginning of the time series grain period to the current displayed query grain period. For example, Figure 9-9 shows a report with the measure "Dollars QTD," which is the Quarter To Date version of the "Dollars" measure.
Figure 9-9 Example Dollars and Dollars QTD Measures
In the example shown in Figure 9-9, Dollars QTD for Month 2008/05 is the sum of Dollars for 2008/04 and 2008/05. In other words, Dollars QTD is the sum of the values for all the query grain periods (month) for the current time series grain period (quarter). The accumulation starts over for the next quarter.
In the example shown in Figure 9-9, the Dollars QTD measure is derived from the Dollars measure.
In Expression Builder, the TODATE function has the following template:
ToDate(<<Measure>>, <<Level>>)
<<Measure>> represents the logical measure column from which you want to derive. In this example, you would select the measure "Dollars" from your existing logical fact tables.
<<Level>> is the time series grain you want to use. In this example, you would select "Quarter" from your time dimension.
Using this function template, you can create the following expression for the measure:
ToDate("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" )
Note that the query grain is specified in the query itself at run time. For example, this measure can display Quarter To Date at the Day grain, but still accumulates up to the end of the Quarter.
See "TODATE" for additional information about TODATE function syntax.
The PERIODROLLING function lets you perform an aggregation across a specified set of query grain periods, rather than within a fixed time series grain. The most common use is to create rolling averages, such as "13-week Rolling Average."
Note that because this function has no time series grain, the length of the rolling sequence is determined by the query grain. For example, "Dollars 3-Period Rolling Average" averages the last 3 months if the query grain is Month, but averages the last 3 years if the query grain is Year.
This section describes how to build two measures using the PERIODROLLING function: "Dollars 3-Period Rolling Sum," and "Dollars 3-Period Rolling Average." Figure 9-10 shows a report with these two measures.
Figure 9-10 Example Dollars, Dollars 3-Period Rolling Sum, and Dollars 3-Period Rolling Avg Measures
In the example shown in Figure 9-10, the Dollars 3-Period Rolling Sum and Dollars 3-Period Rolling Avg measures are derived from the Dollars measure.
In Expression Builder, the PERIODROLLING function has the following template:
PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
<<Measure>> represents the logical measure column from which you want to derive. To create the measure Dollars 3-Period Rolling Sum, you would select the measure "Dollars" from your existing logical fact tables.
<<Starting Period Offset>> and <<Ending Period Offset>> identify the first period and last period used in the rolling aggregation, respectively. The integer is the relative number of periods from the displayed period. In the example shown in Figure 9-10, the query grain is month, and the 3-month rolling sum starts 2 periods in the past and includes the current period. That is, for month 2008/07, the rolling sum includes 2008/05, 2008/06 and 2008/07. Therefore, to create the measure Dollars 3-Period Rolling Sum, the integers to indicate these offsets are -2 and 0.
Using this function template, you can create the following expression for the measure:
PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0)
The example shown in Figure 9-10 also shows a 3-month rolling average. To compute this measure, you can divide the rolling sum that you previously created by 3 to get a 3-period rolling average. We know to divide the rolling sum by 3 because the <<Starting Period Offset>> and <<Ending Period Offset>> fields for the rolling sum are -2 and 0.
The expression for the 3-month rolling average is:
PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0) /3
It is usually a mistake to use the AVG function to create a rolling average. AVG computes the average of the database rows accessed at the storage grain, but you need an average where the denominator is the number of rolling periods at the query grain.
Note that the PERIODROLLING function includes a fourth optional hierarchy argument that lets you specify the name of a hierarchy in a time dimension, such as yr, mon, day, that you want to use to compute the time window. This option is useful when there are multiple hierarchies in a time dimension, or when you want to distinguish between multiple time dimensions. See "PERIODROLLING" for more information about the hierarchy argument and for details on the function syntax.
Compared to modeling an ordinary dimension, the time dimension requires just two additional steps: selecting the Time option in the Logical Dimension dialog, and designating a chronological key for every level of every dimension hierarchy.
Follow these additional guidelines when modeling time series data:
It only makes sense to use a time series function when the data source contains history. Usually, a relational database that contains history uses a star or snowflake schema with an explicit time dimension table. A normalized, historical database is much rarer, but would still include a time hierarchy with levels in a schema similar to a snowflake. A simple date field is not adequate.
Oracle Business Intelligence requires the time dimension physical table (or set of normalized tables) to be separate from the physical fact table to which it is related.
However, a somewhat common source schema pattern is a fully denormalized relational table or flat file, where the time dimension columns are in the same table as the facts and other dimensions. This cannot qualify as a time dimension, because the time dimension table is combined with the fact table.In this case, if you cannot change the model in the source, the best practice is to create an Opaque View of the physical table containing the time columns, which acts as the distinct physical time dimension table. This Opaque View time dimension must then be joined to the physical table that contains the facts.
In the Physical layer, the time dimension table (or lowest-level table in the normalized/snowflake case) must join directly to the fact table without intervening tables. This join must be a foreign key join.
The tables in the physical model containing the time dimension cannot join to other data sources, except at the most detailed level.
A member value (for example, a row in relational sources) must be physically present for every period at every hierarchy level. There cannot be any skips in the sequence. Note that it does not matter whether there is fact data for every period; only the dimension data must be complete.
Each unit of distance between members, such as "month," "half," or "year," must be modeled in a separate hierarchy level.
Select the Time option in the General tab of the Logical Dimension dialog to enable time series functions on this dimension. Only logical dimensions with the Time option selected can be used as the time dimension for the time series functions AGO, TODATE, and PERIODROLLING.
Figure 9-11 shows the Time option in the Logical Dimension dialog.
Figure 9-11 Time Option in Logical Dimension Dialog
Designate a chronological key for every level of each dimension hierarchy. This key must meet the requirements of being sequential (the members have a natural order), cardinal (all members are spaced the same distance apart at a given level, such as day or month), and complete (no members missing).
The Oracle BI Server uses the chronological key to create mathematically correct time series predictions, such as Jan + 2 months = Mar. You should set a chronological key for every level (except for the Grand Total level) so that you can perform time series operations on all levels with good performance. This enables you to use an AGO, TODATE, or PERIODROLLING function for any level of any time dimension hierarchy, such as fiscal month ago, calendar year ago, and day ago.
Theoretically, time series functions operate correctly if only the bottom level key in the Logical Dimension is chronological. In practice, however, this causes performance problems because it forces the physical query to use the lowest grain, causing joins of orders of magnitude more rows (for example, 365 times more rows for a "year ago" joining at the "day" grain). It also means higher-level aggregate tables are never selected by the query planner when using the time series functions, which again significantly slows the query.
As with any level key, be sure the key is unique at its level. For example, a column containing simple month names such as "January" is not unique unless it is concatenated to a column containing year names.
Figure 9-12 shows how to designate a chronological key in the Logical Level dialog.
Figure 9-12 Designating a Chronological Key in the Logical Level Dialog
You can build time series measures by creating derived expressions from base measures. To do this, create a new logical column and select Derived from existing columns using an expression, then open Expression Builder to build the appropriate time series function.
Follow these guidelines when modeling time series functions:
Time series functions cannot be derived from measures that use the fragmentation form of federation. This rule prevents some complex boundary conditions and cross-source assumptions in the query generation and result merging, such as the need to join some time dimension rows from one source to some of the fact rows in a different source.To reduce maintenance and increase accuracy, it is best to create a single base measure, and then derive a family of time series measures from it. For example, start with a base measure, then define variations for month-ago, year-ago, month-to-date, and so on. To do this, select Derived from existing columns using an expression and refer to the base measure in the expression.
Example 9-3 shows how to build the AGO measure. See Appendix C, "Logical SQL Reference" for detailed syntax for the other time series functions, TODATE and PERIODROLLING.
Example 9-3 Creating the AGO Measure
This example explains how to create one of the derived AGO measures in the Sampleapp demonstration repository.
In the Business Model and Mapping layer, create a new logical column. Name the column 2-04 Billed Qty (Mago).
In the Column Source tab, select Derived from existing columns using an expression and click the Expression Builder button.
In Expression Builder, select the Ago function to create a template for the arguments. To do this, select Functions in the Category pane, Time Series Functions in the Functions pane, and Ago in the Time Series Functions pane.
Figure 9-13 shows the AGO function in Expression Builder.
Figure 9-13 AGO Function in Expression Builder
Select the first argument, Measure, then use the selection panes to select the base measure from which to derive this column. In this example, select "Sample Sales"."F0 Rev Base Measures"."2-01 Billed Qty (Sum All)."
Select the second argument, Level, then use the selection panes to select the unit of the ago offset. It must be defined as a level of the time dimension, so that it can take advantage of the chronological keys built in the time dimension. In this example, select Time Dimensions in the Category pane, HO Time in the Time Dimensions pane, and Month in the HO Time pane.
Figure 9-14 shows the Month level in Expression Builder.
Figure 9-14 Selecting the Level Argument in Expression Builder
Select the third argument, Number of Periods, and enter the size of the offset you want to use for this measure. In this example, type 1.
Click OK in the Expression Builder dialog, then click OK in the Logical Column dialog.
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|