Hello
All,
After
long time, I hope all are doing well. Let discuss today what aggregate table is
and why we use aggregate table in OBIEE.
Use of
Aggregate table: Aggregate tables store precalculated measures that have been
aggregate over a set of dimensional attributes. This is very useful technique
for speeding up query response time in decision support systems. This
eliminates the need of run time calculations and delivers faster results to
users.
Steps:
Create
all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.
Below you will see the dimensional table, fact table and hierarchies.
Go
to Tools then Utilities, Select Aggregate Persistence Wizard, and then click Execute
Button.
Now
select appropriate path where you need to generate Aggregate Table SQL.
Note:
Click Generate DDL file for first time generation of Aggregate Table.
Click
next, to move to next page (Select Business Model & Measure Page)
In Select Business Model & Measure Page,
Select Appropriate Business Model & then select associated Fact / Measure.
Click
next, to move to next page (Select Dimensions & Levels).Select
appropriate level of dimension & check Use Surrogate Key.
Click
next, to move to next page (Select output Connection Pool, Container &
Name).
Click
next, to move to next page (Aggregate Definition).
Select
I am Done (Radio Button).Click
Next.
Click
Finish. Your Aggregate Table is created Now & available at the path you
specified in “Select File Location” Page.
I
then close the wizard down and take a look at the script that it’s generated.
To
view generated script move to - C:agg
Before
I run the script, I insert another command into the script before the “create
aggregates” command, to clear down any aggregates that might already exist:
Now
to execute the create table scripts using nqcmd Command , your need to know the
SYSTEM DSN for the OBIEE 11G server.You can find it at the ODBC Administrator.
Type odbcad32 from
windows run
If
you happen to see more than one DSN name for obiee server then get the name
from the Add button as shown below.
Set
the environment variables by the bi-init.cmd as shown below
D:\obiee/instances/instance2/bifoundation/O
racleBIApplication/coreapplication/setup/bi-init.cmd
Issue
the blow command
nqcmd.exe
-u weblogic -p Rakesh123 -d coreapplication_OH530560862 -s D:\agg.sql
Note
that the entry after –d switch is the name of the OBIEE DSN. (to check this
Start>run>type odbcad32)
-u
is the administrator username
-p
is the administrator password
-s
is script path
Once
script executes, the command window displays the same as Processed. Note, any errors
in this stage are because of 2 reasons:
- BI Server is not running (opmnctl startall should be done) OR
- The script is taken from an rpd that’s not deployed (take script from a rpd in online mode)
I
take a look in the scheme in which it’s created the aggregates; I see that some
system-generated tables are now there:
Looking
at the Physical Layer in the BI Administration tool, I can see the new
aggregate tables created and registered, and shown in red to show they’re
aggregates.
Similarly,
new Logical Table Sources have been created in the business model to link these
aggregate tables to the existing logical model.
Now,
if I run a query through Answers that selects the Quantity measure at this
level of aggregation, and then check the NQQuery.log file to check that the new
aggregate tables are being used, I see it’s all working ok.
Notice
that each of the objects created by aggregate persistence wizard in the rpd has
a small “GEAR” symbol.
- Rakesh patil
- rakeshpatil002@gmail.com