Wednesday 20 January 2016

EPM patch 11.1.2.2.500 Breaks EPMA: com.hyperion.awb.web.common.DimensionServiceException

EPMA can be frustrating at the best of times but this was the first time I've had an issue with patching. The issue only occurs with some environments, in this example the patch worked fine on DEV but broke the PROD environment.

The error occurs when applying the 11.1.2.2.500 super patch to EPM 11.1.2.2.300. The first time you start the EPMA services everything will work as normal. However, when the EPMA dimension service starts it executes some SQL on the database which means that the second time you start the EPMA dimension service it won't start correctly.

You'll get this error message in the EPM Workspace when selecting the EPMA Application or Dimension components:

Requested Service not found 

Code: 
com.hyperion.awb.web.common.DimensionServiceException 
Description: An error occurred processing the result from the server. 

A look at the DimensionServer.log reveals the following:

Failed 11.1.2.2.00 FixInvalidDynamicPropertyReferences task at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy) 
...
An error occurred during initialization of the Dimension Server Engine:  startIndex cannot be larger than length of string.
Parameter name: startIndex.    at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
   at Hyperion.DimensionServer.Global.Initialize(ISessionManager sessionMgr, Guid systemSessionID, String sqlConnectionString)

The same error can also occur when applying 11.1.2.3.500. Oracle released 11.1.2.3.501 to fix the issue, unfortunately they didn't release a patch for the 11.1.2.2 code line.

Thankfully there is a fix. Run the following SQL on your EPMA repository and your EPMA Dimension Server will start successfully again:

UPDATE DS_Property_Dimension 
SET c_property_value = null 
FROM DS_Property_Dimension pd 
JOIN DS_Library lib 
ON lib.i_library_id = pd.i_library_id 
JOIN DS_Member prop 
ON prop.i_library_id = pd.i_library_id 
AND prop.i_dimension_id = pd.i_prop_def_dimension_id 
AND prop.i_member_id = pd.i_prop_def_member_id 
JOIN DS_Dimension d 
ON d.i_library_id = pd.i_library_id 
AND d.i_dimension_id = pd.i_dimension_id 
WHERE 
prop.c_member_name = 'DynamicProperties' AND 
pd.c_property_value IS NOT NULL AND pd.c_property_value = ''; 



Sunday 3 January 2016

Bursting Using OBIEE Agents Part 3: The Return of the Burst

Happy New Year!

This bursting series has got legs...maybe I could follow the Star Wars model and do prequels, sequels and bursting franchises?

In the final part of the agent bursting series we will piece all of this together using a batch script.

Remember in Bursting using OBIEE Agents Part 1 our Sales dashboard was filtered by a default region session variable (defined in an SQL table)?

Here is the secret sauce: will use a batch script to modify the default region in the SQL table and then run the dashboard agent for each region.

Our script will do the following:
  • Launch the busting definition agent and write all our bursting regions to Region_Definition.txt.
  • Read the Region_Definition.txt file and for each line:
    • Create a temporary SQL script to update Default_Region SQL column for our "burster" user.
    • Run the dashboard agent as "burster" (so the dashboard is filtered on the Default_Region set in the previous step)
    • Rename Sales_Dashboard.pdf to Sales_Dashboard_<REGION>.pdf and copy to E:\OBIEE_BURSTING\Agent_Output
    • Delete the temporary Sales_Dashboard.pdf
    • Delete the temporary SQL script
    • Loop round to 2nd line of Region_Definition.txt and repeat.
Got that? Too messy for you? I did warn you that it wasn't elegant.

Here are the final ingredients:
  • Batch scripting language of your choice.
  • Oracle or SQL client in order to run sqlplus/sqlcmd from the command line.

In my example I am using Windows 2012 and SQL Server <gasp!> and my scripting language will use the windows command. Unlike me, I recommend that you step out of the 1990s and use a more up-to-date language!

The Batch Script


