Wednesday 23 December 2015

Bursting Using OBIEE Agents Part 2: The Empire Bursts Back

Did you like the topical Star Wars reference? Have you seen the new film yet? Of course not, you've got bursting to do.

In my previous post Bursting Using OBIEE Agents Part1 we looked at how our dashboard was filtered by region using a session variable and we saw how an OBIEE agent can run the dashboard and save it to the file system. The format I chose was .PDF but there are other formats you can choose.


An analysis can be exported in all the above formats but a dashboard page is restricted to PDF, Excel and HTML.

In this post we shall:

  • Create a new analysis which will define which regions we want to burst.
  • Create a new agent which will save our regions as a list in a text file.
  • Use the SaSchinvoke command line utility to run the agents from the command line.

So, to recap here is my dashboard:


Notice the regions in our prompt....I want to burst my report out across my regions.

Bursting Definition

We shall now create a new analysis which will list out the regions to drive the bursting.

I don't care about the formatting, I just want my analysis to list the regions:


These are the droids regions we're looking for. Just filter your analysis to match your desired burst output.

We shall now create an agent which will export the region definition to a text file. The bursting agent has exactly the same configuration as the dashboard agent we created in the previous post with the following exceptions:

Delivery Content

Here we define our bursting definition analysis:



Agent Action

Here we define the file format of the exported bursting definition (plain text):


Run the agent and check that it is exporting our bursting definition to our Agent_Temp folder:



and review the contents:


Yep, those are the regions I want to burst out.

Saschinvoke

Saschinvoke is the Oracle BI Scheduler command line job invocation tool. On Windows it is called saschinvoke.exe and on *NIX it is saschinvoke. It enables you to schedule a job via the command line. It is typically used to launch jobs outside of the OBIEE scheduler using a 3rd party scheduler or batch routine. Saschinvoke isn't anything new and it's been covered in other blogs but it is key to our process so I'll explain how it works.

We shall use sachinvoke to launch our agent via a command script.

The utility can be found in OBIEE_HOME\bi\bifoundation\server\bin
so in my case it is located in: E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin.

Here is a link to the command in the Oracle Documentation.

The syntax for the command is:

SASchInvoke -u <Admin Name>  (-j <job id> | -i <iBot path>)  [-m <machine name>[:<port>]]  [(-r <replace parameter filename> | -a <append parameter filename>)]

We will only need the following parameters to launch our agent:
-u: User name
-i: iBot path (the path to our agent)
-m: machine:port

The syntax is as follows:

saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m SERVERNAME:9511

You will be prompted for the password for your BI administrator user:


I've highlighted the machine:port parameter because this isn't needed in 11g. Oracle haven't taken into account the change in port numbers in 12c and the saschinvoke utility will fail without the machine:port reference because it still defaults to the old 9705 port.

So now we can use the command line to launch an agent which will write our dashboard and bursting definition to the file system. This is an important step because we can use the power of scripting to read our busting definition file and burst the dashboard for each region.

In part 3, the final part of the series, we will use batch scripting to launch our agents and burst out the dashboard for each region in the burst definition file.

Have a great Christmas break!

Tuesday 15 December 2015

Bursting using OBIEE Agents: Part 1

The next few posts will be about how we can burst OBIEE reports using OBIEE agents.

Edit 06/01/2016: This bursting solution relies on invoking agent server scripts which is only supported on Windows servers.

Oracle's definition of bursting is:
"Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered."

One example could be that you have a Sales dashboard filtered by region and you would like to burst this report out for all regions and save each dashboard to a file share for consumption elsewhere.

The Problem

BI Publisher is the tool of choice for bursting in OBIEE. Unfortunately it's also it's the only choice.

Whilst BI Publisher is great for building pixel perfect reports and bursting, it's not the most intuitive of tools and lacks a lot of the features and functionality you get with Answers and the typical dashboards we create in OBIEE. It also requires that you model your data outside of the RPD (even if the RPD is the source).

