克米亚sap论坛,最好的sap论坛,sap系统,sap培训,kemiya,克米亚,sap账号,sap ides,sap mm,sap hana,sap fico,sap pp

 找回密码
 注册
查看: 3166|回复: 24

SAP HANA应用实例:个人消费分析 - HANA数据模型

[复制链接]
kmy 发表于 2013-4-29 14:09:22 | 显示全部楼层 |阅读模式
原文来自: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来实现类似的界面。
[/url]

HANA数据模型 (计算视图,SQL脚本还有R语言脚本)
从[url=http://download.sap.com/SMIGlobal/download.epd?context=8E13B55F9E6B9042C3FE6E21E4F7FE6533A6E195DFDF16666E5DA8F4B723FEB690F2A3414AB066772A8DD9072EB20256B3BD6AD6EDF52B16A7887D6C39D61D105BE0AC9C4868831C]这本免费电子书
里你将能找到所有详细建模步骤。下面我们列出本例将用到的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


本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x
victor_nw05 发表于 2014-8-1 13:29:28 | 显示全部楼层
楼主辛苦了
victor_nw05 发表于 2014-8-1 13:36:29 | 显示全部楼层
资料非常不错呀
chenligui 发表于 2016-10-21 23:21:22 | 显示全部楼层
希望有机会尝试下
ITyangcunjie 发表于 2017-7-13 14:18:45 | 显示全部楼层
sap hana
ken3310 发表于 2017-7-13 17:56:52 | 显示全部楼层
sap ides
springsongsap 发表于 2017-7-13 19:40:44 | 显示全部楼层
SAP标准教材下载
sqsaperp 发表于 2017-7-14 02:52:41 | 显示全部楼层
SAP认证考试
cacai1144 发表于 2018-1-27 22:06:40 | 显示全部楼层
sap hr
龙於扶 发表于 2018-5-29 21:45:33 | 显示全部楼层
sap是什么意思
张法打 发表于 2018-6-27 10:23:22 | 显示全部楼层
sap s4 hana
Fido 发表于 2018-7-7 06:10:11 | 显示全部楼层
sap crm
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|克米亚sap论坛,sap账号,sap系统,sap ides,sap学习机,sap练习环境 ( 渝ICP备18002525号-5 )

GMT+8, 2019-6-25 06:35

Powered by Discuz! X3.4

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表