Home About Clients Consulting Training Support Articles Events Blog Rittman Mead - Delivered Intelligence Aggregate Navigation using Oracle BI Server

In this example I’m going to show you how to create an aggregate table, register it with the Oracle BI Server, and have Oracle Answers and Dashboard use it to speed up queries. Oracle BI server lets you register aggregate (summary) tables that contain the precomputed sums, averages and so on for a fact table, which it then uses in preference to rolling up the detail-level fact table if this would speed up a query. I was shown how to do this by Kurt Wolff so all credit to him, and in this instance I’ll be working with the source tables used in the Global Sample Schema.

The UNITS_FACT table in the Global Sample schema has around 220k rows in it, holding sales data at the month, item, ship to and channel level.

SQL> select count(*) from sales_fact;

COUNT(*)
———-
222589

What I’d like to do is create an aggregate table, where I roll the data up to the product family, and customer region, level. I’ll leave channel as the detail level, and do the same for month, as the data is sparse by the time dimension – customers don’t generally order every day each month, which means aggregating by this level won’t really compress the aggregate any further.

I create the aggregate table using a CREATE TABLE … AS SELECT statement.

SQL> create table agg_sales_fact
2 as
3 select p.family_id
4 , cu.region_id
5 , ch.channel_id
6 , t.month_id
7 , sum(s.units) as units
8 , sum(s.sales) as sales
9 , sum(s.cost) as cost
10 from product_dim p
11 , customer_dim cu
12 , channel_dim ch
13 , time_dim t
14 , sales_fact s
15 where s.ship_to_id = cu.ship_to_id
16 and s.item_id = p.item_id
17 and s.channel_id = ch.channel_id
18 and s.month_id = t.month_id
19 group by p.family_id
20 , cu.region_id
21 , ch.channel_id
22 , t.month_id
23 /
Table created.SQL> select count(*) from agg_sales_fact;

COUNT(*)
———-
5291

That’s better; the summary table is just 2% of the size of the detail-level table.To go with the aggregate table, I create cut-down versions of the PRODUCT_DIM and CUSTOMER_DIM dimension tables, using just the levels and above referenced by the aggregate table.

SQL> create table agg_customer_dim
2 as
3 select distinct region_id
4 , region_dsc
5 , total_customer_id
6 , total_customer_dsc
7 from customer_dim
8 /

Table created.

Now the aggregate fact table and dimensions have been created, I then import them into the physical layer of the semantic model…

… create keys on the relevant columns …

… use the physical diagrammer to create foreign key relationships between the aggregate fact table, the aggregate dimension tables and the existing detail-level TIME_DIM and CHANNEL_DIM dimension tables …

… and then finally, update the row counts on the new tables.

We’re now at the point where we can map these aggregate tables to the existing logical tables in the business model layer.

The way we do this is similar to the way I mapped an Excel spreadsheet in a few weeks ago; you identify the existing logical tables in the business model that have columns that correspond to the incoming data, and then drag the columns you want to match on over from the new, physical table on top of the existing logical columns you want to “join” on. This creates a new logical table source for the logical table, and tells the BI Server that in our instance, the region description, and the family and class descriptions, can now also be found in our aggregate dimension tables.

We then do the same for the unit column, but instead of – as we did with the Excel source – adding it as a new column the fact table, we just drop it on top of the existing units measure.

The final step is to tell the BI Server that this new data source for the units measure, is only valid at the month, channel, product family and customer region level.

Now, when a query comes in against the units measure at the product class and customer region level, the BI Server will use the AGG_SALES_FACT aggregate table instead of the UNITS_FACT table, as it’s row count is so much less than the detail-level table (5k rows rather than 200k).

To test this out, I run a query to return a crosstab at the detail level, like this:

Checking the query log, I see that the detail-level tables are being used in the physical SQL.

select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select sum(T682.UNITS) as c1,
T625.ITEM_DSC as c2,
T645.QUARTER_DSC as c3,
T591.WAREHOUSE_DSC as c4
from
GLOBAL.TIME_DIM T645,
GLOBAL.PRODUCT_DIM T625
GLOBAL.CUSTOMER_DIM T591,
GLOBAL.UNITS_FACT T682
where ( T645.MONTH_ID = T682.MONTH_ID and
T591.SHIP_TO_ID = T682.SHIP_TO_ID and
T625.ITEM_ID = T682.ITEM_ID )
group by T591.WAREHOUSE_DSC,
T625.ITEM_DSC, T645.QUARTER_DSC
) D1
order by c2, c3, c4

