EPM Add-in report with multiple Categories in SAP BPC, version for Microsoft and NetWeaver 10

EPM Add-in report with multiple Categories in SAP BPC, version for Microsoft and NetWeaver 10

With the new user interface in BPC 10, one might wonder how to create a report rendering two different Categories: Actual data for closed months and Plan data for the outlying months.  Follow the below instructions and you will impress your client in no time. 

First, we need to create a property informing the system which months are closed or what the beginning Plan month is.   One way is to create a property on the Category dimension named OPENING_MTH and the administrator updates this monthly.  One advantage of this is your Categories can have different beginning months.  Another was is to create a property on the Time dimension (CLOSED) with valid values of “Y” or blank.  Either way will work and you should base your decision on the businesses requirements.  For this blog, we focus on the later and create a property on the Time dimension. 

Create CLOSED Property on the Time Dimension

Login to BPC 10 Portal and click Planning and Consolidation Administration.

When the Administration tab opens, click Dimensions.

Highlight the Time Dimension and click Edit Structure (at the top).  Be careful to not double-click on the Time dimension as that will take you to the Time dimension members. 

Once you are in the Structure of the Time dimension, click Add and then select New Property.

Type in CLOSED for ID and Name will default with CLOSED.  You can change the Name to something else that is more informative like Closed Actual Months or Actual Months.  Enter 1 (one) for the Number of Characters as this will be either a “Y” or blank.  Click OK to when finished.

Save the dimension by clicking Save.

Click Close when the dimension has finished saving.

Either double-click on the Time dimension to open it or single click and choose Edit Members.

Find the newly created CLOSED property when the Time dimension opens.

Enter a “Y” in all months, quarters, and Years that are closed.  In the below, I have enter a Y for month January, February, March, April, May and Quarter 1 for 2010.

Save and Process the dimension.

When the splash screen asking to take the environment offline, click No.

When the Time dimension has finished process, click Close.

Now it is time to start configuring the actual report.  To do this, click on the EPM Office Add-in for Excel.

Choose the correct model (in BPC 7.x, this was an application) and click OK.

At the prompt, supply a valid user ID and password and click Logon.

Since we are going to be creating formulas above the report, place your cursor in cell G7 and click New Report under the EPM ribbon.

Drag the Account dimension to the Row axis and the Time and Category dimensions to the Column axis.

Click the Category dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose Actual from the Dimension Members pane.  Ensure Member Only is selected for the expansion (at the bottom), and then click the right arrow.  Click OK when done.

Click the Time dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose a single month and select Member Only in the Selection Relationship dropdown (bottom).  Next, click the right arrow to bring the selection into the Selected Members pane.  When finished, click OK.

Click on the Account dimension and choose accounts that you have Actual data for.  In this example, I chose Context (Net Income) and to show the Member and Children. 

Now that the report layout is complete, click OK.

The report should look like below, assuming you used the same dimension members.

In cell F1, type in the formula =EPMContextMember(,”Time”).  Since there is only one active connection, the first parameter can be left blank.  The function EPMContextMember is the new version of EVCVW from BPC 7.x.  Click OK when finished to save the formula.

In cell G1, type in the formula =LEFT(F1,4)&”.01”.  In my environment, the months are represented by numbers (01, 02, 03, etc), but if you can just as easily have .JAN, .FEB, .MAR, etc.  This will be taking the current year in cell F1 and adding a suffix of .01.  Basically, our report will always start with the first month of the year, in this case January.

In cell G2, type in the formula =EPMMemberProperty(,G1,”CLOSED”) where G1 is the first month of the year and CLOSED is the property you defined in the Time dimension.  EPMMemberProperty is the new version of EVPRO.  Again, since we are only using one connection, the first parameter can be blank.

In cell G3, type in the following formula: =IF(G2=”Y”,”Actual”,”Plan”).

In cell H1, type in the formula =EPMMemberOffset(,G1,1).  EPMMemberOffset() is the new version of EVTIM().

Next, copy the contents in cells G2 and G3 to H2 and H3, respectively.

Copy the contents in cells H1, H2, and H3 out 10 (ten) columns to column R.

Next, we need to manufacture the time dimension.  In order to do this, click in cell G5 and enter EPMMemberDesc(G1) for the first parameter.  Click OK when done.

