This is part of a series on exploring the SAP Analytics Cloud (SAC) data export API. When the series is complete, it will also be available as a single tutorial mission.

We’ve taken a tour of SAC’s Model Export API, began constructing our Python API wrapper and added support for extracting model metadata and master data from the tenant. Now we are going to get audit and fact data from the model.

Let’s knock audit data out first, as it is simple. We simply want to call the AuditData endpoint and return the value element of the response JSON. To form the URL for the AuditData endpoint, we concatenate the urlProviderRoot, the providerID and “AuditData”.

def getAuditData(self, providerID):
    urlAuditData = self.urlProviderRoot + "/" + providerID + "/AuditData"
    response = self.oauth.get(urlAuditData)
    responseJson = json.loads(response.text)
    return responseJson["value"]

When we extract fact data, we’re probably not going to want to take all the fact data in the model, every time. There may be times that we want to do this, but we won’t always want to do full data extractions; especially if we’re pulling data out to use in specialized analysis tools. That’s where the OData $filter parameter comes in. As mentioned in the first installment of the Python wrapper series, we’ll concentrate on simpler filtering cases and allow an out for hand crafted OData filters. We’ll want to handle the following:

We don’t want users to constantly have to write out the operators and we want them to be able to use the code completion of their favorite editors, so let’s create some classes to hold static values.

class FilterOperators(object):
    EQUAL = "eq"
    NOT_EQUAL = "ne"
    GREATER_THAN = "gt"
    LESS_THAN = "lt"
    GREATER_THAN_OR_EQUAL = "ge"
    LESS_THAN_OR_EQUAL = "le"


class StringFilters(object):
    CONTAINS = "contains"
    STARTS_WITH = "startswith"
    ENDS_WITH = "endswith"

class FilterLogicGateSymbols(object):
    LG_AND = "and"
    LG_OR = "or"
    LG_NOT = "not"

We’ll start by building out the accommodation for this in the SACConnection class. We’re going to want to add four new dictionaries, all of which have the providerID as key. They will hold:

self.paramManualOverride = {}
self.filterOrderBy = {}
self.filters = {}
self.filterLogicGates = {}
self.filterOperators = FilterOperators()
self.filterStringOperations = StringFilters()
self.logicGateOperators = FilterLogicGateSymbols()

When we acquire the metadata and master data of a new provider, we can already create new entries in these dicts. The $orderby preference should be a dict, as it will occur exactly 0..1 times per column. The logic gate preference should be a simple string value and the filers themselves will be a list. We can do this all in a single method, called addFilterProvider().

def addFilterProvider(self, providerID):
    self.paramManualOverride[providerID] = None
    self.filterOrderBy[providerID] = {}
    self.filterLogicGates[providerID] = self.logicGateOperators.LG_AND
    self.filters[providerID] = []

And we can call this addFilterProvider() method when acquiring the metadata. We can slip the call in as the last thing we do before returning out of the getModelMetadata() method.

self.addFilterProvider(providerID)

One of the filter options mentioned above is OData string/substring filters. For example, if we had a column named “Region” and want to filter for records where the value of this column starts with “Inter”, we’d use an OData $filter statement that looks like this:

$filter=startswith(Region,'Inter')

Let’s create a method that constructs these filters and adds them to self.filters[providerID]. We want to take the providerID, column name and filer value and put them into the format shown above. If the logic gate stored in self.filterLogicGates is “not”, then we’ll want to slip it in front in any case. Otherwise, we’ll want to slip the logic gate in only if there is already an existing filter.

def addStringFilter(self, providerID, columnName, filterValue, operator):
    filterSubstring = "%s(%s,'%s')" %(operator, columnName, filterValue)
    if (len(self.filters[providerID]) < 1) and
         (self.filterLogicGates[providerID] == self.LG_NOT):
        filterSubstring = "%s %s" %(self.filterLogicGates[providerID], filterSubstring)
    elif (len(self.filters[providerID]) > 0) and
         (self.filterLogicGates[providerID] == self.LG_NOT):
        filterSubstring = " and (%s %s)" %(self.filterLogicGates[providerID], filterSubstring)
    elif (len(self.filters[providerID]) > 0):
        filterSubstring = " %s %s" %(self.filterLogicGates[providerID], filterSubstring)
    self.filters[providerID].append(filterSubstring)

