Oracle Database Working Notes

Thursday, June 23, 2011

Oracle BIEE 整合台灣地圖 Part I -- Load Taiwan Map Into BIEE Sample Data

工具
SQLPlus
MapBuilder 下載位置: http://www.oracle.com/technetwork/middleware/mapviewer/downloads/index.html
NAVTEQ Data Bundle for Oracle BI EE 下載位置: http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

圖資
交通部運輸研究所 - 交通路網數值地圖

步驟

1. 依照 "SampleApp_Deploy_Instructions_825.pdf" 中"6 Deploying SampleApp Mapviewer Content (Optional)" import "obiee_navteq.dmp"

2. BIEE Sample App 使用"OBIEE_CITY", "OBIEE_COUNTRY", "OBIEE_STATE", 三個Map Theme. 其分別對應的表格為"OBIEE_CITY", "OBIEE_COUNTRY", "OBIEE_STATE".

使用SQL*Plus 查看其結構.

[oracle@rac1 ~]$ sqlplus obiee_navteq/obiee_navteq@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 22:53:00 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> desc OBIEE_CITY

Name Null? Type

----------------------------------------- -------- ----------------------------

ISO_COUNTRY_CODE VARCHAR2(3)

CITY_NAME VARCHAR2(255 CHAR)

POPULATION NUMBER(10)

GEOMETRY MDSYS.SDO_GEOMETRY

DISPLAY_NAME VARCHAR2(255 CHAR)

STATE_PROVINCE_ABBRV VARCHAR2(255)

STATE_PROVINCE VARCHAR2(255)

CTRY_CD3_CITY VARCHAR2(255)

CTRY_CD3_STATE_NAME_CITY VARCHAR2(255)

CTRY_CD3_STATE_ABBRV_CITY VARCHAR2(255)

SQL> desc OBIEE_COUNTRY

Name Null? Type

----------------------------------------- -------- ----------------------------

NAME VARCHAR2(255 CHAR)

NAME_INIT VARCHAR2(1020 CHAR)

OBIEE_LOWER VARCHAR2(1020 CHAR)

ISO_COUNTRY_CODE VARCHAR2(5)

SQKM NUMBER(11)

NAME_LABEL VARCHAR2(255 CHAR)

GEOMETRY MDSYS.SDO_GEOMETRY

SQL> desc OBIEE_STATE

Name Null? Type

----------------------------------------- -------- ----------------------------

STATE_NAME VARCHAR2(255 CHAR)

ISO_COUNTRY_CODE VARCHAR2(5)

SQKM NUMBER(11)

GEOMETRY MDSYS.SDO_GEOMETRY

STATE_ABBRV VARCHAR2(5 CHAR)

STATE_NAME_INITCAP VARCHAR2(255)

ISO_CTRY_CD_STATE_NAME VARCHAR2(255)

ISO_CTRY_CD_STATE_NAME_INITCAP VARCHAR2(255)

ISO_CTRY_CD_STATE_ABBRV VARCHAR2(40)

ISO_COUNTRY_CODE2 VARCHAR2(2)

3. 匯入台灣向量圖層

因為手上的資料格式與Demo schema的格式不同, 以先建立一個新的使用者

-- USER SQL

CREATE USER twmap IDENTIFIED BY twmap DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ;

-- ROLES

GRANT "CONNECT" TO twmap;

GRANT "RESOURCE" TO twmap;

使用 MapBuild 匯入台灣的向量資料

匯入後的台灣地圖,基本上如下圖

匯入之後,需要新增 Geometry Theme (註:如果在匯入時,沒有順便建立theme;或所建立的theme不合用,就需要新增)
步驟:
在"Metadata"->"Themes"->"Geometry Themes", 滑鼠右鍵"Create Geometry Theme"

完成之後, 在"Theme Option" 下, 點左邊 "Advanced", 右邊 "Advanced Parameters", 下面 "Info Columns", 點選Edit. 新增Column -> COUNTYNAME, Name -> County Name

