Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!


Get assignment help service to meet the high expectations of your professors

GET ASSIGNMENT HELP

SAP HANA Data Modeling Case Study

Step 1: Create the database tables

  1. Logon to the SAP HANA Studio

Start → All Programs → SAP HANA → SAP HANA Studio

  1. Create a database table for the customer master data

Navigator View → Catalog → GBI_XXX → Tables → Right click → New Table

Enter Table Name, Field Name, SQL Data Type and Dim (Field Length) as shown in the screenshot:

SAP HANA Data Modeling Case Study Image 1
  1. Create a database table for the product master data

Navigator View → Catalog → GBI_XXX → Tables → Right click → New Table

Enter Table Name, Field Name, SQL Data Type and Dim (Field Length) as shown in the screenshot:

SAP HANA Data Modeling Case Study Image 2
  1. Create a database table for the sales transaction data

Navigator View → Catalog → GBI_XXX → Tables → Right click → New Table

Enter Table Name, Field Name, SQL Data Type and Dim (Field Length) as shown in the screenshot:

SAP HANA Data Modeling Case Study Image 3

Step 2: Data Provisioning

  1. Open the modeler perspective in the SAP HANA Studio

Window → Open Perspective → SAP HANA Modeler

  1. Add a new Remote Source to the SAP HANA System

Navigator View → Provisioning → Remote Sources

Right-click on Remote Sources, select “New Remote Source” and enter the data as shown in the screenshot. Please type in gbi as AccessToken.

SAP HANA Data Modeling Case Study Image 4
  1. Add virtual Data Tables to the SAP HANA System

Navigator View → Provisioning → Remote Sources → <Source Name>

Right-click on the Customer.csv table and select “Add as virtual table”. Add “VT_” as prefix of the table name, select your Database Schema and click on create:

SAP HANA Data Modeling Case Study Image 5

Repeat this step for the Product.csv and Salesdata.csv file:

SAP HANA Data Modeling Case Study Image 6
  1. Create a new Repository Workspace for the SAP HANA System

First, switch to the SAP HANA Development Perspective:

Window → Open Perspective → SAP HANA Development

Afterwards, right-click within the Project Explorer and navigate to the following path:

New → Other → SAP HANA → Repository Workspace

There, select your system and determine a Workspace Name and Root:

SAP HANA Data Modeling Case Study Image 7
  1. Create a new Repository Package within your Repository Workspace

Within the Repository View, right click on your Repository Workspace and navigate to

New → Repository Package

SAP HANA Data Modeling Case Study Image 8

There, specify a Repository Package Name and click on Finish:

SAP HANA Data Modeling Case Study Image 9
  1. Within your Repository Package, create a new Flowgraph Model

Right-click on your newly created Repository Package and select

New → Other → SAP HANA → Database Development → Flowgraph Model

There, enter a name, select “Flowgraph for Activation as Task Plan” and click on Finish:

SAP HANA Data Modeling Case Study Image 10

After the Loading process finished, right-click on the initially empty flow graph, select “Properties” and there, change the Target Schema to your Schema:

SAP HANA Data Modeling Case Study Image 11
  1. Within the Flowgraph Model, establish a data connection between the Virtual Table and the Data Table

Insert the Virtual Table “VT_Customer.csv” and the Table CUSTOMER_ATTR_XXX into the Model via Drag-and-Drop from the Table Catalog.

Determine the “VT_Customer.csv”-table as Data Source and the CUSTOMER_ATTR_XXX-Table as Data Sink.

Furthermore, create a connection between the two tables, by linking Data Source and Data Sink.

SAP HANA Data Modeling Case Study Image 12

Next, right-click on the connection line and select “Properties”:

SAP HANA Data Modeling Case Study Image 13

There, perform the field mapping between the virtual table and the data table, by linking the corresponding fields:

SAP HANA Data Modeling Case Study Image 14

Afterwards, save and activate the Flowgraph Model.

  1. Execute the Flowgraph Model to load the customer data

In the upper right corner, click on “Open in SQL Editor”:

SAP HANA Data Modeling Case Study Image 15

There, remove the leading dot before the package name and run the command:

SAP HANA Data Modeling Case Study Image 16

You can check the successful execution by viewing the content of the table CUSTOMER_ATTR_XXX:

SAP HANA Data Modeling Case Study Image 17
  1. Within your project, create a Flowgraph Model for the product data, and load the data. Perform the same steps as you did for the customer data.

Perform the steps 2.6 to 2.8 for the product data.

After performing the steps successfully, the data in table PRODUCT_ATTR_XXX should look as shown in the screenshot below.

SAP HANA Data Modeling Case Study Image 18
  1. Within your project, create a Flowgraph Model for the sales data, and load the data. Perform the same steps as you did for the customer data.

Perform the steps 2.6 to 2.8 for the sales data.

After performing the steps successfully, the data in table SALES_XXX should look as shown in the screenshot below.

SAP HANA Data Modeling Case Study Image 19
  1. Open the Modeler perspective in the SAP HANA Studio

Step 3: Create an Attribute View for customer data

Window → Open Perspective → Modeler

  1. Create a new package to group the views that you will create in the following steps

Navigator View → Content → Right click → New → Package

SAP HANA Data Modeling Case Study Image 20
  1. In your package, create an attribute view for the customer data

Navigator View → Content → Package xx.student_xxx→ Right click → New → Attribute View…

SAP HANA Data Modeling Case Study Image 21 SAP HANA Data Modeling Case Study Image 22 SAP HANA Data Modeling Case Study Image 23 SAP HANA Data Modeling Case Study Image 24 SAP HANA Data Modeling Case Study Image 25
  1. Join the table GBI_DEMO_COUNTRY with table CUSTOMER_ATTR_XXX using a text join

Drag & drop the field CUSTOMER_ATTR_XXX.COUNTRY to field GBI_DEMO_COUNTRY.COUNTRY.

SAP HANA Data Modeling Case Study Image 26

Click on the link between the two tables that represents the join. Now you should see the properties of the join on the right.

Adjust the properties as following:

SAP HANA Data Modeling Case Study Image 27
  1. Join the table GBI_DEMO_SALESORG with table CUSTOMER_ATTR_XXX using a text join

Drag & drop the field SALES_ORGANIZATION from the customer table to field SALES_ORGANIZATION of table GBI_DEMO_SALESORG.

SAP HANA Data Modeling Case Study Image 28

Click on the link between the two tables that represents the join. Now you should see the properties view for the join in the bottom.

Adjust the properties as following:

SAP HANA Data Modeling Case Study Image 29
  1. The view should show only current customers. For current customers, the valid-to date is equal to 9999-12-31. Add a corresponding filter to your view.

Select the VALID_TO field in the customer table.

Select the option Apply Filter in the context menu if this field.

SAP HANA Data Modeling Case Study Image 30
  1. Add all attributes that should be visible in this view to the output structure

Select the table fields that should be visible in the output structure.

Click on Add To Output.

SAP HANA Data Modeling Case Study Image 31

Now the fields should be visible in the output structure.

SAP HANA Data Modeling Case Study Image 32

Repeat this for each table field that should be visible in the output structure. Select True for the property Key Attribute to mark an attribute as a key.

  1. Perform the description mapping for the fields COUNTRY and SALES_ORGANISATION in the properties of the output structure

Add the description fields Short Text for both tables to the Output structure.

SAP HANA Data Modeling Case Study Image 33

After adding the description fields to the output, click on the output fields of COUNTRY and SALES_ORGANISATION and set the Label Column to Short Text.

SAP HANA Data Modeling Case Study Image 34

Rename the output fields with the names SHORT_TEXT and SHORT_TEXT_1 to COUNTRY.description and SALES_ORGANISATION.description.

SAP HANA Data Modeling Case Study Image 35
  1. Save, validate and activate the Attribute View

Press Save and Validate.

SAP HANA Data Modeling Case Study Image 36

Check the Job Log. In case of an error, the job is highlighted in red.

SAP HANA Data Modeling Case Study Image 37

Press Save and Activate.

SAP HANA Data Modeling Case Study Image 38

