Data Modeling in SAP Data Warehouse Cloud
Data Modeling is a crucial step for you to be able to get the most insights out of your data with SAP Data Warehouse Cloud, so it’s important that you clearly understand what it means.
A data model is a way to organize the data and define the relationship between the data elements you have, to give it a structure. This structure must be aligned with the way your team needs to generate reports and queries. Data models also are a way to document how your data is organized, so that the engine behind your data warehouse can retrieve data faster whenever needed.
It’s also very important to consider that a data model will shape the insights you can extract from your data warehouse, as it will either highlight the connection between different data elements or completely ignore it if this association is not created in the model. That is why every business needs its own data models, as each model should be driven by a business need to associate data elements and see the results.
Part of the data modeling process is to define your facts, measures, and dimensions. There are many types of data models, with different types of possible layouts. What’s important to understand is that the data models you can build on SAP Data Warehouse Cloud are logical and physical data models.
Logical data models allow you to determine and connect specific attributes of data. For example, this is where you would make sure the data about your customer is complete with full name, street name, city, country, and ZIP code, among other data points about each customer.
Physical data models determine how the data is physically stored, for example, in which drive it is stored.
All data models can be visually represented in different ways within the data warehouse. The first step to start modeling is to determine your entities and creating an Entity Relationship Diagram (ERD). Entities can be better described as “data elements of interest to your business. For example, “Customer” would be an entity. “Sale” would be another. On an ERD, you document how these different entities relate to each other in your business and which high-level connections exist between them.
The next step to modeling your data is to create a data view, which in SAP Data Warehouse Cloud you can do via a graphical tool or via SQL queries. If you are unfamiliar with SQL, the graphical tool is the most intuitive option, allow you to drag and drop elements into your model and visually build your connections.
While creating a view, you have the option to combine tables and even other views into a single output. When you select a source in the Graphical View and drag it on top of a source already associated with the output, you will have the option to either join or create a union of these tables.
As you can see in the screenshot above, there are standard join types for you to choose when joining two tables. Here is a visual representation of how each join type is different from the other:
A union simply takes all the columns and all rows from both objects and creates an output with the complete information.
While building your graphical view, you can also add a semantic layer to your data by adding business properties to each element of your desired output. That means adding more descriptive and business-relevant information to your joined data so that it becomes easier to later analyze the results with the Story Builder within SAP Data Warehouse Cloud.
Now that we’ve introduced many complex concepts, let’s see an example of a data model. An online shoe retailer called Best Run Shoes wants to understand better the customers who only buy once and never come back, trying to identify trends and similarities that might explain this behavior.
Before getting any insights, they need to make sure that the data elements within their Marketing Space are organized and connected.
In SAP Data Warehouse Cloud, you can use the Data Builder to select, filter, and connect data sources, as you can see here.
So, when someone from Best Run Bikes goes into the Data Builder and starts making connections that will help her determine how many customers are one-time customers, and some characteristics about these customers.
They select the Products table and, using the Relationship Model, connect this dimension to the Sales Order table. After making sure that the correct columns are joined, they save this model and then deploy it, to make it available for a Data Story they plan to create with SAP Analytics Cloud.
Data Models can be as simple or as complex as your business requires and creating them requires looking at your available data and your information needs. SAP Data Warehouse Cloud provides you with flexible ways to build your models in any way you need, including being able to choose if you want to go with SQL queries or a graphical view. Flexibility means that you and your teams can collaborate and get the most of your data.