def test_mdx_builder_with_calculated_member(self):
mdx = MdxBuilder.from_cube(cube="Cube").with_member(
CalculatedMember.avg(
dimension="Period",
hierarchy="Period",
element="AVG 2016",
cube="Cube",
mdx_set=MdxHierarchySet.children(member=Member.of("Period", "2016")),
mdx_tuple=MdxTuple.of(Member.of("Dim1", "Total Dim1"),
Member.of("Dim2", "Total Dim2")))) \
.rows_non_empty() \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("DIM1", "DIM1")) \
.columns_non_empty() \
.add_member_tuple_to_columns(Member.of("Period", "AVG 2016")) \
.where("[Dim2].[Total Dim2]") \
.to_mdx()
self.assertEqual(
"WITH\r\n"
"MEMBER [PERIOD].[PERIOD].[AVG2016] AS AVG({[PERIOD].[PERIOD].[2016].CHILDREN},"
"[CUBE].([DIM1].[DIM1].[TOTALDIM1],[DIM2].[DIM2].[TOTALDIM2]))\r\n"
"SELECT\r\n"
"NON EMPTY {([PERIOD].[PERIOD].[AVG2016])} ON 0,\r\n"
"NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([DIM1].[DIM1])},0)} ON 1\r\n"
"FROM [CUBE]\r\n"
"WHERE ([DIM2].[DIM2].[TOTALDIM2])",
mdx)
def test_mdx_builder_multi_fail_combine_tuples_sets_on_axis(self):
with pytest.raises(ValueError):
MdxBuilder.from_cube("CUBE") \
.rows_non_empty() \
.add_member_tuple_to_axis(0, Member.of("Dim1", "Dim1", "Elem1")) \
.add_hierarchy_set_to_axis(0, MdxHierarchySet.all_leaves("Dim1")) \
.to_mdx()
def test_mdx_hierarchy_set_generate_attribute_to_member(self):
hierarchy_set = MdxHierarchySet.all_leaves("Store").generate_attribute_to_member(
attribute="Manager",
dimension="Manager")
self.assertEqual(hierarchy_set.dimension, "MANAGER")
self.assertEqual(
"{GENERATE("
"{TM1FILTERBYLEVEL({TM1SUBSETALL([STORE].[STORE])},0)},"
"{STRTOMEMBER('[MANAGER].[MANAGER].[' + [STORE].[STORE].CURRENTMEMBER.PROPERTIES(\"Manager\") + ']')})}",
hierarchy_set.to_mdx())
def test_mdx_builder_multi_no_where(self):
mdx = MdxBuilder.from_cube("cube") \
.rows_non_empty() \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Dim1")) \
.columns_non_empty() \
.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Dim2", "Elem2"))) \
.to_mdx()
self.assertEqual(
"SELECT\r\n"
"NON EMPTY {[dim2].[dim2].[elem2]} ON 0,\r\n"
"NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([dim1].[dim1])},0)} ON 1\r\n"
"FROM [cube]", mdx)
def test_mdx_builder_simple(self):
mdx = MdxBuilder.from_cube("CUBE") \
.rows_non_empty() \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Dim1")) \
.columns_non_empty() \
.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Dim2", "Elem2"))) \
.where(Member.of("Dim3", "Elem3"), Member.of("Dim4", "Elem4")) \
.to_mdx()
self.assertEqual(
"SELECT\r\n"
"NON EMPTY {[DIM2].[DIM2].[ELEM2]} ON 0,\r\n"
"NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([DIM1].[DIM1])},0)} ON 1\r\n"
"FROM [CUBE]\r\n"
"WHERE ([DIM3].[DIM3].[ELEM3],[DIM4].[DIM4].[ELEM4])", mdx)
def test_mdx_builder_tm1_ignore_bad_tuples(self):
mdx = MdxBuilder.from_cube("cube") \
.tm1_ignore_bad_tuples() \
.rows_non_empty() \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Dim1")) \
.columns_non_empty() \
.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Dim2", "Elem2"))) \
.where(Member.of("Dim3", "Elem3"), Member.of("Dim4", "Elem4")) \
.to_mdx()
self.assertEqual(
"SELECT\r\n"
"NON EMPTY TM1IGNORE_BADTUPLES {[dim2].[dim2].[elem2]} ON 0,\r\n"
"NON EMPTY TM1IGNORE_BADTUPLES {TM1FILTERBYLEVEL({TM1SUBSETALL([dim1].[dim1])},0)} ON 1\r\n"
"FROM [cube]\r\n"
"WHERE ([dim3].[dim3].[elem3],[dim4].[dim4].[elem4])", mdx)
def test_mdx_build_with_multi_calculated_member(self):
mdx = MdxBuilder.from_cube(cube="Cube").with_member(
CalculatedMember.avg(
dimension="Period",
hierarchy="Period",
element="AVG 2016",
cube="Cube",
mdx_set=MdxHierarchySet.children(member=Member.of("Period", "2016")),
mdx_tuple=MdxTuple.of(Member.of("Dim1", "Total Dim1"),
Member.of("Dim2", "Total Dim2")))) \
.with_member(
CalculatedMember.sum(
dimension="Period",
hierarchy="Period",
element="SUM 2016",
cube="Cube",
mdx_set=MdxHierarchySet.children(member=Member.of("Period", "2016")),
mdx_tuple=MdxTuple.of(Member.of("Dim1", "Total Dim1"),
Member.of("Dim2", "Total Dim2")))) \
.rows_non_empty() \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("dim1", "dim1")) \
.columns_non_empty() \
.add_hierarchy_set_to_column_axis(
MdxHierarchySet.members(members=[Member.of("Period", "AVG 2016"), Member.of("Period", "SUM 2016")])) \
.where(Member.of("Dim2", "Total Dim2")) \
.to_mdx()
self.assertEqual(
"WITH\r\n"
"MEMBER [period].[period].[avg2016] AS AVG({[period].[period].[2016].CHILDREN},"
"[cube].([dim1].[dim1].[totaldim1],[dim2].[dim2].[totaldim2]))\r\n"
"MEMBER [period].[period].[sum2016] AS SUM({[period].[period].[2016].CHILDREN},"
"[cube].([dim1].[dim1].[totaldim1],[dim2].[dim2].[totaldim2]))\r\n"
"SELECT\r\n"
"NON EMPTY {[period].[period].[avg2016],[period].[period].[sum2016]} ON 0,\r\n"
"NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([dim1].[dim1])},0)} ON 1\r\n"
"FROM [cube]\r\n"
"WHERE ([dim2].[dim2].[totaldim2])", mdx)
def test_mdx_hierarchy_set_all_leaves(self):
hierarchy_set = MdxHierarchySet.all_leaves("Dimension")
self.assertEqual("{TM1FILTERBYLEVEL({TM1SUBSETALL([DIMENSION].[DIMENSION])},0)}", hierarchy_set.to_mdx())
