SAP HANA efashion案例(eFashion on HANA)

http://wiki.scn.sap.com/wiki/dis ... dn=wiki.sdn.sap.com

Introduction

For years, the BusinessObjects products have been demonstrated with the eFashion sample data set. It came in the format of a Microsoft Access database on top of which a pre-built universe was running using an ODBC connection. From there, numerous sample reports or dashboards could be built and illustrate key features of the Semantic Layer and BI client tools.
This article presents the steps to set up the same data set within HANA and BI 4.0, therefore put together a demo environment for BI 4.0 on HANA.

Highlights of this sample

• the universe is a relational universe of the new format (.UNX) that was released with BI 4.0
• the data set does not contain the aggregate tables anymore, it has the master data tables and one fact table (SHOP_FACTS). The aggregates are defined by Analytic Views to be imported into HANA after the tables were loaded.
• the universe is still leveraging the aggregate_aware function to decide at runtime which aggregate to report off
  

Download the project files

• The files required for this project are available from here : http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/006b8dda-602e-2f10-7688-9a2283b76264
• Extract the archive file into a local folder accessible from HANA Studio and Information Design Tool
  

Load the data into HANA

• From the local folder where you extracted the files, use an FTP client tool to copy the folder dataset/index into the work directory of your HANA instance on the HANA appliance. It would typically be /usr/sap/<SID>/HDB00/work.
  
• Open the HANA DB Studio and log on with the SYSTEM user to the HANA Instance you will work with
• From the navigator pane, expand the system and right click on "Catalog" to select "Import..."
  
• Specify the location on the HANA appliance where you copied the files, typically the work directory
  
• Use the following list of tables and click "Add" :
  EFASHION_TUTORIAL.ARTICLE_LOOKUP,EFASHION_TUTORIAL.ARTICLE_COLOR_LOOKUP,
  EFASHION_TUTORIAL.CALENDAR_YEAR_LOOKUP,EFASHION_TUTORIAL.OUTLET_LOOKUP,
  EFASHION_TUTORIAL.SHOP_FACTS
  
• Select "Catalog + Data" and set the number of parallel Threads to 4 (should not be higher than the number of cores on your HANA appliance)
  
• Click on Finish. The job should execute in a few seconds. From the navigator pane, right click on "Catalog" and select "Refresh". The new schema "EFASHION_TUTORIAL" should now appear.
  
  

Alternative method: if the steps below fail for some unknown reason, run the SQL statements below from an SQL Editor window. Before you execute it, you will need to update the path to the .ctl files in each "IMPORT FROM" statement

[AppleScript] 纯文本查看 复制代码

/* --- DROP EFASHION_TUTORIAL SCHEMA IF IT EXISTS, THEN CREATE IT --- */

DROP SCHEMA EFASHION_TUTORIAL CASCADE;
CREATE SCHEMA EFASHION_TUTORIAL;

GRANT SELECT ON SCHEMA EFASHION_TUTORIAL TO _SYS_REPO WITH GRANT OPTION;

/* --- DROP TABLES --- */
/*
DROP TABLE EFASHION_TUTORIAL.ARTICLE_COLOR_LOOKUP;
DROP TABLE EFASHION_TUTORIAL.ARTICLE_LOOKUP;
DROP TABLE EFASHION_TUTORIAL.ARTICLE_LOOKUP_CRITERIA;
DROP TABLE EFASHION_TUTORIAL.CALENDAR_YEAR_LOOKUP;
DROP TABLE EFASHION_TUTORIAL.OUTLET_LOOKUP;
DROP TABLE EFASHION_TUTORIAL.PRODUCT_PROMOTION_FACTS;
DROP TABLE EFASHION_TUTORIAL.PROMOTION_LOOKUP;
DROP TABLE EFASHION_TUTORIAL.SHOP_FACTS;
*/

/* --- CREATE COLUMN TABLES --- */

CREATE COLUMN TABLE EFASHION_TUTORIAL.ARTICLE_COLOR_LOOKUP (
  ARTICLE_COLOR_LOOKUP_ID INTEGER  NOT NULL PRIMARY KEY,
  ARTICLE_ID INTEGER  NULL,
  COLOR_CODE INTEGER  NULL,
  ARTICLE_LABEL VARCHAR(255)  NULL,
  COLOR_LABEL VARCHAR(255)  NULL,
  CATEGORY VARCHAR(255)  NULL,
  SALE_PRICE DOUBLE  NULL,
  FAMILY_NAME VARCHAR(255)  NULL,
  FAMILY_CODE VARCHAR(255)  NULL
);

CREATE COLUMN TABLE EFASHION_TUTORIAL.ARTICLE_LOOKUP (
  ARTICLE_ID INTEGER  NOT NULL PRIMARY KEY,
  ARTICLE_LABEL VARCHAR(100)  NULL,
  CATEGORY VARCHAR(30)  NULL,
  SALE_PRICE DOUBLE  NULL,
  FAMILY_NAME VARCHAR(30)  NULL,
  FAMILY_CODE VARCHAR(3)  NULL
);

CREATE COLUMN TABLE EFASHION_TUTORIAL.CALENDAR_YEAR_LOOKUP (
  WEEK_ID INTEGER NOT NULL PRIMARY KEY,
  WEEK_IN_YEAR INTEGER  NULL,
  YR VARCHAR(4)  NULL,
  FISCAL_PERIOD VARCHAR(4)  NULL,
  YERA_WEEK VARCHAR(7)  NULL,
  QTR VARCHAR(1)  NULL,
  MONTH_NAME VARCHAR(15)  NULL,
  MTH INTEGER  NULL,
  HOLIDAY_FLAG VARCHAR(1)  NULL
);

