Some scenarios need users to not change any data in a cube based on business rule. In this case the cube should be locked but still can be viewed.
For example in budget cycle there are period of preparation and submission. There must be deadline for preparation and submission. After deadline date reached, business users should be forced to submit the budget. How it can be done in TM1 data modeling?
In TM1 Application once you submit budget, you cannot change data until it is rejected by reviewer/approver. But how if business users not yet submit it? TM1 data modeling should be able to lock it to prevent any changes.
How to lock certain data cells in a cube, unlock and lock it if required using a simple tool that can be done by business user?
I. Create cube lock setting
Create a cube contain some parameters you want to set. In this sample I set it based on period year, month, and version (data version). Just name it ‘lock_setting’ cube.
The structure will be like this
Year: to identify what year of data
Month: to identify what month of data
Version: to identify what version of data
Status: to define if data is read only or editable. Set it as ‘string’
II. Set the lock status
Let say, we are going to lock budget where the cycle started from July last year to Jun current year.
So the setting is like this below picture:
“Read” mean Read only, while blank is editable.
It can be read as: “lock budget data from Jul 2018 to Jun 2019”
III. Create Cell Security Cube
Remember that TM1 security access can be set until cell level. So, to lock cube only for certain data, we can use this feature.
Security cell setting is stored in control cube:
}CellSecurity_[Cube_Name]
The structure cube will be same with the cube [cube_name] plus dimension that listed group of user: }groups
This dimension }Groups is required to define what group of users will be affected by the setting.
By default the Cell security cube is not there until we set the cell security for the cube. To do that:
- Right click cube Name
- Security
- Create Cell Security Cube
To be able to see the cube cell security:
- Go to view
- Display Control Cube
- Browse cube: }CellSecurity_CubeName
IV. Create rule to set cell security access
Then we are going to automate the status by taking data from the cube contain setting of lock by period:
And copy the content to cell security cube.
We need to create rule:
[] = S: db(‘lock_setting’,!gbl_year,!gbl_month,!gbl_version,’status’);
As we do not need feeder here, do not need to put skipcheck nor feedstrings.
The rule will affect all of group users. You can put group name if you want it impact to certain group only.
For example:
[‘HR_Group’] = S: db(‘lock_setting’,!gbl_year,!gbl_month,!gbl_version,’status’);
So the security cell setting will only affect the group “HR Group” only.
All of above steps can be done if you have admin privilege.
V. Test the Security Cell Setting
To test the cell security, login as a non-administrator user, and open the cube. You will see cells are locked follow the setting set in ‘lock_setting’ cube.
To be more users friendly, you can create interface in excel to access the cube setting, so it will be easier for user to change the setting based on their requirement (Veronika Rotua Gultom)
Veronika Rotua Gultom 