Wednesday, February 22, 2012

Note 1657623 - SAP Buffer Tuning for BPC 10NW

Summary

Symptom

Prior to this note, it was not clear which SAP buffers were used for BPC Meta/Master Data server side caching.  This meant that SAP buffer sizes had to be calculated manually and the tuning/sizing of these buffers specifically for BPC NetWeaver was a difficult process

Other terms

BPC Performance, BPC Memory Management, BPC System Tuning, BPC Server Caching

Reason and Prerequisites

The default profile parameters related to SAP buffers are not optimized for the new BPC NetWeaver Meta/Master Data caching mechanism.  These new caching mechanisms in BPC NetWeaver rely heavily on having sufficient sizes set for specific SAP buffers.  If these SAP buffers are not configured correctly for BPC NetWeaver, then BPC Meta/Master Data cannot be fully cached within the Application Server Memory.  If the BPC Meta/Master Data is not fully cached, additional overhead will be added to the back end server runtime due to direct database access and this will impact the overall performance of BPC NetWeaver.

Solution

The buffer parameters listed below must be set to an appropriate size in order to properly utilize server side caching of BPC Meta/Master Data.

  • SAP Table Buffer ( See note 480710 for details )
    • Parameter: zcsa/table_buffer_area
    • Parameter: zcsa/db_max_buftab
  • Shared Object Buffer ( See note 1657612 for details )
    • Parameter: abap/shared_objects_size_MB
    • This buffer is only used when shared memory is turned on for BPC NetWeaver. Please see note 1657612 for additional details on how to enable shared memory for BPC NetWeaver.
  • Export/Import Buffer ( See note 702728 for detail )
    • Parameter: rsdb/obj/buffersize
    • Parameter: rsdb/obj/max_objects
    • You only have to adjust this buffer if note 1634921 is applied or BPC 10NW has been upgraded to SP04

After applying this note or upgrading to BPC 10NW SP05, you can run the report UJA_BPC_BUFFER_SIZER to calculate the recommended size of the buffers listed above.
Note

  • The buffer sizes are calculated based on the total volume of Meta/Master Data for the BPC Environments specified; it does NOT take other SAP Applications into account. You must determine the final profile parameter values by combining what is calculated by the tool along with all other external factors (such as hardware capacity of your server, and usage of the buffers by other applications).
  • You will need to re-adjust the parameters when the meta/master data volume grows dramatically.
  • BPC 10 NetWeaver will not use the Shared Object Buffer by default. As of SP05, you can switch it on in the IMG after you have set it to an adequate size. Please see note 1657612for additional details on how to enable shared memory for BPC NetWeaver.
  • If profile parameter zcsa/table_buffer_area is greater than 2GB and your Application Server is on a 32bit platform, you need to adjust shared memory pool 19 to '0' ( profile parameter: ipc/shm_psize_19 ).

Business Objects Planning and Consolidation(BPC) - Data Push from BPC Cube to BW Cube through APD

Scenario

This scenario will show how to push data from BPC Application to BI

Introduction

The Data Manager module in SAP Business Objects Planning and Consolidation, version for SAP NetWeaver, utilizes SAP NetWeaver Process Chains, and provides a way for business users to execute these chains via Data Manager Packages.

Technical Information to the developer:

Since this development is involved the push data to BI for reporting.

1) A query is developed on BPC application Multi provider. The Bex query is referenced in APD. The filter setting will be done inside the query designer for everytime the user likes to change the selection in pushing the data from BPC. image

2)  Create the required BI structures (InfoCube, DSO[Direct Update, Standard], InfoSource, DTP) as shown in screen shot below

image

3) Data is pushed through APD : Below is the screen shot for the data flow in BI through APD.

Here we are assigning source field to Target. This is a ABAP routine that need to be developed according to the requirement source fields (BPC) to target fields (BI) mapping. Finally the data is sent to BI direct update DSO in BI and then from Direct update DSO to Standard DSO further to target cube on which BI reports  will be developed.

image

4) Create the Process Chain that will be triggered from BPC Data Manager. This process chain is created on APD, which is pushing data from BPC cube to BI cube as mentioned in above screenshots.

image

5) Create DM package inside BPC application to trigger the Process Chain

image

Chavi Jindal   I am SAP BI-BPC & ABAP Certified Consultant. Having overall experience over 5.6 years. I have worked for couple end-end implementation projects as well as support projects.

