Basic Questions about SAP BPC (SAP BPC Faqs)

1. What is BPC ?

As you must be aware that BPC stands for Business Planning and Consolidation. As the name suggest, BPC is used for both Planning and Consolidations, unlike IP, which is used for only planning.

2. When BPC 7.5 NW is avaliable?

The 7.5 version is currently in the ramp-up stage and should be availble in few months. I am not sure of the exact month.

3. BPC for NW has an aggregation level or not, Is BPC an application on top SAP-IP?

BPC and IP have completely different architecture. BPC doesnt use aggregation levels or filters. BPC and IP have no relation and thus, cannot be compared. BPC uses various script logics, business rules for doing the planning. Manual planning can be done with input schedules. The input schedules are on excel platform and are quite versatile. You can use BPC functionalities alongwith native excel functionalities for better design of the input schedules.

4. What about BPC in consolidation part?

BPC comes with pre-delivered components for consolidationm, which can be leveraged on. There are few business rules, which makes the consolidation process easier.

5. What is the concept for plan data from BPC?

The concept of planning is the same as you have in any other tool. The functional side of the planning remains the same. However, the tool differs. The way you configure a planning scenario will be much different than any other planning tool. Unfortunately, it will not be feasible to address this in the forum. You can definitely go through the study materials for a better understanding.

6. BPC for NW need to install .NET application server and Web server or not?

Yes. You can have a look at the installation guide available on market place for detailed requirements.

7. What is BPC client tool? and If I planning on BEx report, I have to create an aggregation level for planning data or not?

BPC client sits on the client machines, from where, you will be doing the design. There are mainly 3 interfaces available - BPC Admin, BPC Office, and BPC Web. BPC Office, in turn has 3 interfaces - Excel, Word, Powerpoint. These interfaces can be used for various requirements.

8. How about BPC license?

You can get in touch with SAP for the license relation information.

Welcome to the central information center for SAP Enterprise Performance Management (formerly CPM) offerings

It has information about following products:
SAP Strategy Management (SSM)
SAP Business Planning and Consolidation (BPC)
Profitability Management and Cost Management (PCM)
Information on Spend Performance Management can be found here (link )
The purpose of this EPM wiki site is to provide a living communication platform between SAP EPM development, Regional Implementation Group, Consulting, SAP implementation partners and EPM customers. Ultimately, we would like to ensure knowledge transfer and customer success through this wiki platform together with  BPX and other resources.
We absolutely need your active participation and contribution to make this site as informative and lively as possible.
Enjoy! 

Welcome to the central information center for SAP Enterprise Performance Management (formerly CPM) offerings

It has information about following products:
SAP Strategy Management (SSM)
SAP Business Planning and Consolidation (BPC)
Profitability Management and Cost Management (PCM)
Information on Spend Performance Management can be found here (link )
The purpose of this EPM wiki site is to provide a living communication platform between SAP EPM development, Regional Implementation Group, Consulting, SAP implementation partners and EPM customers. Ultimately, we would like to ensure knowledge transfer and customer success through this wiki platform together with  BPX and other resources.
We absolutely need your active participation and contribution to make this site as informative and lively as possible.
Enjoy! 

BPC Comments via Excel Pop Up - How to and Comparison with other modes of commenting

As we all know, one of the most admired and user friendly feature of BPC is its Excel Interface. Business users are comfortable with flexibility and features available with excel to work on their different business plans. However they miss the secured and integrated planning environment. BPC is a tool which provides best features of Excel in an integrated planning tool with enhanced planning features like trends, spread, drilldown, commenting etc.

Commenting is a functionality which is used for sharing information among a set of people who work together in any system. And this function plays a vital role in any kind of planning scenario by bridging the communication gap between the people in different level of any organization.

Business users in general add comments for various needs like, to share their view points on the plan data, to specify any reasons for data fluctuations etc.

Posting any Comment in BPC is possible via three interfaces,

1. Via Action Pane
2. Cells of Excel – EVCOM
3. Excel Pop Up – EVCOM

The procedure for adding comments from Action Pane and via cells of excel sheet using EVCOM function is very well illustrated in the How to Guide which is available in the link below,

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/508b62ee-000b-2c10-2d91-a867451ad958?QuickLink=index&overridelayout=true

This blog details how to configure comment by Excel Pop Up and its advantage over other modes of commenting.

Steps involved,

1. Create any Input Schedule or Report in BPC Excel Client.

2. Set the value for Workbook Options à Read Option for Comment  to “As Excel Popup”(As below)

3. Set EVCOM function for various fields in the report and refresh the sheet.

      a. Select the cell where the function is to be written.

      b. Fill it with “=evcom()”

      c. Select insert function and define the cell value for different parameters of the function.

4. Select the cell for which the comment needs to be added then go to Review Tab and select  New comment,

5. Enter the required comment for the cell selected in the pop up screen and move back to Add Ins Tab,

6. Click on the button to save comment to the system,

7. The comment is saved successfully to the system and you have the comment in the Popup Screen as and when you move the mouse over the cell.

Advantages of having comment as Excel Popup are,

1. Business have the feel of using the same Excel functionality for adding comments with data saved in system
2. Can be used to comment any data cell in a Report or Input Sheet having dynamic expansion.
3. No need for allocating any area for commenting in the IS/Reports.
4. User Interface looks good having only the required set of data without explicitly displaying the comment entered for it.

Note: The Comments included via Excel Pop Up can also be viewed using EVCOM function in cells of different BPC reports or Input Sheet and can also be reported via standard system reports for Comment.

It is in general depends on the business user who demands for the interface of commenting, being a consultant it is always good to propose the best option available based on the business need.

The Table below clearly states what type of comment can be used in different business scenarios,

Business Need

Best Option

1. Report or Input sheet dynamic
2. Huge volume of data and commenting across any individual cell of the data

Excel Pop Up

1. Report or Input sheet is static
2. All Relevant comments to be displayed simultaneously.
3. Sheet where comments are entered without any data.

Within Cell

1. For any business user who maintains only comments and doesn’t need any access to see any plan or consolidation data.

Action Pane(Provide Access only to Commenting in Task Profile of that business user)

Thus comments can be added using Excel Popup function and the business users can be provided with the feel of their excel functionality for commenting.

Why the new script logic keyword - *WHEN_REF_DATA?

At times in SAP Business Planning and Consolidation, when writing a *WHEN-*REC-*ENDWHEN structure in script logic, we may come across a scenario where we want to post value to an intersection/region where there is no data. This was not supported previously as calculations happen in transaction mode by default.

The above is not a limitation anymore in SAP BPC NW with the new script logic keyword -*WHEN_REF_DATA. This allows us to switch our calculation mode to either transaction data or master data. 

Syntax

1)      To enable master data mode

*WHEN_REF_DATA = MASTER_DATA    

//*WHEN-*REC-*ENDWHEN CONSTRUCT

2)      To enable transaction data mode

*WHEN_REF_DATA = TRANS_DATA    

//*WHEN-*REC-*ENDWHEN CONSTRUCT

• In transaction data mode, calculations will happen based on the transaction data in the source data region
• In master data mode, calculations will happen based on the Cartesian product of the source data region no matter if the transaction data exists or not
• The mode is set to transaction by default. So when we do not use this keyword in our logic, all the calculations in *WHEN construct will happen in transaction mode
• When we use the *WHEN_REF_DATA statement, all the calculations in following *WHEN  constructs will happen in the mode specified until the next *WHEN_REF_DAT (if specified)

Example

*XDIM_MEMBERSET TIME=2010.MAR

*WHEN ACCOUNT

*IS  ACC1

*REC(EXPRESSION=1000, ACCOUNT=ACC2)

*ENDWHEN

