Avoiding the pitfalls: SAP BW/4HANA on a HANA Multitenancy Database Container (MDC)
The introduction of MDC has opened up interesting architecture possibilities when running SAP BW/4HANA. This could be beneficial but there is a risk of over-complicating your data warehouse solution. In this blog post, Jan van Ansem and Cal Loudon share what they have learned from their experience when implementing a data warehouse solution with BW/4HANA on SAP HANA MDC.
MDC was introduced as a way to deploy HANA in a cost-effective way and to reduce the effort of database maintenance. The idea behind MDC is that a SAP HANA system has one central system database for system administration and any number of ‘tenant databases’. Each tenant database can be used for deploying applications. The BW/4HANA application would be deployed in a dedicated tenant database.
Other applications could share a different tenant database, in the same MDC configuration. MDC allows you to allocate resources more flexibly without the need to change the server configuration. It also allows you to do some maintenance tasks at system database level, so you don’t have to do this for each tenant database separately.
The image below illustrates the relationship between the HANA system, Database Container, Tenant databases, Schemas and Applications (App 1 -6 in the diagram below).
Fig 1. ‘Architecture of SAP HANA Multiple Container Structure’ Source: SAP
More details about MDC in general are in this blog from SAP. That blog is where the above image is from, although it is slightly adapted to show you can have multiple schemas in one database, and multiple applications in one schema.
Enter SAP BW/4HANA
BW/4HANA obviously runs on the SAP HANA platform and to install BW/4HANA you must configure MDC first. You could simply have one tenant database and dedicate this to BW/4HANA. But, should you want to run other applications alongside BW/4HANA on the SAP HANA platform you might benefit from creating multiple tenant databases.
When you run a mixed data warehouse environment, using BW/4HANA and HANA native, you have a choice of how to use your tenant database(s): You can either have both BW/4HANA and HANA native in the same tenant database, or create separate tenant databases, one dedicated to BW/4HANA and a second tenant database for HANA native.
The next paragraph shows a real-life example of a mixed architecture, where BW/4HANA and HANA native were each deployed in their own dedicated tenant database. The reasoning behind this was that initially, BW/4HANA would need most of the system resources. Over time however, the HANA native part would become more prevalent. This set-up would allow the allocation of resources to the HANA native component, without impacting BW/4HANA. On paper, this made sense. In reality, there were some issues, as described in the next paragraphs.
An example data warehouse architecture using two different tenants in MDC
Below in Fig 2. is a simplified overview of the architecture we started with which later changed during the course of the project due to limitations we had not anticipated. On the left is tenant database ‘A’, which is used for the HANA SQL Data Warehouse. The SQL Data Warehouse is used for two different use cases:
- Data specialists, using specialist tools to analyse raw data
- Application Programming Interfaces (API’s), feeding other applications in real-time.
BW/4HANA is deployed in tenant database ‘B’. BW/4HANA is used as a source for traditional BI applications.
Fig 2. Overview of proposed MDC architecture
If the boundary between two tenants is seamless from a data consumption perspective, then this is a sound architecture. Unfortunately, the boundaries between the tenants is not as seamless as we had hoped.
Cross database access and functional limitations
Allowing a user in Tenant A to see data in Tenant B is reasonably straight forward, as long as you work with HANA SQL tables only. You only need to set up remote identities, which is very well described in this blog:
It gets more complicated when you want to read a Calculation View in a different database, but with some perseverance and the help of a blog like this you can achieve this as well:
Adding different SQL objects to the mix will complicate matters though. For example, we have not been able to execute a stored procedure cross-tenant. This might be because of our ignorance, but it seems that this is not possible. As a result, we could not start a remote Smart Data Integration (SDI) process by a BW/4HANA Process Chain. When adding BW/4HANA in the mix there are some restrictions which should be reviewed before deciding on the MDC set-up, so let’s have a look at that.
Text BoxSAP have documented the supported scenarios for BW/4HANA in an MDC configuration (see SAP notes 2121768 and 2312583). According to these notes, you can use a table in a different tenant database in a data source definition and/or as a source table in an Open ODS View. It took me a while to realise that this means that everything else you want to do ‘cross tenant’ is not supported. For example, you cannot include remote Calc Views in Composite Providers.
What was more problematic was the fact that objects from a remote tenant database cannot be transported using the HANA Transport for ABAP (HTA) mechanism. We ended up using different change management mechanisms for (remote) HANA objects and for BW/4HANA objects. This made managing dependencies a lot more difficult and cut-overs (Development to Test, Test to Production) were very difficult to manage.
We also found that some ABAP and AMDP used in transformations became fiendishly complex when calling objects in a remote tenant database.
These limitations of cross tenant database access were not clear from the start and over time issues started to build up. At some point the data warehouse team realised that there were none or very few tangible benefits in having separate tenant databases for the SQL data warehouse and BW/4HANA. However, the issues described above were slowing down the project. It was time to review the approach and make some changes.
From an architecture perspective, it was easy to come up with a solution for all the MDC-related problems: simply move the database schema for the SQL data warehouse from its own dedicated tenant database to a separate schema in the same tenant database where BW/4HANA was deployed. This did involve significant effort, as some code had to be redeveloped. However, the development and architecture teams felt that this one-off effort of merging the two tenant databases was far better than having the ongoing pain of running two separate tenant databases.
Since then, we haven’t looked back. Many annoying issues have now disappeared. The architecture is simplified and means that future developments will take less long to implement. My recommendation for anyone out there considering a mixed scenario SQL data warehouse with BW/4HANA in an MDC configuration, is to keep both parts together in the same database (in different schemas).
This is unless you can identify clear benefits for splitting them over two databases. If you do decide to do the latter, make sure to familiarise yourself with the restrictions which are mentioned in this blog and be prepared for the unexpected. You have been warned.