Tuesday, February 21, 2012

BPC (Business Planning and Consolidation) Consulting

Many companies wonder that SAP strategy is used for business planning and consolidation with the recent trend in business objects and outlook soft. Let's see how SAP has been incorporating the outlook soft technology into business planning and consolidation. And let also see how these application can be compared to the existing SAP and business application.

Business Planning and Consolidation – BPC is a tool and is a part of SAP enterprise performance management. BPC Consulting is one of the components of SAP that is focused on financial performance management. It is used to help the organization to manage their financial planning, consolidation and strategic reporting needs. The current business planning and consolidation comes in two version. One is to integrate the SAP net weaver and the other is a standard Microsoft version that is used to accommodate the current outlook soft technology.

EPM – Enterprise Performance Management is a new module that improves the integration among the financial performance management tool and the other SAP applications like net weaver, BI, and GRC (SAP Governance, Risk and Compliance). As a part of this module SAP introduces BPC consulting. This application can be used as a choice to move forward in budgeting, planning, forecasting, and financial consolidation.

After the implementation of business objects and outlook soft, SAP found an overlap of products and made a decision to place the duplicate product in maintenance mode. This is made possible with a clear articulated maintenance rules, migration kits, and declaration of functional parity.

BCP Consulting expertize in working with multinational corporate managers and executives in order to meet their organizational and professional goals. With the help of BPC, a team can be made more effective and efficient by raising their profile among the high competitive corporate environment.

SAP definitive strategy for planning technologies moving forward: What the word on BPC, BI-IP and SRC?

What is happening to BI-IP?

BI-IP is not disappearing and continues to be supported by SAP to 2013 (2016 with extended maintenance). For any customers who are currently implementing BI-IP, SAP recommends you continue on your BI-IP project, and evaluate BPC for your future needs. SAP will continue to invest further in BI-IP as a "planning engine" (or "planning framework"), rather than as a planning toolset as it is today. The idea behind this change is that planning applications from SAP can then be developed on top of the planning engine.

Does that mean BPC will sit on top of BI-IP in the future?

Firstly, it is important to understand that BPC has a "dual-stack" release strategy. When SAP acquired OutlookSoft, the product ran completely on a Microsoft platform (Windows Operating System; SQL Server; Analysis Services; Reporting Services; DTS/SSIS, etc). SAP is continuing to invest strategically in the Microsoft based version of BPC to maintain our commitment to customers (the official name is "SAP Business Planning and Consolidation, version for the Microsoft platform", but it is sometimes unofficially referred to simply with an "M" suffixed after the release name... e.g. BPC 7.0 M, would refer to version 7.0 of BPC running on the Microsoft platform).

While the Microsoft platform is suitable for many customers, there are also a lot of SAP customers who run NetWeaver BI, and want to leverage their NetWeaver BI system. Therefore, since the acquisition of OutlookSoft, SAP has been working on integrating BPC to the NetWeaver BI platform. The official name for this release is "SAP Business Planning and Consolidation, version for SAP NetWeaver", and is sometimes unofficially reffered to with a "NW" suffix, to indicate the NetWeaver based version of BPC (e.g. BPC 7.0 NW). So, there are 2 flavors of BPC - one based purely on Microsoft technology, and the other that is NetWeaver based. Customers can freely pick which platform they prefer, and SAP is continuing to invest in both versions going forward.

Now the dual-stack release strategy is clear, obviously the pure Microsoft based version of BPC will not have any integration to BI-IP. For the first NetWeaver based version of BPC (BPC 7.0 NW), there will be integration to some aspects of the BI-IP engine. For example, BPC leverages the write-back functions and locking engine of BI-IP, and, under some circumstances, it will also use the same functions for reading data out of a cube too. However, to be perfectly clear - while BPC 7.0 NW does use some of the same the core engine components from BI-IP, BPC 7.0 NW does not use, and there are no future plans to use, aggregation levels/filters/queries/etc.