The first issue we have is that the SaSchinvoke.exe doesn't provide us with a flag to define a password so you need to echo the password and pipe the output to the SaSchinvoke.exe command. There are lots of blogs on this but Rittman Mead's was the earliest I could find so I'll quote it here. The link is relatively old now but still useful as you could obviously integrate this script to any 3rd party ETL/scheduler tool like ODI.

I'll break the script up for explanation purposes and then copy the full script at the end.

Run the Bursting Definition Agent to Create the Burst Definition File

Here is the first part of our script. At the top it sets a bunch of variables. It then runs our Region_Burst_Agent from the command line. Remember the agent writes our list of bursting regions to a text file.


@echo off
REM Set Bursting Variables

set SCRIPT_HOME=E:\OBIEE_BURSTING\Agent_Scripts
set BURST_DEFINITION=E:\OBIEE_BURSTING\Agent_Temp\Region_Definition.txt
set OBIEE_BATCH_HOME=E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin
set BURST_SOURCE=E:\OBIEE_BURSTING\Agent_Temp
set BURST_OUTPUT=E:\OBIEE_BURSTING\Agent_Output
echo ##################################################################
echo #### Create list of Regions to burst from                       ##
echo ##################################################################
echo Bursting started %date% %time% >> %SCRIPT_HOME%\bursting.log
REM Launch the Region_Burst_Definition agent to create the list of bursting
E:
cd %OBIEE_BATCH_HOME%
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak

Notice the timeout at the end of the script. I've added this as we need to wait for the bursting definition file to be written before we move to the next step. I advise you to add better logic here and poll for the file to be written before going to the next step.

Update our Default Region in SQL Table

Once our region definition file is created our batch will read the file and for each line it will update the default region in the SQL table for the "burster" user:

echo ##################################################################
echo #### Begin bursting. Dashboards are written to %BURST_OUTPUT%   ##
echo ##################################################################
REM Parse Region_Definition.txt file created by the Region_Burst_Definition agent.

REM Start For Loop
for /f "delims=" %%i in ('type %BURST_DEFINITION%') do (

REM Create temporary SQL statement to set default Region and execute
echo UPDATE OBIEE_Defaults SET Default_Region='%%i' WHERE User_Name='burster'; >> %SCRIPT_HOME%\update_region.sql
sqlcmd -S SERVER -d DATABASE -U USER -P PASSWORD -i "%SCRIPT_HOME%\update_region.sql"

Run the Dashboard Agent to Write Dashboard to PDF

With our default region set we can now call the Dashboard agent to write the dashboard to PDF. Notice again how I've put in a timeout to ensure that the dashboard is run and saved to PDF before we loop round to the next region in our Burst Definition file. I would recommend this polls for the file to be written before going to the next step.

REM Launch OBIEE Agent to create PDF
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Sales_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak


Rename the Dashboard with the Region Location. Copy to Output Folder

Here I rename the dashboard so it includes the location name. You can of course add a timestamp or anything else you want to with the name/location.

REM Rename file with Region name and move out BURST_OUTPUT folder
echo F|xcopy /Y %BURST_SOURCE%\Sales_Dashboard.pdf "%BURST_OUTPUT%\Sales_Dashboard_%%i.pdf"

REM Clean up temporary files
del /q %SCRIPT_HOME%\update_region.sql
del /q %BURST_SOURCE%\Sales_Dashboard.pdf

REM End For Loop
)

The last steps of this section clean up the temporary SQL script and temporary dashboard.pdf

Finally Delete the Region Definition File

Once all the busting has completed the last step is to delete the file that contains our bursting definition.

REM Delete Region list file
del %BURST_DEFINITION%
echo Bursting finished %date% %time% >> %SCRIPT_HOME%\bursting.log
echo ##################################################################
echo #### Bursting complete.                                         ##
echo ##################################################################

Complete Script

Here is the complete script. If you remove the commentary and variables there is only 14 lines of code.

