OpenSpending model to Cubes model conversion

os_to_cubes.py

from sqlalchemy import create_engine, MetaData, Table
import json

OS_DATASET = "de-bund"

def get_os_metadata(dataset_name):
    engine = create_engine("postgres://postgres@localhost/openspending")
    md = MetaData(bind=engine)

    table = Table("dataset", md, autoload=True)
    w = table.c["name"] == dataset_name
    result = engine.execute(table.select(whereclause=w))
    r = result.fetchone()
    d = json.loads(r.data)
    return d

def create_model(dataset, metadata):
    attr_list = metadata["mapping"]

    mappings = {}

    os_measures = [(key, value) for key, value in attr_list.items() if value["type"] == "measure"]
    os_attributes = [(key, value) for key, value in attr_list.items() if value["type"] == "attribute"]
    os_compounds = [(key, value) for key, value in attr_list.items() if value["type"] == "compound"]
    os_dates = [(key, value) for key, value in attr_list.items() if value["type"] == "date"]

    fact_table = "%s__entry" % dataset

    measures = []
    dimensions = []
    joins = []

    for name, desc in os_measures:
        attr = {"name":name,
                "label":desc.get("label"),
                "description": desc.get("description")
                }
        measures.append(attr)
        mappings[name] = {"table":fact_table, "column":name} 

    for name, desc in os_attributes:
        dim = {"name":name,
                "label":desc.get("label"),
                "description": desc.get("description")
                }
        dimensions.append(dim)
        mappings[name] = {"table":fact_table, "column":name}

    for dim_name, desc in os_compounds:
        attrs = []
        dim_table = "%s__%s" % (dataset, dim_name)
        for attr_name, attr in desc["attributes"].items():
            attr = {"name":attr_name}
            attrs.append(attr)
            ref = "%s.%s" % (dim_name, attr_name)

            mappings[ref] = {"table":dim_table, "column":attr_name}

        dim = {"name":dim_name,
                "label":desc.get("label"),
                "description": desc.get("description"),
                "levels": [
                    {
                        "name": dim_name,
                        "attributes": attrs
                    }
                ]
            }
        dimensions.append(dim)
            
        join = {
            "master": {"table":fact_table, "column": "%s_id" % dim_name },
            "detail": {"table":dim_table, "column": "id"}
        }
        joins.append(join)

    model = {
        "cubes": [
            {
                "name": dataset,
                "dimensions": [dim["name"] for dim in dimensions],
                "mappings": mappings,
                "joins": joins,
                "measures": measures,
                "fact": fact_table
            },
        ],
        "dimensions": dimensions
    }
    
    return model
    
md = get_os_metadata(OS_DATASET)
model = create_model(OS_DATASET, md)

print json.dumps(model, indent=4)