Hello SCN-Community,

As mentioned in my first blog „BRFplus in Big Data scenarios“, I want to share some other topics related to processing mass amounts of data. Faster than I expected, I’ve solved a problem regarding a dialog for operational analysis. It’s about topic 3 of my last blog:

“Exploratory search techniques to determine anomalies in invoices – how can we support the end users during their daily work? Can we use ALV grid to analyze data models with master-detail relationships?”

In this blog I want to describe a kind of analysis that can be compared to “looking for a needle in a haystack” or “exploring for something”. If you mention something unspecific and you don’t know what to call, is it worth a try to build a data model in Business Warehouse, for example? How to describe this data model? To build such a model, you have to do some exploratory searching first. This work is done mostly by an export of the related data and playing around with Excel or some kind of statistic software.
If you gained the knowledge about a specific fact in your data and you want to keep track of this constellation you may want to build a check function to reproduce it at any given time. – We do not want the users to leave the system to do their work.
That’s what the blog is all about. How could the user be supported by an exploratory search for anomalies in an operational analytics scenario without leaving the operational system? How could we place the facts of a completed exploratory search into a check that can be used to reproduce the search?

Introduction

We start with the previously discussed business case: Processing a huge number of invoices sent to a statutory health insurance company.

A set of checks is being processed every time new invoices arrive. Every check focuses on a specific aspect or constellation of an invoice that produces clarification cases that have to be analyzed by the end user. But these checks were developed due to a specific concept or aspect. So they target the most common known anomalies in these invoices. What about other constellation that have not been considered before?

One solution to this problem may be a dialog in which the user can do some kind of pre-analysis to look for new patterns to identify anomalies, errors or even detect possible fraud cases among those invoices.

Requirements

This task sounds easy. You take the invoices to look at and put them into an ALV grid. As a result, you can use all filters and sort criteria of the ALV grid. Your are also you are able to save your “query” for reuse as a user-specific layout.

At this point we need to raise some important aspects:

  • We have to deal with approximately 28,000,000 invoices a year for a single health insurance company. The number of invoices does not fit into an ALV grid.
  • Each invoice is supported by some medical documentation. We have to be able to search the invoices for the existence of a specific medical treatment, surgery, and so on.
  • These filter criteria have to be saved like usual ALV variants for reuse purposes. Unfortunately we can’t use the normal ALV variants since we are dealing with master/detail views. So we have to develop different techniques, which are main topic of this blog entry.
  • The user must be able to see all of the data of a specific invoice, even the detail data.
  • Our solution cannot be addressed to HANA-specific techniques only. We have to support a kind of HANA-readiness without facing a solution with two different lines of code.

In this case we are talking about a generic solution. With this tool, the user is able to do an exploratory search about billing errors in our scenario without moving into a Business Warehouse.

Introducing our UI prototype

The fact of the matter is that we cannot build upon HANA-specific Features, we can only chose a solution with a standard ALV grid. We split the screen of the dialog into two sections. A master view with one ALV grid for displaying the invoices and a detail view containing three ALV grids corresponding to three kinds of supporting documentation.

BALV.jpg

If you double click an invoice, all of the detail data is loaded into the corresponding ALV grids.

Handling of mass data

We decided not to load all invoices into the ALV grid to avoid overloading the master view. During an exploratory search you are not interested in each single invoice. Rather, you have to find another filter to reduce the search result – to give your interest boundaries. For now, we are talking about an extract of the data (called “Ausschnitt” in the screenshots) and the entire data of the underlying table (called “Gesamtdaten”). The size of an extract is defined by the user. With the size of the extract the user decides how many invoices have to be displayed and thus are being transferred to the front-end. It’s a constraint to define the maximum amount of data. To gain this kind of truncation we added a new button to the ALV grid.

Count_Button.JPG

“Sätze” stands for rows or even invoices which definess a kind of truncation. The given filter and sorting criteria of an ALV grid operates on the internal data table of the grid – so it only addresses the extract but operates very quickly.

