def setUp(self):
super(HierarchyTestCase, self).setUp()
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
workspace = self.create_workspace({"engine": engine}, "hierarchy.json")
self.cube = workspace.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
store=workspace.get_store("default"),
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def setUp(self):
super(HierarchyTestCase, self).setUp()
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
workspace = self.create_workspace({"engine": engine},
"hierarchy.json")
self.cube = workspace.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
store=workspace.get_store("default"),
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def setUp(self):
super(StarSQLTestCase, self).setUp()
self.engine = sqlalchemy.create_engine('sqlite://')
metadata = sqlalchemy.MetaData(bind=self.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))
self.metadata = metadata
self.metadata.create_all(self.engine)
self.workspace = self.create_workspace({"engine": self.engine},
"sql_star_test.json")
# self.workspace = Workspace()
# self.workspace.register_default_store("sql", engine=self.engine)
# self.workspace.import_model()
self.cube = self.workspace.cube("sales")
store = self.workspace.get_store("default")
self.browser = SnowflakeBrowser(self.cube,
store=store,
dimension_prefix="dim_")
self.browser.debug = True
self.mapper = self.browser.mapper
class HierarchyTestCase(CubesTestCaseBase):
def setUp(self):
super(HierarchyTestCase, self).setUp()
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
workspace = self.create_workspace({"engine": engine},
"hierarchy.json")
self.cube = workspace.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
store=workspace.get_store("default"),
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def test_cell(self):
cell = Cell(self.cube)
result = self.browser.aggregate(cell)
self.assertEqual(366, result.summary["fact_count"])
cut = PointCut("date", [2000, 2])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(29, result.summary["fact_count"])
cut = PointCut("date", [2000, 2], hierarchy="ywd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(7, result.summary["fact_count"])
cut = PointCut("date", [2000, 1], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(91, result.summary["fact_count"])
def test_drilldown(self):
cell = Cell(self.cube)
result = self.browser.aggregate(cell, drilldown=["date"])
self.assertEqual(1, result.total_cell_count)
result = self.browser.aggregate(cell, drilldown=["date:month"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "month")])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "day")])
self.assertEqual(366, result.total_cell_count)
# Test year-quarter-month-day
hier = self.cube.dimension("date").hierarchy("yqmd")
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", "day")])
self.assertEqual(366, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", "quarter")])
self.assertEqual(4, result.total_cell_count)
def test_range_drilldown(self):
cut = RangeCut("date", [2000, 1], [2000,3])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell, drilldown=["date"])
# This should test that it does not drilldown on range
self.assertEqual(1, result.total_cell_count)
def test_implicit_level(self):
cut = PointCut("date", [2000])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell, drilldown=["date"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell, drilldown=["date:month"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "month")])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "day")])
self.assertEqual(366, result.total_cell_count)
def test_hierarchy_compatibility(self):
cut = PointCut("date", [2000])
cell = Cell(self.cube, [cut])
with self.assertRaises(HierarchyError):
self.browser.aggregate(cell, drilldown=[("date", "yqmd", None)])
cut = PointCut("date", [2000], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", None)])
self.assertEqual(4, result.total_cell_count)
cut = PointCut("date", [2000], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
self.assertRaises(HierarchyError, self.browser.aggregate,
cell, drilldown=[("date", "ywd", None)])
cut = PointCut("date", [2000], hierarchy="ywd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "ywd", None)])
self.assertEqual(54, result.total_cell_count)
class HierarchyTestCase(CubesTestCaseBase):
def setUp(self):
super(HierarchyTestCase, self).setUp()
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
workspace = self.create_workspace({"engine": engine}, "hierarchy.json")
self.cube = workspace.cube("cube")
self.browser = SnowflakeBrowser(self.cube,
store=workspace.get_store("default"),
dimension_prefix="dim_",
fact_prefix="ft_")
self.browser.debug = True
def test_cell(self):
cell = Cell(self.cube)
result = self.browser.aggregate(cell)
self.assertEqual(366, result.summary["fact_count"])
cut = PointCut("date", [2000, 2])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(29, result.summary["fact_count"])
cut = PointCut("date", [2000, 2], hierarchy="ywd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(7, result.summary["fact_count"])
cut = PointCut("date", [2000, 1], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(91, result.summary["fact_count"])
def test_drilldown(self):
cell = Cell(self.cube)
result = self.browser.aggregate(cell, drilldown=["date"])
self.assertEqual(1, result.total_cell_count)
result = self.browser.aggregate(cell, drilldown=["date:month"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "month")])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "day")])
self.assertEqual(366, result.total_cell_count)
# Test year-quarter-month-day
hier = self.cube.dimension("date").hierarchy("yqmd")
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", "day")])
self.assertEqual(366, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", "quarter")
])
self.assertEqual(4, result.total_cell_count)
def test_range_drilldown(self):
cut = RangeCut("date", [2000, 1], [2000, 3])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell, drilldown=["date"])
# This should test that it does not drilldown on range
self.assertEqual(1, result.total_cell_count)
def test_implicit_level(self):
cut = PointCut("date", [2000])
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell, drilldown=["date"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell, drilldown=["date:month"])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "month")])
self.assertEqual(12, result.total_cell_count)
result = self.browser.aggregate(cell,
drilldown=[("date", None, "day")])
self.assertEqual(366, result.total_cell_count)
def test_hierarchy_compatibility(self):
cut = PointCut("date", [2000])
cell = Cell(self.cube, [cut])
with self.assertRaises(HierarchyError):
self.browser.aggregate(cell, drilldown=[("date", "yqmd", None)])
cut = PointCut("date", [2000], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", None)])
self.assertEqual(4, result.total_cell_count)
cut = PointCut("date", [2000], hierarchy="yqmd")
cell = Cell(self.cube, [cut])
self.assertRaises(HierarchyError,
self.browser.aggregate,
cell,
drilldown=[("date", "ywd", None)])
cut = PointCut("date", [2000], hierarchy="ywd")
cell = Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "ywd", None)])
self.assertEqual(54, result.total_cell_count)