Then running a query at the higher level, which corresponds to the aggregate table, I get the following results (rather quickly, I note)…

… and, checking the physical SQL issued, I see the aggregate tables being used instead.

select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select sum(T2386.UNITS) as c1,
T645.QUARTER_DSC as c2,
T2374.REGION_DSC as c3,
T2379.FAMILY_DSC as c4
from
GLOBAL.TIME_DIM T645,
GLOBAL.AGG_PRODUCT_DIM T2379,
GLOBAL.AGG_CUSTOMER_DIM T2374,
GLOBAL.AGG_SALES_FACT T2386
where ( T2379.FAMILY_ID = T2386.FAMILY_ID
and T645.MONTH_ID = T2386.MONTH_ID
and T2374.REGION_ID = T2386.REGION_ID )
group by T645.QUARTER_DSC, T2374.REGION_DSC, T2379.FAMILY_DSC
) D1
order by c2, c3, c4

Not bad. Later on, I run other queries at higher levels of aggregation, and see that the aggregates are still being used.

So, I guess the obvious question here is whether you’d use this, when you have materialized views and query rewrite already in place? Well, I guess this was a feature Siebel put into the BI Server for databases that didn’t have query rewrite, and therefore Siebel Analytics needed to handle aggregate navigation for them. Even if you’ve got an Oracle Enterprise Edition database, you might still want to use this if the intricacies of Oracle summary management are a bit too much for you.

One thing that this would be useful for though, is where an aggregate is stored in one database (possibly an OLAP server?), and the detail in another – using this feature the measure seems seamless to the user, but under the covers two separate databases are being used to return the data. Looking to the future, this ability to store aggregates remotely is being used by a future version of the BI Server, which will come with a utility that automatically creates aggregates for you based on previous query response times, and stores these aggregates on whatever database server is handy, mapping the aggregates back to the existing measures and subsequently speeding up queries against them that require summarized data. Quite a useful feature.

in

Data Warehouse Aggregates: Using Oracle Materialized Views and Query Rewrite NOVEMBER 24TH, 2009. Sabrina

If you want better performance from your data warehouse, one of the most efficient solutions is to create aggregate tables. In this post we will have a look at how Oracle Materialized Views and query rewrite option can help you do it. You can quickly implement aggregates in your data warehouse and you don’t need to make any changes in your existing reporting universe. Aggregates become available automatically when they are ready for use. And using Materialized Views makes your aggregate solution completely independent from your reporting tool.

Aggregate navigation is the process of determining the most efficient source for a user’s query. Oracle Materialized Views are database views whose results are cached in a table and can be returned from the cache instead of creating a new database query. In short, Materialized Views permit aggregate navigation in the Oracle Database instead of your reporting tool. Let’s have a look at how aggregate navigation is normally defined in Oracle Business Intelligence and Business Objects. Then let’s see how we can benefit from using Materialized Views.

Aggregate navigation in Oracle Business Intelligence

In Oracle BI, aggregate navigation is done by defining several logical table sources and the grain of data they contain. OBI then determines the best source for the user’s query. In the image below you will see two logical table sources for the Sales Fact table. In this example, W_SALES_F runs on daily level, and Aggregated_Sales runs on monthly level. When a user queries sales on a monthly or yearly level, the aggregate table is hit. The decision to use the aggregate table is done by OBI, based on our definition of the data sources.

Aggregated source defined in OBI

Aggregate navigation in SAP BusinessObjects

In BusinessObjects, aggregate navigation can be done with the @aggregate_aware function as shown in the image below. With this function, the incompatibilities of objects must be defined. For example, you could define “day” and “week” queries as incompatible with the aggregated source. By default, BusinessObjects will use the Aggregated_Sales source first. But if the query contains an incompatible object, BusinessObjects will revert to the second source. So if either day or week is included in the user query, W_SALES_F will be used.

Aggregated source defined in BusinessObjects with @aggregate_aware function

Aggregate navigation with Materialized Views

When Oracle database detects that user’s query would benefit from using aggregates it automatically rewrites the query and hits the aggregates. The reporting tool isn’t even aware of the rewrite, and thinks that the detailed table has been hit.

Typically, aggregate tables are created at the end of the ETL process and may not be ready when users start working with the system. You would need to create a logic in order to ignore the aggregates if they have not been refreshed. This logic can be complicated to create for aggregate tables. However, it is easy to set Materialized Views online or offline by enabling or disabling them for query rewrite.

