Skip to Content
0

Read data from user defined BW table in EPM

Feb 09, 2017 at 09:40 AM

370

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Vadim Kalinin Feb 09, 2017 at 09:42 AM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 09, 2017 at 10:17 AM
0

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!

Show 14 Share
10 |10000 characters needed characters left characters exceeded

Still not clear! How do you calculate results? Using custom logic badi or ...

0
Former Member
Vadim Kalinin

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.

0

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

0
Former Member
Vadim Kalinin

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.

0

Use properties in the ACCOUNT and PRODUCT dimensions. Forget about Z tables :)

0
Former Member
Vadim Kalinin

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.

0
Show more comments
Former Member
Vadim Kalinin

This is also quite difficult to achieve if the formula is longer than the allowed number of character for member properties.

0

But what is the issue? Property is a string and can be long enough :)

0
Former Member
Vadim Kalinin

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

0
Show more comments

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!

0
Former Member
Vadim Kalinin

Well you can if you correcly distribute the values based on some predefined rules on the base elements.

0

No, if you calculate ratio: something/something :)

0
Vadim Kalinin Feb 09, 2017 at 10:45 AM
0

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.

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

What do you do if your formula is a complex banking formula?

0

Can you provide real sample of your formula?

How many formulas you have in your Z-table?

0
Former Member
Vadim Kalinin

This is unclear, since the formulas will be handled by the customer.
But we think it will be more than 500.

0

"But we think it will be more than 500." And what's the issue? You can store it in the property... 1000...

0
avatar image
Former Member Feb 09, 2017 at 01:12 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

"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/

0
Vadim Kalinin Feb 09, 2017 at 01:43 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0