• Author / Uploaded
• Stefan Urbanek

Citation preview

Cubes – modelling patterns

Cubes modeling patterns r2, December 2012, Cubes v0.10.1 Schema 1

Model or configuration

store

sales

id

product_id

product id

code

store_id

code

address

amount

name

fact table has same name as cube, dimension tables have same names as dimensions

2

sales store

...

product

...

year

...

amount

dimension represented by only one attribute in fact table

3

"cubes": [ { "name": "sales", "dimensions": ["product", "store"], "joins": [ {"master":"product_id", "detail":"product.id"}, {"master":"store_id", "detail":"store.id"} ] } ], "dimensions": [ { "name": "product", "attributes": ["code", "name"] }, { "name": "store", "attributes": ["code", "address"] } ]

"cubes": [ { ... "dimensions": ["product", "store", "year"], ... } ], "dimensions": [ ... { "name": "year" } ]

Python: dim_store

ft_sales

dim_product

id

product_id

id

code

store_id

code

address

amount

name

cubes.create_workspace("sql", url=DATABASE_URL, dimension_prefix="dim_", fact_prefix="fact_") slicer.ini:

all dimension tables have prefix “dim_” and all fact tables have prefix “ft_”

4

Python:

schema: sales_datamart store

sales

product

id

product_id

id

code

store_id

code

address

amount

name

all tables are stored in other than default database schema

5

dimensions

[workspacee] dimension_prefix="dim_" fact_prefix="fact_"

cubes.create_workspace("sql", url=DATABASE_URL, schema="sales_datamart") slicer.ini: [workspacee] schema="sales_datamart"

Python:

facts

store

sales

product

id

product_id

id

code

store_id

code

address

amount

name

cubes.create_workspace("sql", url=DATABASE_URL, schema="facts", dimension_schema="dimensions", ) slicer.ini:

all fact tables are stored in one schema, all dimension tables in another

[workspacee] schema="facts" dimensions_schema="dimensions"

Cubes – modelling patterns

Schema 6

Model or configuration

sales store

...

product

...

sales_year

...

total_amount

"amount":"total_amount"]

flat dimension is called “year”, but column is “sales_year”; measure is reported as “amount”, column is named “total_amount”

7

dim_suppliers id

ft_sales supplier_id client_id

dim_organisation

name

id

address

name address

amount

"cubes": [ { "dimensions": [..., "year"], "measures": ["amount"], "mappings": { "year":"sales_year",

dim_clients id name address

clients and suppliers share one table with all organisations and companies

} } ], "dimensions": [ ... { "name": "year" } ]

"cubes": [ { "name": "sales" "dimensions": ["supplier", "client"], "measures": ["amount"], "joins": [ { "master":"supplier_id", "detail":"dim_organisation.id", "alias":"dim_supplier" }, { "master":"client_id", "detail":"dim_organisation.id", "alias":"dim_client" } ] } ], "dimensions": [ { "name": "supplier", "attributes": ["id", "name", "address"] } { "name": "client", "attributes": ["id", "name", "address"] } ]

8

sales

product

product_id

id

...

code

amount

name category_code category

product dimension has two levels: product category and product

product category

"cubes": [ { "dimensions": ["product", ...], "measures": ["amount"], "joins": [ {"master":"product_id", "detail":"product.id"} ] } ], "dimensions": [ { "name": "product", "levels": [ { "name":"category", "attributes": ["category_code", "category"] }, { "name":"product", "attributes": ["code", "name"] } ] } ]

Cubes – modelling patterns

Schema 9

Model or configuration

aggregate or filter

product id

key

code

label

name

∑

report

price

Product

Amount

coffee

200

tea

250

milk

50

Total

500

attribute code to be used for aggregation, filtering or links and attribute name used as labels in user interface tables

10

product

sales

id

product_id

code

...

name

amount

price

Product

Unit Price

Amount

...

...

...

user interface labels for dimensions, dimension attributes and measures

11

dim_date id year

year

month

month

quarter

Use: result = browser.aggregate(drilldown=["product"]) for row in result.table_rows("product"): print "%s: %s" % (row.label, row.record["amount_sum"])

"cubes": [ { "name": "sales", "label": "Product Sales", "dimensions": ["product", ...] } ], "dimensions": [ { "name": "product", "label": "Product", "attributes": [ {"name": "code", "label": "Code"}, {"name": "name", "label": "Product"}, {"name": "price", "label": "Unit Price"}, ] } ]

(in “dimensions”)

year

quarter

"dimensions": [ { "name": "product", "levels": [ { "name": "product", "attributes": ["code", "name", "price"] "key": "code", "label_attribute": "name" } ] } ]

{ "name":"date", "levels": [ { "name": "year", "attributes": ["year"] }, { "name": "quarter", "attributes": ["quarter"] }, { "name": "month", "attributes": ["month", "month_name"] }, { "name": "week", "attributes": ["week"] }, { "name": "weekday", "attributes": ["weekday"] }, { "name": "day", "attributes": ["day"] } ], "hierarchies": [ {"name": "ymd", "levels":["year", "month", "day"]}, {"name": "ym", "levels":["year", "month"]}, {"name": "yqmd", "levels":["year", "quarter", "month", "day"]}, {"name": "ywd", "levels":["year", "week", "weekday"]} ], "default_hierarchy_name": "ymd"

day

day

month month_name week day weekday

year year month

week weekday

dimension, such as date or geography, has multiple ways of organizing attributes into a hierarchy }

Cubes – modelling patterns

Schema 12

product id code name_en name_fr name_es

dimension attributes have languagespecific content (requirement: one column per language, use locale suffix)

Model or configuration "dimensions": [ { "name": "product", "label": "Product", "attributes": [ {"name": "code", "label": "Code"}, { "name": "name", "label": "Product", “locales”: ["en", "fr", "es"] } ] } ]

Use: !

browser = workspace.browser(cube, locale=”fr”)

Then browse as usual. Localization is transparent. Notes: 1.only one locale per browser 2.refer to dimension attributes as there was no localisation: “product.name” 3.if non-existing locale is requested, then default (first in the list) locale is used

Cubes – modelling patterns

Schema

Model or configuration { "locale": "en", "cubes": [ { "name": "sales", "label": "Product Sales", "dimensions": ["product"], "measures": [ {"name": "amount", "label": "Amount"} ] } ], "dimensions": [ { "name": "product", "label": "Product", "attributes": [ { "name": "code", "label": "Code" }, { "name": "name", "label": "Product", "locales": ["en", "sk"] }, { "name": "price", "label": "Unit Price" } ] } ]

sales product_id ... amount

product id code

Product

Unit Price

Amount

...

...

...

name_en name_sk

Produkt

Jednotková cena

Suma

unit_price

...

...

...

dimension attributes have languagespecific content; labels in report (including measures) should be displayed according to locale

}

Translation dictionary for non-default locale: { "locale": "sk", "dimensions": { "product”: { "levels": { "product" : { "label" : "Produkt", "attributes" : { "code":{"label": "Kód produktu"}, "name":{"label": "Produkt"}, "price":{"label": "Jednotková cena"} } } } } } “cubes”: { "sales": { "measures": { "amount" : {"label": "Suma"} } } } }

Use: !

model_sk = model.localize(translation)

Warning: interface for model localization is not final, might be changed in the future.

Cubes – modelling patterns

Cubes - lightweight Python OLAP Source Documentation

https://github.com/Stiivi/cubes http://packages.python.org/cubes/