[Excel] Writeback in Cube with VBA

Post Reply
HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

[Excel] Writeback in Cube with VBA

Post by HighKeys » Thu Aug 29, 2019 7:01 am

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

User avatar
Alan Kirk
Site Admin
Posts: 6162
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: [Excel] Writeback in Cube with VBA

Post by Alan Kirk » Thu Aug 29, 2019 7:09 am

HighKeys wrote:
Thu Aug 29, 2019 7:01 am
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
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.

User avatar
orlando
Community Contributor
Posts: 134
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

Post by orlando » Thu Aug 29, 2019 7:23 am

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?

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: [Excel] Writeback in Cube with VBA

Post by HighKeys » Thu Aug 29, 2019 7:31 am

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.

User avatar
orlando
Community Contributor
Posts: 134
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

Post by orlando » Thu Aug 29, 2019 7:44 am

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

User avatar
Alan Kirk
Site Admin
Posts: 6162
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: [Excel] Writeback in Cube with VBA

Post by Alan Kirk » Thu Aug 29, 2019 8:01 am

HighKeys wrote:
Thu Aug 29, 2019 7:31 am
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".
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.)
HighKeys wrote:
Thu Aug 29, 2019 7:31 am
I also thought DBRW stands for "Read Write"
As Orlando mentioned, the W stands for Wide Area Network.
HighKeys wrote:
Thu Aug 29, 2019 7:31 am
i can try it again with DBS(S) but last time i got a wrong type error.
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.
HighKeys wrote:
Thu Aug 29, 2019 7:31 am
What do you guys think about DBSW?
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.

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: [Excel] Writeback in Cube with VBA

Post by HighKeys » Thu Aug 29, 2019 10:36 am

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. :)

orlando wrote:
Thu Aug 29, 2019 7:44 am
i think i really don't want to know what you are building there ;-)
VBA is not the future - in my opinion.
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 :D


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

User avatar
orlando
Community Contributor
Posts: 134
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

Post by orlando » Thu Aug 29, 2019 11:00 am

HighKeys wrote:
Thu Aug 29, 2019 10:36 am

Thanks for your help here! Now i got a better understanding of TM1 and PAX.
Sure you are working with PAX and not with TM1 Perspectives?
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. :)
You should consider to read the documentation or find a training. Otherwise you don't have a chance to win the war
This thread could be helpful.
https://www.tm1forum.com/viewtopic.php?f=3&t=14823
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 :D
For working with TM1 you do not need Visual Studio.
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

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: [Excel] Writeback in Cube with VBA

Post by HighKeys » Thu Aug 29, 2019 12:01 pm

Hey,

yea I'm the only warrior :D :lol:
orlando wrote:
Thu Aug 29, 2019 11:00 am
For working with TM1 you do not need Visual Studio.
You can get good results with Turbointegrator, Excel (without VBA), Rules and TM1Web.
But you have to study, study and study
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 :lol:


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 :cry:

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.

User avatar
orlando
Community Contributor
Posts: 134
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

Post by orlando » Thu Aug 29, 2019 1:11 pm

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 :lol:
In TM1 you do the calculation in the Database with Rules and Turbointegrator.
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)

Nope! I think i use Perspectives :D 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"
I see - your colleagues are experts ;-)
Who made the setup of TM1? i think some consultants - could you ask them about your system?
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 :cry:
If you like to develop in TM1, u need an Admin Account in TM1
Do u have Server or DB for development?
For the Version: https://www-01.ibm.com/support/docview. ... wg21654934
But yea if i hit Windows and write TM1 i can find "Architect" and "Perspectives" so it should be perspectives :)
jupp - it's perspectives
otherwise your VBA code would not work ;)

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 :)
As I said befoe, study the guides, try to get a training, go to meeting IBM provides

Post Reply