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 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" }