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
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
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)
Thanks Mohammad, handy tutorial for those trying to get started with SAP HANA.
FYI
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).