Lock TM1 database

[echosun9]

Dear Members,

We have TM1 (9.1) in our company, and the biggest problem I faced is all historical data were able to be change at any time by any super user who has rights to edit or upload data into TM1.

for example: we uploaded customer dimension in weekly basis, for some reasons some of those customer get duplicated. in this case all historical sales for that duplicated customers will be duplicated also.

my inquiry is to know how to protect our historical data in TM1 from any future changes.


Thanks in Advance

Mahmoud

[qml]

Erm... Use TM1 security to restrict these super users' access?
Depending on your model this will probably mean adding some element-level security to the dimension(s) that define which data is historic or not. You can also move your historic data to a different cube with no write access for these users.

[Martin Ryan]

While it'll certainly be worth looking into security if you haven't already I think your problem is more likely related to your import.

I presume when you say you're uploading your dimension on a weekly basis that you mean it's being imported from a source system. My guess is that if numbers are getting duplicated then your import procedure is not correct. Either you're adding one element to two parents so it ends up getting double counted, or you're not pulling through the right data from the source system.

Like most systems administrators and Turbo Integrator have full rights to all parts of the system. It'd be very hard to manage the system any other way. But as a result you need to ensure you administrators know what they are doing and that your TI processes are written properly and tested before being put in a live environment.

Martin

[echosun9]

Dear Martin/ qml,

First of all, I will take the opportunity to thank you for your help.

Second, I can give more clarifications that my issue will be more understand ability.

1- Our group have many subsidiaries.
2- We have ERP software as accounting system.
3- We have TM1 as reporting tools software.
4- We are running an export prosess in ERP, then import process in TM1.
5- Cubes for each subsidiary are: Sales, COGS, GP, Operating Expenses, Marketing Expenses, P&L.

For example ABC subsidiary Net Sales cube have following dimensions:
Channels ( Each channel included thousands of customers), Year, Days, Area, Sales Person, Items and account (which is Gross Sales, Discount, FOC... and all Net sales components).
In daily bases we uploaded sales by running above process(Export from ERP and import in TM1) this process have been checked and tested, It's working properly.
in few days we got an error messag during running daily sales import process in TM1. so, this force us to check our log files. error will be requiring either to update channels (customers) or areas or sales persons....etc.

after updating channels (customers) and in some times one customer code has been duplicated with a different channel (for example) because of changing a that customer dimension from one channel to another one.

TM1 will keep the old channel for that customer and will add him again in new channel. this will make TM1 calculate sales for this customer twice (one in his old channel and one in new channel) and result is sales got duplicated figures for that customer.

Solution: we are searching for the duplication customer and open TM1 server explorer\Dimensions\channels\Edit Dimension Structure\ delete the duplicated from consolidation.
But in case we didn't aware duplication in sales on time , so, our report will continue with duplication amounts.

If there is a manner to lock the old customers that TM1 keep their data as fixed (not recalculating everytime).
Or to make TM1 searching for customer code in all channel that if its existing in any channel should not add it to another channel.

Thanks again and best regards

[tomok]

The answer to your problem is to remove the customer from your channel dimension and make it a separate dimension. That way a particular customer's data can show in one channel for one period and in another channel for another period. When you combine customer and channel, and you have situations where the source data has customer data in more than one channel then you are going to continually have this problem. You could change the leaf level element in the channel dimension to concatenate the customer and channel, so as to create a unique key, but that would probably lead to confusion for your users whenever they tried to query for a particular customer because it wouldn't just be one element. You would need an alternate hierarchy in this dimension to roll up along customer, as well as channel. The easy, and probably best answer is to do as I suggested first. If I was designing a mode under your conditons that is how I would do it.

[David Usherwood]

A good analogy to this issue is payroll modelling. Staff move departments all the time, so you need to have staff and department as separate dimensions. Once you've done that it all starts working properly.

[jstrygner]

Just to add to what tomok wrote.

If your CUSTOMER dimension source table is in RDB and is maintained as a slowly changing dimension (so has its technical primary key [PK] and its natural id key [SK] and columns like [RECORD_VALID_FROM] and [RECORD_VALID_TO]) you could build your dimension basing on [PK] IDs.
The assumption would be, that whenever your customer changes its channel, old row with old [PK] is closed on [RECORD_VALID_TO] column and new record is created with new [PK], but same [SK] code.

This way you get:
RDB table:

Code: Select all

[PK]   [SK]    [CHAN]   [RVF]        [RVT]
1      CUS01   CH01     1900-01-01   2011-06-22
2      CUS01   CH02     2011-06-23   3000-12-31

TM1 dimension:

Code: Select all

 - All Customers
  - CH01
   - 1 (CUS01)
  - CH02
   - 2 (CUS01)

 - CUS01 Total
  - 1 (CUS01)
  - 2 (CUS01)

This would of course require that in your FACT table in RDB all facts point at [PK] = 1 before channel change and point at [PK] = 2 after it.

This way you do not need to separate Customers from Channels.

For better presentation you can combine [PK] with [SK] in an alias in any way comfortable for users.

Of course OLAP analysis is more [SK] than [PK] approach, but requirements are requirements and in your case you want history stay where it was, not to travel with current hierarchy.

As tomok suggested for purpose of analysis of one customer among all the history you can use alternative hierarchies (like 'CUS01 Total')

HTH