@echo off
REM Set Bursting Variables

set SCRIPT_HOME=E:\OBIEE_BURSTING\Agent_Scripts
set BURST_DEFINITION=E:\OBIEE_BURSTING\Agent_Temp\Region_Definition.txt
set OBIEE_BATCH_HOME=E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin
set BURST_SOURCE=E:\OBIEE_BURSTING\Agent_Temp
set BURST_OUTPUT=E:\OBIEE_BURSTING\Agent_Output
echo ##################################################################
echo #### Create list of Regions to burst from                       ##
echo ##################################################################
echo Bursting started %date% %time% >> %SCRIPT_HOME%\bursting.log
REM Launch the Region_Burst_Definition agent to create the list of bursting
E:
cd %OBIEE_BATCH_HOME%
echo Hyp3r10n|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak
echo ##################################################################
echo #### Begin bursting. Dashboards are written to %BURST_OUTPUT%   ##
echo ##################################################################
REM Parse Region_Definition.txt file created by the Region_Burst_Definition agent.

REM Start For Loop
for /f "delims=" %%i in ('type %BURST_DEFINITION%') do (

REM Create temporary SQL statement to set default Region and execute
echo UPDATE OBIEE_Defaults SET Default_Region='%%i' WHERE User_Name='burster'; >> %SCRIPT_HOME%\update_region.sql
sqlcmd -S SERVER -d DATATBASE -U USERNAME -P PASSWORD -i "%SCRIPT_HOME%\update_region.sql"

REM Launch OBIEE Agent to create PDF
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Sales_Burst_Agent" -m epmdb:9511
timeout 10 /nobreak

REM Rename file with Region name and move out BURST_OUTPUT folder
echo F|xcopy /Y %BURST_SOURCE%\Sales_Dashboard.pdf "%BURST_OUTPUT%\Sales_Dashboard_%%i.pdf"

REM Clean up temporary files
del /q %SCRIPT_HOME%\update_region.sql
del /q %BURST_SOURCE%\Sales_Dashboard.pdf

REM End For Loop
)
REM Delete Region list file
del %BURST_DEFINITION%
echo Bursting finished %date% %time% >> %SCRIPT_HOME%\bursting.log
echo ##################################################################
echo #### Bursting complete.                                         ##
echo ##################################################################

The Result


And here is the result:


Our Sales dashboard has been saved as PDF to our output location and burst across all the regions we defined in our Burst Definition report.

Performance

Don't expect to burst out 1000 reports per minute; there is a delay when you launch an agent via Sachinvoke and you need to wait for the dashboard to be written before moving onto the next one. However you could use multiple users to run the process; using 2 user accounts could half the time.

Remember, the object of this exercise is to burst our existing OBIEE dashboards, not to replace BI-Publisher.

Conclusion

Am I happy with this? Yes and no.

This is more complicated than it should be, it is not as fast as normal bursting methods and I have to hard-code passwords in the script (although arguably if someone is able to sniff around on your server you've got bigger issues than password protection). You can also have issues if agents start overlapping each other.

However, you can only work with the tools at your disposal. This is easy to implement and extremely flexible with the output. You can add emailing to the loop, you could upload to Sharepoint...the options are endless.

You can even use this javascript action script in an agent to call our batch script on-demand from the OBIEE browser:

/////////////////////////////////////////////////////////////
//
// launch_burst.js
// 
// Launches a batch script on the server
//
/////////////////////////////////////////////////////////////
var WshShell = new ActiveXObject("WScript.Shell");

WshShell.Run("E:\\OBIEE_BURSTING\\Agent_Scripts\\Agent_Burst.bat");


So there we have it. A more robust version of this script (error trapping, file polling etc) is currently running in a Production environment bursting up to a thousand reports daily and the customer is very happy with the result.

Please feel free to post opinions, both good and bad. Maybe this will spark an idea for you to come up with a better idea? Share and let us let us know!