Describing a Custom SQL Data Warehouse

Launch the video below to get started.

SAP provides three solutions for data warehousing.

Two of these solutions are considered 'out-of-the-box' approaches to data warehousing. These are SAP BW/4HANA, for on-premise, and SAP Datasphere, for cloud. With these solutions customers can get started immediately because they provide content in the form of ready-to-use data models and data flows that already connect to a large number of SAP applications. These solutions also come with tools such as schedulers and monitors that are needed to run the data warehouse.

SAP BW/4HANA and SAP Datasphere are very popular with customers who already run SAP applications, such as SAP S/4HANA, SuccessFactors and Ariba. One of the key reasons is because the out-of-the-box solutions provide predefined connectivity to the data sources. For SAP BW/4HANA and SAP Datasphere, all the customer needs to do is to activate the provided content for the data flows they want to use, and begin scheduling the data loads.

The basic architecture and templates for these out-of-the-box data warehouses are already defined by SAP. When you build your data warehouse using the out-of-the-box solutions, you must stick to the predefined architecture and templates that are provided. Imagine building a house. The individual rooms are already pre-built from a template. All you have to do is select the rooms that you want to include, and click them together. If you need more rooms, just start with a template. With this modular approach you will have a finished house in no time, but it will not be a custom design.

Now imagine you are building a house starting with a huge pile of bricks. In this case you can decide exactly how the house will look and do not have to follow any predefined architecture or templates. It will certainly take a lot longer to complete the house and you will have to think especially about the type of architecture you want to use. This approach usually consumes many resources along the way. But the house will have been designed to your exact requirements. The 'build-from-bricks' approach is what we mean by a custom SQL data warehouse. You decide every single aspect of the design. It really is like building a custom house to your spec. It can be as simple or as sophisticated as you need.

This course covers the custom SQL data warehouse approach.

But why would a customer want to start from the beginning when SAP provide ready-made solutions with templates that are faster to implement?

There are many reasons why a customer would want to develop a data warehouse from the beginning and not use a ready-made solution. These include:

• In some industries there are standard data models that must be used to comply with legal and regulatory requirements. For example, in the health-care industry, patient data must be managed in a standard way across all health care providers in order to facilitate communication and ensure data security. The data models of SAP BW/4HANA and SAP DataSphere are already defined and these may not align with industry specific data models.

• Customers may already have many of the components of a data warehouse and they want to continue to use these. For example, they may already have powerful ETL tools and data extraction pipelines in place. But, but they would like to take advantage of the latest in-memory technology of a the SAP HANA database.

• Many customers already have large teams of database developers and they want to use their experienced resources to develop a custom data warehouse.

• SAP standard solutions are based on a denormalized approach using dimensional / star schemas data models to improve analytical performance at the cost of data redundancy. There are however, alternative data models that could be considered, such a data vault, and even normalized models used by transactional systems such as second and third normal form that reduce data redundancy and improve stability of models in frequently changing environments.

• The out-of-the-box solutions can be customized, but the extent of the customization is limited. For example, adding missing fields or calculating new field values is possible. But sometimes a fresh start is a better approach when the requirements do not fit well.

The out-of-the-box solutions from SAP do not require the skills of coders. The key skill is knowing how to configure the ready-made pieces and work with the templates.

But why do we sometimes include SQL when we refer to a custom data warehouse?

SQL is a popular language used to build a custom data warehouse. SQL is the most common database development language and is adopted by most database vendors. It is extremely powerful, mature and easy to get started and is well-supported with online resources to help develop skills quickly. Experienced SQL developers are usually easy to find.

Using SQL, you can build all the pieces of the data warehouse, including the data structures (tables and views), the processing logic to handle the data flow, and build procedures to automate the tasks of the data warehouse, such as data loading, cleansing, and housekeeping. With SQL you can build tools for monitoring and raising alerts. SQL is built-in to most databases so additional tooling is usually not needed.

Before we leave this lesson, we should add that some customers don't choose just one data warehousing approach. They sometimes combine the three solutions. For example, they might develop a custom SQL data warehouse to supplement their SAP BW/4HANA or/and SAP Datasphere data warehouses by adding custom features or building data marts. Mixing the solutions is known as a hybrid approach.