gctools/cognos7/mdl_convert.py

from itertools import pairwise
import json
import re
import csv


def get_field(field, block, num=1):
    if num == 2:
        res = re.search(field + r' (\d+) "([^"]*)" ', block)
        if res:
            return res[1], res[2]
        return "0", ""
    res = re.search(field + r' "([^"]*)" ', block)
    if res:
        return res[1]
    res = re.search(field + r" (\w*) ", block)
    if res:
        return res[1]
    return ""


def ignore(block):
    s = block.split(" ")
    return (s[0], block)


def model_name(block):
    return {"Type": "Description", "Decription": block}
    # "Name \"Neues Modell\" ModelCodePage \"ibm-5348_P100-1997\" AutoAccess False UpdateCycle 9 ModelStamp 1218210240
    # Version \"10.2.6109.304\" ModelCategoryOrderDefault OrderUsePreference ModelOrderedByDefault False
    # ModelNonRollupHierarchies False "


def cognos_source(block):
    id, name = get_field("CognosSource", block, 2)
    return {
        "Type": "CognosSource",
        "ID": id,
        "Name": name,
        "SourceType": get_field("SourceType", block),
        "SourcePath": get_field("SourcePath", block),
        "PackageTimeStamp": get_field("PackageTimeStamp", block),
        # "CognosSource 10335 \"GC\" SourceType Package SourcePath \"/content/folder[@name='Package']/package[@name='GC']\"
        # PackageTimeStamp \"/content/folder[@name='Package']/package[@name='GC']/model[@name='2020-09-23T14:57:28.424Z']\" "
    }


def cognos_package_datasource_connection(block):
    id, name = get_field("CognosPackageDatasourceConnection", block, 2)
    prs_id, prs_name = get_field("PackageReportSource", block, 2)
    return {
        "Type": "CognosPackageDatasourceConnection",
        "ID": id,
        "Name": name,
        "PackageReportSource": {
            "ID": prs_id,
            "Name": prs_name,
        },
        "CognosPackageConnection": get_field("CognosPackageConnection", block),
        "CognosPackageConnectionSignon": get_field(
            "CognosPackageConnectionSignon", block
        ),
        "CognosPackageAlwaysUseTransformerSignon": get_field(
            "CognosPackageAlwaysUseTransformerSignon", block
        ),
        "CognosPackagePowercubeSource": get_field(
            "CognosPackagePowercubeSource", block
        ),
        # "CognosPackageDatasourceConnection 15957 \"3H_AUTOMOBILE\" PackageReportSource 10335 \"GC\"
        # CognosPackageConnection \"3H_AUTOMOBILE\"
        # CognosPackageConnectionSignon \"3H_AUTOMOBILE\" CognosPackageAlwaysUseTransformerSignon False
        # CognosPackagePowercubeSource False "
    }


def data_source(block):
    id, name = get_field("CognosPackageDatasourceConnection", block, 2)
    prs_id, prs_name = get_field("PackageReportSource", block, 2)
    return {
        "Type": "DataSource",
        "ID": id,
        "Name": name,
        "Separator": get_field("Separator", block),
        "SourceType": get_field("SourceType", block),
        "CharacterSet": get_field("CharacterSet", block),
        "DecimalSep": get_field("DecimalSep", block),
        "Thousandsep": get_field("Thousandsep", block),
        "Columns": get_field("Columns", block),
        "Timing": get_field("Timing", block),
        "PackageReportSource": {
            "ID": prs_id,
            "Name": prs_name,
        },
        "AutoSummary": get_field("AutoSummary", block),
        "SetCurrent": get_field("SetCurrent", block),
        "ServerSource": get_field("ServerSource", block),
        "Speed": get_field("Speed", block),
        "Presummarized": get_field("Presummarized", block),
        "StreamExtractSize": get_field("StreamExtractSize", block),
    }


def org_name(block):
    id, name = get_field("OrgName", block, 2)
    return {
        "Type": "OrgName",
        "ID": id,
        "Name": name,
        "Origin": get_field("Origin", block),
        "Offset": get_field("Offset", block),
        "Column": get_field("Column", block),
        "Storage": get_field("Storage", block),
        "Scale": get_field("Scale", block),
        "Size": get_field("Size", block),
        "Decimals": get_field("Decimals", block),
        "Class": get_field("Class", block),
        "InputScale": get_field("InputScale", block),
        "TimeArray": get_field("TimeArray", block),
        "ColSrcType": get_field("ColSrcType", block),
    }


