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. To learn more about these concepts within the data warehousing context, click here.
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 to add 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. George and Kristina are the founders of an online shoe retailer called Best Run Shoes. Their marketing team 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 source, as you can see here.
So, Maria from Marketing 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.
She selects the Products table and, using the Relationship Model, connects this dimension to the Sales Order table. After making sure that the correct columns are joined, she saves this model and then deploys it, to make it available for a Data Story she plans to create.
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.