Friday, January 11, 2013

OBIEE 11g – Export To The File System


Here’s a nice script I wrote to enable the exporting of content from OBIEE to the Windows file system. Useful if you don’t have enough licences for everyone to consume reports/dashboards within the OBIEE application or if you want to build an archive over time.
The principle is quite simple, an Agent is set up in Oracle Delivers and the Delivery Content specified as normal. In this case a whole dashboard page is going to be exported as a PDF.


On the Actions tab we add a new action to Invoke a Server Script.


Click the Edit Parameters (pencil) icon to change the properties. Set the language to VBScript and set the Script Path to be wherever the export script (downloaded from the bottom of this post) has been saved on the OBIEE server.
Finally we must specify 3 parameters for the export script to use: the file to be exported, the desired file and folder names. See the screenshot:


Now each time the Agent is executed it will create a subdirectory (with yyyy-mm-dd format) and within that directory the file will be saved.


Here you can download the VBScript that does the hard work. Neat eh?

VB Script 

'#####=========================================================================
'## Title: Export Report
'## Rev: 1.0
'## Author: Paul McGarrick
'## Company: Total Business Intelligence / http://total-bi.com
'##       
'## Purpose:
'##        1. This script takes a file from OBIEE and saves to the file system
'##        2. Creates a reporting subdirectory if not already present
'##        3. Creates a further subdirectory with name based on current date
'##                    
'## Inputs (specified in Actions tab of OBIEE Delivers Agent):
'##        1. Parameter(0) - This actual file to be exported
'##        2. Parameter(1) - The filename specified within OBIEE
'##        3. Parameter(2) - Report sub directory name specified within OBIEE
'##
'#####=========================================================================

Dim sBasePath
sBasePath = "S:\reports\reports"

Dim sMasterPath
sMasterPath = sBasePath & "\" & Parameter(2)

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'check whether master directory exists, if not create
Dim objMasterDir
If Not objFSO.FolderExists(sMasterPath) Then
 Set objMasterDir = objFSO.CreateFolder(sMasterPath)
End If
Set objMasterDir = Nothing

'build string to get date in yyyy-mm-dd format
Dim sDate, sDateFull
sDate = Now
sDateFull = DatePart("yyyy", sDate) & "-"
If Len(DatePart("m", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("m", sDate) & "-"
If Len(DatePart("d", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("d", sDate)

Dim sDir
sDir = sMasterPath & "\" & sDateFull

Dim objDir
If Not objFSO.FolderExists(sDir) Then
 Set objDir = objFSO.CreateFolder(sDir)
End If
Set objDir = Nothing

Dim sFileName
sFileName = sDir & "\" & Parameter(1)

Dim objFile
objFSO.CopyFile Parameter(0), sFileName, True

Set objFile = Nothing

Set objFSO = Nothing