search

cubewise-code / tm1py

TM1py is a Python package that wraps the TM1 REST API in a simple to use library.
http://tm1py.readthedocs.io/en/latest/
MIT License
190 stars 109 forks source link

MDX Statements #153

Closed jbeanz23 closed 5 years ago

jbeanz23 commented 5 years ago

Describe what did you try to do with TM1py Hi - When building the mdx queries through TM1py, do they accept Intersect and/or Union operators? I was not seeing any examples of this but i have a large amount of data that i need to compare against another view just on one dimension. The source dimension (Material Staging) has many more elements with a PO line then there are in the same cube with NSB Gross. The loop is looking for 57,000 elements by NSB and 37,000 we know do not have NSB. Is there a way to limit the set? I am already limiting it by values from the PO Lines view but since many do not have NSB it is still taking a long time to run. Is there a way you suggest, based on my code, that could make this more efficient?

Describe what's not working the way you expect Didn't get the expected result? Describe: The results are not incorrect, juts very slow right now, even though the amount of loops has been limited. I am attaching the current version.

Thanks

Version

Additional context scratch_AMEROccupancy2.zip

If you encounter an error, please add the error message and the stack trace

MariusWirtz commented 5 years ago

Hi,

could you perhaps just use a simple FILTER around the set expression, to filter your dimension by values from a different cube, like this? {FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 0 )} More details here: https://www.bihints.com/filter

By the way, iterating through a pandas dataframe is an anti-pattern. It should be avoided if possible. Here are more details: https://stackoverflow.com/questions/16476924/how-to-iterate-over-rows-in-a-dataframe-in-pandas/55557758#55557758

jbeanz23 commented 5 years ago

Thanks Marius I will try one of the other ways mentioned.....still learning how and what works best with these different libraries!

MariusWirtz commented 5 years ago

Perhaps if you use built-in data structures like lists and dictionaries it could be faster

jbeanz23 commented 5 years ago

OK, i will try re-doing this using one of those methods. Thank you!