Database Design/Modelling on SAP HANA

[Roman71]

Hello Colleagues,

in the past I worked as a "classical" SAP Architect in the ERP, SRM and SAP Portal area mostly with ABAP. My knowledge about modelling relational databases (in the SAP Area) is well. I know about normalization and how to design master and transaction data. I follow the SAP HANA track now since 2013 and really like it (maybe it is already a love). Whenever I have some time between my projects I work with SAP HANA and the surounding tools and technics. So now to my problem. Let´s asume we (I) want to create an application on SAP HANA. How do I model the data compared to a relational database? Can anybody explain how to model master and transaction data table on HANA. The question is about general rules how to model on in memory db compared to relational databases.

Any recommendations, tutorials, and readings are welcomen.

Thanks all for support.

Best regards

Roman

[lbreddemann · Accepted solution]

Hi Roman,

general speaking the relational data design is the same with SAP HANA as with any other RDBMS.

What's different is that with SAP HANA you can implement many functions that used to require intermediate tables (e.g. pre-aggregated or pre-joined data) as views.

This could be plain SQL or column views (information models).

The nice piece about the information models is that you can effectively create a view onto the data that looks like there is a completely different data model underneath. For example you can create analytic views that allow the classic OLAP operations (slice'n'dice, aggregate, filter by dimension etc.) based on plain 3rd normal form tables - straight from the application.

In certain situations you may consider loosening the normalization too, since the dictionary compression of the SAP HANA column store effectively compresses repeating values in a table. So, with SAP HANA maybe you don't implement a separate "type" table for a low-cardinality status/flag column but keep the data right in your main table.

Concerning master and transaction data: SAP HANA doesn't come with built-in functionality to cater for that kind of semantics. You will have to build that yourself in your application.

To SAP HANA it's all just tables.

If you want to learn more about data warehouse modelling than the usual suspect books still apply with SAP HANA.

Cheers,

Lars

[Roman71 · Accepted solution]

Hello Lars,

thanks for your reply. Let`s make an example to make the things more clear to me. I don´t know if you know purchasing tables in SAP ERP. There are two main tables EKKO and EKPO keeping the information about e.g. a purchase order. Table EKKO is the header table storing information that are unique for one order number and table EKPO storing the position data of a purchase order. Of course one header (PO) can have multiple positions.

Let´s assume we create a new system for PO handling on a HANA system would I also design the releation as two tables or would I make one table with all the data? In this case I would have for example the supplier number in each record but this would not mind from technical point of view because the compression algorithm stores the entry just once and only references it.

How would you do it? What would you recommend?

Many thanks

Best regards

Roman

[lbreddemann · Accepted solution]

Hi Roman,

did you already read articles like https://blogs.saphana.com/2014/09/30/how-simple-finance-removes-redundancy-2/ ?

It contains a nice example of the kind of data model change that is possible with SAP HANA.

For your example - a classic parent:child relation - I actually wouldn't change the data model.

Here's why:

• Usually there are many other tables that relate to the header table; handling this in a combined table gets more tricky.
• Very often the line items/positions are not even of interest, so having all data in just a single table would increase the amount of data to be touched for these queries.
• "Empty" orders, that is orders that have not yet any position assigned, would need to be modeled by some encoding. Which is semantically less clear than simply defining two separate but related entities.
• Important rules like "every order position must belong to an existing order" are easier to check and enforce when you work with two separate entities.
• Header Item changes would need to be propagated to all positions during update. This means you need to lock all of the positions and also prevent new positions from being added while the update is working. That's just not required with the parent:child approach.

It gets interesting when you consider that there might be an aggregated figure on order level (e.g. total order volume).

Now this could be - if the business logic permits it - calculated on the fly instead of storing it with the header information.

Hope that gets you a bit further on your discovery journey.

- Lars

[Former Member]

This message was moderated.

[Former Member]

Hi Roman,

i'm assuming you have already went through this document: http://help.sap.com/hana/SAP_HANA_Developer_Guide_for_SAP_HANA_Studio_en.pdf

it's fairly recent and contains a lot of information for anyone with access to the latest SMP client download and a HANA server. it's a great primer to 3 opensap classes run by Thomas Jung and Rich Heilman. in it, both sales and purchase order objects, but not (yet?) the GL (artifacts) are used for the SHINE example,

Lars also has a great book out: SAP HANA Administration. of Richard Bremer, Lars Breddemann - by SAP PRESS but it's rather focusing on keeping the system running efficiently.

those two resources should keep you busy for some time to come.

thx,

greg