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())
