Tm1py: How to load to cube and increment

Created on 21 Jul 2021 · 18Comments · Source: cubewise-code/tm1py

Describe what did you try to do with TM1py
I want to load the contents of a small text file (100 lines) into a TM1 cube.
The cellset works and can be loaded in the cube.
However, duplicate lines will overwrite, instead of increment.

I also tried by reading the CSV file in a dataframe and do a Group by, but I did not succeed. The group by needs to add up the hours (last column) over multiple other columns (basically, the 3 other columns).
So I gave up on the group by and focused on the cellset with increment = True in "tm1.cubes.cells.write"

The error I get is: TypeError: unsupported operand type(s) for +: 'float' and 'NoneType'
Somehow the incrementing part is causing in issue in my code.

Input file contents to load:
07-01 Project A Consultancy 8
07-02 Project B Consultancy 9

Code:

from TM1py.Services import TM1Service
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset
import pandas as pd
from TM1py.Utils import Utils

def load_activity():

    # 1. zeroout of existing data

    # this is okay

    # 2. loading of new data

    ADDRESS = '**.***.**.***'
    USER = '*****'
    PWD = '*****'
    PORT = 8001
    SSL = True

    INPUT_FILE = r'D:\Inputfile.csv'

    tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)

    # Build cellset from file
    cellset = {}

    with open(INPUT_FILE, "r") as file:
        for line in file:
            entries = line.split("\t")
            coordinates = ("Actual", entries[2], entries[1], "WGI", "2021", entries[0], "Hours")
            value = float(entries[3])
            cellset[coordinates] = value

    # tm1.cubes.cells.write_values(cube, cellset)

    # print(cellset)

    tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)

if __name__ == "__main__":
    load_activity()

Thanks a lot !

question

Source

wimgielis

All 18 comments

Hi,

Please try to change

value = float(entries[3])

value = float(entries[3] or 0)

Im guessing that you read None from the source file and TM1py is trying to add None to a float value.
The or operation takes 0 if the value is "implicitly false" (in python None, 0, empty lists and empty strings are implicitly False)

MariusWirtz-cubewise on 21 Jul 2021

Thank you Marius but I cannot get the Increment=True to work. Here's my
code and the input file as well, should you have the time to look at it.
In the code I have 6 parts where I add a comment/question/doubt, if
possible any insight on that would be greatly appreciated. They are Wim_01,
Wim_02, ..., Wim_06.

The code first does a zero out, then loading incrementally lines from a
text file. Text file looks like this:

day / project / activity / number of hours

07-01 Project_A Activity_1 2
07-01 Project_B Activity_2 3.5
07-01 Project_A Activity_2 1
07-01 Project_A Activity_1 0.5

This input file has CRLF between lines and TAB between fields. Dot is
the decimal separator.

Ultimately I would require 2.5 hours on July 1, Project_A, Activity_1
(adding up lines 1 and 4).
The cellset that is created from the file, is fine though.

Error received:

Exception has occurred: TypeError
unsupported operand type(s) for +: 'float' and 'NoneType'
File "C:\Users\WGielis\Python testing\test1.py", line 94, in
load_to_BOM_activity
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset,
increment=True)
File "C:\Users\WGielis\Python testing\test1.py", line 97, in
load_to_BOM_activity()

from TM1py.Services import TM1Service
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset
import pandas as pd
from TM1py.Utils import Utils

def load_to_BOM_activity():

# 1. zeroout of existing data

#

# START of parameters and settings

#

# TM1 connection settings
ADDRESS = '**.***.**.***'
USER = '*****'
PWD = '*****'
PORT = 8001
SSL = True

cube = "Activity"

#

# END of parameters and settings

#

tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD,

namespace='', gateway='', ssl=SSL)

