Cubes OLAP - Drill-down Cross Table

crosstable_example.py

import sqlalchemy
import cubes
import cubes.tutorial.sql as tutorial

DATA = "../examples/hello_world/data.csv"
MODEL = "../examples/hello_world/model.json"

engine = sqlalchemy.create_engine('sqlite:///:memory:')
tutorial.create_table_from_csv(engine, 
                      DATA, 
                      table_name="irbd_balance", 
                      fields=[
                            ("category", "string"),
                            ("category_label", "string"), 
                            ("subcategory", "string"), 
                            ("subcategory_label", "string"), 
                            ("line_item", "string"),
                            ("year", "integer"), 
                            ("amount", "integer")],
                      create_id=True    
                        
                        )

def print_cross_table(onrows, oncolumns, table):
    # Print column header:

    format = "%25s" * len(onrows) + "%10s" * len(table.columns)
    values = ([""]*len(onrows) + [str(c[0]) for c in table.columns])
    print format % tuple(values)

    row_heading_f = "%25s" * len(onrows)
    row_values_f = "%10d" * len(table.columns)
    measure = "amount_sum"

    for row_hdr, row in zip(table.rows, table.data):
        row_label = row_heading_f % tuple(row_hdr)
        values_str = row_values_f % tuple([value[0] for value in row])
        print row_label + values_str

model = cubes.load_model(MODEL)
cube = model.cube("irbd_balance")
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
cell = browser.full_cube()

result = browser.aggregate(cell, drilldown={"item":"subcategory", "year":None})

rows=["item.category_label", "item.subcategory_label"]
columns=["year"]

table = cubes.cross_table(result, rows, columns, ["amount_sum"])
print_cross_table(rows, columns, table)

crosstable_output.txt

                                                        2009      2010
                   Assets        Derivative Assets    123065    121626
                   Assets           Due from Banks      3044      1803
                   Assets              Investments     41012     36012
                   Assets        Loans Outstanding    103657    118104
                   Assets            Nonnegotiable      1202      1123
                   Assets             Other Assets      2247      3071
                   Assets        Other Receivables       984       811
                   Assets              Receivables       176       171
                   Assets               Securities        33       289
                   Equity            Capital Stock     11491     11492
                   Equity         Deferred Amounts       359       313
                   Equity                    Other     -1683     -3043
                   Equity        Retained Earnings     29870     28793
              Liabilities               Borrowings    110040    128577
              Liabilities   Derivative Liabilities    115642    110418
              Liabilities                    Other        57         8
              Liabilities        Other Liabilities      7321      5454
              Liabilities             Sold or Lent      2323       998

gistfile1.py

# Summary example:

result = browser.aggregate(cell, drilldown={"item":"subcategory", "year":None})

rows=["item.category_label", "item.subcategory_label"]
columns=["year"]

table = cubes.cross_table(result, rows, columns, ["amount_sum"])

# ARGS:
#   result - aggregation result
#   rows - list of attribute names to be put on rows
#   columns - list of attribute names to be put on columns
#   measures - list of measures, if None, then record_count will be used
#
# RETURNS:
#   named tuple with attributes:
#     rows - list of tuples with row headers
#     columns - list of tuples of column headers
#     data - list of table rows, each row is list of measure tuples