Consuming complex Datasphere models

[clsorensen911]

Introduction

SAP Datasphere is a part of the new SAP Business Data Cloud offering, which enables a business data fabric architecture that uniquely harmonizes mission-critical data across the organization.

One prime feature of Datasphere is the semantically rich data model, that allows for deep structures of dimensions, hierarchies and transactional data. 
The data models created in Datasphere can then be consumed for reporting by various tools, both SAP and non-SAP tools and used as the basis of reporting.

And while the "Single source of truth" is the cornerstone of enterprise reporting, the individual tools may interpret the data models differently. 

 

Purpose

My main objective in this blog is to show how different tools interprets an Analytical Model and what you may want to consider when building models. 

I will focus on the Analytical Model, rather than consumption models / perspective in this blog, because the end-result is the same, and the Analytical Model is the object that most people will first try to consume for reporting. 

 

Basic concepts

Before we dive in, I want to outline a few basic concepts that is important to understand the mechanisms at play. If you are already familiar with the concepts, you can skip this section. 

Annotations

With the introduction of CDS views, SAP introduced Annotations, which is a tool to semantically enri

ch a table or view with instructions for the analytical engine to interpret the data. 
While there are many, many different types of annotations, Datasphere primarily uses Consumption and Semantics. 

Take the below, very simple table

Material	Quantity	Sales Unit
MAT1	100	KG
MAT2	50	TON

We can add the following annotations to the table:

Column	CDS Annotation
Material	none
Quantity	@Semantics.quantity.unitOfMeasure: 'Sales Unit'
Sales Unit	@Semantics.unitOfMeasure: true

Which does nothing to the table definition itself, but we've told the engine that 'Quantity' and 'Unit' belong together.

Associations

One of the, at least to me, best things that came with CDS views was 'associations'. 

In short, an association between two tables allows the engine to fine-tune the query, based on what you need, without you having to join tables together. 

Based on the associations and annotations, the analytical engine can dynamically navigate the underlaying objects to fit the demands of the user at runtime. 

You can read more about associations in the SAP documentation or one of the many blog posts. 

Semantic usage

When modelling objects in Datasphere, you will have the option to mark a table or view with a semantic usage, which provides some higher level annotations to the object. 

Datasphere allows for the following options 

• Fact - Contains one or more measures and attributes. A fact typically has associations pointing to one or more dimensions and is consumed by analytic models (see Create a Fact to Contain Measurable Data).
• Dimension - Contains attributes containing master data like a product list or store directory, and supporting hierarchies (see Create a Dimension to Categorize Data).
• Hierarchy - Contains attributes defining a parent-child hierarchy (see Create an External Hierarchy for Drill-Down).
• Hierarchy with Directory - Contains one or more parent-child hierarchies (see Create a Hierarchy with Directory).
• Text - Contains attributes used to provide textual content in one or more languages (see Create a Text Entity for Attribute Translation).
• Relational Dataset - [default] Contains columns with no specific analytical purpose.

 

Creating a complex data model

When I use the word "complex", I don't mean big but deep. A data model can have a hundred dimensions, and still be pretty simple.

In this example, I've created a straight forward data model, which the following objects:

Description	Semantic Usage
Employee	Fact
Department	Dimension
Education	Dimension
Job Function	Dimension
Job Level	Dimension

The main object in this data model is the employee, which has several attributes: (1) They belong to a department, have an (2) education, and a (3) job function. 
In addition, each job function has another property: (4) job level, which indicates if it is a management position. 

You can see the relationship in a graphical representation here:

When I say "complex" data model, this is what I mean: Some dimensions have no direct link to the main object - as you might have in a star schema - but can still be consumed. 

In a real world scenario, you can imagine an almost infinite amount of levels and data links. 

Imagine, for instance that we extended the "Education" dimension with the following extra fields: (1) Level (Bachelor, master, PhD, ect.) which also needs it one text, (2) area of study (such as engineering, finance, accounting, etc.), (3) completion date, (4) school of graduation, which may be a whole other dimension all by itself. 

You can imagine how quickly we can add layers to a data model, which might technically be "correct" in a data modelling sense, but can present other problems, as we can see shortly. 

When we create our Analytical Model, we have the option to select the associations we want from our "Fact" object.

Take note of a few things here:

• Some of the fields are marked with a cube, which marks them as a Dimension (See the red squares).
• All the dimensions have a "T" on their line, meaning that the dimension contains both key and text
• Notice the "Date" dimension has a little stair / hierarchy icon as well, meaning that the association also comes with a hierarchy.

