Oracle 数据挖掘和分析:零售行业应用举例一

oracle database 10g r2 中引入的新的package 提供了线性代数的两个最重要的函数库。可用于多元线性回归分析,主成份分析,线性方程组求解等等。其中多元线性回归分析经常用于零售行业的销售预测。

概述  

    Oracle Database10g R2 中有个新的Package: UTL_NLA,这个Package没什么名气,但是把线性代数功能带进了Oracle Database. 这使得Oracle数据库成为一个不错的科学计算和分析平台. 现在,我们可以很容易的在Oracle 数据库中写出很好的矩阵代码. 下面是从Oracle Database Data Warehousing Guide 10g R2中摘录的简要描述:

    线性代数是有着很广泛的实际应用的数学分支.很多领域都存在可以用线性代数来描述的任务. 比如: 统计领域(多元线性回归,主成分分析),数据挖掘(聚类和分类),生物信息学(微阵列数据分析),业务研究(供应链和其他优化问题),计量经济学(分析消费者的需求数据)以及财务(资产分配问题)每个人都可以自由使用线性代数的各种函数库。Oracle的UTL_NLA包可以定义矩阵类型的数据并且把两个功能最强,使用最广的两个库封装到PLSQL包的子程序中。

     线性代数依赖于矩阵运算,以前,在PLSQL中执行矩阵运算需要基于PLSQL 的本地数据类型,从零开始写矩阵运算函数。这需要写大量的程序并且性能有限。如果开发人员选择把数据发送到外部程序包中去处理而不是在Oracle 中创建矩阵计算函数来处理 ,就存在数据的导出、导入等IO时间消耗。使用UTL_NLA package可以不用做这些,可以快速交付实现。

   

     BLAS和LAPACK可能是应用最广泛的线性代数库。这些库被广泛的应用于大量的科学计算程序和专门工具。

对开发者来说,这些函数库提供了积木实现大量的高级技术。比如 MATLAB工具库,其大多数功能都是建立在类似于BLAS和LAPACK之类的线性代数库的基础上的。在数据库中提供这些函数库,允许开发者编写紧凑的,易于阅读的代码用于矢量操作,当然因为这些库拥有高效而强劲的线性代数操作的实现,使用UTL_NLA Package的代码自然就具有这些特性。

    除了科学计算,Oracle的线性代数支持可用于业务分析。一个例子就是多元线性回归。数据库中带有一个多元线性回归的应用例子,这个例子就是使用UTL_NLA  写的。这个应用在一个叫做OLS_Regression的对象中实现。注意,例子OLS Regression对象的文件文件可以在$ORACLE_HOME/plsql/demo 中找到。点击 这里 查看例子,学习如何使用这个功能。

  

Example 21-19 Linear Algebra

这是一个Oracle数据库附带的例子,  演示了如何使用Oracle  的线性代数功能来支持业务分析,它使用UTL_NLA Package来调用多元线性回归应用。这个多元线性回归应用在一个叫做 OLS_Regression 的对象中被实现。 对应的例子文件可以在$ORACLE_HOME/plsql/demo中找到。

考虑下列场景:零售商需要分析营销计划的效果。

背景:

每个门店都会把它每年的市场费用预算分配到如下四个方面:1) 媒体广告(media);2)促销(promo);3)折扣券(disct);4)直邮(dmail);回归分析需要在每个门店每年的销售额 与上述4项费用支出 之间建立线性关系。假设这些市场数据都存储于如下的表中:

sales_marketing_data (
  /* Store information*/
  store_no   NUMBER,
  year       NUMBER,
  /* Sales revenue (in dollars)*/
  sales      NUMBER,   /* sales amount*/
  /* Marketing expenses (in dollars)*/
  media      NUMBER,   /*media advertisements*/
  promo      NUMBER,   /*promotions*/
  disct      NUMBER,   /*dicount coupons*/
  dmail      NUMBER   /*direct mailers*/)

那么你可以建立如下线性模型:

Sales Revenue = a + b Media Advisements + c Promotions + d Discount Coupons + e Direct Mailer

实现:

这个模型可以使用下面的视图来实现,这个视图引用了OLS回归对象。

View start:

CREATE OR REPLACE VIEW sales_marketing_model (year, ols)
   AS SELECT year,
        OLS_Regression(
        /* mean_y => */
        AVG(sales),
        /* variance_y => */
        var_pop(sales),
        /* MV mean vector => */
        UTL_NLA_ARRAY_DBL (AVG(media),AVG(promo),
                           AVG(disct),AVG(dmail)),
        /* VCM variance covariance matrix => */
        UTL_NLA_ARRAY_DBL (var_pop(media),covar_pop(media,promo),
                           covar_pop(media,disct),covar_pop(media,dmail),
                           var_pop(promo),covar_pop(promo,disct),
                           covar_pop(promo,dmail),var_pop(disct),
                           covar_pop(disct,dmail),var_pop(dmail)),
        /* CV covariance vector => */
  UTL_NLA_ARRAY_DBL (covar_pop(sales,media),covar_pop(sales,promo),
                           covar_pop(sales,disct),covar_pop(sales,dmail)))
 FROM sales_marketing_data
 GROUP BY year;

view end:

通过这个视图,市场部经理可以进行一些分析,比如“这个销售营销模型对2004年的数据来说合理吗?”也就是说多重关联性是否大于可接受的值。查询sql如下:

SELECT model.ols.getCorrelation(1)

       AS “Applicability of Linear Model”

FROM sales_marketing_model model

WHERE year = 2004;

也可做如下分析:“在2003年,如果没有任何市场活动,那么门店的基本收入是多少?” 或者“在2004年,最有效的是那种类型的市场活动?”

 

解释:

