10 Working with Logical Dimensions
This chapter contains the following topics:
About Working with Logical Dimensions
In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes).
You can associate one or more logical dimension tables with one dimension object.
Common dimensions include time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. 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. The structure represents the organization rules and reporting needs required by your business and provides the metadata the Oracle BI Server uses to drill into and across dimensions to get detailed views of the data.
-
Dimensions with level-based hierarchies (structure hierarchies)
In level-based hierarchies, members are of several types, and members of the same type occur only at a single level.
-
Dimensions with parent-child hierarchies (value hierarchies)
In parent-child hierarchies, members all have the same type.
Because dimensions for multidimensional data sources are defined in the source, you do not create dimension level keys. A dimension is specific to a particular multidimensional data source. You cannot create and manipulate a dimension individually. 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.
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.
Creating and Managing Dimensions with Level-Based 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:
About Level-Based Hierarchies
A dimension contains two or more logical levels. When creating logical levels, you should 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
The Grand Total level represents the sum of all totals for a dimension. Each dimension can have just one Grand Total level. The Grand Total level does not contain dimensional attributes and does not have a level key. You can associate measures with a Grand Total level. The aggregation level for those measures is the grand total for the dimension. The Grand Total level can exists without any columns.
-
Level
Levels must have at least one column. You do not need 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. You must associate all logical columns in the same dimension table with the same dimension.
You can have an unlimited number of levels in a dimension. When using extremely complex SQL queries, a few levels in a dimension can impact query performance.
-
Hierarchy
Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level. For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. In this example, Day has two named parent levels, Fiscal Year and Calendar Year that 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. Logical hierarchies exist implicitly through the relationships between levels.
You can define intermediate levels in your hierarchies to avoid having very large numbers of members at one level. For example, if you are creating a Product dimension for an automotive company that tracks data on 500 different car models, you might want to create some finer-grained hierarchical levels such as Minivans, Subcompacts, and Midsize Sedans. You could improve query performance and make reports and diagrams easier to read and navigate.
-
Level keys
Each logical level, except the 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. You must associate the dimension table logical key with the lowest level of a dimension.
A logical level can have multiple level keys. When a logical level has multiple level keys, specify a key as the primary key for the level. All dimension sources that have 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 Server user selects the object to drill down. You can use any level key to provide user access to the level.
You must create an unique level key. Month is not an unique level key. To create an unique level key with month include the year attribute as part of the key.
If you do not ensure that your level key is unique by including higher-level attributes, then queries might return unexpected results. For example, when the Oracle BI Server needs to combine result sets from multiple physical queries, some expected rows might be dropped because they are not considered unique according to the level key definition.
Create meaningful level keys using common business keys such as Month_name='2010 July', rather than generated surrogate keys such as time_key='1023793'. The generated surrogate keys are physical artifacts that only apply to a single instance of a source table. A business key 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, or a column in a federated spreadsheet. The Physical layer uses the surrogate keys in the joins. Using a business key does not impose a performance or flexibility penalty 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. Use the following guidelines 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.
-
All time series measures using the
AGO,TODATE, andPERIODROLLINGfunctions must be on time levels.AGO,TODATE, andPERIODROLLINGaggregates are created as derived logical columns. -
AGO,TODATE, andPERIODROLLINGfunctionality 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.
-
- Unbalanced or ragged hierarchy
An unbalanced or ragged hierarchy is a hierarchy where the leaves (members with no children) might not 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 moving down from any particular member.
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.
Unbalanced hierarchies are not necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature. Unbalanced level-based hierarchies are possible.
- 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.
The image 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.
About Using Dimension Hierarchy Levels in Level-Based Hierarchies
Learn how to use dimension hierarchical levels.
Dimension hierarchical levels can be used to perform the following actions:
-
Set up aggregate navigation.
-
Configure level‐based measure calculations, see Level-Based Measure Calculations.
-
Determine what attributes appear when Oracle BI Presentation Services users drill down in their data requests.
Manually Creating Dimensions, Levels, and Keys with Level-Based Hierarchies
Learn how to create and manage hierarchy level in level-based hierarchies.
Perform the tasks described in the following sections:
Creating Dimensions in Level-Based Hierarchies
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. See Working with Physical Hierarchy Objects.
Note:
It is a best practice to ensure that the physical hierarchy type set in the Physical layer matches the dimension properties you select in the Business Model and Mapping layer.
In addition, you must ensure that the Ragged and Skipped Levels dimension properties are set correctly for queries to work.
Creating Logical Levels in a Dimension
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.
If you are defining the level as a Grand Total level, leave this field blank. The default value is 1.
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.
- 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.
- 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
- Click Add to define child logical levels.
- In the Browse dialog, select the child logical levels and click OK.
- In the Child Level pane, remove a previously defined child level, select the level in the Child Levels pane and click Remove.
- (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, 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. The drag and drop action associates the logical column with that level of the dimension. To associate the logical level with that logical column, drag a column from one logical level to another.
You must associate the logical column or columns that comprise the logical key of a dimension table 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 examples, see:
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. The image shows how to associate logical columns with a logical level.
Level-Based Measure Calculations
A level-based measure is a column whose values are always calculated to a specific level of aggregation.
You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. For example, a company might want to measure its revenue based on the country, region, and city.
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. If the request only contains ordinary columns and no hierarchical columns, the level-based measure is not replaced with null.
The AllProductRevenue measure 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. Level-based measures are also useful in creating share measures, 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.
For example, to set up these calculations, you need to build a dimensional hierarchy in your repository that contains the Grandtotal, Country, Region, and City levels. 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.
After building a dimensional hierarchy, you need to create three logical columns one each for CountryRevenue, RegionRevenue, and CityRevenue. The columns use the Revenue logical column as its source. The Revenue column has a default aggregation rule of SUM and has sources in the underlying databases.
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.
The image shows the business model in the Business Model and Mapping layer for this example.
Grand Total Dimensional Hierarchy
Learn how to use a grand total dimensional hierarchy with revenue.
If you have a product dimensional hierarchy with TotalProducts (Grand Total level), Brands, and Products levels, and a Revenue column defined with a default aggregation rule of Sum, you can then create an AllProductRevenue logical column. The AllProductRevenue column uses Revenue as its source. Drag the AllProductRevenue column to the Grand Total level. Each query that includes the AllProductRevenue 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
The AllProductRevenue column always returns a value of 600, regardless of the products on which the query constrains.
Identifying the Primary Key for a Dimension Level
Use the Keys tab in the Logical Level dialog to identify the primary key for a 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 a
LOOKUPfunction as part of a primary logical level key. This limitation exists because theLOOKUPoperation 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
LOOKUPfunction as a secondary logical level key. -
-
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.
Selecting and Sorting Chronological Keys in a Time Dimension
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.
Pay attention when the column order in a chronological key has many columns. You set the column order using a SQL ORDER BY clause on the columns reflecting the real-world chronological order in the Chronological Key dialog of the Oracle BI Administration Tool. Since the range for quarters is 1 to 4 with 4 quarters in a year, using an ORDER BY clause with the Quarter before the Year (Quarter, Year) is incorrect. The incorrect order shows all first quarters across all years, before displaying any second quarters, and so on. To correct the results, use (Year, Quarter) in the ORDER BY clause.
To recognize a dimension as a time dimension, you must select Time on the General tab of the Dimension dialog.
- 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.
- 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.
- To sort chronological keys, in the Keys tab, select a chronological key and then click Edit.
- In the Chronological Key dialog, 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 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.
Adding Sequence Numbers to a Time Dimension's Logical Level
Adding absolute or relative sequence numbers to time dimensions optimizes time series functions and in some cases improves query time.
By default Oracle BI Server uses a complex RANK Physical SQL expression to generate sequence numbers for time dimensions. Adding an absolute or relative sequence number to the time dimension's logical level provides direct column references in the Time dimension table that contain the precomputed results of the rank expressions. This mapping, while optional, generates a simpler query that is easier for Oracle BI Server to execute against the data source.
Sequence numbers are enumerations of time dimensional members at a certain level. Use an enumeration without gaps (dense). The enumeration must correspond to a real time order, for example, you can enumerate the months in a year from 1 to 12.
The sequence number type options are:
-
Absolute - Choose this option to configure an absolute sequence number when the column enumerates the members of the time dimension without any reference, for example, calendar year.
-
Relative - Chose this option to configure relative sequence numbers when you have a column that enumerates members of the time dimension relative to some parent level, for example, months in year from 1 to 12.
- In the Business Model and Mapping layer of the Administration Tool, locate a time dimension and then double click a corresponding logical level.
- In the Logical Level dialog, click the Sequence Numbers tab, specify the type of sequence numbers to add to the logical level.
- Click OK.
Rules for Automatically Created Dimensions with Level-Based Hierarchies
The Create Dimensions option is only available if the selected logical table is a dimension table as defined by 1:N logical joins, and a dimension has not been associated with the table.
The following rules are applied:
-
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, which has the greatest number of columns mapped and contains the column mapping for the logical table key, is the level key for the detail level.
-
The parent of the detail level is 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 than one child table exists for a dimension table, the hierarchy is a split hierarchy.
Automatically Creating Dimensions with Level-Based Hierarchies
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. As a best practice, create a dimension table after all the logical table sources have been defined for a dimension table.
Populating Logical Level Counts Automatically
You can use Estimate Levels to automatically populate level counts for one or more dimension hierarchies.
Level counts are used by the query engine to determine the optimal query plan and to improve overall system performance.
You must open the repository in online mode and ensure that the business model is available for queries. 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. If you select the business model object, the Oracle BI Administration Tool attempts to check out all objects in the business model.
-
Dimension. 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.logwhen using an 11g version on the Oracle BI Server. When using Oracle BI EE 12c, the queries and errors are logged in theobis1_query.loglocated in theDOMAIN_Home/servers/obis1/logs.Set the log level at 4 or higher to write this information to the log file. See Diagnosing and Resolving Issues in Oracle Business Intelligence in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
When you check in the objects, you can check the global consistency of your repository.
Creating and Managing Dimensions with Parent-Child Hierarchies
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:
About Parent-Child Hierarchies
A common real-life parent-child hierarchy occurrence is an organizational reporting hierarchy chart.
In an organizational reporting hierarchy chart, the following can 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.
The image shows an example of a 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.
This section contains the following topics:
About Levels and Distances in Parent-Child 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. In a level-based hierarchy, the parent of a member is in a different logical column in the same row. 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-world attribute or category useful for analysis. In level-based hierarchies 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 parent-child hierarchy has two system-generated entities, Total and Detail, that are automatically defined 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. The implicit levels are referred to as parent-child hierarchical levels.
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. See About Parent-Child Hierarchies for an example.
About Parent-Child Relationship Tables
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.
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
An ancestor is 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.
-
The distance and leaf columns are
INTEGERcolumns.
For 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.
The example shown in the table 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 table shows an example of a parent-child relationship table with rows that represent the inter-member relationships for the employees. See the figure in About Parent-Child Hierarchies.
| 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 |
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.
Creating Dimensions with Parent-Child Hierarchies
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 and Defining Parent-Child Relationship Tables.
The Dimension with Parent-Child Hierarchy option is only available if there is at least one logical dimension table in the business model that does not have an associated dimension. The Browse window shows the logical dimension tables in the business model, each with their primary keys and the other columns in the table.
- In the Business Model and Mapping layer of the Administration Tool, do one of the following:
- Right-click a business model, select New Object, select Logical Dimension, and then select Dimension with Parent-Child Hierarchy.
- 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 on the General tab, type a name for the dimension.
- Click Browse located next to the Member Key field.
- Select a Member Key for the parent-child hierarchy, and click OK.
- Click Browse beside the Parent Key field.
- Select a column for the Parent Key for the parent-child hierarchy and click OK.
- If the logical table is not from a relational table source, click OK to finish the process of creating the dimension.
If the logical table is from a relational table source, you must continue the dimension definition process by setting up the parent-child relationship table for the hierarchy.
Defining Parent-Child Relationship Tables
Use these steps to define a parent-child relationship table for parent-child hierarchies based on relational tables.
When you create the parent-child relationship table, you must choose one of the following options:
-
(Recommended method) Use a wizard that generates scripts to create and populate the parent-child relationship table.
When you select Create Parent-Child Relationship Table, 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, make the parent-child relationship table available to the metadata repository.
In the Generate Parent-Child Relationship Table wizard, you must provide a enter name for the DDL Script to Generate the Parent-Child Table, and select the location for storing the generate script. You must also supply a name for the parent-child relationship table and select the catalog or schema for the parent-child relationship table. You can preview the generated scripts.
-
Select a previously-created parent-child relationship table.
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.
Modeling Aggregates for Parent-Child Hierarchies
Fact tables in level-based hierarchies might only contain facts for a single level of the hierarchy.
Facts for higher-level dimension members can be calculated by aggregating the facts from the lower-level fact table or from a higher-level summary table.
In contrast, parent-child hierarchies require data modelers to make some additional decisions related to the following:
-
How to store the base facts in the fact table
-
How to aggregate the base facts to obtain the facts for higher-level members of the parent-child hierarchy
This section describes how to store and aggregate facts for parent-child hierarchies and contains the following topics:
Storing Facts for Parent-Child Hierarchies
There are two options for storing the base facts in the fact table for parent-child hierarchies.
You can use the following options:
-
Store facts for only the leaf members of the parent-child hierarchy.
-
Store facts for members at any level of the parent-child hierarchy, including non-leaf members.
The first option is more appropriate if the facts for the non-leaf members of the parent-child hierarchy can be derived entirely from the facts of the leaf members. For example, if you have a parent-child product hierarchy in which the actual product members appear only as leaf members of the hierarchy, then it makes sense for a revenue fact table to only record revenue facts for the leaf members of this product hierarchy. The revenue figures for the non-leaf members of the product hierarchy such as the product categories can be derived entirely by aggregating the facts for the leaf product members at the bottom of the hierarchy.
The image shows example data for a situation where facts are stored only for leaf members in a parent-child hierarchy.
The following table shows example data for the dimension table PRODUCT_DIM:
| MemberKey | Name | ParentKey |
|---|---|---|
|
P1 |
Product1 |
C1 |
|
P2 |
Product2 |
C1 |
|
C1 |
Category1 |
C2 |
|
C2 |
Category2 |
C3 |
|
C3 |
Category3 |
- |
The following table shows example data for the fact table REVENUE_FACTS:
| ProductKey | YearKey | Revenue |
|---|---|---|
|
P1 |
2011 |
100,000 |
|
P1 |
2012 |
105,000 |
|
P2 |
2011 |
75,000 |
|
P2 |
2012 |
80,000 |
The second option in which facts are stored for members at any level of the parent-child hierarchy is necessary when the facts for the non-leaf members are not completely derived from facts of the leaf members. A good example is a sales person hierarchy in which a sales person might report to a manager who is also a sales person. Each individual sales person, including the manager, could have a different revenue figure stored in the fact table.
The table shows example data for this situation.
Facts Stored for Both Leaf and Non-Leaf Members
The following table shows example data for the dimension table SALES_REP_DIM:
| MemberKey | Name | ParentKey |
|---|---|---|
|
101 |
Phillip |
201 |
|
102 |
Vivian |
201 |
|
201 |
Jacob |
301 |
|
202 |
Audrey |
301 |
|
301 |
Ryan |
- |
The following table shows example data for the fact table REVENUE_FACTS:
| SalesRepKey | YearKey | Revenue |
|---|---|---|
|
101 |
2012 |
1,200,000 |
|
102 |
2012 |
1,100,000 |
|
201 |
2012 |
250,000 |
|
202 |
2012 |
1,400,000 |
Another case in which storing facts for both leaf and non-leaf members is appropriate is when the rules for aggregating the parent-child hierarchy are complex, or when aggregating the hierarchy at query time is expensive and would lead to unacceptably long query response times. In this case, the fact table would store preaggregated facts for the non-leaf members in addition to the facts stored for the leaf members.
Aggregating Parent-Child Hierarchies
As a data modeler, you must determine how to aggregate the stored facts to calculate the aggregated facts for higher level members of the parent-child hierarchy.
In addition to choosing the correct aggregation function for the measure, you must decide if you need to roll up the fact values recorded for lower-level members to calculate the values for higher-level members. In some cases, rolling up the facts of lower-level members of the parent-child hierarchy makes sense. In other cases such as with a pre-aggregated fact table or a measure that is intended to show each member's individual contribution, rolling up the facts from lower-level members of the parent-child hierarchy is incorrect.
Rolling up Facts from Lower-Level Members of a Parent-Child Hierarchy
If a fact table only stores facts for the leaf members of a parent-child hierarchy or if the fact table only records each member's individual contribution, then most likely the values stored in the fact table must be rolled up to obtain the correct aggregated value for higher-level members of the parent-child hierarchy. Rolling up the facts along a parent-child hierarchy is achieved by joining the fact table to the dimension table through the parent-child relationship table, see Adding the Parent-Child Relationship Table to the Model.
For a fact table that stores facts only for the leaf members such as the product revenue fact table, this modeling technique calculates aggregate values that correctly summarize all the facts for the leaf-level members.
For a fact table that stores the individual contribution of both leaf members and non-leaf members, this technique computes a hierarchical aggregate that summarizes the individual contributions of the member and all its members.
Modeling Individual Contribution Measures
To report the individual contribution of each member, in addition, to reporting the summarized hierarchical aggregate that rolls up the individual contributions of multiple members, you must create two separate fact logical table sources. One fact logical table source maps the base fact table and the parent child relationship table. This is the logical table source for the hierarchical aggregate measure. The second fact logical table source maps only an alias of the fact table. This fact table alias should join directly with the dimension table rather that joining indirectly through the parent-child relationship table. This is the logical table source for the individual contribution measure.
Modeling Pre-aggregated Measures
Some fact tables contain pre-aggregated data that is populated for all members of the parent-child hierarchy. For example, the fact value for a root member might be populated with the aggregation of the data for all of its descendent members. It is important to ensure that queries do not aggregate the members from this dimension to avoid erroneous results.
To correctly model this type of parent-child hierarchy, you must create a parent-child relationship table to support hierarchical filter functions like IsAncestor and IsDescendant. You can join the parent-child dimension table directly with the fact table rather than joining through the parent-child relationship table to ensure that the pre-aggregated member value is returned, rather than rolling up all the descendants.
Note:
Do not modify the parent-child relationship table script to only include the self rows, because doing so would break the IsAncestor and IsDescendant functions.
To achieve the correct aggregation for dimensions of this type, you must determine what you want to see as a grand total when the parent-child hierarchy is aggregated. For example, assume that your hierarchy contains a single root member, and you want to display the pre-aggregated value for this root member. You must first create an additional fact logical table source mapped at the Total level of the parent-child hierarchy. Next, in the logical table source, create a WHERE clause filter that selects only the root member.
With this model in place, for queries that are at the Total level of the parent-child hierarchy, the Oracle BI Server selects the aggregate logical table source and applies the root member WHERE clause filter. For queries that are at the Detail level, the Oracle BI Server selects the detailed logical table source and returns the pre-aggregated member values. In either case, it does not matter how the aggregation rule is set, because there is a pre-aggregated source at each level.
Use this approach only if the queries are at the Total or Detail level of the parent-child dimension. For queries that group by some non-unique attribute of the parent-child dimension, the aggregation might not be correct. For example, if an Employee dimension has a Location attribute, and a query groups by Employee.Location, then double counting is likely because an employee often reports to other employees at the same location. Because of this, when fact tables contain pre-aggregated member values, you should avoid grouping by non-unique attributes of the parent-child dimension. If grouping by those attributes is unavoidable, then you should model them as separate dimensions.
Adding the Parent-Child Relationship Table to the Model
For measures in fact tables that are aggregated by rolling up the facts from lower-level members, you must edit Physical layer joins to include the parent-child relationship table.
You need to add the parent-child relationship table to the appropriate logical table source.
For fact tables containing pre-aggregated data for a parent-child hierarchy or for individual contribution measures, you should join the parent-child dimension table directly with the fact table rather than joining through the parent-child relationship table.
Joining the parent-child dimension table directly with the fact table ensures that the pre-aggregated value or individual contribution value is returned, rather than rolling up all the descendants. When pre-aggregated data is populated for all members, do not add the parent-child relationship table to the logical table source to avoid over counting.
- In the Administration Tool, right-click a physical table, select Physical Diagram , and then select Selected Object(s) Only.
- Delete the direct joins from the dimension table to each of the fact tables.
- Create a join from the parent-child relationship table to the dimension table using the ancestor key.
- Create joins from the fact tables to the parent-child relationship table using the member key.
- In the Business Model and Mapping layer, double-click the logical table source for the logical fact table that is used in your parent-child hierarchy.
- In the General tab on the Logical Table Source dialog, click the Add button.
- Click Browse to locate the parent-child relationship table in the Physical layer and click Select.
- Click OK in the Logical Table Source dialog.
Maintaining Parent-Child Hierarchies Based on Relational Tables
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.
If you wrote scripts to create and populate the parent-child relationship table or used the Generate Parent-Child Relationship Table Wizard to create the scripts, you must run these scripts, adapting them to guarantee the integrity of the parent-child relationships in the hierarchy. You should add the Populate script to your extract-transform-load (ETL) process so that the script runs after the dimension table is updated.
Modeling Time Series Data
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:
About Time Series Functions
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 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.
The image shows a sample report that includes several 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.
-
Time Series grain
The time series grain indicates the aggregation or offset is requested, for theAGOandTODATEfunctions. In the report example shown in the image, the time series grain is Quarter. Time series queries are valid only if the time series grain is at the query grain or higher. ThePERIODROLLINGfunction does not have a time series grain; instead, you specify a start and end period in the function. -
Storage grain
You can generate the report, shown in the example, 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.
Queries against time series data must exactly match to access the query cache.
The following sections describe the time series conversion functions:
About the AGO Function
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:
The value of Dollars Qago for month 2008/08 equals the value of Dollars for month 2008/05.
The image shows example values for the Dollars and Dollars Qago measures.
In the example shown above, 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), or 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 in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.
About the TODATE Function
The TODATE function accumulates the measure from the beginning of the time series grain period to the current displayed query grain period.
For example, the image shows a report with the measure Dollars QTD, the Quarter To Date version of the Dollars measure.
In the example, Dollars QTD for Month 2008/05 is the sum of Dollars for 2008/04 and 2008/05. 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, the Dollars QTD measure is derived from the Dollars measure.
In Expression Builder, the TODATE function uses the following format:
ToDate(<<Measure>>, <<Level>>)
<<Measure>> represents the logical measure column from which you want to derive. In this example, you select the measure Dollars from your existing logical fact tables.
<<Level>> is the time series grain you want to use. In this example, you select Quarter from your time dimension.
Using this function format, you can create the following expression for the measure:
ToDate("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" )
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, and accumulates up to the end of the Quarter.
See TODATE in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.
About the PERIODROLLING Function
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 a 13-week Rolling Average.
The PERIODROLLING function does not have a time series grain, the length of the rolling sequence is determined by the query grain. For example, the Dollars 3-Period Rolling Average calculates the mean of values from the last 3 months if the query grain is Month, but calculates the mean of the last 3 years if the query grain is Year.
The image shows a report with these two measures.
In the example above , 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 format:
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 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. The integer is the relative number of periods from the displayed period. In this example, 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. To create the measure, Dollars 3-Period Rolling Sum, the integers to indicate these offsets are -2 and 0.
Using this function format, you can create the following expression for the measure:
PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0)
The example 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. The assumption to divide the rolling sum by 3 results from the <<Starting Period Offset>> and <<Ending Period Offset>> fields for the rolling sum that are -2 and 0.
The expression for the 3-month rolling average is:
PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0) /3
Do not use the AVG function to create a rolling average. The AVG function computes the average of the database rows accessed at the storage grain. To perform the rolling average, you need an average where the denominator is the number of rolling periods at the query grain.
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 in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.
Creating Logical Time Dimensions
Creating time dimensions requires selecting a Time option and designating a chronological key for every level of every dimension hierarchy.
Use these guidelines when modeling time series data:
-
Use a time series function when the data source contains history. A relational database that contains history might use a star or snowflake schema with an explicit time dimension table. A normalized, historical database might include a time hierarchy with levels in a schema similar to a snowflake. A simple date field is not adequate for use with a time series function.
-
Oracle Business Intelligence requires the time dimension physical table or set of normalized tables that are separate from its related physical fact table.
A common source schema pattern is a fully denormalized relational table or flat file that has time dimension columns are in the same table as facts and other dimensions. This common source schema pattern cannot qualify as a time dimension, because the time dimension table is combined with the fact table. Because you cannot change the source model, you can create an Opaque View of the physical table containing the time columns to act as the distinct physical time dimension table. You must join the Opaque View time dimension to the physical table that contains the facts.
-
In the Physical layer, the time dimension table or lowest-level table in the normalized/snowflake must join directly to the fact table without an intervening tables. Create the join as 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, a row in relational sources, must be physically present for every period at every hierarchy level. The must not contain rows that are skipped in the sequence. You do not need a fact data for every period. Only the dimension data must be complete.
-
You must model each unit of distance between members such as month, half, or year, in a separate hierarchy level.
Selecting the Time Option in the Logical Dimension Dialog
Select the Time option in the General tab of the Logical Dimension dialog to enable time series functions on this dimension.
You can only use logical dimensions with the Time option selected as the time dimension for the time series functions AGO, TODATE, and PERIODROLLING.
The image shows the Time option in the Logical Dimension dialog.
Setting Chronological Keys for Each Level
Designate a chronological key for every level of each dimension hierarchy.
The chronological keys must be comparable with the standard SQL ORDER BY clause. The ORDER BY clause on the chronological key must reflect the real world chronological order of the time dimension members represented by the key. For example, if the time dimension members are: Jan-3-2013, Jan-4-2013, Jan-5-2013 then the following chronological keys can be assigned to them in the same order: 1, 5, 9. However, assigning chronological keys such as 2,1,3 would result in Jan-4-2013, Jan-3-2013, Jan-5-2013, which is an incorrect chronological order.
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.
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.
The image shows how to designate a chronological key in the Logical Level dialog.
Creating AGO, TODATE, and PERIODROLLING Measures
You can build time series measures by creating derived expressions from base measures.
To create a derived expression, you must 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:
-
You cannot derived time series functions 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, and month-to-date.
-
You must define the unit as a level of the time dimension, so that it can take advantage of the chronological keys built in the time dimension.
The following example shows how to build the AGO measure. See the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition for detailed syntax for the other time series functions, TODATE and PERIODROLLING.
Creating the AGO Measure
This example shows how to create 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.
- In Expression Builder, from Category, select Functions.
- From Functions, select Time Series Functions, and then from Times Series Functions, select Ago.
- Select Measure, then use the selection panes to select the base measure from which to derive this column.
- Select Level, then use the selection panes to select the unit of the ago offset.
- Select Number of Periods, and enter the size of the offset for this measure. In this example, type 1.
- In the Expression Builder, click OK .
- In the Logical Column dialog, click OK.