def test_next_drill_down(self):
cell = self.full_cube
query = CubeQuery(cell, self.view)
query.drilldown = ["date"]
query.prepare()
stmt = query.drilldown_statement
s = str(stmt)
self.assertRegexpMatches(s, r'date\.year')
self.assertNotRegexpMatches(s, r'date\.month')
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010]))
query = CubeQuery(cell, self.view)
query.drilldown = ["date"]
query.prepare()
stmt = query.drilldown_statement
s = str(stmt)
self.assertRegexpMatches(s, r'date\.year')
self.assertRegexpMatches(s, r'date\.month')
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010, 4]))
query = CubeQuery(cell, self.view)
query.drilldown = ["date"]
self.assertRaisesRegexp(ValueError, "Unable to drill-down.*last level",
query.prepare)
def test_cut_details(self):
cut = cubes.PointCut("date", [2012])
details = self.browser.cut_details(cut)
self.assertEqual([{
"date.year": 2012,
"_key": 2012,
"_label": 2012
}], details)
cut = cubes.PointCut("date", [2013])
details = self.browser.cut_details(cut)
self.assertEqual(None, details)
cut = cubes.PointCut("date", [2012, 3])
details = self.browser.cut_details(cut)
self.assertEqual([{
"date.year": 2012,
"_key": 2012,
"_label": 2012
}, {
"date.month_name": "March",
"date.month_sname": "Mar",
"date.month": 3,
"_key": 3,
"_label": "March"
}], details)
def test_hierarchy_compatibility(self):
cut = cubes.PointCut("date", [2000])
cell = cubes.Cell(self.cube, [cut])
self.assertRaises(HierarchyError,
self.browser.aggregate,
cell,
drilldown=[("date", "yqmd", None)])
cut = cubes.PointCut("date", [2000], hierarchy="yqmd")
cell = cubes.Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "yqmd", None)])
self.assertEqual(4, result.total_cell_count)
cut = cubes.PointCut("date", [2000], hierarchy="yqmd")
cell = cubes.Cell(self.cube, [cut])
self.assertRaises(HierarchyError,
self.browser.aggregate,
cell,
drilldown=[("date", "ywd", None)])
cut = cubes.PointCut("date", [2000], hierarchy="ywd")
cell = cubes.Cell(self.cube, [cut])
result = self.browser.aggregate(cell,
drilldown=[("date", "ywd", None)])
self.assertEqual(54, result.total_cell_count)
def test_cell_details(self):
cell = cubes.Cell(self.cube, [cubes.PointCut("date", [2012])])
details = self.browser.cell_details(cell)
self.assertEqual(1, len(details))
cell = cubes.Cell(self.cube, [cubes.PointCut("product", [10])])
details = self.browser.cell_details(cell)
self.assertEqual(1, len(details))
def test_multi_slice(self):
full_cube = self.browser.full_cube()
cuts_list = (cubes.PointCut("date",
[2010]), cubes.PointCut("cpv", [50, 20]),
cubes.PointCut("supplier", [1234]))
cell_list = full_cube.multi_slice(cuts_list)
self.assertEqual(3, len(cell_list.cuts))
self.assertRaises(CubesError, full_cube.multi_slice, {})
def test_get_cell_dimension_cut(self):
full_cube = self.browser.full_cube()
cell = full_cube.slice(cubes.PointCut("date", [2010]))
cell = cell.slice(cubes.PointCut("supplier", [1234]))
cut = cell.cut_for_dimension("date")
self.assertEqual(str(cut.dimension), "date")
self.assertRaises(cubes.NoSuchDimensionError, cell.cut_for_dimension,
"someunknown")
cut = cell.cut_for_dimension("cpv")
self.assertEqual(cut, None)
def test_cutting(self):
full_cube = self.browser.full_cube()
self.assertEqual(self.cube, full_cube.cube)
self.assertEqual(0, len(full_cube.cuts))
cell = full_cube.slice(cubes.PointCut("date", [2010]))
self.assertEqual(1, len(cell.cuts))
cell = cell.slice(cubes.PointCut("supplier", [1234]))
cell = cell.slice(cubes.PointCut("cpv", [50, 20]))
self.assertEqual(3, len(cell.cuts))
self.assertEqual(self.cube, cell.cube)
# Adding existing slice should result in changing the slice properties
cell = cell.slice(cubes.PointCut("date", [2011]))
self.assertEqual(3, len(cell.cuts))
def test_cell(self):
cell = cubes.Cell(self.cube)
result = self.browser.aggregate(cell)
self.assertEqual(366, result.summary["record_count"])
cut = cubes.PointCut("date", [2000, 2])
cell = cubes.Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(29, result.summary["record_count"])
cut = cubes.PointCut("date", [2000, 2], hierarchy="ywd")
cell = cubes.Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(7, result.summary["record_count"])
cut = cubes.PointCut("date", [2000, 1], hierarchy="yqmd")
cell = cubes.Cell(self.cube, [cut])
result = self.browser.aggregate(cell)
self.assertEqual(91, result.summary["record_count"])
def test_aggregate_point(self):
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.summary_statement
s = str(stmt)
s = re.sub(r'\n', ' ', s)
self.assertRegexpMatches(s, r'WHERE')
self.assertRegexpMatches(s, r'sum(.*amount.*) AS amount_sum')
self.assertRegexpMatches(s, r'view\."date\.year" =')
self.assertRegexpMatches(s, r'GROUP BY .*date\.year')
self.assertNotRegexpMatches(s, r'GROUP BY .*date\.month')
self.assertRegexpMatches(s, r'SELECT.*date\.year.*FROM')
self.assertNotRegexpMatches(s, r'SELECT.*date\.month.*FROM')
self.assertNotRegexpMatches(s, r'cls')
self.execute(stmt)
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010, 4]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.summary_statement
s = str(stmt)
self.assertRegexpMatches(s, r'GROUP BY .*date\.year')
self.assertRegexpMatches(s, r'GROUP BY .*date\.month')
self.assertRegexpMatches(s, r'SELECT .*date\.year')
self.assertRegexpMatches(s, r'SELECT.*date\.month')
self.assertRegexpMatches(s, r'SELECT.*date\.month_name')
self.assertNotRegexpMatches(s, r'cls')
self.execute(stmt)
cell = self.full_cube.slice(cubes.PointCut(self.class_dim, [1]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.summary_statement
s = str(stmt)
self.assertRegexpMatches(s, r'cls')
self.assertRegexpMatches(s, r'group_id')
self.assertRegexpMatches(s, r'group_desc')
self.assertNotRegexpMatches(s, r'class')
self.execute(stmt)
def test_fact_with_conditions(self):
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.facts_statement
s = str(stmt)
self.assertRegexpMatches(s, 'WHERE')
self.assertRegexpMatches(s, 'view\."date\.year" =')
self.assertNotRegexpMatches(s, 'view\."date\.month" =')
self.assertNotRegexpMatches(s, 'view\."class\.[^=]*"=')
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010, 4]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.facts_statement
s = str(stmt)
self.assertRegexpMatches(s, 'WHERE')
self.assertRegexpMatches(s, r'view\."date\.year" =')
self.assertRegexpMatches(s, r'view\."date\.month" =')
def test_cell_details(self):
cell = cubes.Cell(self.cube, [cubes.PointCut("date", [2012])])
details = self.browser.cell_details(cell)
self.assertEqual(1, len(details))
self.assertEqual([[{
"date.year": 2012,
"_key": 2012,
"_label": 2012
}]], details)
cell = cubes.Cell(self.cube, [cubes.PointCut("product", [10])])
details = self.browser.cell_details(cell)
self.assertEqual(1, len(details))
self.assertEqual([[{
"product.category": 10,
"product.category_name": "Things",
"_key": 10,
"_label": "Things"
}]], details)
cell = cubes.Cell(
self.cube,
[cubes.PointCut("date", [2012]),
cubes.PointCut("product", [10])])
facts = list(self.browser.values(cell, "product", 1))
details = self.browser.cell_details(cell)
self.assertEqual(2, len(details))
self.assertEqual([[{
"date.year": 2012,
"_key": 2012,
"_label": 2012
}],
[{
"product.category": 10,
"product.category_name": "Things",
"_key": 10,
"_label": "Things"
}]], details)
def test_fact_query(self):
query = CubeQuery(self.full_cube, self.view)
query.prepare()
stmt = query.fact_statement(1)
s = str(stmt)
self.assertRegexpMatches(s, 'view\.id =')
cell = self.full_cube.slice(cubes.PointCut(self.date_dim, [2010]))
query = CubeQuery(cell, self.view)
query.prepare()
stmt = query.fact_statement(1)
s = str(stmt)
self.assertNotRegexpMatches(s, 'view\."date\.year" =')
def test_implicit_level(self):
cut = cubes.PointCut("date", [2000])
cell = cubes.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_explicit_drill_down(self):
cell = self.full_cube
query = CubeQuery(cell, self.view)
query.drilldown = {"date": "year"}
query.prepare()
stmt = query.drilldown_statement
s = str(stmt)
self.assertRegexpMatches(s, r'date\.year')
self.assertNotRegexpMatches(s, r'date\.month')
cell = cell.slice(cubes.PointCut("date", [2010]))
query = CubeQuery(cell, self.view)
query.drilldown = {"date": "month"}
query.prepare()
stmt = query.drilldown_statement
s = str(stmt)
self.assertRegexpMatches(s, r'date\.year')
self.assertRegexpMatches(s, r'date\.month')
def test_cut_from_dict(self):
# d = {"type":"point", "path":[2010]}
# self.assertRaises(Exception, cubes.cut_from_dict, d)
d = {
"type": "point",
"path": [2010],
"dimension": "date",
"level_depth": 1,
"hierarchy": None
}
cut = cubes.cut_from_dict(d)
tcut = cubes.PointCut("date", [2010])
self.assertEqual(tcut, cut)
self.assertEqual(d, tcut.to_dict())
d = {
"type": "range",
"from": [2010],
"to": [2012, 10],
"dimension": "date",
"level_depth": 2,
"hierarchy": None
}
cut = cubes.cut_from_dict(d)
tcut = cubes.RangeCut("date", [2010], [2012, 10])
self.assertEqual(tcut, cut)
self.assertEqual(d, tcut.to_dict())
d = {
"type": "set",
"paths": [[2010], [2012, 10]],
"dimension": "date",
"level_depth": 2,
"hierarchy": None
}
cut = cubes.cut_from_dict(d)
tcut = cubes.SetCut("date", [[2010], [2012, 10]])
self.assertEqual(tcut, cut)
self.assertEqual(d, tcut.to_dict())
self.assertRaises(ArgumentError, cubes.cut_from_dict, {"type": "xxx"})
def test_coalesce_drilldown(self):
cell = cubes.Cell(self.cube)
dim = self.cube.dimension("date")
drilldown = {"date": "year"}
expected = {"date": [dim.level("year")]}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
drilldown = ["date"]
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
drilldown = {"date": "day"}
expected = {
"date": [dim.level("year"),
dim.level("month"),
dim.level("day")]
}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
# Try "next level"
cut = cubes.PointCut("date", [2010])
cell = cubes.Cell(self.cube, [cut])
drilldown = {"date": "year"}
expected = {"date": [dim.level("year")]}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
drilldown = ["date"]
expected = {"date": [dim.level("year"), dim.level("month")]}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
# Try with range cell
cut = cubes.RangeCut("date", [2009], [2010])
cell = cubes.Cell(self.cube, [cut])
drilldown = ["date"]
expected = {"date": [dim.level("year"), dim.level("month")]}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
drilldown = {"date": "year"}
expected = {"date": [dim.level("year")]}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
cut = cubes.RangeCut("date", [2009], [2010, 1])
cell = cubes.Cell(self.cube, [cut])
drilldown = ["date"]
expected = {
"date": [dim.level("year"),
dim.level("month"),
dim.level("day")]
}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
# Try "last level"
cut = cubes.PointCut("date", [2010, 1, 2])
cell = cubes.Cell(self.cube, [cut])
drilldown = {"date": "day"}
expected = {
"date": [dim.level("year"),
dim.level("month"),
dim.level("day")]
}
self.assertEqual(expected, coalesce_drilldown(cell, drilldown))
drilldown = ["date"]
expected = {"date": [dim.level("year"), dim.level("month")]}
self.assertRaises(ArgumentError, coalesce_drilldown, cell, drilldown)
print "%s%s: %d" % (indent, record[level_label], record["record_count"])
# Construct new path: current path with key attribute value appended
drill_path = path[:] + [record[level_key]]
# Get a new cell slice for current path
drill_down_cell = cell.slice(cubes.PointCut(dimension, drill_path))
# And do recursive drill-down
drill_down(drill_down_cell, dimension, drill_path)
# 2. Load model and get cube of our interest
model = cubes.load_model("models/model_03.json")
cube = model.cube("irbd_balance")
# 3. Create a browser
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
# Get whole cube
cell = browser.full_cube()
print "Drill down through all item levels:"
drill_down(cell, cube.dimension("item"))
print "Drill down through all item for year 2010:"
cell = cell.slice(cubes.PointCut("year", [2010]))
drill_down(cell, cube.dimension("item"))
def test_levels_from_drilldown(self):
cell = cubes.Cell(self.cube)
dim = self.cube.dimension("date")
l_year = dim.level("year")
l_month = dim.level("month")
l_day = dim.level("day")
drilldown = [("date", None, "year")]
expected = [(dim, [l_year])]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
drilldown = ["date"]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
drilldown = [("date", None, "year")]
expected = [(dim, [l_year])]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
# Try "next level"
cut = cubes.PointCut("date", [2010])
cell = cubes.Cell(self.cube, [cut])
drilldown = [("date", None, "year")]
expected = [(dim, [l_year])]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
drilldown = ["date"]
expected = [(dim, [l_year, l_month])]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
# Try with range cell
# cut = cubes.RangeCut("date", [2009], [2010])
# cell = cubes.Cell(self.cube, [cut])
# drilldown = ["date"]
# expected = [(dim, [l_year, l_month])]
# self.assertEqual(expected, cubes.levels_from_drilldown(cell, drilldown))
# drilldown = [("date", None, "year")]
# expected = [(dim, [l_year])]
# self.assertEqual(expected, cubes.levels_from_drilldown(cell, drilldown))
# cut = cubes.RangeCut("date", [2009], [2010, 1])
# cell = cubes.Cell(self.cube, [cut])
# drilldown = ["date"]
# expected = [(dim, [l_year, l_month, l_day])]
# self.assertEqual(expected, cubes.levels_from_drilldown(cell, drilldown))
# Try "last level"
cut = cubes.PointCut("date", [2010, 1, 2])
cell = cubes.Cell(self.cube, [cut])
drilldown = [("date", None, "day")]
expected = [(dim, [l_year, l_month, l_day])]
self.assertEqual(expected,
cubes.levels_from_drilldown(cell, drilldown))
drilldown = ["date"]
expected = [(dim, [l_year, l_month])]
self.assertRaises(HierarchyError, cubes.levels_from_drilldown, cell,
drilldown)
def drill_down(cell, dimension, path = []):
"""Drill-down and aggregate recursively through all levels of `dimension`.
This function is like recursively traversing directories on a file system and aggregating the
file sizes, for example.
:Attributes:
* cell - cube cell to drill-down
* dimension - dimension to be traversed through all levels
* path - current path of the `dimension`
Path is list of dimension points (keys) at each level. It is like file-system path.
"""
# Get dimension's default hierarchy. Cubes supports multiple hierarchies, for example for
# date you might have year-month-day or year-quarter-month-day. Most dimensions will
# have one hierarchy, thought.
hierarchy = dimension.hierarchy()
# Can we go deeper in the hierarchy? Base path is path to the most detailed element,
# to the leaf of a tree, to the fact.
if hierarchy.path_is_base(path):
return
# Get the next level in the hierarchy. ``levels_for_pat`` returns list of levels
# according to provided path. When ``drilldown`` is set to ``True`` then one
# more level is returned.
levels = hierarchy.levels_for_path(path,drilldown=True)
current_level = levels[-1]
# We need to know name of the level key attribute which contains a path component.
# If the model does not explicitly specify key attribute for the level, then first attribute
# will be used
level_key = dimension.attribute_reference(current_level.key)
# For prettier display, we get name of attribute which contains label to be displayed
# for the current level. If there is no label attribute, then key attribute is used.
level_label = dimension.attribute_reference(current_level.label_attribute)
# Just visual formatting - indentation based on path lenght.
indent = " " * len(path)
# We do the aggregation of the cell... Think of ``ls $CELL`` command in commandline, where `$CELL`
# is a directory name. In this function we can think of ``$CELL`` to be same as current working
# directory (pwd)
result = browser.aggregate(cell, drilldown=[dimension])
# ... and display the results
# print "%s==Level: %s==" % (indent, current_level.label)
for record in result.drilldown:
print "%s%s: %d" % (indent, record[level_label], record["record_count"])
# Construct new path: current path with key attribute value appended
drill_path = path[:] + [record[level_key]]
# Get a new cell slice for current path
drill_down_cell = cell.slice(cubes.PointCut(dimension, drill_path))
# And do recursive drill-down
drill_down(drill_down_cell, dimension, drill_path)