CREATE COLUMN TABLE EFASHION_TUTORIAL.OUTLET_LOOKUP (
  SHOP_ID INTEGER NOT NULL PRIMARY KEY,
  SHOP_NAME VARCHAR(50)  NULL,
  ADDRESS_1 VARCHAR(255)  NULL,
  MANAGER VARCHAR(255)  NULL,
  DATE_OPEN VARCHAR(255)  NULL,
  LONG_OPENING_HOURS_FLAG VARCHAR(1)  NULL,
  OWNED_OUTRIGHT_FLAG VARCHAR(1)  NULL,
  FLOOR_SPACE INTEGER  NULL,
  ZIP_CODE INTEGER  NULL,
  CITY VARCHAR(255)  NULL,
  STATE VARCHAR(255)  NULL
);

CREATE COLUMN TABLE EFASHION_TUTORIAL.SHOP_FACTS (
  SHOP_FACTS_ID INTEGER NOT NULL PRIMARY KEY,
  ARTICLE_ID INTEGER  NULL,
  COLOR_CODE INTEGER  NULL,
  WEEK_ID INTEGER  NULL,
  SHOP_ID INTEGER  NULL,
  MARGIN DOUBLE  NULL,
  AMOUNT_SOLD DOUBLE  NULL,
  QUANTITY_SOLD INTEGER  NULL
);

/* --- LOAD DATA FROM FILES --- */

IMPORT FROM '/usr/sap/CSA/HDB00/work/EFASHION_TUTORIAL/AR/ARTICLE_LOOKUP/data.ctl';
IMPORT FROM '/usr/sap/CSA/HDB00/work/EFASHION_TUTORIAL/AR/ARTICLE_COLOR_LOOKUP/data.ctl';
IMPORT FROM '/usr/sap/CSA/HDB00/work/EFASHION_TUTORIAL/CA/CALENDAR_YEAR_LOOKUP/data.ctl';
IMPORT FROM '/usr/sap/CSA/HDB00/work/EFASHION_TUTORIAL/OU/OUTLET_LOOKUP/data.ctl';
IMPORT FROM '/usr/sap/CSA/HDB00/work/EFASHION_TUTORIAL/SH/SHOP_FACTS/data.ctl';

/* --- LOAD DATA FROM FILES --- */

UPDATE "EFASHION_TUTORIAL"."SHOP_FACTS" MERGE DELTA INDEX;
UPDATE "EFASHION_TUTORIAL"."OUTLET_LOOKUP" MERGE DELTA INDEX;
UPDATE "EFASHION_TUTORIAL"."ARTICLE_LOOKUP" MERGE DELTA INDEX;
UPDATE "EFASHION_TUTORIAL"."CALENDAR_YEAR_LOOKUP" MERGE DELTA INDEX;
UPDATE "EFASHION_TUTORIAL"."ARTICLE_COLOR_LOOKUP" MERGE DELTA INDEX;

Import and Activate the Information Models into HANA

• From the Quick Launch screen of HANA Studio, click on Import
  
• Select Information Modeler / Information Models
  
• Select the system you want to import the models into
  
• Browse to the folder where you copied the project files and select the "R12" folder. Expand the efashion and tutorial package, and select the three attribute views and one analytic view that are listed. Add them to the selection and click Finish
  
• A new package "efashion.tutorial" should now appear under the information models and contains three attribute views and one analytic view
  
• Select the views you just imported (CTRL+click for a multi selection), right click on the selection and select "Activate"
  
• The activation process will take a few seconds and upon completion a "Activation Successful" message should appear in the Deployment Log.
  
• Verify the activation process created the column views under the _SYS_BIC schema.
  
• The data is now imported and one analytic view active for consumption. You can now do some reporting on top that information model.
  

Connect and test the Universe from Information Design Tool

• Launch Information Design Tool
• File / Open Project...
• Browse to the local folder where you extracted the project files, click OK, then Finish
  
• You should now see a new project called "efashion on HANA"
  
• Open and Edit the connection object "efashion on HANA". Edit the connection details to your HANA server : username, password, server host and port number.
  
• Click Finish, then save the object.
• You will now make this connection secured by publishing it to the BI 4.x system.
• Right click on the connection object and select "Publish Connection to Repository"
  
• Enter your credentials to the BI 4.x system, click Connect, then Next and select a folder where the Connection object will be stored
  
• Open the Data Foundation object "efashion on HANA.dfx" and click on the Connection pane
  
• Click on the Change Connection icon at the top left corner
  
• Select the secured connection (the one you just published to the repository)
  
• When prompted to update the tables qualifier and owner, click on No
  
• Save the data foundation object. The universe is now ready to be published to the repository.
• Before you publish it, you can test a few queries from the Queries pane. Use the example "yearly sales revenue per line" or build your own.
  
  

Publish the Universe to a BI 4.0 system

• Right click on the Business Layer, and select Publish / To a repository...
  
• Perform an integrity check
  
• You should get a few errors and warnings that can be ignored. The isolated tables and missing primary keys on the aggregate tables are not real problems.
  
• Click Next, then select a folder in the repository for the universe and click Finish
  
• That is all ! The universe is ready for consumption.