How to make a hard copy of the data?

[Learn_TM1]

Dear all,

I am wondering, is it possible to make a hard copy of the data? The situation is so, there is one Version dimension in our TM1 system. Set 2 elements: "final" and "working" to "Version". One day, the "working" data should be copied to "final". People can go on working on "working" data, by setting the privilege, the "final" data should not be changed by users.

We can make sure that "final" data not to be effected by Rule, by adding at the top of the Rule:
[{'final'}] = N:STET; C:CONTINUE;

So that the rules below will not change the data in "final" Version. Is this concept working?

And further more, even the rule concept above is working, if the other dimension structure were changed, for example, dimension Product, remove PrdA, and add PrdB, the aggregated data above PrdA and PrdB, say PrdSum in "final" Version will also be changed. Is it correct?

Is it possible to have a real hard copy in TM1, which will never be changed?

Best regards,

Learn_TM1

[tomok]

Is it possible to have a real hard copy in TM1, which will never be changed?

Yes. That is exactly how to do it.

[Mark RMBC]

This is definitely one for the MVP's but here is my experience (and if anything I say is wrong headed I will be more than happy to be put straight):

In one of my models I have the following at the start of the rule in a cube:

[ ] =N:if(ELISANC('UH_VERSIONS', 'Static Versions', !UH_VERSIONS) <> 0, STET, CONTINUE);

This works for that model.

However I tried this approach in another model where the rules were a lot more complex and suddenly encountered problems where adding the above logic changed some of the figures. So I tried a different approach, and I may get jeered for this, I decided to create a new cube with the exact same dimensions as the source cube but with no rules and created a process to copy the values from the source to this new cube.

I have a chore to run this daily and one advantage of this is that I could create a process which allowed the users to load in the previous days data.

There is a saying in accountancy along the lines of "if in doubt open an account", would “if confused open a cube” be a reasonable approach to TM1 or is that a big no no?

regards, Mark

[tomok]

This is definitely one for the MVP's but here is my experience (and if anything I say is wrong headed I will be more than happy to be put straight):

In one of my models I have the following at the start of the rule in a cube:

[ ] =N:if(ELISANC('UH_VERSIONS', 'Static Versions', !UH_VERSIONS) <> 0, STET, CONTINUE);

This works for that model.

However I tried this approach in another model where the rules were a lot more complex and suddenly encountered problems where adding the above logic changed some of the figures. So I tried a different approach, and I may get jeered for this, I decided to create a new cube with the exact same dimensions as the source cube but with no rules and created a process to copy the values from the source to this new cube.

I have a chore to run this daily and one advantage of this is that I could create a process which allowed the users to load in the previous days data.

There is a saying in accountancy along the lines of "if in doubt open an account", would “if confused open a cube” be a reasonable approach to TM1 or is that a big no no?

regards, Mark

This is a valid approach with the caveat that you can no longer have a view in TM1 that compares the two versions (a view can only pull from one cube). Yes, you can always create a websheet that does that so it's not impossible but just something to consider when designing your model(s). TM1 is truly a whiteboard with which you can do almost anything you want. That can be either a good thing or a bad thing, depending on your experience with the tool. Nothing is a no-no as long as it works for you.

[paulsimon]

Hi

For the first part of your question...

For the rules, I think all you need is

[ 'Final' ] = N: STET ;

at the top

I am not sure what C: CONTINUE would do. If anything, it would just make TM1 continue down and apply any C: level rules. However, in most cases C level rules would be wanted anyway. The most common being setting eg a Price to 0 at consol levels to avoid consolidating Price, or a Ratio calc that applies at both N and C levels. You would need to look at your particular C level rules.

I presume that users only have READ access to the Final element? A slightly safer approach is to use the Lock option. The process that copies the data in, unlocks the Final element, copies in the data and then locks it again. The advantage of a lock is that it applies to Admins, and to any Process, so there is even less change of the data being changed accidentally.

