def test_get_tm1_time_value_now(self):
current_time_from_excel_serial_date = Utils.get_tm1_time_value_now(use_excel_serial_date=True)
current_time_from_tm1_serial_date = Utils.get_tm1_time_value_now(use_excel_serial_date=False)
self.assertIsInstance(current_time_from_excel_serial_date, float)
self.assertIsInstance(current_time_from_tm1_serial_date, float)
self.assertGreater(current_time_from_excel_serial_date, current_time_from_tm1_serial_date)
def test_build_pandas_dataframe_from_cellset(self):
rows = [
DimensionSelection(dimension_name=self.dim1_name),
DimensionSelection(dimension_name=self.dim2_name,
elements=self.dim2_element_names)
]
columns = [
DimensionSelection(dimension_name=self.dim3_name,
expression="TM1SubsetAll([{}])".format(
self.dim3_name)),
DimensionSelection(dimension_name=self.dim4_name,
subset=self.dim4_subset_Name)
]
suppress = None
mdx = MDXUtils.construct_mdx(cube_name=self.cube_name,
rows=rows,
columns=columns,
suppress=suppress)
cellset = self.tm1.cubes.cells.execute_mdx(mdx)
df = Utils.build_pandas_dataframe_from_cellset(cellset,
multiindex=False)
self.assertTrue(df.shape[0] == 1000)
# cater for potential Sandboxes dimension on first position
if df.columns[0] == "Sandboxes":
self.assertTrue(df.shape[1] == 6)
else:
self.assertTrue(df.shape[1] == 5)
self.assertIsInstance(df, pd.DataFrame)
cellset = Utils.build_cellset_from_pandas_dataframe(df)
self.assertTrue(len(cellset.keys()) == 1000)
self.assertIsInstance(cellset, Utils.CaseAndSpaceInsensitiveTuplesDict)
def test_cellset_and_pandas_df(self):
rows = {self.dim1_name: None, self.dim2_name: self.dim2_element_names}
columns = {
self.dim3_name: "{{TM1SubsetAll([{}])}}".format(self.dim3_name)
}
contexts = {self.dim4_name: self.dim4_element_names[0]}
suppress = None
mdx = MDXUtils.construct_mdx(cube_name=self.cube_name,
rows=rows,
columns=columns,
contexts=contexts,
suppress=suppress)
cellset = self.tm1.cubes.cells.execute_mdx(mdx)
df = Utils.build_pandas_dataframe_from_cellset(cellset,
multiindex=True)
self.assertIsInstance(df, pd.DataFrame)
self.assertTrue(df.shape[0] == 1000)
self.assertTrue(df.shape[1] == 1)
cellset = Utils.build_cellset_from_pandas_dataframe(df)
self.assertTrue(len(cellset.keys()) == 1000)
self.assertIsInstance(cellset, Utils.CaseAndSpaceInsensitiveTuplesDict)
df = Utils.build_pandas_dataframe_from_cellset(cellset,
multiindex=False)
self.assertTrue(df.shape[0] == 1000)
self.assertTrue(df.shape[1] == 5)
self.assertIsInstance(df, pd.DataFrame)
cellset = Utils.build_cellset_from_pandas_dataframe(df)
self.assertTrue(len(cellset.keys()) == 1000)
self.assertIsInstance(cellset, Utils.CaseAndSpaceInsensitiveTuplesDict)
def test_extract_unique_name_from_members(self):
members = [
{'UniqueName': '[Dimension3].[Dimension3].[Element 592]',
'Element': {'UniqueName': '[Dimension3].[Dimension3].[Element 592]'}}]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
["[Dimension3].[Dimension3].[Element 592]"])
members = [{'UniqueName': '[Dimension1].[Dimension1].[Element 790]',
'Element': {'UniqueName': '[Dimension1].[Dimension1].[Element 790]'}},
{'UniqueName': '[Dimension2].[Dimension2].[Element 541]',
'Element': {'UniqueName': '[Dimension2].[Dimension2].[Element 541]'}}]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
["[Dimension1].[Dimension1].[Element 790]", "[Dimension2].[Dimension2].[Element 541]"])
members = [{'UniqueName': '',
'Element': {'UniqueName': '[Dimension1].[Dimension1].[Element 790]'}},
{'UniqueName': '',
'Element': {'UniqueName': '[Dimension2].[Dimension2].[Element 541]'}}]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
["[Dimension1].[Dimension1].[Element 790]", "[Dimension2].[Dimension2].[Element 541]"])
members = [{'UniqueName': '[Dimension1].[Dimension1].[Element 790]',
'Element': None},
{'UniqueName': '[Dimension2].[Dimension2].[Element 541]',
'Element': None}]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
["[Dimension1].[Dimension1].[Element 790]", "[Dimension2].[Dimension2].[Element 541]"])
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 test_extract_unique_name_from_members(self):
members = [
{
"UniqueName": "[Dimension3].[Dimension3].[Element 592]",
"Element": {"UniqueName": "[Dimension3].[Dimension3].[Element 592]"},
}
]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
["[Dimension3].[Dimension3].[Element 592]"],
)
members = [
{
"UniqueName": "[Dimension1].[Dimension1].[Element 790]",
"Element": {"UniqueName": "[Dimension1].[Dimension1].[Element 790]"},
},
{
"UniqueName": "[Dimension2].[Dimension2].[Element 541]",
"Element": {"UniqueName": "[Dimension2].[Dimension2].[Element 541]"},
},
]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
[
"[Dimension1].[Dimension1].[Element 790]",
"[Dimension2].[Dimension2].[Element 541]",
],
)
members = [
{
"UniqueName": "",
"Element": {"UniqueName": "[Dimension1].[Dimension1].[Element 790]"},
},
{
"UniqueName": "",
"Element": {"UniqueName": "[Dimension2].[Dimension2].[Element 541]"},
},
]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
[
"[Dimension1].[Dimension1].[Element 790]",
"[Dimension2].[Dimension2].[Element 541]",
],
)
members = [
{"UniqueName": "[Dimension1].[Dimension1].[Element 790]", "Element": None},
{"UniqueName": "[Dimension2].[Dimension2].[Element 541]", "Element": None},
]
self.assertEqual(
Utils.extract_unique_names_from_members(members),
[
"[Dimension1].[Dimension1].[Element 790]",
"[Dimension2].[Dimension2].[Element 541]",
],
)
def execute_cellset(self, cellset_id, cell_properties=None, top=None):
""" Execute Cellset and return the cells with their properties
:param cellset_id:
:param cell_properties: properties to be queried from the cell. E.g. Value, Ordinal, RuleDerived, ...
:param top: integer
:return: Content in sweet consice strcuture.
"""
if not cell_properties:
cell_properties = ['Value', 'Ordinal']
elif 'Ordinal' not in cell_properties:
cell_properties.append('Ordinal')
request = "/api/v1/Cellsets('{cellset_id}')?$expand=" \
"Cube($select=Name;$expand=Dimensions($select=Name))," \
"Axes($expand=Tuples($expand=Members($select=Name;$expand=Element($select=UniqueName)){top_rows}))," \
"Cells($select={cell_properties}{top_cells})" \
.format(cellset_id=cellset_id,
top_rows=";$top={}".format(top) if top else "",
cell_properties=",".join(cell_properties),
top_cells=";$top={}".format(top) if top else "")
response = self._rest.GET(request=request)
return Utils.build_content_from_cellset(
raw_cellset_as_dict=response.json(),
cell_properties=cell_properties,
top=top)
def test_odata_escape_single_quotes_in_object_names_custom_request_threads(self):
url = "https://localhost:8099/api/v1/Threads?$top=0&$filter=ObjectType eq 'Process' and " \
"ObjectName ne 'Process - Get Params REST'&$count=true"
escaped_url = Utils.odata_escape_single_quotes_in_object_names(url)
self.assertEqual(
escaped_url,
url)
def test_odata_escape_single_quotes_in_object_names_element(self):
url = "https://localhost:8099/api/v1/Dimensions('dimen'sion')/Hierarchies('hier'archy')/Elements('elem'ent')"
escaped_url = Utils.odata_escape_single_quotes_in_object_names(url)
self.assertEqual(
escaped_url,
"https://localhost:8099/api/v1/Dimensions('dimen''sion')/Hierarchies('hier''archy')/Elements('elem''ent')"
)
def get_view_content(self,
cube_name,
view_name,
cell_properties=None,
private=True,
top=None):
""" get view content as dictionary with sweet and concise structure.
Works on NativeView and MDXView !
Not Hierarchy aware !
:param cube_name: String
:param view_name: String
:param cell_properties: List, cell properties: [Values, Status, HasPicklist, etc.]
:param private: Boolean
:param top: Int, number of cells
:return: Dictionary : {([dim1].[elem1], [dim2][elem6]): {'Value':3127.312, 'Ordinal':12} .... }
"""
if not cell_properties:
cell_properties = ['Value', 'Ordinal']
cellset_as_dict = self._get_cellset_from_view(cube_name, view_name,
cell_properties, private,
top)
content_as_dict = Utils.build_content_from_cellset(
cellset_as_dict, cell_properties, top)
return content_as_dict
def execute_mdx(self, mdx, cell_properties=None, top=None):
""" Execute MDX and return the cells with their properties
:param mdx: MDX Query, as string
:param cell_properties: properties to be queried from the cell. E.g. Value, Ordinal, RuleDerived, ...
:param top: integer
:return: content in sweet consice strcuture.
"""
if not cell_properties:
cell_properties = ['Value', 'Ordinal']
elif 'Ordinal' not in cell_properties:
cell_properties.append('Ordinal')
request = '/api/v1/ExecuteMDX?$expand=' \
'Cube($select=Name;$expand=Dimensions($select=Name)),' \
'Axes($expand=Tuples($expand=Members($select=Name;$expand=Element($select=UniqueName)){})),' \
'Cells($select={}{})'.format(';$top=' + str(top) if top else '',
','.join(cell_properties),
';$top=' + str(top) if top else '')
data = {'MDX': mdx}
response = self._rest.POST(request=request,
data=json.dumps(data, ensure_ascii=False))
return Utils.build_content_from_cellset(
raw_cellset_as_dict=response.json(),
cell_properties=cell_properties,
top=top)
def test10_execute_view(self):
data = self.tm1.cubes.cells.execute_view(cube_name=cube_name,
view_name=view_name,
private=False)
# Check if total value is the same AND coordinates are the same
check_value = 0
for coordinates, value in data.items():
# grid can have null values in cells as rows and columns are populated with elements
if value['Value']:
# extract the element name from the element unique name
element_names = Utils.element_names_from_element_unqiue_names(
coordinates)
self.assertIn(element_names, self.target_coordinates)
check_value += value['Value']
# Check the check-sum
self.assertEqual(check_value, self.total_value)
# execute view with top
data = self.tm1.cubes.cells.execute_view(cube_name=cube_name,
view_name=view_name,
private=False,
top=3)
self.assertEqual(len(data.keys()), 3)
def execute_mdx(self, mdx, cell_properties=None, top=None):
""" Execute MDX and return the cells with their properties
:param mdx: MDX Query, as string
:param cell_properties: properties to be queried from the cell. Like Value, Ordinal, etc as iterable
:param top: integer
:return: content in sweet consice strcuture.
"""
if not cell_properties:
cell_properties = ['Value', 'Ordinal']
if top:
request = '/api/v1/ExecuteMDX?$expand=Cube($select=Dimensions;$expand=Dimensions($select=Name)),' \
'Axes($expand=Tuples($expand=Members($select=UniqueName);$top={})),Cells($select={};$top={})' \
.format(str(top), ','.join(cell_properties), str(top))
else:
request = '/api/v1/ExecuteMDX?$expand=Cube($select=Dimensions;$expand=Dimensions($select=Name)),' \
'Axes($expand=Tuples($expand=Members($select=UniqueName))),Cells($select={})' \
.format(','.join(cell_properties))
data = {'MDX': mdx}
cellset = self._rest.POST(request=request,
data=json.dumps(data, ensure_ascii=False))
return Utils.build_content_from_cellset(
raw_cellset_as_dict=json.loads(cellset),
cell_properties=cell_properties,
top=top)
def extract_cellset(self,
cellset_id,
cell_properties=None,
top=None,
delete_cellset=True,
skip_contexts=False):
""" Execute Cellset and return the cells with their properties
:param skip_contexts:
:param delete_cellset:
:param cellset_id:
:param cell_properties: properties to be queried from the cell. E.g. Value, Ordinal, RuleDerived, ...
:param top: integer
:return: Content in sweet consice strcuture.
"""
if not cell_properties:
cell_properties = ['Value']
raw_cellset = self.extract_cellset_raw(
cellset_id,
cell_properties=cell_properties,
elem_properties=['UniqueName'],
member_properties=['UniqueName'],
top=top,
skip_contexts=skip_contexts,
delete_cellset=delete_cellset)
return Utils.build_content_from_cellset(
raw_cellset_as_dict=raw_cellset, top=top)
def test_build_element_unique_names_without_hierarchies(self):
dimension_names = ["dim1", "dim1"]
element_names = ["elem1", "elem2"]
gen = Utils.build_element_unique_names(dimension_names=dimension_names, element_names=element_names)
element_unique_names = list(gen)
self.assertEqual(len(element_unique_names), 2)
self.assertTrue("[dim1].[elem1]" in element_unique_names)
self.assertTrue("[dim1].[elem2]" in element_unique_names)
def test_execute_mdx_skip_contexts(self):
mdx = MDX_TEMPLATE.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element1]}",
cube=CUBE_NAME,
where="[" + DIMENSION_NAMES[2] + "].[Element1]")
data = self.tm1.cubes.cells.execute_mdx(mdx, 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_extract_axes_from_cellset(self):
with open(os.path.join("resources", "raw_cellset.json")) as file:
raw_cellset_as_dict = json.load(file)
row_axis, column_axis, title_axis = Utils.extract_axes_from_cellset(raw_cellset_as_dict=raw_cellset_as_dict)
self.assertIn("[City].[City].[NYC]", json.dumps(row_axis))
self.assertIn("[City].[City].[Chicago]", json.dumps(row_axis))
self.assertIn("[Date].[Date].[2017-11-26]", json.dumps(column_axis))
self.assertIn("[Date].[Date].[2017-11-27]", json.dumps(column_axis))
self.assertIn("[Version].[Version].[Actual]", json.dumps(title_axis))
def _url_and_body(self, request, data, odata_escape_single_quotes_in_object_names=True):
""" create proper url and payload
"""
url = self._base_url + request
url = url.replace(' ', '%20').replace('#', '%23')
if odata_escape_single_quotes_in_object_names:
url = Utils.odata_escape_single_quotes_in_object_names(url)
if type(data) is not bytes:
data = data.encode('utf-8')
return url, data
def execute_view_ui_array(self,
cube_name,
view_name,
private=False,
elem_properties=None,
member_properties=None,
value_precision=2,
top=None):
"""
Useful for grids or charting libraries that want an array of cell values per row.
Returns 3-dimensional cell structure for tabbed grids or multiple charts.
Rows and pages are dicts, addressable by their name. Proper order of rows can be obtained in headers[1]
Example 'cells' return format:
'cells': {
'10100': {
'Net Operating Income': [ 19832724.72429739,
20365654.788303416,
20729201.329183243,
20480205.20121749],
'Revenue': [ 28981046.50724231,
29512482.207418434,
29913730.038971487,
29563345.9542385]},
'10200': {
'Net Operating Income': [ 9853293.623709997,
10277650.763958748,
10466934.096533755,
10333095.839474997],
'Revenue': [ 13888143.710000003,
14300216.43,
14502421.63,
14321501.940000001]}
},
:param top:
:param cube_name: cube name
:param view_name: view name
:param private: True (private) or False (public)
:param elem_properties: List of properties to be queried from the elements. E.g. ['UniqueName','Attributes', ...]
:param member_properties: List properties to be queried from the member. E.g. ['Name', 'UniqueName']
:param value_precision: Integer (optional) specifying number of decimal places to return
:return: dict : { titles: [], headers: [axis][], cells: { Page0: { Row0: { [row values], Row1: [], ...}, ...}, ...} }
"""
cellset_id = self.create_cellset_from_view(cube_name=cube_name,
view_name=view_name,
private=private)
data = self.extract_cellset_raw(
cellset_id=cellset_id,
cell_properties=["Value"],
elem_properties=elem_properties,
member_properties=list(set(member_properties or []) | {"Name"}),
top=top,
delete_cellset=True)
return Utils.build_ui_arrays_from_cellset(
raw_cellset_as_dict=data, value_precision=value_precision)
def test_build_element_unique_names_with_hierarchies(self):
dimension_names = ["dim1", "dim1", "dim1"]
hierarchy_names = ["hier1", "hier2", "hier3"]
element_names = ["elem1", "elem2", "elem3"]
gen = Utils.build_element_unique_names(
dimension_names=dimension_names, hierarchy_names=hierarchy_names, element_names=element_names)
element_unique_names = list(gen)
self.assertEqual(len(element_unique_names), 3)
self.assertTrue("[dim1].[hier1].[elem1]" in element_unique_names)
self.assertTrue("[dim1].[hier2].[elem2]" in element_unique_names)
self.assertTrue("[dim1].[hier3].[elem3]" in element_unique_names)
def test2_read_cube_name_from_mdx(self):
all_cube_names = self.tm1.cubes.get_all_names()
all_cube_names_normalized = [
cube_name.upper().replace(" ", "") for cube_name in all_cube_names
]
for cube_name in all_cube_names:
private_views, public_views = self.tm1.cubes.views.get_all(
cube_name)
for view in private_views + public_views:
mdx = view.MDX
cube_name = Utils.read_cube_name_from_mdx(mdx)
self.assertIn(cube_name, all_cube_names_normalized)
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_mdx_rows_and_cells_two_dimensions_on_rows(self):
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_SHORT.format(rows=rows,
columns=columns,
cube=CUBE_NAME)
data = self.tm1.cubes.cells.execute_mdx_rows_and_cells(mdx)
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 test_execute_mdx_raw_skip_contexts(self):
mdx = MDX_TEMPLATE.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element1]}",
cube=CUBE_NAME,
where="[" + DIMENSION_NAMES[2] + "].[Element1]")
raw_response = self.tm1.cubes.cells.execute_mdx_raw(
mdx, 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])
def test_execute_with_return_success(self):
process = Utils.load_bedrock_from_github("Bedrock.Server.Wait")
if not self.tm1.processes.exists(process.name):
self.tm1.processes.create(process)
# with parameters
success, status, error_log_file = self.tm1.processes.execute_with_return(
process_name=process.name,
pWaitSec=2)
self.assertTrue(success)
self.assertEqual(status, "CompletedSuccessfully")
self.assertIsNone(error_log_file)
# without parameters
success, status, error_log_file = self.tm1.processes.execute_with_return(
process_name=process.name)
self.assertTrue(success)
self.assertEqual(status, "CompletedSuccessfully")
self.assertIsNone(error_log_file)
def relative_proportional_spread(self,
value,
cube,
unique_element_names,
reference_unique_element_names,
reference_cube=None):
""" Execute relative proportional spread
:param value: value to be spread
:param cube: name of the cube
:param unique_element_names: target cell coordinates as unique element names (e.g. ["[d1].[c1]","[d2].[e3]"])
:param reference_cube: name of the reference cube. Can be None
:param reference_unique_element_names: reference cell coordinates as unique element names
:return:
"""
mdx = """
SELECT
{{ {rows} }} ON 0
FROM [{cube}]
""".format(rows="}*{".join(unique_element_names), cube=cube)
cellset_id = self.create_cellset(mdx=mdx)
payload = {
"BeginOrdinal":
0,
"Value":
"RP" + str(value),
"*****@*****.**":
list(),
"*****@*****.**":
"Cubes('{}')".format(reference_cube if reference_cube else cube)
}
reference_element_template = "Dimensions('{}')/Hierarchies('{}')/Elements('{}')"
for unique_element_name in reference_unique_element_names:
payload["*****@*****.**"].append(
reference_element_template.format(
*Utils.dimension_hierarchy_element_tuple_from_unique_name(
unique_element_name)))
self._post_against_cellset(cellset_id=cellset_id,
payload=payload,
delete_cellset=True)
def get_view_content(self,
cube_name,
view_name,
cell_properties=None,
private=True,
top=None):
warnings.simplefilter('always', PendingDeprecationWarning)
warnings.warn("Function deprecated. Use execute_view instead.",
PendingDeprecationWarning)
warnings.simplefilter('default', PendingDeprecationWarning)
if not cell_properties:
cell_properties = ['Value', 'Ordinal']
elif 'Ordinal' not in cell_properties:
cell_properties.append('Ordinal')
cellset_as_dict = self._get_cellset_from_view(cube_name, view_name,
cell_properties, private,
top)
content_as_dict = Utils.build_content_from_cellset(
cellset_as_dict, cell_properties, top)
return content_as_dict
def test_execute_process(self):
process = Utils.load_bedrock_from_github("Bedrock.Server.Wait")
if not self.tm1.processes.exists(process.name):
self.tm1.processes.create(process)
# with parameters argument
start_time = time.time()
self.tm1.processes.execute(process.name, parameters={"Parameters": [
{"Name": "pWaitSec", "Value": "3"}]})
elapsed_time = time.time() - start_time
self.assertGreater(elapsed_time, 3)
# with kwargs
start_time = time.time()
self.tm1.processes.execute(process.name, pWaitSec="1")
elapsed_time = time.time() - start_time
self.assertGreater(elapsed_time, 1)
# without arguments
self.tm1.processes.execute(process.name)
def generate_attribute_df(tm1, dimension, attributeName=None):
"""
:param tm1: TM1Py TM1 Connection
:param dimension: dimension for retrieve
:return: pandas data frame containing all attributes
"""
# define MDX Query
if attributeName is None:
mdx = 'SELECT [}}ElementAttributes_{}].[}}ElementAttributes_{}].ALLMEMBERS ON 0,'\
'[{}].[{}].ALLMEMBERS ON 1 ' \
'FROM [}}ElementAttributes_{}]'\
.format(dimension, dimension, dimension, dimension, dimension)
else:
mdx = 'SELECT {{[}}ElementAttributes_{}].[}}ElementAttributes_{}].[{}]}} ON 0, ' \
'[{}].[{}].ALLMEMBERS ON 1 ' \
'FROM [}}ElementAttributes_{}]'\
.format(dimension, dimension, attributeName, dimension, dimension, dimension)
# Get data from cube through MDX
CubeData = tm1.cubes.cells.execute_mdx(mdx)
# Build pandas DataFrame fram raw cellset data
df = Utils.build_pandas_dataframe_from_cellset(CubeData)
return df
def execute_mdx_ui_dygraph(self,
mdx,
elem_properties=None,
member_properties=None,
value_precision=2,
top=None):
""" Execute MDX get dygraph dictionary
Useful for grids or charting libraries that want an array of cell values per column
Returns 3-dimensional cell structure for tabbed grids or multiple charts
Example 'cells' return format:
'cells': {
'10100': [
['Q1-2004', 28981046.50724231, 19832724.72429739],
['Q2-2004', 29512482.207418434, 20365654.788303416],
['Q3-2004', 29913730.038971487, 20729201.329183243],
['Q4-2004', 29563345.9542385, 20480205.20121749]],
'10200': [
['Q1-2004', 13888143.710000003, 9853293.623709997],
['Q2-2004', 14300216.43, 10277650.763958748],
['Q3-2004', 14502421.63, 10466934.096533755],
['Q4-2004', 14321501.940000001, 10333095.839474997]]
},
:param top:
:param mdx: String, valid MDX Query
:param elem_properties: List of properties to be queried from the elements. E.g. ['UniqueName','Attributes', ...]
:param member_properties: List of properties to be queried from the members. E.g. ['UniqueName','Attributes', ...]
:param value_precision: Integer (optional) specifying number of decimal places to return
:return: dict : { titles: [], headers: [axis][], cells: { Page0: [ [column name, column values], [], ... ], ...} }
"""
cellset_id = self.create_cellset(mdx)
data = self.extract_cellset_raw(
cellset_id=cellset_id,
cell_properties=["Value"],
elem_properties=elem_properties,
member_properties=list(set(member_properties or []) | {"Name"}),
top=top,
delete_cellset=True)
return Utils.build_ui_dygraph_arrays_from_cellset(
raw_cellset_as_dict=data, value_precision=value_precision)
