QlikView BI設計實務 第七章QlikView BI ETL

Presentation on theme: "QlikView BI設計實務 第七章QlikView BI ETL"— Presentation transcript:

1 QlikView BI設計實務 第七章QlikView BI ETL
講師：吳文宗

2 目錄 一、ETL的處理說明 二、ETL的關聯說明 三、ETL的其他應用 四、集合運算使用定值 五、集合範例

3 一、 ETL的處理說明 在商業智慧導入過程中，將所需要的異質性資料，透過一個ETL(粹取、轉換、清理、載入)的轉換程序，將不同格式的資料檔案或資料庫，粹取所選取的資料、轉換成正確的資訊、清除重複不需要的資料後，轉至一個統一的資料倉儲資料庫 ETL的程序可以用工具軟體來處理，也可以撰寫程式來處理，或用資料庫的Store Procedure來處理。

4 ETL的處理說明 傳統的BI專案導入過程中，ETL是一個龐大的工程。每個過程都有獨立的產品，各產品之間的整合相當複雜。

5 ETL的處理說明 QlikView BI是一個將 ETL、DataBase、DashBoard、OLAP、Reporting等功能結合在同一個產品裡。 ETL ：Script DataBase ：QVD DashBoard & OLAP & Reporting：在Sheet上的ObjectChart

6 ETL的處理說明 Click 進入Edit Script，所有的資料來源(資料庫的指定、各類型檔案的路徑)、檔案的讀取、檔案的合併、條件的判斷、QVD的產生…均在此Edit Script裡設定。

7 二、ETL的關聯說明 Join Sheet1原始資料 Sheet2原始資料 客戶 銷售 A 100 B 200 C 300 D G 客戶
性別 B 男 C 女 E F

8 ETL的關聯說明 Edit Script 如下：

9 ETL的關聯說明 QlikView 會自動依相同欄位名稱作關聯。

10 ETL的關聯說明 Sheet1 & Sheet2 同上， Left Join Edit Script 如下：

11 ETL的關聯說明 Left Join是以Sheet1為主。

12 ETL的關聯說明 Sheet1 & Sheet2 同上，Right Join Edit Script 如下：

13 ETL的關聯說明 Right Join是以Sheet2為主。

14 ETL的關聯說明 Sheet1 & Sheet2 同上，Concatenate 強制合併 Edit Script 如下：

15 ETL的關聯說明 Concatenate 是依Sheet1 和 Sheet2的筆數加總。

16 ETL的關聯說明 Inline 內崁表

17 ETL的關聯說明 Exists 資料過濾 Sheet1原始資料 Sheet2原始資料 A B 1 AAA 2 BBB 3 CCC 4 DDD
5 EEE 6 FFF A1 C 1 A 2 B 3 4 D 5 E 6 F 7 G 8 H

18 ETL的關聯說明 Not exists Edit Script 如下：

19 ETL的關聯說明 Not Exists(A,A1) 是A1 不存在A，只剩下7、8兩筆資料。

20 ETL的關聯說明 Crosstable 旋轉表 Sheet1原始資料

21 ETL的關聯說明 Crosstable 旋轉表
標準的Excel檔案，一般使用者會將資料彙總整理成容易 閱讀的格式，這在商用程式語言COBOL所設計的程式裡，時常有這種檔案格式，每筆Record 包含的欄位如下：庫別、料號及Occurs 12次的各月份庫存金額。但是這種非正規化的資料格式是不能用來作和年度月份有關的資料分析，我們必須想辦法將其一筆資料拆開成12筆資料，這樣QlikView才能Load進來作分析。

22 ETL的關聯說明 Crosstable 旋轉表 QlikView 有一個功能Crosstable可以將其拆解。

23 ETL的關聯說明 Crosstable 旋轉表 Crosstable(月份, 銷售金額,2)
2表示前面的二個欄位(BU、料號)為重複欄位每筆資料均要加上。月份為2009/Jan…，銷售金額為相對各月份之下的數值。

24 ETL的關聯說明 Crosstable 旋轉表 Reload 後按Ctrl+T，再按右鍵，按Preview。

25 ETL的關聯說明 Crosstable 旋轉表 以Straight Table來展示結果

26 ETL的關聯說明 QVD的產生 QVD 是QlikView存放資料的檔案，副檔名為.qvd。可以將Load 進來的資料再轉存至QVD，有需要時可以再從QVD Load 出來運用。有點像是QlikView 的資料庫。也可以當成是一個暫存檔，當有需要做一些較複雜的運算時，可以先存成QVD。

27 ETL的關聯說明 QVD的產生 同上Crosstable的例子，只需加入
Store TABLE1 into F_TABLE1.qvd(qvd);