If we want to reduce our search result effectivelyy the filter and sort criteria are to operate on the whole underlying data table. Due to that we added some other functionality to the grid to control the ALV filter and sort actions.

Sort_Buttons.JPGFilter_Button.JPG

  • „Sortierung Ausschnitt“ : Sort the extract in the front-end (ALV grid standard)
  • „Sortierung Gesamtdaten” : Sort the data in the backend (database) and rebuild the internal table (of the ALV)
  • „Filterung Ausschnitt“ : Filter the extract in the front-end (ALV grid standard)
  • „Filterung Gesamtdaten“ : Filter the data in the backend and rebuild a new internal table on that result (of the ALV)

With the help of these new functions we are able to search for various facts in a huge amount of data. If the amount of data to be displayed is too big, we only see the tip of the iceberg due to the truncation.

Detail filter – How to deal with them?

The search by detail data was a bigger problem to deal with. An ALV filter only operates on its own data context. But the detail data is separated in additional ALV grids. So we built a popup with additional filter criteria addressing the keys of the medical documentation we have to deal with.

Detail_Button.JPG

Detail_Filter.JPG

The constraints of the detail filter are handled by a manager class of our master view. Such a constraint is defined as a single Select-Option. This manager class builds upon these constraints a SQL query. We built all combinations of master to detail relationships as an EXISTS clause for each detail relationship. We counted 8 valid combinations (guess 2^3 😉 ) so the SQL queries can be hard coded in the manager class.

Detail A

EXISTS (MEDICAL

TREATMENTS)

Detail B

EXISTS (ICD)

Detail C

EXISTS

(SURGERIES)

X O O
X X O
X X X
X O X
O X X
O X O
O O X
O O O

Another problem was taking care of a detail filter while saving an ALV layout in the master view. If the user defined a detail filter it has to be saved together with the normal layout. We solved this by creating an add-on table holding the detail filter under the same key of the ALV layout. To do this ,we added some functionality in to the event handling of the ALV layout button. When the user loads a layout the detail filter is loaded too so the original search result is reproduced.

With these extensions to the ALV grid we realized a master to detail relationship with standard ABAP coding to allow the user to do exploratory searches in the whole data volume.

Advanced techniques

With this solution we are not at the end. Due using a Select-Option to define a detail filter, we are not able to do all kinds of filtering. We suggest keepingtrack of all invoices containing the medical treatment A und B except C or D. With a Select-Option, we are only able to select via OR and not via AND.

Additionally, we want to transform such a saved layout (with detail filter) to a customer defined check function which is being processed when a new invoice arrives. Under this condition we were faced with a major ABAP limit:

The 8 hard coded EXISTS clauses no longer match the new requirements. So we have to deal with a dynamic WHERE clause which does not currently support sub queries. We hit a dead end.

Without trying to use HANA-specific functionalities the only solutions we mentioned were the use of native-SQL or the generation of ABAP coding. We decided to choose code generation. Our manager class is able to transform a saved ALV layout with an applied detail filter into an SQL query. This built query is embedded into a generated ABAP class that could be used in our operational analytics framework.

What should be improved? – A wish list / Subject to Change

During the development of this dialog (which is based on the ALV grid class CL_GUI_ALV_GRID on NetWeaver 7.40 SP4), we also examined the use of the ALV-Grid with IDA (HANA-Grid class CL_SALV_GUI_TABLE_IDA on both NetWeaver 7.40 SP4 and SP6). We also noticed the availability of this grid on none HANA systems. That’s a very important fact. This capability may help us to rely on only one codebase. We do not need to separate HANA-only features in another codebase which has to be switched out on non HANA systems. Unfortunately the current version of the HANA grid doesn’t give us the ability to influence the handling of the grid the way a normal ALV grid does. We need the ability to take care of the grid’s internal table of data storage in non HANA scenarios. Another solution could be a refresh function by the grid. By calling the grid’s refresh function, a new SQL query is sent to the database with the defined filter and sorting criteria. Also we need the ability to define the truncation.