content = tm1.cubes.cells.execute_view('z_ADMIN_PARAM', 'Month for

input', private=False)

# Wim_01: I find the below rather ugly:
# mth = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current month for

input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
# yr = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current year for
input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']

# I like this approach better:
df = Utils.build_pandas_dataframe_from_cellset(content)
mth = df.iat[0,0]
yr = df.iat[1,0]

# Wim_02: I don't really like the double {{ }} but I think it's needed

when I want to insert variables using {0}, {1}, ...
mdx = """
SELECT
NON EMPTY {{[Periode].[{0}].Children}} * {{TM1FilterByLevel(
[Activity].Members, 0 )}} * {{TM1FilterByLevel( Descendants(
[Client].[TOT_CLIENT]), 0)}} ON ROWS,
NON EMPTY {{[Indic].[Hours]}} ON COLUMNS
FROM {1}
WHERE ([Vision].[Actual], [Ressource].[WGI], [Exercice].[{2}])
""".format(mth, cube, yr)

# Wim_03: it's needed that we use an admin account for the zeroout ?
# I could also create a dataframe, change the values to 0 and write

back to the cube.
# Then we would not need an admin account ?
tm1.cells.clear_with_mdx(cube=cube, mdx=mdx)

# 2. loading of new data

#

# START of parameters and settings

#

# New TM1 connection settings ==> admin user not used anymore
USER = '***'
PWD = '***'

# Wim_04: sometimes we need backslash, sometimes forward slash, is that

correct ?
INPUT_FILE = r'D:_AR\data.csv'
INPUT_FILE_2 = r'D:/_AR/data.csv'

#

# END of parameters and settings

#

tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD,

namespace='', gateway='', ssl=SSL)

# Build cellset from file
cellset = {}

# Wim_05: I removed the header line (1 line) from the file but actually

there is one
# so ideally I would like to know how to deal with a 1-line header
with open(INPUT_FILE, "r") as file:
# Read coordinates and values from each line
for line in file:
# first_line = file.readline()
entries = line.split("\t")
coordinates = ("Actual", entries[2], entries[1], "WGI",
str(yr), entries[0], "Hours")
# print(entries[3])
value = float(entries[3] or 0)
cellset[coordinates] = value

# tm1.cubes.cells.write_values(cube, cellset)

# print(cellset)

tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset,

increment=True)

# Wim_06: I was playing around with a dataframe as well, to group by
# but I could not make it work
# input file has CRLF between lines and tab between fields
# # df = pd.read_csv(INPUT_FILE_2, sep="\r\n", header=0)
# df = pd.read_csv(INPUT_FILE_2, sep='\t', lineterminator='\n',

header=0)
# # df = df.groupby("Periode", "Client", "Activity").sum("Hours")

if __name__ == "__main__":
load_to_BOM_activity()

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

Op wo 21 jul. 2021 om 02:36 schreef Marius Wirtz @.*>:

Hi,

Please try to change

value = float(entries[3])

To

value = float(entries[3] or 0)

Im guessing that you read None from the source file and TM1py is trying to
add None to a float value.
The or operation takes 0 if the value is "implicitly false" (in python
None, 0, empty lists and empty strings are implicitly False)

Get Outlook for Androidhttps://aka.ms/AAb9ysg

From: Wim Gielis @.>
Sent: Wednesday, July 21, 2021 2:17:43 AM
To: cubewise-code/tm1py *@.>
Cc: Subscribed @.**>
Subject: [cubewise-code/tm1py] How to load to cube and increment (#574)

Describe what did you try to do with TM1py
I want to load the contents of a small text file (100 lines) into a TM1
cube.
The cellset works and can be loaded in the cube.
However, duplicate lines will overwrite, instead of increment.

I also tried by reading the CSV file in a dataframe and do a Group by, but
I did not succeed. The group by needs to add up the hours (last column)
over multiple other columns (basically, the 3 other columns).
So I gave up on the group by and focused on the cellset with increment =
True in "tm1.cubes.cells.write"

The error I get is: TypeError: unsupported operand type(s) for +: 'float'
and 'NoneType'
Somehow the incrementing part is causing in issue in my code.

Input file contents to load:
07-01 Project A Consultancy 8
07-02 Project B Consultancy 9

Code:

from TM1py.Services import TM1Service
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset
import pandas as pd
from TM1py.Utils import Utils

def load_activity():

1. zeroout of existing data

this is okay

2. loading of new data

ADDRESS = '...'
USER = ''
PWD = ''
PORT = 8001
SSL = True

INPUT_FILE = r'D:\Inputfile.csv'

tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD,
namespace='', gateway='', ssl=SSL)

