Skip to Content

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

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Manish Danani

    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

    (0) 
  2. 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)

    (0) 
  3. Shan Sivan
    • 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)
    (0) 
  4. Taygan Rifat

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

    FYI

    • Importing Flat Files

    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).

    (0) 

Leave a Reply