Audit Data
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"]
OData Filters
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 can filter columns for specific values, using the standard logical operators; “eq”, “ne”, “gt”, “lt”, “ge”, and “le”.
- We want to allow multiple filters, but with the caveat that all filters use the same logic gate; “and”, “or”, or “not”. His greatly simplifies our coding, as we don’t need to worry about nested logic combinations.
- If the user needs something more complex, we want them to be able to apply a custom, hand crafted $filter string. If such a $filter string is available, we’ll use that. Otherwise, we’ll construct one when needed, from the existing filter preferences.
- We’ll also add OData $orderby support.
- String and substring support for the $filter filters.
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:
- The hand crafted $filter, if one exists.
- Any $orderby preferences.
- Any $filter preferences.
- The logic gate to be used, when combining multiple filters.
- While we're at it, let's instantiate the classes with the reference values.
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)
String Filters
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)
Logical Filters
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)
Manual Filters
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
Turning filter objects into $filter strings
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
FactData
Now we finally come to the FactData endpoint. The endpoint is easy to consume on the surface, but with a twist.
- The first url endpoint that we’ll call is the urlProviderRoot + "/" + providerID + "/FactData"
- We’ll append the filter parameter string to this. We’ll do this by calling resolveFilter() and appending the result to our url, before calling it.
- If we supply a page size, there may be a @odata.nextLink element and if there is, we need to follow those additional links, as far as they go.
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.
