def test_creation(self):
desc = {"dimensions": ["date", "product", "flag"]}
model = cubes.create_model(desc)
self.assertEqual(3, len(model.dimensions))
self.assertEqual(0, len(model.cubes))
desc = {
"dimensions": ["date", "product", "flag"],
"cubes": [{
"name": "contracts",
"dimensions": ["date", "product", "flag"]
}]
}
model = cubes.create_model(desc)
self.assertEqual(3, len(model.dimensions))
self.assertEqual(1, len(model.cubes))
self.assertIs(model.dimension("date"),
model.cube("contracts").dimension("date"))
# Test duplicate dimensions
dup = dict(desc)
desc["cubes"].append(desc["cubes"][0])
self.assertRaises(cubes.ModelError, cubes.create_model, dup)
dup = dict(desc)
desc["dimensions"].append(desc["dimensions"][0])
self.assertRaises(cubes.ModelError, cubes.create_model, dup)
def test_creation(self):
desc = { "dimensions": ["date", "product", "flag"] }
model = cubes.create_model(desc)
self.assertEqual(3, len(model.dimensions))
self.assertEqual(0, len(model.cubes))
desc = {
"dimensions": ["date", "product", "flag"],
"cubes": [
{
"name": "contracts",
"dimensions": ["date", "product", "flag"]
}
]
}
model = cubes.create_model(desc)
self.assertEqual(3, len(model.dimensions))
self.assertEqual(1, len(model.cubes))
self.assertIs(model.dimension("date"), model.cube("contracts").dimension("date"))
# Test duplicate dimensions
dup = dict(desc)
desc["cubes"].append(desc["cubes"][0])
self.assertRaises(cubes.ModelError, cubes.create_model, dup)
dup = dict(desc)
desc["dimensions"].append(desc["dimensions"][0])
self.assertRaises(cubes.ModelError, cubes.create_model, dup)
def setUp(self):
desc = {
"cubes": [
{
"name": "test",
"dimensions": ["date"],
"mappings": {
"date.day": {"column": "test_date", "extract":"day"},
"date.month": {"column": "test_date", "extract":"month"},
"date.year": {"column": "test_date", "extract":"year"},
}
}
],
"dimensions": [
{
"name": "date",
"levels": ["month", "year", "day"]
}
]
}
self.model = cubes.create_model(desc)
engine = sqlalchemy.create_engine('sqlite://')
connection = engine.connect()
metadata = sqlalchemy.MetaData(bind=engine)
table = Table('test', metadata,
Column('test_date', Date),
)
table.create()
self.cube = self.model.cube("test")
self.browser = SnowflakeBrowser(self.cube,connectable=connection)
def test_to_dict(self):
desc = self.model.to_dict()
model = cubes.create_model(desc)
self.assertEqual(self.model.dimensions, model.dimensions)
self.assertEqual(self.model.cubes, model.cubes)
self.assertEqual(self.model, model)
desc2 = model.to_dict()
self.assertEqual(desc, desc2)
def test_to_dict(self):
desc = self.model.to_dict()
model = cubes.create_model(desc)
self.assertEqual(self.model.dimensions, model.dimensions)
self.assertEqual(self.model.cubes, model.cubes)
self.assertEqual(self.model, model)
desc2 = model.to_dict()
self.assertEqual(desc, desc2)
def setUp(self):
desc = {
"cubes": [{
"name": "test",
"dimensions": ["date"],
"mappings": {
"date.day": {
"column": "test_date",
"extract": "day"
},
"date.month": {
"column": "test_date",
"extract": "month"
},
"date.year": {
"column": "test_date",
"extract": "year"
},
}
}],
"dimensions": [{
"name": "date",
"levels": ["month", "year", "day"]
}]
}
self.model = cubes.create_model(desc)
engine = sqlalchemy.create_engine('sqlite://')
connection = engine.connect()
metadata = sqlalchemy.MetaData(bind=engine)
table = Table(
'test',
metadata,
Column('test_date', Date),
)
table.create()
self.cube = self.model.cube("test")
self.browser = SnowflakeBrowser(self.cube, connectable=connection)
def test_model_from_dictionary(self):
model_dict = self.model.to_dict()
new_model = cubes.create_model(model_dict)
new_model_dict = new_model.to_dict()
# Break-down comparison to see where the difference is
self.assertEqual(model_dict.keys(), new_model_dict.keys(), 'model dictionaries should have same keys')
for key in model_dict.keys():
old_value = model_dict[key]
new_value = new_model_dict[key]
# self.assertEqual(type(old_value), type(new_value), "model part '%s' type should be the same" % key)
self.assertEqual(type(old_value), type(new_value))
if type(old_value) == dict:
self.assertDictEqual(old_value, new_value, "model part '%s' should be the same" % key)
pass
elif type(old_value) == list:
self.assertListEqual(old_value, new_value)
else:
self.assertEqual(old_value, new_value, "model part '%s' should be the same (type: %s)" % (key, type(old_value)))
self.assertDictEqual(model_dict, new_model_dict, 'model dictionaries should be the same')
def setUp(self):
model = {
"cubes": [
{
"name":"cube",
"dimensions": ["date"],
"joins": [
{"master":"date_id", "detail":"dim_date.id"}
]
}
],
"dimensions": [
{
"name": "date",
"levels": [
{"name":"year"},
{"name":"quarter"},
{"name":"month"},
{"name":"week"},
{"name":"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", "day"]}
],
"default_hierarchy_name": "ymd"
}
]
}
self.model = cubes.create_model(model)
engine = create_engine("sqlite:///")
metadata = MetaData(bind=engine)
d_table = Table("dim_date", metadata,
Column('id', Integer, primary_key=True),
Column('year', Integer),
Column('quarter', Integer),
Column('month', Integer),
Column('week', Integer),
Column('day', Integer))
f_table = Table("ft_cube", metadata,
Column('id', Integer, primary_key=True),
Column('date_id', Integer))
metadata.create_all()
start_date = datetime.date(2000, 1, 1)
end_date = datetime.date(2001, 1,1)
delta = datetime.timedelta(1)
date = start_date
d_insert = d_table.insert()
f_insert = f_table.insert()
i = 1
while date < end_date:
record = {
"id": int(date.strftime('%Y%m%d')),
"year": date.year,
"quarter": (date.month-1)//3+1,
"month": date.month,
"week": int(date.strftime("%U")),
"day": date.day
}
engine.execute(d_insert.values(record))
# For each date insert one fact record
record = {"id": i,
"date_id": record["id"]
}
engine.execute(f_insert.values(record))
date = date + delta
i += 1
self.cube = self.model.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
connectable=engine,
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def setUp(self):
model_desc = {
"cubes": [
{
"name": "sales",
"measures": [
{"name":"amount", "aggregations":["sum", "min"]},
"discount"
],
"dimensions" : ["date", "flag", "product"],
"details": ["fact_detail1", "fact_detail2"],
"joins": [
{"master": "sales.date_id", "detail":"dim_date.id"},
{"master": "sales.product_id", "detail":"dim_product.id"},
{"master": "sales.category_id", "detail":"dim_category.id"}
],
"mappings":{
"product.name": "dim_product.product_name",
"product.category": "dim_product.category_id",
"product.category_name.en": "dim_category.category_name_en",
"product.category_name.sk": "dim_category.category_name_sk",
"product.subcategory": "dim_category.subcategory_id",
"product.subcategory_name.en": "dim_category.subcategory_name_en",
"product.subcategory_name.sk": "dim_category.subcategory_name_sk"
}
}
],
"dimensions" : [
{
"name": "date",
"levels": [
{ "name": "year", "attributes": ["year"] },
{ "name": "month", "attributes":
["month", "month_name", "month_sname"] },
{ "name": "day", "attributes": ["id", "day"] }
],
"hierarchy": ["year", "month", "day"]
},
{ "name": "flag" },
{ "name": "product",
"levels": [
{"name": "category",
"attributes": ["category",
{"name": "category_name", "locales": ["en", "sk"] }
]
},
{"name": "subcategory",
"attributes": ["subcategory",
{"name": "subcategory_name", "locales": ["en", "sk"] }
]
},
{ "name": "product",
"attributes": [ "id",
{"name": "name"}
],
}
]
}
]
}
engine = sqlalchemy.create_engine('sqlite://')
self.connection = engine.connect()
metadata = sqlalchemy.MetaData()
metadata.bind = engine
table = Table('sales', metadata,
Column('id', Integer, primary_key=True),
Column('amount', Float),
Column('discount', Float),
Column('fact_detail1', String),
Column('fact_detail2', String),
Column('flag', String),
Column('date_id', Integer),
Column('product_id', Integer),
Column('category_id', Integer)
)
table = Table('dim_date', metadata,
Column('id', Integer, primary_key=True),
Column('day', Integer),
Column('month', Integer),
Column('month_name', String),
Column('month_sname', String),
Column('year', Integer)
)
table = Table('dim_product', metadata,
Column('id', Integer, primary_key=True),
Column('category_id', Integer),
Column('product_name', String),
)
table = Table('dim_category', metadata,
Column('id', Integer, primary_key=True),
Column('category_name_en', String),
Column('category_name_sk', String),
Column('subcategory_id', Integer),
Column('subcategory_name_en', String),
Column('subcategory_name_sk', String)
)
metadata.create_all(engine)
self.metadata = metadata
self.model = cubes.create_model(model_desc)
self.cube = self.model.cube("sales")
self.browser = SnowflakeBrowser(self.cube,connectable=self.connection,
dimension_prefix="dim_")
self.browser.debug = True
self.cube.fact = 'sales'
self.mapper = self.browser.mapper
def setUp(self):
model = {
"cubes": [{
"name": "cube",
"dimensions": ["date"],
"joins": [{
"master": "date_id",
"detail": "dim_date.id"
}]
}],
"dimensions": [{
"name":
"date",
"levels": [{
"name": "year"
}, {
"name": "quarter"
}, {
"name": "month"
}, {
"name": "week"
}, {
"name": "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", "day"]
}],
"default_hierarchy_name":
"ymd"
}]
}
self.model = cubes.create_model(model)
engine = create_engine("sqlite:///")
metadata = MetaData(bind=engine)
d_table = Table("dim_date", metadata,
Column('id', Integer, primary_key=True),
Column('year', Integer), Column('quarter', Integer),
Column('month', Integer), Column('week', Integer),
Column('day', Integer))
f_table = Table("ft_cube", metadata,
Column('id', Integer, primary_key=True),
Column('date_id', Integer))
metadata.create_all()
start_date = datetime.date(2000, 1, 1)
end_date = datetime.date(2001, 1, 1)
delta = datetime.timedelta(1)
date = start_date
d_insert = d_table.insert()
f_insert = f_table.insert()
i = 1
while date < end_date:
record = {
"id": int(date.strftime('%Y%m%d')),
"year": date.year,
"quarter": (date.month - 1) // 3 + 1,
"month": date.month,
"week": int(date.strftime("%U")),
"day": date.day
}
engine.execute(d_insert.values(record))
# For each date insert one fact record
record = {"id": i, "date_id": record["id"]}
engine.execute(f_insert.values(record))
date = date + delta
i += 1
self.cube = self.model.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
connectable=engine,
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def setUp(self):
model_desc = {
"cubes": [{
"name":
"sales",
"measures": [{
"name": "amount",
"aggregations": ["sum", "min"]
}, "discount"],
"dimensions": ["date", "flag", "product"],
"details": ["fact_detail1", "fact_detail2"],
"joins": [
{
"master": "sales.date_id",
"detail": "dim_date.id"
},
{
"master": "sales.category_id",
"detail": "dim_category.id"
},
{
"master": "sales.product_id",
"detail": "dim_product.id"
},
],
"mappings": {
"product.name":
"dim_product.product_name",
"product.category":
"dim_product.category_id",
"product.category_name.en":
"dim_category.category_name_en",
"product.category_name.sk":
"dim_category.category_name_sk",
"product.subcategory":
"dim_category.subcategory_id",
"product.subcategory_name.en":
"dim_category.subcategory_name_en",
"product.subcategory_name.sk":
"dim_category.subcategory_name_sk"
}
}],
"dimensions": [{
"name":
"date",
"levels": [{
"name": "year",
"attributes": ["year"]
}, {
"name": "month",
"attributes": ["month", "month_name", "month_sname"]
}, {
"name": "day",
"attributes": ["id", "day"]
}],
"hierarchy": ["year", "month", "day"]
}, {
"name": "flag"
}, {
"name":
"product",
"levels": [{
"name":
"category",
"attributes": [
"category", {
"name": "category_name",
"locales": ["en", "sk"]
}
]
}, {
"name":
"subcategory",
"attributes": [
"subcategory", {
"name": "subcategory_name",
"locales": ["en", "sk"]
}
]
}, {
"name": "product",
"attributes": ["id", {
"name": "name"
}],
}]
}]
}
engine = sqlalchemy.create_engine('sqlite://')
self.connection = engine.connect()
metadata = sqlalchemy.MetaData()
metadata.bind = engine
table = Table('sales', metadata, Column('id',
Integer,
primary_key=True),
Column('amount', Float), Column('discount', Float),
Column('fact_detail1', String),
Column('fact_detail2', String), Column('flag', String),
Column('date_id', Integer),
Column('product_id', Integer),
Column('category_id', Integer))
table = Table('dim_date', metadata,
Column('id', Integer, primary_key=True),
Column('sale_date', Date), Column('day', Integer),
Column('month', Integer), Column('month_name', String),
Column('month_sname', String), Column('year', Integer))
table = Table(
'dim_product',
metadata,
Column('id', Integer, primary_key=True),
Column('category_id', Integer),
Column('product_name', String),
)
table = Table('dim_category', metadata,
Column('id', Integer, primary_key=True),
Column('category_name_en', String),
Column('category_name_sk', String),
Column('subcategory_id', Integer),
Column('subcategory_name_en', String),
Column('subcategory_name_sk', String))
metadata.create_all(engine)
self.metadata = metadata
self.model = cubes.create_model(model_desc)
self.cube = self.model.cube("sales")
self.browser = SnowflakeBrowser(self.cube,
connectable=self.connection,
dimension_prefix="dim_")
self.browser.debug = True
self.cube.fact = 'sales'
self.mapper = self.browser.mapper
def setUp(self):
model_desc = {
"cubes": [
{
"name": "sales",
"measures": [
{"name":"amount", "aggregations":["sum", "min"]},
"discount"
],
"dimensions" : ["date", "flag", "product"],
"details": ["fact_detail1", "fact_detail2"],
"joins": [
{"master": "sales.date_id", "detail":"dim_date.id"},
{"master": "sales.product_id", "detail":"dim_product.id"},
{"master": "sales.category_id", "detail":"dim_category.id"}
],
"mappings":{
"product.name": "dim_product.product_name",
"product.category": "dim_product.category_id",
"product.category_name.en": "dim_category.category_name_en",
"product.category_name.sk": "dim_category.category_name_sk",
"product.subcategory": "dim_category.subcategory_id",
"product.subcategory_name.en": "dim_category.subcategory_name_en",
"product.subcategory_name.sk": "dim_category.subcategory_name_sk"
}
}
],
"dimensions" : [
{
"name": "date",
"levels": [
{ "name": "year", "attributes": ["year"] },
{ "name": "month", "attributes":
["month", "month_name", "month_sname"] },
{ "name": "day", "attributes": ["id", "day"] }
],
"hierarchy": ["year", "month", "day"]
},
{ "name": "flag" },
{ "name": "product",
"levels": [
{"name": "category",
"attributes": ["category",
{"name": "category_name", "locales": ["en", "sk"] }
]
},
{"name": "subcategory",
"attributes": ["subcategory",
{"name": "subcategory_name", "locales": ["en", "sk"] }
]
},
{ "name": "product",
"attributes": [ "id",
{"name": "name"}
],
}
]
}
]
}
self.model = cubes.create_model(model_desc)
self.cube = self.model.cube("sales")
self.mapper = cubes.SnowflakeMapper(self.cube,dimension_prefix='dim_')
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk"
}
model_description = {
"dimensions": [
{ "name": "category"},
{ "name": "line_item"},
{ "name": "year"}
],
"cubes": [
{
"name": "irbd_balance",
"dimensions": ["category", "line_item", "year"],
"measures": ["amount"]
}
]
}
model = cubes.create_model(model_description)
cube = model.cube("irbd_balance")
# 4. Create a browser and get a cell representing the whole cube (all data)
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
cell = browser.full_cube()
# 5. Compute the aggregate
# Measure fields of aggregation result have aggregation suffix, currenlty only _sum. Also
# a total record count within the cell is included as record_count
result = browser.aggregate(cell)
def setUp(self):
model_desc = {
"cubes": [{
"name":
"sales",
"measures": [{
"name": "amount",
"aggregations": ["sum", "min"]
}, "discount"],
"dimensions": ["date", "flag", "product"],
"details": ["fact_detail1", "fact_detail2"],
"joins": [{
"master": "sales.date_id",
"detail": "dim_date.id"
}, {
"master": "sales.product_id",
"detail": "dim_product.id"
}, {
"master": "sales.category_id",
"detail": "dim_category.id"
}],
"mappings": {
"product.name":
"dim_product.product_name",
"product.category":
"dim_product.category_id",
"product.category_name.en":
"dim_category.category_name_en",
"product.category_name.sk":
"dim_category.category_name_sk",
"product.subcategory":
"dim_category.subcategory_id",
"product.subcategory_name.en":
"dim_category.subcategory_name_en",
"product.subcategory_name.sk":
"dim_category.subcategory_name_sk"
}
}],
"dimensions": [{
"name":
"date",
"levels": [{
"name": "year",
"attributes": ["year"]
}, {
"name": "month",
"attributes": ["month", "month_name", "month_sname"]
}, {
"name": "day",
"attributes": ["id", "day"]
}],
"hierarchy": ["year", "month", "day"]
}, {
"name": "flag"
}, {
"name":
"product",
"levels": [{
"name":
"category",
"attributes": [
"category", {
"name": "category_name",
"locales": ["en", "sk"]
}
]
}, {
"name":
"subcategory",
"attributes": [
"subcategory", {
"name": "subcategory_name",
"locales": ["en", "sk"]
}
]
}, {
"name": "product",
"attributes": ["id", {
"name": "name"
}],
}]
}]
}
self.model = cubes.create_model(model_desc)
self.cube = self.model.cube("sales")
self.mapper = cubes.SnowflakeMapper(self.cube, dimension_prefix='dim_')
self.mapper.mappings = {
"product.name": "product.product_name",
"product.category": "product.category_id",
"subcategory.name.en": "subcategory.subcategory_name_en",
"subcategory.name.sk": "subcategory.subcategory_name_sk"
}