I've encountered several customers who had invested time and energy in creating great dashboards in OBIEE only to find out that they would need to try and recreate their existing dashboards in Publisher to achieve any form of bursting. The result is inconsistencies in look and feel, double the number of reports and double the amount of report maintenance. No Thanks.

A quick Google shows that many people are in the same position so is there an alternative which will let you burst your existing dashboards?

The Solution

I'll take inspiration from watching Nigella and in true festive style I'm going to give you the yuletide recipe on how to burst OBIEE dashboards using OBIEE agents.

First of all, the ingredients:

  • One dashboard, available in all good OBIEE shops.
  • One Answers report which we'll use as our bursting definition.
  • Two agents.
  • One session variable.
  • One large slice of Saschinvoke.
  • A sprinkling of scripts (a pinch of jscript and your scripting language of choice).


Now, I want to say right from the beginning  that this is not an elegant solution. It is however a very simple and effective way of bursting existing OBIEE dashboards with little or no changes to your existing dashboards. I'll let you decide if it's the golden goose or a mushy brussel sprout.

So, here is my dashboard:



It uses Cameron Lackpour's favourite database -  Sample Basic. This is an Essbase database but the data source is irrelevant.

My Region dashboard prompt top left filters all the components of the dashboard by region. The default region for each user is set by a session variable read from a table when the user logs in. All standard stuff.

Session Variable:



When a user logs in a session variable called DEFAULT_REGION is set. This can then drive the default content in our dashboards.

Dashboard Prompt:

Our dashboard prompt uses the variable to set the default region in our drop down.

User Defaults Table:

This is the table which defines the default region for our users. I've created a specific user called "burster" which will be used during the bursting process.

Creating the Agent:

Next we will create an agent to run the report and save the output to the file system. There isn't an option in the agent GUI to save to the file system so our agent will invoke some javascript to write the file to the file system. This process has been documented already in other blogs but I'll add it here for completeness. Here are the agent properties:

General Tab:


It's important that we specify the correct user here. I've specified our "burster" user which we will use specifically for the bursting process.

Schedule Tab:



 It doesn't matter what we add here, we won't actually use the agent for scheduling.

Condition Tab:


We're not using a condition in this example.

Delivery Content Tab:


Here we just need to define the dashboard which we want to burst.

Recipients Tab:

I've set the bi_admin user to receive the agent info.

Destination Tab:

Again, we don't need to define anything here as our destination is the entire dashboard on the file system.

Actions Tab:

Here we define the javascript we want to invoke as part of the agent. This will run the dashboard and then save the contents to the file system.

Select "Invoke Server Script":


Define which script we will invoke:

We define two parameters, the file type (PDF, Excel....) and the file name of the dashboard. In this example our dashboard will be saved to the file system in PDF format and will be called Sales_Dashboard.pdf.

I've saved the agent as Sales_Burst_Agent.


The script referenced in the agent contains the following:
/////////////////////////////////////////////////////////////
//
// save_to_file.js
// 
// Copies the result of an agent to the file system
// 
// Parameter(0) = Agent Result File Path
// Parameter(1) = Last Part of Output File Name (no path)
//
/////////////////////////////////////////////////////////////
var FSO = new ActiveXObject("Scripting.FileSystemObject");
var fileName = "E:\\OBIEE_BURSTING\\Agent_Temp\\" + Parameter(1);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);


The script above is almost a carbon copy of the simple example which can be found in the OBIEE documentation.

Folder Structure:

I've highlighted the location of where our report will be written to.

  • The Agent_Output folder will be the final location for our bursted reports.
  • The Agent_Scripts folder contains the scripts which will drive the bursting.
  • The Agent_Temp folder is place where the reports will temporarily reside before being modified and copied into the Agent_Output folder.




Bug in OBIEE 12c or my dodgy install?

If you're using OBIEE 11g you should be able to run the agent now and see the PDF output in the Agent_Temp folder. I'm using version 12c and got the following error when I ran the agent:

[nQSError: 66006] Failed to make Oracle BI Script Engine library available in the script.

