def test_mdx_hierarchy_set_intersect(self): hierarchy_set = MdxHierarchySet.member(Member.of("dimension", "element1")). \ intersect(MdxHierarchySet.member(Member.of("dimension", "element2"))) self.assertEqual( "{INTERSECT({[dimension].[dimension].[element1]},{[dimension].[dimension].[element2]})}", hierarchy_set.to_mdx())
def test_add_empty_set_to_axis_error(self): with pytest.raises(ValueError): mdx = MdxBuilder.from_cube("Cube") \ .add_hierarchy_set_to_column_axis(MdxHierarchySet.tm1_subset_all("Dimension1")) \ .add_hierarchy_set_to_axis(1, MdxHierarchySet.tm1_subset_all("Dimension2")) \ .add_empty_set_to_axis(1) \ .to_mdx()
def test_mdx_hierarchy_set_union(self): hierarchy_set = MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT1")). \ union(MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT2"))) self.assertEqual( "{UNION({[DIMENSION].[DIMENSION].[ELEMENT1]},{[DIMENSION].[DIMENSION].[ELEMENT2]})}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_tm1_drill_down_member_set_recursive(self): hierarchy_set = MdxHierarchySet.members([Member.of("DIMENSION", "ELEMENT")]).tm1_drill_down_member( other_set=MdxHierarchySet.members([Member.of("DIMENSION", "ELEMENT")]), recursive=True) self.assertEqual( "{TM1DRILLDOWNMEMBER({[DIMENSION].[DIMENSION].[ELEMENT]}, {[DIMENSION].[DIMENSION].[ELEMENT]}, RECURSIVE)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_except(self): hierarchy_set = MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT1")). \ except_(MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT2"))) self.assertEqual( "{EXCEPT({[DIMENSION].[DIMENSION].[ELEMENT1]},{[DIMENSION].[DIMENSION].[ELEMENT2]})}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_intersect(self): hierarchy_set = MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT1")). \ intersect(MdxHierarchySet.member(Member.of("DIMENSION", "ELEMENT2"))) self.assertEqual( "{INTERSECT({[DIMENSION].[DIMENSION].[ELEMENT1]},{[DIMENSION].[DIMENSION].[ELEMENT2]})}", hierarchy_set.to_mdx())
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_hierarchy_set_union(self): hierarchy_set = MdxHierarchySet.member(Member.of("dimension", "element1")). \ union(MdxHierarchySet.member(Member.of("dimension", "element2"))) self.assertEqual( "{UNION({[dimension].[dimension].[element1]},{[dimension].[dimension].[element2]})}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_except(self): hierarchy_set = MdxHierarchySet.member(Member.of("dimension", "element1")). \ except_(MdxHierarchySet.member(Member.of("dimension", "element2"))) self.assertEqual( "{EXCEPT({[dimension].[dimension].[element1]},{[dimension].[dimension].[element2]})}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_tm1_drill_down_member_set(self): hierarchy_set = MdxHierarchySet.members([ Member.of("dimension", "element") ]).tm1_drill_down_member(other_set=MdxHierarchySet.members( [Member.of("dimension", "element")]), recursive=False) self.assertEqual( "{TM1DRILLDOWNMEMBER({[dimension].[dimension].[element]}, {[dimension].[dimension].[element]})}", hierarchy_set.to_mdx())
def test_mdx_set_cross_joins(self): mdx_set = MdxSet.cross_joins([ MdxHierarchySet.children(Member.of("Dimension", "element1")), MdxHierarchySet.member(Member.of("Dimension", "element2")), MdxHierarchySet.member(Member.of("Dimension", "element3")) ]) self.assertEqual( "{{[dimension].[dimension].[element1].CHILDREN}" " * {[dimension].[dimension].[element2]}" " * {[dimension].[dimension].[element3]}}", mdx_set.to_mdx())
def test_mdx_hierarchy_set_unions_no_duplicates(self): hierarchy_set = MdxHierarchySet.unions([ MdxHierarchySet.children(Member.of("Dimension", "element1")), MdxHierarchySet.member(Member.of("Dimension", "element2")), MdxHierarchySet.member(Member.of("Dimension", "element3")) ]) self.assertEqual( "{{[DIMENSION].[DIMENSION].[ELEMENT1].CHILDREN}" " + {[DIMENSION].[DIMENSION].[ELEMENT2]}" " + {[DIMENSION].[DIMENSION].[ELEMENT3]}}", hierarchy_set.to_mdx())
def test_mdx_set_unions_allow_duplicates(self): hierarchy_set = MdxSet.unions([ MdxHierarchySet.children(Member.of("Dimension", "element1")), MdxHierarchySet.member(Member.of("Dimension", "element2")), MdxHierarchySet.member(Member.of("Dimension", "element3")) ], True) self.assertEqual( "{{[dimension].[dimension].[element1].CHILDREN}," "{[dimension].[dimension].[element2]}," "{[dimension].[dimension].[element3]}}", hierarchy_set.to_mdx())
def as_MDX(self) -> str: """ Build a valid MDX Query from an Existing cubeview. Takes Zero suppression into account. Throws an Exception when no elements are place on the columns. Subsets are referenced in the result-MDX through the TM1SubsetToSet Function :return: String, the MDX Query """ if not self.columns: raise ValueError("Column selection must not be empty") query = MdxBuilder.from_cube(self.cube) if self._suppress_empty_rows: query.rows_non_empty() if self.suppress_empty_columns: query.columns_non_empty() axes = [self.columns] if self.rows: axes.append(self.rows) for axis_id, axis in enumerate(axes): for axis_selection in axis: subset = axis_selection.subset if isinstance(subset, AnonymousSubset): if subset.expression is not None: mdx_hierarchy_set = MdxHierarchySet.from_str( dimension=subset.dimension_name, hierarchy=subset.hierarchy_name, mdx=subset.expression) else: members = [ Member.of(subset.dimension_name, element) for element in subset.elements ] mdx_hierarchy_set = MdxHierarchySet.members(members) else: mdx_hierarchy_set = MdxHierarchySet.tm1_subset_to_set( dimension=axis_selection.dimension_name, hierarchy=axis_selection.hierarchy_name, subset=subset.name) query.add_hierarchy_set_to_axis( axis=axis_id, mdx_hierarchy_set=mdx_hierarchy_set) for title in self._titles: query.add_member_to_where( Member.of(title.dimension_name, title.selected)) return query.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_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_filter_by_wildcard(self): hierarchy_set = MdxHierarchySet.all_members( "Dimension", "Hierarchy").filter_by_pattern("2011*") self.assertEqual( "{TM1FILTERBYPATTERN({[dimension].[hierarchy].MEMBERS},'2011*')}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_filter_by_level(self): hierarchy_set = MdxHierarchySet.all_members( "Dimension", "Hierarchy").filter_by_level(0) self.assertEqual( "{TM1FILTERBYLEVEL({[dimension].[hierarchy].MEMBERS},0)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_tm1_sort_asc(self): hierarchy_set = MdxHierarchySet.tm1_subset_all( "Dimension", "Hierarchy").tm1_sort(True) self.assertEqual( "{TM1SORT({TM1SUBSETALL([dimension].[hierarchy])},ASC)}", hierarchy_set.to_mdx())
def test_mdx_filter_by_attribute_single_numeric(self): hierarchy_set = MdxHierarchySet.tm1_subset_all("Dimension").filter_by_attribute("Attribute1", [1]) self.assertEqual( "{FILTER({TM1SUBSETALL([DIMENSION].[DIMENSION])}," "[}ELEMENTATTRIBUTES_DIMENSION].([}ELEMENTATTRIBUTES_DIMENSION].[Attribute1])=1)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_from_str(self): hierarchy_set = MdxHierarchySet.from_str( dimension="Dimension", hierarchy="Hierarchy", mdx="{[DIMENSION].[HIERARCHY].MEMBERS}") self.assertEqual(hierarchy_set.to_mdx(), "{[DIMENSION].[HIERARCHY].MEMBERS}")
def test_mdx_hierarchy_set_subset(self): hierarchy_set = MdxHierarchySet.tm1_subset_all("Dimension").subset( 1, 3) self.assertEqual( "{SUBSET({TM1SUBSETALL([dimension].[dimension])},1,3)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_tm1_sort_desc(self): hierarchy_set = MdxHierarchySet.tm1_subset_all("Dimension").tm1_sort( False) self.assertEqual( "{TM1SORT({TM1SUBSETALL([dimension].[dimension])},DESC)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_all_consolidations(self): hierarchy_set = MdxHierarchySet.all_consolidations("Dimension") self.assertEqual( "{EXCEPT(" "{TM1SUBSETALL([DIMENSION].[DIMENSION])}," "{TM1FILTERBYLEVEL({TM1SUBSETALL([DIMENSION].[DIMENSION])},0)})}", hierarchy_set.to_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_hierarchy_descendants_with_flag(self): member = Member.of('Dimension', 'Hierarchy', 'Member1') hierarchy_set = MdxHierarchySet.descendants( member, desc_flag='SELF_AND_BEFORE') self.assertEqual( "{DESCENDANTS([dimension].[hierarchy].[member1], SELF_AND_BEFORE)}", hierarchy_set.to_mdx())
def test_mdx_filter_by_attribute_single_string(self): hierarchy_set = MdxHierarchySet.tm1_subset_all( "Dimension").filter_by_attribute("Attribute1", ["Value1"]) self.assertEqual( "{FILTER({TM1SUBSETALL([dimension].[dimension])}," '[}ELEMENTATTRIBUTES_dimension].([}ELEMENTATTRIBUTES_dimension].[Attribute1])="Value1")}', hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_tm1_drill_down_member_all(self): hierarchy_set = MdxHierarchySet.members([Member.of("DIMENSION", "ELEMENT")]).tm1_drill_down_member(all=True, recursive=False) self.assertEqual( "{TM1DRILLDOWNMEMBER({[DIMENSION].[DIMENSION].[ELEMENT]}, ALL)}", hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_range(self): member1 = Member.of('Dimension', 'Hierarchy', 'Member1') member2 = Member.of('Dimension', 'Hierarchy', 'Member2') hierarchy_set = MdxHierarchySet.range(member1, member2) self.assertEqual( "{[dimension].[hierarchy].[member1]:[dimension].[hierarchy].[member2]}", hierarchy_set.to_mdx())