Another important distinction to make is that BPC is, intentionally, not directly integrated into the Enterprise Data Warehouse (EDW), but operates in its own namespace in the NetWeaver BI system. This means that you can't use the BPC client/front-end for reading and writing out of an existing InfoProvider in your system. The reason why this was an intentional design decision, is that if BPC was completely integrated into the EDW, with its shared dimensional model, no business users would ever be able to maintain their own applications (like creating their own dimensions or "characteristics" in NetWeaver BI terms) without impacting the whole EDW. Obviously, no IT department would ever want to allow business users to make changes to the entire EDW. Instead, BPC allows the users to modify the structure of the cube, create their own hierarchies and properties of dimensions (or "attributes of characteristics" in NetWeaver lingo), etc - all from within the BPC application, using a very business-user friendly UI that any business analyst would be comfortable with. In contrast, these types of modeling capabilities are not even available in BI-IP. In BI-IP, you have to go into the NetWeaver BI Administrator Workbench (RSA1) to do any real modeling, because all BI-IP modeling is done "on top of" the cubes. BPC on the other hand directly integrates to the underlying InfoCubes/InfoObjects, etc. So, this design approach actually maximizes the flexibility of the solution AND puts all of this power into the hands of the business without reliance on IT! I have to say, we are pretty proud of this functionality too!

In terms of future integration, SAP continues to evaluate the individual features/functions in releases beyond the upcoming 7.0 and 7.0M releases. That means we will evaluate BI-IP capabilities for future BPC releases, but it is not the intention to have BPC leverage every single feature/function provided in BI-IP as soon as possible. Rather, we will be gathering feedback on the BPC 7.0 NW release, prioritizing any enhancement requests, and then evaluating what we might be able to leverage from BI-IP to fulfill those requirements. One topic that we are strongly evaluating is around leveraging FOX functions in BPC - but if and when this would happen is still being discussed.

So BPC won't be a new front-end on top of BI-IP?

No. It is not our intention to just make BPC a front-end tool. BPC is a complete planning application that will leverage some of the core engine components from NetWeaver BI and BI-IP, but it will not become just a front end tool.

But I heard that BPC isn't scalable. Should I choose BI-IP for large deployments?

No, this is not true. There are many customers on the pure Microsoft platform with large deployments of BPC with thousands of users, so it's simply untrue to say that BPC isn't scalable. It is also important to note though the scalability must be evaluated in the context of an application function. BPC is a "Performance Management application" and was never designed to store line item detail - it is not a data warehouse. BPC 7.0 NW will also be available directly on NetWeaver BI in a matter of weeks, and most readers probably already know that the SAP NetWeaver BI platform is highly scalable with many customers publicly lauding its ability to reach Terabyte data volumes (although again it is important to note that regardless of platform, BPC is still not intended to be a data warehouse!). Furthermore, BPC cubes in the NetWeaver BI release will be able to utilize the BI Accelerator to further improve scalability and performance.

When will BPC 7.0 NetWeaver be available and where can I get more information?

BPC 7.0 NW will be available in Ramp Up at the end of July 2008. Ramp Up's do not go for a fixed period of time at SAP, so a precise General Availability (GA) date is not known, however the initial expected length of a Ramp Up is approximately 6 months. In terms of requirements, BPC 7.0 NW will require NetWeaver BI 7.0 Enhancement Package 1, and BPC will technically be installed as an Add-On to the BI system.

Currently, as the product is still not even in Ramp Up yet, little information has been published on the 7.0 release at this point in time. Obviously, as we get into the Ramp Up, this will change and we will begin releasing much more information (to the Ramp Up customers first). If you or a customer you're working at, is looking to implement BPC this year and wants to go with the NetWeaver version, customers should contact their SAP Account Executive, who can ensure they receive answers to their questions. I am also planning on publishing another blog soon to dispel (or confirm) some of the rumors around what BPC 7.0 NW is and is not, so look for that shortly.

I also want to mention here that there will be a migration tool which supports customers who implement BPC 5.1 now (on the Microsoft platform), and later wish to migrate to the NetWeaver version. Obviously, there are very major architectural differences between the releases, so the migration tools will only be able to automate so much (for example, any custom DTS/SSIS tasks would need to be rebuilt manually), but we have been able to automate a significant portion of the migration. As above, more detailed documentation on the migration tools will be forthcoming closer to the release date.

Will there be any migration tools to go from BPS or BI-IP to BPC?

Currently, we are still evaluating the feasibility of such a migration. There are major differences in the way these products work, so an automated migration would be very difficult and would not give you any opportunity to leverage a lot of the additional functionality in BPC. So SAP will continue to evaluate providing such tools in the future, but there are no firm plans in place today to develop these migration tools.

Will BPC allow me to customize a solution as much as I could with BI-IP?