Here we have no *WHEN_REF_DATA and thus calculations will happen in the default mode i.e. transaction data mode. A value of 1000 will be written to ACC2 wherever there is a value for ACC1 in the period 2010.MAR.

Now, let us look at the same example in master data mode.

*XDIM_MEMBERSET TIME=2010.MAR

*WHEN_REF_DATA = MASTER_DATA   

*WHEN ACCOUNT

*IS  ACC1

*REC(EXPRESSION=1000, ACCOUNT=ACC2)

*ENDWHEN

In this case, 1000 will be written to ACC2 even when there is no data for ACC1 in the period 2010.MAR.

Please check the Note 1474115 for further details.

EPM RIG BPC 7.5 NW Elite Partner Training

From Monday, Jan 24 until Friday, Feb 4, 2011 the EPM RIG welcomes 48 partner employees in Palo Alto to go through a BPC 7.5 NW Elite Partner Training hosted in the Amazon Cloud.

The training covers

• Hands-on walk thru of the BPC 7.5 NW key features (such as Xcelsius integration, automated BW data loads, new Business Process Flows)
• BW Concepts and data integration hands-on
• EPM Roadmap and HANA outlook
• An overview of SAP ERP Data in the context of BPC
• EVDRE Best Practices
• Transports and Lifecycle Management
• EPM Resources and Escalation Process
• Performance Tuning
• Consolidations (Logic Setup, Dynamic Hierarchy Editor, Consolidations rules and methods)
• ABAP and Technical Programs
• BPC Data Manager
• Project Planning and Risk Management
• Script Logic
• Final Exam

There are already plans to repeat this training in Shanghai and discussions around sessions in Europe and a repeat in the US. So, feel free to share your thoughts and feedback in this blog.

Also, with the integration between BPC and PCM becoming a hot topic in 2011 (see How to Generate Product and Customer Data Views using Planning and Consolidation, Profitability and Cost Management, and Financial Information Management for technical integration details) we are investigating the idea of a PCM Elite Partner Training later in 2011.

So, blog away and share your thoughts... If you want to follow us on Twitter look for hashtag #BPCElite

Also don't forget to check out the EPM RIG how-to guides about all EPM products and visit the RIG blog page at SAP BusinessObjects Planning and Consolidation Blogs.

Use BPC Work Status to Enhance Your Budgeting and Planning Process

When it comes time to lock down your budget, do you have the ability to make sure that the data doesn’t change after your company’s executives have reviewed and signed-off on the results?  Or maybe you want to allow certain users the ability to go into the system and put the finishing touches on the updated forecast, but don’t want unauthorized changes to be made?  These are scenarios well-suited for the Work Status functionality that comes delivered with SAP BusinessObjects Planning and Consolidation (“BPC”).

Background

Prior to using BPC to build our budgets and forecasts, we used SEM-BPS essentially as a repository for our budget and plan data that was really being determined using Excel models.  When it came time in the process to lock down the data, we had no real feasible way of doing this.  We developed a custom BW transaction where we could allow only certain people into BPS to make changes to the data being held in the repository, but there was nothing preventing people from changing their Excel models (other than the fact that they would no longer agree to the data in the system).  Furthermore, we were unable to limit the entities (for us, Profit Centers) that a user could change – if they were able to get into the system, they were able to change figures for any entity that they had access to.  Upon implementing BPC to replace BPS, the requirement to be able to allow only certain users in the system at certain points in the process was still relevant, but I’ll get back to that in a little bit.

Our monthly forecast process calls for users to make changes to our Working Projection category (which is also known as a version) beginning on/about the 15^th of the month.  In general, users have until about the 25^th of the month to make changes, at which point in time the Working Projection category is locked down and the business units are required to sign-off on their updated forecast.  With many people involved in the process—almost all of whom had duties outside the forecast process—it seemed as though most of the changes would come through on the last few days before the category was locked down.  This often frustrated our business unit leads who were responsible for reviewing the forecast reports for accuracy – with data changing up to and including the day of sign-off, their job was next to impossible.

Making It Happen

Using BPC’s Work Status functionality, not only were we able to meet the business requirement of being able to lock down the category, we were able to better the forecast process to allow for an intermediate step of locking out everyone but the business unit leads.  In addition, we’re also now able to allow access to only certain entities so that we can maintain tighter control during times in our process when data integrity is at the utmost importance, yet a few select changes need to be made.

I’m not going to go into great detail about how to set up Work Status in BPC (there’s a good SCN Article for that), but rather just some details of how we’ve modified the system delivered setup to reap the benefits.

Out of the box, BPC comes with three Work States – Unlocked, Submitted and Approved.  The thought behind these delivered Work States are that in the Unlocked state any user with access can make changes to the data (and when I say data, I mean financial records, comments stored, journal entries, Data Manager packages and documents); while in the Submitted status, you can limit the users that have access to make changes, but in the Approved status, no user can make changes.

We didn’t particularly care for the titles of those Work States (they didn’t really make sense to our users), plus our business requirements called for an additional Work State, so we needed to make some modifications.  Making these changes is easy in BPC – simply go to the Admin Console and click on Work Status on the menu screen on the left hand side:

On the right hand side appears the list of tasks that you can perform for the Work Status feature:

We simply added a new Work State, edited the descriptions and reordered them as necessary and our resulting Work States were set:

For us, we needed four Work States in order to allow everyone access including our property managers (Unlocked), only allow access to our accounting staff (Analyst), only allow access to our business unit leads (SBU Admin) and to lock everyone out to keep them from making changes (Locked).

Now that we’d modified the available Work States to our liking, it was time to set the Work Status Settings for our BPC Applications.  The Work Status Settings tell the system that you actually want to be able to use the Work Status feature for your given BPC applications – setting up the Work State is not enough.  When configuring Work Status Settings for a given Application, you must think through the process(es) that are using that Application and at what level it makes sense to maintain the ability to lock users out.  For our processes, the combination of Category, Entity and Time was plenty enough detail to lock out users, so we set it up this way, using our Finance Application as an example:

Why was setting it up this way enough for us?  Setting it up this way means that we can open and close any combination of Category/Entity/Time period so this means that we could lock down (or open up, depending on points in the process) the Working Projection for a single profit center (or multiple profit centers) for a certain time period.  For us, it wasn’t important to be able to lock down at the GL Account level (or any other of our Dimensions) as this would be way too much maintenance and not worth it.

Changes in Process

With BPC and the Work Status settings in place now, our monthly forecast process now goes something like this: on/about the 15^th of the month the Work Status of the Working Projection category for all of our entities and all time periods is set to Unlocked so that any user with access can make changes.  On/about the 20^th of the month, our business unit leads begin to change the Work Status for their business unit (entities) to SBU Admin, effectively keeping everyone else in their business unit from making changes while they begin to review their reports. In this SBU Admin Work State, the business unit leads can still make necessary changes themselves, but they have much tighter control over the data.  On/about the 25^th of the month, we change the Work State to Locked so that no user can make changes and our corporate reporting staff can begin to run their consolidated forecast reports.  

Utilizing Work Status has allowed us to improve data quality (by allowing our business unit leads to review their reports earlier in the process with the confidence that no changes will be made that they haven’t made themselves).  Because the business leads previously couldn’t get static reports until BPS was locked for all users, we’ve also become more timely by cutting down on time spent after the actual deadline making changes.

So, you probably noticed that we went from Unlocked right to SBU Admin as part of our forecast process and skipped over the Analyst Work State.  That’s because we really only use the Analyst Work State for our annual budgeting process.  Our annual budgeting process requires that our property managers (those on site at our malls, office buildings, apartment complexes, etc.) start off the process by budgeting revenues and certain expenses associated with operating the properties.  The property managers have access only in the Unlocked Work State.  Once they’re done with their piece of the budget, we change the Work State to Analyst, which allows all of our accounting staff to formulate the rest of the budget for the property.  Ultimately, we use the SBU Admin and Locked state in a similar way for our budget process.

