def test_mdx_hierarchy_set_bottom_count(self):
hierarchy_set = MdxHierarchySet \
.tm1_subset_all("Dimension") \
.bottom_count("CUBE", MdxTuple.of(Member.of("DIMENSION2", "ELEMENT2"), Member.of("DIMENSION3", "ELEMENT3")),
10)
self.assertEqual(
"{BOTTOMCOUNT({TM1SUBSETALL([DIMENSION].[DIMENSION])},"
"10,"
"[CUBE].([DIMENSION2].[DIMENSION2].[ELEMENT2],[DIMENSION3].[DIMENSION3].[ELEMENT3]))}",
hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_unions_allow_duplicates(self):
hierarchy_set = MdxHierarchySet.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 test_mdx_hierarchy_set_order_desc_str(self):
hierarchy_set = MdxHierarchySet.all_members("Dimension1", "Hierarchy1").order(
cube="Cube",
mdx_tuple=MdxTuple.of(
Member.of("Dimension2", "Hierarchy2", "ElementA"),
Member.of("Dimension3", "Hierarchy3", "ElementB")),
order="DESC")
self.assertEqual(
"{ORDER({[DIMENSION1].[HIERARCHY1].MEMBERS},"
"[CUBE].([DIMENSION2].[HIERARCHY2].[ELEMENTA],[DIMENSION3].[HIERARCHY3].[ELEMENTB]),DESC)}",
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_hierarchy_set_filter_by_cell_value_string(self):
hierarchy_set = MdxHierarchySet.all_members(
"Dimension1", "Hierarchy1").filter_by_cell_value(
cube="Cube",
mdx_tuple=MdxTuple.of(Member.of("Dimension2", "ElementA"),
Member.of("Dimension3", "ElementB")),
operator="=",
value='ABC')
self.assertEqual(
"{FILTER({[dimension1].[hierarchy1].MEMBERS},"
"[cube].([dimension2].[dimension2].[elementa],[dimension3].[dimension3].[elementb])='ABC')}",
hierarchy_set.to_mdx())
def test_mdx_hierarchy_set_filter_by_cell_value_string(self):
hierarchy_set = MdxHierarchySet.all_members("Dimension1", "Hierarchy1").filter_by_cell_value(
cube="Cube",
mdx_tuple=MdxTuple.of(
Member.of("Dimension2", "ElementA"),
Member.of("Dimension3", "ElementB")),
operator="=",
value='ABC')
self.assertEqual(
"{FILTER({[DIMENSION1].[HIERARCHY1].MEMBERS},"
"[CUBE].([DIMENSION2].[DIMENSION2].[ELEMENTA],[DIMENSION3].[DIMENSION3].[ELEMENTB])='ABC')}",
hierarchy_set.to_mdx())
def test_calculated_member_sum(self):
calculated_member = CalculatedMember.sum(
dimension="Period",
hierarchy="Period",
element="SUM 2016",
cube="Cube",
mdx_set=MdxHierarchySet.children(Member.of("Period", "2016")),
mdx_tuple=MdxTuple.of(Member.of("Dimension1", "Element1"), Member.of("Dimension2", "Element2")))
self.assertEqual(
calculated_member.to_mdx(),
"MEMBER [PERIOD].[PERIOD].[SUM2016] AS SUM({[PERIOD].[PERIOD].[2016].CHILDREN},[CUBE]."
"([DIMENSION1].[DIMENSION1].[ELEMENT1],[DIMENSION2].[DIMENSION2].[ELEMENT2]))")
def test_mdx_hierarchy_set_order(self):
hierarchy_set = MdxHierarchySet.all_members(
"Dimension1",
"Hierarchy1").order(cube="Cube",
mdx_tuple=MdxTuple.of(
Member.of("Dimension2", "Hierarchy2",
"ElementA"),
Member.of("Dimension3", "Hierarchy3",
"ElementB")))
self.assertEqual(
"{ORDER({[dimension1].[hierarchy1].MEMBERS},"
"[cube].([dimension2].[hierarchy2].[elementa],[dimension3].[hierarchy3].[elementb]),BASC)}",
hierarchy_set.to_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_calculated_member_sum(self):
calculated_member = CalculatedMember.sum(
dimension="Period",
hierarchy="Period",
element="SUM 2016",
cube="Cube",
mdx_set=MdxHierarchySet.children(Member.of("Period", "2016")),
mdx_tuple=MdxTuple.of(Member.of("Dimension1", "Element1"),
Member.of("Dimension2", "Element2")))
self.assertEqual(
calculated_member.to_mdx(),
"MEMBER [period].[period].[sum2016] AS SUM({[period].[period].[2016].CHILDREN},[cube]."
"([dimension1].[dimension1].[element1],[dimension2].[dimension2].[element2]))"
)
def test_mdx_hierarchy_set_drill_down_level(self):
hierarchy_set = MdxHierarchySet.drill_down_level(
Member.of("Dimension", "Element"))
self.assertEqual(
"{DRILLDOWNLEVEL({[dimension].[dimension].[element]})}",
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 build_mdx_from_cellset(cells: Dict, cube_name: str, dimensions: Iterable[str]) -> str:
query = MdxBuilder.from_cube(cube_name)
for coordinates in cells:
members = (Member.of(dimension, element) for dimension, element in zip(dimensions, coordinates))
query.add_member_tuple_to_columns(*members)
mdx = query.to_mdx()
return 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_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_builder_multi_axes(self):
mdx = MdxBuilder.from_cube("cube") \
.add_hierarchy_set_to_axis(0, MdxHierarchySet.member(Member.of("Dim1", "Elem1"))) \
.add_hierarchy_set_to_axis(1, MdxHierarchySet.member(Member.of("Dim2", "Elem2"))) \
.add_hierarchy_set_to_axis(2, MdxHierarchySet.member(Member.of("Dim3", "Elem3"))) \
.add_hierarchy_set_to_axis(3, MdxHierarchySet.member(Member.of("Dim4", "Elem4"))) \
.add_hierarchy_set_to_axis(4, MdxHierarchySet.member(Member.of("Dim5", "Elem5"))) \
.to_mdx()
self.assertEqual(
"SELECT\r\n"
"{[dim1].[dim1].[elem1]} ON 0,\r\n"
"{[dim2].[dim2].[elem2]} ON 1,\r\n"
"{[dim3].[dim3].[elem3]} ON 2,\r\n"
"{[dim4].[dim4].[elem4]} ON 3,\r\n"
"{[dim5].[dim5].[elem5]} ON 4\r\n"
"FROM [cube]", mdx)
def test_mdx_hierarchy_set_tm1_drill_down_member_all(self):
hierarchy_set = MdxHierarchySet.members(
[Member.of("dimension",
"element")]).tm1_drill_down_member(recursive=False)
self.assertEqual(
"{TM1DRILLDOWNMEMBER({[dimension].[dimension].[element]}, ALL)}",
hierarchy_set.to_mdx())
def test_mdx_builder_single_axes(self):
mdx = MdxBuilder.from_cube("cube") \
.add_hierarchy_set_to_axis(0, MdxHierarchySet.member(Member.of("Dim1", "Elem1"))) \
.to_mdx()
self.assertEqual(
"SELECT\r\n"
"{[dim1].[dim1].[elem1]} ON 0\r\n"
"FROM [cube]", mdx)
def build_mdx_and_values_from_cellset(cells: Dict, cube_name: str, dimensions: Iterable[str]) -> Tuple[str, List]:
values = []
query = MdxBuilder.from_cube(cube_name)
for coordinates, value in cells.items():
members = (Member.of(dimension, element) for dimension, element in zip(dimensions, coordinates))
query.add_member_tuple_to_columns(*members)
values.append(value)
mdx = query.to_mdx()
return mdx, values
def test_mdx_hierarchy_descendants_with_flag_and_member_level(self):
member = Member.of('Dimension', 'Hierarchy', 'Member1')
hierarchy_set = MdxHierarchySet.descendants(
member,
MdxLevelExpression.member_level(member),
desc_flag='SELF_AND_BEFORE')
self.assertEqual(
"{DESCENDANTS([dimension].[hierarchy].[member1], "
"[dimension].[hierarchy].[member1].LEVEL, "
"SELF_AND_BEFORE)}", hierarchy_set.to_mdx())
def test_publish(self):
mdx = MdxBuilder.from_cube(self.cube_name).add_member_tuple_to_columns(
Member.of(self.dimension_names[0], "Element1"),
Member.of(self.dimension_names[1], "Element1"),
Member.of(self.dimension_names[2], "Element1")) \
.to_mdx()
self.tm1.cells.write_values(cube_name=self.cube_name,
cellset_as_dict={
("Element1", "Element1", "Element1"): 1
},
sandbox_name=self.sandbox_name1)
values = self.tm1.cells.execute_mdx_values(mdx=mdx)
self.assertEqual(None, values[0])
self.tm1.sandboxes.publish(self.sandbox_name1)
values = self.tm1.cells.execute_mdx_values(mdx=mdx)
self.assertEqual(1, values[0])
def test_calculated_member_lookup(self):
calculated_member = CalculatedMember.lookup(
"Period",
"Period",
"VersionAttribute1",
cube="}ElementAttributes_Version",
mdx_tuple=MdxTuple.of(Member.of("}ElementAttributes_Version", "Attribute1")))
self.assertEqual(
calculated_member.to_mdx(),
"MEMBER [PERIOD].[PERIOD].[VERSIONATTRIBUTE1] AS [}ELEMENTATTRIBUTES_VERSION]."
"([}ELEMENTATTRIBUTES_VERSION].[}ELEMENTATTRIBUTES_VERSION].[ATTRIBUTE1])")
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 _build_drill_intersection_mdx(dimension_name: str, hierarchy_name: str,
first_element_name: str,
second_element_name: str,
mdx_method: str, recursive: bool) -> str:
first_member = Member.of(dimension_name, hierarchy_name,
first_element_name)
second_member = Member.of(dimension_name, hierarchy_name,
second_element_name)
if not hasattr(MDXDrillMethod, mdx_method.upper()):
raise TM1pyException(
'Invalid MDX Drill Method Specified, Options: TM1DrillDownMember or Descendants'
)
elif MDXDrillMethod.TM1DRILLDOWNMEMBER.name == mdx_method.upper():
mdx = MdxHierarchySet.members([first_member]).tm1_drill_down_member(all=True, recursive=recursive) \
.intersect(MdxHierarchySet.members([second_member])).to_mdx()
elif MDXDrillMethod.DESCENDANTS.name == mdx_method.upper():
mdx = MdxHierarchySet.descendants(
first_member, MdxLevelExpression.member_level(first_member),
'SELF_AND_AFTER')
return mdx
def test_calculated_member_lookup(self):
calculated_member = CalculatedMember.lookup(
"Period",
"Period",
"VersionAttribute1",
cube="}ELEMENTATTRIBUTES_version",
mdx_tuple=MdxTuple.of(
Member.of("}ELEMENTATTRIBUTES_version", "Attribute1")))
self.assertEqual(
calculated_member.to_mdx(),
"MEMBER [period].[period].[versionattribute1] AS [}elementattributes_version]."
"([}elementattributes_version].[}elementattributes_version].[attribute1])"
)
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_set_tuples(self):
mdx_set = MdxSet.tuples([
MdxTuple([
Member.of("dimension1", "element1"),
Member.of("dimension2", "element3")
]),
MdxTuple([
Member.of("dimension1", "element2"),
Member.of("dimension2", "element2")
]),
MdxTuple([
Member.of("dimension1", "element3"),
Member.of("dimension2", "element1")
])
])
self.assertEqual(
"{ ([dimension1].[dimension1].[element1],[dimension2].[dimension2].[element3]),"
"([dimension1].[dimension1].[element2],[dimension2].[dimension2].[element2]),"
"([dimension1].[dimension1].[element3],[dimension2].[dimension2].[element1]) }",
mdx_set.to_mdx())
def test_member_unique_name_with_hierarchy(self):
element = Member.of("Dim", "Hier", "Elem")
self.assertEqual(element.unique_name, "[DIM].[HIER].[ELEM]")
def test_member_of_error(self):
with pytest.raises(ValueError):
Member.of("Dim")