同樣的方式, 建立TWN_TOWN, TAIWAN Geometry Theme. 所以目前有三個Geometry Theme: TAIWAN, TWN_COUNTY, TWN_TOWN.

接下來，我們要建立"MAP". "MAP"是由"Theme"組成. 我們接下來將剛才所建立的三個Theme組合成一個"MAP".
步驟
"Metadata"->"Base Map", 右鼠鍵, "Create Base Map"

給 Map名稱"TAIWAN"

接下來, 會看到剛才所定義的三個 Theme

將這三個Theme選入. 並設定適當的Min Scale 與 Max Scale

測試與調整地圖著色的樣式, 放大縮小的感覺, 到覺得舒服, 不會太難看.(需要美感)

接下來, 要產生 Cached Map
"Metadata"->"Tile Layers", 右鼠鍵, "Create Map Tile Layer"

變更名稱為"Taiwan"

調整視窗大小, 儘量讓中間地圖的大小為將來會呈現地圖的大小. 並點選"Update from Map", 更新資料

點選"Minimum Scale"旁的"From Map", 更新數值. 然後模擬使用者操作, 放大至想要的地圖的大小

再點選"Maximum Scale"旁的"From Map", 更新數值. 修改"# Zoom Levels"

點選"Generate", 就會產生相關Zoom Levels的資料

