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.
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:
To allow OBI EE to generate SQL that queries relational views of the OLAP cube, OBI EE should be configured:
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:
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:
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.
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.
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.