Oracle BI EE, there is an option to improve the performance. Aggregate Persistence Wizard

To use go to Tools -> Utilities -> Aggregate Persistence Wizard

Select measures which was applied with an aggregation function. Otherwise , the columns will not be displayed.

The following logical table diagram our situation. Account is used by qualified list item, if you want use new aggregation table, you have to assign this level which has the your qualified list item column within the table.

Select dimension levels:

Account Detail ( consists Qualified list item column )

City

Day Detail

nqcmd -u Administrator -p Administrator -s “D:\AggregatePromAgg.sql” -d AnalyticsWeb

this is the command which will be executed to create your new aggregated table.

The following query is created by Oracle BI EE.

It creates main table which has aggregation column and keys for dimension tables.

Dimension tables has all data in the original table. City table and

sa_city80c084fb are the same. To use this table from main table

ag_custpromagg_city_daydetail_accoundetail, it adds a new column cityid to

ag_custpromagg_city_daydetail_accoundetail.

/* Formatted on 2008/08/08 11:36 (Formatter Plus v4.8.7) */

CREATE TABLE ag_custpromagg_city_daydetail_accoundetail ( city_code80c0829b, date_of_tr80c082cd, card80c088a4, totalamoun80c0eb08 ) AS SELECT d1.c1 AS c1,

d1.c2 AS c2,

d1.c3 AS c3,

d1.c4 AS c4

FROM

(SELECT t82929.city_code80c0829b AS c1,

t82937.date_of_tr80c082cd AS c2,

t82947.card80c088a4 AS c3,

t82972.totalamoun80c0eb08 AS c4,

ROW_NUMBER() OVER (PARTITION BY t82929.city_code80c0829b, t82937.date_of_tr80c082cd, t82947.card80c088a4 ORDER BY t82929.city_code80c0829b ASC, t82937.date_of_tr80c082cd ASC, t82947.card80c088a4 ASC) AS c5

FROM

sa_custome80c08d2a t82947,

sa_day80c0850b t82937,

sa_city80c084fb t82929,

ag_custpromagg_1 t82972

WHERE ( t82929.city_code80c0829b = t82972.city_code80c0829b AND t82937.date_of_tr80c082cd = t82972.date_of_tr80c082cd AND t82947.card80c088a4 = t82972.card80c088a4 )

) d1

WHERE ( d1.c5 = 1 )

We can select any column from city , day detail, account detail. The aggregated table and dimension tables which are selected will be used, you can check it from generated qurey in NQQuery.log , segmentation criteria DAYNUMBER = 2

like that :

WITH
SAWITH0 AS (select distinct T82947.MIGROSCARD80C088A4 as c1
from
SA_CUSTOME80C08D2A T82947,
SA_LOOKUPD80C0850B T82937,
ag_CUSTPROMAGG_1 T82972
where ( T82937.DATE_OF_TR80C082CD = T82972.DATE_OF_TR80C082CD and T82937.DAYNUM80C082D3 = 2 and T82947.MIGROSCARD80C088A4 = T82972.MIGROSCARD80C088A4 ) )
select 'N0' as c1,
count(*) as c2
from
SAWITH0