Setting Work Status

The actual task of setting Work Status is traditionally done through the BPC Web interface (you can see more details in the article I referenced above), but we’ve found that interface to not effectively support our process.  For certain entities we’re forecasting out 5 years each month and for others it’s 2 years, so maintaining Work Status becomes very cumbersome when changing only one intersection of data at a time.  For example, for our Residential business unit to move from the Unlocked Work State to SBU Admin, they have to use the web interface 5 separate times:

Category/Entity/Time
Working Projection/Residential SBU/2011.Total
Working Projection/Residential SBU/2012.Total
Working Projection/Residential SBU/2013.Total
Working Projection/Residential SBU/2014.Total
Working Projection/Residential SBU/2015.Total

Each of our business units must follow the same steps.  Only five instances are necessary when moving the entire company (All Entities) to Locked status since everyone moves to that status at one time, but if a change needs to be made to the data for a single entity, we must go through several iterations to ensure that the change can be made: first, change all five years of the All Entities dimension member (and only that member, none of its children) to SBU Admin status. Next, set Residential SBU (and only that member, none of its children) to SBU Admin status.  Lastly, set the specified entity to SBU Admin Status.  As you can see, that’s 15 executions of the web interface screen just to make a change to one entity.

Luckily for us, we’ve got some savvy Microsoft SQL developers on our team that have built a handy web interface for us to maintain Work Status more easily.  This is possible because Work Status is really driven by entries into certain BPC tables in the backend database.

For those who are interested in an easier way to maintain Work Status, check out a blog by James Lim on maintaining Work Status through the use of Data Manager Packages here.  Our custom built web interface was developed before James’ blog was available, but our developers tell me that our interface is essentially doing the same things in the backend as what’s going on in the packages James describes how to build.

Reporting on Work Status

There are a couple different ways that Work Status can be reported.  I personally prefer the EVLCK function that’s available in BPC’s Excel client.  The EVLCK function allows a user to display the Work State for a given intersection of Work State Dimensions.  For example, in a single cell in Excel, we would type =EVLCK(“Finance”,0,”Residential”,”2010.Total,”Working Projection”) and Excel displays the current Work State for the intersection of Residential/2010.Total/Working Projection.  The first two qualifiers refer to the Application (“Finance”) and the Parameter (0=Status, 1=User who changed the Work State and 2=Time when the Work State was changed).

We’ve incorporated an EVLCK formula in the header section of all of our BPC Input Templates so that users know whether or not they’ll be allowed to make changes to the data before they even interact with the template.  That helps to prevent them from the frustration of making changes that they expect to save successfully, but getting rejected when they actually click the ‘Send’ button.

Using the EVLCK function, you can build a Work Status report/dashboard to display the Work State for multiple intersections at once – a snippet of ours looks like this:

The other alternative for reporting on Work Status is through the BPC Web interface, which allows you to view activity of the Work Status tables to gain insight into which users changed the Work State, what they changed it to, and when the change was made.  This, to me, is a little less useful and the web interface is a little restrictive, but you may find a need for it in your implementation.

Conclusion

Work Status is a very useful and powerful tool when it comes to maintaining integrity of your forecast and budget data.  Yes, it can be a bit of a pain to maintain, but with some creativeness you can work around those issues and unlock just one of the many ways that BPC can help you to improve your budgeting and planning processes.

Master Data Volume and its impacts on BPC for NetWeaver

Master Data Volume and its Impacts on BPC for NetWeaver

Overview

BPC for NetWeaver does not have any pre-defined limits on the amount of master data that can be stored in any one dimension.  The amount of master data included in a dimension impacts BPC performance in the following ways:

• Client Login Performance
  • Dimension member master data cache files are created for clients on the BW tier the first time a client logs in from a specific system and on subsequent logons if there are changes to master data.
  • These cache files must be loaded into memory on the client tier (BPC for Excel, BPC Administration, etc).

In both cases, the amount of master data is one of the primary factors dictating performance.

• Administration Operations
  • Processing dimensions will take progressively longer as more master data is created
  • Selecting members when configuring Member Access Profiles and Business Process Flows is impacted by volume of master data

• Client Report Creation Operation
  • Some operations, such as creating an EvDRE report may cause master data to be read from the cache files.  In these cases the amount of master data has a direct impact on client side performance.

It is possible that client side issues exist and have not yet been identified which impose a limit on the actual amount of master data that can be loaded for a given dimension/application.  In these cases the limitation is generally induced by a defect which is later corrected by development.

There is no limitation to the amount of accessible master data inherent in the design of BPC at this time.

What Factors Determine Master Data Cache File Size

Each dimension a user has access to gets cached during the first login on a given machine and then subsequent logins, when changes are made.  The XML cache files consist of one line per dimension member / hierarchy that a user has access to.  Each line documents all of the properties associated with that dimension member.

Thus, there are three primary factors that determine the size of a master data cache file for a given dimension:

1. The number of hierarchies
2. Either:
   • The number of dimension members (for unsecured dimensions)
   • The number of dimension members that a user has access to (for secured dimensions)
3. The number and size of properties

Example 1:

For example, a simple unsecured dimension and one hierarchy containing 15,000 members will result in a file with approximately 15,000 lines (there will be a couple more to make it a well formed XML file).

Example 2:

If you take the same dimension, and double the number of properties (with consistent sizes to the first set of properties) you will effectively double the size of the cache file even though it will not result in any additional lines in the XML file.

Example 3:

Similarly, if you were to add a second hierarchy to the same dimension, including all existing members, you would approximately double the size of the cache file by approximately doubling the number of lines in the file.

Increasing the size of the cache file increases:

• The amount of time it takes to create cache files in BW
• The amount of time it takes to transfer cache files to the client over the network
• The amount of time it takes for the client tier to interact with master data (login, some action pane operations, etc).

Recommendations / Best Practices

Now that we know the drivers to large dimensions, we can design some best practices to mitigate their impacts.

Recommendation 1:  Secured Dimensions – Only provide access to required members

Often times, secured dimensions are the largest.  When working with large secured dimensions, ensure that you configure member access profiles in a way that only grants access to required members.  By limiting the number of members and/or hierarchies a user has access to you are directly impacting the size of the cache files used on the client tier which in turn increases performance.

Recommendation 2: Hierarchies – Evaluate whether additional hierarchies are required

Multiple hierarchies are often required and can be included in well designed applications however; it is worth evaluating whether an additional hierarchy is required to reach your goal.  There are other techniques that can sometimes be used in place of a hierarchy without losing any functionality.

Depending on the purpose of the hierarchy, some possible alternatives include:

• Adding one or more additional dimensions to the application model
• Adding logic to simulate the rollups to be included in the new hierarchy

Recommendation 3: Property Names – Keep property names concise

This is a relatively simple one to demonstrate.   If you have a dimension with 120,000 lines (unique member / hierarchy combinations), and you name a property “ARLP” instead of “A_REALLY_LONG_PROPERTY”, you will decrease the cache file by a little more than two megabytes.  If you enact shorter names for multiple properties, or have more lines in your cache file, the savings can be much greater.

Final Thoughts

We are continually seeing refinements in the way BPC creates and interacts with master data so setting any targets or limits on master data volume would quickly become out of date.  That said we currently have successful BPC NetWeaver customers with dimensions containing greater than 100,000 members.

Based on the information above, you know what drives the size of the master data cache files and where the application will be impacted.  You also have the aforementioned guidelines to help mitigate the impact of large amounts of master data.

Rolling 12 Custom Measure in BPC 7.x, version for MS and NW

