BI reporting on SAP Solution Manager data

[TomCenens]

Reflecting on past thoughts

I remember about three years back when i was involved  in a Solution Manager upgrade/implementation project, I advised a consultant from a Solution Manager consulting firm to dig into custom BW reporting on top of Solution Manager. In retrospect it's always nice to see advice was actually good advice. More and more customers are reporting on Solution Manager data nowadays so he should have had a head start on that by now already.

Revisiting reporting options

Not so long ago, I needed to do the same since we proposed at customer side to automate some statistical reporting which was generated in a manual fashion still. In the past I had done some discovery of potential options and some work on specific reporting (availability through ccmsping in a custom SLR for example) but not yet utilized custom queries. So I started again by looking at what's possible nowadays, some releases further down the road.

My first idea was to use custom queries in SLR (Service Level Reporting) in Solution Manager but that didn't work out as well as I had hoped. It's too restrictive and too slow to make quick adjustments and generate a result. Since I don't have a publishing platform (BusinessObjects, portal, ...) at hand at this customer on short notice I had to reside to an alternative option. You can see in above screenshot that it did work but you cannot build really advanced queries or anything so in the end it didn't have the requirements I was looking for.

In the end, I ended up using the Bex Query Designer and Bex Query Analyzer. Why? AnalysisOffice requires a specific license which is not in place.

I've played around with AnalysisOffice during ASUG preconference (at SAPPHIRENOW just recently) and it's indeed better / more interesting then Bex Query Analyzer.

Combining Bex Query Designer, Bex Query Analyzer and Microsoft Office, I was able to generate a PDF based report which meets the requirements of the customer in terms of reporting on KPI's. As a next step I will be looking at publishing options and leveraging the BI suite (Web Intelligence or alikes ...). You can read some related posts as I leveraged learning hub, read about that here and I tried out BW connectivity to SAP Lumira.as well which you can read about here.

Some challenges along the way:

• The exception in Bex Query Designer is hard to leverage for building the graph in Excel. So I resorted to a calculated key figure matching the exception value so I have another column in Excel which can be used to build the graph.
• I used an offset function to define the area where the data resides to have the graph dynamically build depending on the available data ~ some months are longer than other months and reporting on the previous month means you need a dynamic graph
• Performance wasn't optimal ~ I leverage a number of Bex SAP notes to change some parameter settings to optimize the loading time and I also set the query refresh to single dataproviders in order not to be forced to refresh the whole workbook each time
  • I need to look into this further and potentially leverage caching as a way to speed things up further or try to use aggregation up front
• Figuring out what's what isn't too easy, especially if you don't have a BW background :smile: so I spent some time on figuring out the key figures and how SAP calculated them to come to a specific result. Help.sap.com helped to some extent on the infoproviders and key figure description info (you need to know in what something is expressed also for example so check the content first ~ms versus second for example makes a big difference :wink: ).

In the end, it has been super interesting already to work this out and I look forward to do more around BI reporting on SAP Solution Manager based data. SAP Solution Manager 7.2 is approaching and as it approaches, I'll be checking what options we've got available there for custom reporting.