1、OLS_Regression 定义在 $ORACLE_HOME/plsql/demo/olstype.sql 中

      OLS是普通最小二乘分析法(ordinary leastsquares)的简写 ,想深入了解OLS可点击这里,而 OLS Regression 即指使用最小二乘法进行线性回归分析。

     

2、var_pop, 函数参考这里  , 或者这里  covar_pop 函数参考这里

 

3、OLS_Regression 使用说明

$Header: olstype.sql 09-jun-2004.17:32:14 lvbcheng Exp $
 
olstype.sql
 
Copyright (c) 2004, Oracle. All rights reserved.  
 
   文件名
     olstype.sql - 普通最小二乘回归
 
   描述
     这个文件包含普通最小二乘(OLS)的类型定义
     This files contains the type definition for Ordinary Least Squares (OLS)
     回归: y = b0 + b1 z1 + ... + bn zn.
 
   备注
     OLS_Regression 的构造函数需要用户传入如下参数:除其他值外,
     预测变量的均值向量(vm), 自变量的协方差矩阵(vcm),  因变量和自变量的协方差(cv). 
 
     1. 有 n个 自变量 (zi) 就应该有n个均值, 这里 MV[i] = avg(zi).
 
     2. 协方差矩阵(VCM) 是一个 NxN 对称矩阵, 并且作为一个阵列参数传入,
        主要使用阵列的右上三角部分,(i <= j  i是列,j是行)  当i!=j时, V[i,j] 是协方差
        covar_pop(zi, zj);当i=j的时候,V[i,j] 是方差  ,比如 ,如果有三个预测变量
        (a, b, and c) 那么其协方差(VCM)就应该是
        :
 
                [     var_pop(a)   covar_pop(a,b)   covar_pop(a,c) ]
         VCM =  [ covar_pop(b,a)       var_pop(b)   covar_pop(b,c) ]
                [ covar_pop(c,a)   covar_pop(c,b)       var_pop(c) ]   
 
        但传给 OLS_Regression()的应该是右上三角部分:
 
         VCM =  UTL_NLA_ARRAY_DBL(var_pop(a), covar_pop(a,b), covar_pop(a,c),
                                                  var_pop(b), covar_pop(b,c),
                                                                  var_pop(c))
 
     3. 对于 N 个自变量(zi)和一个因变量(y)的情况, 协方差(CV) 应该有n中不同情况,
         CV[i] = covar_pop(y, zi).
 
     4. 标准化的评分回归只需要一个相关矩阵(RM)和一个相关矢量(RV),
        使用 RM 替换上面的 VCM, 不过,RM[i,j] = corr(zi, zj) when i != j, and 1 when i = j.
        使用 RV 替换上面的 CV  不过,  RV[i] = corr(y,zi).
 
   依赖
     这个文件依赖于 UTL_NLA package.
 
   参考
     Johnson, Richard A., and Dean W. Wichern, "Applied Multivariate Statistical
     Analysis (5th ed.)". New Jersey: Prentice Hall, 2002.
     《多元统计分析应用》
 
   举例
     1. 下面的SQL语句将从模型:y = b0 + b1 z1 + b2 z2  中返回回归方程。
         而因变量y 和自变量 z1,z2 存储在表中:ols_data(y number, z1 number, z2 number).
 
        SELECT model.ols.getEquation() "OLS Regression Equation"
        FROM
        (SELECT OLS_Regression(AVG(y), VAR_POP(y),
                               UTL_NLA_ARRAY_DBL(AVG(z1),AVG(z2)),
                               UTL_NLA_ARRAY_DBL(VAR_POP(z1),COVAR_POP(z1,z2),VAR_POP(z2)),
                               UTL_NLA_ARRAY_DBL(COVAR_POP(y,z1),COVAR_POP(y,z2))) ols
         FROM ols_data) model;
 
     2. 下面的SQL语句将从模型:y = y = 0 + b1 z1 + b2 z2  中返回回归方程。
        相应的数据存储在表table ols_data(y number, z1 number, z2 number)
 
        SELECT model.ols.getEquation() "OLS Regression Equation"
        FROM
        (SELECT OLS_Regression(AVG(y), VAR_POP(y),
                               UTL_NLA_ARRAY_DBL(AVG(z1),AVG(z2)),
                               UTL_NLA_ARRAY_DBL(VAR_POP(z1),COVAR_POP(z1,z2),VAR_POP(z2)),
                               UTL_NLA_ARRAY_DBL(COVAR_POP(y,z1),COVAR_POP(y,z2)), 0) ols
         FROM ols_data) model;
 
     3. 更多其它关于 OLS_Regressions 应用的例子可以参考 olsexmpl.sql 文件.

专业需求,局限和可用性。

    在使用UTL_NLA Package之前,必须了解一些事情,Oracle文档指出使用这个Package的开发者应该有线性代数基础,特别是BLAS和LAPACK的知识。我相信,如果要使用这个Package来实现一些总所周知的算法的话,只需要了解线性代数的基础即可。关于BLAS和LAPACK, 熟悉一些基本概念是重要的。(比如,矩阵存储表示:列或行),除了Oracle文档,其他一些有用的参考包括:

The Lapack Users’ Guide, the BLAS and the LAPACK chapters in the in CRC Handbook of Linear Algebra

参考:http://oracledmt.blogspot.com/2007/04/way-cool-linear-algebra-in-oracle.html

RMS-RETL-RDW 数据抽取测试实例

RMS-RETL-RDW 数据抽取测试实例

