OBIEE - How to define OBIEE to leverage SQL OLAP Query ?
What’s Different about Configuring OBI EE to Query Cube Views ? The Aggregation Measures
While not really different from any other implementation, it is useful to point out that it is very likely that cube views will contain measures such as rankings and percentages that should generally not be aggregated in SQL. The cube will calculate these measures at summary levels and aggregation should be turned off in OBI EE.
To OBI EE, the selection a column from a dimension table or view is an indication of what level of summarization is required. For example, if a user selects the DEPARTMENT column from a product dimension table OBI EE understands this as being a request for data summarized to the DEPARTMENT level. When querying a table, OBI will select as
SUM(SALES) … GROUP BY DEPARTMENT
Because the cube view already includes rows for summaries, OBI EE needs to use a different method to indicate that it needs data at a particular level of summarization. Instead of using GROUP BY, queries need to filter on the LEVEL_NAME columns in the dimension views. For example, if department level data is needed the query should include a filter such as :
WHERE PRODUCT_VIEW.LEVEL_NAME = ‘DEPARTMENT’
as shown in the following query.
Articles Related
OLAP SQL query
Queries that are tuned to leverage OLAP aggregations in this way will optimize performance. In addition, all OLAP calculations occur in the Oracle OLAP calculation engine.
In the OLAP SQL query, note the following:
- There are no aggregation functions on the measure columns in the select list. The cube aggregates data, so OBI EE does not need to. Calculated measures are simply selected as columns. The data is computed in the OLAP calculation engine and passed through the cube view.
- There is no GROUP BY clause because there are no aggregation functions.
- There are filters on LEVEL_NAME columns of the dimension views. This filter effectively replaces the group by clause by indicating the level of summarization for data required from the cube.
- Level conditions are applied to all four dimensions, even though only three dimensions are in the SELECT statement (Geography, Product, and Time).
- Level conditions are applied at the lowest selected level for each dimension in the query: REGION, DEPARTMENT, and QUARTER.
- Since the channel dimension is omitted from the SELECT statement, the 'ALL_CHANNELS' level condition is automatically applied to the query for that dimension. This feature ensures that OLAP cube aggregations are leveraged.
The method to generate a Sql Olap Query
To allow OBI EE to generate SQL that queries relational views of the OLAP cube, OBI EE should be configured:
- Using multiple Logical Table Source objects, each representing a specific level of summarization, we specify the Where Clause.
- With a security filter to force the joins between dimension views and the cube view in the case where a dimension is not selected in a query.
By defining the additional metadata, you leverage the aggregations in the OLAP cube views by making queries “Level-Aware”. As a consequence, the following ideal OLAP query characteristics will apply to the generated SQL:
- Level conditions are applied to every dimension in the WHERE clause.
- Level conditions are applied to lowest selected level for a dimension. For example, if “Year” and “Quarter” are both in a query, then the condition time_level=‘QUARTER’ is applied.
- An “All/Total Level” condition is applied to dimensions that are omitted from the SELECT statement. This is required in order to leverage cube aggregation.
Define a logical table source for each dimension level
For all dimension levels, you use the Column Mapping tab to specify mappings for the current level, and all levels above it in the hierarchy. Therefore, mappings for all levels below it must be deleted.
However, the DIM_KEY column mapping must be left as is.( Why ??? )
For instance, with the Dimension Channel, we have three levels, we moet create therefore three logical tables source.
because we have three levels :
For the instance for the level “All Level”, you delete the mappings for the Channel and Class logical columns, since they are at a lower level than All Channels.
In the Content tab, you:
- Select the Logical Level for the Dimension object that is associated with the Logical Source.
- Create a 'WHERE clause' that selects the correct level from the OLAP Dimension or Hierarchy View.
In the WHERE clause (shown below), the cube and dimension hierarchy views are joined. Then, conditions are applied to each dimension using the LEVEL_NAME column from the hierarchy views.
These “level” conditions help ensure that the appropriate OLAP Cube aggregations are leveraged in the SQL query.
Automatically Add Level Conditions Using Security Filters
Recall that a condition must be applied to all dimensions in the WHERE clause of an OLAP cube query. To accomplish this, you add a Security Filter to the fact table that forces a join between the fact table and the dimension tables.
The BI EE Security Filters feature is used to automatically generate the required level conditions.
- BI EE security filters are applied automatically to queries. These security filters are similar to Oracle Database VPD; they are filters that are automatically applied to any query against the “secured” table.
- A filter condition on each logical fact table ensures that the logical dimension tables are joined to the facts based on the dimension keys. As a result, a star join is forced, causing the level filters to be applied to every query.
A security filter can be applied to a user or a group. A new group is created, and users of the repository must be assigned to this group.
Reference
- How to manually create SQL queries against OLAP 11g data, see Querying OLAP 11g Cubes.
- Using Oracle Business Intelligence Enterprise Edition with the OLAP Option to Oracle Database 11g. This contains a guide on how to configure the OBIEE metadata layer to leverage the 11g Oracle OLAP option, both indirectly via OLAP cube based materialized views, and directly via OLAP cube views.