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


Friday, February 8, 2013

Action Framework and GO URL in OBIEE

Let's talk about an unsung hero in Oracle BI (OBIEE) -- the "Go URL." It is hard to find good documentation on this feature, but let me give credit to a few sites that helped me out: Gerard Nico, iWareLogic.

First off, what is "Go URL"? In a nutshell, it is a direct URL to execute an OBIEE object (Analysis, Dashboard, BI Publisher Report), but its true power comes when paired up with the Action Framework in OBIEE. Through this technique, the developer has a very powerful method for creating virtual "drill paths."

Stepping back a bit, the OBIEE Action Framework enables the report developer to allow the user to invoke actions from within query results. These actions include navigation to other OBIEE content, websites, web services, E-Business Suite, Siebel, or Oracle EPM. A common use case is to enable navigation to other BI content. The great thing about this is that the context of the entire row is passed implicitly to the receiving Analysis, allowing an effective "drill-down" outside of standard hierarchy-style automatic drill-down. But there is a catch -- the fully-qualified name of the column must exactly match for the prompt to be accepted. In other words, if the master query is in Subject Area A, Folder X and the receiving query has the same column in Subject Area A, Folder Y then it is NOT THE SAME COLUMN.

This may seem obvious to many OBIEE experts working within ideal subject areas containing one fact table but I have been working with a (purchased) model that defies some OBIEE best practices. But this solution also addresses potentially more common cross subject-area use cases. Fundamentally, what the GO URL "hack" allows the developer to do is pass any column values from the master table into the "WHERE" clause of the receiving query as values rather than column/value combinations.

Let me give you an example. I am using the Noetix Generator for Oracle BI to create subject areas for Oracle E-Business Suite. My use case is that I have one report created against the GLG0_Balances "Folder". Below is a screenshot of my query, which is a basic Trial Balance report.

Now I also know that I would love to be able to drill down on the "Period Net" to see the detailed journal entries. That ultimate query is shown below -- note that although it is the same Subject Area, the entire query comes from a different "Folder" in the presentation layer. [For the semantic layer purists in the room who balk at this, please imagine for now that it was a different subject area...]

 The next step is to capture the URL for the target Answers query; run the analysis from the catalog and snag it from your browser. It will look something like this:

https://yourservername.com/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FGeneral%20Ledger%2FJournal%20Entries%20Report

Copy this off to Notepad or something. Change "PortalGo" to just "Go" and delete "Action=prompt" from the URL. At the end add this syntax "&Action=Navigate&P0=1" Now your base URL looks like this:

https://yourservername.com/analytics/saw.dll?Go&path=%2Fshared%2FGeneral%20Ledger%2FJournal%20Entries%20Report&Action=Navigate&P0=1

Now on to passing parameters/prompts. The first rule is that every column you want to prompt must be configured in the "Filters" section of the target, but it -- and this is important -- need not be defined "is prompted" as is the case with Dashboard Prompts. Decide which filters you want to set and find the extended name of the columns, for example: "- Nx_GLG0_Journal_Entry_Lines (General Ledger Views)"."Period Name" This is a little tricky because the "table name" portion is not exactly the same as what shows in the Analysis GUI.

You will now create matched pairs of "col" and "val" entries in the URL. Don't worry about the val so much except to test. In my example, I am hoping to pass four prompts into my target: GL Period and 3 segments of our chart of accounts. So my modified URL now looks like this:

https://yourservername.com/analytics/saw.dll?Go&path=%2Fshared%2FGeneral%20Ledger%2FJournal%20Entries%20Report&Action=Navigate&P0=1&col1="- Nx_GLG0_Journal_Entry_Lines (General Ledger Views)"."Period Name"&val1=NOV-12&col2="- Nx_GLG0_Journal_Entry_Lines (General Ledger Views)"."Acct$SV$Harvard Tub"&val2=610&col3="- Nx_GLG0_Journal_Entry_Lines (General Ledger Views)"."Acct$SV$Harvard Org"&val3=56256&col4="- Nx_GLG0_Journal_Entry_Lines (General Ledger Views)"."Acct$SV$Harvard Object"&val4=6640

Test the URL by pasting it directly into your brower before proceeding to the next step...
Okay, so now it is time to integrate. Edit the "master" query and visit the Column Properties / Interaction tab on whichever column you want to add the action to. In this case, the Net Period column is the winner. Change the Primary Interaction to "Action Links" and then add a new action of type "Navigate to a Web Page" (counter-intuitive when "Navigate to BI Content" is right there...) Insert your big URL and go ahead to define parameters.


OBIEE does something annoying here, assuming that "Go" should take an argument. You need to edit the URL to remove the =@{1} which will result in an error later that you can ignore.


Next, scroll down and map the "val" placeholders to columns in the master view. TIP: you may need to include additional columns in your Answers query and make them hidden if you use them in your Filters but do not intend to display them. Only columns that are part of the query are available to the URL prompts.




When you're done, you'll get an message (above) because you deleted that false prompt for the "Go" itself. Ignore the error and move on. Proceed with options such as "Do not display in a popup if only one action is available at runtime" or any other options on the interaction.

Below is how this looks in action... First, the "master" query has a blue link wherever the action has been enabled.

Click a link to see the details... Like magic.

So there you go... A piece of cake, right?

posted by JasonShaffner @ 11:10 PM Add a comment!

3 Comments:

At July 7, 2013 at 4:39 PM, Blogger Gareth said...

Hi Jason - perfect post, great detail. I got solution for the issue I had - the go=x parameter value. Thanks,
Gareth

 
At March 7, 2014 at 2:00 PM, Anonymous Anonymous said...

Can we parametrize the
https://yourservername.com/analytics

part so that it picks the corresponding environments url and we don't need to change this to match the environment ?

 
At May 30, 2014 at 2:16 PM, Blogger Unknown said...

Hello!
Very good information.
One question: How do I "find the extended name of the columns"?
Thank you.

 

<< Home