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)