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_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 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
- Calculate statistical measures on dataset (mean, median, std)
"""
import configparser
config = configparser.ConfigParser()
config.read('..\config.ini')
from TM1py.Services import TM1Service
from TM1py.Utils import Utils
with TM1Service(**config['tm1srv01']) as tm1:
# define MDX Query
mdx = "SELECT {[plan_time].[Jan-2004]:[plan_time].[Dec-2004]} * {[plan_chart_of_accounts].[Revenue]," \
"[plan_chart_of_accounts].[COS], [plan_chart_of_accounts].[Operating Expense], " \
"[plan_chart_of_accounts].[Net Operating Income]} on ROWS, "\
"{[plan_version].[FY 2004 Budget]} on COLUMNS " \
"FROM [plan_BudgetPlan] " \
"WHERE ([plan_business_unit].[10110],[plan_department].[410], "\
"[plan_exchange_rates].[local],[plan_source].[input]) "
# Get data from P&L cube through MDX
pnl_data = tm1.cubes.cells.execute_mdx(mdx)
# Build pandas DataFrame fram raw cellset data
df = Utils.build_pandas_dataframe_from_cellset(pnl_data)
print(df)
# Calculate Statistical measures for dataframe
print(df.describe())
def query_formats(mdx,
cell_properties=None,
elem_properties=None,
member_properties=None,
value_precision=None,
top=None):
print("Query:")
print(mdx)
# raw: native response dictionary
print("\nRaw (native) format")
raw_data = tm1.cubes.cells.execute_mdx_raw(
mdx=mdx,
cell_properties=cell_properties,
elem_properties=elem_properties,
member_properties=member_properties,
top=top)
pp.pprint(raw_data)
# default: (Case and space insensitive) dictionary
print("\nDefault: (Case and space insensitive) dictionary")
data = tm1.cubes.cells.execute_mdx(mdx,
cell_properties=cell_properties)
print(data)
# Note: can be converted into pandas dataframe with Utility function:
print("\nCoverted to dataframe")
df = Utils.build_pandas_dataframe_from_cellset(data, multiindex=False)
pp.pprint(df)
# values: get only the cell values
print("\nValues only")
values = tm1.cubes.cells.execute_mdx_values(mdx)
for value in values:
print(value)
# csv: get cellset content as raw csv (Zero / null is suppressed by default, Context dimensions are omitted)
print("\nCSV")
csv = tm1.cubes.cells.execute_mdx_csv(mdx)
print(csv)
# dataframe: get cellset content as pandas dataframe (Zero / null is suppressed by default, Context dimensions are omitted)
print("\ndataframe (pandas)")
df = tm1.cubes.cells.execute_mdx_dataframe(mdx)
print(df)
# array
print("\nArray: for grids and charts")
array = tm1.cubes.cells.execute_mdx_ui_array(
mdx=mdx,
value_precision=value_precision,
elem_properties=elem_properties,
member_properties=member_properties)
pp.pprint(array)
# dygraph
print(
"\nDygraph: for charts that want columns of data, instead of rows")
dygraph = tm1.cubes.cells.execute_mdx_ui_dygraph(
mdx=mdx,
value_precision=value_precision,
elem_properties=elem_properties,
member_properties=member_properties)
pp.pprint(dygraph)
