Data Modeling in SAP HANA with sample eFashion Database-Part II

[mohammad_shafiullah]

In first part Data Modeling in SAP HANA with sample eFashion Database-Part I ,we created schema “eFashion”, 6 dimension tables and 2 fact tables.

We also loaded data in all tables using BODS flat file option. Following catalog has been created in HANA.

Now let us start creating Information model.There are three information views group together under a container called Package.Package created under CONTENT folder.

•   ATTRIBUTE VIEW: Attribute views are the reusable dimensions or subject areas used for business analysis.You can use attribute views to join master data tables to each other.

•   ANALYTIC VIEW: Analytic views are the multidimensional views or OLAP cubes.Using analytic views you analyze values from a single fact table that is based on the related attributes from the data foundation and attribute views.Analytic views are consumed by Report.

•   CALCULATION VIEW:Calculation views are used to create your own data foundation using other views or database tables.A calculation view can join several fact tables.Calculation views can be created as graphical views or as scripted views.Calculation views are consumed by reports.

1. CREATE HANA PACKAGE:

•      Select Content folder.
•      Right Mouse Click > New > Package
•      Provide Package Definition

2. CREATE ATTRIBUTE VIEW

•     Select eFashion package
•      Right Click > New > Attribute View
•      Provide View Definition
•      Next, Select ARTICLE_COLOR_LOOKUP table,Finish
•      Add Attributes
•      Select ARTICLE_COLOR_LOOKUP_ID > Right Click > Add as Key Attribute
•      Validate > Save & Activate
•     There are two scenarios in Attribute view.
  • Data Foundation:Container of attributes and joins.Details area shows the attributes and Output columns and their properties
  • Semantics:Shows the properties,Columns and Hierarchy.
•       Attribute View must have a Key attribute.
•       Once attribute columns and key attributes define,click on Validate and then Save and Activate.

• Repeat above process to create Attribute View for all dimension tables.
  

3. CREATE ANALYTIC VIEW FOR SHOP_FACT.

• Select efashion package> Right Click > New > Analytic View.
• Provide View Definition.
• Next, Select SHOP_FACT table.
• Next, Select Dimensions.
• Finish
• Add Attributes
  • SHOP_FACT,ARTICLE_ID,COLOR_CODE,WEEKID,SHOP_ID
• Add Measures
  • MARGIN,AMOUNT_SOLD,QUANTITY.
• Create Calculated Measures.
• Join Fact Tables and Dimensions
• Validate
• Save and Activate.
• Data Preview.
• Analytic View contains Semantics,Logical Joins and Data Foundation scenarios.
• Data Foundation consist of one or more FACT tables.
• Logical Join area containsDimensions and Fact tables linked together in the form of star like structure;Fact table center surrounded by dimension tables.This is also called as Star Schema.
• Analytic views are created as Column Views under system defined catalog _SYS_BIC.
  

     Calculated columns can be created as shown in below screenshot.

     Click on Logical Join > Right Click on Calculated Column > New Calculated Column..

    

    

4. CREATE ANALYTIC VIEW FOR PROMOTION FACT:  Follow similar process to create Analytic view for promotion fact.

5. CREATE CALCULATION VIEW USING GRAPHIC VIEW:

•     Select eFashion package > Right Click > New > Calculation View..
•     Provide View Definition
•     Next,Select Analytic Views, Finish
•     Select UNION > Link Analytic Views to UNION > Link UNION to OUTPUT
•     Select UNION and MAP elements
•     Select OUTPUT > Add Attributes and Measures
•     Validate > Save & Activate >Data Preview
•     Calculation views contains Semantics,Aggregation,Union,Join,Projection objects.
•     UNION is used to combine two or more Analytic views.
•     Aggregation in Calculation views is highly optimized.
•     Calculation views can be created either as GRAPHICAL or SCRIPTED.
•     SCRIPTED Calculation view support CE(Calculation Engine) functions,highly optimized for UNION,AGGREGATION,JOIN.
•     Use of CE function recommended over SQL command.

6. CREATE CALCULATION VIEW USING SQL SCRIPT -CE FUNCTION:

•      Select eFashion package > Right Click > New > Calculation View..
•      Select SQL Script

   /********* Begin Procedure Script ************/

   BEGIN

  /** CREATE SALES ANALYTIC VIEW**/

AV_SALES=CE_OLAP_VIEW

("_SYS_BIC"."efashion/AV_SHOP_FACTS",

      ["ARTICLE_LABEL",  "CATEGORY", "QTR", "MONTH_NAME", sum("AMOUNT_SOLD") AS "AMOUNT_SOLD"]

);

/** CREATE PROMOTION ANALYTIC VIEW**/

 
AV_PROMOTION=CE_OLAP_VIEW

 
("_SYS_BIC"."efashion/AV_PROMOTION",

    ["ARTICLE_LABEL", "CATEGORY", "QTR", "MONTH_NAME", sum("PROMOTION_COST") AS "PROMOTION_COST"]

);

  /**CREATE SALES PROJECTION**/

SALES_PROJECTION=CE_PROJECTION

(:AV_SALES,
  ["ARTICLE_LABEL","CATEGORY", "QTR", "MONTH_NAME", "AMOUNT_SOLD" AS "AMOUNT",

             CE_CALC('''SALE''', VARCHAR(4)) AS "DATA_TYPE"]

);

/**CREATE PROMOTION PROJECTION**/

 
PROMOTION_PROJECTION=CE_PROJECTION

(:AV_PROMOTION,
  ["ARTICLE_LABEL", "CATEGORY", "QTR", "MONTH_NAME", "PROMOTION_COST" AS "AMOUNT",

             CE_CALC('''PROM''', VARCHAR(4)) AS "DATA_TYPE"]

);

/**UNION*//

 
var_out = CE_UNION_ALL(:SALES_PROJECTION,:PROMOTION_PROJECTION);

END

/*********  End Procedure Script ************/