Build cellset from file

cellset = {}

with open(INPUT_FILE, "r") as file:
for line in file:
entries = line.split("\t")
coordinates = ("Actual", entries[2], entries[1], "WGI", "2021",
entries[0], "Hours")
value = float(entries[3])
cellset[coordinates] = value

tm1.cubes.cells.write_values(cube, cellset)

print(cellset)

tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset,
increment=True)

if __name__ == "__main__":
load_activity()

Thanks a lot !

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub<
https://github.com/cubewise-code/tm1py/issues/574>, or unsubscribe<
https://github.com/notifications/unsubscribe-auth/AHLDPJCNKRDTLPXK3VCOVRTTYYG2PANCNFSM5AW4EXTQ

.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/574#issuecomment-883797168,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULOJTKQLJLRGZM6WDXDTYYJCLANCNFSM5AW4EXTQ
.

wimgielis on 21 Jul 2021

@wimgielis The increment functionality doesn't work the same way as it is in TM1.

The write method creates a cellset (dictionary) with the data that you have in your file and then build an MDX using this cellset. The MDX gets queried and then the values that got obtained will be added with the values from your new cellset.
We don't perform a cellwise increment here, its merely an addition of two table like structure. If you have duplicate records in your source file, the latest value in the file will be added with the MDX obtained result and loaded into the cube.

Example:

Your source file:
a,b,c,9
a,b,c,4

Your cube:
a,b,c,10

After the calling the write method with increment set as True, your cube would look like this:
a,b,c,14

rkvinoth on 21 Jul 2021

Okay thanks for the information.
How should I proceed to mimick CellIncrementN rather than have CellPutN,
for the duplicate lines in my file ?

All the best,

Wim

wimgielis on 21 Jul 2021

@MariusWirtz I couldn't spend time to understand the usage of None here. But I think this is the one causing the issue for Wim.
https://github.com/cubewise-code/tm1py/blob/57ff1d9e42b956f6eacd7bae00843ed2124ba383/TM1py/Services/CellService.py#L705

values = [5, 3.34, 4]
current_values = [3.8, 0, 5]

values = (x + (y or None) for x, y in zip(values, current_values))
print(list(values))

TypeError: unsupported operand type(s) for +: 'float' and 'NoneType'

rkvinoth on 21 Jul 2021

@rkvinoth thanks for looking into this!

To me, this looks like a bug. it would be more logical if it said :

values = (x + (y or 0) for x, y in zip(values, current_values))

or even better:

values = ((x or 0) + (y or 0) for x, y in zip(values, current_values))

@wimgielis
Could you please change this line in your local TM1py CellService file in the lib/site-packages/tm1py/Services/CellService.py file and let us know if this fixes the issue?

MariusWirtz on 21 Jul 2021

@wimgielis
happy to check out the code but it's almost impossible to re-use when it comes from an email reply.
Can you post it again in this thread and mark it as code with markdown (triple backticks at the top and triple backticks at the bottom). Then I can copy and paste it into PyCharm.

MariusWirtz on 21 Jul 2021

@wimgielis
happy to check out the code but it's almost impossible to re-use when it comes from an email reply.
Can you post it again in this thread and mark it as code with markdown (triple backticks at the top and triple backticks at the bottom). Then I can copy and paste it into PyCharm.

Sure, I will do this. For now, I see that it is not working for me. I can see in the stack trace that my new code was used.

wimgielis on 21 Jul 2021

@wimgielis

I would suggest that you perform a groupby for all the columns to consolidate the duplicate combinations.

