def test_execute_view_dataframe_pivot_one_row_one_column_dimensions(self):
view_name = PREFIX + "Pivot_one_row_one_column_dimensions"
view = NativeView(cube_name=CUBE_NAME,
view_name=view_name,
suppress_empty_columns=False,
suppress_empty_rows=False)
view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression='{ HEAD ( {[' +
DIMENSION_NAMES[0] + '].Members}, 10) } }'))
view.add_column(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression='{ HEAD ( { [' +
DIMENSION_NAMES[1] + '].Members}, 10 ) }'))
view.add_title(dimension_name=DIMENSION_NAMES[2],
selection="Element 1",
subset=AnonymousSubset(
dimension_name=DIMENSION_NAMES[2],
elements=("Element 1", )))
self.tm1.cubes.views.create(view, private=False)
pivot = self.tm1.cubes.cells.execute_view_dataframe_pivot(
cube_name=CUBE_NAME, view_name=view_name)
self.assertEqual((10, 10), pivot.shape)
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
# generate random coordinates
cls.target_coordinates = list(
zip(('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100))))
# Build Dimensions
for i in range(3):
elements = [
Element('Element {}'.format(str(j)), 'Numeric')
for j in range(1, 1001)
]
hierarchy = Hierarchy(dimension_names[i], dimension_names[i],
elements)
dimension = Dimension(dimension_names[i], [hierarchy])
if not cls.tm1.dimensions.exists(dimension.name):
cls.tm1.dimensions.create(dimension)
# Build Cube
cube = Cube(cube_name, dimension_names)
if not cls.tm1.cubes.exists(cube_name):
cls.tm1.cubes.create(cube)
# Build cube view
view = NativeView(cube_name=cube_name,
view_name=view_name,
suppress_empty_columns=True,
suppress_empty_rows=True)
subset = AnonymousSubset(dimension_name=dimension_names[0],
expression='{[' + dimension_names[0] +
'].Members}')
view.add_row(dimension_name=dimension_names[0], subset=subset)
subset = AnonymousSubset(dimension_name=dimension_names[1],
expression='{[' + dimension_names[1] +
'].Members}')
view.add_row(dimension_name=dimension_names[1], subset=subset)
subset = AnonymousSubset(dimension_name=dimension_names[2],
expression='{[' + dimension_names[2] +
'].Members}')
view.add_column(dimension_name=dimension_names[2], subset=subset)
cls.tm1.cubes.views.create(view, private=False)
# Sum of all the values that we write in the cube. serves as a checksum
cls.total_value = 0
# cellset of data that shall be written
cls.cellset = {}
for element1, element2, element3 in cls.target_coordinates:
value = random.randint(1, 1000)
cls.cellset[(element1, element2, element3)] = value
# update the checksum
cls.total_value += value
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_view_skip_contexts(self):
view_name = PREFIX + "View_With_Titles"
if not self.tm1.cubes.views.exists(
cube_name=CUBE_NAME, view_name=view_name, private=False):
view = NativeView(cube_name=CUBE_NAME,
view_name=view_name,
suppress_empty_columns=False,
suppress_empty_rows=False)
view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression='{[' + DIMENSION_NAMES[0] +
'].[Element 1]}'))
view.add_column(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression='{[' + DIMENSION_NAMES[1] +
'].[Element 1]}'))
view.add_title(
dimension_name=DIMENSION_NAMES[2],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[2],
expression='{[' + DIMENSION_NAMES[2] +
'].Members}'),
selection="Element 1")
self.tm1.cubes.views.create(view=view, private=False)
data = self.tm1.cubes.cells.execute_view(cube_name=CUBE_NAME,
view_name=view_name,
private=False,
skip_contexts=True)
self.assertEqual(len(data), 1)
for coordinates, cell in data.items():
self.assertEqual(len(coordinates), 2)
self.assertEqual(
Utils.dimension_name_from_element_unique_name(coordinates[0]),
DIMENSION_NAMES[0])
self.assertEqual(
Utils.dimension_name_from_element_unique_name(coordinates[1]),
DIMENSION_NAMES[1])
def test_execute_view_raw_skip_contexts(self):
view_name = PREFIX + "View_With_Titles"
if not self.tm1.cubes.views.exists(
cube_name=CUBE_NAME, view_name=view_name, private=False):
view = NativeView(cube_name=CUBE_NAME,
view_name=view_name,
suppress_empty_columns=False,
suppress_empty_rows=False)
view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression='{[' + DIMENSION_NAMES[0] +
'].[Element 1]}'))
view.add_column(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression='{[' + DIMENSION_NAMES[1] +
'].[Element 1]}'))
view.add_title(
dimension_name=DIMENSION_NAMES[2],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[2],
expression='{[' + DIMENSION_NAMES[2] +
'].Members}'),
selection="Element 1")
self.tm1.cubes.views.create(view=view, private=False)
raw_response = self.tm1.cubes.cells.execute_view_raw(
cube_name=CUBE_NAME,
view_name=view_name,
private=False,
skip_contexts=True,
member_properties=["UniqueName"])
self.assertEqual(len(raw_response["Axes"]), 2)
for axis in raw_response["Axes"]:
dimension_on_axis = Utils.dimension_name_from_element_unique_name(
axis["Tuples"][0]["Members"][0]["UniqueName"])
self.assertNotEqual(dimension_on_axis, DIMENSION_NAMES[2])
Create a classic TM1 cube view
Assumptions: all referenced subsets exist in TM1 Server.
"""
from TM1py.Objects import NativeView
from TM1py.Services import TM1Service
# establish connection to TM1 Server
with TM1Service(address='localhost',
port=12354,
user='******',
password='******',
ssl=True) as tm1:
native_view = NativeView(cube_name='Plan_BudgetPlan',
view_name='TM1py View3')
# set up native view - put subsets on Rows, Columns and Titles
subset = tm1.dimensions.subsets.get(dimension_name='plan_version',
subset_name='FY 2004 Budget',
private=False)
native_view.add_row(dimension_name='plan_version', subset=subset)
subset = tm1.dimensions.subsets.get(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 = tm1.dimensions.subsets.get(dimension_name='plan_department',
subset_name='n level departments',
private=False)
"""
Create a classic TM1 cube view
Assumptions: all referenced subsets exist in TM1 Server.
"""
from TM1py.Objects import NativeView
from TM1py.Services import TM1Service
# establish connection to TM1 Server
with TM1Service(address='localhost', port=12354, user='******', password='******', ssl=True) as tm1:
native_view = NativeView(cube_name='Plan_BudgetPlan',
view_name='TM1py View3')
# set up native view - put subsets on Rows, Columns and Titles
subset = tm1.dimensions.subsets.get(dimension_name='plan_version',
subset_name='FY 2004 Budget',
private=False)
native_view.add_row(dimension_name='plan_version', subset=subset)
subset = tm1.dimensions.subsets.get(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 = tm1.dimensions.subsets.get(dimension_name='plan_department',
subset_name='n level departments',
private=False)
native_view.add_row(dimension_name='plan_department', subset=subset)
def build_views(tm1, overwrite):
# build views in Bike Shares Cube
cube_name = "Bike Shares"
view_name = "2014 to 2017 Counts by Day"
rows = [
ViewAxisSelection(
'Date',
AnonymousSubset('Date', 'Date',
'{[Date].[2014-01-01]:[Date].[2017-12-31]}'))
]
columns = [
ViewAxisSelection(
'City',
AnonymousSubset(
'City', 'City',
'{[City].[NYC], [City].[Chicago], [City].[Washington]}'))
]
titles = [
ViewTitleSelection(
'Bike Shares Measure',
AnonymousSubset('Bike Shares Measure', 'Bike Shares Measure',
'{[Bike Shares Measure].[Count]}'), 'Count'),
ViewTitleSelection('Version',
subset=AnonymousSubset('Version',
'Version',
elements=['Actual']),
selected='Actual')
]
view = NativeView(cube_name,
view_name,
rows=rows,
columns=columns,
titles=titles,
format_string="####.##")
if tm1.cubes.views.exists(cube_name, view_name)[1]:
if overwrite:
tm1.cubes.views.update(view, False)
else:
tm1.cubes.views.create(view, private=False)
view_name = "2017 Counts by Month"
rows = [
ViewAxisSelection(
'Date',
AnonymousSubset('Date', 'Date',
'{[Date].[2017-01]:[Date].[2017-12]}'))
]
columns = [
ViewAxisSelection(
'City',
AnonymousSubset(
'City', 'City',
'{[City].[NYC], [City].[Chicago], [City].[Washington]}'))
]
titles = [
ViewTitleSelection(
'Bike Shares Measure',
AnonymousSubset('Bike Shares Measure', 'Bike Shares Measure',
'{[Bike Shares Measure].[Count]}'), 'Count'),
ViewTitleSelection('Version',
subset=AnonymousSubset('Version',
'Version',
elements=['Actual']),
selected='Actual')
]
view = NativeView(cube_name,
view_name,
rows=rows,
columns=columns,
titles=titles,
format_string="####.##")
if tm1.cubes.views.exists(cube_name, view_name)[1]:
if overwrite:
tm1.cubes.views.update(view, False)
else:
tm1.cubes.views.create(view, private=False)
# build views in Weather Data Cube
cube_name = "Weather Data"
view_name = "2014 to 2017 Average by Day"
rows = [
ViewAxisSelection(
'Date',
AnonymousSubset('Date', 'Date',
'{[Date].[2014-01-01]:[Date].[2017-12-31]}'))
]
columns = [
ViewAxisSelection(
'City',
AnonymousSubset(
'City', 'City',
'{[City].[NYC], [City].[Chicago], [City].[Washington]}'))
]
titles = [
ViewTitleSelection(
'Weather Data Measure',
AnonymousSubset('Weather Data Measure', 'Weather Data Measure',
'{[Weather DataMeasure].[TAVG]}'), 'TAVG'),
ViewTitleSelection('Version',
subset=AnonymousSubset('Version',
'Version',
elements=['Actual']),
selected='Actual')
]
view = NativeView(cube_name,
view_name,
rows=rows,
columns=columns,
titles=titles,
format_string="####.##")
if tm1.cubes.views.exists(cube_name, view_name)[1]:
if overwrite:
tm1.cubes.views.update(view, False)
else:
tm1.cubes.views.create(view, private=False)
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
# generate random coordinates
cls.target_coordinates = list(
zip(('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100))))
# Build Dimensions
for dimension_name in DIMENSION_NAMES:
elements = [
Element('Element {}'.format(str(j)), 'Numeric')
for j in range(1, 1001)
]
element_attributes = [
ElementAttribute("Attr1", "String"),
ElementAttribute("Attr2", "Numeric"),
ElementAttribute("Attr3", "Numeric")
]
hierarchy = Hierarchy(dimension_name=dimension_name,
name=dimension_name,
elements=elements,
element_attributes=element_attributes)
dimension = Dimension(dimension_name, [hierarchy])
if cls.tm1.dimensions.exists(dimension.name):
cls.tm1.dimensions.update(dimension)
else:
cls.tm1.dimensions.create(dimension)
attribute_cube = "}ElementAttributes_" + dimension_name
attribute_values = dict()
for element in elements:
attribute_values[(element.name, "Attr1")] = "TM1py"
attribute_values[(element.name, "Attr2")] = "2"
attribute_values[(element.name, "Attr3")] = "3"
cls.tm1.cubes.cells.write_values(attribute_cube, attribute_values)
# Build Cube
cube = Cube(CUBE_NAME, DIMENSION_NAMES)
if not cls.tm1.cubes.exists(CUBE_NAME):
cls.tm1.cubes.create(cube)
# Build cube view
view = NativeView(cube_name=CUBE_NAME,
view_name=VIEW_NAME,
suppress_empty_columns=True,
suppress_empty_rows=True)
view.add_row(dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression='{[' +
DIMENSION_NAMES[0] + '].Members}'))
view.add_row(dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression='{[' +
DIMENSION_NAMES[1] + '].Members}'))
view.add_column(
dimension_name=DIMENSION_NAMES[2],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[2],
expression='{[' + DIMENSION_NAMES[2] +
'].Members}'))
cls.tm1.cubes.views.create(view=view, private=False)
# Sum of all the values that we write in the cube. serves as a checksum.
cls.total_value = 0
# cellset of data that shall be written
cls.cellset = {}
for element1, element2, element3 in cls.target_coordinates:
value = random.randint(1, 1000)
cls.cellset[(element1, element2, element3)] = value
# update the checksum
cls.total_value += value
# Fill cube with values
cls.tm1.cubes.cells.write_values(CUBE_NAME, cls.cellset)
