Skip to content
This repository has been archived by the owner on Dec 5, 2024. It is now read-only.

Latest commit

 

History

History
195 lines (145 loc) · 11.6 KB

README.md

File metadata and controls

195 lines (145 loc) · 11.6 KB
Raw

Exercise 03 - Data Layer - Prepare Your Data

📝 Note: This is an Optional Exercise

🔰 Detour: SAP Data Warehouse Cloud - Model Types

Before we are going to start with the first exercise in creating a table and creating the first model, letm us have a look at the different asset types that you can create in the Data Builder of SAP Data Warehouse Cloud.

So, let’s now look at the different asset types:

  • Table: Create a table to contain data by defining its column structure. You can configure each field of the table, and already define semantics and associations. You have the ability to upload data to this table later on.

  • Graphical View: In the Graphical View you can leverage Tables and Views to prepare data and the create new Views using a graphical no-code/low-code editor. Join datasets as appropriate, add other operators to remove or create columns and filter or aggregate data, and specify measures and other aspects of your output structure in the output node.

  • SQL View: In the SQL View you can leverage Tables and Views to the create new Views by using SQL or SQL-script (table function) view in a powerful SQL editor.
    Not used in the DA160 exercises.

  • Entity Relationship Model: Here you define the relationships between Tables or Views, which then are being leveraged when you create a new View based on the Tables or Views.

  • Data Flow: You define Data Flows to use data transformations and leverage the option to load data from a source system and persist it in SAP Data Warehouse Cloud.
    Not used in the DA160 exercises.

  • Intelligent Lookup: Create an intelligent lookup to merge data from two entities even if there are problems joining them.
    Not used in the DA160 exercises.

  • Task Chain: Create a task chain that groups multiple tasks, which can be run in series manually or through a schedule.
    Not used in the DA160 exercises.

End of Detour

Start of the exercise

In this section we will start creating the tables for our sample models and then upload the raw data to those tables in Excercise 5.

We will start to create the tables in SAP Data Warehouse Cloud.

  1. Log On to your SAP Data Warehouse Cloud tenant.

  2. Select the menu option Data Builder on the left-hand side.

  3. In case you are being asked, select your Space (e.g. GE12345)

  4. Select the option New Table


  5. You are being presented with the details to create a new table.

  6. Enter the following details:

    • Business Name - Sales Transactions
    • Technical Name - Sales_Transactions
    • Type - Relational Dataset


  1. We also can provide already as part of the table, some business description and Tags, which then will be used as part of the Business Catalogue.

  2. Enter the following details for the Business Purpose:

    • Description - This is the table for the Sales Transactions
    • Tags - Sales transaction, Revenue, transactions

💡 Tip: Please note, that when entering the Tags, you have to enter each tag individually for now and you can’t enter multiple tags separated by comma right now.

  1. Scroll down to the area Columns (you can also use the tabs in the page header for navigation). Here you define now the structure of the table by adding the individual columns.

  2. Use the “+” sign in the top right corner of the Columns area to start the process of creating your first table column

  3. You now need to enter a Business Name, a Technical Name, and you need to configure the Data Type

  4. For the first column, enter the following details:

    • Business Name - Transaction ID
    • Technical Name - Transaction_ID
    • Data Type - Integer64

💡 Tip: Please note, that you can change the Data Type simply by clicking on the item in the Data Type column.

  1. After you entered the details for the first column, please enter additional columns. All columns of the table are listed as follows:
