Execute MDX Functions raise exceptions with "WITH MEMBER" is used in MDX

[rclapp]

Describe the bug
Execute MDX as dataframe functions do not support WITH MEMBER AS notation in MDX. Functions return exception
File "/var/task/tm1_service/api/data_fetcher_handler.py", line 146, in _execute_future future.result() File "/var/lang/lib/python3.8/concurrent/futures/_base.py", line 437, in result return self.__get_result() File "/var/lang/lib/python3.8/concurrent/futures/_base.py", line 389, in __get_result raise self._exception File "/var/lang/lib/python3.8/concurrent/futures/thread.py", line 57, in run result = self.fn(*self.args, **self.kwargs) File "/var/task/tm1_service/api/data_fetcher_handler.py", line 156, in _fetch_and_write_query_data tm1_response = tm1_repo.execute_mdx_query(query, dtype) File "/var/task/tm1_service/repository/tm1_repository.py", line 18, in execute_mdx_query result_data_frame = self._tm1_client.cubes.cells.execute_mdx_dataframe( File "/var/task/TM1py/Utils/Utils.py", line 52, in wrapper return func(self, *args, **kwargs) File "/var/task/TM1py/Services/CellService.py", line 1066, in execute_mdx_dataframe return self.extract_cellset_dataframe(cellset_id, top=top, skip=skip, skip_zeros=skip_zeros, File "/var/task/TM1py/Utils/Utils.py", line 52, in wrapper return func(self, *args, **kwargs) File "/var/task/TM1py/Services/CellService.py", line 1756, in extract_cellset_dataframe raw_csv = self.extract_cellset_csv(cellset_id=cellset_id, top=top, skip=skip, skip_zeros=skip_zeros, File "/var/task/TM1py/Services/CellService.py", line 1727, in extract_cellset_csv return build_csv_from_cellset_dict(rows, columns, cellset_dict, line_separator=line_separator, File "/var/task/TM1py/Utils/Utils.py", line 291, in build_csv_from_cellset_dict line_items = _build_csv_line_items_from_axis_tuple( File "/var/task/TM1py/Utils/Utils.py", line 319, in _build_csv_line_items_from_axis_tuple element_name = member['Element']['Name'] if 'Element' in member and member['Element'] else member['Name'] KeyError: 'Name'

After digging through the exception I found that the error is due to how the function is getting the NAME of the Element. Query scoped members do not have an Element Name, but rather have a Member Name. Need to update the method to build a CSV from a cellset to handle this case

[zsoltmoravcsik]

That would be great, with this addition our pivot would able to use with member queries, also maybe the ARC could do this upgrade. I thought that is too problematic to solve but Ryan suggestion seems nice!

…

On 2021. Aug 12., Thu at 1:17, Ryan Clapp ***@***.***> wrote: *Describe the bug* Execute MDX functions do now support WITH MEMBER AS notation in MDX. Functions return exception File "/var/task/tm1_service/api/data_fetcher_handler.py", line 146, in _execute_future future.result() File "/var/lang/lib/python3.8/concurrent/futures/_base.py", line 437, in result return self.__get_result() File "/var/lang/lib/python3.8/concurrent/futures/_base.py", line 389, in __get_result raise self._exception File "/var/lang/lib/python3.8/concurrent/futures/thread.py", line 57, in run result = self.fn(*self.args, **self.kwargs) File "/var/task/tm1_service/api/data_fetcher_handler.py", line 156, in _fetch_and_write_query_data tm1_response = tm1_repo.execute_mdx_query(query, dtype) File "/var/task/tm1_service/repository/tm1_repository.py", line 18, in execute_mdx_query result_data_frame = self._tm1_client.cubes.cells.execute_mdx_dataframe( File "/var/task/TM1py/Utils/Utils.py", line 52, in wrapper return func(self, *args, **kwargs) File "/var/task/TM1py/Services/CellService.py", line 1066, in execute_mdx_dataframe return self.extract_cellset_dataframe(cellset_id, top=top, skip=skip, skip_zeros=skip_zeros, File "/var/task/TM1py/Utils/Utils.py", line 52, in wrapper return func(self, *args, **kwargs) File "/var/task/TM1py/Services/CellService.py", line 1756, in extract_cellset_dataframe raw_csv = self.extract_cellset_csv(cellset_id=cellset_id, top=top, skip=skip, skip_zeros=skip_zeros, File "/var/task/TM1py/Services/CellService.py", line 1727, in extract_cellset_csv return build_csv_from_cellset_dict(rows, columns, cellset_dict, line_separator=line_separator, File "/var/task/TM1py/Utils/Utils.py", line 291, in build_csv_from_cellset_dict line_items = _build_csv_line_items_from_axis_tuple( File "/var/task/TM1py/Utils/Utils.py", line 319, in _build_csv_line_items_from_axis_tuple element_name = member['Element']['Name'] if 'Element' in member and member['Element'] else member['Name'] KeyError: 'Name' After digging through the exception I found that the error is due to how the function is getting the NAME of the Element. Query scoped members do not have an Element Name, but rather have a Member Name. Need to update the method to build a CSV from a cellset to handle this case — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#593>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHNEK3GUFWA73RKTPXKA4RTT4MAIFANCNFSM5B7WOSNA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> .

[rclapp]

Arc's mdx plug in actually helped me find the problem. I wouldn't mind some feature parity though. Tm1py should support query scoped members, like ARC. ARC should support properties statements like tm1py

[MariusWirtz]

Hi @rclapp,

I can't reproduce the error. The below code executes fine for me.

from TM1py import TM1Service

with TM1Service(address='', port=12354, ssl=True, user='admin', password='apple') as tm1:
    mdx = """
    WITH MEMBER[d2].[e81] AS 2
    SELECT[d1].MEMBERS ON ROWS,
    {[d2].[e81]} ON COLUMNS
    FROM[c1]
    """

    df = tm1.cells.execute_mdx_dataframe(mdx=mdx)

    print(df.head())

it assumes that you have a simple cube c1 with 2 dimensions d1, d2 with simple elements e1,e2

[rclapp]

I missed a key point, set include_attributes to true, then you get the exception Code: mdx = """ WITH MEMBER[d2].[e81] AS 2 SELECT[d1].MEMBERS ON ROWS, {[d2].[e81]} ON COLUMNS FROM[c1] """ print(tm1.cells.execute_mdx_dataframe(mdx=mdx,include_attributes=True,top=100))

[MariusWirtz]

I can reproduce the issue. Will try to provide a fix.

[MariusWirtz]

It should be addressed with 8ae5e2d

@rclapp please double check

from TM1py import TM1Service

with TM1Service(address='', port=12354, ssl=True, user='admin', password='apple') as tm1:
    mdx = """
        WITH MEMBER[d2].[e81] AS 2
        SELECT[d1].MEMBERS  PROPERTIES [d1].[Attribute Something] ON ROWS,
        {[d2].[e81]}  PROPERTIES MEMBER_NAME ON COLUMNS
        FROM[c1]
        """

    print(tm1.cells.execute_mdx_dataframe(mdx=mdx, include_attributes=True, top=100))

[rclapp]

That fixed it!

[MariusWirtz]

Thanks for reporting the issue!