We’ve taken a tour of SAC’s Model Export API and began constructing our Python API wrapper. We’ve marked out the foundation of the SACConnection class, made our initial connection to the tenant, acquired the OAuth token and read the provider list.  Now we are going to handle model metadata and master data. Much like we used the SACProvider class to store the connection relevant metadata for our providers (models), we are going to create a class for managing metadata, master data and data of individual models. Specifically, we’ll want to differentiate different type of dimensions from one another and we’ll want to track their master data. We will want to track:

We’ll create a dictionary for each of the different types of dimensions. The keys will be the dimension member ID and the values will be their descriptions. We’ll call this class ModelMetadata().

class ModelMetadata(object):
    dimensions = {}
    dateDimensions = {}
    measures = []
    accounts = {}
    versions = {}

Unfortunately, while all of this information is available, it does not come in any nice, handy, prepackaged form. We’ll have to tease it out. There are two broad strategies that we can take, but first, we’ll take a quick tour of the OData EDMX document.

<EntityType Name="FactData">
<Key>
<PropertyRef Name="Account_BestRunJ_sold"/>
<PropertyRef Name="Store_3z2g5g06m4"/>
<PropertyRef Name="Location_4nm2e04531"/>
<PropertyRef Name="Product_3e315003an"/>
<PropertyRef Name="Sales_Manager__5w3m5d06b5"/>
<PropertyRef Name="Date_703i1904sd"/>
<PropertyRef Name="Version_BestRunJsold_V"/>
</Key>
<Property Name="Account_BestRunJ_sold" Type="Edm.String" MaxLength="256">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="Store_3z2g5g06m4" Type="Edm.String" MaxLength="256">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="Location_4nm2e04531" Type="Edm.String" MaxLength="256">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="Product_3e315003an" Type="Edm.String" MaxLength="256">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="Sales_Manager__5w3m5d06b5" Type="Edm.String" MaxLength="256">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="Date_703i1904sd" Type="Edm.String" MaxLength="6">
<Annotation Term="Integration.OriginalDataType">
<String>VARCHAR</String>
</Annotation>
</Property>
<Property Name="Version_BestRunJsold_V" Type="Edm.String" MaxLength="300">
<Annotation Term="Integration.OriginalDataType">
<String>NVARCHAR</String>
</Annotation>
</Property>
<Property Name="SignedData" Type="Edm.Decimal" MaxLength="32" Precision="31" Scale="7">
<Annotation Term="Integration.OriginalDataType">
<String>DECIMAL</String>
</Annotation>
</Property>
<Annotation Term="Integration.CDataID">
<String>sap.epm:BestRunJuice_SampleModel</String>
</Annotation>
</EntityType>​

The Fact Data EntityType element is where we’ll start. Here, we can get a list of model dimensions. Unfortunately, the dimensions don’t give up what kind of dimension they are based on datatype. So after looping through Fact Data EntityType element, to determine which columns are measures and which are dimensions, you’ll do one of two things:

Let’s now interrogate the OData EDMX document. Let’s call this method getModelMetadata().  Fetch the EDMX document, from the model’s $metadata endpoint. Parse the returned XML. This is why we imported minidom at the start of this coding project.

def getModelMetadata(self, providerID):
    modelMetadata = ModelMetadata()
    urlMetadata = self.urlProviderRoot + "/" + providerID + "/$metadata"
    response = self.oauth.get(urlMetadata)

    xmlData = minidom.parseString(response.text.encode("UTF-8"))

We want to select the Fact Data EntityType element. We’ll do this selection by opening a for loop for every EntityType element and then checking to see if the current one is named FactData.

for entityTypeElement in xmlData.getElementsByTagName("EntityType"):
    nameAttribute = entityTypeElement.getAttribute("Name")
    if nameAttribute.find("FactData") > -1:
        # There will be more than one EntityType element, but only one named "FactData"

        # all non-measure columns appear in the PropertyRef elements

In this element, the dimension columns are listed inside PropertyRef elements. Let’s find all of the dimension columns and list them in a list, called dimList. We can determine the kind of dimension later.

dimList = []
for propertyRefElement in entityTypeElement.getElementsByTagName("PropertyRef"):
    prnAtt = propertyRefElement.getAttribute("Name")
    dimList.append(prnAtt)

We’ll want to loop over the child Property elements and get the name of the Property element (i.e. the column name) of each column.

# Property elements include all columns
for propertyElement in entityTypeElement.getElementsByTagName("Property"):
    prAtt = propertyElement.getAttribute("Name")
    dataType = ""

Call the master data endpoint for each column and parse the response JSON.