Key: Business Name: Technical Name: Data Type:
X Transaction ID Transaction_ID Integer64
Transaction Date Transaction_Date Date
Store ID Store_ID String (6)
Product ID Product_ID String (4)
Sales Manager ID Sales_Manager_ID String (4)
Profit Profit Decimal(15,2)
Discount Discount Decimal(15,2)
Revenue Revenue Decimal(15,2)
Cost Cost Decimal(15,2)

  1. After you entered all columns for the table, ensure you enable the Key Column option for the column Transaction ID.

  2. Now use the Save option in the General menu.

  3. On the first time you save the table, you will be asked to confirm the name and technical name.

  4. Click Save.

  5. After you saved the changes, you also have to deploy the table, so that we can later on upload data to the table.

  6. Use the Deploy option from the General menu

  7. We configured, saved, and deployed our first table.

  8. Click on "back arrow" on the top menu bar to navigate back to the list of tables.

  9. You are back at the home screen of the Data Builder and you should see your table in the list of objects.

  10. Use the option New Table.

  11. Enter the following details:

    • Business Name - Store
    • Technical Name - Store
    • Type - Dimension


  1. Navigate to the Attributes area.

  2. Use the “+ sign to create new attributes.

💡 Tip: You will notice, based on the Type Dimension, we now have two additional options for each Column: Semantic Type and Label Column.

The Label Column allows you to specify a column from the table to be used as Label. For example, you could have a Product ID and a Product Description in the table and use the Product Description column as Label for the Product ID.

The Semantic Type option provides you with several option to choose from, so that you can configure an additional context for the column, such as the option to configure the column as a Currency column or a language column.

  1. Enter the following Attributes for the table:
Key: Business Name: Technical Name: Data Type: Semantic Type Label Column
X Store ID Store_ID String (6) None Store_Name
Store Name Store_Name String (30) Text
Store City Store_City String (20)
State ID State_ID String (2)
State Name State_Name String(30)
Country Country String(30)
Latitude Latitude Decimal(15,8)
Longitude Longitude Decimal(15,8)

💡 Tip: Please note, that you can only select the Store Name for the Label Column after you entered the details for the Store Name into the Attributes.

  1. After you entered all columns for the table, ensure you enable the Key Column option for the column Store ID.

  2. Click Save in the General menu.

  3. You will be asked to confirm the Business Name as well as the Technical Name.

  4. Click Save.

  5. Click Deploy in the General Menu.

  6. Click on "back arrow" on the top menu bar to navigate back to the list of tables.

  7. Use the option New Table.

  8. Enter the following details:

    • Business Name - Product
    • Technical Name - Product
    • Type - Dimension


  1. Navigate to the Attributes for the table.
  2. Enter the following Attributes for the table:
Key: Business Name: Technical Name: Data Type: Semantic Type Label Column
X Product ID Product_ID String (4) None Product Name
Product Name Product_Name String (30) Text
Product Category ID Product_Category_ID String (4) None Product Category Name
Product Category Name Product_Category_Name String (30) Text

  1. After you entered all columns for the table, ensure you enable the Key Column option for the column Product ID.

  2. Click Save in the General menu.

  3. You will be asked to confirm the Business Name as well as the Technical Name.

  4. Click Save.

  5. Click Deploy in the General Menu.

  6. Click on "back arrow" on the top menu bar to navigate back to the list of tables.

  7. Enter the following details:

    • Business Name - Sales Manager
    • Technical Name - Sales_Manager
    • Type - Dimension


45. Navigate to the Attributes for the table.
46. Enter the following Attributes for the table:
Key: Business Name: Technical Name: Data Type: Semantic Type Label Column
X Sales Manager ID Sales_Manager_ID String (4) None Sales Manager Name
Sales Manager Name Sales_Manager_Name String (30) Text
  1. After you entered all columns for the table, ensure you enable the Key Column option for the column Sales Manager ID.
  2. Click Save in the General menu.
  3. You will be asked to confirm the Business Name as well as the Technical Name.
  4. Click Save.
  5. Click Deploy in the General Menu.
  6. Use the Back option in top menu or as alternative you can click on your Space name to navigate back to the home screen of the Data Builder.

Summary

You've now created all tables that we need for our model and the overview in the Data Builder should look like this:

Continue to Exercise 04 - Creating the Entity Relationship Model