[Excel] Writeback in Cube with VBA
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
[Excel] Writeback in Cube with VBA
Hello friends,
i was woundering about if it is possible to writeback a Value to a cube using VBA?
If i use Application.Run with the DBRW Formula it just gets the value and i can't change.
Any hint for me?
Thanks and BR
i was woundering about if it is possible to writeback a Value to a cube using VBA?
If i use Application.Run with the DBRW Formula it just gets the value and i can't change.
Any hint for me?
Thanks and BR
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: [Excel] Writeback in Cube with VBA
Only that maybe DB =>RRRR <===ead isn't the best formula to use when you want to DB ===>SSS <==end a value.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
Out of pure curiosity, if your user can type in data, why can't he type it directly into a cell with the DBRW formula and press F9? Why in VBA?
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
Its a bigger calculation with % Keys, its my Forecasting Tool, now i wanna try to impement an Autoupload to TM1.
@Alan, i thought DBS stands for "String" and DBSS "Send String".
I also thought DBRW stands for "Read Write"
i can try it again with DBS(S) but last time i got a wrong type error.
What do you guys think about DBSW?
Thanks and BR
Edit: Somebackground, most of this sheet is handled in Userforms.
@Alan, i thought DBS stands for "String" and DBSS "Send String".
I also thought DBRW stands for "Read Write"
i can try it again with DBS(S) but last time i got a wrong type error.
What do you guys think about DBSW?
Thanks and BR
Edit: Somebackground, most of this sheet is handled in Userforms.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
i think i really don't want to know what you are building there
VBA is not the future - in my opinion.
The W of DBRW does not stand for write - you even can write back data with a DBR
The DBRW Forumla was the first try of applix to get a better performace of perspectives in a WAN (that's what i learnd 12 years ago)
DBR formulas are processed sequentially
DBRW are processed in a kind of "batch" mode
The S in DBS stands for send an can send figures (only figures)
A DBSS can send strings to TM1
VBA is not the future - in my opinion.
The W of DBRW does not stand for write - you even can write back data with a DBR
The DBRW Forumla was the first try of applix to get a better performace of perspectives in a WAN (that's what i learnd 12 years ago)
DBR formulas are processed sequentially
DBRW are processed in a kind of "batch" mode
The S in DBS stands for send an can send figures (only figures)
A DBSS can send strings to TM1
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: [Excel] Writeback in Cube with VBA
Which is why it's kind of important to consult the reference guide. (This is an older version's copy but IBM keep shifting the naffing addresses around. It will suffice for the purposes of this thread.)
As Orlando mentioned, the W stands for Wide Area Network.
Again, the Reference Guide:
DBS sends a numeric value to a TM1 cube. This function cannot send a string to a cube. To send strings, use the DBSS function.
As Orlando also mentioned the W functions do a batch send and receive rather than doing it one value at a time. It helps when you have an entire sheet of send formulas.
Except that when you are doing it by VBA you are doing it one value at a time.
So that value of a DBSW in that circumstance would be....? Pat yourself on the back if you answered "bupkis".
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
Hi,
Thanks for your help here! Now i got a better understanding of TM1 and PAX.
Sorry for sometime my noobish questions, i started in a new Company and we use TM1 here, it is my fristtime where i have to handle it, i have to administrate this tool and i think its very powerful, but at the moment not optimal in use to say it friendly.
To get it Clear:
DBS is for send a value, "W" stands for WAN and batch the sends to a collection for better Performance...
Hmmmm now i should use DBSW to batch them and i should use a tmp sheet where i place all DBSW Formulas to get that batched into TM1?
Sounds good!
THank you, if someone is interested i will share my code again
BR
Thanks for your help here! Now i got a better understanding of TM1 and PAX.
Sorry for sometime my noobish questions, i started in a new Company and we use TM1 here, it is my fristtime where i have to handle it, i have to administrate this tool and i think its very powerful, but at the moment not optimal in use to say it friendly.
A better way to create Custom Forms for Work with TM1? I dont have the rights to use Visual Studio, so i embedded some DLL's to excel and use the userforms there
To get it Clear:
DBS is for send a value, "W" stands for WAN and batch the sends to a collection for better Performance...
Hmmmm now i should use DBSW to batch them and i should use a tmp sheet where i place all DBSW Formulas to get that batched into TM1?
Sounds good!
THank you, if someone is interested i will share my code again
BR
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
Sure you are working with PAX and not with TM1 Perspectives?
You should consider to read the documentation or find a training. Otherwise you don't have a chance to win the warSorry for sometime my noobish questions, i started in a new Company and we use TM1 here, it is my fristtime where i have to handle it, i have to administrate this tool and i think its very powerful, but at the moment not optimal in use to say it friendly.
This thread could be helpful.
https://www.tm1forum.com/viewtopic.php?f=3&t=14823
For working with TM1 you do not need Visual Studio.A better way to create Custom Forms for Work with TM1? I dont have the rights to use Visual Studio, so i embedded some DLL's to excel and use the userforms there
You can get good results with Turbointegrator, Excel (without VBA), Rules and TM1Web.
But you have to study, study and study
If you are the "lonly warrior" in your company, maybe events like this give u an impression what is possible with TM1:
https://www-01.ibm.com/events/wwe/grp/g ... cale=de_DE
Via YouTube u can also find some stuff
Best regards
orlando
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
Hey,
yea I'm the only warrior
I see that TM1 isnt be made for VBA use, but Calulating a Year on a daily base would be hard with formulas in a 32 Bit Environment
Today i got my Server User so i can get directly on the TM1 Server, i think i sould explore the server to get the right version number from TM1, the Documentation have so many Versions
But yea if i hit Windows and write TM1 i can find "Architect" and "Perspectives" so it should be perspectives
I'm very sorry if i flood your forum with my nonsens questionsm but i try to get deep into as fast as i can to get the best workingenviroment for me and the company
yea I'm the only warrior
Hmmmm Excel without VBA? Could you explain why? I do Excel since years but I'm not the Formula friend, cause VBA brings more performance to it.
I see that TM1 isnt be made for VBA use, but Calulating a Year on a daily base would be hard with formulas in a 32 Bit Environment
Today i got my Server User so i can get directly on the TM1 Server, i think i sould explore the server to get the right version number from TM1, the Documentation have so many Versions
But yea if i hit Windows and write TM1 i can find "Architect" and "Perspectives" so it should be perspectives
I'm very sorry if i flood your forum with my nonsens questionsm but i try to get deep into as fast as i can to get the best workingenviroment for me and the company
Last edited by HighKeys on Fri Aug 30, 2019 12:38 pm, edited 1 time in total.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: [Excel] Writeback in Cube with VBA
In TM1 you do the calculation in the Database with Rules and Turbointegrator.HighKeys wrote: ↑Thu Aug 29, 2019 12:01 pm
Hmmmm Excel without VBA? Could you explain why? I do Excel since years but I'm not the Formula friend, cause VBA brings more performance to it.
I see that TM1 isnt be made for VBA use, but Calulating a Year on a daily base would be hard with formulas in a 32 Bit Environment
Excel should be there to read or write data - everythink else could be done in TM1
(and yes, sometimes you need to do thinks in Excel - but I the main calculation/features schould be done in tm1)
I see - your colleagues are experts
Nope! I think i use Perspectives This is also a topic where nobody can help me here, They say just yes everytime, when i ask "do we use PAX?" then they say yes, if i ask "so then we dont have perspectives?" they answer me with "no we have perspectives in use, its the excel ribbon tool for TM1"
Who made the setup of TM1? i think some consultants - could you ask them about your system?
If you like to develop in TM1, u need an Admin Account in TM1Today i got my Server User so i can get directly on the TM1 Server, i think i sould explore the server to get the right version number from TM1, the
Documentation have so many Versions
Do u have Server or DB for development?
For the Version: https://www-01.ibm.com/support/docview. ... wg21654934
jupp - it's perspectivesBut yea if i hit Windows and write TM1 i can find "Architect" and "Perspectives" so it should be perspectives
otherwise your VBA code would not work
As I said befoe, study the guides, try to get a training, go to meeting IBM providesI'm very sorry if i flood your forum with my nonsens questionsm but i try to get deep into as fast as i can to get the best workingenviroment for me and the company