测试:币别数据抽取测试
目的:从RMS模块币别基表中把币别数据读取出来,转换后写入到RDW的币别基表中。
好处:RDW-数据仓库实则也为一数据库,但跟通常的事务性数据库相比,多了对数据的分析、制订计划及高层次的聚合。普通的数据库通常是为特定应用程序设计的,而数据仓库集成了不同源头的数据。普通的数据库关心的是现在,而数据仓库关心的是随着时间变化的活动。普通的数据库是易变的,而数据仓库则是稳定的,它的信息只会以标准的间隔进行更新,也许是按月、按周,或按小时,并且在理想情况下更新只会增加新时间段的数据,而不会更改先前存储在数据仓库中的值。
   针对我们项目,把其它各模块中的数据读写到RDW中,进行高度汇总后,再使用BI报表工具制作管理分析报表(OLAP和数据挖掘),这样即保证了对各模块写入的大容量数据进行快速整合获取,也确保了因没有在其它模块中进行开发,而影响到对应模块的整体性能。
        我们常用的google、baidu是运用数据仓库最典型的实例。

一、从RMS模块币别基表中把币别数据读取出来(参考 rms-1205-og1.pdf 文档考核章节:RETL Extraction Programs)
说明:1.RMS Extract Data – Based on RDW Dimension Data 数据抽取是没有参数的,直接运行.ksh脚本;
         2.RMS Extract Data – Based on RDW Fact Data 数据抽取是带参数的,参数即为运行 .ksh 脚本后面加入 output_file_path/filename
1. oracle用户:su oracle
   启动RETL:source /home/rfx/retl.env
   启动RMS:source /home/oracle/rmsbatchenv.env
   MMHOME环境变量修改为:
   MMHOME=/d01/app/retail/rms/db/retlforRDW
   export MMHOME

2. cd $MMHOME/rfx/src
   ./crncycdex.ksh
   备注:(a)查看crncycdex.ksh中,PROPERTY name=”query” 是通过以下Sql获取出币别数据:
                SELECT CURRENCY_CODE CRNCY_CDE_IDNT,
                             CURRENCY_DESC CRNCY_CDE_DESC
                   FROM rms12dev.CURRENCIES
           (b) 为了查看运行RETL功能执行脚本的更多日志,可以启用两个参数:
                export RFX_DEBUG=1
                export RFX_SHOW_SQL=1
3. 在$MMHOME/log 和 $MMHOME/error中分别产生执行crncycdex.ksh脚本日志:
19960101.log:
========================================
crncycdex 17:52:21: Program started …
crncycdex 17:52:22: Program completed successfully
========================================

crncycdex.19960101:
========================================
—————————–
crncycdex 17:52:21: Program started …
—————————–
Starting the threads
All threads complete

Flow ran successfully

—————————–
crncycdex 17:52:22: Program completed successfully
—————————–
========================================

4. 在$MMHOME/data中产生数据文件:crncycddm.txt,根据验证即为上面第2步中的Sql查询的结果,所以此时完成从RMS币别基表中把币别数据读取出来的功能;

二、把从RMS中获取的币别数据转换及写入RDW中(参考 rdw-12001-og.pdf 文档考核章节:Program Flow Diagrams)
说明:在进行往RDW中转换及写入数据前,要确保RDW安装的完整性,包括初始数据导入的完整性。
1. oracle用户:su oracle
   启动RETL:source /home/rfx/retl.env
   启动RDW:source /home/oracle/rdw_profile_ora
   INSTALL_DIR=/d01/app/retail/rdw/dbserverunix
   export INSTALL_DIR

2. 把RMS data目录下产生的数据文件 crncycddm.txt 拷贝到RDW data目录下:
   cp /d01/app/retail/rms/db/retlforRDW/data/crncycddm.txt /d01/app/retail/rdw/rdw12.0/dev/data/crncycddm.txt

3. 根据文档说明及流程图(图1、2),需要先执行‘A’的五个脚本文件:

图2

图1

   执行顺序:factopendm.ksh -> medfactopendm.ksh -> factclosedm.ksh -> seasnopendm.ksh -> mt_prime.ksh
   cd $MMHOME/rfx/src
   3.1 factopendm.ksh  (参考文档52页Factopendm.ksh说明) 根据文档,需要先执行以下三个脚本:prditmdm.ksh -> prddepdm.ksh -> orglocdm.ksh
       ./prditmdm.ksh
       ./prddepdm.ksh
       ./orglocdm.ksh
       ./factopendm.ksh
   3.2 ./medfactopendm.ksh
   3.3 ./factclosedm.ksh
   3.4 ./seasnopendm.ksh
   3.5 ./mt_prime.ksh

4. ./crncycddm.ksh
   备注:以上各.ksh脚本执行后,均可在目录$MMHOME/log 或 $MMHOME/error中查看日志文件,如果有错误可根据日志进行修改后,重新执行对应脚本。

5. 也可通过以下Sql验证导入到RDW中的币别数据:SELECT * FROM CRNCY_CDE_DM
   导入不同的数据,需要RDW相应的表中进行验证;

若执行以上.ksh脚本发生问题,可参考RDW 12安装日志(包括补充文档)、Retail-RDW-讨论帖。

RDW 12 安装日志补充说明

RDW 12 安装日志补充说明