...

The second part of the question was about keeping dimension structures static, so that numbers are not altered by changes in hierarchies, ie you want the ability to report in a hierarchy as at a certain point in time, and not in the latest version of the hierarchies. This is a common difference between the requirements of financial/statutory accountants, who want things static as at a point in time, and management accountants who will generally want history restated in the latest hierarchy.

There are three possible approaches that I have seen

a) Copy all data to another cube which has the same dimension elements, but in which all dimensions are flat, ie consolidations in the original dimensions are N level dimensions in the flattened version of the dimension. Data would need to be exported from the original cube using a source view that did not suppress consolidations. The downside of this approach is that in any reasonably large cube you will force TM1 to calculate all possible consolidated results, which will take a long time, and the resulting flattened cube will be large.

b) Create an alternate hierarchy at that point in time, prefixing all elements in the hierarchy with the date. In reality this sort of requirement is generally only needed for statutory accounts, which for most companies means once or twice and year, and even for publicly listed companies it is generally only once a quarter. If you need to report on the data using the hierarchies as at a certain point in time then you select the alternate hierarchy with appropriate date prefix in each dimension, and then as the data in the Final element is static, and the hierarchies are static, you are bound to get the same results as you did at that time. It is probably best to front end this with a spreadsheet to coordinate selecting the appropriate 'as at' hierarchy in each dimension. Some people prefer to do this in a separate cube with the alternate hierarchies being created on copies of the dimensions. This is probably the TM1 equivalent of a slowly changing dimension in a data warehouse.

c) Do a Save Data, physically copy the .cub and .dim files to a separate server per 'as at' period. Keep the server shutdown unless required, by eg auditors who need to see the data exactly as it was. The downside is that you need to prevent any dimension changes until the accounts are closed down, which can mean that management accounts cannot update hierarchies until financial accounts have produced the statutory accounts.

Regards

Paul Simon

[Wim Gielis]

Good and accurate contribution Paul.

One addition:

The downside of this approach is that in any reasonably large cube you will force TM1 to calculate all possible consolidated results, which will take a long time, and the resulting flattened cube will be large.

Some dimensions do not need to have their consolidations flattened out as N-type elements.
Total year and the quarters will probably stay constant. Likewise for other dimensions. Other dimensions that are just a total with level-0 children below, don't need to have such a treatment either.
This on its own might already cut down the number of combinations in the source view dramatically.

[Learn_TM1]

Hi Paul,

Thanks a lot for your details answer. It gives me a big help. I would like to use "Lock" option. This feature is unknown for me. I tried it in one test system. If I set one element as "Lock", even admin can't change the data with it. So that the rule [ 'Final' ] = N: STET ; is also no necessary in this case.

For the hard copy issue, it is good idea to have flat hierarchy to copy the data to another cube, or even copy data to another server. It depends on the requirement.

Best regards,

Learn_TM1

[Steve Rowe]

Just one thing on Lock, it is the simplest approach but you need to make sure that there are no cross version rules in the system. Also make sure that the rights for removing the lock are very tightly controlled.

[Wim Gielis]

So that the rule [ 'Final' ] = N: STET ; is also no necessary in this case.

Why do you jump to this conclusion ?

[Learn_TM1]

Hi Wim,

I just realize that I understood the Paul's answer in the wrong way. The rule [ 'Final' ] = N: STET ; is in any way necessary. So that the conclusion should be:
1. Add rule [ 'Final' ] = N: STET ; at the top of the Rules;
2. Lock "Final" element.

Best regards,

Learn_TM1

[Learn_TM1]

Hi Steve,

Thanks for your remind. I should add rule [ 'Final' ] = N: STET ; to avoid other cross version rules. And only Admin should be able to remove the lock.

Best regards,

Learn_TM1

Just one thing on Lock, it is the simplest approach but you need to make sure that there are no cross version rules in the system. Also make sure that the rights for removing the lock are very tightly controlled.