def test_tm1tidy_false_input_view(self):
# define input view and output view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
stdev_p(tm1_services={"tm1srv01": self.tm1},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE,
tidy=False)
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
private=False))
def test_tm1io_input_view(self):
# define input view and output view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(IRR_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, IRR_INPUT_VALUES)
# execute method
result = irr(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE)
self.assertAlmostEqual(IRR_EXPECTED_RESULT,
result,
delta=IRR_TOLERANCE)
def test_tm1io_input_values_output_view(self):
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# execute method
stdev_p(tm1_services={"tm1srv01": self.tm1},
tm1_target="tm1srv01",
cube_target=CUBE_NAME_TARGET,
view_target=VIEW_NAME_TARGET,
values=STDEV_INPUT_VALUES,
tidy=False)
# do check
result = next(
self.tm1.cubes.cells.execute_view_values(
cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
self.assertAlmostEqual(result,
STDEV_P_EXPECTED_RESULT,
delta=STDEV_TOLERANCE)
def test_tm1io_input_mdx_view_output_mdx_view(self):
# create input view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# create output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
result = stdev(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
tm1_target="tm1srv02",
cube_source=CUBE_NAME_SOURCE,
cube_target=CUBE_NAME_TARGET,
view_source=VIEW_NAME_SOURCE,
view_target=VIEW_NAME_TARGET)
self.assertAlmostEqual(STDEV_EXPECTED_RESULT,
result,
delta=STDEV_TOLERANCE)
# check output view
cell_value = next(
self.tm1.cubes.cells.execute_view_values(
cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
self.assertAlmostEqual(cell_value,
STDEV_EXPECTED_RESULT,
delta=STDEV_TOLERANCE)
def test1_create_view(self):
# create instance of native View
native_view = NativeView(cube_name='Plan_BudgetPlan',
view_name=self.native_view_name)
# set up native view - put subsets on Rows, Columns and Titles
subset = self.tm1.get_subset(dimension_name='plan_version',
subset_name='FY 2004 Budget',
private=False)
native_view.add_row(dimension_name='plan_version', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_business_unit',
subset_name='n level business unit',
private=False)
native_view.add_row(dimension_name='plan_business_unit', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_department',
subset_name='n level departments',
private=False)
native_view.add_row(dimension_name='plan_department', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_chart_of_accounts',
subset_name='Consolidations',
private=False)
native_view.add_row(dimension_name='plan_chart_of_accounts',
subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_exchange_rates',
subset_name='local',
private=False)
native_view.add_title(dimension_name='plan_exchange_rates',
subset=subset,
selection='local')
subset = self.tm1.get_subset(dimension_name='plan_time',
subset_name='2004 Total Year',
private=False)
native_view.add_column(dimension_name='plan_time', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_source',
subset_name='budget',
private=False)
native_view.add_column(dimension_name='plan_source', subset=subset)
# create native view on Server
self.tm1.create_view(view=native_view, private=self.random_boolean)
# create instance of MDXView
nv_view = self.tm1.get_native_view(cube_name='Plan_BudgetPlan',
view_name=self.native_view_name,
private=self.random_boolean)
mdx = nv_view.as_MDX
mdx_view = MDXView(cube_name='Plan_BudgetPlan',
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.create_view(view=mdx_view, private=self.random_boolean)
def test_execute_view(self):
mdx = self.MDX_TEMPLATE.format(
rows="{[" + self.dimension_names[0] + "].[Element1], [" + self.dimension_names[0] + "].[Element2]}",
columns="{[" + self.dimension_names[1] + "].[Element1], [" + self.dimension_names[1] + "].[Element2]}",
cube=self.cube_name,
where="[" + self.dimension_names[2] + "].[Element1]")
self.tm1.cubes.views.create(MDXView(self.cube_name, self.view_name, mdx), private=False)
df = self.tm1.power_bi.execute_view(self.cube_name, self.view_name, private=False)
self.assertEqual(len(df), 2)
self.assertEqual(
tuple(df.columns),
(self.dimension_names[0], "Element 1", "Element 2"))
element1 = df.loc[df[self.dimension_names[0]] == "Element 1"]
self.assertEqual(
tuple(element1.values[0]),
("Element 1", "1.0", None))
def test_tm1tidy_false_input_values_output_view(self):
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# execute method
irr(tm1_services={"tm1srv01": self.tm1},
tm1_target="tm1srv01",
cube_target=CUBE_NAME_TARGET,
view_target=VIEW_NAME_TARGET,
values=IRR_INPUT_VALUES,
tidy=False)
# check view existence
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
def test1_create_view(self):
# create instance of native View
native_view = NativeView(name_cube='Plan_BudgetPlan',
name_view=self.native_view_name)
# set up native view - put subsets on Rows, Columns and Titles
subset = self.tm1.get_subset(dimension_name='plan_version', subset_name='FY 2004 Budget')
native_view.add_row(dimension_name='plan_version', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_business_unit', subset_name='n level business unit')
native_view.add_row(dimension_name='plan_business_unit', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_department', subset_name='n level departments')
native_view.add_row(dimension_name='plan_department', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_chart_of_accounts', subset_name='Consolidations')
native_view.add_row(dimension_name='plan_chart_of_accounts', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_source', subset_name='budget')
native_view.add_row(dimension_name='plan_source', subset=subset)
subset = self.tm1.get_subset(dimension_name='plan_exchange_rates', subset_name='actual')
native_view.add_title(dimension_name='plan_exchange_rates', subset=subset, selection='actual')
subset = self.tm1.get_subset(dimension_name='plan_time', subset_name='2003 Total Year')
native_view.add_column(dimension_name='plan_time', subset=subset)
# create native view on Server
self.tm1.create_view(view=native_view, private=self.random_boolean)
# create instance of MDXView
mdx = "SELECT {([plan_version].[FY 2003 Budget], [plan_department].[105], [plan_chart_of_accounts].[61030], " \
"[plan_exchange_rates].[local], [plan_source].[goal] , [plan_time].[Jan-2004]) } on COLUMNS," \
"{[plan_business_unit].[10110]} on ROWS FROM [plan_BudgetPlan]"
mdx_view = MDXView(cube_name='Plan_BudgetPlan',
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.create_view(view=mdx_view, private=self.random_boolean)
def test_execute_view(self):
mdx = MDX_TEMPLATE.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element1], [" +
DIMENSION_NAMES[0] + "].[Element2]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element1], [" +
DIMENSION_NAMES[1] + "].[Element2]}",
cube=CUBE_NAME,
where="[" + DIMENSION_NAMES[2] + "].[Element1]")
self.tm1.cubes.views.create(MDXView(CUBE_NAME, VIEW_NAME, mdx),
private=False)
df = self.tm1.power_bi.execute_view(CUBE_NAME,
VIEW_NAME,
private=False)
self.assertEqual(len(df), 2)
self.assertEqual(tuple(df.columns),
(DIMENSION_NAMES[0], "Element 1", "Element 2"))
element1 = df.loc[df[DIMENSION_NAMES[0]] == "Element 1"]
self.assertEqual(tuple(element1.values[0]), ("Element 1", "1.0", None))
view.add_row(dimension_name='plan_department', subset_name='n level departments')
view.add_row(dimension_name='plan_chart_of_accounts', subset_name='Consolidations')
view.add_row(dimension_name='plan_source', subset_name='budget')
view.add_title(dimension_name='plan_exchange_rates', subset_name='actual', selection='actual')
view.add_column(dimension_name='plan_time', subset_name='2003 Total Year')
# create native view against TM1 Server
q.create_view(view=view)
# delete native view from server
q.delete_view(view.get_cube(), view.get_name())
# random text
random_string = str(uuid.uuid4())
# create mdx view
mdx = "SELECT {([plan_version].[FY 2003 Budget], [plan_department].[105], [plan_chart_of_accounts].[61030], " \
"[plan_exchange_rates].[local], [plan_source].[goal] , [plan_time].[Jan-2004]) } on COLUMNS," \
"{[plan_business_unit].[10110]} on ROWS FROM [plan_BudgetPlan]"
mdx_view = MDXView('plan_Budgetplan', random_string, mdx)
# create mdx view on TM1 Server
q.create_view(view=mdx_view)
# delete mdx view from server
q.delete_view(mdx_view.get_cube(), mdx_view.get_name())
# logout
q.logout()
class TestMDXView(unittest.TestCase):
cube_name = "c1"
view_name = "v1"
mdx = """
SELECT
{[d1].[e1], [d1].[e2], [d1].[e3]} ON ROWS,
{Tm1SubsetAll([d2])} ON COLUMNS
FROM [c1]
WHERE ([d3].[d3].[e1], [d4].[e1], [d5].[h1].[e1])
"""
def setUp(self) -> None:
self.view = MDXView(cube_name=self.cube_name,
view_name=self.view_name,
MDX=self.mdx)
def test_substitute_title(self):
self.view.substitute_title(dimension="d3",
hierarchy="d3",
element="e2")
expected_mdx = """
SELECT
{[d1].[e1], [d1].[e2], [d1].[e3]} ON ROWS,
{Tm1SubsetAll([d2])} ON COLUMNS
FROM [c1]
WHERE ([d3].[d3].[e2], [d4].[e1], [d5].[h1].[e1])
"""
self.assertEqual(expected_mdx, self.view.mdx)
def test_substitute_title_different_case(self):
self.view.substitute_title(dimension="D3",
hierarchy="D3",
element="e2")
expected_mdx = """
SELECT
{[d1].[e1], [d1].[e2], [d1].[e3]} ON ROWS,
{Tm1SubsetAll([d2])} ON COLUMNS
FROM [c1]
WHERE ([D3].[D3].[e2], [d4].[e1], [d5].[h1].[e1])
"""
self.assertEqual(expected_mdx, self.view.mdx)
def test_substitute_title_without_hierarchy(self):
self.view.substitute_title(dimension="d4",
hierarchy="d4",
element="e2")
expected_mdx = """
SELECT
{[d1].[e1], [d1].[e2], [d1].[e3]} ON ROWS,
{Tm1SubsetAll([d2])} ON COLUMNS
FROM [c1]
WHERE ([d3].[d3].[e1], [d4].[e2], [d5].[h1].[e1])
"""
self.assertEqual(expected_mdx, self.view.mdx)
def test_substitute_title_with_hierarchy(self):
self.view.substitute_title(dimension="d5",
hierarchy="h1",
element="e2")
expected_mdx = """
SELECT
{[d1].[e1], [d1].[e2], [d1].[e3]} ON ROWS,
{Tm1SubsetAll([d2])} ON COLUMNS
FROM [c1]
WHERE ([d3].[d3].[e1], [d4].[e1], [d5].[h1].[e2])
"""
self.assertEqual(expected_mdx, self.view.mdx)
def test_substitute_title_value_error(self):
with self.assertRaises(ValueError) as error:
self.view.substitute_title(dimension="d6",
hierarchy="d6",
element="e2")
print(error)
def setUp(self) -> None:
self.view = MDXView(cube_name=self.cube_name,
view_name=self.view_name,
MDX=self.mdx)
from TM1py import TM1pyQueries as TM1, TM1pyLogin, MDXView
import uuid
# establish connection to TM1 Server
login = TM1pyLogin.native('admin', 'apple')
with TM1(ip='', port=8001, login=login, ssl=False) as tm1:
# random text
random_string = str(uuid.uuid4())
# create mdx view
mdx = "SELECT {([plan_version].[FY 2003 Budget], [plan_department].[105], [plan_chart_of_accounts].[61030], " \
"[plan_exchange_rates].[local], [plan_source].[goal] , [plan_time].[Jan-2004]) } on COLUMNS," \
"{[plan_business_unit].[10110]} on ROWS FROM [plan_BudgetPlan]"
mdx_view = MDXView(cube_name='plan_BudgetPlan',
view_name='TM1py_' + random_string,
MDX=mdx)
# create mdx view on TM1 Server
tm1.create_view(view=mdx_view)
# get view content
content = tm1.get_view_content(cube_name=mdx_view.cube,
view_name=mdx_view.name)
# print content
print(content)