Track lastupdate in TM1 cube

[ima_canuk]

I have an Oracle process that loads daily raw data to a cube. I have a user that wants to capture additional updates on specific days and track the systemdate associated with the loads.

To do this, I have a dimension entitled 'Iteration' in my cube. the daily load posts to v0, on demand loads will then post to v1, v2, v3 etc depending on how many runs there are.

I want to be able to do the following in TM1 and am looking for suggestions on best practice methodolody:

1) Is there a way to have the TI process post to the next available interation?
- If iteration v1 is zero then post here else if Iteration v2 is zero then post here else if iteration v3 is zero post here

2) Have a control cube that tracks iteration and lastupdate

Cube Iteration Lastupdate
Daily Load v1 11-5-2013
Daily Load v2 11-6-2013
Daily Load v3 NULL

[Alan Kirk]

I have an Oracle process that loads daily raw data to a cube. I have a user that wants to capture additional updates on specific days and track the systemdate associated with the loads.

To do this, I have a dimension entitled 'Iteration' in my cube. the daily load posts to v0, on demand loads will then post to v1, v2, v3 etc depending on how many runs there are.

I want to be able to do the following in TM1 and am looking for suggestions on best practice

{Teeth grind, teeth grind...}

methodolody:

There really isn't one single method that's "right"; there are a bunch of ways that you could do it. One of which you appear to have hit on below, or pretty close to it.

1) Is there a way to have the TI process post to the next available interation?
- If iteration v1 is zero then post here else if Iteration v2 is zero then post here else if iteration v3 is zero post here

2) Have a control cube that tracks iteration and lastupdate

Cube Iteration Lastupdate
Daily Load v1 11-5-2013
Daily Load v2 11-6-2013
Daily Load v3 NULL

You could tweak the above just a little bit.

If you're only interested in tracking one load, then you don't need a Cube dimension. Although it wouldn't be a bad idea to keep it in there or, better still, a "Source" dimension in case you ever want to track multiple loads to the one cube, or single loads to multiple cubes. The source name can be whatever you want to call it; just something that identifies this upload process.

The Iteration dimension would be as you have it above.

You could do with a Dates dimension, the elements of which are individual days.

Finally, you have a Measures dimension with LastUpdate as a string element.

Thus you have a 4 dimension cube:
Source
Iteration
Date
Measures

During the upload all you need do is the following in your Prolog:
- Obtain the current date (see here for details);
- Create a While loop in your Prolog;
- Use that to loop through the elements of the Iteration dimension. This involves using DimNm to get each element in turn.
- Doing a CellGetS within the loop for ControlCube(YourSourceName, The current Iteration element, the current date that you obtained above, LastUpdate)
- If that value is an empty string, then that's the Iteration that you write to. Assign that element name to a variable that you can use on your Data tab when you write the values, and do a CellPutS to write something in there (like a timestamp) so that the next time you run the process, your loop will skip over that Iteration element (since the control cube cell value won't be an empty string) and use the next one.

[BariAbdul]

Hi ima_canuk,I think below post is relevant:
http://www.tm1forum.com/viewtopic.php?f=3&t=6786 Thanks

[ima_canuk]

Appreciate the assistance. I have taken your advice but I am a bit stuck. I am getting a syntax error in the Prolog so I am not sure if my logic is sound.

The control cube:
Cube Name = Load Control Cube
Dimensions: Cube Names(Sales Planning Forecast), Iteration(v0,v1,v2,,,v5), m_LoadParameters (Source, LastUpdate)

Prolog:

Code: Select all

#Create loop for Iteration
vDim = 'Iteration';
vCube = 'Load Control Cube';
vCubeName = 'Sales Planning Forecast';
vMax = DIMSIZ('Iteration');
vStep=1;
WHILE (vStep <=vMax);

#Convert step into Iteration Dimension Element
vEL = DIMNM ('Iteration',vStep);

# Evaluate if the iteration has been used in the Load Control Cube
IF (CellGetS (vCube,vCubeName,vEL,'LastUpdate') = 0);
     vIterationDerived=vEL;
ENDIF;
vStep = vStep+1;
END;