Absolutely. If you wish to do a lot of customization, BPC is an extremely flexible and extensible solution, so you feel totally comfortable that you will be able to tailor the solution to your individual needs. For example, you can create custom stored procedures (or ABAP logic in 7.0 NW) for completely custom planning functions; you create custom ETL tasks (in SSIS in Microsoft, or Process Types in NW); there are exits for custom VBA logic on the front-end; there are API's available completely customized application; and many, many other types of places where you can customize the solution. The great thing about BPC that is so enciting to customers is that end-users don't have to know any of these more technical topics to build an application - they are there if you want them, but you aren't forced to learn them to build powerful applications quickly.

Why would I want to go with BPC instead of BI-IP?

Every single planning product on the market, no matter which vendor it is from, will have its own set of strengths and weaknesses. So you can try and do a side by side feature/function comparison, and you will find there will be a few features and functions in BI-IP that arent in BPC, and this should not be a surprise. On the other hand, SAP obviously acquired OutlookSoft because it contained a lot of additional functionality not found in BI-IP/BPS. I know this better than most as I was the person responsible for the functional and technical analysis of OutlookSoft before the acquisition, and I have had a long NetWeaver and SEM background before this so I know all our existing products in detail too. However, doing feature/function comparisions is really missing the strategic value BPC offers. The most important points to consider are:

  • SAP is developing and investing heavily into BPC. As I mentioned, BI-IP as an engine will still be invested in, but for creating planning applications, BPC is the product that SAP is investing in heavily, with a large development team and significant go-to-market focus.
  • BPC provides integrated Planning, Reporting and Financial Consolidations. SAP is also investing in BPC as both a planning and consolidation product with excellent financial reporting capabilities. That means BPC is uniquely positioned as a unified solution that is capable of both planning and consolidations and financial reporting in a single system. Even if you arent looking to implement consolidations now, having a single system where you can leverage all of the same master data, metadata, and application artifacts is a huge advantage.
  • BPC is administered by Business Experts, not by IT. For any IT owned systems, a very significant pain point for an end user is having to wait for IT to make their requested changes. Because BPC can be maintained by the business, it puts the power back in their hands. This concept is often a hard pill to swallow (at least initially) for IT as it can be a little scary to think of putting this much power into the hands of the business. However, with some reflection, it is evident that if the business doesn't get the changes they want in a timely manner, they just turn to using spreadsheets, and this is much worse! In BPC, the system and data are still centralized, and that gives IT the opportunity to govern and monitor the changes while not becoming a bottleneck during the process. It really is the best of both worlds.
  • Fast implementations and best in class usability. BPC really has one of the best user experiences of any product in the market (we will be sure to get some new demos of BPC 7.0 NW onto SDN very soon so you can see this for yourself), and the implementation times for BPC are quite rapid. Faster time to delivery, combined with better usability means happier users and better adoption throughout the business.
  • Integration to FPM (Financial Performance Management) suite and Business Objects BI. The 7.0 releases will contain new functionality and the NetWeaver BI integration, but in the future, SAP is also integrating BPC into the overall FPM suite of products (including Strategy Management and Profitability and Cost Management), and the Business Objects BI toolsets.
  • Customers continue to validate SAP BPC is the right choice. Everyday we are seeing an unbelievable amount of interest in BPC. There are many independent analysts validating the strategy to go forward with BPC, and we are seeing customers are constantly selecting it as their solution of choice: "SAP reported that over the past several months more than 100 customers worldwide purchased SAP® solutions for enterprise performance management with the intention to replace Hyperion solutions from Oracle." http://www.sap.com/usa/company/press/press.epx?pressid=9100
  • Extensive Product Functionality. There are a lot of capabilities in BPC that are really impressive. To name just a few: Work Status, Business Process Flows, Word and PowerPoint integration, the guided Action Pane across all user interfaces, Data Auditing, Dimension Member formulas that save having to create and maintain multiple Structures, Dynamic Templates for reporting, Books and Distributor/Collector functionality, Park'N'Go for offline planning, tight Excel integration allowing for advanced reports to be created using just Excel without any other client tools.

SAP BPC Interview Questions

Saturday, February 18, 2012

SAP Business Planning and Consolidation-Introduction

 