def dimension(block):
    id, name = get_field("Dimension", block, 2)
    return {
        "Type": "Dimension",
        "ID": id,
        "Name": name,
        "DimType": get_field("DimType", block),
        "EarliestDate": get_field("EarliestDate", block),
        "LatestDate": get_field("LatestDate", block),
        "ManualPeriods": get_field("ManualPeriods", block),
        "DaysInWeek": get_field("DaysInWeek", block),
        "NewCatsLock": get_field("NewCatsLock", block),
        "ExcludeAutoPartitioning": get_field("ExcludeAutoPartitioning", block),
        "DimDefaultCategory": get_field("DimDefaultCategory", block),
        # "Dimension 1463 \"Zeit\" DimType Date EarliestDate 19010101 LatestDate 21001231 ManualPeriods False DaysInWeek 127
        # NewCatsLock False ExcludeAutoPartitioning False DimDefaultCategory 0 "
    }


def categories(block):
    return {
        "Type": "Categories",
        # "Categories"
    }


def root(block):
    id, name = get_field("Root", block, 2)
    return {
        "Type": "Root",
        "ID": id,
        "Name": name,
        "Inclusion": get_field("Inclusion", block),
        "Lastuse": get_field("Lastuse", block),
        "Date": get_field("Date", block),
        "Filtered": get_field("Filtered", block),
        "Suppressed": get_field("Suppressed", block),
        "Sign": get_field("Sign", block),
        "HideValue": get_field("HideValue", block),
        "IsKeyOrphanage": get_field("IsKeyOrphanage", block),
        "IsTruncated": get_field("IsTruncated", block),
        "Blanks": get_field("Blanks", block),
        # "Root 1467 \"Zeit\" Inclusion Generate Lastuse 20180820 Date 0 Filtered False
        # Suppressed False Sign False HideValue False
        # IsKeyOrphanage False IsTruncated False Blanks False Drill 1469 \"Nach Zeit\" Label \"Nach Zeit\" Inclusion Suppress Filtered False
        # Suppressed True PrimaryDrill True HideValue False YearBegins 20080101 PartialWeek Split ExtraWeek None WeekBegins Sunday
        # Levels 1475 \"Jahr\" Blanks \"( Leerstelle )\" Inclusion Generate DateFunction Year Generate Need RefreshLabel False
        # RefreshDescription False RefreshShortName False NewCatsLock False CatLabFormat \"YYYY\" Timerank 10
        # UniqueCategories True UniqueMove False Associations 1477 \"Order Date\" AssociationType Type_Query
        # AssociationRole Role_Source AssociationReferenced \"Order Date\" Associations 1479 \"Order Date\"
        # AssociationContext 1469 AssociationType Type_Query AssociationRole Role_OrderBy
        # AssociationReferenced \"Order Date\" SortOrder Default SortAs Ascending "
    }


def levels(block):
    id, name = get_field("Levels", block, 2)
    return {
        "Type": "Levels",
        "ID": id,
        "Name": name,
        "Blanks": get_field("Blanks", block),
        "Inclusion": get_field("Inclusion", block),
        "DateFunction": get_field("DateFunction", block),
        "Generate": get_field("Generate", block),
        "RefreshLabel": get_field("RefreshLabel", block),
        "RefreshDescription": get_field("RefreshDescription", block),
        "RefreshShortName": get_field("RefreshShortName", block),
        "NewCatsLock": get_field("NewCatsLock", block),
        "CatLabFormat": get_field("CatLabFormat", block),
        "Timerank": get_field("Timerank", block),
        "UniqueCategories": get_field("UniqueCategories", block),
        "UniqueMove": get_field("UniqueMove", block),
        # "Associations": get_field("Inclusion", block),
        # "AssociationType": get_field("Inclusion", block),
        # "AssociationRole": get_field("Inclusion", block),
        # "AssociationReferenced": get_field("Inclusion", block),
        # "Levels 1481 \"Quartal\" Blanks \"( Leerstelle )\" Inclusion Generate DateFunction Quarter Generate All RefreshLabel False
        # RefreshDescription False RefreshShortName False NewCatsLock False CatLabFormat \"Q\"\". Q.\"\" YYYY\" Timerank 20
        # UniqueCategories True UniqueMove False Associations 1483 \"Order Date\" AssociationType Type_Query
        # AssociationRole Role_Source AssociationReferenced \"Order Date\" Associations 1485 \"Order Date\"
        # AssociationContext 1469 AssociationType Type_Query AssociationRole Role_OrderBy
        # AssociationReferenced \"Order Date\" SortOrder Default SortAs Ascending "
    }


