Tm1py: Subset used in MDX query

[jbeanz23]

Describe what did you try to do with TM1py
Does anyone know if you can assign a subset to the rows in an MDX query? I am trying to use a subset I created on a new MDX pull but am not sure how to do this. Also, does anyone know if you can use the roll-up of the subset in the MDX query (this would be preferred)?

Describe what's not working the way you expect
Didn't get the expected result? Describe:
I have a script creating a subset by subtracting elements that have data in 1 or both of 2 cubes from all elements in the dimension, leaving those with no data, code is below. I want to then create a new MDX query against just this subset, or even the rolled-up subset so I can drill down to all accounts. I cannot do this initially because I run out of memory.

df2 = tm1.cubes.cells.execute_mdx_dataframe(mdx2)
for _, row in df2.iterrows():
    material = row['Material EMEA']
    used_assumption_materials.add(material)

unused_materials = set(all_materials) - used_staging_materials - used_assumption_materials

dimension_name = "Material EMEA"
subset_name = "Unused Materials EMEA"
elements = set(unused_materials)

s = Subset(dimension_name=dimension_name, subset_name=subset_name, alias='', elements=elements)
tm1.dimensions.subsets.create(subset=s, private=False)

Preferably in the next query (below) would like to use the roll-up of the subset created for the Materials and then just drill down on all N level accounts. I am using all the accounts that had data when I checked this because i don't know how to use a roll-up or the subset. I want to remove the elements from the first set that have data at N level accounts that gets reversed. and total to 0 at Total Account.

mdx = """
   SELECT 
   NON EMPTY {{TM1FILTERBYLEVEL( TM1SUBSETALL( [Material EMEA] ), 0)}} *
   {{[GL Account].[40100000],[GL Account].[40101500],[GL Account].[50156000],
   [GL Account].[40200000],[GL Account].[40120000],[GL Account].[40850000],[GL Account].[40880000],
   [GL Account].[40160000],[GL Account].[50100000],[GL Account].[50199300],[GL Account].[50200000],
   [GL Account].[50120000],[GL Account].[50810000],[GL Account].[50860000],[GL Account].[50880000]}} ON ROWS,

Version

• TM1py - 1.4.1
• TM1 Server Version: 2.0.9.1

Additional context
Thanks

[rkvinoth]

Hi @jbeanz23

IMO, if you have the list of elements filtered better use them directly in the MDX instead creating a subset with them.

For the first part of your question: You can use the subset name just like any other element in a dimension in the MDX query, you can also use the TM1SUBSETTOSET function {{TM1SUBSETTOSET([Material EMEA], {subset_name})}}.
For the second part of your question: I'm not sure if this can be achieved with REST based MDX requests. I know it works in Architect or Perspectives.

Instead of using iterrows you can call unique on a series to get the unique elements (much faster).

df2 = tm1.cubes.cells.execute_mdx_dataframe(mdx2)
used_assumption_materials = set(df2['Material EMEA'].unique())

unused_materials = set(all_materials) - used_staging_materials - used_assumption_materials

dimension_name = "Material EMEA"
subset_name = "Unused Materials EMEA"
elements = set(unused_materials)

s = Subset(dimension_name=dimension_name, subset_name=subset_name, alias='', elements=elements)
tm1.dimensions.subsets.create(subset=s, private=False)

mdx = f"""
   SELECT 
   NON EMPTY {{[Material EMEA].[{subset_name}]}} *
   {{[GL Account].[40100000],[GL Account].[40101500],[GL Account].[50156000],
   [GL Account].[40200000],[GL Account].[40120000],[GL Account].[40850000],[GL Account].[40880000],
   [GL Account].[40160000],[GL Account].[50100000],[GL Account].[50199300],[GL Account].[50200000],
   [GL Account].[50120000],[GL Account].[50810000],[GL Account].[50860000],[GL Account].[50880000]}} ON ROWS,

[rclapp]

@jbeanz23 @rkvinoth

Rather than issuing multiple queries, finding the common (or dissimilar) members and then querying again, I would suggest doing the filtering directly in MDX. The query will be "longer" but you can rely on server side optimizations to improve the speed. Once that query works, you can add a dynamic total via a calculated member.

From what I understand, you have a query that returns a set of members on rows, you then want to exclude those members from the the subsequent query. The query would take this format.

SELECT NON EMPTY EXCEPT( {TM1SUBSETALL([Material EMEA])}, {FILTER( {TM1SUBSETALL([Material EMEA])}, [Cube 1].([Dim1].[H1].[M1], [Dim2].[H2].[M1]) <>0)})

