Thanks to everyone who joined in our author Q&A with veteran SAP Financials consultant Peter Jones of MI6 Solutions.
Peter is a Financials Expert advisor, the author of the article “Build a Report Using SAP BPC 10.0's EPM Add-In Functionality" and a contributor to the Financials Expert anthology Best Practices for Financial Reporting in SAP.
Review the entire discussion in the chat replay and the edited transcript below.
Gary Byrne, Financials Expert: Welcome to today’s Q&A on improving and updating your financial reporting for FI-CO, BW, and SAP BPC.
I’m Gary Byrne from Financials Expert and I’m pleased to introduce Peter Jones, who will be taking your questions today on options, including SAP BPC and SAP EPM, for improving financial reporting in your SAP systems.
Peter Jones of MI6 Solutions is a Financials Expert and BI Expert advisor and author, and a contributor to the new Financials Expert anthology “Best Practices for Financial Reporting in SAP.” He’ll be here for the hour to take your questions on rethinking spreadsheets and updating your financial reports.
Peter Jones: Hi, Gary. Thanks for having me for this session.
Gary Byrne: You’ve written a few articles about the EPM add-in. Any quick notes or updates on the EPM add-in for FI-CO users as well as SAP BPC customers, and where it can help update financial reporting?
Peter Jones: Absolutely. There are a number of interesting opportunities for using the EPM add-in for non-BPC infoproviders. We can also see that SAP is starting to make inroads into integrating the EPM add-in with the other reporting tools available.
For example, there will be a change around Q2 of 2015 where the EPM add-in will become a part of the Analysis for Office component. I’m not sure which one will be the main focus of this effort, but I can say that this will become the main frontend for BPC activities.
Gary Byrne: I’m sure we’ll have some questions on this – and I know we already have some questions waiting for you. We’ll let you get to those now.
Comment From Harish: When working on EPM reports, I have observed that we have to use a lot of Excel functions/macros for complex reports. These sometimes seem to interfere within the EPM reporting area. Are there any suggested guidelines on how to use both EPM functions as well as Excel functions in a report/input template? Is SAP planning to add more functions to the EPM tool?
Peter Jones: When I’m working with business users, one question seems to always come up: “How do I do a specific function or process within the EPM Add-in?” My answer: Always remember that the EPM add-in is an Excel spreadsheet. There are many activities within the EPM add-in that you accommodate by using basic Excel functionality. This is very different than if we were working with BOBJ components or BEx — in these cases, we mostly look to the actual toolset to give us the approaches to solve specific issues. In the case of the EPM add-in, the solution is to use Excel. So, to your question, there are definitely quite a few times where we use Excel functions.
I have used quite a bit of macros/VBA/vlookups/etc. within the EPM add-in and yes, there have been times where these solutions impact the reporting area. For example, I have had issues keeping the formatting correct when I’m using some of the macros and VBA, but after tweaking the code I’ve been able to resolve the issue — maybe not to the extent that I would like, but definitely to achieve the required results.
In another situation, the VBA code was locking up the Excel spreadsheet, but adjusting the timing of the different calls of the VBA code helped fix that issue. I have also run into times where the Excel formula is more complex than the EPM add-in can handle and turns into a “local formula.” For example, I was able to get an IF/THEN statement to convert to a local member formula, but I added a bit of additional Excel code to do a search on values column by column. In that case, the EPM add-in would not convert that formula to a local member formula, so I had to go with the Excel formula and adjust to make sure that it covers the entire columns values. It didn’t look as pretty as a local member formula would look, but it did the job.
I can’t say that there are best practices or suggested guidelines for the integration of EPM functions and Excel functions. But I would say that using the EPM features would be the first layer I would look to for solutions. EPM functions and the FPXML functions would be the second layer, and then finally look to Excel for the solution. For example, you may need to use reference information in a report for calculations or formulas. I would position the reference information in the same worksheet so that I could use the EPM functions rather than putting it in another worksheet and then having to use an Excel reference worksheet-to-worksheet to reference the data. This way, you use EPM functions before reverting to Excel functions.
Comment From Guest: In a trading business, can the company have profit and loss account based on principal customer?
Peter Jones: Yes, we see this quite often, but more based on LOB or Regions, or even customer groups. Having to align a P&L to specific customers is just a bit different.
We don’t have an issue with the mechanics or design of the data and reports, but we need to understand what “principal” means to this customer. If we have 10,000 principal customers, then this may cause an issue with data volumes, but if the total number of principal customers is around, say, 200 or less, then we can definitely accommodate this level of granularity.
Now, this is based on a non-HANA system. If HANA is being used, then there are no issues with the total data volumes, since the HANA platform is stored in-memory and can easily be retrieved and reported on for more involved reporting needs.
Comment From Jozsef Zerczi: Our users are asking more often for drilldown capability to the details. What is your best practice for that when using BPC?
Peter Jones: In this case, I’m thinking you are talking about the idea of drillthrough versus drilldown — drilldown being the ability to double-click on a hierarchy, for example, and see the lower-level values show up in the report. This feature is very mature in all of the reporting components of SAP except for some limitations within Crystal to accommodate hierarchies for allowing drilldown.
In terms of drillthrough, the functionality within BPC with the EPM add-in is pretty good, and is about the same as we would see in either BEx or other BOBJ components. The setup is not really available for the business user, as many of the other functions are, and this may have a bit of a drawback, but overall the drillthrough ability is very good.
I will say that the standard drillthrough from an EPM add-in component to the BEx-level components is a bit of a challenge. There are two different reporting toolsets to get comfortable with and to understand the nuances of. So, initially the drillthrough starts with the user executing the function from the EPM add-in, and then the drillthrough report is a BEx web-based report.
Therefore, for the first-layer drillthrough, you need to understand EPM and the BEx web. If you decide to go with a second-layer drillthrough, if you are heading to ECC you will have to be familiar with the ECC reporting components as well.
I find the challenge for this feature is where the data is available – knowing that we may have summary data in BPC for the EPM add-in, and the more detailed data in BW. The next layer is the challenge. Where’s the very detailed data — in BW in a DSO or in ECC? Once that decision is made, then the setup or design of the drillthrough can be achieved.
There is also a limited amount of drillthrough if you use the EPM add-in against non-BPC objects such as BW infocubes or other providers. But it is possible to execute a drillthrough to the first layer of information from the EPM add-in.
Comment From Guest: Will migration to BusinessObjects affect the EPM add-in?
Peter Jones: Migrating to the BOBJ components will not impact the EPM add-in. Currently the EPM add-in is not really thought of as part of the BOBJ suite, but that will change next year.
Originally I was told that the EPM add-in would be set up as a link in the Analysis for Office toolset, but now the thinking on the SAP side may mean you’ll have one reporting tool with the combined functions of both Analysis and EPM. At that time, if you were migrating then, we would probably have additional components to install.
Comment From Andrea Zenner: We will be implementing BPC next year. We haven’t started digging into what dimensions/models we’ll need, but our Basis people need to have an idea for sizing when setting up our sandbox. Is there a way to get an estimate for sizing for our Basis folks?
Peter Jones: Yes, there is definitely a process to help with the sizing. It’s great to see that you are focusing on that topic now, since in a number of situations I’ve seen this question goes unanswered, and the sizing done only on the BW system just isn’t enough to accommodate both the data volumes and the activities within the BPC process.
There are a number of sizing documents available, but make sure that you use the one most consistent with what you need — currently, there’s a sizing document just for BPC v10.0. In the 7.5 version, the sizing document was more generic to BW, but in v10.0 there’s one that addresses BPC 10.0 needs.
Look for BPC_Quicksizer_110612 — this is one of the latest available. Hope this helps!
Comment From Nathan: When thinking about how to integrate our current EPM BPC reporting into a more standard enterprise reporting environment, such as BusinessObjects, are there any best practices that should be observed? What have you seen many clients doing to balance the question of EPM vs. BI toolset reporting?
Peter Jones: Very interesting question. Using the EPM add-in in conjunction with the other BI toolsets for reporting, including BOBJ and BEx, is relatively new, and it’s becoming more and more of a challenge.
Of course, due to the background and origin of the EPM add-in, I am seeing more in the area of FI. That makes sense, since the FICO business users lean toward the Excel-based toolsets for the calculation engines and features, which allow tons of functions and analysis. Now that the EPM add-in will become part of the mainstream of reporting tools, we will be seeing this question more and more.
Comment From Touria: How many times do we have to refresh BPC during month-end process?
Peter Jones: Good question. That would depend on several items. Would there be any reporting in the interim of the month end close process? If so, and it’s being done by another reporting toolset, then you would have to “refresh” so that a) the data can be optimized, and b) that request can be closed for the other reporting toolset to recognize the data.
I would say it depends on the data volumes as well — in one case, the closing process was handling over 13 million records and we had to optimize multiple times during the close — but with my current customer we only refresh after the close is over.
Comment From Guest: Can we use the EPM add-in component if I don’t have BPC or any of the EPM Suite?
Peter Jones: Good question. Currently the EPM add-in is not available without having either one of the EPM suite components, which includes BPC. It’s a shame that is the case today, since I’ve worked with BEx and some components of BOBJ and this is the most integrated reporting tool with all the functions of Excel that I’ve experienced with SAP. I’ve been involved with a “bake-off” with Essbase and the EPM add-in, and the EPM component ended up being better at more functions then Essbase. So, it would be great to have it as another reporting option outside of just the EPM suite. In 2015 we will have that opportunity since it will then be integrated with Analysis for Office and a part of BOBJ.
Comment From JPP: In BPC, can we create and report with a Calendar hierarchy within the time dimension after having created a Fiscal Year hierarchy?
Peter Jones: Interesting question. Remember that BPC is a bit different than BW in that BW has multiple TIME infoobjects, so that we can accommodate very quickly the use of both Calendar and Fiscal Year and can even work them both together with a little user exit for reporting. In BPC, we only have one TIME dimension — it’s a bit trickier, but possible. We would have to create a unique TIME hierarchy in the TIME dimension, but this may require more time members to accommodate.
For example, I wanted to have the QX/2014 as a postable value. In BPC, we can’t, due to the fact that QX/2014 is a node level. With TIME, it’s not too bad due to the limited # of values. So, I created additional TIME members and made them base level.
This is an example of what can be done. In your case, you may be able to continue to post as you are but have a property on the FISCALYEAR to denote Calendar year. Then, using the EPM function, call up the property in the report. This may not get you all the way, but it could offer you ideas of how to achieve it. I’ll see if there’s a way to follow up and I can prototype this one for you.
Comment From Guest: I’m new to BPC and would like to know if there is a difference in reporting functionality between the Microsoft and NetWeaver versions?
Peter Jones: Good question. If you look at BPC V10.0 for MS versus NW, there’s no real difference between the two core reporting functions. If you look a bit closer, though, you can see that there are some EPM functions that are available only in MS versus NW. That’s the normal lean — MS has a few additional or extra EPM functions than the NW version. All in all, they are about the same. A feature may be in a different location in the options tab, but they do mirror image each other very closely.
To give you an example of a difference: In the NW version, via the Webservices link, we can create “local member formulas,” whereas with the MS version, we create “global member formulas.” The difference is that the local is specific to that report whereas the global you can share across reports.
Comment From Rimma: My question is actual spend vs. budget analysis during month end close. Is BPC the right tool for that? Presently, we pushed FI budget back to SAP (we do not yet have drilldown capability activated in BPC) to facilitate these types of analysis. However, we are not sure if this was the best approach.
Peter Jones: Good question. This is actually something that many companies struggle with in terms of what to do with the data after the planning or budgeting process is complete. The answer to this question has changed quite a bit during the v10.0 and 10.1 process (versions of BPC). In the 10.1 version, using the embedded version, you don’t have to push anything back, since the objects and infocubes used are the same as in BW. So if you are pushing back the data to an FI Cube in BW, there’s no need in the embedded version.
Now, in the standard version of BPC V10.0 or 10.1, the question would be: Do we upload the actuals to BPC models for reporting purposes and support this redundant data load, or do we push back the data and then do the analysis in BW for the FI analysis? That, in my eyes, is a wash since we have to move the data anyway (aside from using HANA). So it comes down to the reporting toolsets and what is best for the analysts.
I’m always of the opinion that we execute reports on the data in the original table. Therefore, I lean toward keeping the data in BPC models and integrating the BW data either a) via an upload into a BPC reporting model, or b) use the functionality of the EPM add-in, where you can marry up data within the reporting toolset without moving the data. This, of course, would require that the master data and granularity of the data be consistent across the two tables.
Again, there is a push to move the data back to BW from what I’ve seen of multiple projects, but we have to be convinced that it’s the only way before doing the “retraction” process.
Gary Byrne: Peter, thank you so much for joining us here today. And thanks, everyone, for joining today’s Q&A. You can read Peter’s articles in Financials Expert, as well as our recent articles on financial reporting. You’ll also find Peter’s pieces in our newly published Financials Expert anthology “Best Practices for Financial Reporting in SAP.” Thanks to everyone for great questions and discussion, and thank you again, Peter.
Peter Jones: I would like to thank you and everyone that took the time to log in and post questions. All great questions and I hope I was able to offer some guidance.