def category(block):
    return {
        "Type": "Category",
        # "Category 10555 \"20170101-20171231\" Parent 1469 Levels 1475 OrderBy Drill 1469 Value \"2017\" Label \"2017\"
        # Lastuse 20180820 SourceValue \"2017\" Date 20170101 Filtered False Suppressed False Sign False HideValue False
        # IsKeyOrphanage False IsTruncated False Blanks False Category 10557 \"20170101-20170331\" Parent 10555 Levels 1481
        # OrderBy Drill 1469 Value \"20170101\" Label \"1. Q. 2017\" Lastuse 20180711 SourceValue \"20170101\" Date 20170101
        # Filtered False Suppressed False Sign False HideValue False IsKeyOrphanage False IsTruncated False Blanks False
        # Category 10559 \"20170101-20170131\" Parent 10557 Levels 1487 OrderBy Drill 1469 Value \"201701\" Label \"Jan./2017\"
        # Lastuse 20180711 SourceValue \"201701\" Date 20170101 Filtered False Suppressed False Sign False HideValue False
        # IsKeyOrphanage False IsTruncated False Blanks False Category 10561 \"20170101\" Parent 10559 Levels 1493
        # OrderBy Drill 1469 Value \"20170101\" Label \"01/01/2017\" Lastuse 20180711 SourceValue \"20170101\" Date 20170101
        # Filtered False Suppressed False Sign False HideValue False IsKeyOrphanage False IsTruncated False Blanks False "
    }


def special_category(block):
    return {
        "Type": "SpecialCategory",
        # "SpecialCategory 1503 \"M bisher\" Parent 1467 Levels 0 Lastuse 20180820 Rollup True TimeAggregate ToDate RunningPeriods 0
        # ToDateLevel \"Monat\" TargetOffset 0 TargetLevel \"Tag\" ContextOffset 0 DateDrill 1469 Primary 11737 Primary 11739
        # Primary 11741 Primary 11743 Primary 11745 Primary 11747 Primary 11749 Primary 11751 Primary 11753 Sign False "
    }


def map_drills(block):
    return {
        "Type": "MapDrills",
        # "MapDrills MapDrill 1469 "
    }


def view_name(block):
    return {
        "Type": "ViewName",
        # "ViewName 1471 \"Alle Kategorien\" Type All ViewCustomView 0 ViewName 1473 \"Dimension auslassen\" Type Omit
        # ViewCustomView 0 Dimension 1521 \"AH-Gruppe\" DimType Regular NewCatsLock False ExcludeAutoPartitioning False
        # DimDefaultCategory 0 "
    }


def associations(block):
    return {
        "Type": "Associations",
        # "Associations 15065 \"Hauptbetrieb_Name\" AssociationType Type_Query AssociationRole Role_Label
        # AssociationReferenced \"Hauptbetrieb_Name\" Associations 15069 \"Hauptbetrieb_ID\"
        # AssociationContext 1525 AssociationType Type_Query AssociationRole Role_OrderBy
        # AssociationReferenced \"Hauptbetrieb_ID\" SortOrder Text SortAs Ascending "
    }


def drill(block):
    id, name = get_field("Drill", block, 2)
    return {
        "Type": "Drill",
        "ID": id,
        "Name": name,
        "Label": get_field("Label", block),
        "Inclusion": get_field("Inclusion", block),
        "Filtered": get_field("Filtered", block),
        "Suppressed": get_field("Suppressed", block),
        "PrimaryDrill": get_field("PrimaryDrill", block),
        "HideValue": get_field("HideValue", block),
        "YearBegins": get_field("YearBegins", block),
        "PartialWeek": get_field("PartialWeek", block),
        "ExtraWeek": get_field("ExtraWeek", block),
        "WeekBegins": get_field("WeekBegins", block),
        # "Drill 1595 \"Nach Marke\" Inclusion Suppress Filtered False Suppressed True PrimaryDrill True HideValue False
        # Levels 1601 \"Marke\" Blanks \"( Leerstelle )\"
        # DateFunction None Generate None RefreshLabel False RefreshDescription False RefreshShortName False NewCatsLock False Timerank 0
        # UniqueCategories False UniqueMove False
        # Associations 1603 \"Fabrikat\" AssociationType Type_Query AssociationRole Role_Source AssociationReferenced \"Fabrikat\" "
    }