df = df.groupby(by=["Periode", "Client", "Activity"]).sum("Hours").reset_index()

Then use the increment argument to load it into the cube.

If things don't workout please attach the source file here.

rkvinoth on 21 Jul 2021

Here's my code and the input file as well, should you have the time to look at it.
In the code I have 6 parts where I add a comment/question/doubt, if possible any insight on that would be greatly appreciated. They are Wim_01, Wim_02, ..., Wim_06.

The code first does a zero out, then loading incrementally lines from a text file. Text file looks like this:

day / project / activity / number of hours

07-01 Project_A Activity_1 2
07-01 Project_B Activity_2 3.5
07-01 Project_A Activity_2 1
07-01 Project_A Activity_1 0.5

This input file has CRLF between lines and TAB between fields. Dot is the decimal separator.

Ultimately I would require 2.5 hours on July 1, Project_A, Activity_1 (adding up lines 1 and 4).
The cellset that is created from the file, is fine though.

Error received:

Exception has occurred: TypeError
unsupported operand type(s) for +: 'float' and 'NoneType'
File "C:\Users\WGielis\Python testing\test1.py", line 94, in load_to_BOM_activity
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)
File "C:\Users\WGielis\Python testing\test1.py", line 97, in
load_to_BOM_activity()

from TM1py.Services import TM1Service
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset
import pandas as pd
from TM1py.Utils import Utils

def load_to_BOM_activity():

    # 1. zeroout of existing data

    # =============================================================================================================
    # START of parameters and settings
    # =============================================================================================================

    # TM1 connection settings
    ADDRESS = '**.***.**.***'
    USER = '*****'
    PWD = '*****'
    PORT = 8001
    SSL = True

    cube = "Activity"

    # =============================================================================================================
    # END of parameters and settings
    # =============================================================================================================

    tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)

    content = tm1.cubes.cells.execute_view('z_ADMIN_PARAM', 'Month for input', private=False)

    # Wim_01: I find the below rather ugly:
    # mth = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current month for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
    # yr = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current year for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']

    # I like this approach better:
    df = Utils.build_pandas_dataframe_from_cellset(content)
    mth = df.iat[0,0]
    yr = df.iat[1,0]

    # Wim_02: I don't really like the double {{ }} but I think it's needed when I want to insert variables using {0}, {1}, ...
    mdx = """
    SELECT
    NON EMPTY {{[Periode].[{0}].Children}} * {{TM1FilterByLevel( [Activity].Members, 0 )}} * {{TM1FilterByLevel( Descendants( [Client].[TOT_CLIENT]), 0)}} ON ROWS,
    NON EMPTY {{[Indic].[Hours]}} ON COLUMNS
    FROM {1}
    WHERE ([Vision].[Actual], [Ressource].[WGI], [Exercice].[{2}])
    """.format(mth, cube, yr)

    # Wim_03: it's needed that we use an admin account for the zeroout ?
    # I could also create a dataframe, change the values to 0 and write back to the cube.
    # Then we would not need an admin account ?
    tm1.cells.clear_with_mdx(cube=cube, mdx=mdx)


    # 2. loading of new data

    # =============================================================================================================
    # START of parameters and settings
    # =============================================================================================================

    # New TM1 connection settings ==> admin user not used anymore
    USER = '***'
    PWD = '***'

    # Wim_04: sometimes we need backslash, sometimes forward slash, is that correct ?
    INPUT_FILE = r'D:\_AR\data.csv'
    INPUT_FILE_2 = r'D:/_AR/data.csv'

    # =============================================================================================================
    # END of parameters and settings
    # =============================================================================================================

    tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)

    # Build cellset from file
    cellset = {}

    # Wim_05: I removed the header line (1 line) from the file but actually there is one
    # so ideally I would like to know how to deal with a 1-line header
    with open(INPUT_FILE, "r") as file:
        # Read coordinates and values from each line
        for line in file:
            # first_line = file.readline()
            entries = line.split("\t")
            coordinates = ("Actual", entries[2], entries[1], "WGI", str(yr), entries[0], "Hours")
            # print(entries[3])
            value = float(entries[3] or 0)
            cellset[coordinates] = value

    # tm1.cubes.cells.write_values(cube, cellset)

    # print(cellset)

    tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)

    # Wim_06: I was playing around with a dataframe as well, to group by
    # but I could not make it work
    # input file has CRLF between lines and tab between fields
    # # df = pd.read_csv(INPUT_FILE_2, sep="\r\n", header=0)
    # df = pd.read_csv(INPUT_FILE_2, sep='\t', lineterminator='\n', header=0)
    # # df = df.groupby("Periode", "Client", "Activity").sum("Hours")


