execute_mdx_csv Only Writes Out a Maximum of 6 ON ROW Dimensions

[raeldor]

Describe the bug I am using this function to write out the results of an MDX query that contains 8 dimensions on rows and 1 dimension on columns. However, the resulting file only outputs 6 row dimensions and then 1 column dimension and then the value.

To Reproduce

with TM1Service(**config['plan']) as tm1:  
        cube_name = 'AASRV Servers'  #    'plan_Report' 
        view_name = 'Everything View'    #'AACSP Communications Services'

        nv = tm1.cubes.views.get_native_view(cube_name,view_name,private=False)

        test = 'SELECT NON EMPTY [AAAA Time Periods].[level001].AllMembers*[AAAA Customer Type].[level001].AllMembers*[Geography C43].[level001].AllMembers*[Geography C43].[level002].AllMembers*[Geography C43].[level003].AllMembers*[AASRV Servers Segmentation_Max Socket].[level001].AllMembers*Descendants([AASRV Servers Segmentation_CPU].[AASRV Servers Segmentation_CPU].[CPU Family],2,LEAVES)*[AASRV Servers Segmentation_Form Factor].[level001].AllMembers on ROWS, NON EMPTY TM1SubsetAll([AASRV Servers Segmentation_Metrics]) on COLUMNS  FROM [AASRV Servers] WHERE ([AAAA 3Scenarios].[Base Case],[AAAA Editable].[Editable],[AAAA Iteration].[1Q20 Published Forecast],[AAAA Metric Type].[Actual],[AAAA Vintage].[Early May Publication])'
        print (test)

        # Extract data from CubeView as CSV
        #csv = tm1.cubes.cells.execute_view_csv(cube_name, view_name, private=False)
        csv = tm1.cubes.cells.execute_mdx_csv(test)
        # csv[0:10]
        # print(csv[0:1250]);
        f = open("c:\\Temp\\demofile2.txt", 'w', newline='')
        f.writelines(csv)
        f.close()

Expected behavior I expected 9 columns of dimensions data and a value column. Well, to be honest I expected it to also honor the pivoted ON COLUMNS as individual columns too, but that didn't happen either but I figured that was by design.

Version TM1py [e.g. 1.3.1] TM1 Server Version: [e.g. 11.4]

Additional context Add any other context about the problem here.

[MariusWirtz]

Hi @raeldor,

I can't reproduce your findings.

I just ran this script and it produces 10 columns as expected (see below). One column for every dimension that was placed in rows or columns in the MDX and one column for the values.

You have to know about this function, that the elements that are selected in the titles / where-statement are not included in the response.

I hope this helps

with TM1Service(**config["tm1srv01"]) as tm1:
    mdx = """
    SELECT
    NON EMPTY
    {Tm1SubsetAll([Business Unit])} *
    {Tm1SubsetAll([Customer])} *
    {Tm1SubsetAll([Executive])} *
    {Tm1SubsetAll([Industry])} *
    {Tm1SubsetAll([Product])} *
    {Tm1SubsetAll([State])} *
    {Tm1SubsetAll([Time])} *
    {Tm1SubsetAll([Version])} ON 0,
    NON EMPTY {Tm1SubsetAll([SalesMeasure])} ON 1
    FROM [SALES]
    """

    text = tm1.cells.execute_mdx_csv(mdx)
    for line in text.split("\r\n")[0:10]:
        print(line)

SalesMeasure,Business Unit,Customer,Executive,Industry,Product,State,Time,Version,Value
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,Total Time,Total Version,121969096.409999937
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,Total Time,Actual,60970596.7700000554
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,Total Time,Budget,60998499.6400000229
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,Total Time,Actual vs Budget,-27902.8699999991804
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,201308,Total Version,366227.289999999979
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,201308,Actual,191008.850000000006
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,201308,Budget,175218.440000000002
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,201308,Actual vs Budget,15790.409999999998
Total SalesMeasure,Total Business Unit,Total Customer,Total Executive,Total Industry,Total Product,Total State,201309,Total Version,990306.879999999539

[MariusWirtz]

If you need to create an output that includes the elements from the titles, you could query them separately from the source view, or if you are using MDX you should already know which elements you placed in the titles.

[MariusWirtz]

If you would like the response to be in the same shape (meaning the composition of dimensions on rows and columns) as the cube view, you can look into execute_view_dataframe_pivot. It returns a pandas pivot dataframe. Unfortunately it is way slower than the execute_view_csv method.

[raeldor]

Appreciate the quick response. I think I've figured out what's going on here. I think it might be to do with how you're counting the columns somewhere. I have the following in my MDX...

[Geography C43].[level001].AllMembers[Geography C43].[level002].AllMembers[Geography C43].[level003].AllMembers

To return the levels as separate columns. It works, but limits the amount of columns returned. If I remove say the 001 level then the next column that WAS missing shows up on the end.

Can you try a test your end with a similar query? Thanks!

[MariusWirtz]

I am not sure what you are trying to do is 'allowed' in TM1-MDX. Unless you are using alternative hierarchies (a.k.a Hierarchies), that was introduced with TM1 11, every dimension of a cube can be referenced only once in an MDX query.

So this you can't do: {[Region].[Level001].AllMembers} * {[Region].[Level002].AllMembers}

This, on the other hand, would be valid, assuming that the Hierarchies [RegionbyGeography] and [RegionbySalesChannel] do exist : {[Region].[RegionbyGeography].[Level001].AllMembers} * {[Region].[RegionbySalesChannel].Level002].AllMembers}

If you just need the Union of different sets, you can also do this: { {[DIMENSION].[Level001].Members} + {[DIMENSION].[Level000].Members} }

[raeldor]

Wow, really, that's not TM1-MDX compatible? I think what you're doing is a union of member items, but what I need is for the levels to be in physically separate columns. Really appreciate the fast response though, thank you. Looks like I'm going to have to find another way around this.

[MariusWirtz]

Hi @raeldor,

Yes. That's not possible unless you implement alternate hierarchies in the dimension. You can read more about it here: https://code.cubewise.com/blog/mastering-hierarchies-in-ibm-tm1-and-planning-analytics