Bedrock enhancements when clearing data
Posted: Sat Feb 01, 2020 11:06 am
Hello all, especially Lotsaram and co-workers,
For a major TM1 site I have been developing a generic data clear procedure in Turbo Integrator, making use of Bedrock (v3).
The idea is to fill out an Excel file to indicate which slices of the data should be cleared. Then, record after record, TI reads the text file variant of the Excel file.
There are selection possibilities for dimensions in pairs:
- column A contains a dimension name like 'Year'
- column B contains the selection to be cleared like '2016 + 2017 + 2018'
- column C contains a dimension name like 'Scenario'
- column D contains the selection to be cleared like 'Actual'
- column E contains a dimension name like 'Version'
- column F contains the selection to be cleared like 'FIN*'
- and so on
I allow up the 5 'pairs' per record but that limit can be increased of course.
An additional column uses the TEXTJOIN function in Excel to concatenate the different, non-empty, dimension/element pairs. That TEXTJOIN function is awesome. It allows me to skip the pairs that are empty without creating empty selections. The end result of the concatenation goes to the pFilter argument in Bedrock (clear) processes.
Wildcards are supported in the selections: * and ? in selections lead to an MDX-driven subset.
With the ~ character as the first character of the selection, I take 'all elements except what follows'. ~FIN* means all elements except elements like FINAL.
Dimension names do not support wildcards but also there it could be extended.
The code will validate whether all supplied selection pairs are in fact selections in dimensions that are part of a cube to be cleared. So providing 4 selections in 1 record means that all 4 dimensions need to be part of the cube. If not, the record is rejected since clearing too much data is not harmless
As said, after initial validations, the process executes a number of Bedrock processes, notably data.clear. There I have a number of remarks:
- the mother process already creates MDX-driven subsets for selections if they contain wildcards. In the data clear Bedrock process, I do not want to create a new subset, since it already exists. Would there be an option to indicate that creating subsets is optional ? Right now I changed the data.clear Bedrock process to not create them or delete all its elements if it already exists, but a general parameter in the generic Bedrock process would be far better. I haven't checked Bedrock v4 if it already exists.
- Clearing data will be blocked bij TM1 when 1 or more subset element is locked. Would it be possible to check for locked elements in the generic data clear process such that this case is avoided ? It would need a loop over the relevant elements in the }ElementProperties control cube, so I don't think it's a major change. If that control cube does not exist, we can avoid the loop.
For this customer I used Bedrock since it came rather close to my data clear purposes but if the deviation of the Bedrock process and what I need to do would be bigger, I would have rolled my own TI process rather than using Bedrock. Still it turns out that by making the Bedrock processes a bit more generic, we can allow many more combinations.
Best regards,
Wim
For a major TM1 site I have been developing a generic data clear procedure in Turbo Integrator, making use of Bedrock (v3).
The idea is to fill out an Excel file to indicate which slices of the data should be cleared. Then, record after record, TI reads the text file variant of the Excel file.
There are selection possibilities for dimensions in pairs:
- column A contains a dimension name like 'Year'
- column B contains the selection to be cleared like '2016 + 2017 + 2018'
- column C contains a dimension name like 'Scenario'
- column D contains the selection to be cleared like 'Actual'
- column E contains a dimension name like 'Version'
- column F contains the selection to be cleared like 'FIN*'
- and so on
I allow up the 5 'pairs' per record but that limit can be increased of course.
An additional column uses the TEXTJOIN function in Excel to concatenate the different, non-empty, dimension/element pairs. That TEXTJOIN function is awesome. It allows me to skip the pairs that are empty without creating empty selections. The end result of the concatenation goes to the pFilter argument in Bedrock (clear) processes.
Wildcards are supported in the selections: * and ? in selections lead to an MDX-driven subset.
With the ~ character as the first character of the selection, I take 'all elements except what follows'. ~FIN* means all elements except elements like FINAL.
Dimension names do not support wildcards but also there it could be extended.
The code will validate whether all supplied selection pairs are in fact selections in dimensions that are part of a cube to be cleared. So providing 4 selections in 1 record means that all 4 dimensions need to be part of the cube. If not, the record is rejected since clearing too much data is not harmless
As said, after initial validations, the process executes a number of Bedrock processes, notably data.clear. There I have a number of remarks:
- the mother process already creates MDX-driven subsets for selections if they contain wildcards. In the data clear Bedrock process, I do not want to create a new subset, since it already exists. Would there be an option to indicate that creating subsets is optional ? Right now I changed the data.clear Bedrock process to not create them or delete all its elements if it already exists, but a general parameter in the generic Bedrock process would be far better. I haven't checked Bedrock v4 if it already exists.
- Clearing data will be blocked bij TM1 when 1 or more subset element is locked. Would it be possible to check for locked elements in the generic data clear process such that this case is avoided ? It would need a loop over the relevant elements in the }ElementProperties control cube, so I don't think it's a major change. If that control cube does not exist, we can avoid the loop.
For this customer I used Bedrock since it came rather close to my data clear purposes but if the deviation of the Bedrock process and what I need to do would be bigger, I would have rolled my own TI process rather than using Bedrock. Still it turns out that by making the Bedrock processes a bit more generic, we can allow many more combinations.
Best regards,
Wim