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

[mayankthu3@gmail.com]

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.

[Alan Kirk]

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.

[HighKeys]

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]

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

[PavoGa]

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.