BPC10.1: How to define TIME dimention entry for Fiscal Periods 00 & Special Periods?

[Former Member]

Hi Expert

In BW FI data are segregated in Fiscal Period 00 to 16 as shown in following screen shots:

I define these periods as follows specially fiscal period 00 and Special Period in TIME dimension:

When I run Validate and Process Transformation file no error occurred. But when I run Package for loading transaction data it shows following error:

Task name LOAD INFOPROVIDER:
Error running default logic ()
Property MONTHNUM is not maintained for ID 2017.00 in dimension TIME
Submit count: 40000
Reject count: 0

model: Consolidation. Package status: ERROR

[former_member186338 · Accepted solution]

OK, now it's clear!

Has to be:

Transformation file:

*MAPPING
...
FLOW=*IF(0FISCPER(6:7)=*STR(00) then *STR(F00);0GL_ACCOUNT)
...

All data for period 00 will go to F00 for example!

Conversion file:

EXTERNAL      INTERNAL
F00           F00
CBY002        F30
CBY001        F30
...

You don't need any vb in your conversion file!

[Former Member · Accepted solution]

vadim.kalinin

*MAPPING
...
FLOW=*IF(0FISCPER(6:7)=*STR(00) then *STR(F00);0GL_ACCOUNT)
...

Is above FLOW statement save GL wise opening balance into Cube?

e.g.

GL      FLOW   PERIOD  .....    AMOUNT 
CBY001   F00   2010.01          1000
CBY001   F30   2010.01          5000
....
GBX002   F00   2010.01          3000
GBX002   F30   2010.01          3000
....

What is the purpos of following conversion:

EXTERNAL      INTERNAL
F00           F00

[former_member186338 · Accepted solution]

imran.shafiq

"Is above FLOW statement save GL wise opening balance into Cube?" - ONLY for period 00!!!

Look on the mapping:

FLOW=*IF(0FISCPER(6:7)=*STR(00)then*STR(F00);0GL_ACCOUNT)

If OFISCPER(6:7)=*STR(00)

2019000 - characters 6:7 = "00" - period 00, F00 will be sent to conversion for processing

2019001 - characters 6:7 = "01" first period, 0GL_ACCOUNT will be sent to conversion for processing

In conversion:

IF F00 - keep it, same F00

If some account - then replace it with defined FLOW

Absolutely easy!!!

[Former Member · Accepted solution]

I mean if I see the data in BW what is the difference between 2019000's row and 2019001's row for any specific GL?

[former_member186338 · Accepted solution]

imran.shafiq

I am unable to understand what is not clear:

"2019000's row" will go to 2019.01, Flow F00 for all accounts

"2019001's row" will go to 2019.01, Flow specific for each account (mapping is defined in conversion file).

[former_member186338 · Accepted solution]

imran.shafiq

P.S. Please talk to any finance person in your company, he will explain!

[Former Member · Accepted solution]

Yes, now it's clear.

[former_member186338 · Accepted solution]

imran.shafiq

Then, please accept the correct answer!

[Former Member · Accepted solution]

Vadim Kalinin

As per your recommendation

After assigning 00 to 01

  EXTERNAL         INTERNAL
  ????000          ????.01
  ????001          ????.01
 ....

And assigning Flow to GL including F00

EXTERNAL         INTERNAL
  F00            F00
  GAA001         F20
  GAA002         F20
 ....

And mapping

*MAPPING
VERSION=*NEWCOL(ACTUAL)
CURRENCY=*NEWCOL(LC)
SCOPE=*NEWCOL(S_NONE)
AUDIT_TRAIL=*NEWCOL(INPUT)
TIME=0FISCPER(1:4) + *STR(.) + 0FISCPER(6:7)
ENTITY=0COMP_CODE
INTERCO=*STR(I_NONE)
ACCOUNT=0GL_ACCOUNT
PROFIT_CTR=0CO_AREA+0PROFIT_CTR
COSTCENTER=0CO_AREA+0COSTCENTER
FLOW=*IF(0FISCPER(6:7)=*STR(00) then *STR(F00);0GL_ACCOUNT)
AMOUNT=0SALES

Then running package for one year data, all flows are assigned to there GLs successfully but F00 not assigned to every GLs

So, what else I missed?

[former_member186338 · Accepted solution]

