This topic describes how to use Object Storage Service (OSS), Data Lake Analytics (DLA), and Quick BI to store data, perform interactive query and analytics, and generate business intelligence (BI) reports.

Step 1: Create an OSS schema and tables

Create an OSS schema and tables in DLA. Sample statements:
Create an OSS schema.
CREATE SCHEMA my_schema_name WITH DBPROPERTIES (
    CATALOG = 'oss', 
    LOCATION = 'oss://bucket_name/workshop_sh/'
  );
// Create a table that contains transaction records
CREATE EXTERNAL TABLE tradelist_csv (
    t_userid STRING COMMENT 'User ID',
    t_dealdate STRING COMMENT 'Application time', 
    t_businflag STRING COMMENT 'Business code', 
    t_cdate STRING COMMENT 'Confirmation date', 
    t_date STRING COMMENT 'Application date',
    t_serialno STRING COMMENT'Application No.', 
    t_agencyno STRING COMMENT'Agency No.', 
    t_netno STRING  COMMENT'Branch No.',
    t_fundacco STRING COMMENT'Fund account',
    t_tradeacco STRING COMMENT'Transaction account',
    t_fundcode STRING  COMMENT'Fund code',
    t_sharetype STRING COMMENT'Share type',
    t_confirmbalance DOUBLE  COMMENT'Confirmed balance',
    t_tradefare DOUBLE COMMENT'Transaction fees',
    t_backfare DOUBLE COMMENT "Service fees",
    t_otherfare1 DOUBLE COMMENT'Other fees 1',
    t_remark STRING COMMENT'Remarks'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://bucket_name/workshop_sh/trade/';
// Create a table that contains user information.
CREATE EXTERNAL TABLE userinfo (
    u_userid STRING COMMENT 'User ID',
    u_accountdate STRING COMMENT 'Account creation date', 
    u_gender STRING COMMENT 'Gender', 
    u_age INT COMMENT 'Age', 
    u_risk_tolerance INT COMMENT 'Risk tolerance, 1-10, 10 indicates the highest level',
    u_city STRING COMMENT'City', 
    u_job STRING COMMENT'Job category, A-K', 
    u_income DOUBLE  COMMENT'Annual income (USD 10,000)'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://bucket_name/workshop_sh/user/';
After the tables are created, you can execute SELECT statements to check whether the tables contain appropriate data. The following statement queries 100 transaction records on June 3 and 4, 2018 in agency SXS_0010.
SELECT * FROM tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
limit 100;

Step 2: Add a DLA data source

Add a DLA data source to Quick BI. For more information, see Add a cloud data source DLA.

Note Before you add a DLA data source, add the Classless Inter-Domain Routing (CIDR) blocks 10.152.69.0/24, 10.152.163.0/24, and 139.224.4.0/24 to a whitelist of DLA. If you do not add these CIDR blocks to the whitelist of DLA, Quick BI cannot connect to DLA and the DLA data source fails to be added.
Parameter Description
Name Optional. The name of the DLA data source. We recommend that you choose an identifiable name, which facilitates subsequent management.
Database Address The endpoint of the DLA data source. For more information about how to obtain the endpoint, see Create an endpoint.
Port Number The port number in the endpoint of the DLA data source.
Database The name of the schema in the DLA data source.
Username The account that is used to access the DLA data source. For more information, see Account types.
Password The password that corresponds to the account.

Step 3: Create a dataset

Create a dataset in Quick BI. For more information, see Create a dataset. In this step, you use custom SQL statements to create a dataset named citygender and execute the following statement to query the total amount of funds purchased by males and females in different cities by using JOIN operations. Sample statement:

SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM tradelist_csv , userinfo  
where u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

Step 4: Create a dashboard

Perform dashboard operations in Quick BI. For more information, see Basic dashboard operations. In this step, you select the citygender dataset, specify Value Axis (Mea.), Category Axis (Dim.), and Color Legend (Dim.), and then click Update to view the total amount of funds purchased by males and females in different cities.

You can then create other datasets and dashboards in Quick BI.