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

1.CREATE eFASHION SCHEMA & TABLES:

• Launch SQL Editor from HANA Studio.
• Run SQL Command to create schema.

                        CREATE SCHEMA EFASHION;

• Run SQL Commands to create tables
  • Six Dimension Tables
  • Two Facts Tables
• SQL Commands to create tables

    CREATE COLUMN TABLE “EFASHION”.“ARTICLE_COLOR_LOOKUP”

    (

      “ARTICLE_COLOR_LOOKUP_ID” INTEGER CS_INT,

            “ARTICLE_ID” INTEGER CS_INT,

      “COLOR_CODE” INTEGER CS_INT,

      “ARTICLE_LABEL” VARCHAR(255),

      “COLOR_LABEL” VARCHAR(255),

      “CATEGORY” VARCHAR(255),

            “SALE_PRICE” DECIMAL(19, 4) CS_FIXED,

            “FAMILY_NAME” VARCHAR(255),

      “FAMILY_CODE” VARCHAR(255)

    ) UNLOAD PRIORITY 5 AUTO MERGE

        CREATE COLUMN TABLE “EFASHION“.“ARTICLE_LOOKUP”

    (

      “ARTICLE_ID” INTEGER CS_INT,

            “ARTICLE_LABEL” VARCHAR(100),

            “CATEGORY” VARCHAR(30),

            “SALE_PRICE” DECIMAL(19,4) CS_FIXED,

            “FAMILY_NAME” VARCHAR(30),

            “FAMILY_CODE” VARCHAR(3)

    ) UNLOAD PRIORITY 5 AUTO MERGE

    CREATE COLUMN TABLE “EFASHION“.“ARTICLE_LOOKUP_CRITERIA”

    (

      “ARTICLE_LOOKUP_CRITERIA_ID” INTEGER CS_INT,

            “ARTICLE_ID” INTEGER CS_INT,

            “CRITERIA” VARCHAR(5),

            “CRITERIA_TYPE” VARCHAR(5),

            “CRITERIA_TYPE_LABEL” VARCHAR(50),

            “CRITERIA_LABEL” VARCHAR(100)

    ) UNLOAD PRIORITY 5 AUTO MERGE 

    CREATE COLUMN TABLE “EFASHION“.“CALENDAR_YEAR_LOOKUP”

    (

  “WEEK_ID” INTEGER CS_INT,

      “WEEK_IN_YEAR” INTEGER CS_INT,

      “YR” VARCHAR(4),

      “FISCAL_PERIOD” VARCHAR(4),

      “YEAR_WEEK” VARCHAR(7),

      “QTR” VARCHAR(1),

      “MONTH_NAME” VARCHAR(15),

      “MTH” INTEGER CS_INT,

      “HOLIDAY_FLAG” VARCHAR(1)

  ) UNLOAD PRIORITY 5 AUTO MERGE

CREATE COLUMN TABLE “EFASHION“.“OUTLET_LOOKUP”

  (

  “SHOP_ID” INTEGER CS_INT,

      “SHOP_NAME” VARCHAR(50),

      “ADDRESS_1” VARCHAR(255),

      “MANAGER” VARCHAR(255),

      “DATE_OPEN” VARCHAR(255),

      “LONG_OPENING_HOURS_FLAG” VARCHAR(1),

      “OWNED_OUTRIGHT_FLAG” VARCHAR(1),

      “FLOOR_SPACE” INTEGER CS_INT,

      “ZIP_CODE” INTEGER CS_INT,

      “CITY” VARCHAR(255),

      “STATE” VARCHAR(255)

  ) UNLOAD PRIORITY 5 AUTO MERGE

CREATE COLUMN TABLE “EFASHION“.“PRODUCT_PROMOTION”

  (

  “PRODUCT_PROMOTION_FACTS_ID” INTEGER CS_INT,

      “ARTICLE_ID” INTEGER CS_INT,

      “WEEK_ID” INTEGER CS_INT,

      “PROMOTION_ID” INTEGER CS_INT,

      “DURATION” INTEGER CS_INT,

      “PROMOTION_COST” DOUBLE CS_DOUBLE

  ) UNLOAD PRIORITY 5 AUTO MERGE

CREATE COLUMN TABLE “EFASHION“.“PROMOTION_LOOKUP”

  (

    “PROMOTION_ID” INTEGER CS_INT,

      “PROMOTION_FLAG” VARCHAR(1),

      “PRINT_FLAG” VARCHAR(1),

      “RADIO_FLAG” VARCHAR(1),

      “TELEVISION_FLAG” VARCHAR(1),

      “DIRECT_MAIL_FLAG” VARCHAR(1)

  ) UNLOAD PRIORITY 5 AUTO MERGE

CREATE COLUMN TABLE “EFASHION“.“SHOP_FACTS”

  (

  “SHOP_FACTS_ID” INTEGER CS_INT,

      “ARTICLE_ID” INTEGER CS_INT,

      “COLOR_CODE” INTEGER CS_INT,

      “WEEK_ID” INTEGER CS_INT,

      “SHOP_ID” INTEGER CS_INT,

      “MARGIN” DECIMAL(19,4) CS_FIXED,

      “AMOUNT_SOLD” DECIMAL(19, 4) CS_FIXED,

      “QUANTITY_SOLD” INTEGER CS_INT

  ) UNLOAD PRIORITY 5 AUTO MERGE 

2. LOAD DATA INTO TABLES:

    There are several methods to load data into HANA table. I use flat file using BODS (Business Object Data Services) to load data into HANA tables.

    Load data into ARTICLE_COLOR_LOOKUP table.

      2.1. Open Business Object Data Service Designer & Create New Project.

      2.2. Create New Job.

      2.3. Create Work Flow.

      2.4. Create Data Flow.

      2.5. Select File Format option from local Object Library.Use flat file option to create file format for source data.

            Set the file format properties,modify fields name and data type.

      2.6. Click on Save.Source of flat file will be created and available in Object Library under Flat File option.

    2.7. Create Datastore for target HANA. Import HANA table to datastore.

    2.8. Drag source flat file and target datastore to Data Flow.

    2.9. Create mapping query to map source fields to target HANA table fields.

  2.10. Validate and Execute Job.(If break point is set execute in debug mode to trace the transformation)

    Repeat above steps to load data into remaining tables.

• ARTICLE_LOOKUP
• ARTICLE_LOOKUP_CRITERIA
• CALENDAR_YEAR_LOOKUP
• OUTLET_LOOKUP
• PRODUCT_PROMOTION
• PROMOTION_LOOKUP
• SHOP_FACTS

  Download DataFile.rar and extract to folder and browse while data load.

     https://sites.google.com/site/journeytosqlserver/DataFile.rar