Passing parameters with Hierarchical Col in Report

OBIEE notes :

• The create prompted link menu item provides a good example of how to pass parameters to a dashboard page
• This URL can be embedded within an analysis to pass parameters between hierarchical summary report and a detail report without hierarchy. Action links fail with Javascript error on reports with hierarchical columns. 

Parent Child Hierarchies– OBIEE Wizard Script

 

As per the OBIEE documentation, the parent-child table requires the following columns -

• member_key
• ancestor_key
• distance
• is_leaf

We will work with the following very simple employee hierarchy

• bob
• jill
• pete
• phil

This is represented in the table EMPS as follows

The parent child map table EMPS_H, created via the OBIEE wizard, has the following rows in it

Ten rows have been created. (ignore hierarchy col)

What has the script done?

In effect, it has created a link between each member and each of its ancestor, while calculating the distance between member and ancestor.

For our example, then:

• bob has two rows, one identifying him as a root node (ancestor_key is null) and another self-referential row
• jill has two rows, one link to only ancestor bob, and self-referential
• pete has three rows, two links to ancestors bob and jill, and self-referntial
• phill has three rows, two links to ancestors bob and jill, and self-referential

So in effect, for each member, there will N rows created, where N = 1 + number of ancestors. The exception is top level members, these will always have two rows, one for self-referential, and one link to a null ancestor (this is how OBIEE identifies top-level nodes).

So we can calculate the number of rows as follows

• Bob =  top-level member = 2 rows
• Jill = 1 + 1 ancestor = 2 rows
• Bob = 1 + 2 ancestors = 3 rows
• Pete = 1 + 2 ancestors = 3 rows

Total = 10 rows

The script actually performs the following steps

• Issue a hierarchical subquery (CONNECT BY PRIOR) => returns 4 rows
• Issue a levels subquery => returns 3 rows (levels: null, 1, and 2)
• Cross join hierarchial results with levels results and set ancestors => 3*4 = 12 rows
• Set distance, and prune null ancestors => remove 3 rows = 9 rows
• Add row for each top-level member => add 1 row => 10 rows

 

 

Can a single simple CONNECT BY PRIOR SQL be used instead?

• No because a link and the distance between each member and ancestor is needed
• CONNECT BY PRIOR  can start at the bottom “mgr = prior emp” or start at the top “emp = prior mgr” but in both cases do not get the ancestor and distance cols as needed for OBIEE. (and number of rows is different for top-down)

Parent Child Hierarchies–Multiple Roll Ups

 

Is it possible to roll up a member to multiple ancestors in a parent child hierarchy?

I have the following two hierarchies in the same table:

Management hierarchy:

• bob
  • jill
    • phil
    • pete

Sales hierarchy:

• barbara
  • jenny
    • pete
    • pat

Modelled in the RPD

And shown in Answers

As can be seen, pete rolls up to two different ancestors: bob and barbara.

Using a standard OBIEE parent child hierarchy, however, the dipslay control only shows pete once when both hierarchies are shown.

If each individual hierarchy is shown, then pete appears under both.

Interestingly, the underlying SQL is rolling up pete twice correctly.

 

In conclusion, it appears that the front-end control does not like multiple roll ups.

The problem occurs even if you using a session variable to choose one of the underlying hierarchies.

Notes on parent child tables:

No join needed for map

How to use the Aggregate Persistence Wizard – Part II - Under the Hood

Technorati Tags: OBIEE,Aggregate Persistence Wizard,10g,11g,Aggregates

Following up to my previous post, this section looks at what happens under the hood when the logical SQL is executed. the Aggregate Persistence Wizard issues the following logical SQL:

• create aggregates

. We will also look at:

• delete aggregates;
• prepare aggregates

 

Create Aggregates

The built-in Aggregate Manager module performs the following where the “create aggregates” logical SQL is issued:

1. Defines Aggregate Creation Plan
   1. Phase 1 = dimensions
   2. Phase 2 = facts
