Data Export API: Building a Python Wrapper (Part 2)
source link: https://blogs.sap.com/2023/01/30/data-export-api-building-a-python-wrapper-part-2/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Data Export API: Building a Python Wrapper (Part 2)
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. The completed export API wrapper can be found on github.
- Generic dimensions
- Date dimensions
- Measures
- Accounts
- Versions
class ModelMetadata(object):
dimensions = {}
dateDimensions = {}
measures = []
accounts = {}
versions = {}
- In the OData EDMX document, there are a handful of EntityType elements. One of them is for defining the fact data. Its name attribute is “FactData”. Among others, is another for defining the master data and its name attribute is “MasterData”.
- Within the Fact Data EntityType element, there is a single Key element. Key has a number of child PropertyRef elements. The name attribute of each of these PropertyRef elements refers to the column name of a dimension. Measures do not show up in the Key element.
- Within the Fact Data EntityType element, there is a Property element for each column – measure or dimension – in the fact data. The name attribute is the name of the column. This information, combined with the Key element PropertyRef membership, tells us what our measures are.
- Within the Property element, there is one or more Annotation elements. Within the Fact Data EntityType element, they tend to occur once and are used for signifying the data type of the fact data column. In the Master Data EntityType element, they refer to the columns in the dimension definition for all the model’s dimensions and the Annotation elements annotation elements can occur multiple times.
- Each Annotation elements refers to a datatype. This is stored in a String child element. So the datatype of a column in the fact table is inside the relevant ../Annotation/String element for that column.
Here is the Fact Data EntityType element for the Best Run Juice demo model.
<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>
Strategies for determining dimension type
- After processing the Fact Data EntityType element, go to the Master Data EntityType element. Each dimension will have several Property elements, and each will have one or more Annotation elements. In the soup of ../Annotation/String elements, the dimension will tell us what it is. If it is a version dimension, at least one of the ../Annotation/String data types will be VERSION. If it is an account, then at least will one will be ACCOUNT_TYPE. If it is a date dimensions, then at least one will be DATE.
- A less computationally complex approach is to call the Masterdata endpoint and look in its response JSON. We have to call this endpoint anyway, to get the ID and Description pairs. ID and Description are not the only keys in the JSON entities. If it is a date, then there is a DATE key. If it is a version dimension, then there will be a VERSION key and accounts will have an accType key. This is the approach we will use.
def getModelMetadata(self, providerID):
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 = ""
urlCurrDimMetadata = self.urlProviderRoot + "/" + providerID + "/" + prAtt + "Master"
currDimResponse = self.oauth.get(urlCurrDimMetadata)
currDimResponseJson = json.loads(currDimResponse.text)
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
return modelMetadata
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)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK