Hi,
I just wanted to ask if there is any way to access data stored in a user defined BW table directly from the EPM frontend?
Thank you!
Best regards,
Alex
Theoretically it's possible, but looks strange! Can you explain the business scenario you want to implement?
P.S. What do you mean by user defined BW table?
What data you want to store in this table?
How do you want to use this data in Excel?
Hi Vadim,
we are trying to implement our "own" type of script logic, featuring more features for calculating members and nodes.
This so called "Calculation Engine" is used to calculate all kind of different accounts in an overnight run.
To be able to achieve this we save formulas and target accounts for those formulas in Z table. (In this case I created a table in the dictionary).
This Z Table includes all formulas and if we analyse this table we will see that those formulas will create a tree where a previously calculated account will be used to calculate a following account.
What I now want to do is read this Z_Table and visualize this Calculation Tree with the help of VBA in Excel
Since there are many calculations necessary we don't want to go the member formula way for performance reasons.
Thank you!
Still not clear! How do you calculate results? Using custom logic badi or ...
We are using a script logic which triggers the calculation based on the previously defined formulas saved in a Z Table.
The calculation happens in a function module. Triggered by a custom logic badi, which was triggered by the script logic.
OK, doesn't matter it's done by function module, actually you have calculations in custom logic badi based on some formulas stored in Z table. But you can use dimension member properties for some dummy members instead of Z table. And properties can be read by EPM Excel :)
There are many formulas possible for one single account.
It is possible that a target account formula is only valid for a certain product.
Here an example in our syntax.
Account(A1), Product(P1) = Account(A5),Time(-1), Version(Actual) + Account(A2) / N(100)
Another Formula could always link to another product.
Use properties in the ACCOUNT and PRODUCT dimensions. Forget about Z tables :)
This wouldn't work...
the Product(P1) is just a filter on the account. The account is still the one being calculated. The business scenario itself is quite hard to describe by text.
But if we would use properties of the dimensions we would lack flexibility in any case. Also there is the problem of order of calculation...
How can you handle the order by which formulas are calculated?
They need to be calculated in their order otherwise the results will be wrong.
In the end we want to use Z Tables. Being able to read them from an excel report is just an extra on top.
This is also quite difficult to achieve if the formula is longer than the allowed number of character for member properties.
But what is the issue? Property is a string and can be long enough :)
Properties cap at a total of 3800 characters across all properties of a dimension.
I understand that this is a lot, but what if for whatever reason a formula is longer than that, then the whole function needs to be redesigned.
In the end this is also a project to acquire knowledge and being able to use relational tables in BPC (we had scenarios where customers wanted to use BPC even though most of their data should be modeled in a relational model) would be a good thing to know
P.S. By the way you can't replace member formulas with some calculated results - incorrect values on nodes...
To my mind there is something wrong with this idea in general!
Well you can if you correcly distribute the values based on some predefined rules on the base elements.
No, if you calculate ratio: something/something :)
Just simple sample from real system!
We have to implement rounding on data save to cube with variable number of digits for different accounts.
The rounding function is programmed in the write back badi but number of digits to round is stored in the ACCOUNT dimension property ROUND.
What do you do if your formula is a complex banking formula?
Can you provide real sample of your formula?
How many formulas you have in your Z-table?
This is unclear, since the formulas will be handled by the customer.
But we think it will be more than 500.
"But we think it will be more than 500." And what's the issue? You can store it in the property... 1000...
I need to open up a new answer since I can't comment on your last one anymore :)
99% won't have this amount of characters. But you have to keep in mind, that it's not just the formula itself which needs characters, but it needs the whole syntax.
Easy Suggestion Formula:
Account(numberOfContracts), Product(fondDepot), Category(Forecast) = (Account(NumberOfContracts), Time(-1), Product(fondDepot),
Category(Actual) OP(+) Account(NumberOfContracts), Time(-2), Product(fondDepot), Category(Actual) OP(+)
Account(NumberOfContracts), Time(-3), Product(fondDepot), Category(Actual)) OP(/) N(3) *
Account(numberOfContracts), Product(fondDepot), Category(Forecast), Version(plan_step1).
In the end after we read the data for that it will be a formula for a certain time member: numberOfContracts = (70+50+60)/3*1,05 = 63
This is something we want to be able to cover. And this in itself is a pretty easy formula which could be handled by scriptlogic. But we want to be able to use exponential functions, logarithmic functions and so on. This formula could be a lot longer though. At the moment it is already 412 characters long.
"But we want to be able to use exponential functions, logarithmic functions and so on" - you can use JavaScript functions in script logic :)
Please read: https://blogs.sap.com/2012/10/08/inline-rounding-in-rec-expression/
In general, if you want to create your own calculation engine instead of script logic I can recommend the following:
1. You need some interface to store formula texts: dimension property (web interface to maintain), text files on the server (Excel upload) - even script logic files can be used!, comments to some dummy members (EPMSaveComment).
2. Then you will need a DM package with custom logic badi to process formula texts and generate and store abap functions to perform calculations defined in formulas.
3. Then DM package with custom logic badi to execute calculations using generated abap code.
P.S. By the way please look on the internal BPC script logic calculation code with the calculation engine set to ABAP, you will see examples of formula processing with abap code generation.