As you saw above, our "Job Function" dimension have attributes of it's own, so we can choose that and see that it has two attributes, but only one of them is a dimension:

The final analytical model looks like this:

 

Consuming the Analytical Model

As promised, we shall now see how the Analytical model can be consumed with all the semantic enrichment. I will go through the following tools:

1. SAP Analytics Cloud via a Live Connection
2. Microsoft Excel via an SAP Add-In
3. 3rd Party tools via OData

Consumption in general

I have skipped over a lot of different topics, so I would absolutely recommend the official documentation on consumption to see in more detail. 

SAP Analytics Cloud via a Live Connection

To be able to use this connection, you must have a user in both Datasphere and SAC, and have a working live connection (see Live Data Connections to SAP Datasphere in the SAP Analytics Cloud documentation). 

When you create a story in SAC, or just edit an existing one, you have the option to add a data source:

From here, you can see a subfolder called Datasphere. Select the correct Datasphere and Space:

And select your Model:

Choose Analytical Model

You can now consume the model including all associations and metadata.
While there are a lot to explore, consider these two points:

1. The attribute “Management” is available as a property under Job Function and can be used in modelling
2. While the actual data only contains 1, 2, 3 in the “Department” column, because we have created the association to texts in Datasphere, we can use the texts with no extra work.

Additionally, take note of the "Date" column, where we have both attributes and the hierarchy:

Which is absolutely fantastic! 
No objects are transferred to SAC, it just acts as a tunnel between Datasphere and the end-user. 

However, there is one problem. As you may have noticed, Job Level is not visible as a property of "Job Function", whereas "Management" is. "Job Level" appears as it's own dimension.

SAC displays nested dimensions as their own entity, rather than related to "Job Function". Which may be perfectly alright, but might cause confusion for the people building the reports, that might not know the underlaying data model. 
Imagine you have some sales data with a ship-to and ship-from columns. Both of these might have an "Address" dimension, and may cause confusion. 

Microsoft Excel via an SAP Add-In

The SAP Analytics Cloud add-in (which is not the same as Analysis for Office), can simply be downloaded from the Microsoft Store. 

After installing, you can add the model to Excel, very similar to SAC:

And will interpret the metadata in the exact same way SAC does:

3rd Party tools via an OData Service

Non-SAP products, such as Power BI or Tableau, can consume the data from Datasphere through an OData service.

Setup

You can find other guides to help you set up a connection for your service of choice, but the setup in Datasphere is the same for all tools. 
I highly recommend @gustavokath blogpost on the topic, which has all the information you need to set up your client.

The OData request

Datasphere allows you to build and preview an OData request from the Analytical Model builder:

Which allows you to play and and configure the request and will generate a URL for you:

If you preview the data, you can see what kind of response it will generate. 
However, there are a lot of things to note here, so let's take them one at a time.

1. Datasphere completely flattens the data before it sends it: 

The dimensions are served as both the key, and the text. 
Note that the "Dimension" is served as the technical name of the dimension.
All Text fields added this way will have the suffix "_T", but uses the technical name of the field in the annotation:

Which can make the output difficult to read. 

2. All data is served as either string or number (decimal, integer ect).

This may not seem as such a big deal, until you consider the dates:

And while it can seem like a non-issue, the data itself contains no hint as to the format. 
You can request the metadata and see the type of the field is Edm.Date, which is a nice standard format:

But that only happens because we've specified the column as a Date:

But you can imagine different sources serving data in different formats, which can make consumption and modelling a nightmare, unless you enforce strict data harmonization in Datasphere - which you should obviously do - but mistakes happen. 

3. Nested dimensions are flattened with the business name, not technical name.

There is, however, one absolutely brain-melting thing that I personally foresee causing endless amounts of confusion. 

The nested dimension M_LEVEL, seen above as "Job Level" is a part of the data, but it is represented by the description of the parent dimension:

Just looking at this output, you have a ice cube's chance in hell to deduce which fields belong together. 
Again, you can query the metadata and see the relationship, but you know as well as I do, that at some point, the data is going to end up in the hands of a super-user who has a very specific use case, and will be absolutely floored. 

And yes - you are correct. If I had named my dimensions and fields better, a lot of the confusion could be avoided. Absolutely. That is kind of the point 🙂 

 

Conclusion

The ability to consume Analytical Models build in Datasphere is an absolute godsend. 
It allows you to build central complex, semantically rich data model and allow your users to build their own reports, with whatever tools - to an extent - they prefer. 

However, you as the data engineer have a responsibility to find the sweet spot between a model that contains 'enough', without it becoming too complicated. 

Sometimes, two models might be better than one.