if __name__ == "__main__":
    load_to_BOM_activity()

wimgielis on 21 Jul 2021

👍1

If you are performing a zero out of the cube, then a simple groupby and calling the write method should work for you.

rkvinoth on 21 Jul 2021

That's what I tried to do but failed. Please refer to a couple of lines in
the code I supplied above in code tags.
The file has a header (1 line) but also without I stumbled upon errors when
doing even the simplest things. I am still a beginner with Python :-)

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

Op wo 21 jul. 2021 om 14:19 schreef Vinoth Kumar Ravi <
@.*>:

If you are performing a zero out of the cube, then a simple groupby and
calling the write method should work for you.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/574#issuecomment-884145609,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULNN5XRUD5SH7DZXJQDTY23M3ANCNFSM5AW4EXTQ
.

wimgielis on 21 Jul 2021

Giving you an example.

Copy the below table:
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

href="file:////Users/rkvinoth/Library/Group%20Containers/UBF8T346G9.Office/TemporaryItems/msohtmlclip/clip.htm">
href="file:////Users/rkvinoth/Library/Group%20Containers/UBF8T346G9.Office/TemporaryItems/msohtmlclip/clip_filelist.xml">

a | b | c | h
-- | -- | -- | --
ab | df | we | 2
ab | df | we | 5
ab | er | qw | 2

Run the below code:

import pandas as pd

df = pd.read_clipboard()
df = df.groupby(by=['a', 'b', 'c']).sum('h').reset_index()
print(df)

Output:
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

a | b | c | h
-- | -- | -- | --
ab | df | we | 7
ab | er | qw | 2

Now load this into the cube using TM1py.

ps: Don't worry everyone here is a beginner and we love stackoverflow

rkvinoth on 21 Jul 2021

❤1

Thanks. It's the part on loading the text file that was posing a problem
but I have that cleared up now. I will post later my final code. Thanks !

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

Op wo 21 jul. 2021 om 14:50 schreef Vinoth Kumar Ravi <
@.*>:

Giving you an example.

Copy the below table:
a b c h
ab df we 2
ab df we 5
ab er qw 2

Run the below code:

import pandas as pd
df = pd.read_clipboard()df = df.groupby(by=['a', 'b', 'c']).sum('h').reset_index()print(df)

Output:
a b c h
ab df we 7
ab er qw 2

Now load this into the cube using TM1py.

ps: Don't worry everyone here is a beginner and we love stackoverflow

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/574#issuecomment-884163754,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULP42INVY5QBJ7SQJXLTY27AZANCNFSM5AW4EXTQ
.

wimgielis on 21 Jul 2021

I have it all working now: zero out of the data for 1 month, then reload data again.
The csv file has a header record, and tab delimited fields.

5 remarks / questions / remain, indicated in the code below.

from TM1py.Services import TM1Service
import pandas as pd
from TM1py.Utils import Utils

