Data in tm1 process

[mmckimson]

This is a very stupid question, but one I'll ask anyway as I seem to have a discrepancy in data! When you run a process to update a cube with data, with the Cube Action "Update Cube" selected and "Store Values" selected for Data Action, what does TM1 do with target cells that are non-zero? Let me explain:

On a nightly basis, a process is run to copy data from one cube (Cube 1) to a "data" cube with identical dimensions (Cube 2) that is used in a forecast.
Based upon a date calculation, a rule exists for Cube 1 as follows (simplified for this discussion):

['Data'] = N: IF(DB('Product Forecast Flags','Forecast Flag') @= 'Closed', DB('Cube 2', 'Data'),STET);

As you can probably gather, the purpose of the rule is to either pull data from Cube 2 for closed periods or allow end users to enter data in a particular cell. Works fine. However, what doesn't seem to be working is the transfer of data from Cube 1 to Cube 2 as after the process is run, data will not always match up. It's almost as if what happens is that the process doesn't overwrite existing data in the target cube (Cube 2) with a zero when a zero exists in the source cube but not in the target. Or in other words:

LINE 1----------CUBE 1 DATA----------CUBE 2 DATA----------RESULT
1------------------------10----------------------5-------------------10
2-------------------------3----------------------0--------------------3
3-------------------------0----------------------2--------------------2

This is what I'm seeing in the data, but it doesn't really make sense. I thought the update simply would replace data in the target cube. Is my understanding incorrect?

Thanks in advance!
Mike

[declanr]

You are probably skipping zeroes in the datasource view. You can either untick that or create a destination view and use viewzeroout (my preference).

[mmckimson]

You are probably skipping zeroes in the datasource view. You can either untick that or create a destination view and use viewzeroout (my preference).

Actually, the view used for the source only has one option ticked, and that is "Automatic Recalculate" so it can be reviewed by the system administrator easily.... Any other ideas?
Mike

[Duncan P]

A view can be used in two ways, either to view data or as a datasource in TI. The way in it is used is different and the set of properties that affect its behaviour, and are visible in the UI, is different according to the use.

The only things that are shared are the subsets on the dimension, where defined.

As a UI Viewport
This has a set of dimensions (or subsets) on rows and a set on columns, and it has a selected item for all other dimensions. It has suppress zeros on rows, column or both.

As a data source
This uses the subsets on the dimensions but creates a tabular view with the columns in the cube dimension order, and a single column of values. It has properties that define whether to include zeroes, consolidations and rule calculated values or not, but these are completely separate from the suppression properties above. These properties can only be set by TI (see here et al.) or when creating a view from the cube datasource panel of the TI editor.

It sounds as though you might be confusing the two. I did as well for the first year or so.

[lotsaram]

...what doesn't seem to be working is the transfer of data from Cube 1 to Cube 2 as after the process is run, data will not always match up. It's almost as if what happens is that the process doesn't overwrite existing data in the target cube (Cube 2) with a zero when a zero exists in the source cube but not in the target.

As declanr already pointed out you are observing what you would expect given skipping null values in the source view (the default) and not zeroing out before the load in the target cube. To fix this you DON'T want to include nulls in the source view and copy in zeros, that would be hugely inefficient and could leave you with a process running for hours or even days depending on the dimensionality of the cube. What you need to do if the target should match the source exactly is make sure data is cleared from the target with a ViewZeroOut before loading from the source.

Actually, the view used for the source only has one option ticked, and that is "Automatic Recalculate" so it can be reviewed by the system administrator easily.... Any other ideas?

Yikes! Obviously no one told you yet that the view you see in the cube viewer and the view processed by TI are not the same object? Well they are kinda sorta the same object but not really, more different perspectives of the same object. To differentiate lets call a view used by TI a "data source view". A data source view has no concept of automatic calculation, this is something that exists only in the cube viewer GUI. Also a data source view has no concept of title or column dimensions, for a data source you can think of all dimensions as being on rows. The "skip null values" property in the data source view is also not the same property as "suppress zeros" in the cube viewer GUI. To manually create and manipulate the data source view there is a separate GUI, to get to this dialog you need to right click the cube and select "export as ASCII" then select the view. However in general this is much better done using code, by the sounds of it most things in your system are probably done manually and could be a lot more automated and run a lot better with a whole lot less maintenance and issues. It might be worth your while to get someone competent and experienced in for a few days to show you the ropes with this kind of thing.

