Exploring OBIEE Web Services through Python

January 29th, 2014 by

My very first blog post for Rittman Mead was on the subject of Web Services in BI Publisher 11g, and now I want to return to the subject here, looking at the Web Services of OBIEE itself.

Web Services are basically a way of enabling an application or service to offer up an API to as-yet-undefined-clients. This may be an API for fetching data, sending data, or invoking a service or action. In this age of The Cloud, systems being able to interact in this abstracted manner (i.e. without requiring one to be aware of the other at design time) is a Good Thing and something that if an application does not support is rather frowned upon.

For a simple explanation of Web Services, and details of how to explore and test them using SoapUI then refer to my article Web Services in BI Publisher 11g.

OBIEE itself has supported Web Services for a long time now, but they are exposed through the SOAP, a HTTP-based protocol that relies on carefully formed XML messages. This is fine, but had meant that to actually utilise them beyond tinkering within SoapUI you had to create a heavy-weight JDeveloper project or similar. One of the many great things about working at Rittman Mead is that as a company we use Macs, meaning that a unix-like command line, and Python, is just a click away. Recently I discovered that Python has a library called SUDS. This makes calling a web service as simple as:

from suds.client import Client
client = Client('http://obiee-server:9704/analytics-ws/saw.dll/wsdl/v7')
sessionid = client.service['SAWSessionService'].logon('weblogic','Password01')

With this code I have just logged into OBIEE and returned a session ID token that I can now use in subsequent web service calls. In the background, SUDS sorts out the forming of the XML SOAP messages to send to the web service, and the parsing of the returned XML SOAP message into a Python object matching the object. So, now I can actually start exploring and programming using the Web Services straight from my command line…nice.

The Web Services discussed below are all session based, meaning that you have to first authenticate and retrieve a session ID, which is then used for subsequent Web Service calls.

Checking a user’s Subject Area grants

What prompted me to try again with exploring OBIEE’s Web Services was my discussions with my colleagues over ways to Regression Test OBIEE, above and beyond what I have already covered here and here, where I use nqcmd and Catalog Manager to work with the Logical SQL for an analysis. What about security – how can we test what a user can and cannot see from the point of view of an RPD’s Presentation Layer permissions?

Once authenticated, we can call a Web Service which shows the Subject Areas that the user may see. The MetadataService and getSubjectAreas are both straight from the documentation, transposed into SUDS/Python syntax.

sa_list=client.service['MetadataService'].getSubjectAreas(sessionid)
print sa_list

This returns:

[(SASubjectArea){
   name = ""Sales - Fact Sales""
   displayName = "Sales - Fact Sales"
   description = None
 }, (SASubjectArea){
   name = ""Sales - Store Quality""
   displayName = "Sales - Store Quality"
   description = None
 }]

And now for each subject area, examine the available tables and columns within:

for sa in sa_list:
    print '\t%s'%(sa.name)
    sa_contents=client.service['MetadataService'].describeSubjectArea(sa.name,'IncludeTablesAndColumns',sessionid)
  for table in sa_contents.tables:
        print '\t\t%s' % (table.name)
        for col in table.columns:
            print '\t\t\t%s' % (col.name)

Which returns:

"Sales - Fact Sales"
        "Fact Sales"
                "Cost"  
                "Revenue"
                "Sale Amount" 
                "Sale Amount Month Ago"
                "Sale Amount Target" 
        "Dim Times"
                "Month YYYYMM" 
                "Month" 
                "Quarter"
                "Quarter YYYYQ"
[...]

Nice huh? And with a bit of Python scripting magic we could easily dump that out to disk for comparison again once an RPD change has been made.

Listing users

Listing out the available RPD objects for a user is nice, but how do we know which users there are? Not everyone’s going to be logging in as weblogic, right? (right?!). There is a SecurityService Web Service that offers a getAccounts method, but how do we call it? Unlike the above MetadataService.getSubjectAreas call, where we just passed in the session ID token, here we need to form a proper object describing the account query that we want to run. SUDS offers a way to do this, using an object ‘factory’:

accountlist = client.factory.create('Account')

From the interactive Python shell we can see what an “Account” object looks like:

>>> print accountlist
(Account){
   name = None
   accountType = None
   guid = None
   displayName = None
 }

