Creating a Semantic Layer in Snowflake for SAP Data

When I started working at Snowflake, I noticed that there is a huge interest from SAP customers to build a modern data warehouse on Snowflake. SAP has it is own technology which is mainly based on ABAP and Netweaver. Thus, it is not very straightforward to extract data from SAP and consume it with 3rd party applications. Furthermore, once you extract and load data into target systems, you will end up with complex and difficult-to-understand data structures.

So, when we look at a typical Snowflake project where SAP is a data source, we see 2 challenges;

1. Extracting data from SAP
2. Understanding the SAP data model

You can find several ways/tools to extract data from SAP. Here my colleague David Richert explains most of these methods.

On the other hand, making data understandable and usable may require more effort because of the complexity of data models and the difficult-to-understand table structures.

SAP tables usually have 5-letter column names abbreviated from German descriptions of those fields. As you will expect those fields will not make any sense unless you are an SAP expert.

Here we have an example of the material table (MARA) with 244 columns. In a typical SAP project, you may have hundreds of these tables and if you need to build a business-friendly layer with proper field names, it may easily take days if not weeks.

SAP MARA Table

How Can We Get There Quickly?

I started to think about an automated solution to build a business-friendly layer with properly named columns which can be understood easily by data engineers, business analysts or any user who needs this data.

I ended up creating a stored procedure in Snowflake which leverages SAP’s dictionary table (DD03M) to create a view on each SAP table which is already ingested into Snowflake.

Basic Architecture of the solution

Basically, this solution creates a view for each table that you selected from the raw SAP tables. It finds the matching definitions from the DD03M table for each column of the raw table and creates a view with this enriched information.

Example of a raw table and automatically created view based on this raw table

How To Use SAP View Generator

• Ingest your Raw tables from SAP to Snowflake, including the DD03M table.
• Create the stored procedure in your Snowflake system. Here you can find the code.

SAP-View-Generator-for-Snowflake/SAP View Generator.sql at main ·…

• Run the stored procedure as shown below. You can provide multiple tables as input and the SP will create a view for each table that you provided.

CALL sp_generate_sap_views(
        array_construct('BSEG','MARA'), -- list of raw tables
        'SAP_PLAYGROUND.RAW', --source schema to read SAP tables from
        'SAP_PLAYGROUND.TARGET', -- target schema to create views
        'SAP_PLAYGROUND.RAW.DD03M', -- SAP Dictionary table DD03M
        'E' -- Language
    );

Easy and Quick!

Instead of spending days to create a semantic layer, you will be able to create a simple one, automatically and in a couple of minutes. Now you can focus and spend your valuable time on creating the data marts as explained by John Gontarz in his blog.

Views and opinions expressed in this article are my own and do not represent that of my place of work. I expressly disclaim any liability or loss incurred by any person who acts on the information, ideas or strategies discussed in my stories on Medium.com. While I make every effort to ensure that the information I’m sharing is accurate, I welcome any comments, suggestions, or correction of errors.