With Materialized Views, all aggregate navigation logic resides in an Oracle database. The database will automatically rewrite the query if Materialized Views exist. If there are no Materialized Views, the detail tables are used. When the Oracle database detects that a user query would benefit from using aggregates, it automatically rewrites the query and targets the aggregates. The reporting tool doesn’t register the rewrite and continues to behave as if the detail table was targeted instead.

Materialized Views also simplify the Oracle BI repository or BusinessObjects universe. You only need to define one source, the detail table. A single logical table source pointing to the detail table is now sufficient for the Oracle BI repository. The BusinessObjects universe is also simplified because the @aggregate_aware function is no longer needed and incompatibilities no longer need to be defined.

Simplified OBI repository when using Oracle materialized views

Simplified BusinessObjects universe when using Oracle materialized views.

Main benefits of using Materialized Views

There are several good reasons to use Materialized Views as aggregates:

1. Quick and flexible development of aggregates
2. No changes are required in the reporting universe or repository; all development is done in Oracle database (in the case of new aggregates)
3. Simplified reporting universe or repository (in the case of changing existing aggregate tables to Materialized Views)
4. Aggregates come online automatically when they have been updated; no complex logic is required
5. Aggregate development is independent of our choice of reporting tool

There is much more to say on the topic, but hopefully this explains the basics of Materialized Views and the ways they can save you time and improve the performance of your data warehouse.

Hierarchy Aggregation – A Best Practice

Another topic could be added in the goodbook of Best Practice .In the OBIEE hierarchy, starting from the top and moving down. At each level below the Grand Total level, double-click the level. In the field Number of elements at this level: enter 10 and increase in increments of 10 through all levels in the hierarchy. This is necessary to avoid the below Hierarchy errors and let BI server optimizes the aggregated query :

[nQSError: 15001] Could not load navigation space for subject area Student Enrollment.

[nQSError: 15019] Table <Logical table name> is functionally dependent upon level <Logical level>, but a more detailed child level has associated columns from that same table or a more detailed table.

This message occurs when either a key is defined under the Total level or when the first child level below the Total level contains two keys. Right-click on the Total level and select Properties. The Keys tab should be grayed out. If it is selectable, check to see what is there and delete it. Go to the next level down, right-click, select the Keys tab. Only one Key should appear. Delete the key that does not belong.

Aggregates outside OBIEE - materialized views and query rewrite

In this post I explained how to use aggregates in OBIEE. Then we did manually create aggregate tables on the database and set each logical table source to triggering only on the certain level of dimension.

We then used aggregate tables SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH and dimension tables CATEGORIES, MONTHS i YEARS. Here, we don't need that.

In this post we'll try to explain and set up materialized views and the query rewrite to get the same queries as in the post above, but without setting anything in the BMM in the logical table sources.

I'll use oracle 10g database, oracle SH schema and the measure from the SALES fact table.

For using dbms_mview.explain_rewrite we need to have rewrite_table table (file utlxplan.sql) and for dbms_mview.explain_mview table mv_capabilities_table (file utlxmv.sql).

First set:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE
ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY='TRUSTED'

This is the level of the query rewrite. I set TRUSTED only to be able to test this. You should see other options as well. In TRUSTED mode, the optimizer trusts that the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions. This mode offers greater query rewrite capabilities but also creates the risk of incorrect results if any of the trusted relationships you have declared are incorrect (Text reference:Oracle Database Data Warehousing Guide 11g Release 1 (11.1)).

RELY constraints:

We use SALES table as the reference. Existing constraints we need to modify to RELY. RELY only affects those constraints that are ENABLE NOVALIDATE. Parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED (TRUSTED informations are constraints (NOVALIDATE RELY) and dimensions). Oracle will not do the check whether relationships defined with RELY constraints are TRUE. That refers to primary key and unique key constraints (RELY ENABLE NOVALIDATE). Query rewrite also use joinback method for recognition attribute that is not in the materialized view query but can be retrieved with joinback. For example, the query rewrite materialized view has CALENDAR_MONTH_ID and we want to group by CALENDAR_MONTH_DESC and then the query optimizer make the join between materialized view and the TIMES table one more to get CALENAR_MONTH_DESC. TIMES table is joinback table.

Because of the connection with the higher levels we need to have dimensions:


