SAP HANA应用实例：个人消费分析 - HANA数据模型【翻译】

wizzhi · ‎2013 Feb 21

原文来自：SAP HANA Application Example : Personal Spending Analysis - HANA Models

本文所有HANA建模细节都可以从这本免费的电子书找到。

项目概况

现在好多信用卡公司都给客户提供下载和分析他们自己交易数据的工具。Discover卡就是其中一家。他们的网站给用户提供了详细的分析工具(www.discovercard.com)。这个工具能让用户回顾自己的消费历史记录并做出统计分析。SAP HANA是一个强大的计算平台，我们将用采用类似Discover卡的数据结构做示例，建立一个个人消费分析工具。只要你跟着我们的步骤就能建立一套基于SAP HANA和Java开源方案的分析工具。通过你的亚马逊Web Service帐号，你将能下载你的交易记录并能扩展这个工具作出你自己独有的分析。

系统构架

这个应用包括7个组件（我们有好几个不同的实现，比如基于BI的，Java的或者SAP HANA XS的。这些界面组件不一定完全相同，但是都是类似的）。为了把数据展示给用户，每个组件都通过HANA模型查询数据，这些模型可能是分析视图，也可能是计算视图。本文把所有这些组件分为两类：加载时查询组件和事件驱动查询组件。前者每当报表加载时就调用查询。后者只有当特定事件发生才触发查询动作。

组件	查询时机	查询语句
1.交易列表	加载	SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID;
2.饼图	加载	SELECT SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY CATEGORY_TEXT ORDER BY CATEGORY_TEXT
3.静态报表 ——3,6,12个月	加载	SELECT SUM(AMOUNT) AS AMOUNT, RANGE, OUTPUT_TYPE FROM \"_SYS_BIC\".\"psa/CAL_GET_AMOUNT_REPORT\" GROUP BY RANGE, OUTPUT_TYPE
5.按交易类型分类	加载	SELECT COUNT(ID) AS ID, AVG(AMOUNT) AS AMOUNT, CLUSTER_ID FROM \"_SYS_BIC\".\"psa/CAL_GROUP_TRANSACTION\" GROUP BY CLUSTER_ID ORDER BY CLUSTER_ID
4.线图	选项选中	这里有三个单项选择项，每项对应一个不同的查询语句。查询 1: 按月汇总 SELECT SUM(AMOUNT) AS AMOUNT, MONTH_INT AS MONTH FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY MONTH_INT ORDER BY MONTH_INT 查询 2: 按月平均 SELECT AVG(AMOUNT) AS AMOUNT, MONTH_INT AS MONTH FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY MONTH_INT ORDER BY MONTH_INT 查询 3: 按月求差 SELECT SUM(AMOUNT) AS AMOUNT, MONTH_INT AS MONTH FROM \"_SYS_BIC\".\"psa/CAL_AVG_DIFF_TREND\" GROUP BY MONTH_INT ORDER BY MONTH_INT
6.按ID查询	交易选中	SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" WHERE id = ? GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID
7.按描述查询	查询按钮	SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" WHERE UPPER(description) LIKE '%" + description + "%' GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID

基于这些HANA数据模型，我们有几种不同的实现来展示数据。下图显示的是通过HTML5 + Ajax + REST Web Service来读取ODBC数据的实现。更多详情请看这篇博客。你也可以用BusinessObject Dashboard来实现类似的界面。

HANA数据模型 (计算视图，SQL脚本还有R语言脚本)

从这本免费电子书里你将能找到所有详细建模步骤。下面我们列出本例将用到的SQL语句。

连续两月均差

var_out =

SELECT

SUM(T2.AMOUNT-T1.AMOUNT) AS AMOUNT,

     T1.MONTH_INT AS MONTH_INT

FROM

(SELECT AVG(AMOUNT) AS AMOUNT, MONTH_INT

  FROM

  "_SYS_BIC"."psa/ANA_TRANSACTION"

  GROUP BY MONTH_INT) AS T1,

(SELECT AVG(AMOUNT) AS AMOUNT, MONTH_INT

  FROM

  "_SYS_BIC"."psa/ANA_TRANSACTION"

  GROUP BY MONTH_INT) AS T2

