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)