TM1 Dimensions
-
- Posts: 26
- Joined: Sun Mar 10, 2019 9:06 pm
- OLAP Product: TM1
- Version: Planning Analytics 2.0
- Excel Version: Excel version 2013
TM1 Dimensions
Is it possible to create a new dimension and then assign/add it to an existing cube?
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Dimensions
In some TM1 clients, yes. But as this exercise happens so infrequently (unless the cube architecture is way off) the usual practice is to export the data, backup rules, views, security, ..., destroy the cube, recreate with an additional dimension, and add the earlier items all back in. It’s not a trivial task in most cases.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 26
- Joined: Sun Mar 10, 2019 9:06 pm
- OLAP Product: TM1
- Version: Planning Analytics 2.0
- Excel Version: Excel version 2013
Re: TM1 Dimensions
Thank you Wim
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TM1 Dimensions
Unless you have bedrock version 4. In which case it is actually a trivial task.Wim Gielis wrote: ↑Tue Mar 26, 2019 7:45 pm ... the usual practice is to export the data, backup rules, views, security, ..., destroy the cube, recreate with an additional dimension, and add the earlier items all back in. It’s not a trivial task in most cases.
(any references in the model like DB or CellGetN to the changed cube will still need to be updated manually. But the actual action of adding, removing or replacing a dimension in an existing cube is a one click operation.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Dimensions
I wrote that myself too, including 4 modes:lotsaram wrote: ↑Tue Mar 26, 2019 10:53 pmUnless you have bedrock version 4. In which case it is actually a trivial task.Wim Gielis wrote: ↑Tue Mar 26, 2019 7:45 pm ... the usual practice is to export the data, backup rules, views, security, ..., destroy the cube, recreate with an additional dimension, and add the earlier items all back in. It’s not a trivial task in most cases.
(any references in the model like DB or CellGetN to the changed cube will still need to be updated manually. But the actual action of adding, removing or replacing a dimension in an existing cube is a one click operation.)
mode 1: add a new dimension
mode 2: delete an existing dimension
mode 3: swap the order of 2 dimensions
mode 4: switch a dimension with a new one
Here's the link: http://www.wimgielis.com/tm1_cubedimens ... ons_EN.htm
Next to that a colleague of mine wrote a TI script to also update the DB and CellGet/CellPut references when you add a dimension and assuming the dimension is added as the (new) first dimension in the cube. This full set of custom TI scripts are not mine and cannot be published here, in case someone would ask for it.
Last edited by Wim Gielis on Wed Mar 27, 2019 10:42 pm, edited 1 time in total.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: TM1 Dimensions
Too bad Bedrock 4 isn't publicly available yet... *hint hint*
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TM1 Dimensions
Yeah. Soon though. Just need to iron out as many bugs as possible before the public release on GitHub. Stay tuned.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: TM1 Dimensions
Hi
As well as CellGetNs you will also need to re-create any views on the cube with the extra dimensions. I have a utility that generates the TI script necessary to create all views on the current cube and then this can be manipulated in Excel to add a reference to the appropriate subset on the new dimension. The Ti can then be pasted into a process and run to re-create the views once the extra dimension has been added. The utility also helps with changing DBRW formula in related Excel formula, which is often the harder task. However, that can only work reliably if all the Excel sheets are in the Application Folders and therefore in the }Externals folder. If Excel sheets linked to TM1 have been developed by end users and stored on their local drives then you will need to coordinate with them before changing a cube structure like this.
However, there are two alternatives that you should consider that I don't think have been mentioned yet.
1) Consider creating another cube with the extra dimension, and populating both. This may be less work than re-creating everything that runs off the older cube, particularly if you have Cognos BI reports, etc. The downside is a certain amount of duplication of data, but if the cube is only used for reporting, it can help you to move over to the new structure more gradually.
2) Consider whether you can achieve the same use the new Hierarchy objects if you are on Planning Analytics.
Regards
Paul Simon
As well as CellGetNs you will also need to re-create any views on the cube with the extra dimensions. I have a utility that generates the TI script necessary to create all views on the current cube and then this can be manipulated in Excel to add a reference to the appropriate subset on the new dimension. The Ti can then be pasted into a process and run to re-create the views once the extra dimension has been added. The utility also helps with changing DBRW formula in related Excel formula, which is often the harder task. However, that can only work reliably if all the Excel sheets are in the Application Folders and therefore in the }Externals folder. If Excel sheets linked to TM1 have been developed by end users and stored on their local drives then you will need to coordinate with them before changing a cube structure like this.
However, there are two alternatives that you should consider that I don't think have been mentioned yet.
1) Consider creating another cube with the extra dimension, and populating both. This may be less work than re-creating everything that runs off the older cube, particularly if you have Cognos BI reports, etc. The downside is a certain amount of duplication of data, but if the cube is only used for reporting, it can help you to move over to the new structure more gradually.
2) Consider whether you can achieve the same use the new Hierarchy objects if you are on Planning Analytics.
Regards
Paul Simon
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TM1 Dimensions
Beware of the client front ends.
Anything with TM1 formulas will break (Excel with formulas, Active Forms)
In turn that means any published websheets using that cube are likely to break.
You get a little lucky with BI as Cognos reports *Might* not break since you can specify the default member to be used when none is specified. However testing is still likely required.
Haven't tested this theory in PAW but my guess is even though you may generate new views with the same name, the cube views in PAW will likley have to be re created.
Anything with TM1 formulas will break (Excel with formulas, Active Forms)
In turn that means any published websheets using that cube are likely to break.
You get a little lucky with BI as Cognos reports *Might* not break since you can specify the default member to be used when none is specified. However testing is still likely required.
Haven't tested this theory in PAW but my guess is even though you may generate new views with the same name, the cube views in PAW will likley have to be re created.
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TM1 Dimensions
Happy Easter! Bedrock 4 is officially released on GitHub.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.