Sometimes we want to use a logical operator in a filter against column member. E.g. if instead of the partial string match above, we wanted to filter specifically for the value “InterMountain”, the $filter value would look like:

$filter=Region eq 'InterMountain'

Let’s create a method that builds these logical filters. We’ll use the same logic gate logic as in the addStringFilter().

def addLogicalFilter(self, providerID, columnName, filterValue, operator):
    filterSubstring = "%s %s '%s'" %(columnName, operator, filterValue)
    if (len(self.filters[providerID]) < 1) and
         (self.filterLogicGates[providerID] == self.LG_NOT):
        filterSubstring = "%s %s" %(self.filterLogicGates[providerID], filterSubstring)
    elif (len(self.filters[providerID]) > 0) and
         (self.filterLogicGates[providerID] == self.LG_NOT):
        filterSubstring = " and (%s %s)" %(self.filterLogicGates[providerID], filterSubstring)
    elif (len(self.filters[providerID]) > 0):
        filterSubstring = " %s %s" %(self.filterLogicGates[providerID], filterSubstring)
    self.filters[providerID].append(filterSubstring)

Let’s create a setter for the filter override, which would allow us to comfortably set a manual $filter value for any provider. We should also add the ability to easily clear that override.

def setParamOverride(self, providerID, moValue):
    self.paramManualOverride[providerID] = moValue

def clearParamOverride(self, providerID):
    self.paramManualOverride[providerID] = None

When it comes time to finally call the FactData endpoint, we’re going to actually turn our list of filter strings into an OData $filter parameter, or if a manual override filter has been supplied, we should use it instead. Additionally, add any orderby parameters. We’ll call this method resolveFilter(). If we don’t add a pagesize parameter to our call, we’ll get everything in one go. This might result in very long response times, that time out. If we provide a pagesize parameter and the result set size exceeds it, then we’ll get the first page and a follow up link for the next page. We’ll add it as a parameter for the resolveFilter() method and later account for this when we touch the FactData endpoint.

def resolveFilter(self, providerID, pagesize = None):
    returnVal = "?"
    ampersand = ""
    if self.paramManualOverride[providerID] is not None:
        returnVal = "%s%s" %(returnVal, self.paramManualOverride[providerID])
        return returnVal
    else:
        if self.filterOrderBy[providerID]:
            providerOrderByCol = list(self.filterOrderBy[providerID].keys())[0]
            providerOrderDir = self.filterOrderBy[providerID][providerOrderByCol]
            returnVal = "%s%s$orderby=%s %s" % (returnVal, ampersand, providerOrderByCol, providerOrderDir)
            ampersand = "&"
        if len(self.filters[providerID]) > 0:
            returnVal = "%s%s$filter=" %(returnVal, ampersand)
            for currentFilter in self.filters[providerID]:
                returnVal = "%s%s" %(returnVal, currentFilter)
            ampersand = "&"
        if pagesize is not None:
            returnVal = "%s%spagesize=%s" %(returnVal, ampersand, pagesize)
        return returnVal

Now we finally come to the FactData endpoint. The endpoint is easy to consume on the surface, but with a twist.

To accomplish this, we’ll create two methods. The first will construct the initial FactData endpoint call, taking in any page size parameter that is passed. We’ll call this getFactData(). We’ll create a helper method to actually call the endpoint, called factDataRecordRollup(). If there is @odata.nextLink element in the response, we’ll make a recursive call, using that new link, appending the records as we go.

def getFactData(self, providerID, pagesize = None):
    filterString = self.resolveFilter(providerID, pagesize)
    urlFactData = self.urlProviderRoot + "/" + providerID + "/FactData" + filterString
    fdRecordList = self.factDataRecordRollup(urlFactData)
    return fdRecordList


def factDataRecordRollup(self, urlFactData):
    response = self.oauth.get(urlFactData)
    responseJson = json.loads(response.text)
    fdRecordList = responseJson["value"]
    if "@odata.nextLink" in responseJson:
        fdRecordSubList = self.factDataRecordRollup(responseJson["@odata.nextLink"])
        fdRecordList.extend(fdRecordSubList)
    return fdRecordList

That’s it! We have implemented a Python wrapper for the SAP Analytics Cloud Export API. In the blog posts, we skipped the defensive coding that we’d normally want to use. The completed API is available in the SAP samples organization, on Github.  Next time, in our last installment of the series, we’ll take it for a test drive.