We are going to do the same thing for the Category dimension.  In cell G6, type in EPMMemberDesc(G3) to display the correct Category Member.

Technorati Tags:

Now, copy cells G5 and G6 out to column R.

Click the Refresh Report and two different Category dimensional data will be rendered in the same report.

Bonus:

Now that you have created this, it takes one formula to create a Rolling 12 report.  In cell G1, enter the following formula, change the Time Current Context to 2010.Q2, and click Refresh:

=IF(EPMMemberProperty(,F1,"Level")="Year",LEFT(F1,4)&".01",IF(RIGHT(F1,2)="Q1",LEFT(F1,4)&".01",IF(RIGHT(F1,2)="Q2",LEFT(F1,4)&".04",IF(RIGHT(F1,2)="Q3",LEFT(F1,4)&".07",IF(RIGHT(F1,2)="Q4",LEFT(F1,4)&".10",F1)))))

Robert Marshall   is a member of the BPC CSA team

sap bpc tutorials pdf, sap bpc tutorials, sap bpc tutorials download, sap bpc interview, questions, sap bpc interview questions pdf, sap bpc interview questions free download, sap bpc interview questions and answers

Ownership Manager System Generated Proposals from SAP Business Objects Planning and Consolidation 10.0, version for Netweaver

Similar in function to the Dynamic Hierarchy Editor available in BPC 7.x versions, the SAP Business Objects Planning and Consolidation 10.0, version for Netweaver (BPC10NW), Ownership Manager allows the definition of complex holding/subsidiary relationships that can be time, category, and scope specific.  The organizational structures defined by Ownership Manager are also used by the Consolidation Monitor and the Controls Monitor to display and status the defined organizational levels.

New to the Ownership Manager functionality is the ability for the BPC system to propose key consolidation parameters. 

There are three main prerequisites for activating system calculations for ownership manager:

1. Model and Master Data requirements

Ownership manager functionality requires a configured ownership manager model as defined by : 

Ownership-based Hierarchy Requirements . 

Summary requirements:

Required member ids in the O_ACCOUNT (ownership model account) dimension:

METHOD

METHOD_SYS is populated by the system to store the generated proposal for the consolidation method

PCON

PCON_SYS is populated by the system to store the generated proposal for the consolidation rate

POWN

POWN is also known as the ultimate percent ownership of an entity by the holding company. It is the calculated share percentage based upon a specified calculation method (Direct Share or Group Share).  For the Direct Share calculation, the percent ownership is assumed to be 100% of the subsidiary ownership.  For Group Share calculations, the percent ownership is the product between the various subsidiaries percentages. 

Example:

Holding Company (H1) owns 70% of subsidiary (S1) and S1 owns 50% of subsidiary S2.

Using the Direct Share method, the ultimate percent ownership of S2 is 50%.

Using the Group Share method, the ultimate percent ownership of S2 is 35% (=70%x50%).

directly or indirectly

PCTRL

PCTRL identifies the ultimate percent of control for each entity in a model. 

Member ids used internally during Ownership Manager Calculations

METHOD_SYS

METHOD_SYS is populated by the system to store the generated proposal for the consolidation method

PCON_SYS

PCON_SYS is populated by the system to store the generated proposal for the consolidation rate

POWN_SYS

POWN_SYS is populated by the system to store the generated proposal for the financial interest rate, or percent shares owned. Shares Owned contains the number of shares and voting shares that an entity owns of another entity.

PCTRL_SYS

PCTRL_SYS is populated by the system to store the generated proposal for the percent of subsidiary control

Required O_ACCOUNT Dimension Properties

The IS_INPUT property for the O_ACCOUNT dimension identifies the dimension members which can be displayed in the Ownership Manager hierarchy display.

2. A Defined Method Business Rule:

The Method Business Rule table supports the Ownership Manager calculations by identifying a relationship between a range of shares allowing a system derived consolidation method (METHOD) as well as a proposed Financial Interest Rate (PCON):

• indentifying source accounts
• Indentifying target accounts
• Indentifying source flows
• Indentifying target flows

Note the unique syntax for identifying the share ranges. 

0.20<,<=0.49 is interpreted by the system as a range definition:  “any value greater than 20%, through any value less than or equal to 49%”.

0.50<,<=1.00 is interpreted by the system as a range definition:  “any value greater than 50%, through any value less than or equal to 100%”.

3. Implementing a Cross Ownership Matrix

