Best Approach to save BPC 10.0 NW - YTD data into Periodic

[former_member212095]

Hello

We have a requirement to prepare Monthly Profitability Ratios for all entities in Legal Consolidation Model which can further be used for designing Dash Boards using BPC saved member data with the help of Bex Queries.

However, since our Legal Consolidation Model is built based on YTD figure, we want to save ACCTYPE - EXP & INC as periodic.AST & LEQ are always stored as YTD irrespective of data storage method.

We have 3 options:

1. Create a script logic in the same model using TMVL  functionality and convert YTD to periodic with a dummy Flow dimension

2. Create a new model and push data from previous model to new model using Script : Destination App and convert YTD to periodic

3. Create a new model and a new transformation file and map our P & L items with non cumulative key figures viz: 0DEBIT & 0CREDIT

Any suggestions on which should be the proper approach will be highly appreciated.

Regards

[former_member186338 · Accepted solution]

Hi Gaurav,

Can you explain what do you mean by "Monthly Profitability Ratios"? How do you want to use it?

In general approach 2 is commonly used...

Vadim

[former_member212095 · Accepted solution]

Hello Vadim

Thanks for the prompt reply.

What I meant by "Monthly Profitability Ratio" is creating various Profit & Loss ratios such as Gross Profit Ratio  ,Net Profit Ratio, EBIDTA Ratios at Periodic level.

Our ultimate objective is to use the aforesaid ratios in creating dashboards in  Business Object Dashboards via BEx query.

Regards

Gaurav

[former_member186338 · Accepted solution]

In general for ratios in EPM reports (some_member/other_member) you have to use dimension member formulas, not scripts. For BEx reports you will need periodic cube.

Vadim

[former_member186338 · Accepted solution]

P.S. Can you show some sample ratio formula?

[former_member212095 · Accepted solution]

Dear Vadim

We do not want to use dimension formulas/EPM Excel formula as we want to save the data so that we can use it for BEX report.

Please see the below a sample for Gross Profit Ratio

Gross Profit = Gross Profit/Revenue

Net Profit = Net Profit/Revenue

Below is the sample for Gross Profit Script:

*XDIM_MEMBERSET L_SCOPE= S_NONE

*XDIM_MEMBERSET L_CATEGORY=Actual

*XDIM_MEMBERSET L_SCOPE=S_NONE

*XDIM_MEMBERSET L_DATA_SRC=SYSTEM_INPUT

*XDIM_MEMBERSET L_RE_CURRENCY=LC

*XDIM_MEMBERSET L_INT_CO=I_NONE

[L_ACCOUNT].[#GROSS_PROFIT_RATIO]= [L_ACCOUNT].[GROSS_PROFIT]/[L_ACCOUNT].[16]

*COMMIT

Regards

Gaurav T

[former_member186338 · Accepted solution]

Sorry, but "we want to save the data" - is the wrong way. You will have absolutely incorrect results on the parent nodes.

Vadim

[former_member212095 · Accepted solution]

Dear Vadim

My concern is, in Bex query we are not able to find data for Parent member.Either I have to include all the GLs that are assigned to the parent and then workout the ratio formula in my Dashboard or write a script logic for all the ratios and then create a dashboard with the help of Saved ratios in Bex queries.

If there is any other suggestion on the turnaround kindly suggest.

Regards

[former_member200327 · Accepted solution]

Hi Gaurav,

If you build your BEx Query on BPC Virtual Provider that is generated if you check "Use as Source of Data" then members that defined as formulas will be available in the Query.

Regards,

Gersh

[Former Member]

Hi,

Having a new flow for the closing balance of current period minus closing flow of previous period provide you the period figures.

Vadim ~ Will it work ??

[former_member186338]

Sorry, but periodic measure will do the same for all accounts....