We have a problem with sending data from Excel to TM1 as I believe the limit of the DBS/ DBSS formulae is 16 dimensions and we have some cubes with more.
A few options are being discussed presently:
Tm1Tools and use DBRW to copy/ paste
UDF using REST API to write the data
UDF using classic API to write the data - preferred approach as we are most familiar with this API, and involves minimal changes to the templates we already have set up.
Has anyone else managed to get round this limitation? I can't find much info on using the classic VBA API to write data other than Application.Run( "DBS", ... which is understandable. Any code I can use as a starting point would be a massive help. I am looking at the samples that ship with the installation as well.
Thanks,
Dan
Limitation of DBS/ DBSW
-
- Community Contributor
- Posts: 127
- Joined: Wed Oct 14, 2009 7:46 am
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 11
- Location: London
-
- MVP
- Posts: 3113
- 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: Limitation of DBS/ DBSW
Or, thinking out loudly, DBS(W) to a staging cube where some dimensions contain concatenated elements - so you have less dimensions.
Like, Year / Month / Version could be concatenated and merged in 1 (longer) dimension.
Company and department likewise, currency and measure likewise, ...
It depends on your cubes but you can gain quite some dimensions and still remain in a similar way of working.
When the recalculation is done, use an Action button to transfer the data from the input cube to the real cube by "un-concatenating" again.
I would stay away from doing too much outside of TM1. It will be technically more involved, and also you cannot use VBA it in TM1 Web, and other limiting factors exist. Which is not to say that the above method is perfect. Even more, I only invented it now and I never used it in a TM1 model.
Like, Year / Month / Version could be concatenated and merged in 1 (longer) dimension.
Company and department likewise, currency and measure likewise, ...
It depends on your cubes but you can gain quite some dimensions and still remain in a similar way of working.
When the recalculation is done, use an Action button to transfer the data from the input cube to the real cube by "un-concatenating" again.
I would stay away from doing too much outside of TM1. It will be technically more involved, and also you cannot use VBA it in TM1 Web, and other limiting factors exist. Which is not to say that the above method is perfect. Even more, I only invented it now and I never used it in a TM1 model.
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
-
- Community Contributor
- Posts: 127
- Joined: Wed Oct 14, 2009 7:46 am
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 11
- Location: London
Re: Limitation of DBS/ DBSW
I have found a sample: modCreateCubeFromFile that seems to have all the necessary functionality I require.