Automatic ownership calculations require additional information to allow the ultimate ownership share ownership values.  An input schedule providing details on Percent Control and Percent Ownership data must be provided by entity and intercompany partner.  Data is saved to the OWNERSHIP model.

In the following example:

- Germany controls 80% of a UK subsidiary, and the UK subsidiary controls 60% of a French subsidiary

- Germany owns a 70% share of a UK subsidiary, and the UK subsidiary owns a 50% share of a French subsidiary

Note: Share and control data must be entered without any scope (group) specification (i.e.: S_NONE). 

Once all prerequisites have been completed, the user can request system generated ownership manager calculation through the CALCULATE option available in the edit mode of the ownership definition:

User can select the accounting principle for calculating the ultimate ownership and control (Direct or Group share methods).

Checkbox options allow the user to update the “Current” sub column values for Method, Percent Consolidation, and calculated ownership percentages.

A graphical illustration of the data flow between the three prerequisites:

Impact of Maintaining Debug Users in BPC NW

BPC is a tool for planning and consolidation, which is available in two platforms one in Microsoft and other in Netweaver. All the objects created in BPC Netweaver platform are created as BI objects and saved in the backend BI system connected to it.

All the logics and functions defined in BPC NW frontend are developed using ABAP logics in the backend. Also we have option to write custom ABAP codes using standard BADI’s delivered with the tool for various business requirements. So for checking the malfunctioning of any function in BPC frontend we need to debug the corresponding ABAP logic of that function in backend. For which we need the option for debugging. The same is provided in the BPC NW system which has to be configured in the server end with the details for Domain User, BI user ID and its Password.

System Connection Issue:

The user details what we use in the front end to connect to the BPC system is domain/system specific, whereas different set of user details are used to connect to BI system in the back end. So when a domain specific user connects to the BPC system in front end, simultaneously a connection is established with the backend BI system.

To establish this connection with the backend BI system, it uses different BI user details depending on the debug user maintained in the server. Below flow chart clearly states the procedure for connection establishment between BPC and BI system.

So having said these one can connect to a BPC NW system only when,

1. Domain User ID and Password are correct
2. Backend BI user ID and Password are correct

The Domain user ID and Password are the one we would be providing from the front when connecting to the BPC system, but BI user ID and password are automatically taken by the system for establishing the connection with BI system. These values are taken either from the key user details or from debug user details depending on the user logging in and the debug user table.

Consider we have a domain user maintained with his corresponding BI user ID and password in the debug user screen of the server. When the user tries to connect to BPC, the BI credentials provided in the debug user screen of the server will be used for establishing connection with BI system. Having said this, if the details are not maintained correctly or the BI password is locked then the user will not be able to connect to the system.

Authorization Issue:

When we don’t have any debug user maintained in the system, we are connected using a Key BI user ID which will have all access to the system and so from the frontend we will not face any problem in performing any operations. But if a debug user is maintained for a domain user and if the BI user used has a limited access in the BI system we may have problems in performing certain tasks from BPC end.

Consider a domain user which is maintained in the debug user screen with BI user ID and password. The BI user provided in the debug user screen has access only to certain functions like create BI objects and to some other transactions used for programming but do not have access to the transactions to save files in the SAP system. When this particular domain user logins to the BPC system and tries creating any BPC object he will not have any problem, as he have access to create any object.

But when he tries to run some Data Manager Package for loading a master data from a flat file which is in the BI server, since the BI user provided in the debug user screen doesn’t have authorization to access the file the data manager will fail stating “No Authorization for Accessing the file” in the error log.

So when we maintain debug user screen in the server we need to make sure of the following,

1. The BI credentials provided are correct
2. The Password for BI user provided is not locked
3. BI user provided for debug user has all access which will be required when working from the front end BPC system.

If we miss the first and second option, the user will not be allowed to login to the system. If we miss the third option, it is not sure that user can perform all the actions from BPC end.

To prevent all these problems it is better to make sure that the Debug user details are maintained as and when it is required for debugging and removed when we don’t have the work with debugging.

A Taste of What’s Coming in BPC 10.0

Today, the 2011 ASUG Annual Conference (and SapphireNOW) began with a series of pre-conference education sessions.  I was fortunate enough to attend the Enterprise Performance Management Version 10.0 Roadmap session led by a number of product experts within SAP’s EPM leadership team.  The goal of the session was to provide an overview of the changes that were coming in the to-be-launched-this-week EPM 10.0 as well as the vision for the suite of products beyond this pending release.