def load_to_BOM_activity():

    # 1. zeroout of existing data

    # =============================================================================================================
    # START of parameters and settings
    # =============================================================================================================

    # TM1 connection settings (ISM = 1)
    ADDRESS = 'bla bla bla'
    USER = '11111'
    PWD = '22222'
    PORT = 8001
    SSL = True

    cube = "Activity"

    # =============================================================================================================
    # END of parameters and settings
    # =============================================================================================================

    tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)

    content = tm1.cubes.cells.execute_view('z_ADMIN_PARAM', 'Month for input', private=False)

    # Wim_01: I find the below rather ugly:
    # I grab the current year and month from a parameter admin cube, just 2 string text cells
    # mth = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current month for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
    # yr = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current year for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']

    # I like this approach better - any advice ?
    df = Utils.build_pandas_dataframe_from_cellset(content)
    mth = df.iat[0,0]
    yr = df.iat[1,0]

    # Wim_02: I don't really like the double {{ }} but I think it's needed when I want to insert variables using {0}, {1}, ...
    mdx = """
    SELECT 
    NON EMPTY {{[Periode].[{0}].Children}} * {{TM1FilterByLevel( [Activity].Members, 0 )}} * {{TM1FilterByLevel( Descendants( [Client].[TOT_CLIENT]), 0)}} ON ROWS,
    NON EMPTY {{[Indic].[Fact_Exec], [Indic].[TransportYN]}} ON COLUMNS
    FROM {1}
    WHERE ([Vision].[2018-S53], [Ressource].[WGI], [Exercice].[{2}])
    """.format(mth, cube, yr)

    # Wim_03: it's needed that we use an admin account for the zeroout ?
    # I could also create a dataframe, change the values to 0 and write back to the cube.
    # Then we would not need an admin account ?
    tm1.cells.clear_with_mdx(cube=cube, mdx=mdx)


    # 2. loading of new data

    # =============================================================================================================
    # START of parameters and settings
    # =============================================================================================================

    # TM1 connection settings ==> admin user not used anymore (ISM = 1)
    USER = '77777'
    PWD = '8888'

    # Wim_04: sometimes we need backslash, sometimes forward slash, is that correct ?
    # I also had code using "with open(INPUT_FILE, "r") as file:" and there I needed backslashes 
    # INPUT_FILE = r'D:\_AR\Input_BOM_AR.csv'

    # so now forward slashes to read data into the dataframe
    INPUT_FILE = r'D:/_AR/Input_BOM_AR.csv'

    # =============================================================================================================
    # END of parameters and settings
    # =============================================================================================================

    tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)

    # Wim_05: can the below easier or more straightforward or shorter ? For instance, the header names that are twice in the code
    # and latin1 was needed for diacritics like é en ô
    df = pd.read_csv(INPUT_FILE, sep='\t', lineterminator='\r', header=0, encoding='latin1')

    # manipulating the dataframe
    df['Vision'] = 'Current'
    df['Ressource'] = 'WGI'
    df['Exercice'] = yr
    df['Msr'] = 'Fact_Exec'

    # check the total hours:
    # print(df.sum(axis=0))

    df = df[["Vision", "Activity", "Client", "Ressource", "Exercice", "Periode", "Msr", "Hours"]]
    ### alternatively:
    # column_names = ["Vision", "Activity", "Client", "Ressource", "Exercice", "Periode", "Msr", "Hours"]
    # df = df.reindex(columns=column_names)

    # can I say in the groupby: "take all columns" without specifying them ? The columns with constants shouldn't play a role, no ?
    # but these 4 extra columns cannot be dropped from the df or writing to TM1 will fail
    df = df.groupby(by=['Vision', 'Activity', 'Client', 'Ressource', 'Exercice', 'Periode', 'Msr']).sum('Hours').reset_index()

    tm1.cubes.cells.write_dataframe(cube_name=cube, data=df)

if __name__ == "__main__":
    load_to_BOM_activity()

wimgielis on 21 Jul 2021

🚀1

Apologies, the formatting with 3 asterisks at the top and bottom won't work I'm afraid.

wimgielis on 21 Jul 2021

Hi @wimgielis,

Sorry for the late reply. It's 3 backticks ( ` ), not 3 asterisks. I edited your reply to apply the formatting.

With regard to your comments in the code:

I find the below rather ugly: I like this approach better - any advice?