接着安装 RDW Database 的最后一步操作:
2.3.15 创建RDW时间和日期(即往 RDW 各日期基表中写入初始数据) 时出现以下问题:
一、安装 RDW Database 参考文档 rdw-120-ig-db.pdf 章节:Create Time in RDW 中有说明:
====================================
1. If RMS is not being used as the source of the time calendar, move on to step 2,
otherwise after running time extraction in RMS (see RMS Operations Guide for
details), FTP time_454.txt, start_of_half_month.txt, and wkday.txt from RMS install
directory to the RDW directory <base_directory>/rdw12.0/install.
2. Login to the RDW database server as the UNIX user rdw12dev. Verify the RETL
executable is in the path of your Unix session by typing which rfx at the UNIX
prompt.
3. Change directories to <base_directory>/rdw12.0/install.
4. Verify the C compiler is in the path of your Unix session and the C compiler is in
your Unix library path. At the Unix prompt, enter:
which cc
5. Compile the module cr_time_454, cr_time_13 and cr_time_g with a standard ANSI
C compiler. At the Unix prompt, enter:
cc –g –I. –o cr_time_454 c_utils.c cr_time_454.c
cc –g –I. –o cr_time_13 c_utils.c cr_time_13.c
cc –g –I. –o cr_time_g c_utils.c cr_time_g.c
6. Execute the cr_time.ksh module. At the Unix prompt enter:
cr_time.ksh
7. This script will prompt for the calendar type. Choose 1 for 454 time, 2 for 13 period
time and 3 for 454 with Gregorian time.
8. At the prompt enter the 4-digit year for the beginning and ending of the time
calendar:
Please enter first year to be loaded:
Please enter last year to be loaded:
Note: To determine the beginning and ending fiscal year,
refer to the text file modified above. Verify all months or
periods are included in the text file for the first year; no
partial years are allowed.
One text file will be generated in the install directory for each dimension table.
9. At the UNIX prompt, for 454 time calendar or 13 period time calendar enter:
time_load.ksh
time_trnsfrm_load.ksh
10. At the UNIX prompt, for 454 time with Gregorian time calendar enter
time_load.ksh
g_time_load.ksh
time_trnsfrm_load.ksh
g_time_trnsfrm_load.ksh
====================================

根据 第1点中 see RMS Operations Guide for details,参考 rms-1203-og3.pdf 章节:Running the Time 454 Extract Module 来进行安装:
====================================
1. Log in to the RMS database server as RMS RETL-specific database user. Run the
profile and verify that the MMUSER and PASSWORD variables are set to the RETLsepcific
database user, and the appropriate password. Verify the RETL executable is
in the path of your UNIX session by typing:
%which rfx
2. Change directories to $MMHOME/install.
3. Modify the variable l_path in the extract_time.sql script to reference the UTL_FILE
directory specified in the RMS database parameter file.
4. At the UNIX prompt enter:
%extract_time.ksh
This script generates three files called time_454*.txt, wkday*.txt, and
start_of_half_month*.txt located in the utl_file_dir directory specified in your RMS
database parameter file.
5. Change directories on the UNIX server to $MMHOME/log. Review the log file that
was created or modified.
6. Change directories on the UNIX server to $MMHOME/error. Review the error files
that were created.
7. Move the three output files to $MMHOME/install directory.
====================================

参照RMS提供的以上7个步骤进行安装:
1)执行启动RMS环境变量:source /home/oracle/rmsbatchenv.env
2)重新指定:export MMHOME=/d01/app/retail/rms/db/retlforRDW
3)创建目录:$MMHOME/install
4)cp $MMHOME/rfx/src/extract_time.ksh $MMHOME/install/extract_time.ksh
   cp $MMHOME/rfx/src/extract_time.sql $MMHOME/install/extract_time.sql

   vi extract_time.sql  //修改l_path=$MMHOME/install/

5)执行:cd $MMHOME/install
            ./extract_time.ksh
在目录$MMHOME/log 和 $MMHOME/error 下产生执行以上脚本的错误日志(extract_time.19960101.txt):
====================================

—————————–
extract_time 11:51:49: Program extract_time started
—————————–

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Nov 11 11:51:49 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> old   2:    l_filename    VARCHAR2(50) := ‘&1’;
new   2:    l_filename    VARCHAR2(50) := ‘time_454.txt’;
old   3:    l_filename2 VARCHAR2(50) := ‘&2’;
new   3:    l_filename2 VARCHAR2(50) := ‘wkday.txt’;
old   4:    l_filename3 VARCHAR2(50) := ‘&3’;
new   4:    l_filename3 VARCHAR2(50) := ‘start_of_half_month.txt’;
old  26:    FROM &4.calendar c,
new  26:    FROM RMS12DEV.calendar c,
old  27:     &4.system_options s
new  27:     RMS12DEV.system_options s
old  40:    FROM &4.calendar c,
new  40:    FROM RMS12DEV.calendar c,
old  41:     &4.system_options s
new  41:     RMS12DEV.system_options s
old  50:    FROM &4.calendar;
new  50:    FROM RMS12DEV.calendar;
old  54:    FROM &4.calendar;
new  54:    FROM RMS12DEV.calendar;
old  58:    FROM &4.system_options;
new  58:    FROM RMS12DEV.system_options;
Unable to open file <$MMHOME/install/time_454.txt>: -29280:ORA-29280: invalid directory path
DECLARE
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at line 66

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

—————————–
extract_time 11:51:49: ERROR: Program failed – check /d01/app/retail/rms/db/retlforRDW/error/extract_time.19960101
—————————–
====================================

问题一:因根据以上RMS安装指导第4点说明,运行extract_time.ksh会产生time_454*.txt, wkday*.txt, and start_of_half_month*.txt,但目前没有生成此三个文件,并报在相应目录下找不到time_454.txt文件的错误。

解决方案:
登录PL/SQL tool,在Command Windows下查看utl_file_dir的值
SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
———————————— ———– ——————————
utl_file_dir                         string      /d01/app/oracle/product/10.2.0/db_1/admin/retl/utl_file_tmp, /tmp, /ftphome/gml/working

再修改:l_path=/d01/app/oracle/product/10.2.0/db_1/admin/retl/utl_file_tmp’; /* value of utl_file_dir */
删除之前运行 extract_time.ksh 在目录 $MMHOME/error 中产生的 extract_time.*.txt 文件(有两个文件),再次运行:
./extract_time.ksh
执行正确,日志文件显示如下:
====================================

