PROBLEM STATEMENT :
Few months ago i was able to extract xml data from OBIEE and download it in a shared drive for further use. However the query which i am using was a simple query that extract few rows and not a complex queries i.e queries that does not take much time(say 2-3 min) to execute. Very ofter i got an error message "Query Time Out" at the calling portion of the web application. I have posted few queries on OBIEE blog and did try to take help from the experts but did not get the right solution what i am looking for.
I am sure that this post will help for those friends who are trying to integrate with web(.net) or develop a system for extracting or displaying data out of OBIEE by using queries that is taking long time to execute.
SOLUTION STATEMENT :
Asyncronous Call ! Now i am going to show how we can call OBIEE executeXMLQueryAsync Method of XMLviewservice. Dot Net provides us the functionality to implement Asyncronous Call i.e waiting long running task to complete without interrupting the main thread. When the task got completed the event handler automatically transfer the control to an address where we actually need to download or extract the information received from OBIEE.
Step 1 - Add OBIEE web services as a web reference using asp.net
Step 2 - Add a Class file into the project & Paste the following Source Code
Imports saw = OBIDownloader.obieesms ' OBIDownloader is the name of the project & "obieesms" web reference name.
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Threading
Imports System.Data
Imports System.net
Imports System.Web
Imports System.Web.Services
Public Class obiee
Dim xmlviewservice As New saw.XmlViewService ' Creating xmlviewservice object for accessing executeXML method
'Below declaration are important parameters for executeXML method
Dim repref As New saw.ReportRef()
Dim reportparams As New saw.ReportParams
Dim xmlQueryExecutionOption As New saw.XMLQueryExecutionOptions
Dim QueryResult As New saw.QueryResults
'Declaring SessionID variable
Public sessionid As String
'Other variable declaration
Public qid As String
Dim rstring As String = ""
Private repPath As String
Public repId As String
Public ds As New DataSet
'XML and XSD export Path, Please create a temp folder in C drive.
Dim tempPath As String = "C:\temp\"
Dim App_Path As String = tempPath
Private xmlPath As String = tempPath
Private xsdPath As String = tempPath
Public exportPath As String = tempPath
Private colCount As Integer
'*************************************************************************
'Below procedure call executeXMLquery by supplying "reportPath". Note the report path need to be copy from OBIEE.
'*************************************************************************
Public Sub ExecuteQueryPath(ByVal reportPath As String)
repPath = reportPath
repref.reportPath = repPath
Try
xmlQueryExecutionOption.async = True
xmlQueryExecutionOption.maxRowsPerPage = 500
xmlQueryExecutionOption.refresh = True
xmlQueryExecutionOption.presentationInfo = True
'Declaring Asyncronous Event Handler, Transfer of control will redirect to "Extracting" procedure upon completion of the event.
AddHandler xmlviewservice.executeXMLQueryCompleted, AddressOf Me.Extracting
'Retriving schema file i.e xsd containing datatypes and columns
QueryResult = xmlviewservice.executeXMLQuery(repref, saw.XMLQueryOutputFormat.SAWRowsetSchema, xmlQueryExecutionOption, reportparams, sessionid)
'Saving schema file
CreateXSD(QueryResult.rowset)
'Asyncronous executeXML query method for retriving xml data file using sessionid
xmlviewservice.executeXMLQueryAsync(repref, saw.XMLQueryOutputFormat.SAWRowsetData, xmlQueryExecutionOption, reportparams, sessionid)
Catch ex As Exception
'dobj.UpdateStatus(repId, "N", ex.Message & Chr(10) & "Module ExecuteQueryPath")
End Try
End Sub
'*************************************************************************
'Below procedure is call back when executeXMLquery is completed
'*************************************************************************
Sub Extracting(ByVal sender As Object, ByVal e As saw.executeXMLQueryCompletedEventArgs)
Try
QueryResult = e.Result
qid = QueryResult.queryID
Dim flag As Integer = 0
Dim nstring As String = ""
If Trim(QueryResult.rowset) <> "" Then
rstring = "<?xml version='1.0' ?>" & Chr(10) & QueryResult.rowset
rstring = Replace(rstring, "</rowset>", "")
nstring = rstring
flag = 1
Else
nstring = ""
flag = 0
End If
Dim count As Integer = 2
Do While Not QueryResult.finished
count = count + 1
QueryResult = xmlviewservice.fetchNext(qid, sessionid)
qid = QueryResult.queryID
If flag = 0 And Trim(QueryResult.rowset) <> "" Then
rstring = "<?xml version='1.0' ?>" & Chr(10) & QueryResult.rowset
rstring = Replace(rstring, "</rowset>", "")
flag = 1
Else
rstring = QueryResult.rowset
rstring = Replace(rstring, "<rowset xmlns=""urn:schemas-microsoft-com:xml-analysis:rowset"" >", "")
rstring = Replace(rstring, "</rowset>", "")
End If
nstring = nstring & rstring
Loop
nstring = nstring & "</rowset>"
'Saving XML data file
CreateXML(nstring)
Dim s As New saw.SAWSessionService
s.logoff(sessionid)
s.Dispose()
Catch ex As Exception
'dobj.UpdateStatus(repId, "N", ex.Message & Chr(10) & "Module Extracting")
End Try
End Sub
'*************************************************************************
Public Sub CreateXSD(ByVal strs As String)
rstring = "<?xml version='1.0' ?>" & Chr(10) & strs
rstring = Replace(rstring, "<rowset xmlns=""urn:schemas-microsoft-com:xml-analysis:rowset"" >", "")
rstring = Replace(rstring, "</rowset>", "")
Dim Apath As String = xsdPath
Apath = Apath & repId & ".xsd"
Try
Dim reader As New StringReader(rstring)
Dim myschema As XmlSchema = XmlSchema.Read(reader, AddressOf ValidationCallback)
Dim file As FileStream = New FileStream(Apath, FileMode.Create, FileAccess.ReadWrite)
Dim xwriter As XmlTextWriter = New XmlTextWriter(file, New UTF8Encoding())
xwriter.Formatting = Formatting.Indented
myschema.Write(xwriter)
xwriter.Close()
reader.Close()
Catch e As Exception
'dobj.UpdateStatus(repId, "N", e.Message & Chr(10) & "Module CreateXSD")
End Try
End Sub
'*************************************************************************
Public Sub CreateXML(ByVal nstring As String)
Dim Apath As String = xmlPath
Apath = Apath & repId & ".xml"
Try
Dim TextFile As New StreamWriter(Apath, False, System.Text.Encoding.Unicode)
TextFile.WriteLine(nstring)
TextFile.Close()
Catch e As Exception
'dobj.UpdateStatus(repId, "N", e.Message & Chr(10) & "Module CreateXML")
End Try
End Sub
'*************************************************************************
Public Sub LogOFF(ByVal saw As saw.SAWSessionService) saw.logoff(sessionid)
End Sub
'*************************************************************************
Public Sub New()
Dim saw As New obieesms.SAWSessionService
sessionid = saw.logon("UserID", "Password")
End Sub
End Class
Step 3 - Using the obiee.vb class created above. Add a Server side button and paste the following code.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim obj As New obiee
obj.repid = "reportname"
obj.ExecuteQueryPath("/userid/foldername/reportname")
End Sub
Step 4 - Debug and run the project. You will be receiving an XML file and XSD file in "C:\temp\" folder.
Good Luck !
RSingh
