Star schemas and snowflakes – finding data models in SAP BW

Understanding data models in SAP BW is a challenge

Here is another in our occasional series of data models.   This one is a little different, in that it is about SAP Business Warehouse (BW).

Part of the reason I’ve done BW this time is that we get a lot of demand for this capability.   Despite SAP introducing their in-memory database HANA a couple of years ago, BW seems to be as popular as ever.  HANA can dramatically reduce access times for data, meaning that the need to summarise data for reporting purposes is also reduced.   But that does not seem to have made a big impact on the BW market – or not yet.

The challenge of course, as with finding relevant data models in SAP, is to be able to locate the what you need in BW and understand it in the context of the task at hand.

The diagram shown is of a BW InfoCube, which is the multi-dimensional ‘object’ of a BW system.  Whereas the main tables in a SAP transaction system will be largely the same from implementation to implementation, the nature of a BW system is that it provides BI information and the Cubes therein are often created by the customer to address their own particular Business Information requirements.  This adds complexity and therefore to the time and effort needed to find and understand it.

There are, however, a set of InfoCubes that are delivered with the BW system by SAP (sometimes referred to as ‘standard content’) and one of these is a Demo Cube – which is what I have chosen to use for this example.

You can click on the image to download a larger version.

Using Safyr to understand details of SAP BW cubes

Using Safyr to find and visualise SAP BW cubes

The model was created be locating the Fact table in Safyr, pulling in some of the related tables (I will explain some of this below) and then exported to Safyr’s own ERDiagrammer tool.

I can easily make the same set of tables available in ERwin, ER/Studio or PowerDesigner formats – please feel free to ask if you would like to see it in one of those formats.  Or if you have another format in mind I will do my best to provide it – there is no obligation!

The model presented is a Snowflake representation of an InfoCube.   At the centre is the fact Table, and surrounding this are a set of parent Dimensions.  With just Fact and Dimension tables the diagram would be a ‘Star Schema’, but it’s the next level of tables that make it a Snowflake.

These are the BW Characteristics that are related to the Dimensions.  For example, the Organization dimension has a parent characteristic of Company Code (amongst others) and this has an attribute of ‘Country’.   So you can see that not only could you use this cube to report on the Measures in the Cube (SAP calls them Key Figures), but these can be sliced by Company, and the cube has access to the Country for that Company too.

The Snowflake can be extended to include Characteristic to Characteristic relationships too.

Without Safyr, the complexity of BW Cubes in either Snowflake or Star Schemas format can be daunting

If I had done that with this cube, there would have been a bunch of additional tables in the model, one being the Country characteristic with a relationship to the Company characteristic.  I decided to leave that layer out as it would make the model bigger and therefore not so easily shown in a blog.

What we’ve found from our customers is that they find these kinds of diagrams a very good way to explain to users what the capabilities of their own InfoCubes are.

As we are providing something which is usable in some contexts… here is the normal legal paragraph. In no event will Silwood Technology Ltd be liable for any indirect, special or consequential loss arising out of or in any way relating to the use or performance or the use or misuse of the information presented in this model.

You can find other example models from other ERPs in earlier Blog posts.

As always, I welcome any comments, criticisms, effusive praise or other observations.

Nick Porter