—————————–
extract_time 13:29:34: Program extract_time started
—————————–

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Nov 12 13:29:34 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> old   2:    l_filename    VARCHAR2(50) := ‘&1’;
new   2:    l_filename    VARCHAR2(50) := ‘time_454.txt’;
old   3:    l_filename2 VARCHAR2(50) := ‘&2’;
new   3:    l_filename2 VARCHAR2(50) := ‘wkday.txt’;
old   4:    l_filename3 VARCHAR2(50) := ‘&3’;
new   4:    l_filename3 VARCHAR2(50) := ‘start_of_half_month.txt’;
old  26:    FROM &4.calendar c,
new  26:    FROM RMS12DEV.calendar c,
old  27:     &4.system_options s
new  27:     RMS12DEV.system_options s
old  40:    FROM &4.calendar c,
new  40:    FROM RMS12DEV.calendar c,
old  41:     &4.system_options s
new  41:     RMS12DEV.system_options s
old  50:    FROM &4.calendar;
new  50:    FROM RMS12DEV.calendar;
old  54:    FROM &4.calendar;
new  54:    FROM RMS12DEV.calendar;
old  58:    FROM &4.system_options;
new  58:    FROM RMS12DEV.system_options;
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

—————————–
extract_time 13:29:34: Program completed succesfully
—————————–

====================================
同时产生相应文件:
(i)在目录$MMHOME/log 和 $MMHOME/error 下产生执行脚本日志文件(extract_time.19960101.txt);
(ii)在目录$ORACLE_HOME/admin/retl/utl_file_tmp 下产生 time_454.txt、wkday.txt、start_of_half_month.txt 三个数据文件;

登录RMS数据库,查看日期数据:select * FROM RMS12DEV.calendar;

接着往下操作:
cp $ORACLE_HOME/admin/retl/utl_file_tmp/time_454.txt $MMHOME/install/time_454.txt
cp $ORACLE_HOME/admin/retl/utl_file_tmp/wkday.txt $MMHOME/install/wkday.txt
cp $ORACLE_HOME/admin/retl/utl_file_tmp/start_of_half_month.txt $MMHOME/install/start_of_half_month.txt

二、接着参考 RDW Database 安装指导文档 rdw-120-ig-db.pdf 章节:Create Time in RDW 进行操作:
1) source /home/oracle/rdw_profile_ora
2) cp /d01/app/retail/rms/db/retlforRDW/install/time_454.txt $MMHOME/install/time_454.txt
   cp /d01/app/retail/rms/db/retlforRDW/install/wkday.txt $MMHOME/install/wkday.txt
   cp /d01/app/retail/rms/db/retlforRDW/install/start_of_half_month.txt $MMHOME/install/start_of_half_month.txt
3) which cc
返回结果:/usr/bin/cc
/usr/bin/cc -g -I. -o cr_time_454 c_utils.c cr_time_454.c
/usr/bin/cc -g -I. -o cr_time_13 c_utils.c cr_time_13.c
/usr/bin/cc -g -I. -o cr_time_g c_utils.c cr_time_g.c
4) 执行:
cd $MMHOME/install
./cr_time.ksh
显示及输入如下信息:
Do you wish to use 454 time calendar, 13 period time calendar or 454 time calendar with Gregorian time calendar?
Enter 1 when you wish to use 454 time calendar,
Enter 2 when you wish to use 13 period time calendar,
Enter 3 when you wish to use 454 with Gregorian time calendar. [1, 2 or 3]
1
Please enter first year to be loaded:
1994-01-03
Please enter last year to be loaded:
2023-12-04
Starting the threads
All threads complete

Flow ran successfully
Started
Terminated Ok
备注:
(i)上面提示中输入的启始年月日,是根据$MMHOME/install 目录下面的time_454.txt 文档中存在的最小最大日期输入的;如果输入的日期不在 time_454.txt 文件中列出来的日期范围内,则会产生错误;
(ii)如果RDW日期表中的初始日期是根据 time_454.txt 文档中的日期导入,那如果需要更大的日期时,是否要手动编辑往time_454.txt 文件中增加日期;如果这样做,那工作量可能很大,并根据自带 time_454.txt 文件看,产生日期的有方式不是通常年月的初末形式,而是从每个月第一个星期一开始。所以以后Retail各模板的日期或区间的初始化是业务顾问在系统中建立,还是从后台编辑此文本再进行导入?
(iii)上面提示中选择输入 Calendar type 时,我选择的是 1 for 454 time;而真正在系统中需要什么样的 Calendar type 还得请业务顾问确认一下。
(iv)执行了cr_time.ksh脚本后,在$MMHOME/install中产生许多time_*_dm.txt 数据文件,记录各种类型的数据,以供导入到RDW基表。

5)因为执行了1 for 454 time,所以执行第9步,即运行:time_load.ksh 和 time_trnsfrm_load.ksh 脚本:
./time_load.ksh

在目录下$MMHOME/log 和 $MMHOME/error 下产生执行以上脚本的错误日志(time_load.20010310.txt):
====================================

—————————–
time_load 13:58:09: Loading initial data files into TIME_DAY_DM TIME_YR_DM TIME_HALF_DM TIME_MTH_DM TIME_WK_DM TIME_QTR_DM in time_load
—————————–
Starting the threads
WARN  – W149: [changecapture:1] “value” is not specified.  Comparing all fields.
Consider specifying the “value” property for better performance.
FATAL – retl 12.0.1 build 1142 (1.4.2_04-b05 :: Sun Microsystems Inc. :: i386 :: Linux :: 2.6.9-42.0.0.0.1.ELsmp)

FATAL – E135: Exception in operator [sort:2]
E108: java.io.IOException: gsort: not found

