TM1 Forum

I have built TI processes using ODBC datasources with one date parameter. However, now I need to build a TI process that increments by 1 day from the Current Period. I figure I can use a loop but I do not know how to build the SQL statement in the advanced tab with the loop.

Any assistance is greatly appreciated.

Below is the SQL statement.

Thanks,

SELECT distinct hrp.person_id,hrp.emp_id,hrp.last_name,hrp.first_name,hrp.middle_name,hrp.person_type,hrp.system_type,
hrp.gender,hrp.latest_start_date, hrp.rehire_date,hrp.term_date, substr(hra.department,1,3)Department,substr(hra.job_code_title,1,3) Job_Title,hra.grade,hra.location
FROM apps.hr_emp_dump_v hrp,
apps.hr_emp_assignment_dump_v hra
WHERE 1 = 1
AND hra.person_id = hrp.person_id
AND '?Current Period?' BETWEEN hra.from_date
AND hra.to_date
AND '?Current Period?' BETWEEN hrp.from_date
AND hrp.to_date
AND(('?Current Period?' BETWEEN hra.sal_from_date
AND hra.sal_to_date )
OR ( hra.sal_from_date IS NULL OR hra.sal_to_date IS NULL AND hrp.SYSTEM_TYPE like '%Contingent%'));

In the TI Process, you can use TODAY() function to get the current date and can then pass this as a variable to the SQL.

I am able to pass a date parameter in the query. Where I am having difficulty is how to automate incrementing by 1 day until an end period. To give a better picture, I am building a headcount cube that will be populated on a daily basis by current period data for the current date from the HRMS system. This cube will be a snapshot of the employees in the HRMS as of the TI run. Since we allow retroactive changes in our shop, I have another cube that will be repopulated by another TI from a start date (assume Dec 31 xxxx) to the current date on a daily basis to capture the changes that were made retroactively. So, cube 1 will be a historical cube and cube 2 will be a restated/current cube (what would prior periods look like if we made retroactive changes). The challenge I have is how to increment the SQL by 1 day in one run to populate cube 2.

Thank you.

jorelb wrote:I am able to pass a date parameter in the query. Where I am having difficulty is how to automate incrementing by 1 day until an end period. To give a better picture, I am building a headcount cube that will be populated on a daily basis by current period data for the current date from the HRMS system. This cube will be a snapshot of the employees in the HRMS as of the TI run. Since we allow retroactive changes in our shop, I have another cube that will be repopulated by another TI from a start date (assume Dec 31 xxxx) to the current date on a daily basis to capture the changes that were made retroactively. So, cube 1 will be a historical cube and cube 2 will be a restated/current cube (what would prior periods look like if we made retroactive changes). The challenge I have is how to increment the SQL by 1 day in one run to populate cube 2.

One way:
1/ Define a process to populate Cube 2. This process should accept a string parameter representing the date.
2/ Define a second process which receives the starting date, and uses the various TM1 date and time functions to calculate each day in the format that you want it in. You'll be doing this by incrementing the TM1 serial date value for each day. See my post Using Dates And Times In TM1 if you aren't familiar with the TM1 serial date system. The calculation of the date string will of course be inside a While loop; whether you want to iterate through that loop for a fixed number of days, or whether you want to calculate how many days to iterate through using start and end dates, is up to you. In the latter case, you'd again be using a serial date to determine the start and end values. All of this code will go in the second process' Prolog.
3/ For each calculated date string, call the first process using the ExecuteProcess TurboIntegrator function.

Allan,

At first, I was having a hard time conceptualizing the approach but once I’ve thought about it – it does make sense. Please correct me if I misunderstood your recommended approach.

1. Create Process 1 (P1) that will populate Cube 2 with the date string parameter.
2. Create Process 2 (P2) that:
a. Retrieves the Starting Date from the Control Cube
b. Write a Loop statement that increments the date up to the End Date
c. Within the loop call P1

It’s brilliant, I think it is going to work.

Thank you.

jorelb wrote:Allan,

At first, I was having a hard time conceptualizing the approach but once I’ve thought about it – it does make sense. Please correct me if I misunderstood your recommended approach.

1. Create Process 1 (P1) that will populate Cube 2 with the date string parameter.
2. Create Process 2 (P2) that:
a. Retrieves the Starting Date from the Control Cube
b. Write a Loop statement that increments the date up to the End Date
c. Within the loop call P1

It’s brilliant, I think it is going to work.

Thank you.

If you change my name to the correct spelling, you'll have it exactly.

Yes, that's exactly what I meant. Didn't really matter whether the starting date came from the control cube or via a user input to its own parameter, (or any other source), but as long as you have it coming from somewhere that, plus the loop, plus the ExecuteProcess function to call P1 should be all you need.

Hi Alan,

I apologize for misspelling your name. Want to let you know the process works, thank you!