2. Create physical dimension tables
3. Check in and save changes
4. Populate dimension tables
   1. Example: SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1; populate "AG_Detail_000153B1" mode ( append table connection pool "OBIEE Local DB"."OBIEE - Connection Pool") as  select_business_model "Dim - Source"."Source_Type" as "Source_Typ000153A2","Dim - Source"."Segment" as "Segment000153A3", RCOUNT (1) as "Detail__000153B1SK" from "Use Case 1: Calcul Parc";
5. Create logical table sources and set to state to Active
6. Check in and save changes
7. Create physical fact table
8. Check in and save changes
9. Populate physical fact with populate command (similar to above example)
10. Create fact LTS and set state to Active
11. Check in and Save Changes

-------------------- Aggregate Manager: *******BEGIN: Create Aggregates*******

-------------------- Aggregate Manager: *******END: Create Aggregates*******

Delete Aggregates

The built-in Aggregate Manager module performs the following where the “delete aggregates” logical SQL is issued:

1. Deletes logical table sources for aggregate tables
2. Checks in changes and saves RPD
3. Drops physical db tables
4. Deletes physical layer tables
5. Checks in changes and save RPD

These actions are visible in the query log identified by:

-------------------- Aggregate Manager: *******BEGIN: Delete Aggregates*******

-------------------- Aggregate Manager: *******END: Delete Aggregates*******

prepare aggregates

Similar to create aggregates without the populate scripts

 

The populate command

Perhaps this can be used for an aggregate refresh script.

How to Use the Aggregate Persistence Wizard – Part I

Technorati Tags: obiee,10g,nqcmd,aggregates,performance

The Aggregate Persistence Wizard is a nifty utility for creating, modelling and populating aggregate tables available in OBIEE 10g and 11g. This utility is especially useful to quickly build a single aggregation layer upon federated heterogeneous physical sources.  OBIEE will create, model and populate aggregate dimensions and facts based upon the intelligence you have built into the RPD. This avoids re-writing the same logic into your ETL.

For this walk through, you will need a physical database connection where you can create and populate tables and the OBIEE Scheduler to schedule the automatic creation of aggregates.

Mark Ritmman has a post on the subject here. My entry complements his.

3 Steps

A) Generate script with Wizard

B) Test script with nqcmd

C) Schedule script with job manager

Pros/Cons of Aggregate Persistence WIZARD

Pros:

• Simple setup
• Leverages RPD defined dimensional model, avoids redefinition in ETL
• Combines well with federated data sources
• Can be scheduled in post load processing of ETL

Cons:

• Production ready?
• RPD and physical objects are recreated on each run and limits on object naming
• Physical table metadata maintained in OBIEE (as opposed to say the Data Warehouse Administration Console or other tool)
• Cannot define aggregate table indices (though there may be a work-around)

 

A) Generate script with Wizard

1. In the Admin Tool, Tools->Utilities->Aggregate Persistence Wizard
2. Enter a location to save scripts, ex: c:\temp\aggwiz.sql (leave Generate DDL File unticked), Next
   1. Note: The Generate DDL script will create a second script with the suffix _DDL which can be used for the create and model the aggregate(s) without population. The main script will create, model, and populate aggregates.
3. Select business model then measure or fact table to aggregate
4. Set the logical levels, check the Use Surrogate Key? checkbox if the underlying physical joins are complex (OBIEE will create surrogate keys for the aggregate)
5. Next
6. Select database, catalog / schema, connection pool, and aggregate table name where the aggregate table will be created. Note:
   1. The aggregate table name is the physical table that is created in the database and can only contain a certain number of characters
   2. Dimensional aggregates will also be created, populated, and modelled. These are named automatically with the prefix SA_ by default (changeable in NQSConfig.ini option AGGREGATE_PREFIX)
   3. Check the Allow populate by default checkbox for the database
7. Click on “I am done” then Finish. Note: This does not create the aggregate.

B) Test script with nqcmd

1. Open the resulting script in your favourite text editor
2. Notice the create aggregates BI SQL
3. This command will physically create, populate, and model the dimensional and fact aggregates
4. Make sure you have no RPD objects checked out
5. Run the script with nqcmd (see below for example)
6. Notice the creation of aggregate dimensions and fact in physical and BMM layers.
7. Test that aggregates are indeed used from Answers.

Example script (c:\temp\aggwiz1.sql):

create aggregates

"ag_Faits_Lignes_MS"
for "Use Case 1: Calcul Parc"."Faits - Lignes"("Nb_Activations","Nb_Resiliations")
at levels ("Use Case 1: Calcul Parc"."Dim - Source"."Detail - Segment", "Use Case 1: Calcul Parc"."Dim - Date"."Mois")
using connection pool "OBIEE Local DB"."OBIEE - Connection Pool"
in "OBIEE Local DB".."POCFED01"

Example execution with nqcmd, change parameters as necessary:

c:\temp>nqcmd -u Administrator -p Administrator –d AnalyticsWeb –s aggwiz1.sql

RPD showing aggregates created by script:

Content tab for a logical table source showing aggregation level:

C) Schedule script with job manager

1. Modify your script and add the following line at the beginning: delete aggregates;
   1. This will drop physical tables, and RPD BMM and Physical layer objects
2. In Job Manager, open a Scheduler Connection
3. Jobs->Add New Job
4. Enter Name, User ID, DSN, and Schedule accordingly (see below for my example)
5. Script Type: NQCmd
6. Set SQL Input File to your aggregate (optionally place your script in  server\Scripts\Scheduler directory)
7. OK
8. Click on Run Job(s) Now to test execution

Voila. You now have a scheduled aggregate. If you check the physical SQL that is generated you will notice populate and create BI SQL. It may be possible for a script to simply repopulate the aggregate instead of dropping and recreating it each time.

If you want more take a look at what goes on under the hood.

OBIEE 11g Publisher Boilerplates

Changing BI Publisher Default Templates

Ever wondered how to change the Shared Templates section when you create a new BI Publisher report ?

I did.

In fact, these are stored under in the report /Shared Folders/Components/Boilerplates.xdo

Click Edit to edit the boilerplate. Then Add New Layout.

Make layout changes as necessary, and save as a new Layout.

 

Create your layout as necessary, and save it as a new Layout.

Now, when you click on New->Report, select a Data Model, and your layout should be present.

Click on the New Layout, the online layout editor will open, based upon the boilerplate.

Done.

Load Balanced OBIEE 10g Clustered Installation

In this post, I’ll install a four node OBIEE 10g cluster integrated with Oracle Application Server (for BI Publisher) IIS, and SSO.

There will be two “web nodes” which will host IIS, OAS, and the web plugins. These machines will be node01 and node02. There will be two “app nodes” which will host all the other OBIEE components.

I will setup the installation with the default sample sales application.

Download Oracle Software

First download

biee_windows_x86_101341.zip

soa_windows_x86_101310_disk1.zip

(32-bit)

Install Oracle Software

We will be integrating OBIEE10g with OAS 10g. Download and unzip Oracle Application Server 10.1.3.1:

soa_windows_x86_101310_disk1.zip

Run setup.exe

Choose Installation Directory, and Advanced Install Next

Then, J2EE Server and Web Server Next

I simply ignored DHCP error message.

Then, Automatic Port Config, Next

Then

AS Instance Name: iAS

Password: oc4jadmin

Tick configure …

Next

Ignore Cluster Topology Configuration, Next

Summary, then Install

 

 

 

The Install.

Setup Vmware Image

Ensure that the vmware image has plenty of disk space set up. (I am using Windows 2003 SP2 32-Bit) Also ensure that 1 Gb of ram is ready for use and that you can access host system folders. Setup bridged or NAT networking. (I use bridged)

Fire up the VM.

Log on as Administrator. Change the computer name to something identifiable – eg: OBIEE10gNode01

Reboot.