The row set leverages both except and filter to get the elements you want.

On the memory issue, is TM1 giving you a view memory error, or is python running out of RAM?

[jbeanz23]

Hey @rclapp
I guess TM1 because in Pulse I see Memory Pool exceeded and Tm1py says something similar.

The problem is I am needing to query from 2 different cubes that have a common dimension that has 1.9 million elements. I need to find the unused elements, meaning no data in either cube for any period. The primary cube is a transaction cube which has multiple dimensions with over 1 millions elements. The 2nd cube has less dimensions but also includes huge dimensions. I need every N level element from Time dimension, due to reversals, which is 3 years of periods. For the primary cube I also need all N level Accounts due to reversals. I need to drill down into individual months and accounts against 1.9 million N level materials.

If I use the subset (which is 1.3 million with no data at Total Account) I cannot drill down to account, because I run out of memory with 40 periods and all N level accounts. Is this achievable using some type of filter and exception statement above?

@rkvinoth - thanks for the tip about iterating. That will save me lots of time.

[rkvinoth]

This looks like a hardcore exercise. Since you are only worried about the Time and Material EMEA dimensions, lookup all the other dimensions at top-level and take Time dimension to the column.

Send concurrent requests to the TM1 server by splitting the subset elements

Something like below should help you.. just pass the mdx_list to a ThreadPoolExecutor. Handle the MDX in a separate function.

from concurrent.futures import ThreadPoolExecutor
from TM1py.Services import TM1Service

dim_name = 'Material EMEA'
element_list = ['item1', 'item2', 'item3', 'item4', 'item5', 'item6', 'item7']

elements = ["[Material EMEA].[" + item + "]" for item in element_list]
mdx_list = []
subset_size = 3
counter = 0
thread_count = 20

for n in range(subset_size, len(elements) + 1, subset_size):
    mdx = f"""
       SELECT
       NON EMPTY {{[Material EMEA].[{','.join(elements[counter:n])}]}} *
       {{TM1FILTERBYLEVEL( {{TM1SUBSETALL( [GL Account] )}}, 0)}} ON ROWS,"""
    mdx_list.append(mdx)
    counter = n

if len(elements) - counter:
    mdx = f"""
       SELECT
       NON EMPTY {{[Material EMEA].[{','.join(elements[counter:len(elements)])}]}} *
       {{TM1FILTERBYLEVEL( {{TM1SUBSETALL( [GL Account] )}}, 0)}} ON ROWS,"""
    mdx_list.append(mdx)

with ThreadPoolExecutor(max_workers=thread_count) as executor:
            executor.map(function_name, mdx_list)

[MariusWirtz]

@jbeanz23
Since your initial question is answered I will close this issue. Feel free to re-open if necessary

[vamsi-cloud]

While executing below MDX TM1 instance is crashing because of hefty amount of cell combinations . so i split the dept count based on attribute condition shown below .but mdx is not taking these list items . any one of the below mentioned points solution is ok for me
1.How to pass list items into MDX
2.How to define a specific subset in MDX query

1. How to extract leaf elements based on Parent Element input

######MDX for Filtering dept with attribute###########

mdx="""
       SELECT
       NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL([Department])},0)} ON ROWS,
       NON EMPTY {[}ElementAttributes_Department].[RD_D]} ON COLUMNS
       FROM [}ElementAttributes_Department] """

DF_Attr=tm1.cubes.cells.execute_mdx_dataframe(mdx)
Dept_List=DF_Attr['Department'].to_list()

#################
mdx_list=[]

for Dept in Dept_List:
    mdx="""
        SELECT
            NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL([Level Type])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Shift Type])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Level Class])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Skills])},0)}
              *{TM1FILTERBYLEVEL({TM1SUBSETALL([Location])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Location Type])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Level])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Month])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Resource Unit])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Role])},0)}*
              {TM1FILTERBYLEVEL({TM1SUBSETALL([Vendor])},0)}*

             ON ROWS,
              NON EMPTY {[Measure HC FTE].[FTE (PlanYear)]} ON COLUMNS
              FROM [Input_HC_FTE_for_Externals]
              WHERE ([Version].[Version 1],[Scenario].[1EA],[Year].[2021],[Department].[{Dept}])"""

[rkvinoth]

Wim Gielis has this wonderful page for learning/working with MDX statements, I suggest that you go through it.