I was most interested in the SAP BusinessObjects Business Planning and Consolidation (“BPC”) portions of the session, since that’s the tool that I work with the most within the EPM product category.  In today’s session, I learned that SAP will be delivering a number of enhancements in the 10.0 release and I’ll do my best to try to outline the bigger changes below and my thoughts on those changes.

BPC Netweaver

New look and feel – One of the themes that SAP has been touting in the EPM 10.0 release is ‘Unified’, and this refers to making each of the EPM products have a similar user-friendly interface so that the user doesn’t know whether they’re in a BPC report or a Strategy Management scorecard (for example).  This new user interface seems very visually appealing based on the screenshots and demos provided.

Integrated security with Netweaver back-end – One of the complaints about BPC security in previous 7.x releases was that the security was not integrated with the underlying BW system.  SAP appears to have addressed this issue and provides a new administration area that will give system administrators a user friendly tool that acts as a front-end to PFCG or SU01 transaction code.

Enhanced hierarchy maintenance – While maintaining hierarchies were do-able by maintaining the ParentHx property in the dimension sheet, new functionality was developed that will allow for drag and drop hierarchy builds within BPC.

Enhanced script logic editor – Let’s face it, writing script logic can be less than straight-forward (and that’s putting it nicely), but with the new functionality planned for this enhancement, it will become a little easier.  With keywords and MDX statements now at a script-writer’s finger tips, searching online for the exact nomenclature will hopefully be less frequent.

No more .Net tier – As a business guy, I’m not sure that I understand exactly what this involves, but a more simplified and streamlined backend likely makes things easier.

Enhanced core consolidation functionality – New features such as Consolidation Central will give administrators a single place for configuration.  Better data validations (with thresholds that will allow consolidation process to continue or if breached, to stop) and a Consolidation Monitor (ala SEM-BCS) are added features in the upcoming release.

Ownership Manager – the Dynamic Hierarchy Editor is now called the Ownership Manager.  This web-based tool allows for auto calculation of ownership percentages based upon a user input of the Parent/Child relationships.  Based on the description, this reminds me a little of the SEM-BCS Group Shares report.

Web-based Journals – Journals will now be entered via a new web-based front-end, with what seemed to be a user-defined layout.  Enhanced journal reporting using a query manager will also allow users to better investigate the entries that are being posted in the system.

Future HANA Enablement – Beginning later in 2011, BPC will be enabled to run on HANA, first with replication of data using the Sybase Replication Server.  Then, in mid-2012, look for BPC to run fully on the HANA technology.

BPC Microsoft

Further demonstrating what SAP has said it would do, some of the Netweaver enhancements mentioned above will be available in the Microsoft platform, but not all.  Specific notable enhancements called out for the Microsoft platform were performance enhancements brought on by fully leveraging the 64-bit architecture and a new Advanced Variance Analysis tool that will help drive variance reporting.  The AVA will only be available in the Microsoft version of BPC, at least for the initial roll-out of 10.0

Conclusion

It was a really good Roadmap session with a lot of excellent and knowledgeable speakers.  I hate to short-change the speakers for the non-BPC EPM tools, but that’s what my personal focus is on these days.

I look forward to an exciting week at ASUG Annual Conference and SapphireNOW as there will be plenty of EPM sessions (more than any one person can actually attend by him/herself).

Don’t miss the official launch of EPM 10.0 on Tuesday, May 17 at 11:00am ET.

I’m quite certain that I missed out on some other enhancements that are coming in 10.0, especially on the Microsoft side, so please don’t think that’s all that’s coming.

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books

Blogger Labels: Taste,ASUG,Annual,Conference,series,education,Enterprise,Performance,Management,Version,Roadmap,session,product,leadership,team,goal,overview,vision,suite,products,BusinessObjects,Consolidation,tool,category,Netweaver,user,interface,Strategy,example,system,administration,area,PFCG,transaction,code,hierarchy,maintenance,ParentHx,dimension,sheet,script,logic,enhancement,writer,tips,nomenclature,tier,features,Central,configuration,Better,data,Monitor,Ownership,Manager,Dynamic,Editor,auto,calculation,Parent,Child,relationships,description,Group,journal,Future,HANA,Enablement,replication,Sybase,Server,technology,Microsoft,Further,platform,Specific,architecture,Variance,Analysis,Conclusion,tools,person,Interview,sessions,portions,enhancements,themes,demos,complaints,administrators,statements,thresholds,percentages,Journals,users,speakers,Tutorials,week,easier