def measure(block):
    return {
        "Type": "Measure",
        # "Measure 10053 \"DG\" Missing Zero IgnoreMissingValue False Storage Float64 OutPutScale 0 Decimals 8
        # ReverseSign False IsCurrency False
        # IsFolder False Format \"#,##0~0\" DrillThrough False EndList
        # Associations 10055 \"DG\" AssociationType Type_Query AssociationRole Role_Source AssociationReferenced \"DG\" "
    }


def signon(block):
    return {
        "Type": "Signon",
        # "Signon 15673 \"GC_CARLO\" UserId \"gaps\" PromptForPassword False
        # EncryptedPW \"*1*0154377D72534C4B5F384F556F5B7970286A59373F70255D63D4EDE5BC9A7FD45994\" \"AC2C0B8CE18548\"
        # AutoLogon False SignonType \"DataSource\" Signon 15917 \"Global1\" UserId \"Global1\" PromptForPassword False
        # EncryptedPW \"*1*013B396846296D72232C62274C312B48432B464B52705B2147BC0680EA66C628A0FE\" \"A70FDB4177ACBA\"
        # AutoLogon True SignOnNamespace \"CognosEx\" SignonType \"Cognos\" "
    }


def dimension_view(block):
    return {
        "Type": "DimensionView",
        # "DimensionView 1463 \"All Categories\" DimensionView 1521 \"All Categories\" DimensionView 1551 \"All Categories\"
        # DimensionView 1575 \"All Categories\" DimensionView 1591 \"All Categories\" DimensionView 1651 \"All Categories\"
        # DimensionView 1665 \"All Categories\" DimensionView 1693 \"All Categories\" DimensionView 15741 \"All Categories\"
        # MeasureInclude 9829 Yes MeasureInclude 10053 Yes MeasureInclude 10309 Yes MeasureInclude 10313 Yes
        # MeasureInclude 10317 Yes MeasureInclude 15761 Yes "
    }


def allocation_add(block):
    return {
        "Type": "AllocationAdd",
        # "AllocationAdd Measure 9829 Type Default AllocationAdd Measure 10053 Type Default AllocationAdd Measure 10309 Type Default
        # AllocationAdd Measure 10313 Type Default AllocationAdd Measure 10317 Type Default AllocationAdd Measure 15761 Type Default "
    }


def cube(block):
    return {
        "Type": "Cube",
    }


CONVERSION = {
    "Name": model_name,
    "CognosSource": cognos_source,
    "CognosPackageDatasourceConnection": cognos_package_datasource_connection,
    "DataSource": data_source,
    "OrgName": org_name,
    "Dimension": dimension,
    "Categories": categories,
    "Root": root,
    "Category": category,
    "SpecialCategory": special_category,
    "Levels": levels,
    "MapDrills": map_drills,
    "ViewName": view_name,
    "Drill": drill,
    "Measure": measure,
    "Signon": signon,
    "DimensionView": dimension_view,
    "Cube": cube,
}


def convert_block(block):
    block = block.replace("\n", "")
    block_type = block.split(" ")[0]
    # block_pair = re.findall(r'("[^"]+"|\w+) ', block)
    # return (block_type, list(zip((block_pair[::2], block_pair[1::2]))))
    if block_type in CONVERSION:
        return CONVERSION[block_type](block)
    return block_type


def main(filename):
    with open(filename, "r") as frh:
        mdl_str = frh.read()

    mdl_str = re.sub(r"\n+", "\n", mdl_str)
    tags = "|".join(list(CONVERSION.keys()))
    mdl_str = re.sub(r"\n(" + tags + r") ", r"\n\n\1 ", mdl_str)
    mdl_blocks = mdl_str.split("\n\n")

    result = [convert_block(b) for b in mdl_blocks]

    json.dump(result, open(filename[:-4] + ".json", "w"), indent=2)


if __name__ == "__main__":
    main("data/S_Offene_Auftraege.mdl")