—————————–
time_load 13:58:11: ERROR: Loading time flat file rdw12dm.TIME_DAY_DM failed
—————————–
====================================
问题二:正常情况是把上一步产生的 time_*_dm.txt 数据文件中的数据写入到对应的RDW日期基表中,但出现以上错误,不知何因,还在查找原因中?

解决方案:

6)./time_trnsfrm_load.ksh
在目录下$MMHOME/log 和 $MMHOME/error 下产生执行以上脚本的错误日志(time_trnsfrm_load.20010310.txt):
====================================
./time_trnsfrm_load.ksh[30]: LAST_YR_DAY_IDNT
            LAST_HALF_DAY_IDNT
            LAST_QTR_DAY_IDNT
            LAST_MTH_DAY_IDNT
            LAST_WK_DAY_IDNT: bad number
====================================
问题三:正常情况也是写入RDW日期表的值,但出现以上错误,不知何因,还在查找原因中?

解决方案:

2.3.16 Populate Static Dimension Data
1 ./timelfldm.ksh
在目录下$MMHOME/log 和 $MMHOME/error 下产生执行以上脚本的日志(timelfldm.20010310.txt):
====================================

—————————–
timelfldm 15:15:12: Loading time LIKE FOR LIKE tables
—————————–

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Nov 11 15:15:12 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL>
0 rows deleted.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting the threads
All threads complete

Flow ran successfully

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Nov 11 15:15:14 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL>
0 rows deleted.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting the threads
All threads complete

Flow ran successfully

—————————–
timelfldm 15:15:15: Loading time LIKE FOR LIKE tables completed successfully
—————————–

====================================

2 ./vchragedm.ksh
在目录下$MMHOME/log 和 $MMHOME/error 下产生执行以上脚本的日志(vchragedm.20010310.txt):
====================================

—————————–
vchragedm 15:17:18: Loading voucher age band table
—————————–

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Nov 11 15:17:18 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL>
0 rows deleted.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting the threads
All threads complete

Flow ran successfully

—————————–
vchragedm 15:17:20: Loading voucher age band table completed successfully
—————————–

====================================

此章节的两个脚本导入都成功,可用rdw12dm登录RDW数据库进行验证基表产生相应数据:
SELECT * FROM time_last_yr_by_day_lfl_dm;
SELECT * FROM time_last_yr_by_wk_lfl_dm;
SELECT * FROM vchr_age_band_dm;

一个MSTR WEB开发测试示例

一个MSTR WEB开发测试示例

一:希望通过此文档能达到以下目的:

1.1 让大家初步了解MSTR的WEB功能。

1.2 用户能用MSRT的WEB功能自定义开发报表,浏览报表。让用户的操作进一步简化。

1.3 怎样用客户端,服务器的方式来实现以上功能!

二:准备工作过程:

为了实现客户端和服务器模式来进行WEB开发测试,我在我的电脑上安装了WINDOWS SP2版的虚拟机。把我的虚拟机作为服务器。在虚拟机上安装了MSTR的所有组件,在这一过程中需要注意以下问题:

2.1 连通主机和虚拟机。

2.1.1 在虚拟机安装完成以后可以安装虚拟机优化工具。在菜单栏àVMàinstall vmware tools. 这样可以让我们使用虚拟机更为方便。

2.1.2 把虚拟机的 NETWORK ADAPTER 设置为 NAT方式。

clip_image002

2.1.3 把虚拟机和主机端口进行映射,这样做的目的是,以后别人访问我主机的时候,是通过这IP映射访问你虚拟机上的SMTR WEB服务的。要达到此目的,首先要确认主机和虚拟机能相互PING通。最简单的方法就是让虚拟机IP段和主机上创建的网关IP段相同,查找虚拟机IP:

clip_image004

下边是虚拟网卡的设置:

值得注意的是2个连接都在126同一个IP段。

clip_image006

现在我们可以互PING主机和虚拟机的IP了 ,这时候如果PING不通,可以把主机和虚拟机的防火墙都关掉。

clip_image008

clip_image010

在确保主机和虚拟机能互PING通过以后,现在来做主机虚拟机IP映射。

clip_image012

点EIDT进入:

clip_image014

点PORT FORWARDING 进行端口映射:

clip_image016

当我们完成了虚拟机和主机的端口映射以后,我们访问主机IP的时候,就能访问虚拟机上的网页了。

2.1.4在虚拟机上安装IIS,版本要5或5版本以上的才能满足。这一过程相对简单,不再描述。

到此,我们已经完成了MSTR的安装,虚拟机的安装和设置,IIS的安装。我们的准备工作已基本就绪,接下来我们来做一个例子来看WEB功能是如何实现的。

一个step by step 的MSTR报表例子

SMTR8i DEMO程序步骤笔记

为了熟悉MSTR8开发工具,我特做了一个DEMO演示程序!把关键过程写了下来,希望能对后边学习MSTR8的同事们有些帮助!当然,由于时间和个人能力的原因,这一过程难免有误,希望大家以予指正!为了做演示程序,我在rdw12md这个用户下面建了2张表:CUX_EMP_INF: 员工基本信息维护表,CUX_EMP_PER_PAY :员工待遇维护表,为了使用方便,我在员工待遇表里加入了员工姓名。两张表通过EMP_ID 关联。

在前面,已经写过建立DESK TOP 与RDW数据库之间的连接,那么今天我们就来看一下我们怎么用MSTR8来开发报表。

一:建立项目源。建立属于自己的项目源,我们的开发都将在这个项目源里进行。

工具à项目资源管理器à添加à

进入如下界面:

clip_image002