SAP Business Planning and Consolidation, version for SAP Net Weaver is a robust
planning and consolidation application that can meet all of your budgeting, planning,
consolidation, and reporting requirements. It supports the full array of top-down and
bottom-up financial and operational planning needs as well as consolidation processes
necessary to ensure the smoothest, most timely financial close possible — all through a
single application. As a result,you gain the confidence to meet increasingly stringent
regulations and reporting requirements across the globe.

BPC Overview
BPC Administration


The Administration module allows administrators to perform setup and maintenance tasks
for Business Planning and Consolidation applications.
Activities
You can access the Administration module by going to http://<server>/osoft and choosing

BPC Administration

. Select one of the application sets for which you have administrative
rights.You can use the Connection Wizard to refresh the list of application sets, if
necessary.
Note :
If you get a prompt to update files, select
Yes
.
BPC for Office:


The BPC for Office client includes interfaces for Microsoft Excel, Word, and PowerPoint.
These interfaces leverage your familiarity with the Microsoft Office applications of Excel,
Word, and PowerPoint, enabling you to collect, analyze, and store financial data and
efficiently distribute enterprise performance management reports.
The interface for BPC for Excel is  the primary interface you use, complemented by
features of the interfaces for BPC for Word and BPC for PowerPoint.

Features


With BPC for Excel, BPC for Word, and BPC for PowerPoint, you can perform the
following tasks:
•   Utilize the powerful and flexible formulas and functions within reports and input
schedules to retrieve, display, and submit data for a real-time view of the
financial position of your organization.
•  Use predefined report and input schedule templates that you can also customize
to meet your specific business requirements.
•  Instantly change the information you see in a report or the entities, accounts,
time period, and so on of input data simply by changing your current view.
•  Display accurate, live data from the database within Microsoft Excel worksheets,
Microsoft Word documents, and Microsoft PowerPoint slides.
•  Analyze data in reports, perform data entry in input schedules, and distribute
information based on user access rights when you are completely offline from the
system.
•  Submit budgets with a wide range of supporting attachments in the form of
spreadsheets, documents, and presentations

SAP BPC 7.5 Netweaver Frequently Asked Questions

  • TELL ME BREIFTLY ABOUT UR PROJECT?
  • What dimensions u took in your project?
  • Secondary dimensions for entity and category?
  • Mandatory dimensions for currency translation?
  • What is EVHOT, EVCGP?
  • What is offline distribution?
  • What u know about Work status, is parent is highest or owner?
  • Explain your applications?
  • Tell me about budgeting process in your application?
  • What dimensions u used for budgeting?
  • Why u used macros?
  • What method u used in macros?
  • In bi system where do u go and check the code?
  • What is the mandatory thing u need to do in input schedule before sending the data?
  • What is NPV in budgeting?
  • How many tables in DSO?
  • In bi system where u go and check the cube?
  • What is static and dynamic report and when u makes it static and dynamic?
  • Did u use your own packages?
  • How transaction upload takes?

Gold Stone Hyd:

  • What are Roles & Responsibilities you involved in?
  • Business Blue Print?
  • Did you created Application set or Copied from existing one?
  • Max No of Dimensions there in Consolidation application? What are Mandatory Dimensions?
  • Rate & Ownership?
  • Work Status?
  • Admin & Excel?
  • Reference Dimensions?
  • How you create Balance Sheet in BPC by keeping Category in Rows and Time in Columns?
  • Member Set Option( BAS, ALL, SELF)?
  • Where you Implemented Script Logic?
  • How you resolve Loops?
  • Difference Between Webi and Deski?
  • Live Data / Static Data?
  • What is Xcelsius?
  • Aggregate Awareness?
  • Static Data /Dynamic Data which one you build in BPC?
  • Can you do reporting and input schedule on single report?
  • Distribution ( BPC Office)?
  • Publishing?

SAP Manila Interview:

  • What is the mandatory thing for currency conversion?
  • How is budgeting done?
  • What forecast have you done?
  • To what extent you have the data?
  • For how long you went for the prediction?
  • At what level you did the planning?
  • What did you do with the final result?
  • How the retraction is done?
  • Blue Print Stage?
  • Decisions?
  • How u understand the Client business?
  • Managerial consolidations Factors and developed reports?