And this matches the AccountStructure in the documentation (displayName doesn’t do anything in this method). We can set the object’s attributes to define the accounts we want to see – any account name (* is wildcard), and show users (accountType=0) only, not application roles:

accountlist.name = '*'
accountlist.accountType = 0

And now call the getAccounts method, storing the results in an object:

accounts=client.service['SecurityService'].getAccounts(accountlist,sessionid)

Dumping the object’s contents to the screen in the interactive shell shows:

>>> print accounts
[(Account){
   name = "BISystemUser"
   accountType = 0
   guid = "208001E0834C11E3AF35BD098D6B48E3"
   displayName = "BISystemUser"
 }, (Account){
   name = "OracleSystemUser"
   accountType = 0
   guid = "E33C7570834111E3BF03EF3A9BA6EB6D"
   displayName = "OracleSystemUser"
 }, (Account){
   name = "author_user"
   accountType = 0
   guid = "8FB2B570873F11E3BF851386414CD489"
   displayName = "author_user"
 }, (Account){
   name = "consumer_user"
   accountType = 0
   guid = "A38EEFF0873F11E3BF851386414CD489"
   displayName = "consumer_user"
 }, (Account){
   name = "weblogic"
   accountType = 0
   guid = "E33CC390834111E3BF03EF3A9BA6EB6D"
   displayName = "weblogic"
 }]

Iterating through user accounts

Now we have an object that gives us each user defined on the system. We can iterate through this object, and use Impersonation (which I have written about in detail here) to login as each user. Once logged in as the user, we could use the above example to iterate through the subject areas available to the user and record their actual RPD security privileges, and so on.

for account in accounts:
        print '\n----\n\n'
        this_acc = account.name
        try:   
                print 'Logging in as %s' % (this_acc)
                imp_client = Client('http://obiee-server:9704/analytics-ws/saw.dll/wsdl/v7')
                new_session = imp_client.service['SAWSessionService'].impersonate('weblogic','Password01',this_acc)
                print 'New session ID %s' % (new_session)
                try:   
                        user=imp_client.service['SAWSessionService'].getCurUser(new_session)
                        print 'Logged in as %s'%(user)
                        #
                        # Do cool stuff as impersonated user here
                except:
                        print 'failed to get user session'
        except:
                print 'failed to login as %s' % (this_acc)

NB to use impersonation you need to have the oracle.bi.server.impersonateUser Application Policy granted, which in a default 11.1.1.7 environment is not granted even to the BIAdministrators Application Role

SUDS logging

Use this little snippet to dump out the raw request/response XML SOAP messages to help debug issues you may^H^H^H will encounter:

import logging
logging.basicConfig(level=logging.INFO)
logging.getLogger('suds.client').setLevel(logging.DEBUG)

See the SUDS documentation for more detail.

Toolkit

Web Services, even through SUDS, cannot be blagged nor approached through the standard code-by-Google method. You have to be extremely precise with your syntax and object formations, which means that you will need:

  • Description of Services and Methods in Oracle BI EE Web Services
  • Description of Structures in Oracle BI EE Web Services
  • SoapUI – invaluable for testing methods, checking structures, and eliminating/identifying your code as the fault for any errors you hit. The free version does everything you need. Good tutorial on SOAP/WSDL here
  • The WSDL for OBIEE’s Web Services is WSDL describes all of the methods and structures so that the SOAP client can automatically generate the necessary XML messages. In this case, the SOAP client is SUDS. (writing that has made me only just now realise where the SUDS name come from…duh, McFly)

To install SUDS on a *nix platform with Python installed already (which it is by default on Macs, and most Linux distros), use Python’s easy_install program:

sudo easy_install suds

And off you go!

This is all very cool, but is it useful?

(Well, I think it’s cool, but then I laughed at this so go figure)

Being able to tap directly into core OBIEE functionality with a few lines of script, callable natively from any Linux OBIEE server (and any Windows service with Python installed) with minimal dependencies is very useful. It opens up much of OBIEE’s functionality as – in effect – a Python library. Whether it’s for building out some form of testing suite, automating web catalog management, auditing user privileges, or a tasks like a light-touch way to kick off an Agent (or create one from scratch), it’s another option handy to have up one’s sleeve.

Tags: , , , , , ,

Comments

  1. Seth Williams Says:

    Hi Robin, Thanks a lot for sharing this excellent tutorial. Looking forward to more interesting posts for you.

Website Design & Build: tymedia.co.uk