注意,ODBC DSN中 xie_ds 是我们建立好的数据源。这是用来连数据资料的。之前志茹已经写过步骤了。至于如何确定我们写的ODBC DSN 是否连接成功,我们可以打开套件里中的 ODBC TEST TOOL :

clip_image004

确定以后,进入界面,可以用一个SQL 测试一下,

select * from cux_emp_inf

进入下图:

clip_image006

此时,我们项目源建立成功!

二:创建我们的项目。

2.1创建项目。选中我们的项目源。右击选择新建项目。点创建。

clip_image008

进入下个界面:

clip_image010

如果你第一次进入,系统会默认为管理员,并留空密码进入。在创建过程中,会为客人用户选安全角色,我们选NORMAL USERS:

clip_image012

确定!至此,第一步完成。

2.2 创建仓库目录:clip_image014

我们选择之前建立好的数据库实例,把我们要用的表>放到右边去 进入下图:

clip_image016

点击保存并关闭!此时,第2步完成。

2.3创建事实。

选中全部,放到右边。

clip_image018

此时,系统将可用列,生成为事实!

2.4创建实体。将可用列全选到右边。

clip_image020

关系定义:以后做钻取的时候定义了比较简单的层次,当然也可以通过建立实体对象来做更详细的钻取!

clip_image022

点下一步,然后确定,至此,第二步完成!入下图

clip_image024

三:报表开发。

3.1 创建实体对象。展开MSTR_DEMO,展开公共对象,新建一个文件夹,存放我们的对象文件。

打开文件夹,右击 新建实体,我们首先创建一个月份的实体,用来按月展示或度量员工信息!

选取存放月份的表,把字段EMP_MONTH 拖到右边,点手动。

clip_image026

确认以后;把表源勾上,写上注释!

clip_image028

确定以后,我们点关闭并保存,命名为:月份。

用同样的方法创建 姓名,地址等对象!

3.2 创建度量。在度量文件夹里创建一个空白度量,把事实EMP SARLARY 放到右边框里,这里我们选择汇总,(可以根据需要选择函数):

clip_image030

点关闭并保存,命名为:工资总数。

同理,创建奖金总数,津贴总数,创造总价值等度量!

入下图:

clip_image032

现在基本工作应该做好了 ,我们先点框架,然后点更新框架,这样我们所做的操作才会产生效果!

3.3创建报表模版。

文件à新建à报表:

clip_image034

在对象浏览器中 选择 公共对象,找到我们创建的对象,拖到报表对象栏中,再把我们建立的度量加入到报表对象里,如下图。

clip_image036

我们运行一下报表:

clip_image038

为了方便观察,我们把它做成纵向报表:切换到视图设计,点攻 行列互换按钮!我们再运行报表,如下图:

clip_image040

clip_image042

到此,一个简单的报表初步形成。下边我们用这张报表来分析所有员工的平均收入,员工的工作效率,并把工作效率小于80的员工效率显示红色,把效率大于等于90的员工效率显示为蓝色!

四:进一步开发报表,在以上报表里实现上诉需求。

4.1添加总计函数,先把视图筛选里的 自动应用更改勾上。在菜单栏里选数据,点总计,就自动为你做好总计了。

clip_image044

4.2计算员工的每月的总收入,总收入=工资+奖金+津贴,我们在报表对象栏里选择空白地方,右击插入计算。

clip_image046

clip_image048

4.3计算员工的效率, 员工的效率=员工创造总价值/(员工创建总价值+员工总收益),由于还没解决括号问题,我用总价值来替代 员工创建总价值+员工总收益,按以上步骤写出计算公式。得出下图结果:

clip_image050

4.4 阀值的运用,显示员工效率小于80的效率表格为红色,大于等于90的为蓝色!

选取员工效率行,右击选择 阀值。做如下设置。

clip_image052

得到如下图:

clip_image054

4.5报表的拖拉,拖拉是工具本身所带的功能,如下图:

clip_image056

clip_image058

4.6筛选,选出公司员工效率大于等于90以上的员工信息:

在视图筛选里做如下操作,就能得出结果:

clip_image060

4.7 钻取:我们选中6月份这栏;右击,钻取,其他方向,EMP DATE IN,确定以后,一个新的报表产生,如下图:clip_image062

我们继续向下钻取,同理,选 向下钻取:来到EMP DATE OUT(员工离职当月信息)如图:

clip_image064

我们现在来新建一个钻取方向:比如按性别来钻取:

4.7.1 实际上,我们只需要创建一个性别的对象实体就能获得钻取路径,由于怎么创建对象实体在前面以已经描述过了,那直接得出下图:

clip_image066

4.8用图形界面或报表一起查看:

4.8.1 在菜单里 选择 看看,图形查看,我们选择饼图的第一个样式:

clip_image068

4.8.2 图表查看:

方法同上,选择图表查看:

clip_image070

4.8.3 SQL查看。同上,选择SQL查看,我们将来的开发可能需要修改报表里的SQL代码,我们通过SQL查看能得到以下信息:

报表: DEMO_REP

数据行: 140

数据列: 1

使用报表缓存: 否

查询引擎执行开始时间: 2008-10-22 14:23:59

查询引擎执行结束时间: 2008-10-22 14:24:00

查询引擎的整个处理周期: 0:00:00.43

SQL持续时间: 0:00:00.06

分析持续时间: 0:00:00.00

查询引擎的其它处理周期: 0:00:00.37

返回行数: 19

返回列数: 7

临时表的数目: 0

SQL 的总数目: 1

SQL的数目: 1

分析的数目: 0

DB用户: rdw12md (1)

DB实例: xieyang_ins

访问的表:

CUX_EMP_INF

CUX_EMP_PER_PAY

SQL声明:

路径0 – 持续期间: 0:00:00.06

select a11.EMP_NAME EMP_NAME,