After solving this, we are still not ready to use the HANA grid due to the fact that we are not able to manipulate the WHERE-clause. Well in the meantime I think the solution will be a master-detail-relationship based on HANA-features. But this doesn’t help us in our case.

Due to our business scenario, our solution has to be used by both, HANA and non HANA customers.

As I described earlier, we use code generation to avoid native-SQL. But the code generation doesn’t help us in case of the HANA grid. Remember we do not have the ability to query the database on our own.

Summary

With our solution, we are able to support the user with an exploratory search technique. It’s a kind of generic solution that does not rely on further development once it is transported to the customer. Due to the fact that the user never wants to jump from one System to another (for instance to jump into a BW system, do an snalysis there and jump back to his operative application), he is able to build a check out of the combined filter criteria to integrate it into the previously defined set of checks that are processed when a new invoice arrives.

This prototyping is not at the end. Some technical problems must be solved to build other features on top of this approach. But these problems are tough because they depend on needed extensions to OpenSQL (sub queries in dynamic WHERE clauses) or to the HANA grid.

The general availability of the HANA Platform features is not only a huge gain of speed but also of additional functionalities to operate on Big Data. But first we have to lift the customers to that platform. I suggest it is only done by building hybrid solutions that handle of the base business cases which run faster and give some view of benefits if run on HANA. To minimize the effort, we have to rely on only one codeline that we have to support.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Tammy Powlas

    Hi Daniel –

    Thank you for another thoughtful blog.

    I assume this is an SAP ERP/ECC system?  Could you use the BW system inside your ERP box to access the necessary data “real-time”?  See What are Transient Providers?   See if you have BW inside your ERP System

    Starting with EHP5 you have BW inside your ERP system.  Transient providers are an option; I know there are improvements with enhancement pack 6 too.

    In theory you could access the data using a BEx query and use either Lumira to explorer or create an ERP universe on the data and have BusinessObjects “explore” the data.

    Not sure if this fits in your scenario but to me this is what operational analytics is.

    Tammy

    (0) 
    1. Daniel Ridder Post author

      Hallo Tammy,

      I noticed your blog about Transient Providers. It’s really great and I use this technique already in another project. But I think the use of a Transient Provider is not the right technique for this Business Case. Remember we want a real online approach. With the BEx tools, I have to place every variant of the analysis in a separate report.

      With the discussed ALV mechanism I’m able to filter online without hopping to BEx Query Designer and back. I will see the results directly after applying my new filter. If I found my personal setting, I can save it and even translate it into ABAP code to use it as a check function. So the defined queries write back to the operative data.

      That’s the important fact we have to implement. Do some analysis and write the result back to operative data or trigger some actions based on the queried result on the operative data. Do you have some suggestions doing this with ERP BI tools? That might be really great.

      Cheers

      Daniel

      (0) 
      1. Tammy Powlas

        Hello Daniel,

        Good question – my understanding is the only tools that write back are Lumira and Design Studio – but I don’t have personal experience with that.

        I will have to think some more about this to give you a better answer.

        (0) 
  2. Tobias Trapp

    Hi Daniel,

    I like your approach using ALV/IDA in an analytics because it has two innovative ideas:

    • you extended the capabilities of an ALV to a relational data model
    • you persisted complex selection criteria which is very interesting since you can reuse the selection when you analyze different data set but you can also use it to pass it to batch calculations or visualization tools

    Moreover you gave an outlook to the IDA-grid.

    But I have a question: ALV is a very complex control available in ABAP Dynpro and WDA. The table control in SAP UI5 is not that powerful compared to the former ones – so what ALV features are most urgent and should be available SAP UI5 in your opinion?

    You have much experience with the traditional ALV and in your blog you showed in your use case room for improvement both for ALV and IDA. But can you imagine other feature that could make ALV more useful? Maybe a completely new and different feature?

    Best Regards and keep on blogging,

    Tobias

    (0) 
  3. Thorsten Franz

    Daniel,

    An exceptional blog – it’s always a great delight to read about good craftsmanship in the ABAP space. Thank you for posting this!

    Thorsten

    (0) 

Leave a Reply