Back again for the final part in the series looking at the Essbase Web Services and today I will focus on the operations that are available in the Query module which covers both metadata and data.
If you are have not done already I would recommend reading through the previous three parts.
Just like in the previous posts the examples I am going to be using Boomerang which is a great free SOAP and REST client available for Chrome, if you don’t use Chrome then there are plenty of other free clients available, I will also provide a few example scripts in Powershell to try and demonstrate that is quite simple to work with the Web Services.
The WSDL URL for the Query service is:
http(s)://:/essbase-webservices/QueryService?wsdl
Once the WSDL URL has been added to the client the following operations will be returned:
I will start with the metadata operations which are queryMemberHeader, queryMember and queryMemberReport.
queryMemberHeader and queryMember are pretty much the same except the header only returns member names and no member information, most of the time it would be much practical to use queryMember.
The documentation provides the following information on the operation.
queryMemberHeader
headeronly – This does not make any difference what this is set to and can be excluded from the request.
memberCount – The number of members to return in the response, setting to 0 will return all the members based on the query options.
memberName – Nice and easy as it is the member name you want to be base the query on.
queryOption – The following are options are available for the query.
queryType – The options available for the query type are
startMemberIndex – this defines which member in the results to start from, so say the query would return east,west,south,central and the member index was set to 2 then only south and central will be in the response.
So back to my request which I displayed above the query should return the children of member "East".
To convert this into a script I used the same basis in previous examples and started out with an XML template file for the SOAP request which I load and populate the required values.
So it is pretty easy to create your own query by only changing the member name and query type and then process the results however you like.
Right on to an example using input string which is only used for certain query types and one of them being QUERY_TYPE_WILDSEARCH
If I wanted to search for all the members starting with N then input string is used to define this.
As input string is being populated the member name is not required as an input.
As expected the response returns all the member names starting with N.
Moving on to the queryMember operation which is exactly the same as queryMemberHeader except it returns more information about the member.
The documentation offers the following information:
queryMember
The above request will return the level0 members of member 100.
As you can see this operation returns much detailed information about each member that is returned from the query.
Once again changing the query option between member and alias makes no difference as if a member has an alias it will be returned in the response.
For a script example I used actually the same as with the queryMemberHeader script and just changed the operation to queryMember.
The above script returns all the level0 members below the Sales member, as the Sales member is a level0 member then only that member is returned.
Another example shows how easy it is to pick which properties you want to output.
This could be useful if you want to extract certain member properties or create a formatted output to feed into other systems or cloud instances like PBCS.
On to the final metadata operation which is queryMemberReport, the documentation provides the following information.
queryMemberReport
I like how the documentation states the options are part of fieldSelection and mbrSelection but does not provide any information on what they are.
After some searching I found what they are all about in the Javadoc for the Essbase Java API.
fieldSelection - The query string which defines the set of fields that will be returned for each member.
The syntax is "<OutputType Binary | Text <SelectMbrInfo ( FieldName {, FieldName}, ... )"
If you are have not done already I would recommend reading through the previous three parts.
- Part 1 – Introduction to Essbase Web Services, how to get up and running, pitfalls, bugs and examples.
- Part 2 – In-depth look at the Datasource service module and examples for each of the operations.
- Part 3 – Covers the Administration service module with examples of each of the operations.
Just like in the previous posts the examples I am going to be using Boomerang which is a great free SOAP and REST client available for Chrome, if you don’t use Chrome then there are plenty of other free clients available, I will also provide a few example scripts in Powershell to try and demonstrate that is quite simple to work with the Web Services.
The WSDL URL for the Query service is:
http(s)://
Once the WSDL URL has been added to the client the following operations will be returned:
I will start with the metadata operations which are queryMemberHeader, queryMember and queryMemberReport.
queryMemberHeader and queryMember are pretty much the same except the header only returns member names and no member information, most of the time it would be much practical to use queryMember.
The documentation provides the following information on the operation.
queryMemberHeader
- In: URI (Essbase cube), QueryMemberOptions
- Out: Essbase member array
headeronly – This does not make any difference what this is set to and can be excluded from the request.
memberCount – The number of members to return in the response, setting to 0 will return all the members based on the query options.
memberName – Nice and easy as it is the member name you want to be base the query on.
queryOption – The following are options are available for the query.
- QUERY_OPTION_MEMBERSONLY
- QUERY_OPTION_ALIASESONLY
- QUERY_OPTION_MEMBERSANDALIASES
- QUERY_OPTION_COUNTONLY
- QUERY_OPTION_NOTOTALCOUNTS
- QUERY_OPTION_INCLUDEHYBRIDANALYSIS
- QUERY_OPTION_EXCLUDEHYBRIDANALYSIS
- QUERY_OPTION_FORCECASESENSITIVE
- QUERY_OPTION_FORCEIGNORECASE
- QUERY_OPTION_UNIQUENAME
- QUERY_OPTION_USESUBSTITUTIONVAR
queryType – The options available for the query type are
- QUERY_TYPE_CHILDREN
- QUERY_TYPE_DESCENDANTS
- QUERY_TYPE_BOTTOMLEVEL
- QUERY_TYPE_SIBLINGS
- QUERY_TYPE_SAMELEVEL
- QUERY_TYPE_SAMEGENERATION
- QUERY_TYPE_PARENT
- QUERY_TYPE_DIMENSION
- QUERY_TYPE_NAMEDGENERATION
- QUERY_TYPE_NAMEDLEVEL
- QUERY_TYPE_SEARCH
- QUERY_TYPE_WILDSEARCH
- QUERY_TYPE_USERATTRIBUTE
- QUERY_TYPE_ANCESTORS
- QUERY_TYPE_DTSMEMBERS
- QUERY_TYPE_DIMUSERATTRIBUTES
- QUERY_TYPE_INDEPDIMS
- QUERY_TYPE_INDEPDIMS_DISCRETE
- QUERY_TYPE_INDEPDIMS_CONTINUOUS
startMemberIndex – this defines which member in the results to start from, so say the query would return east,west,south,central and the member index was set to 2 then only south and central will be in the response.
So back to my request which I displayed above the query should return the children of member "East".
To convert this into a script I used the same basis in previous examples and started out with an XML template file for the SOAP request which I load and populate the required values.
So it is pretty easy to create your own query by only changing the member name and query type and then process the results however you like.
Right on to an example using input string which is only used for certain query types and one of them being QUERY_TYPE_WILDSEARCH
If I wanted to search for all the members starting with N then input string is used to define this.
As input string is being populated the member name is not required as an input.
As expected the response returns all the member names starting with N.
Moving on to the queryMember operation which is exactly the same as queryMemberHeader except it returns more information about the member.
The documentation offers the following information:
queryMember
- In: URI (Essbase cube), QueryMemberOptions
- Out: Essbase member array
The above request will return the level0 members of member 100.
As you can see this operation returns much detailed information about each member that is returned from the query.
Once again changing the query option between member and alias makes no difference as if a member has an alias it will be returned in the response.
For a script example I used actually the same as with the queryMemberHeader script and just changed the operation to queryMember.
The above script returns all the level0 members below the Sales member, as the Sales member is a level0 member then only that member is returned.
Another example shows how easy it is to pick which properties you want to output.
This could be useful if you want to extract certain member properties or create a formatted output to feed into other systems or cloud instances like PBCS.
On to the final metadata operation which is queryMemberReport, the documentation provides the following information.
queryMemberReport
- In: URI (Essbase cube), QueryReportOptions
- Out: Essbase member array
I like how the documentation states the options are part of fieldSelection and mbrSelection but does not provide any information on what they are.
After some searching I found what they are all about in the Javadoc for the Essbase Java API.
fieldSelection - The query string which defines the set of fields that will be returned for each member.
The syntax is "<OutputType Binary | Text <SelectMbrInfo ( FieldName {, FieldName}, ... )"
I am not going to provide the full list of FieldName values but if you want to find out what they are have a look here.
mbrSelection - The query string which defines the set of members to be returned. The syntax of this query string is the syntax for member selection; that is, the query string can be anything that you can use in a FIX() statement.
The format of request is again very similar but this time includes fieldSelection and mbrSelection.
In the above request I have changed < in the field selection to the html code equivalent of & lt; otherwise the XML would not be valid and the request will fail.
Also the field selection can be a little misleading as if I had only included MemberName it will still return most of the properties like the previous operation but just not all of them.
Basically the query is requesting to return all the children of member Ratios and include the formula in the response.
The member count threw me a little as if you set it to zero it will return nothing, setting to -1 seems to bring back all members defined in the query.
I have displayed a cut down version of all the retuned properties but highlighted formula, if I did not include MemberFormula in the field selection of the request the formula would not be returned.
The equivalent script did not need many changes from the previous script example.
Another slight change to the script and I can only output only the members in the measures dimension which have a formula attached.
Which metadata operation you choose to work with I suppose depends on what you are trying to achieve, each of them have their own merits.
It does highlight that you can have your own version of an outline extractor without the requirement of any clients or third party tools.
Moving on to the data operations and I will start with “execute” which is the one I feel provides the biggest benefit out of them all.
The documentation provides the following information.
execute
- Input:
- Cube: URI (Essbase cube)
- Text: MDX query statement
- Options: MDX query options
- Output: Either an MDX MDData result set or a grid based on resultFormat specified as part of input Options
Let us first take a simple MDX query using Sample Basic to return the children of margin as the rows and children of East as the columns.
To convert this into the request required for the execute operation it would be something like the following format.
The MDX query is put into the text element, there are a number of different options available and in this example the result format is being set as MDData.
The MDData result set looks something like:
I have hidden the Slicer section so the response is not too big, in this example the Slicer just contains the static dimensions not included in the query
If you look at the MDX query that was run in EAS and the XML response above you should be able to understand how the result set operates.
To give you an idea of what is returned in the Slicer I have updated the MDX query to include a where clause.
The Slicer section then looks like this:
So once you have the format of the input correct all you need to change is the MDX query and data will be returned.
For processing the data I came up with a script that takes the XML response and extract all the dimension, member names and data values and then stores them in an object ready to output in a desired format.
In the example above I have not included the function to store the data values as you are probably bored enough by now :)
The example is in table format but it can easily be changed to csv format.
On to the Grid result set which just requires an update to the result format to set it from MDData to Grid.
The response is the XML equivalent of building a grid request in Smart View.
The output has information on the number of rows returned and columns, I have hidden some of the output to try and make it look manageable on screen.
The output includes properties for each of the cells which might not be of any interest but it is there if you need to have that level of detail.
The blank cells are also included in the output, the equivalent grid in Excel would look like:
Which result set to use really depends on the output requirements though both can be logically processed to produce the same results.
I only going to cover two more operations as I believe that it is reaching a point where it would be worth considering the Java API due to the complexity and the value it adds.
So let use quickly look at the Retrieve operation and the documentation provides the following:
Retrieve
- Input:
- URI: (Essbase cube)
- Options: Grid operation parameters
- Grid: Input grid
- Output: Output grid
It is possible to not provide a grid and perform a top level retrieve using the following request format.
The results produce a grid which would resemble.
I am not going to display the output grid as it is the same format as with the execute operation.
To retrieve a defined grid the input would need to contain the row and column count, all the cells including blank ones, the cell properties do not need to be included.
Let us take a basic grid and then convert that into what is required as input for retrieve operation.
The equivalent XML grid would be
Running the request would return the same grid but include the data and the cell properties.
Here is an example of the data cell for Sales.
Finally on to “UpdateData” which I am sure you know what is going to do, it follows the same principal as the retrieve except the grid needs to be populated with the data values.
For consistency the documentation states:
UpdateData
- Input:
- Cube: URI (Essbase cube)
- Options: Grid operation parameters
- Grid: Input grid
- Output: Output Grid
Executing the request will update the database with the value from the grid.
Just to prove it I ran a Smart View retrieve.
The remaining operations are pivot, zoomIn, zoomOut, keepOnly, removeOnly which if you have ever used Smart View or the excel addin you will understand what are they are all about, I am not going to cover them because I don’t see them being used and you are entering into the realms of the Java API being better equipped for these types of tasks.
So that wraps up the Essbase Web Services, well done if you have made it this far :)
My final thoughts are that as usual Oracle have come up with a tool that can be very useful but the execution has been poor, the documentation is off putting and the investment into fixing bugs and enhancing the Web Services has just not been there.
With the ongoing rise of Cloud offering I am sure Web Services will start to take more of a front seat in terms of integration and I know the upcoming Essbase cloud service will have a REST API which I am sure I will definitely prefer over a SOAP based one, whether this comes to on-premise or there is more investment in the Essbase Web Services we will have to wait and see.
It has definitely been quite a challenge to put all this together but at the same time it has been a good learning experience, I wanted to cover an area that has had practically no coverage even if it does not convince you to look into the Web Services any further.