When just retrieving a couple of values from a parameter cube I like to use MDX queries with explicit tuples and then use the the execute_mdx_values function that only returns a list of cell values.
Since the ordering of the values is in line with your query you can make sense out of it. It should be efficient code and performance-wise.

I don't really like the double {{ }} but I think it's needed when I want to insert variables using {0}, {1}, ...

Yeah. I know. The {} formatting in python is not friendly when building MDX queries with string formatting.
A good solution is to use mdxpy IMO. Especially when building more complex MDX queries it can save you from writing lots of code and curly brackets and headaches in general :)
Here is a sample:

from TM1py import TM1Service
from mdxpy import MdxBuilder, MdxHierarchySet

with TM1Service(address='localhost', port=12354, ssl=True, user='admin', password='apple') as tm1:
    query = MdxBuilder.from_cube(cube="c1")
    query.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves(
        dimension="d1",
        hierarchy="d1"))
    query.add_hierarchy_set_to_column_axis(MdxHierarchySet.tm1_subset_to_set(
        dimension="d2",
        hierarchy="d2",
        subset="Default"))

    mdx = query.to_mdx()

    print(mdx)

is it needed that we use an admin account for the zero out?

the clear_with_mdx function uses the ViewZeroOut function through an unbound process. So yes it requires admin permissions.
You can clear out through the "normal" REST-based write functions in TM1py but it's much less efficient.

sometimes we need backslash, sometimes forward slash, is that correct?

Check out the pathlib module from the python standard library. It saves you writing either and makes your code work on Linux and Windows :)

can the below be easier or more straightforward or shorter ? For instance, the header names that are twice in the code and latin1 was needed for diacritics like é en ô

I don't think it can be done in a more easy way.

MariusWirtz on 26 Jul 2021

Thank you Marius, that's useful.

Wim

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

Op ma 26 jul. 2021 om 09:29 schreef Marius Wirtz @.*>:

Hi @wimgielis https://github.com/wimgielis,

Sorry for the late reply. It's 3 backticks ( ` ), not 3 asterisks. I
edited your reply to apply the formatting.

With regard to your comments in the code:

I find the below rather ugly: I like this approach better - any advice?

When just retrieving a couple of values from a parameter cube I like to
use MDX queries with explicit tuples and then use the the
execute_mdx_values function that only returns a list of cell values.
Since the ordering of the values is in line with your query you can make
sense out of it. It should be efficient code and performance-wise.

I don't really like the double {{ }} but I think it's needed when I want
to insert variables using {0}, {1}, ...

Yeah. I know. The {} formatting in python is not friendly when building
MDX queries with string formatting.
A good solution is to use mdxpy IMO. Especially when building more complex
MDX queries it can save you from writing lots of code and curly brackets
and headaches in general :)
Here is a sample:

from TM1py import TM1Service
from mdxpy import MdxBuilder, MdxHierarchySet

with TM1Service(address='localhost', port=12354, ssl=True, user='admin', password='apple') as tm1:
query = MdxBuilder.from_cube(cube="c1")

query.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves(

    dimension="d1",

    hierarchy="d1"))

query.add_hierarchy_set_to_column_axis(MdxHierarchySet.tm1_subset_to_set(

    dimension="d2",

    hierarchy="d2",

    subset="Default"))



mdx = query.to_mdx()



print(mdx)
is it needed that we use an admin account for the zero out?

the clear_with_mdx function uses the ViewZeroOut function through an
unbound process. So yes it requires admin permissions.
You can clear out through the "normal" REST-based write functions in TM1py
but it's much less efficient.

sometimes we need backslash, sometimes forward slash, is that correct?

Check out the pathlib module from the python standard library. It saves
you writing either and makes your code work on Linux and Windows :)

can the below be easier or more straightforward or shorter ? For instance,
the header names that are twice in the code and latin1 was needed for
diacritics like é en ô

I don't think it can be done in a more easy way.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/574#issuecomment-886450366,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULP5QIT7REFYAKS3GBTTZUFD5ANCNFSM5AW4EXTQ
.