Writing a Single Dynamic TI process to load data from multiple files

Post Reply
mayankthu3@gmail.com
Posts: 2
Joined: Wed Oct 24, 2018 9:13 am
OLAP Product: IBM Cognos TM1
Version: 10.2
Excel Version: Excel 2016

Writing a Single Dynamic TI process to load data from multiple files

Post by mayankthu3@gmail.com » Fri Feb 14, 2020 9:50 am

Hello TM1 Gurus,

I am a newbie in to world of TI & simple google search couldn't answer my query hence seeking your suggestions.

Suppose I have files for each month named "YYYYMM_ABCD" having exactly same columns(eg. AA, BB, CC, DD).
I need to write a single dynamic TI process which can read these files, runs for file of each month to load data.

If I am unclear in my queries, that could be because of lack of Knowledge in TI scripting.

Please suggest ways to achieve this.

User avatar
Alan Kirk
Site Admin
Posts: 6024
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Writing a Single Dynamic TI process to load data from multiple files

Post by Alan Kirk » Fri Feb 14, 2020 10:07 am

mayankthu3@gmail.com wrote:
Fri Feb 14, 2020 9:50 am
Hello TM1 Gurus,

I am a newbie in to world of TI & simple google search couldn't answer my query hence seeking your suggestions.

Suppose I have files for each month named "YYYYMM_ABCD" having exactly same columns(eg. AA, BB, CC, DD).
I need to write a single dynamic TI process which can read these files, runs for file of each month to load data.

If I am unclear in my queries, that could be because of lack of Knowledge in TI scripting.

Please suggest ways to achieve this.
Since you say that the files have "exactly the same columns" I assume that the only part that needs to be dynamic is the filename(s).

In which case:
(a) In the PROLOG tab, work out the date for the current upload using rules date and time functions. (This link is to 10.2.2, but for the most part the ones you need like Now() are still applicable.)
(b) Assign the name of that month's file to the DatasourceNameForServer variable.

The process will then upload the current month's file.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

HighKeys
Posts: 55
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: Writing a Single Dynamic TI process to load data from multiple files

Post by HighKeys » Fri Feb 14, 2020 1:06 pm

Alan Kirk wrote:
Fri Feb 14, 2020 10:07 am
mayankthu3@gmail.com wrote:
Fri Feb 14, 2020 9:50 am
Hello TM1 Gurus,

I am a newbie in to world of TI & simple google search couldn't answer my query hence seeking your suggestions.

Suppose I have files for each month named "YYYYMM_ABCD" having exactly same columns(eg. AA, BB, CC, DD).
I need to write a single dynamic TI process which can read these files, runs for file of each month to load data.

If I am unclear in my queries, that could be because of lack of Knowledge in TI scripting.

Please suggest ways to achieve this.
Since you say that the files have "exactly the same columns" I assume that the only part that needs to be dynamic is the filename(s).

In which case:
(a) In the PROLOG tab, work out the date for the current upload using rules date and time functions. (This link is to 10.2.2, but for the most part the ones you need like Now() are still applicable.)
(b) Assign the name of that month's file to the DatasourceNameForServer variable.

The process will then upload the current month's file.
Hi!

Just wanna hookup here, cause its very interessting for me.

As i can see in the IBM Docs "DatasourceNameForServer='Name';" will set the Datasource for the process (like when i choose it in the GUI when building the Process), which could allow me to import multiply files with the same structure by changing the DatasourceNameForServer Param.

Sounds very nice, what i'm woundering now is, if i set up the datasource via GUI and then change it in the code, does tm1 remember the structure that i set up in the GUI at the first sample file?


Thanks!

Wim Gielis
MVP
Posts: 2339
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Writing a Single Dynamic TI process to load data from multiple files

Post by Wim Gielis » Fri Feb 14, 2020 1:11 pm

If you set the data source in the Prolog tab, TM1 will overwrite at run-time the settings that you used at design time.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
PavoGa
Community Contributor
Posts: 409
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Writing a Single Dynamic TI process to load data from multiple files

Post by PavoGa » Fri Feb 14, 2020 1:43 pm

As Wim says, it overrides the DataSource tab settings, but the settings on the variables tab remain. For example, take the following SQL query on the DataSource tab:

Code: Select all

--for Oracle
SELECT
  '1' AS vTheNumberOne,
  '2' AS vMySecondVariable
FROM DUAL
If not overridden, these variable names are the ones set on the Variables tab and are used in the MetaData and Data procedures. So if the following is done in the Prolog:

Code: Select all

sSQL = 'SELECT 
    a.EMPL_ID AS vEmployeeID,
    a.NAME AS vEmployeeName
FROM EMPLOYEE a';

DataSourceQuery = sSQL;

The variables are still going to be vTheNumberOne (a.EMPL_ID) and vMySecondVariable (a.NAME) in MetaData and DATA.

I typically use the former in the DataSource tab just to set the number and names of the data source variables, then either put the actual query in the PROLOG or read it from a cube when a client's migration policies are somewhat overburdening.
Ty
Cleveland, TN

Post Reply