I am VERY sorry, but do you understand what you are doing???

In transformation:

TIME=0FISCPER(1:4)+*STR(.)+0FISCPER(6:7)

Result will be for 2017000 -> 2017.00, for 2017001 ->2017.01

And then you have conversion for time:

EXTERNAL         INTERNAL
????000          ????.01
????001          ????.01

But you will never have external ????000 or ????001 !

Conversion receives data AFTER transformation!

And after transformation you have:

2017.00

2017.01

...

Correct conversion for TIME will be:

EXTERNAL         INTERNAL
????.00          ????.01
????.01          ????.01
...

Another option - for your existing conversion file for TIME dimension use the following line in transformation mapping:

TIME=0FISCPER

[Former Member · Accepted solution]

Thanks for your guidance and highlighting my mistakes.

[former_member186338]

You can use 12 for 13...16 and 01 for 00

In the conversion file for TIME dimension simply use mapping.

[Former Member]

Please correct me if I understand wrong, period 00 contained opening balance of any GL.

e.g. In

GL Account: A0001 contained

Period00: 1000$ and

Period01: 2000$

If we mapped 00 into 01 then balance of 01 is changed to 3000$ which is incorrect then how can we use opening balance and 01 balance seperately in BPC?

[former_member186338]

imran.shafiq

You can load 00 to 01 to special FLOW member (Opening Flow). Easy!

[Former Member]

Vadim Kalinin

Kindly guide me how to do this? I mean Opening Flow for every GL, currently we have 4000 GLs.

[former_member186338]

imran.shafiq

For example, in transformation file:

*MAPPING
FLOW=*IF(0FISCPER(6:7)=*STR(00) then *STR(F_OPEN);/ERP/FLOW) 
...

[Former Member]

Vadim Kalinin

Currently I'm using following code for flow in transformation file:

  *MAPPING
  FLOW=*STR(~)+0GL_ACCOUNT
.........
  *CONVERSION
  FLOW=Conversion.xls!FLOW
.........

And in conversion file I'm currently using 5 flow types:

EXTERNAL  INTERNAL
*~EYY111  if(vb:left("%external%",1)=- Then F10; F10)
 .....
*~AYK444  if(vb:left("%external%",1)=- Then F15; F15)
 .....
*~GYK333  if(vb:left("%external%",1)=- Then F20; F20)
 .....
*~CYK222  if(vb:left("%external%",1)=- Then F30; F30)
 .....
*~ZYK555  if(vb:left("%external%",1)=- Then F35; F35)
 .....

Let suppose if Opening flow is F00 than How can I incorporate this logic for every GL into my transformation file and conversion file?

[former_member186338]

imran.shafiq

Unable to understand the existing logic...

0SALES is not used at all

vb test is strange

not all strings of conversion file provided...

you are talking about only 5 flow types...

[former_member186338]

imran.shafiq

FLOW=*STR(~)+0GL_ACCOUNT

Still absolutely strange...

and

"not all strings of conversion file provided..."

[Former Member]

Yes you are right about 0Sales, corrected

Actually I'm trying to assign Flows according to GLs e.g. Assigning Flow F10 for all the Assets GLs

Am I doing correctly into conversion file?

[former_member186338]

imran.shafiq

Sorry again, but in order to recommend you some solution I need to understand FULL!!!!!!! logic, not "e.g."

How do you match particular accounts with particular flows?

One line for each account??? Not using any BW properties?

[Former Member]

I select flow for any GL or GLs by using following IFRS kit flow table:

I already shared one sample line for each account and its flow.

Not using any BW properties.

[former_member186338]

imran.shafiq

I am asking you again and again!

Provide FULL (complete, containing all lines...) conversion file.

[Former Member]

vadim.kalinin

As you know Excel conversion file is not attached here, so I copy the Flow tab into text file and attached for your review.conversion-flow.txt

[former_member186338]

Sorry, but it's absolutely bad idea to use periods 00, 13, 14, 15, 16 in BPC standard!

Use normal periods 01...12 only.

For data in special periods use transformation and conversion to put this data to normal periods.

[Former Member]

Thanks for the correction, kindly guide me how can I use transformation & conversion to put periods 00,13,14,15,16 data into normal periods?

[Former Member]

If I refer all the special periods data into period 12 in conversion file then what to do period 00 data?

Kindly guide me.