Aggregate Persistance Wizard in OBIEE

Steps for Creating Aggregate Tables in OBIEE

1. Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.
2. Go to ToolsàUtilities (from Administration tool menu bar), a dialog box will pop up with all available utilities.
3. Select Aggregate Persistence Wizard, and then click Execute Button. (See Figure Below)

4.       Now Select appropriate path where you need to generate Aggregate Table SQL.

Note : Click Generate DDL file for first time generation of Aggregate Table. (See Figure Below)

5. Click Next, to move to next page (Select Business Model & Measure Page)
6. In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.(See Figure Below)

7.       Click next, to move to next page (Select Dimensions & Levels).

8. Select appropriate level of dimension & check Use Surrogate Key.

9.       Click next, to move to next page (Select output Connection Pool, Container & Name).

10. Click next, to move to next page (Aggregate Definition).
11. Select I am Done (Radio Button).
12. Click Next.

13. Then Click Finish. Your Aggregate Table is Created Now & available at the path you specified in “Select File Location” Page.
14. To view generated script move to - C:\Agg\
15. Select The Aggregate Table Created & View the Code. Code for above process is mentioned below :

delete aggregates;  /* Delete code manually added to remove any pre-existing aggregate tables with the same name */

create aggregates

"ag_FACTINTERNETSAL"

 for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")

 at levels ("SALES"."Time"."Year" using_surrogate_key )

 using connection pool "Adventure Works"."Agg CP"

 in "Adventure Works"."AdventureWorksDW2008";

Now the script is ready, I run it using the “nqcmd.exe” utility in the /OracleBI/server/bin directory.

Steps for running Aggregate Script are mentioned below:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation.  All rights reserved. C:\Users\gaurav.mishra>cd\ C:\>cd oraclebi C:\OracleBI>cd server C:\OracleBI\server>cd bin C:\OracleBI\server\Bin>nqcmd.exe -u Administrator -p Administrator -d analyticsweb -s c:\Agg\agg.sql Where : u : UserName p : Password d : Domain s : SQL Query Path -------------------------------------------------------------------------------           Oracle BI Server           Copyright (c) 1997-2009 Oracle Corporation, All rights reserved delete aggregates delete aggregates Statement execute succeeded create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" Statement execute succeeded Processed: 2 queries

After getting successful aggregate script execution, you need to restart your BI Services.

Now Open your metadata Repository & you will see that new aggregate tables created and registered, and shown in red to show they’re aggregates.

Note : No change in presentation layer, as data is executed from logical table & physical table, so presentation layer remains unaffected.

OBIEE Performance Management

Performance Management in OBIEE for optimal Query Execution time

After the implementation of OBIEE project completes, then the real world issues datawarehouse team faces. The issues reported by end user & client are mostly based on performance issue or to say report execution time for most common business reports having huge data. Other than Table Indexing at database level, here are some of the common technique’s which may help you in metadata repository development (rpd) to overcome performance related issues.

Caching :
Caching / seeding important reports before working hours on daily basis, reduces query execution time and report executes in optimal time.

There can be various technique for caching, some of which are :

      Seeding cache from delivers / alerts (IBots)

Setting Event Pooling Table

Aggregate Table : (Aggregate Persistence Wizard)

Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard

Aggregate Table: Aggregate tables store precalculated measures that have been aggregated over a set of dimensional attributes.

Note : You must create aggregate table only in the situation when you can suppress huge number of rows of in the record of detail table.

The Ratio should be :

Detail Table : Aggregate Table >= 30 : 1

Turning off Loging :

Rpd -->Manage --> security --> users -->(Select appropriate user)--> right click --> properties

Logging can affect in performance of Oracle BI Server and can create large log files. By default logging level is 0.

Note : Logging can be used in problem solving and troubleshooting problematic queries. Do not change or update logging level until required.

Connection Pools – Best Practices for Performance Enhancement:

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage.
• Create a separate connection pool for execution of session variables.
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables.
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Constructing result using where clause :

With the help of where clause in content tab in logical table source you can filter the data (limits the rows) returned from database.Here, you can see the where clause and restrict the data using Expression Builder( marked in Blue).

Limiting Number of Initialization block:
Initialization block are the only means to initialize dynamic repository, system session and non-system session variables. We should be very careful about not to create too many init blocks.

As we know, In the case of system and non-system session variables, the initialization blocks get executed every time a user logs in to the server. And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.

Setting Query limits : 
We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition:

1) maximum number of rows a query can retrieve from a database.

2) maximum time a query can run on a database.

3) restricting access to a database during particular time periods from Analytic server.

Modelling Dimension Hierarchies Correctly : 
Dimension Hierarchy must be modelled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact, but ratio of number from one parent to child logical level should be accurate.

Setting NQSConfig Parameters : ( General Section )

Home : OracleBI\server\Config\ NqsConfig.ini (Open in Notepad to edit) à General Section à Edit :

• Sort Memory size
• Sort buffer Increment size
• Virtual Table Page Size