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

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

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

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

Create CLOSED Property on the Time Dimension

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

When the Administration tab opens, click Dimensions.

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

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

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

Save the dimension by clicking Save.

Click Close when the dimension has finished saving.

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

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

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

Save and Process the dimension.

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

When the Time dimension has finished process, click Close.

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

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

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

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

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

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

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

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

Now that the report layout is complete, click OK.

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

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

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

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

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

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

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

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

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

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

Technorati Tags:

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

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

Bonus:

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

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

Robert Marshall   is a member of the BPC CSA team

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

EVDRE Memberset Selector in Treeview

In SAP BPC 7.x EVDRE you may want to filter members according to both attribute values and/or member selections. You can find these complex selection examples in "Usage and Considerations of EVDRE" document like "SELF,DEP and ACCTYPE="INC",ID=Account:SalesKorea". For some of end users it may be complicated to hardcode this selection string into EVDRE Dimension Memberset field.

For one of our clients we decided to leverage MS Excel VBA functionality to achieve easy selection of dimension members in an EVCVW functionality fashion. Of course you can argue we may achieve this functionality with defining hierarchies in dimension but I personally do not like to have many many hierarchies, and prefer flat dimension structure.

Lets start with a EVDRE(1x1) in "Sheet1" which has material dimension in row expansion. As you can see Memberset is by default SELF,DEP.

In Sheet2 prepare a EVDRE for only ROW expansion listing Material dimension and place properties which you want to construct hierarchy based on these properties. In BPC MS you can also use EVLST function but since in NW version you do not have this functionality it is better to use EVDRE.

In Excel VBA Editor start with inserting a UserForm.

You can use default controls like combobox, listbox, checkboxes but if you want to use Treeview you have to add Treeview control from "Additional Controls..."

From "Additional Controls" list you can add Microsoft TreeView Control, version 6.0

Place Treeview and two command buttons into your UserForm.

First command button will construct treeview from a selected region in Sheet2.  VBA coding will be similar to the below one:

Dim nodItem As Node
Dim rangeaddress As Range
Dim level1 As Range
Dim level1name, level1key, level1text As String
Dim level2 As Range
Dim level2name, level2key, level2text As String
Dim dimmember As Range
Dim dimmemberkey, dimmembertext As String
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
TreeView.CheckBoxes = True
TreeView.Nodes.Clear
Set nodItem = TreeView.Nodes.Add(, , "ROOT", "ALL")
nodItem.Expanded = True
level1name = "GRUPICI"
level2name = "YAYINEVITEXT"
Set rangeaddress = Worksheets("Sheet2").Range("B138").Cells
For Each c In Worksheets("Sheet2").Range(rangeaddress.Value).Cells
Set level1 = c.Offset(0, 1)
level1key = level1name & "=""" & level1.Value & ""","
level1text = level1.Value
Set level2 = c.Offset(0, 2)
level2key = level2name & "=""" & level2.Value & ""","
level2text = level2.Value
Set dimmember = c.Offset(0, 3)
dimmemberkey = "ID=""" & c.Value & ""","
dimmembertext = dimmember.Value
On Error Resume Next
TreeView.Nodes.Add "ROOT", tvwChild, level1key, level1text
TreeView.Nodes.Add level1key, tvwChild, level2key, level2text
TreeView.Nodes.Add level2key, tvwChild, dimmemberkey, dimmembertext
Next

Second Command Button enables user to write selected nodes into a cell seperated with commas. Code for second button will be like this:

Dim filterdestination As Range
Set filterdestination = Worksheets("Sheet1").Range("A2").Cells
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
Dim FilterString As String
For Each tnode In TreeView.Nodes
  If Not tnode Is tnode.Root Then
    If tnode.Checked = True Then
     FilterString = FilterString & tnode.Key
    End If
  End If
Next
If Len(FilterString) > 0 Then
  FilterString = Left(FilterString, Len(FilterString) - 1)
  filterdestination.Value = FilterString
  Unload UserForm1
Else
  MsgBox ("Please Select Material")
End If

Now our UserForm is ready for users to call, so place a Shape object in Sheet1 and assign a macro to this shape.

When you click on shape object you will get a hierarchical representation of Material dimension like this

After you select dimension members and attribute nodes and click on commandbutton2 as you can see comma seperated memberset is written to destination cell specified in code.

Now you can pass these selected values to EVDRE Memberset field. In case of no selection you may want to use EVCVW option, so using an IF formula for checking empty selection will be beneficial.

SAP BPC allows us to use flexibility of Excel, in this example I have tried to show a simple solution. I hope developers may add some simple but handy functionalities in coming service packs.

You may want to examine a simple macro example without EVDRE's, download here

Best Regards

BPC 10 for NetWeaver Authentication Scenarios

Overview

One of the major differences in BPC 10 for NetWeaver from previous releases is that all client to server traffic from the EPM Add-In (Office client) and Web client go through NetWeavers WAS (Web Application Server) as opposed to the .NET server.  Since the web server performs authentication services, this change brings new options and security considerations to the table.

Client Communication Overview

As I alluded to earlier, there are two primary ways users access BPC 10:

1. The EPM Add-In which is the new office client.
2. The BPC Web client which has been totally redesigned in this release.

Both clients access content in BPC by querying RESTFUL web services, which is similar to previous versions – just a different format.  Why am I going over all this?  The different clients each support different authentication mechanisms, in addition to the web services themselves.  The chart below provides an overview of what is supported where.  We will cover each scenario in more detail later on.

Basic Authentication

Basic authentication is used in the EPM Add-In by default and results in a prompt for your username and password during each login.

This authentication type is not secure unless using SSL as usernames and passwords are only Base64 encoded.  Due to this, SSL should always be used when using basic authentication.

HTML Form based Authentication

HTML form based authentication is used by default by the BPC web client and results in an HTML form that prompts you for your username and password.

Like basic authentication, form based authentication requires SSL (HTTPS) to be secure “across the wire”.   Due to this, SSL should always be used when using form based authentication.

Client Certificate

Client certificates are supported by both the EPM Add-In (for BPC 10 NetWeaver connections) and the BPC web client.  Users are not prompted for credentials when using client certificates, and as such it provides Single Sign On capabilities.  The configuration of client certificates is beyond the scope of this blog; however they basically work like this:

1. The BASIS team installs an SSL certificate on the BW instances and enables the HTTPS protocol.
2. The BASIS team then maps X.509 certificates to BW users (using STRUST, etc).
3. The security or networking teams deploy user specific X.509 certificates to end users’ desktops.
4. When a user executes a request, the client (either the EPM Add-In or web browser) verifies that it trusts the server certificate, and the server verifies that it trusts the client certificate allowing each side to validate each other’s identity before carrying out the request.

Client certificates must be installed in the end users’ desktop certificate store to be used with the BPC web client or EPM Add-In.  Additionally, you must enable client certificates for the EPM Add-In connection by checking the Client Certificate checkbox and selecting the appropriate certificate in the connection manager.

This authentication mechanism is convenient since it delivers SSO but comes along with more overhead then the other scenarios, since the X.509 certificates have to be maintained and deployed to end users systems.

SAP Logon Ticket

SAP Logon ticket’s allow users to obtain a ticket (which is stored in the form of a MYSAPSSO2 cookie) from one system and use it to authenticate to other trusted SAP systems.  SAP Logon tickets can also be used to generate reentrance tickets, which can be used by applications other than browsers (like the EPM Add-In) for authentication purposes without prompting users for credentials.

This makes a number of single sign on scenarios possible.  For example:

1. A user can login to the EPM Add-In from the BPC web client without being prompted for credentials using a reentrance ticket.
2. A user can logon to an SAP Portal instance, connect to the BPC web client (as a new page in Portal Content) and launch the EPM Add-In while only having to log in once – at the Portal.
3. A user can logon to an SAP Portal instance and launch the EPM Add-In directly while only having to login to the portal.
   • Note – This doesn’t work out of the box, but I was able to create a relatively simple web application that can be deployed in an AS JAVA portal that enables this behavior.  It will be posted to SDN as an HTG soon.

Like the basic and html form authentication models, it is crucial that all communication occurs over HTTPS to ensure that the MYSAPSSO2 cookie is not compromised.  This scenario also requires that the BASIS team configure the required SAP systems to “trust” each other.

SAML 2.0

SAML 2.0 authentication is not used directly by any of the BPC clients at this time, but is supported by the BPC Web Services.  SAML 2.0 may be valuable for integration and custom development scenarios.

Suggestions…

I have but one suggestion and if you’ve made it this far, you probably already know what it is.  Whatever authentication mechanism(s) you chose to deploy, ensure you enable and use SSL / HTTPS.  Your network security auditors will thank you.

Making The Case for Investing in Financial Excellence

One of the fun aspects of working closely with a wide range of retailers is the friendly competition and passionate sharing of ideas that highly competitive retailers show when they have the chance for dialog. It's always fun to listen to companies like Under Armour and New Balance as they highlight their differences but then also dive deep into what they can learn from each other's internal processes.

This week 30 current and potential customers from 16 companies met at SAP's Newtown Square campus to share best practices in driving Financial Excellence using SAP's Business Planning and Consolidation solution. The highly focused two day event gave a sharp and vivid snapshot of how some of retail's most innovative companies are using BPC to transform the role that their finance functions play.

Financial Excellence for Retailers

Making the case for excellence is always a challenge for any supporting function in a retailer. Executive leadership and key stakeholders rightly focus transformation close to the customer, where the connection between investment and increased sales or increased GMROI is clearly visible. What this week's discussion showed though is that flexible tools like BPC can drive real transformation in finance and enable finance functions to provide much stronger value to the business.

Here are a few examples of how customers are using BPC to dramatically improve their own processes, better inform the business and deliver tangible results:

• New Balance reduced their monthly financial close from several weeks to just a couple of days
• Given that more than 40% of a typical retailer's SKUs are unprofitable based on Net Margin Return on Investment level - merchants need to see this data and use it to transform their assortments
• Even with a complex landscape of legacy systems, Charming Shoppes is able to deliver full store P&Ls to each of its 6,900+ stores to empower store management
• No business can grow 300% without changing the way it plans - in the middle of the Nook's unexpectedly dramatic growth, BarnesAndNoble.com was able to deploy BPC in just seven weeks so that it now has the ability to plan and adapt successfully for its new world
• By assessing total cost of risk at store level, Family Dollar is able to drive highly effective loss prevention
• Under Armour is now able to complete a bottom-up budgeting cycle in just two weeks instead of 8 weeks
• Leading multi-banner retailers using shared service organizations for key back office functions can provide the business with clear visibility on shared service usage and costs to enable them to control their shared service costs

Flexible and Intuitive Tools Deliver Ongoing Transformation

In addition to the significant value customers have been able to deliver, one of my other key takeaways from the event was that the BPC tools are so flexible and intuitive (thanks partly to the familiarity of Excel) that both finance and business users are constantly pushing to use them more widely and expand their use within the company.

Successful and flexible Enterprise Performance Management empowers executives, finance, IT and the business to focus on what they do best.

• Executives get rapid and actionable insight that they can use to drive strong performance.
• Finance has robust tools that allow their operational processes to be streamlined and enables them to invest in developing new insight, improving the strategic dialog with the business and driving incremental performance.
• The business no longer has to wait for IT or finance to let them access new data or detail and can get the answers they need when they need them.
• And IT... gets to put a solution in place and see the business vigorously engage and want to expand it to new areas, contribute to its administration and own the data in a way that can take years to achieve with other solutions.

it's very much to the credit of SAP's partners who focus on implementing Enterprise Performance Management and BPC that so many customers at the event were able to enthusiastically share evidence of how their organizations continue to extend using the strong foundation their implementation partner had put in place. Throughout the two days, customers mentioned that the expertise they had relied on from Aster Group, Column5, The Glenture Group, Sandpoint Consulting and SAP Consulting had been one of the most crucial success factors in their successful transformations.

Now Make It Real Time

Looking into the very near future, Steve Townsend of SAP gave a vivid outline of how SAP HANA will enable customers to take the scattered legacy of data sources that they have to aggregate, transfer and consolidate today and provide amazing fast access to real-time data with no aggregation. Combining this power with tools like Sales Analysis for Retail, a great foundation of retail intelligence available today using POS data feeds, will transform the speed at which customers can respond to the dynamic retail environment of rapidly changing consumer preferences, highly volatile demand, and long, risky supply chains.

What if every financial report and dashboard in your company was in real time? Replay your last major holiday event or promotion - if the lessons learned in preparing for and executive the event could have been identified and responded to in real time, think how the results could have been significantly improved.

Find Out More

More details on SAP BPC can be found here and any of us on SAP's Value Engineering team would be keen to work with you and your customers on developing the case for implementing or extending your use of SAP BPC.

SAP BusinessObjects Operational Performance Management Elite Enablement - Spend 3.0, Supply Chain 2.0, Data Enrichment 1.0 - Sep 26-30, 2011, Palo Alto

BE THE FIRST TO HAVE LIVE SPEND 3.0, SCPM 2.0 and DEC 1.0 EXPERIENCE at the SAP BusinessObjects Operational Performance Management Elite Enablement.

After successful Elite enablement sessions for BPC in US, Shanghai and (soon to come) in EMEA and a great PCM 10.0 Elite enablement session in Palo Alto, we felt that is was time to extend the offering to the Operational Performance Management (OPM) suite that is part of EPM 10.0:

• SAP BusinessObjects Spend Performance Management / Spend Analytics 3.0 (SPM)
• SAP BusinessObjects Data Enrichment and Classification 1.0 OnDemand (DEC)
• SAP BusinessObjects Supply Chain Performance Management 2.0 (SCPM)

This action-packed week conducted by OPM product and solution management and Customer Solution Adoption (CSA – former RIG), will provide customers and partners invaluable "hands-on" exercises and information to ensure you hit the ground running with the latest OPM suite. In addition to mingling with the solution experts you will also have the opportunity to meet our OPM executive leadership team.

This Elite enablement workshop will cover a large variety of topics including:

• Day 1 – Hands-on walk thru of new features in SAP BusinessObjects Spend Performance Management 3.0 (SPM), SAP BusinessObjects Supply Chain Performance Management 2.0 (SCPM) & SAP BusinessObjects Data Enrichment and Classification OnDemand (DEC) including What-If-Analysis, Advanced Analytics and Risk Management Integration
• Day 2 – Product deep dives covering User Interface configuration, data sourcing and integration from SAP ERP, integrating Spend and Data Enrichment & Classification
• Day 3 – Product deep dives (continued): Extensibility of SPM data model, integration of External Factors, Risk management integration and related applications configuration, Integration of SPM & BPC
• Day 4 – performance tuning SAP NetWeaver BW 7.3, SCPM, SPM, DEC, Backend Modification to extend out-of-the-box functionalities (JAVA and ABAP Extensions), standard transports, install, security
• Day 5 – SAP In-Memory (HANA), best practices project setup, documentation, issue handling, project scoping

Also hear from the experts on the roadmap and future of OPM and OPM with HANA – SAP's in-memory technology.

We will distribute iPads to the participants to support the exercises.

Space is limited so register today! We will be limiting space per company in order to ensure we can meet the demand from all partner companies, so we encourage you to respond quickly to ensure your registration.

What other Elite enablement is coming up?

A BPC 10.0 MEGA Elite training completely on BPC 10.0 NW is planned for Nov 14-18, 2011 in Philadelphia. For the first time this will be open for over 100 customers and partners to go hands-on and dive deep into BPC 10.0 NW. Register here. Contact liz.fitzgibbons@sap.com for details and follow us on Twitter at #BPCElite or directly @SAPEPMRIG and @jenskoerner

Also SAPinsider is hosting a 3 day BPC bootcamp seminar in Chicago, Orlando, Amsterdam, Las Vegas and Philadelphia for customers and partners. For details check out www.bpcbootcamp.com and http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/24378

Debugging BPC 10 for NetWeaver

Debugging in BPC 10

In my opinion the ability to debug is one of the biggest advantages in the BPC for NetWeaver platform.  It has proven to be a huge asset when writing custom logic (BAdi’s and custom integration scenarios) in addition to general troubleshooting tasks.

The good news is that the latest release of BPC for NetWeaver (BPC 10) also allows you to debug the BW tier but there have been a couple of changes that you need to be aware of:

1. The .NET tier has been removed from the 10.0 release of BPC for NetWeaver.  This means you don’t have to map accounts on the .NET tier to enable debugging anymore.
2. The entry point for debugging has changed

After reading this (short) blog you will be prepared to debug BPC 10 for NetWeaver to your heart’s content.

Let’s get started…

Setting up your debug user

The only thing you need to start debugging BPC 10 is a valid BW user account with the same access as the BPC BW System Account.  You can find the required roles in section 4.1 of the Installation guide.

Once that is complete, grant the user the appropriate rights to the environment / model (application set / application) and set your breakpoints.

Where do I set my breakpoints?

There has been a pretty good amount of rework in the UJ package for BPC 10 and this has also impacted where you need to set your breakpoints.  I’ll cover two different scenarios below, debugging EPM Add-In and Administration functions and debugging data manager packages.

Debugging EPM Add-In and Administration functions

1. Log into the BPC client you want to debug (EPM Add-In or Web Administration console) with your BW debug user account.
2. Go to the portion of the use case you want to debug, but don’t execute the action yet.
3. Log into SAPGUI with your BW debug user.
4. Go to transaction SE80
5. Navigate to the class CL_BPC_REST_RES (under package UJX, embedded package UJX0).
6. Go to line 56 of the HANDLE_REQUEST method and set an external breakpoint.
   • Note – If you know the specific location you want to debug you can set the external breakpoint there and skip the breakpoint mentioned above.
7. Switch to the BPC client and execute the action you want to debug.
8. This will launch a SAPGUI debugging session
9. The case statement on line 60 will get you to the code you need to evaluate.

Debugging Data Manager Packages

1. Log into SAPGUI with your BW debug user.
2. Go to transaction SE80
3. Navigate to the class CL_UJXD_PACKAGES_RES (under package UJX, embedded package UJXD).
4. Go to line 179 of the DO_POST method and set an external breakpoint.
5. Switch to the BPC client and execute the data manager package you want to debug.
6. This will launch a SAPGUI debugger
7. Double click on the ‘ls_package_run-if_debug’ exporting parameter and set its value to true (represented by the character X).
8. You can then continue to any downstream external breakpoints you already set (for instance, in your BAdi) or can step through the code line by line.