I didnt create them, they are already on the oracle SH schema.

Modify all SALES table constraints to RELY ENABLE NOVALIDATE:

alter table sales modify constraint sales_product_fk RELY ENABLE NOVALIDATE
alter table sales modify constraint sales_channel_fk RELY ENABLE NOVALIDATE
alter table sales modify constraint sales_time_fk RELY ENABLE NOVALIDATE
alter table products modify constraint products_pk RELY ENABLE NOVALIDATE
alter table times modify constraint times_pk RELY ENABLE NOVALIDATE
alter table channels modify constraint channels_pk RELY ENABLE NOVALIDATE

We create materialized view to support all queries like in the post:

create materialized view mv_sales_all
build immediate
refresh force on demand
with primary key
enable query rewrite
as
select t.calendar_month_id,
s.prod_id,
s.channel_id,
grouping_id(t.calendar_month_id, s.prod_id, s.channel_id) as gr_id,
sum(s.amount_sold) as amount_sold,
sum(s.quantity_sold) as quantity_sold
from sales s, times t
where s.time_id=t.time_id
group by
grouping sets
(
(t.calendar_month_id),--gr_id 3
(t.calendar_month_id,s.prod_id), --gr_id 1
(t.calendar_month_id,s.prod_id,s.channel_id)--gr_id 0
)

In the grouping sets we support all three combinations like in the post.

Grouping_id function will get the decimal interpretation of the binary. If the attribute gives the contribution to aggregation then the value is 0, otherwise it is 1.

For example, calendar_month_id has value 3 because it's in the combination:

(0, 1, 1) = (calendar_month_id, prod_id, channel_id)

Check:

select bin_to_num(0, 1, 1) from dual--3 decimal
select bin_to_num(0, 0, 0) from dual--0 decimal
select bin_to_num(0, 0, 1) from dual--1 decimal

Example of combinations:


To explain materialized view query we use the table mv_capabilities_table and the procedure dbms_mview.explain_mview.

BMM (clean model):


The focus is on how this works with queries that OBIEE generates, not how to refresh materialized views during the part of the job of the ETL process.

To test this we need to refresh materialized view:

begin
dbms_snapshot.refresh('MV_SALES_ALL','C');
end;

Get schema statistics:

begin
dbms_stats.gather_schema_stats('SH', CASCADE=>TRUE);
end;

Now, if we choose:


NQQuery.log:


Explain plan, table plan_table:


See the joinback to TIMES table to get the CALENDAR_MONTH_DESC.

If we instead of CALENDAR_MONTH_DESC put the CALENDAR_MONTH_ID there is no joiback to TIMES because we use CALENDAR_MONTH_ID which is already in the materialized view query.


To verify that the query did rewrite we can use dbms_mview.explain_rewrite, and the table rewrite_table:


If we choose:


NQQuery.log:


Explain plan, table plan_table:


If we choose:


NQQuery.log:


Explain plan, plan_table:


We see that in all three queries the query rewrite works correctly, query has been rewritten.

I really try to show how this works when you are using OBIEE queries. If you have any question or suggestion please post the comment. 

Aggregates in OBIEE

Aggregate fact tables contain same measure data like in the lowest granularity fact table but summarized on certain level. Aggregates in obiee can be created using aggregate persistence wizard or manually.

For the first option:

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/aggpersist/aggpersist.htm
http://www.rittmanmead.com/2007/10/26/using-the-obiee-aggregate-persistence-wizard
http://obiee101.blogspot.com/2008/11/obiee-aggregate-persistence-wizard.html

Advanced option is using materialized views, dimensions and query rewrite.

I'll show the second option (manually).

Creating database objects

For this example we'll create database objects, higher level dimension tables, aggregates, indexes, ect. Something about higher dimension tables, it depends how you understand normalized and denormalized structure in business intelligence term. Dimension tables are always denormalized, each level is placed inside it. If you for example query sh.products table you'll see that the lowest level has information about high levels. If you are using dimension operator in OWB to load data into, the result is dimension table with addition that all levels are separately loaded with each with its own ID, primary key. So other aggregation fact tables can reference high level dimension ID from the same dimension. The very similar way is how olap dimension works, see global.channel_dimview. Anyway, we'll create higher dimension level tables for this example purpose.

create table months as
select
distinct
calendar_month_id,
calendar_month_desc,
calendar_year_id,
calendar_year
from times