urlCurrDimMetadata = self.urlProviderRoot + "/" + providerID + "/" + prAtt + "Master"
currDimResponse = self.oauth.get(urlCurrDimMetadata)
currDimResponseJson = json.loads(currDimResponse.text)

If the column is not in dimList, then it is a measure. If it is in dimList, then check the keys in the master data JSON entities, to see which kind of dimension it is.

if prAtt not in dimList:
    # Measures and versions show up in the modelMetadata.measures list
    if prAtt not in modelMetadata.dimensions:
        modelMetadata.measures.append(prAtt)
else:
    # modelMetadata.dateDimensions
    # modelMetadata.accounts
    # modelMetadata.versions
    # modelMetadata.dimensions
    isAccount = False
    isVersion = False
    isDate = False
    mdMembers = {}
    for cdMember in currDimResponseJson["value"]:
        cmID = cdMember["ID"]
        cmDesc = cdMember["Description"]
        mdMembers[cmID] = cmDesc
        if 'VERSION' in cdMember:
            isVersion = True
        elif "accType" in cdMember:
            isAccount = True
        elif "DATE" in cdMember:
            isDate = True
    if isAccount:
        modelMetadata.accounts[prAtt] = mdMembers
    elif isVersion:
        modelMetadata.versions[prAtt] = mdMembers
    elif isVersion:
        modelMetadata.dateDimensions[prAtt] = mdMembers
    else:
        modelMetadata.dimensions[prAtt] = mdMembers

Store the model metadata and return

self.modelMetadata[providerID] = modelMetadata
return modelMetadata

The complete getModelMetadata() method.

def getModelMetadata(self, providerID):
    try:
        modelMetadata = ModelMetadata()
        urlMetadata = self.urlProviderRoot + "/" + providerID + "/$metadata"
        response = self.oauth.get(urlMetadata)

        xmlData = minidom.parseString(response.text.encode("UTF-8"))
        for entityTypeElement in xmlData.getElementsByTagName("EntityType"):
            nameAttribute = entityTypeElement.getAttribute("Name")
            if nameAttribute.find("FactData") > -1:
                # There will be more than one EntityType element, but only one named "FactData"

                # all non-measure columns appear in the PropertyRef elements
                dimList = []
                for propertyRefElement in entityTypeElement.getElementsByTagName("PropertyRef"):
                    prnAtt = propertyRefElement.getAttribute("Name")
                    dimList.append(prnAtt)

                # Property elements include all columns
                for propertyElement in entityTypeElement.getElementsByTagName("Property"):
                    prAtt = propertyElement.getAttribute("Name")
                    dataType = ""

                    # occurs oncce, so this little for loop will fetch us our only String grandchild of Property
                    for stringElement in propertyElement.getElementsByTagName("String"):
                        dataType = stringElement.childNodes[0].data

                    urlCurrDimMetadata = self.urlProviderRoot + "/" + providerID + "/" + prAtt + "Master"
                    currDimResponse = self.oauth.get(urlCurrDimMetadata)
                    currDimResponseJson = json.loads(currDimResponse.text)

                    # sort dimensions into the dimension dicts and measures into the measure list

                    if prAtt not in dimList:
                        # Measures and versions show up in the modelMetadata.measures list
                        if prAtt not in modelMetadata.dimensions:
                            modelMetadata.measures.append(prAtt)
                    else:
                        # modelMetadata.dateDimensions
                        # modelMetadata.accounts
                        # modelMetadata.versions
                        # modelMetadata.dimensions
                        isAccount = False
                        isVersion = False
                        isDate = False
                        mdMembers = {}
                        for cdMember in currDimResponseJson["value"]:
                            cmID = cdMember["ID"]
                            cmDesc = cdMember["Description"]
                            mdMembers[cmID] = cmDesc
                            if 'VERSION' in cdMember:
                                isVersion = True
                            elif "accType" in cdMember:
                                isAccount = True
                            elif "DATE" in cdMember:
                                isDate = True
                        if isAccount:
                            modelMetadata.accounts[prAtt] = mdMembers
                        elif isVersion:
                            modelMetadata.versions[prAtt] = mdMembers
                        elif isVersion:
                            modelMetadata.dateDimensions[prAtt] = mdMembers
                        else:
                            modelMetadata.dimensions[prAtt] = mdMembers

        self.modelMetadata[providerID] = modelMetadata
        dimList = list(modelMetadata.dimensions.keys())
        self.addFilterProvider(providerID)
        return modelMetadata
    except Exception as e:
        errorMsg = "Unknown error during token acquisition."
        if e.status_code:
            errorMsg = "%s  Status code %s from server.  %s" % (errorMsg, e.status_code, e.error)
            raise RESTError(errorMsg)
        else:
            errorMsg = "%s  %s" % (errorMsg, e.error)
            raise Exception(errorMsg)