For your first question - Check this reply from me
For your second question - Check this reply from me
For your third question - TM1FilterByLevel( Descendants( [<Dimension name>].[<Parent element>]), 0)

[vamsi-cloud]

I have executed the MDX query in threads but i am not getting data output file .below is the script

from TM1py.Services import TM1Service
from TM1py.Utils import Utils
import pandas as pd
from concurrent.futures import ThreadPoolExecutor
import re

tm1=TM1Service(address='****',port=22222,user='abcd',password='ccccc',ssl=True,namespace='Active Directory')

mdx="""
SELECT
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL([Level Type])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Shift Type])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Level Class])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Skills])},0)}
{TM1FILTERBYLEVEL({TM1SUBSETALL([Location])},0)}
{TM1FILTERBYLEVEL({TM1SUBSETALL([Location Type])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Level])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Month])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Resource Unit])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Role])},0)}*
{TM1FILTERBYLEVEL({TM1SUBSETALL([Vendor])},0)}*
{TM1FilterByLevel( Descendants( [Department].[1100]), 0)}
ON ROWS,
NON EMPTY {[Measure HC FTE].[FTE (PlanYear)]} ON COLUMNS
FROM [Input_HC_FTE_for_Externals]
WHERE ([Version].[Version 1],[Scenario].[1EA],[Year].[2021])"""

def export_data(expression):
data=tm1.cubes.cells.execute_mdx_datafrmae(expression)
data.to_csv('C:\Vamsi_IMP_Documents\test.csv')

thread_count=10
with ThreadPoolExecutor(max_workers=thread_count) as executor:
executor.map(export_data,mdx)

[jrobinsonAG]

dataframe is misspelt?

data=tm1.cubes.cells.execute_mdx_datafrmae(expression)

[vamsi-cloud]

Tq jrobinsonAG , Yeah i spot that and corrected but still i am not getting a CSV with Data

[jrobinsonAG]

have you checked the mdx statement against the cube directly, via PAfE or another interface?

[vamsi-cloud]

yes i have checked MDX is working fine with one Department as condition

[jrobinsonAG]

what is the state of the data object just prior to the data.to_csv operation?

[jrobinsonAG]

you may need to use a more robust string expression for your file name.

for instance escaping the \
data.to_csv('C:\\Vamsi_IMP_Documents\\test.csv')

[vamsi-cloud]

what is the state of the data object just prior to the data.to_csv operation?

it is data frame

[vamsi-cloud]

you may need to use a more robust string expression for your file name.

for instance escaping the \
data.to_csv('C:\\Vamsi_IMP_Documents\\test.csv')

i have modified still the same issue

[cubewise-nswan]

Just confirming, is the MDX statement that one that is being executed? I think you might be missing * on the Skills and Location lines

[rclapp]

I would remove your file path and just put a file name like test.csv. It will put the file in your py directory. Chances are you either are not working in the directory you think you are, or you don't have proper permissions to write to the location.

Sent from my mobile phone

On Jun 22, 2021 10:48 PM, vamsi-cloud @.*> wrote:

you may need to use a more robust string expression for your file name.

for instance escaping the \
data.to_csv('C:\Vamsi_IMP_Documents\test.csv')

i have modified still the same issue

-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/tm1py/issues/284#issuecomment-866547298, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEK7GZXR3KG62NJYHZP6BCLTUFYPPANCNFSM4O2W55GA.

[rkvinoth]

@vamsi-cloud May be you need to revisit your use case here. If you just want to export the data, you can use unbound TI process to export the data into a csv file.
If it is just exporting a cube into a csv file, do you even need TM1py?

Also, you never mentioned if you received any error. If it is out of memory that you are getting, then you need to break down the MDX further.

[vamsi-cloud]

@vamsi-cloud May be you need to revisit your use case here. If you just want to export the data, you can use unbound TI process to export the data into a csv file.
If it is just exporting a cube into a csv file, do you even need TM1py?

Also, you never mentioned if you received any error. If it is out of memory that you are getting, then you need to break down the MDX further.
@rkvinoth actaully i can take CSV from tm1 to fetch the data but this mdx is not just to fetch data but after this we are performing operations on top of this data .and whole point of using MDX is to automate the Script in the first step we are facing this issue so thought of passing it in threads to process

[rkvinoth]

In a normal TI process you can use prolog to create the source view, in the data tab export the data into a csv file.
In the epilog call the python script which will load this file into memory and do the work for you.