SAP – BPC: Implementation Lesson Learnt Part – 1.

SAP – BPC: Implementation Lesson Learnt Part – 1.

    SAP BPC going through a sea of ideological changes. Firstly the tool was built with a different perspective and development standard. From its birth ‘Outlooksoft' was positioned more towards from a business centric view. Gradually it created a niche for itself but one fine day SAP has acquired it and positioned it as the ‘tool to go' for Financial Consolidation & Planning.

   The classical SAP folks, who were more used to the old ECCS, SEM-BPS, BW-BPS, BI-IP, SEM-BCS tools will find it radically different from its onset. The service oriented architecture model in comparison to the ‘technically robust, training intensive' is bound to create a gear shift in the world of SAP-Consolidation & Planning.

   Not only this, as SAP trying to make ‘Outlooksoft/BPC' more SAPiezed, by creating two separate parallel version of BPC (we know it an ‘N' for NetWeaver & ‘M' for Microsoft) - it is important to understand the consulting partner's look ahead to serve the client thoughtfully.

   In this series of article, I am going to walk you through the BPC - Implementation best practices from an architect point of view.  In the part 1, we will cover the general discussion and in future parts we will deep dive into Consolidation, Planning etc.

Point # 1: Software positioning & strategizing:

  SAP - BPC is based on 2 key points.

• a. Service oriented architecture - Business Driven. ‘Flexibility'.
• b. Microsoft technology based platform.

    The way I see BPC as a tool is a ‘close knit controlled combinations of several excel objects connected by a well defined SQL/MDX based business logic'.  The first time, I looked at BPC, I was looking for all those technical robustness SAP BI has or the pre-defined Business rule SEM-BPC has. In fact, when I realized, there are no standard BAPIs, extractors (remember 0FIGL_10), no STS (transport system) - I was quite disheartened. But if you see the tool from a business user's angle, you will be amazed. The toll is so flexible.

    Always remember, the best thing, the best saleable feature BPC posses, is its ‘Flexibility'. It might not have all the other technical advancement like other SAP tools, but the amount of flexibility, and comfortably it provides to a native Finance user is unmatched. Never compromise on this.

    In a SAP based environment, the only options you have right now (Jan'09) to install BPC is its version 5.1. The BPC 7.0 version is still at its ramp-up stage. There are some projects which are the ramp-up customer for this right now but generally 5.1 is the version you should concentrate. The SAP BPC 5.1 is a Microsoft based tool, it uses ‘SQL server' as its basis. Remember, mostly in the SAP BPC environment you need to do some work on SQL and not everything will be readily available. So in your implementation team you got' a have resource(s) who can work on -

                SQL server Management Studio (equivalent to RSA1) à priority 1

                SQL server Integration Services (Data Flow controller - Remember BI process chain) à priority 2

                SQL server Analysis Services (OLAP world) à low priority

                Internet Information Services (Web application server) à low priority

    If you planning to use the new version of SAP BPC (7.0), you have 2 options - ‘N' & ‘M'. Now before you choose the route for your implementation(s) - hold back and understand -

                Pro's & Con's of the two BPC versions.

                Which one fits your companies need?

    All the SAP savvy technical group will jump into the BPC ‘N' versions, but consider the facts where this is pretty new integration model. Also the backend is getting integrated to SAP-BI model. This is good from the point of view of the SAP CC team. But one of the premier salability of BPC as a tool is/was ‘less dependency on IT'. With this, ‘N' version this might be severely compromised. Does your company really needs that? In future, I will discuss more on this topic about when to choose which version.

  Lastly consider you reporting tool. You can use BPC's reporting feature fully and can house 100% of your report there. You also have an option of using your old reporting tool or SAP reporting tool (BI). Whatever the option you choose, make sure there is a clean & thoughtful segregation report housing strategy.  When you have some report coming from BPC & some coming from outside BPC, make sure you have decided about the reconciliation, migration & sequencing strategy clearly defined. I have seen multiple occurrences when there is not so clear reporting disposition strategy between BPC and non-BPC reporting tool.