Generating Entity-Relationship diagrams from the SAP Ariba Analytical Reporting API metadata

In this blog post, I will cover how we can generate an Entity-Relationship (ER) diagram from the metadata returned by the SAP Ariba Analytical Reporting API.

The metadata endpoint of the SAP Ariba Analytical Reporting API will return details of all the facts and dimensions we can use to create our view templates. Although all the available facts and dimensions are returned by the API, it can be hard to navigate the JSON payload and understand the relationship(s) between facts and dimensions. The Python script shown in this blog post, was created to generate an ER diagram and easily visualise the relationship(s) a fact/dimension has.

To accomplish this we will do the following:

1. Retrieve the Analytical Reporting metadata
2. Install the required Python packages
3. Run the Python script

Step 1 – Retrieve the Analytical Reporting API metadata

For this, you will need an approved Analytical Reporting API application.I covered in a previous blog post, how to create an application and request API access in the SAP Ariba Developer Portal. Alternatively, you can use the sample file provided below.

⚡ Retrieve the Analytical Reporting metadata available for your realm, you can run the curl command below.

Store the JSON response as it will be needed by the Python ER generation script. A sample of the file can be found here.

$ curl --location --request GET 'https://openapi.ariba.com/api/analytics-reporting-view/v1/prod/metadata?realm=[YOUR-REALM-T]&product=analytics&includePrimaryKeys=true' \
--header 'apiKey: [YOUR-API-KEY]' \
--header 'Authorization: Bearer [YOUR-ACCESS-TOKEN]' > AnalyticalReportingMetadata.json

Step 2 – Install the required Python packages

The packages below are required by the Python script to generate the ER diagram and output the PDF file. The main package used by the script is eralchemy. The script will generate an ER markdown file (example), which will be provided to eralchemy to generate a user-friendly PDF diagram.

⚡ “pip install” the packages below before running the script.

ERAlchemy==1.2.10
pygraphviz==1.6
SQLAlchemy==1.3.20

Now to the fun part….. The Python script below will process the documentTypes included in the AnalyticalReportingMetadata.json file. We can specify a document type to the script below, so it generates the ER diagram for that document type only, instead of all document types.

import json
import argparse
from eralchemy import render_er

METADATA_FILE = "AnalyticalReportingMetadata.json"

def generate_er_markdown(entities = None):
    """
    docstring
    """
    with open(METADATA_FILE) as f:
        ar_structure = json.load(f)

    output = ""
    relationships = []

    for document in ar_structure:
        document_md = ""
        document_type = document['documentType']

        if entities is None or document_type in entities:
            document_md = f"[{document_type}]\n"

            primary_keys = []
            # Process primary keys
            for pk in document['primaryKeys']:
                primary_keys.append(pk['name'])

            # Get all fields
            for field in document['selectFields']:
                field_name = field['name']
                field_type = field['type']

                prefix = ""

                if field_name in primary_keys:
                    prefix = "*"

                fk_entity = ""
                if field_type.endswith("Dim"):

                    # Only add FK prefix if it is not a primary key
                    if field_name not in primary_keys:
                        prefix = "+"
                    fk_entity = f' {{label:"{field_type}"}}'
                    relationships.append((document_type, "1--1", field_type))
                
                document_md += f"{prefix}{field_name}{fk_entity}\n"

            output += document_md + "\n"

    for rel in set(relationships):
        rel_str = f"{rel[0]} {rel[1]} {rel[2]}\n"
        output += rel_str
    
    return output
    
def document_type_entities(doc_type):
    with open(METADATA_FILE) as f:
        ar_structure = json.load(f)

    entities = []

    for document in ar_structure:
        document_type = document['documentType']

        if doc_type == document_type:
            entities.append(doc_type)

            for field in document['selectFields']:
                field_type = field['type']

                if field_type.endswith("Dim"):
                    entities.append(field_type)
    
    return entities


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='SAP Ariba ER Diagram generator')

    parser.add_argument('--document_type', type=str, default='',
                        help='Example value: S4ApprovalFlowFact')
    
    args = parser.parse_args()
    
    er_markdown = ""

    if args.document_type != "":
        er_markdown = generate_er_markdown(document_type_entities(args.document_type))
    else:
        er_markdown = generate_er_markdown()

    # Generate output file name
    document_filename = "_" + args.document_type if args.document_type != "" else ""
    er_markdown_filename = f"analytical_reporting{document_filename}"

    # Store Entity Relationship markdown
    with open(er_markdown_filename + ".er", "w") as f:
        f.write(er_markdown)
    
    ## Draw from Entity Relationship markdown to PDF
    render_er(er_markdown_filename + ".er", er_markdown_filename + ".pdf")

Step 3 – Run the Python script

⚡ Create the python script locally and run it with a command like the one below.

$ python generate-er-diagram.py --document_type ApprovalHistoryFact

The command above will generate 2 files:

• analytical_reporting_ApprovalHistoryFact.er: Contains the ER markdown expected by eralchemy.
• analytical_reporting_ApprovalHistoryFact.pdf: PDF file containing a user-friendly ER diagram.

The ER diagram in the analytical_reporting_ApprovalHistoryFact.pdf file will look something like the screenshot below:

ApprovalHistoryFact – ER Diagram

We’ve seen how we can use a Python script to generate a user-friendly ER diagram, based on the Analytical Reporting API metadata. It can used to generate ER diagram(s) for the different document types.

I hope this helps you understand the relationships between the facts and dimensions available in the Analytical Reporting API metadata and also whenever you want to define a custom view templates.