HP Chennai:

  • Significance of  NEWID?
  • Tell me some Dimensions and their Properties?
  • Tell me about Option Range? What is the Use of NO SEND function?
  • Tell me about Expansion Range (ALL, BAS, Member, Self)?
  • Tell me Currency Translation Set-Up?
  • What is Rate Dimension?
  • EVTIM? EVTRIM?
  • Records added how will they add in BPC?
  • Blue print Off Line Budget Process?
  • Phases & Flow of project?
  • Type of reports you created?
  • Tell me about Cash Flow?
  • Direct Flow and Indirect Flow?
  • Tell me about Script Logic step by step?
  • Do you know MDX?
  • Customer Menu Creation Flow?
  • BPF Security?
  • Tell me about Transformation Files and Conversion Files? Where we will see Transformation Files?
  • What is Flow Dimension? What are Mandatory Dimensions?
  • What is Brokerage?
  • Tell me about Get only range EV Function?
  • How you did Currency Elimination? And Set Up?
  • What is Default Logic?
  • What are Business Rules?
  • What are Custom Menu? Creation & Flow?
  • What are Expert Routines?
  • What are the Data Source types you used?
  • Tell me about SSIS Package?

PWC Mumbai:

  • What are your Primary Responsibilities?
  • Did you Used Scripts? Where?
  • If I delete a Member of a Dimension what happened in transaction data?
  • Tell me about scoping?
  • What is lite optimization?
  • Consolidation process?
  • Inter-company elimination?  Matching & Booking? Why?
  • Reports (Month /Year—5.1) (Month/Year/Day/Week Level in 7.0)

What is SAP BPC

 

What is SAP BPC

SAP Business Objects Planning and Consolidation (formerly OutlookSoft) is a corporate performance management tool that can cater for all types of planning and forecasting, from simple small processes to complex multi layer processes, while also providing consolidation and easy to use reporting.

SAP BPC offers a robust, multiuser platform, which is fully integrated with Microsoft Excel.  Initially leveraging off the power of Microsoft SQL Server and Analysis Services there is now another version that uses SAP Netweaver as its back-end database. Although similar, especially to the end user, these two versions of the product do offer different functionalities (see whitepaper)

The main features of SAP BPC are:

Multi-Dimensional

The multi-dimensional nature allows users to “slice and dice” the data according their needs.

Dynamic

As SAP BPC utilises OLAP technology, this means that data is available as soon as it has been entered – no need to aggregate data!

Workflow Management

Enables an organisation to keep track of its progress through a budget/forecasting process

BPC 7.5 CLIENT INSTALLATION SCREEN SHOTS

 

BPC 7.5 CLIENT INSTALLATION SCREEN SHOTS

Download links for Prerequisite softwares:

Dotnetfx:

http://www.microsoft.com/downloads/details.aspx?FamilyID=262d25e3-f589-4842-8157-034d1e7cf3a3&displaylang=en

msxml:

http://www.microsoft.com/downLoads/details.aspx?familyid=3144B72B-B4F2-46DA-B4B6-C5D7485F2B42&displaylang=en

msxml3:

http://www.microsoft.com/downloads/details.aspx?FamilyID=28494391-052B-42FF-9674-F752BDCA9582&displaylang=en

vbrun60sp5:

http://www.microsoft.com/downloads/details.aspx?FamilyID=bf9a24f9-b5c5-48f4-8edd-cdf2d29a79d5&displaylang=en

vcredist_x86:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9b2da534-3e03-4391-8a4d-074b9f2bc1bf&displaylang=en

.Net framework 1.1

clip_image002[6]

clip_image004[6]

clip_image006[6]

MSXML 4.0

clip_image008[6]

clip_image010[6]

clip_image012[6]

clip_image014[6]

clip_image016[6]

Select “Install Now”.

clip_image018[6]

clip_image020[6]

MSXML 3.0

clip_image022[6]

clip_image024[6]

clip_image026[6]

clip_image028[6]

Select “Install”.

clip_image030[6]

clip_image032[6]

VB 6.0 Service Pack 5

clip_image034[6]

VC++ 2005

clip_image036[6]

clip_image038[6]

clip_image040[6]

Prerequisites installation is finished. Then install client software.

clip_image042[6]

clip_image044[6]

clip_image046[6]

clip_image048[6]

clip_image050[6]

clip_image052[6]

clip_image054[6]

clip_image056[6]

clip_image058[6]

clip_image060[6]

clip_image062[7]

clip_image064[10]

Friday, February 3, 2012

BPC 10 MS/NW - Sending and retrieving comments in an EPM Add-in input form

Introduction

Very frequently, while customers are opening their planning period to entity owners or managers, they would like to attach some comments, explaining their figures.

In the new EPM Add-in, if you use the standard way of inputting or retrieving comments (via the comment button), you are not able to see them directly in Excel cells, but in an Internet Explorer window instead.

In this blog, you will learn how to use the EPMCopyRange formula in order to dynamically send and retrieve comments in an EPM Add-in input form.

Create your Balance Sheet input form

Launch the EPM add-in and choose a valid Planning and Consolidation connection.

Once logged-on, click the New Report button.

New Report button

Drag & Drop PRODLINE dimension in rows, TIME dimension in columns, and all other dimensions, ACCOUNT, CATEGORY, ENTITY, RPTCURRENCY, MEASURES, in page axis.

Report Editor

Click on PRODLINE dimension in row axis, select All Product Lines account and Member and Descendants as for the relationship, and click the OK button.

Member Selector

Then, click on TIME dimension in column axis, select 2012.TOTAL member and Member and Descendants as for the relationship, and click the OK button.

Member Selector

Finally, on your page axis, select Third Party sales member for your ACCOUNT dimension, Budget member for your CATEGORY dimension, United States  member for your ENTITY dimension, LC member for your RPTCURRENCY dimension, and Periodic member for your MEASURES dimension.

Then, move the Total placements on the top right, by accessing the Sheet Options and selecting the Top radio button on the General tab. On the same tab, check the Use as Input Form box, to allow data input.

Sheet Options

Then, on the Refresh tab, select the option Calculate parents in hierarchies (same as the SumParent option in former EVDRE reporting mode), and click OK.

Sheet Options

Your input form should look like below.

Input Form layout

Notice that APL member is calculated on the fly, as soon as you input a value in its children.

Building the EPMCopyRange formula in order to allow comment input in this input form.

Insert 5 new rows on the top of this sheet.

In cell P2, enter following formula:

=EPMSaveComment($O2,,$A2,$N$12,$A$6,$A$7,$A$8,$A$9,$A$10)

Then, choose a grey background in cell O2.

O2 and P2 cells

Then, in cell A1, type-in following formula and press Enter:

=EPMCopyRange("000",TRUE,B2:P2)

EPMCopyRange Formula

Note: “000” is the ID of your Default Report, TRUE is for rows expansion (FALSE would have been applied to the columns), and B2:P2 is the formatting range you’ve just defined for your data range.

Finally, type-in Comment input in cell O11, and Comment send in cell P11, and click the Refresh button.

Input Form Layout

At that point, type-in following comments and click the Send Data button:

  1. APL Comment in cell O13
  2. FPS Comment in cell O14
  3. RPG Comment in cell O15
  4. SPT Comment in cell O16
  5. STR Comment in cell O17

Result should be this one:

Result

Finally, hide column P and rows 1 to 4 for visibility purpose.

Final Result

You now have the last comment being displayed in the rows, following the defined expansion on the rows.

BONUS

You can use the same way of building the EPMCopyRange formula to retreive Work Status in an Excel cell.

In fact, by having your Entities being displayed on the row axis, you can use the EPMWorkStatus formula (instead of EPMSaveComment in the example above), as the source range for your EPMCopyRange.

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.

01_Portal.jpg

When the Administration tab opens, click Dimensions.

02_Dimensions.jpg

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. 

03_Time_Dimension

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

04_Time_Add_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.

05_Closed

Save the dimension by clicking Save.

06_Time_Save

Click Close when the dimension has finished saving.

07_Time_Close

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

08_Time_Open

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

09_Time_Closed_Propertyn

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.

10_Time_Closed_Y

Save and Process the dimension.

11_Time_Save_Process

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

12_Time_Offline

When the Time dimension has finished process, click Close.

13_Time_Close

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

14_Open_Excel

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

15_Environmentl.jpg

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

16_Login

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.

17_New_Report

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

18_Report_Dimensions

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.

19_Category_Dimension

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.

20_Time_Dimension

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. 

21_Account_Dimension

Now that the report layout is complete, click OK.

22_Report_Layout

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

23_Report

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.

24_Current_Context

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.

25_Current_Month

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.

26_Current_Month_Closed

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

27_Actual_IF_Statement

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

28_EVTIM

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

29_Copy

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

30_Copy_2

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.

31_Create_Time

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.

32_Create_Category

Technorati Tags:

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

33_Copy_Dmension_Formula

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

34_Final

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)))))

35_Rolling

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