I had to register the nqActiveXHost64.dll library manually for the agent server script to work:



Once I had registered the nqActiveXHost64.dll my agent ran successfully:


I'm not sure if it's just my install that has this issue or whether it's a bug but at least this fixes it.

The Result

Here we can see my dashboard has been successfully written to our Agent_Temp folder:



And here it is in PDF format:



OK, that's enough for now. Come back for part 2 where we shall define our bursting definition and burst out this report.

Thanks for reading!

Monday 7 December 2015

EPM 11.1.2.4 Financial Reporting error: com/hyperion/ap/hsp/HspAdmDriver

I was working on a new install of 11.1.2.4 and everything seemed to be functioning correctly until it came to testing a Financial Reporting report which used the Planning connector. The report would fail with the following error message:

com/hyperion/ap/hsp/HspAdmDriver

I hadn't received any errors during the configuration and I'd seen this type of connection work time and again so what could have gone wrong?

The issue was down to regional settings. The last time I'd seen issues like this was in Essbase 5.0.2 where installing on a French OS caused Essbase to break. It didn't do much for Franco-US relations at the time :)

My server had Danish regional settings but the issue applies to most non-English settings as indicated on OTN: https://community.oracle.com/thread/3735578

My fix as I mention on the forum was:

1- Make sure that your Windows EPM services use a service account and not the local system account.
2- Set the following regional options for the service account:
Display Language: English US
Input Language: English US
Format: English US
Location: Engish US

I'm not sure whether you need to set them all as English but it worked for me.

Tipiak on the forum fixed his issue by setting the Planning preferences time format to "auto detect" so try this as well.

OBIEE 12c: Oracle Business Intelligence has stopped working

Ok, lets get this blog started then...

I've been playing with a new install of OBIEE 12c on Windows 2012 and I couldn't get the bi server to start correctly. I kept on getting the following error:



Error: oracle business intelligence has stopped working
Problem signature:
  Problem Event Name: BEX64
  Application Name: sawserver.exe
  Application Version: 12.2.1.0
  Application Timestamp: 5616f0d2
  Fault Module Name: yod.dll
  Fault Module Version: 0.0.0.0
  Fault Module Timestamp: 50ff431d
  Exception Offset: 000000000001e940
  Exception Code: c0000005
  Exception Data: 0000000000000008
  OS Version: 6.3.9600.2.0.0.272.7
  Locale ID: 2057
  Additional Information 1: d406
  Additional Information 2: d406e47b17f73b5fc03376291226207b
  Additional Information 3: 7458
  Additional Information 4: 74581c46e97a8bc42cd289645d01850b

and this error:


Error: nqsserver.exe has stopped working
Problem signature:
  Problem Event Name: BEX64
  Application Name: nqsserver.exe
  Application Version: 0.0.0.0
  Application Timestamp: 561bb059
  Fault Module Name: yod.dll
  Fault Module Version: 0.0.0.0
  Fault Module Timestamp: 50ff431d
  Exception Offset: 000000000001e940
  Exception Code: c0000005
  Exception Data: 0000000000000008
  OS Version: 6.3.9600.2.0.0.272.7
  Locale ID: 2057
  Additional Information 1: d92a
  Additional Information 2: d92afc5ed82d4e5d1dbbd90e1ee4ae90
  Additional Information 3: 0ed0
  Additional Information 4: 0ed072c3fcb2518b6476270391ad9386

It turns out the issue was due to OBIEE picking up the wrong version of the yod.dll library.

What made my environment different from others?

My server also had the full EPM 11.1.2.4 stack installed,  The EPM instance contains several different versions of the yod.dll and my OBIEE biserver was picking up the wrong version of yod.dll because of the Windows path entries added by the EPM installer.

To fix the issue I created a batch script which sets only the Windows related paths and then calls the OBIEE start.cmd:

set PATH=C:\ProgramData\Oracle\Java\javapath;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
call E:\Oracle\OBIEE\Oracle_Home\user_projects\domains\bi\bitools\bin\start.cmd

After this OBIEE started without any issues.