WHERE T1.MONTH_INT = T2.MONTH_INT - 1

GROUP BY T1.MONTH_INT;

CREATE COLUMN TABLE DATE_RANGE(RANGE INT PRIMARY KEY);

为报表准备时间配置

INSERT INTO DATE_RANGE VALUES(1);

INSERT INTO DATE_RANGE VALUES(3);

INSERT INTO DATE_RANGE VALUES(6);

INSERT INTO DATE_RANGE VALUES(12);

生成交易报告

TIME_RANGE_OUT = SELECT

L.DATE_SQL AS DATE_FROM,

D.LAST_DATE AS DATE_TO,

R.RANGE AS RANGE

FROM "_SYS_BIC"."psa/ATT_TIME_VIEW" AS L,

(SELECT MAX(TRAN_DATE) AS LAST_DATE

FROM "SYSTEM"."PSA_TRANSACTION") AS D,

"SYSTEM"."DATE_RANGE" AS R

WHERE DAYS_BETWEEN(L.DATE_SQL, D.LAST_DATE) = R.RANGE * 30;

CALL "_SYS_BIC"."psa/PRO_GET_TIME_RANGE"(TIME_RANGE);

AVG_OUTPUT = SELECT R.RANGE AS RANGE, AVG(AMOUNT) AS AMOUNT, 'AVG' AS OUTPUT_TYPE

FROM "_SYS_BIC"."psa/ANA_TRANSACTION" AS D, :TIME_RANGE AS R

WHERE D.DATE_SQL >= R.DATE_FROM AND D.DATE_SQL <= R.DATE_TO

GROUP BY R.RANGE;

SUM_OUTPUT = SELECT R.RANGE AS RANGE, SUM(AMOUNT) AS AMOUNT, 'SUM' AS OUTPUT_TYPE

FROM "_SYS_BIC"."psa/ANA_TRANSACTION" AS D, :TIME_RANGE AS R

WHERE D.DATE_SQL >= R.DATE_FROM AND D.DATE_SQL <= R.DATE_TO

GROUP BY R.RANGE;

var_out = SELECT * FROM :AVG_OUTPUT union SELECT * FROM:SUM_OUTPUT;

创建R语言的存储过程

DROP PROCEDURE "SYSTEM"."GROUP_TRAN";

DROP TYPE "SYSTEM"."DATA_TYPE";

CREATE TYPE "SYSTEM"."DATA_TYPE" AS TABLE(

"ID" INTEGER not null,

"TRAN_DATE" DATE null,

"POST_DATE" DATE null,

"DESCRIPTION" NVARCHAR (60) null,

"AMOUNT" DOUBLE null,

"CATEGORY_TEXT" NVARCHAR(20) null);

DROP TYPE "SYSTEM"."DATA_OUTPUT_TYPE";

CREATE TYPE "SYSTEM"."DATA_OUTPUT_TYPE" AS TABLE (

"ID" INTEGER not null,

"TRAN_DATE" DATE null,

"POST_DATE" DATE null,

"DESCRIPTION" NVARCHAR (60) null,

"AMOUNT" DOUBLE null,

"CATEGORY_TEXT" NVARCHAR(20) null,

"CLUSTER_ID" INTEGER null);

CREATE PROCEDURE "SYSTEM"."GROUP_TRAN"(IN data_input "SYSTEM"."DATA_TYPE", OUT result "SYSTEM"."DATA_OUTPUT_TYPE" )

LANGUAGE RLANG reads sql data AS

BEGIN

library(kernlab)

model<-kmeans(data_input$AMOUNT, 3)

result<-data.frame(data_input, CLUSTER_ID=model$cluster)

END;

temp_var = SELECT "ID","TRAN_DATE","POST_DATE","DESCRIPTION","AMOUNT","CATEGORY_TEXT" FROM "SYSTEM"."PSA_TRANSACTION";

CALL "SYSTEM"."GROUP_TRAN"( :temp_var, var_out);

通过以下链接可以下载示例数据：http://www.saphana.com/servlet/JiveServlet/download/38-8484/transaction.csv.zip

其他相关信息: http://www.saphana.com/servlet/JiveServlet/download/38-8485/HANA_Exercise.pdf