The error is the following:
Error During Process Compilation.
Procedure: Prolog,
Line Number 22 ( which is the IF (CellGetS...) line)
Error: Syntax error on or before: CellGetS(vCube,VCubeName... logical expression

I have deleted all the lines and added them back and the culprit seems to be the IF stmt with the CellGetS Stmt. Any ideas on what I am doing wrong?

Also, I was going to replace the iteration in the CellPutN portion of the data tab with the variable vIterationDerived. Have I got that part correct at least?

Your help is greatly appreciated.

[Alan Kirk]

Appreciate the assistance. I have taken your advice but I am a bit stuck. I am getting a syntax error in the Prolog so I am not sure if my logic is sound.

...
Prolog:

Code: Select all

# Evaluate if the iteration has been used in the Load Control Cube
IF (CellGetS (vCube,vCubeName,vEL,'LastUpdate') = 0);

The error is the following:
Error During Process Compilation.
Procedure: Prolog,
Line Number 22 ( which is the IF (CellGetS...) line)
Error: Syntax error on or before: CellGetS(vCube,VCubeName... logical expression

I have deleted all the lines and added them back and the culprit seems to be the IF stmt with the CellGetS Stmt. Any ideas on what I am doing wrong?

When you post code it's best to wrap it in a Code tag (using the button at the top of the posting window) so that it keeps all of the indentations and so on. I've modified your post accordingly.

The problem with your syntax is this; CellGetS() returns a string. You are comparing it to a numeric value. Also, when you compare a string you need to use the @= operator, not the = one. If the string value 0 is stored in the cell you would need to make the comparison as follows:

Code: Select all

IF (CellGetS (vCube,vCubeName,vEL,'LastUpdate') @= '0');

However string elements do not have a default value of 0 so what you probably really want is:

Code: Select all

IF (CellGetS (vCube,vCubeName,vEL,'LastUpdate') @= '');

That is, confirming that the cell has an empty string.

Also, I was going to replace the iteration in the CellPutN portion of the data tab with the variable vIterationDerived. Have I got that part correct at least?

Not sure, as I'm not at all sure what you're trying to say there.

[ima_canuk]

The changes you suggested work perfectly for the syntax, thanks.

Manual Update Example.docx

Cube Update Example

(82.26 KiB) Downloaded 365 times

I have attached a word doc with the Prolog and Data codes which compile properly.

The only issue now is that the code is not writing to the correct iteration. I have an example noted in the word doc.

Really appreciate the help, good learning experience.

[Alan Kirk]

The changes you suggested work perfectly for the syntax, thanks.

Manual Update Example.docx

I have attached a word doc with the Prolog and Data codes which compile properly.

The only issue now is that the code is not writing to the correct iteration. I have an example noted in the word doc.

Really appreciate the help, good learning experience.

For the benefit of those who haven't downloaded the document, the code that you have is:

Code: Select all

vDim='Iteration';
vStep=1;
vMax=DimSiz(vDim)

# Snip... take as read that the other variables are populated correctly.

While(vStep<=vMax);

    If(CellGetS(vCube,vCubeName,DIMNM(vDim,vStep),'LastUpdate' @='');
        vIteration=DimNm(vDim,vStep);
    EndIf;

    vStep=vStep+1;

End;

The control cube has an entry in element v0, but not v1, v2, etc up to v5. It's not clear whether the dimension only runs up to v5 but I suspect not.

Here's your problem. This:

Code: Select all

If(CellGetS(vCube,vCubeName,DIMNM(vDim,vStep),'LastUpdate' @='');

will evaluate to True on v1. But it will also evaluate to True on v2, v3, v4, etc. I suspect that if you check the last element in your Iteration dimension, that's where you'll find the data.

Solution?

Put an extra line in the If() block setting the value of vStep to vMax. In that way the loop will be exited and vIteration will be the element that represents the first blank cell instead of the last blank cell for the dimension.

(By the way, I'd like to add that these kinds of questions are a joy to answer. You've tried to find the answer for yourself first, you've thought the matter through as far as your experience allows, and you've provided all of the relevant details. This thread stands in contrast to quite a few that we've had of late.)

[ima_canuk]

Thanks for your help, again much appreciated . Could you clarify what you meant by add an extra line in the block setting? Do you mean add another conditional statement? If so, what would I be evaluating?

[Alan Kirk]

Thanks for your help, again much appreciated . Could you clarify what you meant by add an extra line in the block setting?

I didn't say "in the block setting", I said "in the if block {verb goes here =>} setting the value of vStep to vMax".

Do you mean add another conditional statement? If so, what would I be evaluating?

What I meant is this:

Code: Select all

While(vStep<=vMax);

    If(CellGetS(vCube,vCubeName,DIMNM(vDim,vStep),'LastUpdate' @='');
        vIteration=DimNm(vDim,vStep);

        # The next line will break you out of the loop as soon as it encounters 
        # an Iteration element that has no value. It does this because you are
        # setting the value of vStep to the same value as vMax. When you exit
        # the If block vStep will again be increased by 1, taking it OVER the 
        # value of vMax. Consequently the next time the loop 
        # passes to the While(vStep<=vMax) line, vStep will no longer 
        # be <= vMax, and will just continue with the code that 
        # appears AFTER the While loop..
        # In this way the variable vIteration will keep the name
        # of the element where it **first** found the blank.
        # If you don't have the following line the loop will just keep going.
        # In that case *every* time the loop finds a blank, it'll update 
        #  the value of vIteration. You want it to only happen the *first* time,
        # so you need to have some way of exiting the loop
        # once that happens. The following line is that way.
        vStep = vMax;
    EndIf;

    vStep=vStep+1;

End;

[ima_canuk]

Understand now, thanks. It works perfectly!! Most helpful...