alter table months
add constraint
months_pk primary key (calendar_month_id);

create table categories as
select
distinct
prod_category_id,
prod_category
from products

alter table categories
add constraint
categories_pk primary key (prod_category_id)

create table years as
select
distinct
calendar_year_id,
calendar_year
from times

alter table years
add constraint
years_pk primary key (calendar_year_id)

create table sales_months as
select
t.calendar_month_id,
sum(s.amount_sold) as amount_sold,
sum(s.quantity_sold) as quantity_sold
from sales s, times t
where s.time_id=t.time_id
group by t.calendar_month_id;

alter table sales_months
add constraint sm_months_fk
foreign key (calendar_month_id)
references months (calendar_month_id)

create bitmap index sm_months_idx
on sales_months (calendar_month_id);

create table sales_year_cat as
select
t.calendar_year_id,
p.prod_category_id,
sum(s.quantity_sold) as quantity_sold,
sum(s.amount_sold) as amount_sold
from sales s, products p, times t
where s.prod_id=p.prod_id
and s.time_id=t.time_id
group by t.calendar_year_id, p.prod_category_id;

alter table sales_year_cat
add constraint syc_years_fk
foreign key (calendar_year_id)
references years (calendar_year_id)

create bitmap index syc_years_idx
on sales_year_cat (calendar_year_id);

alter table sales_year_cat
add constraint syc_categories_fk
foreign key (prod_category_id) references categories (prod_category_id)

create bitmap index syc_categories_idx
on sales_year_cat (prod_category_id);

create table sales_months_cat_ch as
select
t.calendar_month_id,
p.prod_category_id,
c.channel_id,
sum(s.quantity_sold) as quantity_sold,
sum(s.amount_sold) as amount_sold
from sales s, products p, times t, channels c
where s.prod_id=p.prod_id
and s.time_id=t.time_id
and s.channel_id=c.channel_id
group by t.calendar_month_id, p.prod_category_id, c.channel_id;

alter table sales_months_cat_ch
add constraint smcc_months_fk
foreign key (calendar_month_id)
references months (calendar_month_id)

create bitmap index smcc_months_idx
on sales_months_cat_ch (calendar_month_id);

alter table sales_months_cat_ch
add constraint smcc_channels_fk
foreign key (channel_id) references channels (channel_id)

create bitmap index smcc_channels_idx
on sales_months_cat_ch (channel_id);

alter table sales_months_cat_ch
add constraint smcc_categories_fk
foreign key (prod_category_id)
references categories (prod_category_id)

create bitmap index smcc_categories_idx
on sales_months_cat_ch (prod_category_id);

The focus is on how to implement this in obiee, not how these tables are refreshed with data or recreated as a part of the job of ETL process.

Implementation in obiee

Physical layer:


Foreign keys:

SALES.PRODUCT_ID >- PRODUCTS.PRODUCT_ID
SALES.TIME_ID >- TIMES.TIME_ID
SALES.CHANNEL_ID >- PRODUCTS.CHANNEL_ID

SALES_MONTHS_CAT_CH.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES_MONTHS_CAT_CH.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID
SALES_MONTHS_CAT_CH.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

SALES_YEAR_CAT.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID
SALES_YEAR_CAT.CALENDAR_YEAR_ID >- YEARS.CALENDAR_YEAR_ID

SALES_MONTHS.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

BMM:

Drag and drop attributes from the physical layer to BMM, for example CALENDAR_YEAR_ID and CALENDAR_YEAR from YEARS physical table to TIMES logical table to create additional logical table sources. We repeat this step for other higher level dimension tables on the physical layer as weel as for SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH aggregate fact tables that contains measures AMOUNT_SOLD and QUANTITY_SOLD.



Dimensions:


On each logical fact table source on the logical fact table SALES we need to set aggregation levels and this is mandatory step for obiee to redirect SQL query on aggregate tables.

Aggregate sources are activated on certain levels of dimension.




Test

If we add CALENDAR_MONTH_DESC, instead of going to SALES (TIME_ID lowest level) and summarize it on the month level, the SQL query is redirected to SALES_MONTHS:


NQQuery-log:


In case of CALENDAR_YEAR the SQL query is also redirected to SALES_MONTHS:


Some other cases:

CALENDAR_MONTH_DESC, PROD_CATEGORY and CHANNEL_DESC:


NQQuery-log:


CALENDAR_YEAR and PROD_CATEGORY:



NQQuery.log: