OBIEE Aggregate Persistence Wizard…. and all the wrongs about it!!!

Wrong Mustard

It’s fundamentally wrong!!

Have you heard of Oracle Business Intelligence’s aggregate persistence wizard or worked on it lately? I think, it’s one poor guy whom most of the developers got wrong!!!

Let me try clearing some air about it…….

  • Were you being tired of designing your aggregated data models?  
  • Were you having tough time generating dimensional surrogate keys?  
  • Are you thinking of how to go about loading the aggregates, even if you had the datamodel?
  • Are you constantly being confronted by the task of updating the Metadata (.rpd), even if you have all the aggregates in place?

If you have any of the above issues, try OBIEE’s Aggregate Persistence Wizard.
What is an aggregate wizard?
It’s one insanely cool wizard that has got some great intelligence. With few selections, it can generate an extremely powerful Sql file that can be used to generate, load aggregate tables and update the metadata too.

Hey, If are quite smart enough to write your own file, yes you could, just follow a bunch of syntactic and semantic rules out there!!

I get it, but how do I start one?
Just open up your .rpd and invoke it through Tools -> Utilities -> Aggregate Persistence -> and hit the Execute button. And yesss, follow the instructions through which are quite simple enough. High level steps are

  1. Choosing the file destination. 
  2. Choosing the business model and measures 
  3. Selecting hierarchical levels of various dimensions, at which the aggregates have to be designed. 
  4. An option if you would like the tool to generate surrogate keys and make use of them.
  5. The destination where you would like the aggregate table to persist (In simple terms, reside!!)

I got the file, but how do I run it?
You got to the almost final step of the activity, ie generating the aggregate. If you have worked on NQCMD tool in OBIEE, you could simply use it to run this file too.

Hey, it’s all good; What makes you worried by the way?
Most of the developers get to this step very easily, and that is the reason I say running the file as the almost last step. Following are few main points to be borne in mind

  • SQL generated by the wizard, is a logical sql that could be understood only by the BI Engine and you, of course. Yes, if you happen to go through the sql, it’s NOT ANSI sql. There are no Datatypes, Sizes, Constraints, Keys .. etc. So, please do not attempt to run this sql command against your physical database. 
  • The NQCMD syntax is as follows

nqcmd -u -p -d -s
: Is the user who is present in OBIEE with administrator privileges.
: Of course, it’s the above user’s password.
: This is the DSN which the OBI Presentation Services uses to talk to the BI Server. If you understand my previous point, it’s a logical sql that could be understood by BI Server only. So, to pass this command to it, we have to use the DSN that connects to it.
: Yes, you are correct!! It’s the sql file with the commands.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s