def test_execute_view_rows_and_cells_two_dimensions_on_rows(self):
view_name = PREFIX + "MDX_View_With_Two_Dim_On_Rows"
if not self.tm1.cubes.views.exists(
cube_name=CUBE_NAME, view_name=view_name, private=False):
rows = """
{{ [{dim0}].[Element1], [{dim0}].[Element2]}} * {{ [{dim1}].[Element1], [{dim1}].[Element2] }}
""".format(dim0=DIMENSION_NAMES[0], dim1=DIMENSION_NAMES[1])
columns = """
{{ [{dim2}].[Element1], [{dim2}].[Element2], [{dim2}].[Element3] }}
""".format(dim2=DIMENSION_NAMES[2])
mdx = MDX_TEMPLATE.format(rows=rows,
columns=columns,
cube=CUBE_NAME,
where="[" + DIMENSION_NAMES[2] +
"].[Element1]")
view = MDXView(cube_name=CUBE_NAME, view_name=view_name, MDX=mdx)
self.tm1.cubes.views.create(view, False)
data = self.tm1.cubes.cells.execute_view_rows_and_cells(
cube_name=CUBE_NAME, view_name=view_name, private=False)
self.assertEqual(len(data), 4)
for row, cells in data.items():
self.assertEqual(len(row), 2)
dimension = Utils.dimension_name_from_element_unique_name(row[0])
self.assertEqual(dimension, DIMENSION_NAMES[0])
dimension = Utils.dimension_name_from_element_unique_name(row[1])
self.assertEqual(dimension, DIMENSION_NAMES[1])
self.assertEqual(len(cells), 3)
def setUp(self):
for private in (True, False):
# create instance of native View
native_view = NativeView(
cube_name=CUBE_NAME,
view_name=self.native_view_name)
# Set up native view - put subsets on Rows, Columns and Titles
subset = Subset(
dimension_name=DIMENSION_NAMES[0],
hierarchy_name=DIMENSION_NAMES[0],
subset_name=SUBSET_NAME,
expression='{{[{}].Members}}'.format(DIMENSION_NAMES[0]))
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=subset)
subset = AnonymousSubset(
dimension_name=DIMENSION_NAMES[1],
hierarchy_name=DIMENSION_NAMES[1],
elements=['element1', 'element123', 'element432'])
native_view.add_title(
dimension_name=DIMENSION_NAMES[1],
subset=subset,
selection='element123')
elements = ['Element{}'.format(str(i)) for i in range(1, 201)]
subset = Subset(
dimension_name=DIMENSION_NAMES[2],
hierarchy_name=DIMENSION_NAMES[2],
subset_name=SUBSET_NAME,
elements=elements)
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_column(
dimension_name=DIMENSION_NAMES[2],
subset=subset)
# Suppress Null Values
native_view.suppress_empty_cells = True
# create native view on Server
self.tm1.cubes.views.create(
view=native_view,
private=private)
# create instance of MDXView
nv_view = self.tm1.cubes.views.get_native_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
mdx = nv_view.MDX
mdx_view = MDXView(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.cubes.views.create(
view=mdx_view,
private=private)
def test1_create_view(self):
# create instance of native View
native_view = NativeView(cube_name=cube_name,
view_name=self.native_view_name)
# Set up native view - put subsets on Rows, Columns and Titles
subset = Subset(dimension_name=dimension_names[0],
hierarchy_name=dimension_names[0],
subset_name=str(uuid.uuid4()),
expression='{{[{}].Members}}'.format(
dimension_names[0]))
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_row(dimension_name=dimension_names[0], subset=subset)
subset = AnonymousSubset(
dimension_name=dimension_names[1],
hierarchy_name=dimension_names[1],
elements=['element1', 'element123', 'element432'])
native_view.add_title(dimension_name=dimension_names[1],
subset=subset,
selection='element123')
elements = ['Element{}'.format(str(i)) for i in range(1, 201)]
subset = Subset(dimension_name=dimension_names[2],
hierarchy_name=dimension_names[2],
subset_name=str(uuid.uuid4()),
elements=elements)
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_column(dimension_name=dimension_names[2],
subset=subset)
# Suppress Null Values
native_view.suppress_empty_cells = True
# create native view on Server
self.tm1.cubes.views.create(view=native_view,
private=self.random_boolean)
# create instance of MDXView
nv_view = self.tm1.cubes.views.get_native_view(
cube_name=cube_name,
view_name=self.native_view_name,
private=self.random_boolean)
mdx = nv_view.MDX
mdx_view = MDXView(cube_name=cube_name,
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.cubes.views.create(view=mdx_view, private=self.random_boolean)
def test_execute_mdxview_dataframe_pivot(self):
mdx = MDX_TEMPLATE.format(
rows="{{ [{}].DefaultMember }}".format(DIMENSION_NAMES[0]),
columns="{{ [{}].DefaultMember }}".format(DIMENSION_NAMES[1]),
cube="[{}]".format(CUBE_NAME),
where="( [{}].[{}] )".format(DIMENSION_NAMES[2], "Element1"))
view = MDXView(CUBE_NAME, PREFIX + "MDX_VIEW", mdx)
self.tm1.cubes.views.create(view=view, private=False)
pivot = self.tm1.cubes.cells.execute_view_dataframe_pivot(
cube_name=CUBE_NAME, view_name=view.name, private=False)
self.assertEqual((1, 1), pivot.shape)
self.tm1.cubes.views.delete(cube_name=CUBE_NAME,
view_name=view.name,
private=False)
import uuid
from TM1py.Objects import MDXView
from TM1py.Services import TM1Service
config = configparser.ConfigParser()
config.read(r'..\config.ini')
with TM1Service(**config['tm1srv01']) as tm1:
# Random text
random_string = str(uuid.uuid4())
# Create mdx view
mdx = "SELECT " \
"NON EMPTY {TM1SUBSETALL( [}Clients] )} on ROWS, " \
"NON EMPTY {TM1SUBSETALL( [}Groups] )} ON COLUMNS " \
"FROM [}ClientGroups]"
mdx_view = MDXView(cube_name='}ClientGroups',
view_name='TM1py_' + random_string,
MDX=mdx)
# Create mdx view on TM1 Server
tm1.cubes.views.create(view=mdx_view)
# Get view content
content = tm1.cubes.cells.execute_view(cube_name=mdx_view.cube,
view_name=mdx_view.name)
# Print content
print(content)