網站Cookies的使用

我們與第三方合作,在我們的網站上放置cookies。 這些Cookies有助於提供使用者客製化內容、投放廣告和分析網站流量。以進而提供您更好的網頁瀏覽體驗。藉由點選“同意”或繼續瀏覽網頁的方式,您同意我們使用網站Cookies。之後,您仍可以管理是否要繼續使用網站與第三方合作的Cookies。

詳細資訊
即時BI報表,我只服Smart Query!輕鬆做資料整理與轉置(ETL),把報表玩出新高度
Judy 2020-09-22



  市面上的 BI 工具,大多能做出滿足使用者需求的數據分析頁面,但若使用者的需求很複雜怎麼辦呢?聯銓BI產品開發了十分便利的資料組合,能讓 IT 人員在短時間內,透過滑鼠點選的方式做資料組合,組合時不需下SQL,且整理完的資料可利用Transaction功能寫回資料庫,達成ETL的其中兩步驟:萃取(Extract)、轉置(Transform)。如此一來,即便使用者有以下複雜的需求,IT人員也能快速迎刃而解。

使用者需求舉例

業務主管想請 IT 部門製作一張即時BI報表,內容為每月的總交易額與利潤。以下為主管的報表需求:

  • 業務主管每個月要看到當月的各客戶總交易額及利潤(為了適時訂定業務策略)

  • 業務主管還希望每月20號自動收到此份報表

  • 因牽涉到客戶的已成交訂單,需區分已出貨未出貨

  • 有部份訂單是國外客戶,因此會有未出貨的匯率計算方式


  若要滿足以上的需求,以往 IT 人員需撰寫很長的 SQL 語法或利用 Stored Procedure 進行資料的整理,再自行撰寫程式將報表呈現出來,既費時又費工。現在,可以利用Smart Query報表工具快速製作符合使用者需求的報表

  此篇將以上述舉例來說明利用Smart Query整理資料、製作即時BI報表以及資料轉置的流程。步驟如下:

  • 使用者利用其Know-How決定報表呈現的方式。

  • IT人員依據使用者需求,將原始資料做資料組合,或稱萃取(Extract),組成使用者想看的資料樣貌。

  • 利用格式化條件,設定顏色來標示異常的數據。

  • 利用Excel與Report Designer設計複雜報表。

  • 若有需要,可搭配Transaction將資料回寫到資料庫,即ETL中的轉置(Transform)。

即時BI報表製作流程與資料轉置

圖1. 即時BI報表製作流程與資料轉置

  即使SQL語法能力沒有很強大,也能做到複雜的報表,一起來輕鬆製作報表!


使用者Know-How

  IT在製作報表之前,通常必須先與使用者溝通需求,因為使用者擁有該領域的Know-how,最了解哪些資料是最重要的,要如何編排呈現才能符合他們的觀看需求。接著,IT就能依據使用者需求進行資料整理。


IT進行資料整理

  依上述案例來說,IT會先透過 SQL 語法,各別組出3段Query,如圖2。接著,進入資料組合階段。Smart Query支援以多樣的方式整合各種類型的資料來源,包含過濾條件、群組加總、連結資料、合併資料、分類統計,以及樞紐分析。以上皆透過滑鼠點選來操作,資料組合步驟如下:

  • 透過過濾條件,各別篩選出已出貨、未出貨資料。

  • 透過聯結資料,將未出貨的資料與匯率聯結。

  • 透過合併資料,將未出貨已出貨資料合併。

  • 透過群組加總,將明細資料Sum,以達成使用者想呈現的報表資料。

資料組合處理流程,對OLTP資料來源進行整理

圖2. 資料組合處理流程,對OLTP資料來源進行整理

  資料整理完成後,可以再將重要的欄位設定格式化條件讓使用者需要特別留意的數據利用顏色凸顯出來。例如,將Amt(TWD)設定為當數值小於5000時,顯示橘底色。

  Smart Query 在顏色管理的設計上非常容易操作,就像Excel的格式化條件,您可以定義各欄位在各種條件下的顯示顏色,不管是Web查詢或是Web報表都會依照此規格顯現顏色標示,且從簡單到複雜的條件判斷都適用:

  • 簡單的判斷公式:金額欄位< 5000 ,顏色:橘色。(圖3)

  • 複雜的判斷公式:a欄位='D' and b欄位='E' and 金額欄位> 500000,顏色:紫色。

將欄位設定格式化條件,顯示顏色標示。

圖3. 將欄位設定格式化條件,顯示顏色標示。

  當資料與顏色標示準備完成後,即可透過報表工具快速設計版型,製作符合企業各式需求的管理報表。聯銓報表製作工具Report Designer結合了Excel介面,利用點選的方式帶入想呈現的欄位,且沿用Excel排版工具列功能,像是調整表格框線、文字樣式、儲存格格式、圖表功能等,讓使用者可以像平常在編輯試算表一樣直覺便利。另外,可以將常用的報表版面存成樣板供未來使用,不需重複設計。

  如下圖,左方報表設計視窗列出所有資料欄位,IT人員或報表設計者可利用貼上的方式,將欄位名稱與欄位值貼在適當的格子中,在Subtotal、Total處,也可貼上經過合計的欄位。最後,可以利用Excel的排版工具做美化,讓資料更容易閱讀,如:框線、綠底色、紅字…等。

Smart Query報表設計畫面,左方視窗為聯銓報表製作工具Report Designer

圖4. Smart Query報表設計畫面,左方視窗為聯銓報表製作工具Report Designer

  報表預覽時,可下拉選擇不同月份,查看當月各客戶的統計交易金額及利潤比。最後,可搭配排程模組進行定期派送,自動抓取當月條件並自動Mail給管理者。

預覽報表結果

圖5. 預覽報表結果

  當頁面設計完成所有的資料處理流程後,想再次利用回寫到資料庫,則可透過自訂資料倉儲 (Data Warehouse) 中的資料輸出 (Transaction)功能,資料回寫到資料庫

  如何做到?在自訂資料倉儲 (Data Warehouse) 中,將原頁面設計的資料組合流程再同樣寫一份至自訂資料倉儲(Data Warehouse),接著再啟用資料輸出 (Transaction)功能,設定要將處理後的資料比對目的資料表的值,同時進行該筆資料的UPDATE、INSERT、累加、累減的效果,若這些功能還不夠,也另外提供自組SQL語法,自行控制資料流回寫資料庫的方式,十分彈性。

  觸發執行時機點,可透過排程模組設定固定時間處理,或自行撰寫 Batch File後,利用Windows排程進行觸發,定期執行。

Transaction回寫資料庫設計便利

圖6. Transaction回寫資料庫設計便利

  利用Smart Query製作即時BI報表能讓IT人員省下許多時間與精力,能利用滑鼠點選的方式快速又簡單地做資料組合,提升報表製作效率。而整理後的資料並不只能用再此一報表,還能寫回資料庫作其他應用。即時BI報表,我只服Smart Query!



此篇分類於: 聯銓資訊 Smart BI平台 商業智慧 Smart Query Web報表 報表設計 合併SQL 複合SQL Store Procedure 資料處理(ETL)



我們的臉書粉絲團

我們的YouTube頻道

若有興趣請立即連繫:service@lcnet.com.tw

還沒有接收到聯銓最新消息?點選右上方的「註冊」加入會員,最新消息第一時間寄信通知您!