Consoldiation Account Data Loads

[tim_atkins]

Hi all,

In loading Account members from ERP into BW for BPC, we need to populate certain properties for the consolidation to work (ACCTYPE, RATETYPE, DIMLIST etc.).

The 0ACCOUNT object from ERP will only have on it a balance sheet indicator which is not enough detail to derive the properties. We need to be using the full ERP Chart of Accounts and use ERP Financial statement version for the hierarchies. It is impractical to manually maintain the properties in BPC for each account and account hierarchy nodes.

What is the best approach in loading the properties? We can leverage the Account Range groups from ERP and map those in a DSO in BW. That could be in turn used in a transformation up update the account properties. However that approach will no address the account nodes needing an AccType.

I was hoping there is a best practice / approach for this.

Many thanks

[tim_atkins · Accepted solution]

Just an update,

We are using the SKA1 extraction as a basis for the account ranges. This populates a custom characteristic which has the acctype / rate type properties which can be manually updated in BW. There are only about 40-50 ranges, so its not too troublesome. We will also use it for exception to the range rules.

I think this is probably the most workable solution for the attributes, and we can extended it for other attributes if needed (i.e. DIMLIST TYPEELIM etc.)

Tim

[damovand]

Hi,

I recently created a number of InfoObjects for a consolidation model in BPC embedded and I did it all  manually.  I created all the attributes, e.g. ACCTYPE, RATETYPE and so on.  Then created all dimensions in RSD1.  Then I used BPC web client, believe it or not BPC web client Administration works for this, and I populated the dimensions.  It is cumbersome but I am a BPC person, so I don't have that much experience loading data from ERP.  

Regards,

Leila

[Former Member]

Hi Tim,

If you have so many transformations to be done for data coming from ERP...then my suggestion for you is to create a staging cube in BW same structured(same info objects) like BPC cube.... Load the data both master and Transaction from ERP --->BW cube(standard)----->Staging cube in BW(for BPC) ----> then BPC.

Do all the mappings conversions transformations on BW side....because BW ETL is robust. In the end any complex transformations needs BW ETL processes like routines BADI's etc!

This approach would be most convenient!

For Acctype, if there are serial nos used to recognised the Chart of accounts then you can put a logic like Acct nos starting with 1XXXXXX are AST, with 2XXXXXX are LEQ etc.....If not then ask the business for any distinguisher in system.

For Ratetype....it would depend on the business case actually....but normally P&L accounts are AVG and Balance Sheet are END/CLO. You can used the Balance sheet flag for this or ACCTYPE.

Other properties that are needed to run business rules, need to be maintained manually only I guess!

Hope this helps....

Regards,

JP

[tim_atkins]

Thanks JP,

We will probably use a DSO mapping account ranges to the right attributes. We could then handle exceptions after the initial load.

Using that approach we an map sets of accounts to various members without too much hassle.

The main challenge is the BPC 10.1 front end is very cumbersome to use. You can't easily drag entries down or even copy and paste. So with potential 2000-4000 accounts, it becomes quite impractical to do this manually.

At least with a DSO we have more control of how the mapping gets updated. Rate type should follow acctype, except of equity accounts.

Regards,

Tim

[Former Member]

Hey Tim........If you have any more queries please let us know....or else please close the thread...

Regards,

JP

[Former Member]

P.S If you have around 2000-4000 accounts to be handled.....for maintaining other properties I would suggested you to maintain the members in Excel. After loading from ERP download them into an csv or Excel file. So here you can easily maintain in Excel, copy paste drag drop etc.......then use this file to either copy paste in the BPC web or use the Data manger package "Import using flat file" to upload it...I guess this would be a one time activity before setting up the BPC to rollup!

Hope this helps...

JP

[tim_atkins]

Thanks JP,

I'll close it next week if no one else adds anything. Sorry I didn't add in my question was we are using BPC 10.1 Embedded consolidation. So we don't have data manager to upload files for master data.

I am trying to find the best / easiest way to maintain this in the future as the BPC super users will need to do this.

Tim

[Former Member]

Maybe you can load that flat file in the info object used for Accounts?... But I guess that will be a tedious activity for the users....will need to train them in doing that. Or can consult the BW consultant in automating that process... Its just a suggestion! I havent worked on Embedded version so....

JP