Putting ERP to Work in Higher Ed: OBIEE Analysis as BI Publisher Data Source

Examining the ongoing challenges of delivering high-quality, value-added ERP services in Higher Education.

Monday, August 6, 2012

OBIEE Analysis as BI Publisher Data Source

BI Publisher Enterprise is a very powerful tool, especially because it can access so many different data sources without the burden of data modeling. By going around the RPD layer, BIP can access transactional data sources directly, which makes it an excellent option for replacing existing operational reports. BIP's powerful and flexible formatting including WYSIWYG parity between report layout and report output ("pixel-perfect" is another way to say this) make it a far superior tool to Analysis (Answers) or Dashboards for producing printable output.

But there are some compelling features about Analysis (Answers) that you may want to retain, including all that security you've spent so much time building into the repository (RPD). That alone may be a compelling reason to want to layer BIP on top of BI Analysis. But also consider these justifications: 1.) The ease of building queries in Analysis (Answers); 2.) the potential for "multi-use" for a single query -- perhaps you also want the data in a Dashboard as well as in a printable format.

On the face of things, this seems like a straight-forward integration. Right there in the Data Model configuration screen there is an option to use BI Analysis as a data source. Unfortunately, Oracle has made some design decisions that make this option less than perfect... Below I will walk you through the challenges of the provided integration and suggest an alternative path to meeting your requirements.

First, let's have a look at the query I created, which merges some customer indicative data along with revenue and order count by year.

Now let's look at the Advanced Tab for a moment -- especially the SQL generated by my query. I'll copy this and save it in Notepad for now... You'll see why in five minutes...

Now over to BIP, we create a new data model, choose BI Analysis as the data source, and voila -- there's my data model. With one little problem -- what is "Column1"? All columns are brought over with generic text.

Create New Data Model

Create Data Source "Oracle BI Analysis"

Navigate to the Analysis (Answers) Query

Imported Metadata from Analysis query

I can fix this problem by going to the "Structure" tab and revising the XML Tag Name to match the "Business view Display Name" at the far right. See below.

This alone would not be a major deterrent to taking advantage of the integration. However, it is not the only limitation... Back on the Diagram tab I should have some flexibility to restructure the XML. Note that for this data model, the only options available to me are Properties and Help. Options to create a group or define a link to another structure are not available.

This is by design and documented in section 2.8.1 of the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1). But I am not satisfied with this outcome, and I have another idea. Remember that SQL we copied from the Advanced Tab of my initial Analysis (Answers) query? Let's put that to use. Back on the Data Model screen, I will initiate a new SQL data source and use the Analysis SQL (with slight modifications).

Now when I try to manipulate the columns in my data structure I have complete flexibility -- note the additional options.

Below is a fully restructured XML diagram, with the customer indicative data pulled up (along with a grand total) and then detailed annual figures collected as children. This XML will be much easier for me to use in the report I want to build!

One other quick point of information... I have used this technique extensively in one of my applications, but found that a helpful companion configuration is to define an Agent for the original Analysis for the purpose of cache seeding. If the data is in the cache, the BIP reports are lightning fast with the SQL against the presentation layer!

Labels: BI, Business Intelligence, OBIEE, Tips and Tricks

5 Comments:

At February 13, 2013 at 6:00 PM, Anonymous said...: Thank a lot
At March 14, 2013 at 9:02 PM, Anonymous said...: Nice article. Is there any way to pass a parameter in the logical sql. :param_name does not work.
At November 21, 2013 at 2:40 AM, Sandeep said...: Hey

Nice post!
I have created a same report like this i.e. I created a data model and the data set is based on the analysis report.
The problem I am facing is that, the analysis report data is changing by selecting values from the prompt. But that is not reflected in the BIP report. BIP report is always showing the default data of the analysis report.
Any suggestion about what I am doing wrong would be helpful.
Thanks in advance!
At February 13, 2014 at 4:23 AM, Unknown said...: Hi,

Very Nice Post.

I have an user who has created a Data Model, Data Set and saved it in "Shared Folders" (in OBIEE). But while creating a new report and using the exisiting Data Model he was not able to select that Data Model which previously created as he is not able to see the "Shared Folders" itself.

I have checked the access (roles and permissions) and he has all the access.

Could you please help me on this issue.

Thanks,
Vijay
At July 11, 2017 at 11:20 AM, John said...: Thank you this really helped!! Exactly what I was looking for.