Part one – The classroom problem child
Part two – How the problem child gets along with the other children
Part three – The class protégé
Part one – The classroom problem child
SAP Business Warehouse (BW) is often perceived as a difficult, anti-social, arrogant and even an aggressive problem child. BW does not want to interact with the other children. BW has some peculiar quirks, which may be difficult to understand or even accept. BW comes across as a little dimwitted (inability to process data on-the-fly) and requires a lot of looking after. Additionally, BW eats its food (data) in a different way to the other children. Due to these oddities, BW is a bit of an outcast, and hasn’t really been accepted as part of your company’s IT landscape.
The aunt
SAP BW (even BW/4HANA without a HANA schema implementation) is that one unruly child, present in every class. This problem child is a bit of mystery to you, the teacher, and sometimes does things that are out of your control (~features). Regardless, this child has learned quite a lot (i.e. Business Content data model) during his first years. The child’s mother tongue is a mystic multidimensional language (over BICS connectivity), which you as the teacher do not speak or understand. The child’s parents also make no effort whatsoever to teach it to you. As a result, BW is near impossible to communicate with. Only one of its relatives (SAP Competence Center) can understand it on some level.
Fortunate friendships
Fortunately, our problem child has made some friends along the way. These friends (reporting tools) can interpret the child and allows us to open a dialog. The first friend is called Web Application Designer (BEx/WAD) — our problem child’s oldest friend, a well-travelled senior citizen. Unfortunately this geriatric friend might not be with us for much longer, as retirement is just around the corner. She – the only person who fully understands our cryptic problem child. Other friends include some younger children who are frequently over to play. Analysis for Excel, is very skilled, direct and easy to communicate with. Spontaneous and fun to play with, our problem child gets along with this friend quite well. However, our problem child has grown up to be somewhat shallow, and is ashamed to introduce this new friend to any of your friends. Analysis has a best friend called Design Studio (second name Lumira 2.0 Designer). However, this friend is rather high maintenance, requiring a lot of our problem child’s time (pixel perfect mouse tuning, write a lot of Java Script code, external add-ons and custom charts).
Classy classmates
There are some other kids in school that our problem child interacts with. Lumira/Lumira 2.0 Explorer, for example. Lumira was originally taught to speak one of the local language (SQL), and even tried to learn the mystic mother tongue of our problem child, albeit unsuccessfully. An older classmate, WebIntelligence, comes from the same part of town as Lumira. The youngest kid is SAP Analytics Cloud (SAC). However, we know close to nothing about them, as they have only recently transferred to this school and we are yet to see if SAC is willing to talk to BW.
In practice, this means that our problem child’s oldest friend, the old lady, has not been able to retire yet, and probably half of the classroom (organizations) still use her to communicate with our problem child. However, they know that they should do something as she is getting ready to retire. The other half are talking to BW via its other friends. The inherent problem here is that these children have not developed their vocabulary fully yet and do not possess the same amount of skill as the old lady. To make matters worse, the kids in the other classrooms seem to be more well-rounded (PowerBI, Tableau, Qlik) and are playing nicely with their classmates (non-SAP and non-BW systems).
Part two – How the problem child gets along with the other children
One major challenge is that BW speaks a strange internal language and we cannot understand it. As a workaround, we are using SAP’s reporting tools as the interpreters (our kid’s friends) to communicate with it. However, to our advantage, this child also speaks a second language, which can be used with third party tools.
The challenge is that these other generic interpreters (PowerBI, Tableau, Qlik) speak the same SQL-language natively as SAP Lumira and SAP BO WebIntelligence, but our problem child does not. BW is unable learn this language, as its brain processes everything as a cube. Conversly, the SQL language can only be understood if you are able to express yourself as a square (tabular tables). The situation however, is not hopeless.
There is new way of thinking in Town
During last decade, the cube model way of thinking was common (Cognos PowerPlay, Microsoft Analysis Services, Hyperion Essbase, etc). They all had their own mystic proprietary languages, but they also adopted one common language (Microsof’s MDX over OLE DB for OLAP), which BW also adopted to some extent. As time progressed, this cubic way of thinking started to grow old, and was soon replaced by the tabular way of thinking by almost everyone except BW. Due to this legacy, the modern generic interpreters (PowerBI, Tableau, Qlik) understand the MDX language too, but as there is little need for it any more, the interpreters do not even try to provide a full support for talking MDX over OLE DB for OLAP.
But as BW has some unique features, which do not have expressions in the common language, and interpreters do not understand all of BW’s expressions, the following are problematic to interpret:
- In BW ID and description always are bound to the same “field” and SAP’s interpreters have a feature allowing users to select what combination to display, and in what order. This is a fundamental concept of BW and not supported by other tools.
- The multi-language support for descriptions. At the time of login, a user selects the language to be used during the session. The other client may ask to fill this information on every query.
- It is common to use hierarchies everywhere in BW. In general, the SQL-centric tools cannot utilize pre-created hierarchies coming from the server. Typically they try to flatten them and then recreate them internally with limited success. As they are not as hierarchy centric as BW, they commonly have issues to even visualize hierarchies (especially in the filters components).
- Issues to support programmatically modifying report metadata during the execution. For example, changing column names on-fly based on user inputs (variable), given at the beginning of execution.
- If showing of the prefixes of so called “compounded infoObjects” is not necessary/valuable, SAP’s Bex/WAD interface automatically hides them. As an example, the fiscal year is show as “2016” instead of a compounded fiscal year variant and fiscal year “K4/2016”. In many cases, visibility of the “compounded prefix” is confusing and annoying. Similarly the annoying controlling area value is displayed even in the case when there is only one controlling area.
There are no easy solutions for these issues. In many cases the answer is to avoid or at least minimize their usage. Instead of having definitions of these in BW/Bex, you can build them at the level of the end user tools. Unfortunately, many of these features come as part of the pre-made data model (the business content) and un-building them is not always such a simple task – especially if you do not have HANA tools available.
The picture below illustrates how the features of the different tools just do not match if you have traditional BW or even un-optimally designed BW/4HANA. You just need to make a compromise and decide which features you value the most.
If you are using BW 7.5 or older, with or without HANA, and you are using any of these third party reporting tools, be careful in using the extraction mode that copies data out from BW. If you want to get a copy of your data, you need to pay for it (Open hub license).
Problem child & language selection
In a well-designed HANA powered BW the usage of the third party tools can change dramatically. You can design it to speak fluent SQL parallel to the traditional languages described above. Or you can design it even as a generic DW and support only the SQL language. The latest BW/4HANA wants to be play nice in other ways too, and does not even require the Open Hub license for the data copies either. If you just upgrade your old system or design it only in the traditional BW way with SAP Business Content, it will not speak SQL, and you will have exactly the same issues as described above.
Bring the food & let it eat
We have talked about how BW is a kin to a problematic child, and how communicating with it is problematic (at the reporting end). Similar challenges exist at the other end – how it ‘eats’ (data). Any other ‘kid’ in your IT landscape exposes a database table with fields. You then push your data into the table. If you want to use this push method with BW, it does not, as you might guess, understand the standard SQL language. Instead you need to learn its proprietary languages (iDocs, Bapis, and other ABAP paradigms). Fortunately, these write-in APIs are public and documented, albeit very SAP-specific knowledge. In this case, you can use a third party ETL tool, like Informatica for example.
Picky kid
These tools provide a varying level of support for the required ABAP interfaces, and you don’t need to write the API code by yourself. This idea of feeding BW with data is bit complex any way. The easier and more common approach is to bring the food (data) in front of BW and let it ‘eat’ by itself. From a API standpoint, this method is referred to as ‘pull’ instead of ‘push’.
Even with this pull approach, our problem child is very restrictive about what it is willing to eat. He never accepts a single core piece (field) of data, like Customer ID. Instead, you may need to provide BW with a dozen other pieces (fields) of data at the same time, telling it what this core data is (metadata). For example, this metadata may include: a short description of Customer ID, a medium length description of Customer ID, a long description of Customer ID, a place in the Customer hierarchy, several attributes of the customer, like age and so on.
And worst of all, before loading the data, you will need to define what metadata you are going to define for the Customer IDs. This is called InfoObject-based modelling. It’s not an issue for SAP sources as there are automated adapters producing the required information and mapping it correctly. In most external data cases, this infoObject based modelling is far too robust, complex and a time-consuming process, and therefore the BW systems mainly eat and process SAP ERP data only. By this analogy, our problem child is also an introvert.
However, in HANA powered BWs even this data-in approach gets significantly easier, with the help of better tools, and a so-called field-based modelling approach (especially when non-SAP data is introduced).
Part three – The class protégé
I am sorry to say that this troublesome child you have tried to teach and understand, is a lost cause in the big picture. Even if this problem child were able to graduate from school as a teenager (migrate HANA to BW/4HANA platfrom), the child will never learn to utilize his new skills in an optimal way. There is far too much complexity inside him. Sooner or later, you need to accept that and pick a new protégé – the straight-A student.
The Earth is not Cube
So why is this new kid so different? You need to understand that your old BW is an application, and an application only for you. I talked about how the BW application processes everything as a cube, and this practice still continues in the BW/4HANA application. This cube brain is the reason it can never behave in an open, and friendly way towards other systems, and the generic reporting tools (the interpreters). Ultimately, deep inside BW’s brain is a database engine (Oracle, or even a fast IBM DB2 Blue) with the squared tabular-formatted hidden database content. To be exact, this cubic format is actually an application level in-memory mirroring of this tabular origin. The key difference between traditional BW and HANA-powered BW is following:
- Traditional BW: The underlying relational database is totally hidden from you, and you enrich and expose data with SAP’s non-SQL centric ‘cubic’ BW features
- HANA-powered BW: The underlying database, and its SQL-centric tools are suddenly completely open for BW developer to enrich and expose the data. The improved BW application tools are still available for the BW developer, and each step of the modelling can be done on either side and mirrored back.
This can be illustrated as follows:
Even though the pre-made BW business content is only available on the BW modelling side, the assumption is that you also use the database tools, as they are much more efficient. It enables you to remove the internal complexity bothering the traditional BW systems. In the previous part, I emphasized how it is critical to provide data access for third party tools with native SQL language support. If you do not use database modelling to expose and enriched data to the SQL channel, you don’t get much additional value from your system. Be aware that many do not seem to understand or know the database side modelling at all, and you end up just getting a faster horse, even though you wanted to switch to a car.
SQL interface & HANA Views
I need to admit that the challenges related to BW’s special features listed in the previous part of the blog still exist when using the tabular SQL modelling in the HANA database. This is due to the fact that SQL does not understand the concept of hierarchies. However, it is much easier to invent workarounds on the SQL side than in the BW modelling. With BW4/HANA, you can still connect third party tools to BI Queries (rebranded Bex query with a new developer tool), using OLE DB for OLAP, but I strongly recommend the SQL interface using HANA Views.
You can also define and publish cubic representations for Analysis for Excel and Design Studio (combined as SAP BO in the previous picture) from HANA database views. These features are still a bit behind the BI query, and therefore the BW modelling is the proposed approach for them as drawn in the picture.
Report access
With regards to report access, the open database modelling significantly simplifies feeding data into the HANA-powered BW. As illustrated below, SAP HANA Runtime Edition for Apps and SAP BW license (HANA REAB), the mandatory prerequisite for BW/4HANA, includes a build-in ELT and replication tool, the SAP Smart Data Integrator (SDI). As data is brought into the database level, it resides in simple field-based SQL tables. If you have a powerful enough HANA license, you can create these tables manually without SAP tools, and use an ETL tool of your choosing to load data with standard SQL and ODBC driver – like you would do with MSSQL Server or Oracle. Even on the BW modelling side the “HANA Source System” shown in the picture is a so-called openODS view, and it does not know and require complex InfoObject modelling. In other words, the non-SAP sources can follow a simple field-based modelling approach throughout the system, and the SAP sources can use the traditional, richer InfoObject modelling.
If you need to merge the content from both sources, it can be done in a very simple way using Composite Providers (illustrated in the previous picture).
All in all, this means that SAP is finally following IBM Cognos and Microsoft, who started to emphasize SQL/tabular based modelling a decade ago. Personally, I think SAP has done this in a more integrated and usable way than its rivals. As the HANA powered BW 7.4 and 7.5 already included the majority of these new.
So what could you do? And what should you do? At the very least check out our whitepapers on the subject:
- The most relevant topics to go through when moving on from your old SAP Business Warehouse
- 10+1 Things to consider on your road to self service analytics
Read other blog posts about the topic:
Antti Lyytikäinen: Life after SAP Business Warehouse?
Mikko Koljonen: Scam alert: SAP BW users WATCH OUT!
Lauri Puolanne: Data warehouse – a burning platform?