[mmckimson]

The light bulb just went off... I'm an old EP guy and assumed that an "update" worked in a similar fashion as that product, i.e. with a default behavior that replaces everything in the target with everything that matches or is allocated from the source. Instead TM1 by default is really only moving what has to be moved (non-zeros) and if an end user had put in something one day my process would copy it from the source, and if the end user deleted it the next day, the zero that was now in the source would be ignored.

Hence, what I need to do is make sure the target is zeroed out selecting the correct option / view as a part of my normal process. Correct?

Mike

[Duncan P]

Absolutely. It's like substitute mode (as opposed to add mode) in an accumulation (or "consolidation" - if you're very old) link.

[mmckimson]

Absolutely. It's like substitute mode (as opposed to add mode) in an accumulation (or "consolidation" - if you're very old) link.

Uh oh... major screwup and I don't quite know what happened. Based upon my understanding, I went into my process that was transferring the data, and here is how I set up the import to zero out the target of the process:



As suggested in my initial post, data is moved from Cube 1 (where data entry occurs) to Cube 2 via this process, and once a month is closed a rule for that particular month kicks in which Cube 1 (the source for the process) now looks to Cube 2 for the data (which is of course why I'm copying it in the first place to Cube 2). After running the process with the above settings, the closed months in Cube 2 (and of course Cube 1 using rules) no longer contain data!!! This of course is a problem, as Cube 1 is looking to Cube 2 for values for closed months and these values existed before I ran the modified process. I would have thought that the option I've selected for my view extract leaving "Skip Rule Calculated Values" unchecked was correct.

What did I do wrong so we can restore the data and fix it?

Mike

[lotsaram]

Doing zero outs and imports with manually created and maintained processing views is NOT the way to go. This should be done with code and automated so that all the end user needs to do is run the process and pass in a parameter for the time period to copy. This will require writing code, the wizard will not do this for you!

I would repeat my advice to get in an experienced TM1 consultant for a few days to straighten some stuff out for you. If it happens that you are the external TM1 consultant then really I don't know what more to say.

[mmckimson]

I would repeat my advice to get in an experienced TM1 consultant for a few days to straighten some stuff out for you. If it happens that you are the external TM1 consultant then really I don't know what more to say.

Thanks for the advice. It's comments like this that turn people off from ever posting to a forum. As someone who has trained thousands of people on the use of application software (not TM1 obviously), we learn from our mistakes, not by hiring consultants to fix our problems. How is one to learn if they don't try something, ask questions, and try again?

[lotsaram]

I believe the advice is sound and I stand by it but I think you misunderstand where I'm coming from, in fact I am almost certain, so let me explain. According to the world as I see it a "good consultant" one worth what you pay them is not there to come in and code away according to a set of technical specifications (or a set of business requirements for that matter) and leave the customer with a perfectly functioning black box, nor is the consultant there to produce a perfectly functioning application complete with excellent documentation, that's still a black box albeit a semi-transparent one if the documentation is top notch. The job of a really good consultant is to mentor, train and teach the customer as the system is built so that the customer truly owns the solution and is fully capable of maintaining it, even further developing it independently of any consultant. This might not be true for transactional database systems but I believe it is true for any system or technology in the performance management space where the business logic and the system itself needs to be truly business owned to be successful, be that Cognos Planning, TM1 or say Anaplan.

To many traditional IT consulting mindsets this goes against the grain as traditionalists see this as cutting off a revenue stream of ongoing application maintenance consulting. I see it differently, if consultants truly focus on the best result and the best interests of the customer this will create new opportunities as customers see where other applications can be developed and as clients move to new positions and new companies. Of course not all customers are up for the journey or can commit business resources to really understanding a technology and there is no choice but a more "traditional" approach, but that doesn't change that how things should be done is that the consultant is part trainer and after even a very short engagement the customer should have a very good grasp of the technology and even how to develop it. Most if not all of the good CPM products are easy to learn and can be picked up quickly, with the right guidance remarkably quickly.

But as you rightly point out there are pitfalls. When learning there will always be mistakes, but it's good to have guidance and make small harmless mistakes as opposed to major stuff ups in a production system. I'm all for learning on the job or learning by doing as the most effective way to pick something up. But what I do not believe in is learning on someone else's dime. Having spent half of my career working in business and financial planning roles and half in consulting (not in 50:50 blocks, but some back and forth along the way) I take a very dim view of consultancies throwing people in to work on systems where they have no expertise. Obviously even consultants need to learn but this should be transparent to the customer and at no cost or reduced rates under the guidance of a senior consultant who does have a depth of knowledge.

But more to the point there's an incredible amount of flexibility in any CPM technology that can build driver based models and handle what-if analysis. There are thousands of ways to skin a cat so to speak, not all of them equal, not all of them good. A solution that "works" and "meets the business requirements" is not necessarily good or optimal. Learning out of a manual and by trial and error is not impossible but experience counts for a lot and can really smooth and quicken the learning path.

I'd be interested to know your opinion.

[mmckimson]

I believe the advice is sound and I stand by it but I think you misunderstand where I'm coming from, in fact I am almost certain, so let me explain. According to the world as I see it a "good consultant" one worth what you pay them is not there to come in and code away according to a set of technical specifications (or a set of business requirements for that matter) and leave the customer with a perfectly functioning black box, nor is the consultant there to produce a perfectly functioning application complete with excellent documentation, that's still a black box albeit a semi-transparent one if the documentation is top notch. The job of a really good consultant is to mentor, train and teach the customer as the system is built so that the customer truly owns the solution and is fully capable of maintaining it, even further developing it independently of any consultant. This might not be true for transactional database systems but I believe it is true for any system or technology in the performance management space where the business logic and the system itself needs to be truly business owned to be successful, be that Cognos Planning, TM1 or say Anaplan.

To many traditional IT consulting mindsets this goes against the grain as traditionalists see this as cutting off a revenue stream of ongoing application maintenance consulting. I see it differently, if consultants truly focus on the best result and the best interests of the customer this will create new opportunities as customers see where other applications can be developed and as clients move to new positions and new companies. Of course not all customers are up for the journey or can commit business resources to really understanding a technology and there is no choice but a more "traditional" approach, but that doesn't change that how things should be done is that the consultant is part trainer and after even a very short engagement the customer should have a very good grasp of the technology and even how to develop it. Most if not all of the good CPM products are easy to learn and can be picked up quickly, with the right guidance remarkably quickly.

But as you rightly point out there are pitfalls. When learning there will always be mistakes, but it's good to have guidance and make small harmless mistakes as opposed to major stuff ups in a production system. I'm all for learning on the job or learning by doing as the most effective way to pick something up. But what I do not believe in is learning on someone else's dime. Having spent half of my career working in business and financial planning roles and half in consulting (not in 50:50 blocks, but some back and forth along the way) I take a very dim view of consultancies throwing people in to work on systems where they have no expertise. Obviously even consultants need to learn but this should be transparent to the customer and at no cost or reduced rates under the guidance of a senior consultant who does have a depth of knowledge.

But more to the point there's an incredible amount of flexibility in any CPM technology that can build driver based models and handle what-if analysis. There are thousands of ways to skin a cat so to speak, not all of them equal, not all of them good. A solution that "works" and "meets the business requirements" is not necessarily good or optimal. Learning out of a manual and by trial and error is not impossible but experience counts for a lot and can really smooth and quicken the learning path.

I'd be interested to know your opinion.

Yes, I did misinterpret your reply, and for that I apologize!

My consulting approach (I've been doing it for 25 years) mirrors yours. With every engagement, I believe my role is to impart knowledge to my clients and to ultimately consult my way out of a job, empowering the customer to not only maintain whatever solution we've been engaged to develop / modify, but have the necessary knowledge and skill to visualize how to adapt this solution to their changing business environment. In fact, while I won't turn down the occasional "black box" engagement, I deplore them.

Unfortunately, I am the consultant on this TM1 job.... done as a favor for a long-standing customer at an exceptionally low rate who is quite understanding while I "learn" at their expense (the expense is quite minimal, as I'm not billing most of my unproductive time). Would this solution be better if a more experienced consultant were to have done the work? Quite possible, if not probable, but this is the path they've chosen and I'm doing my best.

Mike

[tomok]

Unfortunately, I am the consultant on this TM1 job.... done as a favor for a long-standing customer at an exceptionally low rate who is quite understanding while I "learn" at their expense (the expense is quite minimal, as I'm not billing most of my unproductive time). Would this solution be better if a more experienced consultant were to have done the work? Quite possible, if not probable, but this is the path they've chosen and I'm doing my best.

That's fine but I would advise you not to abuse this forum and use it as your personal help desk to help you get through the engagement. We don't exist to help people with topics that could and should be learned on your own or at a training class. There may be others willing to assist but they will likely lose their patience quickly. Remember, none of us are paid. I don't participate in this forum to help people finish a job they are unqualified to do, whether it was through their own volition, their boss did it to them, or a favorite client did it to them. I participate to share my experiences with challenging and/or unique circumstances. Good luck, you'll need it. TM1 is not the easiest thing to learn and certainly not the easiest to get right.

[mmckimson]

Unfortunately, I am the consultant on this TM1 job.... done as a favor for a long-standing customer at an exceptionally low rate who is quite understanding while I "learn" at their expense (the expense is quite minimal, as I'm not billing most of my unproductive time). Would this solution be better if a more experienced consultant were to have done the work? Quite possible, if not probable, but this is the path they've chosen and I'm doing my best.

That's fine but I would advise you not to abuse this forum and use it as your personal help desk to help you get through the engagement. We don't exist to help people with topics that could and should be learned on your own or at a training class. There may be others willing to assist but they will likely lose their patience quickly. Remember, none of us are paid. I don't participate in this forum to help people finish a job they are unqualified to do, whether it was through their own volition, their boss did it to them, or a favorite client did it to them. I participate to share my experiences with challenging and/or unique circumstances. Good luck, you'll need it. TM1 is not the easiest thing to learn and certainly not the easiest to get right.

Very true. I don't think posting a dozen questions in about a year would constitute abuse, would you?

[Martin Ryan]

Doing zero outs and imports with manually created and maintained processing views is NOT the way to go. This should be done with code and automated so that all the end user needs to do is run the process and pass in a parameter for the time period to copy. This will require writing code, the wizard will not do this for you!

Mike, there are a few examples of this code on the forum, such as this one. You'll find a few more by searching on "ViewZeroOut", hopefully they'll point you in the right direction.

You do have a dev box don't you? ViewZeroOut is a good way to balls up a perfectly good model, if it goes wrong.

Martin

[Martin Ryan]

Very true. I don't think posting a dozen questions in about a year would constitute abuse, would you?

No. The trouble we're having at the moment though is quite a few cut price consultants trying to use the forum as their own personal helpdesk. Unfortunately a few good apples are getting caught in the crossfire. I know there are a lot of "long time listeners, never time callers" because of the perception that they're going to get their heads chopped off for posting questions. It's a shame, but all time on the forum is freely given so people get a bit grumpy if this is taken advantage of - particularly if the questioners are trying to undercut the livelihoods of other TM1 consultants with very low rates. As I say, unfortunately some deserving forum users get caught in the crossfire.

[mmckimson]

Very true. I don't think posting a dozen questions in about a year would constitute abuse, would you?

No. The trouble we're having at the moment though is quite a few cut price consultants trying to use the forum as their own personal helpdesk. Unfortunately a few good apples are getting caught in the crossfire. I know there are a lot of "long time listeners, never time callers" because of the perception that they're going to get their heads chopped off for posting questions. It's a shame, but all time on the forum is freely given so people get a bit grumpy if this is taken advantage of - particularly if the questioners are trying to undercut the livelihoods of other TM1 consultants with very low rates. As I say, unfortunately some deserving forum users get caught in the crossfire.

Got it... hence the sensitivity from people with far more knowledge than mine! Trust me, after I finish this job, I'll never do a low-rent job again.... and this one was a one-time favor to a client that has paid me a lot of money over the years.

One of the biggest challenges in using forums is knowing when as a user you've crossed the line from trying to gain insight and knowledge versus coming across as just looking for someone to do your job for you for free... it's a fairly fine line when one is being sincere and I am that. In this case, I found myself truly surprised by how TM1 functionality handled zeros when transferring data from one cube to another, as it was "foreign" to my way of thinking. On the other hand, had I given a little more thought to how TM1 generally handles sparsity, it makes sense.

Onward and upward...
Mike

[mmckimson]

For those that have an interest, I successfully resolved my issue. It turns out that my problem in using ViewZeroOut had to do with my failure to correctly define the views being zeroed out by the process, something that only took a couple of minutes to fix. Once I properly defined what was to be zeroed out, it worked fine.

Thanks to those who pointed me in the right direction.
Mike