It is not out of the ordinary for a client to want a summation of last 12 months for many accounts.  One way of doing this, if the number of accounts is minimal, is to create several dimension formulas, but this could diminish performance.  A better way of accomplishing this is to create a Custom Measure called Rolling 12.

BPC version for Microsoft

In BPC, version for Microsoft, it is fairly straight forward in that the formula for both Periodic and YTD are the same, except for “ELSE” statement at the end.

The formula for a Periodic application is as follows:

          iif([Account].CurrentMember.Properties("ACCType")="INC" or

          [Account].CurrentMember.Properties("ACCType")="EXP",sum(LastPeriods

          (12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[Periodic])

For a YTD application:

         iif([Account].CurrentMember.Properties("ACCType")="INC" or

         [Account].CurrentMember.Properties("ACCType")="EXP",sum(LastPeriods

         (12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[YTD])

To test the formula, login into SQL Server Business Intelligence Development Studio

Select File-> Open -> Analysis Services Database.  When the splash screen renders, select the correct database.  If a database is not listed, enter the correct server and database.

On the right side of the pane in the Solution Explorer Pane, select the desired cube, right click, and select Browse.

Click on the Calculations Tab and in the script Organizer, right click and select New Calculated Member.

Fill out the Name and the Expression as specified below:

To save the formula, click on the Process button, three green arrows in a circle or from the menu
(Build -> Process)

Click Run.

When the process is finished, click Close and Close again.

Navigate back to the Browser Tab and reconnect to the database by clicking on the  icon.

Drag the Measures dimension into the center of the table, Time along rows, and Account for the columns.

You will notice that the YTD and the new Rolling 12 measure should equal for the current year, but will be different once you span multiple years.

In order to get this into BPC, you must log into SQL Server Management Studio:

Navigate to the proper Application Set -> Tables -> dbo.MeasureFormula.  Right click and choose Edit Top 200 Rows.

Fill out the table according to the below:

•      SEQ: Pick a number greater than what is already stored by 10, in my
       instance I chose 270
•      Name: The name of the Custom Measure, in this case it is Rolling_12
•      Statement: enter

            [Measures].[Rolling_12] as 'iif([%

             ACCOUNTDIM%].CurrentMember.Properties("ACCType")="INC" or [%

             ACCOUNTDIM%].CurrentMember.Properties("ACCType")="EXP",sum

             (closingperiod([%TIMEDIM%].month,[%TIMEDIM%].currentmember).lag

             (11):closingperiod([%TIMEDIM%].month,[%

             TIMEDIM%].currentmember),Measures.[Periodic]),Measures.[Periodic])

•      SolveOrder: 4
•      Description: Rolling_12
•      Required Level: Year
•      IsVisible: 1
•      IsYTDApp: 1 for yes or 0 for Periodic
•      IsSystem: 1

Tab out of the last column to save the row.  Right click on dbo.MeasureFormulaApp and Edit the Top 200 Rows.  Scroll to the bottom and enter the below:

•      Application Name: Finance or your cube name
•      SEQ: 270, or the number you specified earlier
•      Name: Rolling_12
•      Description: Rolling 12

The last step is to log into BPC Admin and reprocess the cube.  To do this, login to BPC Administration, go to the applications, and highlight Application.  On the right, click Modify Application.

Select the appropriate application and check the boxes to Reassign SQL Index and Process Application.  Next, click Modify Application.

When this is complete, log into BPC for Excel and in the Measures dimension, you should now see Rolling_12.

For BPC 7.x, version for Netweaver

Login to the SAP GUI and go to transaction SE38.  For the Program, enter UJA_MAINTAIN_MEASURE_FORMULA and click the Execute icon (3^rd from the left)

Enter the Application Set ID, Application ID, and the User ID:

Click the green check mark.

Click on the first icon to create a new measure.  Enter Formula Name, Description, and formula.

For Periodic applications, the formula is as follows:

          MEMBER [MEASURES].[ROLLING12] AS 'IIF([%

          P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM

          (LastPeriods(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),-[Measures].

          [/CPMB/SDATA]),IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES

          ("2/CPMB/ACCTYPE")="EXP",SUM(LastPeriods(12,CLOSINGPERIOD([%

          TIME%].[LEVEL02])),[Measures].[/CPMB/SDATA]),IIF([%

          P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

          ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),IIF

          ([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-

          ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),-

          [MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

For YTD applications, the formula is as follows:

         MEMBER [MEASURES].[YTD] AS 'IIF(([%

          P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR

         [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-

          ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),

         ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])))'

          SOLVE_ORDER=3##MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%

          P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR

          [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP")

          AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")

          ="TOTAL" OR  [%TIME%].CURRENTMEMBER.PROPERTIES

          ("2/CPMB/PERIOD")="Q1"  OR [%TIME%].CURRENTMEMBER.PROPERTIES

          ("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%

          TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3##MEMBER

          [MEASURES].[ROLLING12_YTD] AS 'IIF([%

          P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR

          [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")

          ="EXP",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),

          [MEASURES].[PERIODIC]),[MEASURES].[PERIODIC])';SOLVE_ORDER=3

***Note- The Rolling 12 custom measure for a YTD application is

                   more complex because it has to build off the Periodic calculation

                   which is built off the YTD calculation.

Click the Save Icon.

Login to BPC for Excel, and you should be able to see the new custom measure.

Dependent Tasks in Business Planning and Consolidation 7.0 NW (BPC)

Task Manager helps in managing different activities to be performed by the users in Business Planning and Consolidation (BPC) system. Task Profile Creation being One time activity it involves lot of responsibility in creating it. Right set of system authorization needs to be given to right set of people else it might lead to lot of issues. Usually System administrator creates task profiles and assigns it to end users.

The Security in BPC system needs to be maintained for any user to perform the desired activities in the BPC system. The Security of the BPC system is handled through different profiles on different criteria.

As represented in the diagram above, Member Access Profiles help in providing the authorization to the users based on the Secured Dimensions in the BPC applications. The major mapping for providing access to BPC system is as below,

1.  Team <--> Member Access Profile (Many to Many), Team <--> Task Profile (Many to One)

A Team can have any number of Member Access Profiles and Only one Task Profile

2.  User <--> Team (Many to Many), User <--> Member Access Profile (Many to Many), User <--> Task Profile (Many to Many)

A User can be assigned to any number of Teams, Member Access Profile and Task Profiles.

Once a user is assigned to any Team he has access that is defined in Member Access and Task Profiles assigned to the Team. Assigning users to Team helps in reducing the effort of maintaining users individually.

Note:

1. It is good practice to assign single task profile to any user.

2. Both Member Access Profile and Task Profile needs to be assigned accordingly for correct set of activities.

(No use of access to write data through Task Profile without having write access to any master data through Member Access Profile)

There are different set of tasks available for BPC system. Every task is unique and provides different access specific to them.

Not all tasks can individually provide the required access specific to them. They require certain other tasks to be added for providing their access to assigned users. The table below gives information on the additional set of tasks required for those individual tasks to provide the required access.

Dependent Task Name

Description

Supporting Task

Accessing System

Manage Template

It provides read access to Reports and Input Schedules saved in the system.

eAnalyze

Excel Client

Submit Data

It provides access to save any data to the system through any input schedule.

eAnalyze

Excel Client

Manage Distributor

Provides access to,

• Create new distribution list
• Validate and Save distribution list
• Edit existing distribution list

Needs to be a Primary Admin.

Excel Client

Publish Offline

Collects offline changes in Input Sheets and sends to data base

eAnalyze,WebAdmin, SubmitData

Excel Client

Create Journal

Provides access to create and save new journal entries into the system.

Review Journal and eAnalyze

Excel Client

Post Journal

Provides access to post data to the system through available journal entries.

Review Journal and eAnalyze

Excel Client

Review Journal

Provides access to review available journal entries

eAnalyze

Excel Client

Unpost Journal

Provides access to unpost the data entered through journal entries

Review Journal and eAnalyze

Excel Client

Set Work Status

Access Content Lib: user can set work status through BPC for Web.

Submit Data :User can set work status through BPC for Excel

Access Content Lib or Submit Data

Excel Client, Web Interface

Create Web Page

Provides access to,

• Add a new web Page
• Post a document
• Preview available documents
• Filter or Sort the document list

Access Content Lib

Web Interface

Manage Content Lib

Provides access to,

• Post a document
• Edit Page information of available documents
• Preview available documents
• Filter or Sort the document list
• Delete required documents available

Access Content Lib

Web Interface

Manage Live Report

Access to Build a Report and Delete available Live reports

Live Report

Web Interface

UpdateToCompanyFolder

Allows user to Update the templates in Company folder.

Manage Template

Excel Client

Tasks available in BPC 7.0 NW other than what mentioned in the above table are Independent Tasks and can provide their specific access without requiring any additional supporting tasks.

Thus various Dependent tasks and its Supporting Tasks help us configuring the Exact Task Profile required for various End Users.

Tip on Using Data Manager Package IMPORT_USING_FTP in SAP BusinessObjects Planning and Consolidation, version for NetWeaver

If you have used the SAP Business Objects Planning and Consolidation (BPC) Data Manager package to load a flat file you will know you must first import the file onto the BPC server before running the load package.  But what if you have sensitive data like a payroll file and you want to limit the potential for someone to access a file they are not permitted to view?  Then you may want to automate the transfer of data from a source system into BPC.  For this type of use case, there is a Data Manager package called IMPORT_USING_FTP which allows BPC to pick up a file from an FTP site and import the data in a single step.

The Import_Using_FTP package works fine for all versions of BPC for NetWeaver if your FTP site containing the source file allows anonymous FTP login.  But unfortunately there is a bug in this Data Manager package in BPC before Support Package 10 of BPC NW 7.0 and before BPC NW 7.5 Support Package 05 which causes the package to fail if the FTP site has anonymous logon disabled.  Naturally, when dealing with sensitive data, you would not allow anonymous logon to the FTP site, so the IMPORT_USING_FTP package has not been a workable option for many BPC users.

SAP Note 1504226 has been released for BPC 7.0 NW SP10 and BPC 7.5 NW SP05 to fix the problem the IMPORT_USING_FTP package does not work if anonymous access is disabled on the FTP server.  If due to your application lifecycle you are not able to install the support package or, even the SAP note individually, then you can utilize the following work around to be able to use the IMPORT_USING_FTP package.

From BPC Excel, go to the modify package option for your Data Manager package.

Click on the Advanced button.

In the script window, change the highlighted reference from %FtpSourceFile% to %DestinationFolder% and save the change.  Below is the original script.

The following screen print shows the corrected script.

The changed line should read as follows:

TASK(/CPMB/CONVERT,FILE,%DestinationFolder%)

Below shows the package execution prompts with a working example.

SAP Note 1504226 fixes a problem with the BPC Data Manager package IMPORT_USING_FTP.  But if you need to utilize that feature before applying the note, then just follow the simple steps explained in this work around.

A Checklist for BPC Projects Who Are Preparing for Using SAP Safeguarding Services

Due to the tremendous adoption of the SAP BusinessObjects Planning and Consolidation (BPC) application many SAP customers are implementing the software currently.  During the Realization phase of a BPC project, customers who have SAP MaxAttention support contracts may elect to participate in a service offering called SAP Safeguarding for Integration Validation.  These services may also be known as a Technical Integration Check (TIC) or Technical Integration Check Extended (TICx).

These Safeguarding services are delivered by experts from SAP Active Global Support (AGS) and sometimes supplemented by my teammates from the SAP Labs Regional Implementation Group (RIG) or from SAP Field Services.  The Safeguarding services focus on integration, performance, and data consistency. The goal is to make your core business processes work, end to end. To accomplish this, we analyze and monitor the critical components and interfaces that support core business processes, carefully checking that:

• Integration is defined, tested, and transparent
• Interfaces are monitored and are passing consistent data
• Performance of critical business processes is optimized

Your organization’s assigned SAP Technical Quality Manager (TQM) and/or the SAP AGS service lead person will work with you to schedule and prepare your organization for this service.  To receive the most efficient review service, I suggest five topics which you should tackle prior to the SAP team’s arrival.

1.       Business Process Identification

2.       Environment to be Used for Monitoring

3.       Remote Connectivity

4.       Security

5.       Logging, Tracing, and Debugging

----------------------------------------------------------------------------------------

1.       Business Process Identification

The BPC Safeguarding services usually run between three and five days during which it is customary to review a few significant business processes.  Your BPC Administrators and Subject Matter Experts should discuss prior to the review which are the most important processes for the SAP team to review.  The “got-to-have” processes might include key input schedules/reports to be used by a large portion of your users, a critical piece of script logic that is essential to your planning process, or other crucial business rules used, for example, in consolidations.  Ideally you will be at a stage in your project timeline to have already tested these processes on production-like data sets or using a simulated number of users and you will have established some benchmark performance data you seek to have the SAP review team validate or improve upon.

2.       Environment to be Used for Monitoring

After documenting and understanding the critical business processes you identified in step 1, the SAP review team will run those processes as a baseline measurement to compare against subsequent runs after making any suggestions for improvement.  Given this iterative test and review process, certain factors have to be thought about in the context of your project environment.  Will the system environment be comparable in size and load to production?  Will the system review be competing with other testing activities which may be occurring at that time?  If the SAP team asks to change a certain parameter, script logic file, or other configuration setting is your change and transport management system and process ready to accommodate the review team’s requests?  Are the security profiles of the test users truly representative of that of the future end users?  BPC security can have a big impact on not only the system’s performance but on the overall user experience within the business process.  Finally, due to the unique way BPC data is transferred to the client hardware it is very helpful to gather a sample client hardware snapshot from some of the BPC Administrators and the future end users.   A screen print of the system properties is sufficient.  To collect this from your users ask them to press the Windows key + Pause, or alternatively go to the Windows Start menu then right-click on the computer name and select Properties.   Preparing for the points above before the SAP review team arrives will make the assessment go smoother and more effort can be put forth on review of the business processes, rather than working through the environment questions.

3.       Remote Connectivity

During the course of the BPC review some SAP team members may be working off-site and therefore it is vital that remote connectivity to the customer’s system is available.  Not only does this mean the SAP R/3 Connection to access the BW system (BPC, version for NetWeaver only) needs to be open, but it also means a WTS connection has to be open to access the BPC .NET server and the BPC client applications which need to be installed on the server.  SAP Note 605795 explains the WTS set up.  Since WTS setup is generally not needed for the SAP Business Suite including BW many customers do not have this in place.   However without WTS, remote analysis of the BPC client applications and .NET server setup is not possible.

4.       Security

User ids and security for the SAP review team members needs to be setup in advance of arrival for three different areas of the BPC landscape.  First BW Basis users are needed with SAP_ALL security profiles to review and monitor the system parameters and performance.  The profiles must contain the transaction codes that begin with ST*, SM*, DB*, SE*, AL*, UJ*and RS*.  The SAP team also needs an Active Directory or local admin user which has authorization to the .NET server to look at the BPC Server Manager application, IIS, and various logs which BPC creates.  Finally, the SAP review team needs to be provided with valid BPC user ids with appropriate security to execute the critical business processes and to review the BPC administration setup.  To reiterate a point made before, the BPC security profiles, especially the member access profiles, need to be representative of the users who will execute the business processes in production.  It may be simpler for you to just give the SAP team full member access rights to these test users, but that approach will sacrifice accuracy in providing a realistic measurement of system performance.

5.       Logging, Tracing, and Debugging

Prior to the SAP review team’s arrival, you should activate several Appset and Application parameters used for logging and tracing in BPC.   You can setup these parameters from tasks within the BPC Web Administration homepage.

Appset Parameters:

                LOGLEVEL = Info
                TRACELEVEL = Debug
                TRACEUSER = DOMAIN\USER

Application Parameters (for each application that is to be reviewed):

                BPC_STATISTICS = ON

Trace Files:

On each local machine which is to be used for the review, find the file named log-configuration.xml which is located in the <Drive>:\Program Files\SAP BusinessObjects\PC_NW directory where the client software was installed.  If using Windows with 64 bit platform the path would be: c:\Program Files (x86)\SAP BusinessObjects\PC_NW.

Edit this file with a text editor such as Notepad and change the setting TRACE=ERROR to TRACE=DEBUG.  Note that with Windows 7 you have to run Notepad as an Administrator to write to the Program Files directory named above.

Within the log-configuration.xml file, you can specify where the trace and log files should be stored.  To do this you need to modify the following items by inserting the drive and path inside the quotation marks.  For example:

TraceDestination value="C:\Program Files\SAP BusinessObjects\PC_NW\Trace"

LogDestination value="C:\Program Files\SAP BusinessObjects\PC_NW\Log"

Make sure the BPC users have write access permission to the drive locations where the trace and log files are written.  In Windows 7 you may have to right-click on the Microsoft Excel menu item and select Properties, then click on the Compatibility tab and check the option to run as an Administrator.  If permissions are setup correctly BPC will begin to write trace and log information to two files:

BPCTRACE.0.LOG and BPCLOG.0.LOG

The log and trace files are explained further in chapter 3 of the BPC Operations Guides.

• SBOP Plan & Consol 7.5 NW Operations Guide
• SBOP Plan & Consol 7.5 M Operations Guide

Debug User

If you are running the NetWeaver version of BPC, it is critically important that you setup one or more debug users for the SAP review team to utilize.  How to establish the debug users in the BPC Server Manager application is detailed in chapter 4 of the BPC NW Operations Guide.  From our experience, many customers are unsuccessful at defining the debug users on the first attempt.  Often this is because they failed to type the Planning and Consolidation user id in capital letters as DOMAIN\USER ID or they mistyped or cut and pasted the information which resulted in an extra space after the user id.  Please test your setup to see if the debugger does launch.

• Login to the BW system with the user id named as a debug user
• Go to transaction SE37
• Enter function module name as:  UJ0_ON_NEW_RFC2 and click display
• Set an external breakpoint on line 39
• Login to the BPC Excel client and start the connection wizard using the user id defined as a debug user and proceed to the step when BPC obtains the AppSets available to the user
• Switch to the BW session and the debugger should have started
• Optionally you can use t-code SM50 to confirm that the BPC user id is shown logged into BW rather than the generic system user BPC-ADMIN
• End the debugger session and log off BW and BPC

If you did not setup the debugger ID correctly, then remove them from the BPC Server Manager and restart IIS.  Then try to assign the user ids again and restart IIS a second time.  Repeat the test as explained above.

Fiddler

Fiddler is a free web debugging tool that is useful for monitoring the traffic between the .NET server and the ABAP server.  Please download and install this application on the client machines which will be used for the review service.

In Conclusion

In the RIG team’s experience in helping with these Safeguarding reviews these are specific preparation tasks that could be addressed prior to the assessment to get the most out of the service.  Often these tasks do not get performed until the SAP team shows up on site, which obviously cuts into the available time to focus on the value added deliverables of the engagement.

The SBOP PC Migration Situation

The SBOP PC Migration Situation
There is a lot of confusion out there regarding how to migrate from BPC 5.1/7.0 Microsoft to 7.x for NetWeaver and now there is a two day workshop designed to make you an expert!

What’s on the agenda?

During the two day workshop we will discuss:
  • The migration process
  • How to estimate the effort required
  • The migration utility / consulting aid
    o What is it?
    o How does it work?
    o Why use it?
    o What does it migrate automatically?
    o Gotchas
  • Manual migration steps
    o Templates
    o Script Logic
    o Dimension Member Formulas
    o What is a BAdi and when should I use them?

In addition, we will have:

  • A live demo exporting data from BPC for Microsoft
  • Hand’s on exercise’s covering:
    o Importing an Application Set into NetWeaver
    o Migrating and EvDRE template
    o Basic Dimension Member Formula migration
    o Basic Script Logic migration

There will be a competency exam at the end of the second day to ensure you have mastered all of the relevant facts.  A passing score gets you access to the migration utility!

Who should attend?
Anyone interested in gaining a broader understanding on the migration process and get access to the migration utility.

When and Where?
The workshop is being held at the MicroTek King of Prussia office January 6th through the 7th, 2011.

Where can I register?
You can get additional information, and sign up for the workshop at the official SAP Education site.

Installation Options for SAP Business Objects Planning and Consolidation, version for Netweaver

There are two main installation paths that can be chosen for your SAP Business Objects Planning and Consolidation, version for Netweaver (BPC) implementation:   you can install BPC within your current BW system (the "Add On" method) or you can install BPC on a separate BW system (the "Stand Alone" method).  Each installation method has pros/cons that should be considered prior to deciding upon an implementation path.

Prerequisites:

SAP Business Objects Planning and Consolidation 7.5, version for Netweaver (BPC75NW), must be installed on a suitable Netweaver platform.  For BPC75NW Support Pack 07 or earlier, the Netweaver platform version must be Netweaver 7.01.  Based upon customer demand (SAP listens, and SAP delivers), Netweaver 7.3 will be supported by BPC75NW SP08 (scheduled to be released in June 2011).

Which is better:  Add-On vs. Stand Alone?  The answer varies for each customer since it depends on a number of factors that need to be evaluated for each implementation:

• Hardware Investment
• Performance
• Administration Costs
• Integration Development and Maintenance
• Upgrade Considerations
• Support Pack Considerations
• BWA Investment
• Data Movements

Hardware Investment

There is typically some increase in hardware investment when implementing any BPC project.  The Stand Alone scenario will lead to some additional cost for servers required for creating the new instance of BW that will be used to support the BPC application.  However following an  "Add-On" implementation scenario will also require additional application servers to accommodate the anticipated incremental BPC user load on the existing BW system.   The net hardware cost differences between "Stand Alone" and "Add-On" may be negligible.

Performance

On initial reflection, the Stand Alone scenario may be presumed to inherently have superior performance; however this is not necessarily to case if the Add-On scenario is properly sized.  In addition, load balancing is also an important consideration to ensure overall system responsiveness (for example, BPC processes can be isolated from the BW processes, from a load balancing perspective, if desired).

Administration Costs

System Admin costs may be greater with a Stand Alone implementation since an additional host system will need to be maintained, tuned, monitored, etc.  

However if the administration support is provided using in-house resources, the additional cost may be negligible by being easily absorbed into the existing in-house administration support model.  

If system administration is out-sourced, then additional cost should be anticipated (as most external support contracts charge by system instance).

Additional consideration should be anticipated to keep your out-sourced system up to date with current support packs and corrections.  Failure to maintain the system can result in increased administration and operational costs through the need to research and implement corrections that have already been developed for more current support pack levels.

Integration Development and Maintenance

There are a variety of ETL tools to pull master and transactional data into BPC, but these tools typically point to BW objects (info cubes and info objects) within the hosting BW for source input.  Following a Stand Alone implementation path, additional processes will need to be developed to load the source data into the BPC supporting NW environment.  This additional configuration will need to developed and, of course, maintained.

Upgrade Considerations

New BW functionality is continuously being deployed and customers struggle to determine the best timing to upgrade their systems.  In a Stand Alone deployment, it may be easier to upgrade your main BW system while leaving your BPC hosting BW system at an earlier revision.  One of the main benefits to the stand alone path is the elimination of many of the dependencies between BW upgrades and BPC upgrades.   This decoupling may be a significant consideration since currently BPC 75 NW is currently only supported by Netweaver 7.01.  If you have a BPC75NW system today deployed using the Add On approach, you cannot upgrade your NW environment to NW 7.3 since BPC is not supported by this version of NW.

Recognize that system upgrades depend on a number of business environmental factors:  corporate politics, the needs and schedule of the end user work streams (i.e.: Finance, Sales, Marketing, HR, etc.), and the technical capacities to perform the work.  Some companies elect to upgrade their systems on regular basis, others are more conservative and will upgrade infrequently if the system performance is acceptable.

Support Pack Considerations

Certain highly regulated industries require extensive regression testing of all BW processes after implementation of any support packs.   Even companies that are not specifically bound by industry regulations should always consider some planned regression testing after any system change is introduced (i.e.: support pack, individual correction, SAP kernel updates, OS kernel updates, DB patch updates, etc...).  Similar to the discussion surrounding "Upgrade Considerations", a decoupled system approach allows timely support pack implementation for BPC without the need for extensive regression testing of the main BW system.

BWA Investment

Many SAP BW customers are currently enjoying the benefits of speed enhanced processing provided by BW Accelerator (BWA).   In an Add On approach, the BWA can very easily be extended provide performance enhancement for BPC.  The Stand Alone approach requires additional BWA hardware to be installed since BWAs are typically setup to service a specific BW instance using a 1:1 connection between the BW instance and the BW Accelerator (reference slides 24-25 of http://www.sdn.sap.com/irj/sdn/bwa?rid=/library/uuid/3604c604-0901-0010-f0aa-b37378495537 ).

Data Movements

As discussed in the "Integration Development and Maintenance" section a Stand Alone approach will require additional data extraction configuration and maintenance.  Most customers will load master/transactional data into their main BW systems for general reporting purposes.  Following a Stand Alone process will require at least two sets of data to be generated (one for the main BW system, and a second set for the host BW system).  When data is pulled into the BPC application a third set of data can be generated as well.  All this data requires storage and has to be periodically backed up.  In a high availability system, additional copies may also be generated by the mirrored backup systems.

Other Considerations

• Creating and installing a "system copy" (i.e.: to establish a quality test environment) will be incrementally easier with Stand Alone approach. The copied system will be much smaller due to its segregation from the main BW processes.
• Data Manger is by design constrained to one BW instance (the instance upon which BPC is installed) , so the ability to create a master process chain to update all data will require addition separate steps for a stand-alone system. Additional BW configuration, such as additional staging cubes and the potential for redundant data with both BW systems should be anticipated.

This blog does not provide a final recommendation for one implementation path over the other.  There is no best implementation path that can be generalized for all customers.  The choice for Add On or Stand Alone should be thoroughly evaluated by your system architecture group along with project goals and appropriate cost considerations.

When the performance of BPC data manager package is not consisent. (EPM BPC Microsoft platform)

When we say the performance issue of BPC, we need to make clear which performance issue.
Data retrieving performance issue in the report?
The performance of Logic calculation? Data posting performance in the input template?
Or performance of data manager package?
The reason why we need to make clear is the bottom reason of performance issue is quite different. (I will post about each root cause of performance issue later.)
When I visited a customer site 3 weeks ago, I heard the complaint about the inconsistent performance of their custom data manager package.
So we monitored SQL process and found there was some CXPACKET wait type.

CXPACKET wait type comes from Microsoft SQL engine for waiting worker threads on the CPU.
SQL server usually split a SQL query into multiple internal queries by query engine and multiple CPU threads are performing those partial queries and sync its result afterwards. Most of case, it works perfectly without any problem but sometimes, especially when insert a lot of data, CXPACKET wait type happens. It means query engine is waiting one of other partial query.

The problem is, when it happens, its performance becomes REALLY SLOW. (I saw a worst case that took several hours to finish. Usually, it can be finished within 30 minutes.)

That's why customer complains the performance of their custom data manager package is not consistent.

Some Microsoft experts says if user maintains indexes and statistics very well, this CXPACKET WAIT should not happen but as I addressed, when user tries to insert a lot of records in the table, sometimes it happens.

To resolve this issue, there is one workaround which is disable parallelism option (MAXDOP: Max degree of parallelism option.)

Of course, when we disable MAXDOP, it means we gave up parallel execution feature of SQL server. Therefore, its performance will be decreased but it will guarantee consistent execution time. (Actually, performance difference is not that big.)

In addition, we will not set this option forever because this is an option of SQL server, it will affect all other query performance, Therefore, if a custom data manager package has this issue, we can disable this option as a first step of package and enable it when package finishes.

Here is a screen shot of the package that has two additional SQL execute Tasks.

The first SQL execute task should have below statement
sp_configure 'show advanced options', 1; --Should show advanced option to set parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;   --Turn off parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO

The last SQL execute task should have below statement.
sp_configure 'max degree of parallelism', 0;   --Turn on parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0;  --will not show advanced options.
GO
RECONFIGURE WITH OVERRIDE;
GO

In conclusion, when user executes this package, parallelism option will be turned off during package is running and roll back parallelism option when it finishes.

Of course, this option should be added when user finds CXPACKET wait type in the process monitor while their package is running and user should not run same package at the same time because parallelism option might be turned on/off unitentionally. (User can avoid this through set the flag while package is running.)

The two customers solved inconsistent performance issue of their cusom package using this solution. Of course, user should check the environment and other condition like running lite-optimization at the same time before apply this solution.

Performance is important but sometimes consistent performance is more important when it is scheduled job.

Enhanced selections to load transactional data from any infoprovider

SAP BusinessObjects Planning and Consolidation 7.5 version for Netweaver successfully leverages the Netweaver infrastructure and we can use data stored in infocubes in the Enterprise Data Warehouse. In this blog we will discuss the functionality that we can use to specify the selections to load a specific data slice from any infoprovider.

We can use a data manager package to load data from any infocube to SAP BusinessObjects Planning and Consolidation version for Netweaver. You can get more details about this at: http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3414700)ID1897954850DB00258195100095085303End?blog=/pub/wlg/14002

The first option to specify selection is the option that is available in the transformation file. Please see the example of the following transformation file:

Note that in the OPTIONS section, we have entered a selection to select data from only two characteristic values C2000 and C1000 of the characteristic ZACCT01. Hence we are selecting only a subset of the source Infoprovider. You can enter selections from multiple characteristics here in the selection and thus load data from a specific data slice in the source from Infoprovider.

In addition to this, we can also have enhanced user interface to specify the selections. The delivered process chain for this enhanced user interface is /CPMB/LOAD_IFOPROV_UI.

When we run the data manager package that is linked with this process chain, we can specify the technical name of the source infocube.

In the user interface that we get in the next screen, we can see the characteristics for the infocube that we selected in the earlier screen and we can specify the individual values as well as the ranges for the selections for each characteristic in the source infocube as you can see in the screenshots below..

What if your source cube has multiple key figures? In that case, one option is to convert the key figure based model into an account based model (https://help.sap.com/saphelp_crm60/helpdata/en/0f/903d41d4cc4c0de10000000a1550b0/content.htm) prior to loading the data. If you don't want to adopt that approach, SAP BusinessObjects Planning and Consolidation 7.5 version for Netweaver also provides us a keyword MVAL. For us to use this keyword, the dimension must be one existed column in the cube. The dimension members in dim_mem must be the base members of the dimension (CALC = ‘N'). The key figure type must be the field name of the source data. Here is an example of using this keyword:

[Users can directly input the dimension members in the dim_mem, or input any existing mapping command in the dim_mem, such as *newcol, *col or *newcol(cash)+*col(1)].

Thus we can see that using a combination of these, we can effectively load a specific data slice easily from any infoprovider.

The Key Benefits of SAP BusinessObjects Planning and Consolidation -- BPC7.5 Curriculum and Certification

The SAP BusinessObjects Planning and Consolidation application unifies and streamlines the planning, budgeting, and forecasting process and can enable a faster close. Finance-owned and maintained, the application unifies planning, financial consolidation and management reporting for the enterprise – improving budget cycle times, driving compliance with regulatory and financial standards, helping reduce external audit costs, instilling confidence, and reducing business risk.

SAP BusinessObjects Planning and Consolidation is available in two versions:

• SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform – A unified planning, budgeting, and consolidation application that is designed to be owned and managed by business users
• SAP BusinessObjects Planning and Consolidation, version for SAP NetWeaver – A unified planning and consolidation application built directly on the SAP NetWeaver technology platform and designed to be owned and managed by business users

With SAP BusinessObjects Planning and Consolidation, everything you need to meet your bottom-up and top-down financial and operational planning requirements, as well as complete consolidation and reporting, is available through a single, unified application and user interface. You can benefit from:

• Improved agility – Quickly model new business scenarios and decide on a course of action that maximizes value creation
• Reduced cycle time – Finance and line-of-business managers can collaborate in a unified landscape, streamlining the process of creating and approving plans, budgets, and forecasts.
• Minimized business and compliance risk – Transparent financial data and a single version of the truth enable fast, accurate management and statutory reporting
• Increased user productivity – An intuitive interface and familiar office tools help workers make the most of their time.

SAP Education offers a series of BPC7.5 courses in the areas of Administration, Reporting & Planning, and Consolidations:

BPC410 – BPC7.5 Administration for the Microsoft platform includes modeling and maintaining dimensions and applications, performing data imports and transformations, creating your own script logic,  setting up a security model, how to set up business process flows and work status.

BPC420 – BPC7.5 Administration for the NetWeaver platform includes modeling and maintaining dimensions and applications, performing  characteristic and InfoCube data imports and transformations, creating your own script logic,  setting up a security model, set up auditing, how to set up business process flows and work status, and how to create drill throughs to BW and ECC.

BPC430 – BPC7.5 Reporting for the NetWeaver & Microsoft platform includes fundamental and advanced dynamic range exchange features, cross application reporting, formatting, report jumps, EV functions, audit reports, how to use business process flows, and how to create and use drill throughs to BW and ECC.  In addition, you’ll be able to carry out top down and bottom up planning, use the spread/trend/weight functions, copy and mark up data,  set up macro buttons or hypertext link commands, and model various planning scenarios.

BPC440 – BPC7.5 Consdolidations for the NetWeaver platform including modeling and maintaining dimensions and applications for legal consolidations, configure business rules, create script logic for consolidation scenarios, set up and test all consolidation task such as validation, currency, intercompany eliminations, ownership eliminations, journals,validations, and reclassifications.  Work with a robust business process flow for legal consoldations and learn how to use the dynamic hierarchy editor to graphically maintian ownership data.

And let's not forget certification!!  A new certification is now available for SAP BusinessObjects Planning and Consolidation -- C_EPMBPC_75 (http://bit.ly/epmbpc75).     This certification test verifies the knowledge in the area of SAP BusinessObjects Planning and Consolidation 7.5. It validates that the Associate Consultant has a good sound knowledge and understanding of SAP BusinessObjects Planning and Consolidation in SAP BusinessObjects Planning and Consolidation 7.5, version for SAP NetWeaver and SAP BusinessObjects Planning and Consolidation 7.5, version for the Microsoft platform.

While the Geeks Are Away, The Suits Can Still Play

With TechEd Las Vegas going on this week, a number of IT folks are out of the office, but that doesn't mean that you can't make any necessary changes to your planning or consolidation application, that is, so long as you're using SAP BusinessObjects Planning and Consolidation ("BPC").  SAP bills BPC as "A unified planning, budgeting, and consolidation application that is designed to be owned and managed by business users" and for the most part, I've found that to hold true, especially compared to our previous planning application SEM-BPS.

As a former business guy (I'm a CPA who's somehow found a home in IT, but that's a blog post for another time) and one of the key members of our company's BPC implementation team, I find BPC to be an easy application to work with.  It helps that the primary facing tool used by the application is Microsoft Excel, something that most business users hold near and dear to their hearts.  Creating simple input templates and reports is a breeze after spending a couple hours in a training session to learn the ropes (may not be quite so brief if you want to write more complicated input templates and reports).

If you're a business person who's squeamish about writing your own input templates or reports, BPC is delivered with some out of the box templates that can be utilized.   I've yet to extensively use these delivered schedules myself, given that my company's on a slightly older version (7.0 for Microsoft) and the schedules delivered with this version don't utilize the newer EVDRE function; however, EVDRE input templates and reports are available in version 7.5 (both Microsoft and Netweaver) and I'm sure that you would find them useful.

Exhibit 1 - Example of delivered input template in BPC 7.0 Version for Microsoft

Whether you build your own input templates and reports or you utilize the ones out-of-the-box, BPC allows you to harness the power of Excel and use it as a front-end to store your planning and consolidation data in a database.  By storing the data in a database, you no longer have to email Excel files back and forth or store them on some sort of shared drive; everyone with access to BPC can simply login and get the most current data.  I like to think that there's nothing that I can't accomplish in Excel and BPC doesn't prohibit me from doing anything that I'm used to doing in Excel.

An advantage that I've found in BPC is being able to re-use Excel files that have been used historically by our business users to generate their budgets.  We've got a couple of ‘models' that our business users have built/tweaked/improved over a number of years and they didn't really want that hard work to be lost upon moving to a new planning system.  By wrapping BPC functionality around these previously existing models, we were able to keep the functionality of the models exactly the same, but still enable them to interact with the BPC database.

When it comes to making changes to dimensions and applications in BPC (think ways you want to slide/dice or otherwise report on or input your data), the Admin Console is very intuitive and easy to understand, which also lends itself well to being maintained by a business user.  Changes to security are also made in the Admin Console, which means that you no longer have to wait for IT to grant security to new users or make changes for existing ones - which is really nice for those crunch times where a lack of access can come back to bite you.

Exhibit 2 - Admin Console

In the above screenshot, you'll notice the Action Pane on the right hand side of the screen - this is a context sensitive menu which guides both users and administrators through BPC (in both Excel and Admin Console), and is really the key piece of functionality that makes it straightforward, allowing business users to ‘own and manage' the application.  The Action Pane intuitively knows where you are in the system and provides you with a series of options, tasks or activities that are available given where you are.  

While I've been hyping the ability for business users to ‘own' the application, I don't advocate zero involvement from IT when it comes to BPC - there are certain technical aspects that definitely lend themselves to getting IT involved, such as moving data to/from BPC and other systems or writing script logic (or other logic) to help manipulate data behind the scenes, just to name a few.  But, once these back-end processes are set up by IT, the tasks themselves are able to be executed by the business user, allowing optimal control of planning and consolidation processes.

A prime example, and one that's used in most BPC Administrator training courses, is the ability to ‘seed' a budget by copying actual balances from the current year to the budget year.  Executing what's called a Data Manager package allows this to be accomplished with ease - simply fill out the intuitive parameter selection screen and let it fly.  The data is copied into the budget year and is ready to be updated via the input templates that are available.

Exhibit 3 - Copy Data Manager Package Parameter Screen

You could always elect to leave some (or all) of the administration of BPC within your IT department (like we have at my company) - just don't expect your IT folks to be available to make changes this week while they're off learning at TechEd