cancel
Showing results for 
Search instead for 
Did you mean: 

Read data from user defined BW table in EPM

mondfalter
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (5)

Answers (5)

former_member186338
Active Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

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.

mondfalter
Participant
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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.

mondfalter
Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

Can you provide real sample of your formula?

How many formulas you have in your Z-table?

mondfalter
Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

mondfalter
Participant
0 Kudos

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!

former_member186338
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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!

mondfalter
Participant
0 Kudos

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.

mondfalter
Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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 🙂

former_member186338
Active Contributor
0 Kudos

No, if you calculate ratio: something/something 🙂

mondfalter
Participant
0 Kudos

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.

mondfalter
Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

mondfalter
Participant
0 Kudos

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.

mondfalter
Participant
0 Kudos

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

former_member186338
Active Contributor

Looks like you are trying to invent some unrealistic scenarios 🙂

Formulas with thousands of characters??? Try to be realistic!

About relational tables - please don't mix cubes and flat tables, the resulting software will be terrible in future support!

P.S. For sure you can use RFC libraries to get data in Excel from SAP tables but to my mind it's like writing new BPC yourself!

former_member186338
Active Contributor
0 Kudos

"In the end we want to use Z Tables." - I see, you just "want"!

In this case no arguments will be helpful!

former_member186338
Active Contributor
0 Kudos

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?