28 ETL的關聯說明 QVD的產生 再Load 進來其檔案結構如下： LOAD BU, 料號, 月份, 銷貨金額 FROM
F_TABLE1.qvd (qvd);

29 ETL的關聯說明 QVD的產生

30 三、ETL的其他應用 用CONCATENATE來合併兩個Fact Table
兩個Fact Table ：Orders Fact、Sales Fact 兩個Dimension Table ：Country、Product

31 ETL的其他應用 要將Orders Fact、Sales Fact合併成一個Fact Table。

32 ETL的其他應用 用AutoNumber 來合併多個欄位，給合成一個Key。

33 ETL的其他應用 兩個Table 都有DEPARTMENT_NO、DEPARTMENT_TYPE_NO、COLLEGE_NO。如果不作任何處理QlikView 會自動產生複合鍵，複合鍵會使用很多記憶體資源，也會影響執行效率。

34 ETL的其他應用 將DEPARTMENT_NO、DEPARTMENT_TYPE_NO、COLLEGE_NO三個欄位用AutoNumber 組合起來形成一個單一的Key。其qvw變小 執行速度也變得更快。

35 ETL的其他應用 IF 指令 if (期初BMI >= L11 and 期初BMI <= L12, '1過輕',if (期初BMI >= L21 and 期初BMI <= L22, '2稍輕',if (期初BMI >= L31 and 期初BMI <= L32, '3適當',if (期初BMI >= L41 and 期初BMI <= L42, '4稍重','5過重')))) as 期初體重標準 將期初BMI依體重標準的定義來分為過輕、稍輕、適當、稍重及過重。

36 ETL的其他應用 消除關聯Loop 在資料庫Table設計時會有一種情況：Table1 中有一個Key 會Join到Table2，Table2中也有一個Key 會Join到Table3，而Table3中也有一個Key 會Join到Table1。如此Table1Table2Table3Table1，會形成Reference Loop。當使用者的程式用到此種狀況時，會造成無法正常執行，或者無法正常結束。

37 ETL的其他應用 消除關聯Loop QlikView BI可以載入不同的平台(AS/400、Unix、Linux、Windows…)中不同的資料格式(Oracle、Sybase、MS SQL、Access、Text File、QVD…)，而載入的各個Table中，用欄位名稱相同的當作關聯的Key。使用者也可以在Edit Script去變更欄位的名稱，這會造成Reference Loop發生的風險。 當有二個以上的 Fact table，共用了二個以上的Dimension時，就會發生Reference Loop的現象。

38 ETL的其他應用 消除關聯Loop QlikView BI在按 時會去檢查是否發生Reference Loop。

39 ETL的其他應用 消除關聯Loop QlikView BI在按 時會去檢查是否發生Reference Loop。

40 ETL的其他應用 消除關聯Loop 下列是測試的五個Tale及所屬的欄位名稱。

41 ETL的其他應用 消除關聯Loop 下列是測試的五個Tale及所屬的欄位名稱。 Edit Script 如下： LOAD 日期, 訂單代號,
訂單說明 FROM loop2.xls (biff, embedded labels, table is order$); LOAD 訂單序號, 產品代號, 單價 as 單價O, 金額 as 金額O (biff, embedded labels, table is order_detail$);

42 ETL的其他應用 消除關聯Loop LOAD 日期, 發票代號, 發票說明 FROM loop2.xls
(biff, embedded labels, table is invoice$); LOAD 發票序號, 產品代號, 單價, 金額 (biff, embedded labels, table is invoice_detail$);

43 ETL的其他應用 消除關聯Loop LOAD 產品代號, 產品名稱 FROM loop2.xls
(biff, embedded labels, table is goods$);  Reload 後會產生如上畫面所說的Reference Loop，按Ctrl+T。

44 ETL的其他應用 消除關聯Loop 其中虛線為發生Loop的部份，需想辦法將其解決。消除Loop的思考方向是，想辦法切斷虛線的部份。切斷任意一條虛線，便可消除Reference Loop。 消除Reference Loop的方法： 1.修改欄位名稱，強行切斷關聯 2.移動欄位，消除關聯

45 ETL的其他應用 消除關聯Loop 我們只介紹修改欄位名稱。order和invoice之間，是依靠日期相關聯的，invoice的date修改名稱發票日期為則Reference Loop便可切斷。 LOAD 日期 as 發票日期, 發票代號, 發票說明 FROM loop2.xls (biff, embedded labels, table is invoice$);

46 ETL的其他應用 消除關聯Loop 重新 後Reference Loop的情況已消失。

47 Q&A