Check the Job Log. In case of an error, the job is highlighted in red.

SAP HANA Data Modeling Case Study Image 39

Step 4: Create an Attribute View for product data

  1. Create a new Attribute View for product data in your package

Navigator View → Content → Package xx.student_xxx → Right click → New → Attribute View…

SAP HANA Data Modeling Case Study Image 40 SAP HANA Data Modeling Case Study Image 41
  1. Add all relevant attributes to the output structure of the Attribute View

Select the table field that should be visible in the output structure as attribute.
Click on Add To Output.

After adding all relevant fields, the output structure should look as following:

SAP HANA Data Modeling Case Study Image 42
  1. Save, validate and activate your Attribute View

Press Save and Validate.

SAP HANA Data Modeling Case Study Image 43

Check the Job Log. In case of an error, the job is highlighted in red.

SAP HANA Data Modeling Case Study Image 44

Press Save and Activate.

SAP HANA Data Modeling Case Study Image 45

Check the Job Log. In case of an error, the job is highlighted in red.

SAP HANA Data Modeling Case Study Image 46
  1. Create a new Analytic View for the sales data

Step 5: Create an Analytic View

Navigator View → Content → Package xx.student_xxx → Right click → New → Analytic View…

SAP HANA Data Modeling Case Study Image 47 SAP HANA Data Modeling Case Study Image 48 SAP HANA Data Modeling Case Study Image 49 SAP HANA Data Modeling Case Study Image 50 SAP HANA Data Modeling Case Study Image 51 SAP HANA Data Modeling Case Study Image 52

On the Data Foundation component, select the relevant fields of table SALES_XXX and choose Add to Output to add them to the output structure.

SAP HANA Data Modeling Case Study Image 53

Select the Semantics component to change the type of the output fields.

SAP HANA Data Modeling Case Study Image 54
  1. Join the sales table with the product and customer view, using a referential join

Switch to the Star Join component.

Drag & drop field DATAFOUNDATION.CUSTOMER_NUMBER_1 to field CUSTOMER_ATV_XXX.CUSTOMER_NUMBER.

Drag & drop field DATAFOUNDATION.PRODUCT_1 to field
PRODUCT_ATV_XXX.PRODUCT.

SAP HANA Data Modeling Case Study Image 55
  1. Add the net sales price as calculated measure to the output structure of your Analytic View

In the output structure of the analytic view, select the folder Calculated Columns and choose New… from the context menu. Enter the details as indicated in the screenshots.

SAP HANA Data Modeling Case Study Image 56 SAP HANA Data Modeling Case Study Image 57

Press OK.

  1. Save, validate and activate the Analytic View

Press Save and Validate.

SAP HANA Data Modeling Case Study Image 58

Press Save and Activate.

SAP HANA Data Modeling Case Study Image 59

Open the data preview for your analytic view and check the data

Content → xxà GBI_XXX → Right click → Data Preview

SAP HANA Data Modeling Case Study Image 60

Step 6: Analyze the sales data

  1. Open SAP Lumira and login with your Credentials

Start Lumira by calling the link provided by your instructor.

SAP HANA Data Modeling Case Study Image 61

Type in Username and Password and click on Log on.

  1. Select your Analytic View as basis for your analysis

Click on your Analytic View Sales 001 (SALES_AV_001), which you have created in one of the previous tasks.

SAP HANA Data Modeling Case Study Image 62
  1. Analyze the data provided by your Analytic View

What is the total revenue in Germany in May 2011?

  • Select Revenue(SUM) in the Measures section
  • Select 2011 as year
  • Select 5 as month
  • Select Germany as country

The correct answer is: 4.887.410,42 EUR

SAP HANA Data Modeling Case Study Image 63 SAP HANA Data Modeling Case Study Image 64 SAP HANA Data Modeling Case Study Image 65

What are the top 3 products by revenue sold in Germany in May 2011?

  • Select Revenue(SUM) in the Measures section
  • Select 2011 as year
  • Select 5 as month
  • Select Germany as country
  • Select Top 3 and Product_Name (see screenshot)

The screen should look as following:

SAP HANA Data Modeling Case Study Image 66