Skip to Content
Author's profile photo Mohammad Shafiullah

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.

          LOADDATA1.png

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

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

          LOADDATA2.png

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

            LOADDATA3.png

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

            LOADDATA4.png

    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

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      A small little correction to the above. The table that is mentioned as PRODUCT_PROMOTION is infact PRODUCT_PROMOTION_ID.

      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


      So replace the above command with below:


      CREATE COLUMN TABLE "EFASHION"."PRODUCT_PROMOTION_ID"

        (

        "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


      Once this is done you can use the .rar file which has scripts to populate the data.


      Manish

      Author's profile photo harish nair
      harish nair

      calendar_year_lookup Table is giving an error while loading the data using the excel file .I am attaching the error message below

      Short Text

        The value inserted for the column is larger than the column size defined during creation of the table

      Cause

        The inserted value is too large for the column

      Recommendations

        Check the file and make sure that the data in the file is not larger in size than the column

      Error Message

        Batch from record 262 to 262 failed: SAP DBTech JDBC: [274]: inserted value too large for column: Failed in "YR" column with the value '2006.0'(input position 2): SAP DBTech JDBC: [274]: inserted value too large for column: Failed in "YR" column with the value '2006.0'(input position 2)

      Exception

        com.sap.db.jdbc.exceptions.BatchUpdateExceptionSapDB: SAP DBTech JDBC: [274]: inserted value too large for column: Failed in "YR" column with the value '2006.0'(input position 2)

        at com.sap.db.jdbc.CallableStatementSapDB.executeBatch(CallableStatementSapDB.java:676)

        at com.sap.db.jdbc.trace.PreparedStatement.executeBatch(PreparedStatement.java:675)

        at com.sap.ndb.studio.bi.filedataupload.deploy.populate.PopulateSQLTable.populateTable(PopulateSQLTable.java:106)

        at com.sap.ndb.studio.bi.filedataupload.ui.job.FileUploaderJob.uploadFlatFile(FileUploaderJob.java:201)

        at com.sap.ndb.studio.bi.filedataupload.ui.job.FileUploaderJob.run(FileUploaderJob.java:61)

        at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

      Author's profile photo Former Member
      Former Member
      • Calendar year lookup table error
      • Data load - batch information
        • Batch from record 2 to 263 failed: SAP DBTech JDBC: [274]: inserted value too large for column: Failed in "YR" column with the value '2002.0'(input position 263)
      Author's profile photo Former Member
      Former Member

      Thanks Mohammad, handy tutorial for those trying to get started with SAP HANA.

      FYI

      • Importing Flat Files
        • If you want to keep the data import workflow within Eclipse/SAP HANA Studio (File > Import > Data From Local File). See video tutorial @ HANA Academy - Importing Data from CSV file - YouTube
        • Prior to importing the flat files, rename the file extensions from *.txt to *.csv

      Note: When importing the ARTICLE_COLOR_LOOKUP, ARTICLE_LOOKUP and SHOP_FACTS flat files, you may encounter a java.lang.NumberFormatException error.

      To workaround this issue, remove the "$" (dollar symbol) from all three files. Also, change the formatting of negative numbers in SHOP_FACTS from brackets to using the minus symbol (i.e. FROM (32.25) TO -32.25).