指定"Tile Storage" 為儲存 "map tiles"的位置. (最好與Mapviewer 存放Map tiles的位置相同.

設定地圖顯示中心位置與預設Zoom Level

到目前, 資料庫上地圖相關的設定, 算是完成.

註: 產生台灣地圖的相關指令
CREATE TABLE TWN_COUNTY
AS
SELECT countyname_mb countyname,sdo_aggr_union(sdoaggrtype(t.geometry,0.05)) GEOMETRY
FROM twn_town t
GROUP BY t.countyname_mb;

CREATE TABLE TWN_COUNTRY
AS
SELECT 'TW' AS countryname,sdo_aggr_union(sdoaggrtype(t.geometry,0.05)) GEOMETRY
FROM TWN_COUNTY t ;

Friday, May 27, 2011

Oracle Time Series Forecast Stock Price

首先，我要說的是，這純綷是好玩，除了熟悉與了解Oracle Database Mining的功能外，不具其它目的。
文章的做法是參考Time Series Forecasting，有興趣了解詳情的人，可前往參考。
使用Time Series進行預測，基本上採用上列網址所描述的方法：

Variance stabilization and trend removal
Target normalization
Lagged attribute selection

資料來源：台灣証券交易所每日個股成交行情。
我選擇使用1326台化，資料範圍：2001/4/26 ~2011/5/26，計2,500筆資料。(再重申一遍，只是純綷個人選擇與熟悉Oracle Database Data Mining)我想要預測的目標值為當日最低價格。

依據網址所提供步驟，需要將資料進行1. Variance stabilization and trend removal; 2. Target normalization
建立 View tw1326_xfrm1

CREATE VIEW tw1326_xfrm1 AS
SELECT a.*
FROM (SELECT tx_date, lowest,
tp - LAG(tp,1) OVER (ORDER BY tx_date) tp
FROM (SELECT tx_date, lowest,
tp - LAG(tp,1) OVER (ORDER BY tx_date) tp
FROM (SELECT tx_date, lowest, LOG(10,lowest) tp
FROM tw1326))) a;
在建立Normalized View需要先知道AVG與STDDEV
select avg(tp), STDDEV(TP) from tw1326_xfrm1;

AVG =-0.00000159300346119353465928925527311818673339
STDDEV= 0.0113938809872619115175711395449817433574
提供經過LOG Transformation之後的圖

經過First order differencing after log transformation的圖

轉化成Normalized Table/View

我是利用SQL Developer 的 Data Miner來做Transform的設定，我選用Z-score

其結果的分佈圖如下：

尋找週期
個人覺得，這個部分應該是最難的部分了。因為週期的長短會決定模型與其最後的預測準確度，當然週期愈長，所需要計算的時間也愈長。
使用已Normalized Table/View計算correlation
依原作者的方式，建立XCORR procedure
關於LAG，我選了2,500(所有資料
所有2,500個時間點

只看前面365個時間點

再看少一些的點:180個時間點

90個時間點

由上幾個圖，其實很難找到"定期"的模式。由上圖，我們抓14天為一週期。
以下為建立 TW1326_LAG View

CREATE VIEW tw1326_lag AS
SELECT a.*
FROM (SELECT TX_DATE, LOWEST, TP_NORM,
LAG(TP_NORM, 1) OVER (ORDER BY TX_DATE) L1,
LAG(TP_NORM, 2) OVER (ORDER BY TX_DATE) L2,
LAG(TP_NORM, 3) OVER (ORDER BY TX_DATE) L3,
LAG(TP_NORM, 4) OVER (ORDER BY TX_DATE) L4,
LAG(TP_NORM, 5) OVER (ORDER BY TX_DATE) L5,
LAG(TP_NORM, 6) OVER (ORDER BY TX_DATE) L6,
LAG(TP_NORM, 7) OVER (ORDER BY TX_DATE) L7,
LAG(TP_NORM, 8) OVER (ORDER BY TX_DATE) L8,
LAG(TP_NORM, 9) OVER (ORDER BY TX_DATE) L9,
LAG(TP_NORM, 10) OVER (ORDER BY TX_DATE) L10,
LAG(TP_NORM, 11) OVER (ORDER BY TX_DATE) L11,
LAG(TP_NORM, 12) OVER (ORDER BY TX_DATE) L12,
LAG(TP_NORM, 13) OVER (ORDER BY TX_DATE) L13,
LAG(TP_NORM, 14) OVER (ORDER BY TX_DATE) L14
FROM TW1326_norm) a;

接下來要準備建立Data Mining 資料模型的訓練資料(Training)與測試資料(Testing)，我們的比例抓6:4(要扣除前面沒有資料的16筆，日期2001/05/18日之前。)

CREATE OR REPLACE VIEW TW1326_Train AS
SELECT To_CHAR(TX_Date, 'YYYYMMDD') as CASE_ID, TP_NORM, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12, L13, L14
FROM TW1326_lag a
WHERE TX_DATE > TO_DATE('2001/05/17','YYYY/MM/DD') AND TX_DATE < TO_DATE('2007/06/01','YYYY/MM/DD');

CREATE OR REPLACE VIEW TW1326_Test AS
SELECT To_CHAR(TX_Date, 'YYYYMMDD') as CASE_ID, TP_NORM, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12, L13, L14
FROM TW1326_lag a
WHERE TX_DATE > TO_DATE('2007/05/31','YYYY/MM/DD');

利用SQL Developer Data Miner 建立模型與測試

使用Regression模型來進行預測，在Oracle Data Miner會使用GLM(Generalized Linear Model)與SVM(Support Vector Machine)2種演算法，進行回歸預測。下圖為二種演算法回歸預測的比較圖

GLM看起來比SVM要來的好
兩種演算法的Residual Plot
首先是SVM Residual Plot

GLM Residual Plot

預測明日股價
由於在進Normalized時，選AVG= -0.000001593；STDDEV=0.000001593。在建立預測Table做下列修正

CREATE TABLE TW1326_pred AS
SELECT tx_date, lowest, power(10, svm_pred) svm_pred, power(10, glm_pred) glm_pred
FROM (SELECT tx_date, Lowest,
svm_pred + LAG(lp,1) OVER (ORDER BY tx_date) as svm_pred,
glm_pred + LAG(lp,1) OVER (ORDER BY tx_date) as glm_pred
FROM (SELECT tx_date, Lowest, LOG(10, lowest) lp,
(PREDICTION(tw1326_SVM USING a.*)
* 0.000001593 + -0.000001593) svm_pred,
(PREDICTION(REGR_GLM_1_7 USING a.*)
* 0.000001593 + -0.000001593) glm_pred
FROM tw1326_lag a));
其結果