a12.EMP_ADDR EMP_ADDR,

a11.EMP_MONTH EMP_MONTH,

sum(a11.EMP_TASK) WJXBFS1,

sum(a11.EMP_SALARY) WJXBFS2,

sum(a11.EMP_BONUS) WJXBFS3,

sum(a11.EMP_ALLOWANCE) WJXBFS4

from CUX_EMP_PER_PAY a11

join CUX_EMP_INF a12

on (a11.EMP_NAME = a12.EMP_NAME)

group by a11.EMP_NAME,

a12.EMP_ADDR,

a11.EMP_MONTH

[分析引擎计算步骤:

1. 计算度量: <员工效率值> 在最初的数据级别 在视图中

2. 计算度量: <总价值> 在最初的数据级别 在视图中

3. 计算度量: <员工总收益> 在最初的数据级别 在视图中

4. 计算小计: <总计>

5. 计算度量: <员工效率值> 在小计级别 在视图中

6. 计算度量: <总价值> 在小计级别 在视图中

7. 计算度量: <员工总收益> 在小计级别 在视图中

8. 评估阈值

9. 执行交叉表

4.9报表细节处理:

4.9.1分页的使用:我们可以选择查看某个月的员工情况,在运行报表以后,点工具栏里的分页按钮,比如,我们想让报表按月来显示员工信息,我们可以把月份对象拖放到分页栏里!

clip_image072

然后就能选择需要显示的月份!由于我们安装的是7天评估版的,现在已经到期,这个功能的页面浏览不能出现。以致不能截图!但功能不变!

4.9.2小计的使用:按月汇总员工信息:点小计算按钮,点上总计,点高级,在应用级别界面上选分组 加入月份,如下图,我们点确定以后,报表自动修改:

clip_image074

clip_image076

我们也可以按性别,按人来进行汇总!同样,也能求取平均!比如求员工的平均收入,平均工作效率等!

clip_image078

4.9.3报表风格:我们可以选择自己喜欢的报表风格来显示报表:表格—>选择的自动样式,这时,可以选择我们喜欢的报表显示,如:

clip_image080

4.9.4 大纲显示对我们也是帮助的,能让我们更好的分析报表,可以按大纲一级一级的展开。点击工具栏里的按显示大纲结果:遗憾,由于版本问题,现在还截取到截面!

4.9.5用提示来自定义报表。我们现在要实现的功能是不用的用户,可以用提示来创建自己想看的报表,可以自定义报表的显示部分和数据分析!

我们到 公共对象 新建 提示,选择对象提示, 下一步,点加入,把我们需要的实体加入到框内,如下图:

clip_image082

勾选 默认提示答案。我们继续点 下一步 ,加入我们的答案,把性别设置为默认答案!

clip_image084

点下一步,完成,设置标题:

clip_image086

点保存!

我们现在运行报表以后,在对象浏览器中找到我们的提示,把它拖到报表相应的位置上,

clip_image088

点 下一步,完成,则新报表产生。

我们点保存并关闭!

clip_image090

点确定,以后我们请求运行报表的时候,就可以自定义想要显示的列了!

4.9.6 利用筛选来进行简单的人机交流!找到我们希望看到的数据,屏蔽掉我们不想看到的数据!比如:我们只想分析6 月份和7月份 姓名是ANDY 和JON的情况! 我们可以这样来实现!

在公共对象里,打开 筛选文件夹!新建筛选!

clip_image092

clip_image094

选择实体 月份,把6月7月的描述加入到选的对象里

clip_image096

点确定,现在我们完成了对月的筛选!我们用同样的方法建立对人的筛选!完成以后如下图:

clip_image098

保存名字为:对姓名和月份的筛选。运行报表,把筛选拖到报表里,效果如下:

clip_image100

4.9.7 将提示和筛选结合使用,这样会让报表的自定义更加灵活。由于版本licence到期,这个功能暂不能演示!在后边我会将缺少的功能一一列出!

到此,我们完成了开发基本报表的步骤!

五:需要注意的问题和等待进一步研究的问题

5.1 需要注意的问题:

5.1.1:创建完实体对象和度量以后,记得更新框架。因为只有在更新以后,系统才会把你所做的操作进行更新映射。如果不更新直框架而直接运行报表则会出现以下错误:

当装载报表时发生错误: DSSSQLEngine:

转换报表:模板 (本地模板). -模板单元度量-模板度量-维度量 (liangdu).

-维度量-维度量-聚合度量 (). -聚合度量表达式-聚合度量-聚合度量叶-表达式-表达式-在DFC转换的时候DFC框架中找不到对象。

5.1.2:把自己创建对象,报表,度量放在不同的文件夹里,有利于以后在有大量对象的情况下快速选择和应用。

5.1.3:如果我们需要修改报表,例如加一个字段,可以直接从数据表里抽取那个字段做成实体对象,在报表设计视图拖入该对象就行。

5.1.4:关于度量,不做分析就可以不用创建度量,就相当于是做一样张没有分析的陈列报表。

要做好度量也不容易,需要我们更深层次的去研究。

5.1.5:由工具产生的报表能直接导出成EXEL 或HTML格式。不需要我们再做转换。

5.2 等待进一步解决和研究的问题:

5.2.1数据抽取和筛选(重点研究),分析数据的前提是一定要先选出我们需要的数据。

5.2.2 对开发工具的进一步研究。

5.2.3 按需求定义钻取层次,达到想怎么钻取就怎么钻取。

5.2.4 深化度量的创建,度量是分析计算存储的数据(事实)。度量可在报表中定义以指定什么样的数据可显示在报表中。数据可被查看或用来做决策分析。所以对度量还需进一步研究,以便解决客户提出更复杂的需求!