OBIEE11g Aggregate At

One of the new feature of 11g is the AGGREGATE AT function. It uses the hierarchical level to pin the the aggregate. Problem is you can’t select a hierarchy level in the formula editor, so you have to some old school typing:

 

Example:

Only month 1 to 6 are selected.

Just like the BY statement you can do multiple levels from different Hierarchies:

Till Next Time

OBIEE Aggregate BY part 2

About two year ago I wrote an article on using the BY statement to “pin” your calculation on certain level. (http://obiee101.blogspot.com/2008/02/obiee-aggregate-by.html). Recently Kurt Wolf of KPI partners did a good analysis on how to “pin” the calculations on for the complete request. (http://kpipartners.blogspot.com/2009/12/aggregate-function.html) Here is a simple implementation of his findings.

Let’s start with a simple report, YEAR and AVG PRICE ("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)"):

If we drill down the time dimension, we will see the AVG Price change accordingly:

We can “pin” an extra avereg column to the calendar year by using the AGGREGATE BY function (AGGREGATE(("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)") by "D0 Time"."T05 Per Name Year")) (IT’S NOT in the formula editor, you will have to type it!)

 

But what if we want it for the whole report? Simple leave the BY part empty: (AGGREGATE(("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)") by )

Till Next Time

OBIEE Moving period to date aggregations

When using the OBIEE todate functionality the BI-server starts at the first entry next higher dimensional level. => TODATE(FACT, TIME.MONTH) means that it will start aggregating from the first month it will find on the TIME.YEAR level.

Recently on of out customers wanted a moving todate column, Starting 21 days ago and going on for the next 7 days after CURRENT_DATE. We couldn’t use a MSUM since we didn’t have an entry for each date and the total reporting period was greater then the aggregation period.

We fixed by creating a extra column on the calendar view on the database indentifying the period.

CASE WHEN DAY_DATE <SYSDATE –21 then ‘00-PREVIOUS’ ELSE CASE WHEN DAY_DATE > SYSDATE + 7 THEN ‘02-NEXT’ ELSE ‘01-CURRENT’ END END AS P21MINT7PLUSPERIOD

This will give ‘00-PREVIOUS’, ‘01-CURRENT’ or ‘02-NEXT’ for P21MINT7PLUSPERIOD. It’s important that you created this in the physical layer. The TODATE function doesn’t like dynamical column in the Business Model Layer.

Next when created an alternative hierarchy: TIME.TOTAL => TIME.P21MINT7PLUSPERIOD => TIME.DAY. Now we could use the TODATE function like TODATE(FACT, P21MINT7PLUSPERIOD) Which gives us a daily moving period.

Till Next Time

OBIEE identifying the hierarchy level

On the OTN forum I was asked how you can identify the hierarchy level which the user is currently on (Did he select Year, Quarter,Month etc?).

One way of doing this is "using" the aggregate awareness availability of a column. First you create a "dummy" aggregate view for each hierarchy:

• year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
• quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date
• etc.

These tables you physically join to your dimension:

Update 2010/06/28:

Add Dummy joins to your other dimensions:

 

Set the expression to 1=1

In your fact table you add an extra column named dim_date_level:

Next add the sources to your fact table:

Add an inner join on your core fact table to your original dimension:

On each level source map to the column:

and set the aggregation content level:

set the aggregation level for the level column to MIN:

Test your Report:

Be sure to check all your dimension levels!

Till Next Time

OBIEE Creating indexes Q&D Style

If you are the Aggregate Persistence Wizard to create your aggregate tables, you don't get any indexes. Here is a small (ORACLE) script to create them on the fly Q&D style:

BEGIN
FOR i IN (SELECT 'CREATE INDEX IDX_AGGR' SUBSTR ('0000'
TO_CHAR (ROWNUM), -5) ' ON AGGR.' table_name ' (' column_name ') NOLOGGING NOPARALLEL' AS sql_str
FROM all_tab_cols
WHERE owner = 'AGGR'
AND (table_name LIKE 'AG%' OR table_name LIKE 'SA%'))
LOOP
execute immediate (i.sql_str);
END LOOP;
END;

Till Next Time

OBIEE Aggregate Persistence Wizard

If you are doing POC on OBIEE or have to do some RAD work, you usually don’t have access to ETL of the database to prep the aggregate tables. If you have access to an DB-schema with some create and drop table rights you can use the OBIEE Aggregate Persistence Wizard to do it Q&D / RAP style. (More info on using aggregate tables you can find here: http://obiee101.blogspot.com/2008/11/obiee-making-it-aggregate-aware.html )

The examples in this article are based on this simplified business model;

Prep the repository:

Open the repository in online mode.
In the physical layer of your repository create a new database, connection pool and schema.

From the tools > utilities menu select the aggregate persistence wizard:

Enter a location for the script:


Select the fact(s) you want to aggregate:
Select the logical levels for the aggregate:

Checking the "Use Surrogate Key ?" will later on map the aggregates using a surrogate key, this can improve performance if you have (complex) keys, consisting of multiple columns.

Select the database, schema and connection pool the script has to use. Be sure to have create and drop table rights in that schema.

Check the "I am done"

Close the online repository .
Open the file, it should look something like this:

create aggregates
"ag_F_FACTS" for "BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at
levels ("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Before the "create aggregates" add "delete aggregates;" (don’t forget the semicolon!)

delete aggregates;
create aggregates
"ag_F_FACTS" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Run the script using nqcmd:

{OracleBI}\server\Bin>nqcmd -u Administrator -p Administrator -d AnalyticsWeb
-s c:\agg_wizz.sql

Open the repository again and have a look at the AGGR database in the physical layer:

If you have a close look at the joins you will see that they are using the surrogate key:

Now check your business model:


If you open one of the aggregates in the Sources you will see that OBIEE already did the level mapping:

In the log file you can find the SQL issued to the database:

CREATE TABLE ag_F_FACTS ( LEVEL_0_000000C6SK DOUBLE PRECISION, D_YEAR_000000D6SK
DOUBLE PRECISION, F_FACT_VAL00000086 DOUBLE PRECISION )

Now check with a simple request if OBIEE is actually "picking up" the aggregate:

From the log:

select distinct T1047.LEVEL_0100000074 as c1, T1052.D_YEAR0000007E as c2,
T1059.F_FACT_VAL00000086 as c3 from SA_LEVEL_0000000C6 T1047, SA_D_YEAR000000D6
T1052, ag_F_FACTS T1059 where ( T1047.LEVEL_0_000000C6SK =
T1059.LEVEL_0_000000C6SK and T1052.D_YEAR_000000D6SK = T1059.D_YEAR_000000D6SK )
order by c1, c2

The next step

Based on the basic script we have now with some clever copy and paste actions it’s easy to extend to other aggregates.

delete aggregates;
create aggregates
"ag_F_FACTS01_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS02_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_02" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS03_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_03" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS04_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_04" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Before you run the script check your "," and "";" and be sure that each aggregate has a unique name .

When (not) to use this feature:

• This is perfect in a Proof of Concept (POC) or if your have to do some rapid application development (RAP)
• This feature work very crude: A delete aggregates is a "DROP TABLE"
• This feature doesn’t create any indexes, you have to create them yourself!
• This works perfect if you have to do some cross database aggregates, it can really boost the performance there.

Till next time

This article was orginally written for the Ciber knowledge blog: http://knowledge.ciber.nl/weblog/?p=149

OBIEE making it "aggregate aware"

In a previous posting (http://obiee101.blogspot.com/2008/11/obiee-huge-dimensions-lets-split-them.html) I showed a way to split huge dimensions to bring down the response time of OBIEE. Can we bring down the response time even more? Of course we can, let’s make OBIEE aggregate aware.

The aggregate tables

First we have to change our ETL so that we get aggregate table(s) in the form:

ag_fact_table_level_1; dim_level_1, fact_aggregate_level_1
ag_fact_table_level_2; dim_level_2, fact_aggregate_level_2
ag_fact_table_level_3; dim_level_3, fact_aggregate_level_3
ag_fact_table_level_4; dim_level_4, fact_aggregate_level_4

Mapping the physical layer

Next we have to map our dimension table(s) to our aggregate table(s):

Mapping the bussiness model

Your fact mapping should look like this:

Be sure to check to level on the datasource of the aggregate table:


Check that the Aggregation content is on "Logical level"
If you are not sure about the level use the Check levels functionality (press the more button)

Check if each level of the dimension(s) is mapped correctly:

Checking the results:
Level 1

Log:

select distinct T36.LEVEL_01 as c1, T307.F_FACT_VAL as c2 from DIM_LEVEL_01 T36, F_FACTS_1 T307 where ( T36.LEVEL_01 = T307.LEVEL_1 ) order by c1

Level 2

select distinct T38.LEVEL_01 as c1, T38.LEVEL_02 as c2, T310.F_FACT_VAL as c3 from DIM_LEVEL_02 T38, F_FACTS_2 T310 where ( T38.LEVEL_01 = ‘A’ and
T38.LEVEL_02 = T310.LEVEL_2 ) order by c1, c2

Level 3

select distinct T41.LEVEL_01 as c1, T41.LEVEL_02 as c2, T41.LEVEL_03 as c3,
T314.F_FACT_VAL as c4 from DIM_LEVEL_03 T41, F_FACTS_3 T314 where ( T41.LEVEL_01 = ‘A’ and T41.LEVEL_02 = ‘AA’ and T41.LEVEL_03 = T314.LEVEL_3 ) order by c1, c2, c3

And so on…..

This article was orinally written for the ciber knowledge blog :

http://knowledge.ciber.nl/weblog/?p=148

Till Next Time

OBIEE ReportAggregateEnabled

My list of things I want to blog about is getting hugher every day. Today it stands at 32. As with most bloggers my biggest problem is R&D time. Luckily more and more people start blogging on OBIEE.
One of those people is Kevin Custer. He made a great entry on "ReportAggregateEnabled" here: http://oraclebi.soundvoid.net/2008/10/grand-totals-with-calculated-columns-2/. This is one of those things you have look at when you work "complex" grand totals. It is not in the documentation only on metalink3 : SR 3-159350471



Till Next Time

OBIEE Aggregate BY

OBIEE has many aggregate functions: AVG, AvgDistinct, BottomN, Count, CountDistinct, Count (*) (CountStar), First, Last, Max, Median, Min, NTile, Percentile, PeriodAgo, PeriodToDate, Rank, StdDev, Sum, SumDistinct, TopN.

Default OBIEE "groups" these functions on the lowest level. If you want it in an other "grouping" you can use the "BY" statement in your function definition.

If neccesary you can also define multiple columns (Comma seperated):

In the background OBIEE is translating this to ANSI-SQL:

WITH SAWITH0 AS (select T21.LICENSEPLATE as c1,
T21.MEASUREDATE as c2, T21.FUELCONSUMPTION as
c3, avg(T21.FUELCONSUMPTION) as
c4, count(T21.FUELCONSUMPTION) as
c8, sum(T21.FUELCONSUMPTION) as c9from
TBLFUELCONSUMPTION T21group by T21.LICENSEPLATE,
T21.MEASUREDATE, T21.FUELCONSUMPTION)

select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3
as c3, SAWITH0.c4 as c4,

sum(SAWITH0.c9) over (partition by SAWITH0.c2) / sum(SAWITH0.c8) over
(partition by SAWITH0.c2) as c5,

sum(SAWITH0.c9)
over (partition by SAWITH0.c1) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1) as c6,

sum(SAWITH0.c9) over (partition
by SAWITH0.c1, SAWITH0.c2) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1, SAWITH0.c2) as c7

from SAWITH0order by
c1, c2, c3

Which isn't always the most cost effective way, you might consider using the EVALUATE_AGGR function instead for very complex aggregations.



Till Next Time