BI數(shù)據(jù)可視化報表開發(fā)：分析模型VS自定義SQL
???? ?? 各位數(shù)據(jù)的朋友，大家好，我是老周道數(shù)據(jù)，和你一起，用常人思維+數(shù)據(jù)分析，通過數(shù)據(jù)講故事。
??????? 很多朋友會問：“以前用報表工具開發(fā)了很多報表，也寫了很多的SQL，這次想移植到奧威BI中來，但在奧威BI軟件中怎么通過自定義SQL來配置圖表？”如果我說奧威BI軟件雖然有自定義SQL這個功能，但是基本上強烈建議大家不去使用。你一定會覺得不可思議。為什么？用自定義SQL來實現(xiàn)數(shù)據(jù)可視化不是更容易上手嗎？別急，請大家耐心把今天的內(nèi)容看完。我相信大家以后再也不愿意回到寫SQL的年代了！
??????? 亨利·福特曾說過:如果我最初問消費者他們想要什么,他們會告訴我”要一匹更快的馬”！奧威BI軟件不是更快的報表工具，它是為了解決用戶在數(shù)據(jù)分析中遇到的需求場景：如何更快發(fā)現(xiàn)數(shù)據(jù)中的問題，如何更快的分析原因？
??????? 奧威BI軟件就是將以往報表工具由需求部門提需求，然后IT部門開發(fā)報表的這樣一種開發(fā)模式，改為IT人員僅需要開發(fā)數(shù)據(jù)倉庫并構(gòu)建分析模型，至于報表的開發(fā)，則完全由需求用戶自行來完成。要想實現(xiàn)這樣的改變，有兩個地方要進行創(chuàng)新：1、構(gòu)建分析模型要簡單；2、前端報表要實現(xiàn)0代碼拖拖拽拽就可以完成。我們今天就通過一些示例來看一下奧威BI軟件是如何做到這些的。
??????? 自定義SQL：用個性化的開發(fā)去滿足個性化的需求
??????? 對于一個有經(jīng)驗的報表開發(fā)者而言，已經(jīng)非常習(xí)慣根據(jù)用戶的需求，通過自定義SQL來開發(fā)報表?；貞浺幌?，當我們拿到用戶的報表需求表樣時，就會開始這樣的構(gòu)思：報表中有哪些列，這些列會用到哪些聚合函數(shù)，比如銷售收入，用sum的方式聚合，亦或者哪些列之間有什么樣的計算公式，比如毛利=收入-成本。這樣，我們就基本確定了select語句中的各列是如何定義的；然后再看表樣中的行是什么，來決定我們是按什么來構(gòu)建group by。然后再看表樣中需要按什么進行篩選，比如按時間，那么，我們就加上相應(yīng)的where條件。
??????? 通過這樣的思維方式，我們來確定我們要開發(fā)的SQL查詢語句，如果涉及到多表，則通過join來實現(xiàn)，如果再復(fù)雜些，涉及到中間表或其他更復(fù)雜的計算，則通過存貯過程來實現(xiàn)。
??????? 不管我們最終是通過select查詢，還是存貯過程，我們都將這種自定義SQL的方式稱之為：是用個性化的開發(fā)去滿足個性化的需求。
??????? 為什么這樣說呢？因為我們在根據(jù)用戶的需求開發(fā)時，一定是用戶要什么就開發(fā)什么，絕對不會去想用戶可能還需要什么，也絕對不會畫蛇添足的去做一些其他無關(guān)的開發(fā)，假設(shè)用戶的報表有10列，我們絕對不會開發(fā)第11列。而一旦用戶第二天告訴你，不好意思，我還想加另外一個字段，或者我漏掉了一個計算規(guī)則。這時，你又得重新根據(jù)用戶的想法開發(fā)。所以，在現(xiàn)實場景中，IT開發(fā)人員經(jīng)常加班加點開發(fā)完一個報表，第二天用戶就把需求給改變了，還報怨說，在EXCEL一下就改過來了，怎么到你們手上還要花那么長時間呢？
??????? 怎么辦？如何改變這種IT費力不討好，需求部門卻不停排隊等開發(fā)的狀況呢？有沒有更好的辦法？有的，這就是用構(gòu)建分析模型的方式去應(yīng)對需求，我們稱之為用共性的開發(fā)去滿足個性化的需求。
??????? 構(gòu)建分析模型：用共性的開發(fā)去滿足個性化的需求
??????? 很多人一聽分析模型這么高大上的概念，就感覺這一定很復(fù)雜啊。其實并不復(fù)雜，我們只需要通過幾個簡單的問題，就可以將分析模型構(gòu)建出來，接下來，我們舉一個銷售分析的例子，來看我們是怎么通過幾個簡單的問題，就將一個銷售分析模型構(gòu)建出來的：
??????? 第一個問題：我們要分析什么業(yè)務(wù)——即確定分析主題。這里我們明確是分析銷售的業(yè)務(wù)，也就是說分析主題為銷售分析；
??????? 第二個問題：這個分析主題要從哪些角度分析? ——即確定分析維度。那我們來看一下，銷售分析要從哪些角度來分析呢？我們通常要從產(chǎn)品、客戶、業(yè)務(wù)員這幾個維度來分析，另外，這些維度通常還會衍生出許多其他的屬性，比如產(chǎn)品可能還有不同的品類、品牌的，而客戶還區(qū)分不同的客戶級別、客戶分類等，這些也都是分析的維度。另外，還有一個非常重要不可或缺的維度，那就是時間維度，而時間維度又會區(qū)分為年、季、月、周、日甚至?xí)r分秒不同的顆粒度，這些，都是分析維度，這么一細分，我們可能就會有10多種甚至數(shù)十種分析的維度出來了。
??????? 接下來第三個問題：這個分析主題要分析哪些指標? ——即確定分析的度量值和計算成員。那銷售分析需要分析哪些指標呢？一般都會有銷售金額、銷售數(shù)量、單價、成本這些基本的指標，另外，還會有一些指標是基于這些基本指標計算出來的，比如毛利=銷售金額-成本，還有一些衍生出來的統(tǒng)計指標，比如銷售金額的占比，同比，本年累計等。這些就叫做計算成員。
??????? 當我們回答完這些問題的時候，閉上眼睛想一想，原來銷售分析模型可以從這么多維度來分析，又可以分析這么多的指標，那是不是類似于一個魔方（立方體）呢？維度相當于水平的單元，而指標相當于垂直的單元，各單元又可以任意的進行旋轉(zhuǎn)組合。大家想想，是不是所有關(guān)于銷售的分析，無外乎就是這些維度和這些指標的排列組合呢？
??????? 有了這么一個立體的分析模型，最后，我們就只要再回答這些數(shù)據(jù)要從哪里取數(shù)？——即確定數(shù)據(jù)視圖。比如銷售的數(shù)量是從訂單表的哪個字段上取，客戶名稱又是來自于哪個表哪個字段？銷售訂單表和客戶表該如何關(guān)聯(lián)？
??????? 經(jīng)過了上述幾個步驟，我們就基本上完成了一個銷售分析模型的構(gòu)建。大家想一想，是難還是不難呢？可能有的人會問，我怎么知道銷售分析會從哪些維度、分析哪些指標啊，會不會因為我沒有經(jīng)驗，就會漏掉很多內(nèi)容??？答案是：不會的，不需要什么經(jīng)驗，你也一定可以想清楚銷售模型要分析哪些指標，從哪些維度去分析。為什么這么說呢？其實我們只要回歸到業(yè)務(wù)本身就可以了。比如銷售分析，我們一般是根據(jù)銷售訂單或者銷售出庫單來進行分析的，所以，我們只要回歸到ERP的銷售訂單或銷售出庫單就可以。大家就會發(fā)現(xiàn)，ERP中銷售訂單上會有很多的字段，這些字段我們只要大概的分一下類就可以了：將不需要分析的字段，如送貨地址去掉；然后再看哪些字段是數(shù)值型的，可以用來統(tǒng)計度量的，比如銷售數(shù)量、單價之類的，我們就將之歸為分析指標，而其他如客戶、業(yè)務(wù)員、產(chǎn)品則是分析維度。
??????? 為了再幫助大家理解，我們再引出兩個概念，一個是事實表，一個是維度表。這里的事實表即涉及到交易的表，比如銷售訂單，銷售出庫單，他們存貯了交易事實，我們稱之為事實表，而客戶、產(chǎn)品這些存貯基礎(chǔ)資料的表，因為他們通常是用作為分析的維度，所以我們則稱之為維度表。
??????? 講到了現(xiàn)在，大家可能會覺得，這個分析模型似乎也沒有什么神秘的，但是，也沒有覺得這個分析模型有什么用啊。
??????? 接下來，我們就來舉幾個需求場景的例子，來對比一下，傳統(tǒng)的自定義SQL與分析模型的方式，哪個更簡單，區(qū)別又在哪里？
?
??????? 假設(shè)有一天，銷售部的銷售助理小芳找你來開發(fā)報表，說：“強哥，我想按客戶統(tǒng)計截止到當前的訂單出庫率，要導(dǎo)出好多數(shù)據(jù)，好麻煩啊，你能不能幫我啊！”
??????? 這時的你一定已經(jīng)在腦海中將所需要的SQL都已經(jīng)寫出來了：
??????? Select 客戶,sum(訂單數(shù)量), sum(出庫數(shù)量), sum(出庫數(shù)量)/ sum(訂單數(shù)量) as 訂單出庫率 from (
Select 客戶,sum(訂單數(shù)量) from 訂單表 join 客戶表 on訂單表.客戶ID=客戶表.客戶ID
Where訂單表.時間<=時間條件 group by客戶
) a,(
Select 客戶,sum(出庫數(shù)量) from 出庫表 join 客戶表 on出庫表.客戶ID=客戶表.客戶ID
Where出庫表.時間<=時間條件group by客戶
) b
Where a.客戶=b.客戶 group by客戶
?
??????? 第二天，小芳又提出新需求：“不好意思啊，強哥，光按客戶來看訂單出庫率還不夠，還要按客戶所在的區(qū)域，除了要看這個月的總的訂單出庫率，還要看與去年同期相比是高還是低，另外，還要按每個月來看這個訂單出庫率有沒有什么變化?！?/strong>”
??????? 這時，你的心情就變得五味雜陳了，心里想：怎么不早說啊？但看著小芳那雙水汪汪充滿期待的大眼睛，你只得故作輕松道：“行，沒問題！”
??????? 于是，你將原來的SQL復(fù)制出來，分別修改了一下。雖然其中那個訂單出庫率同比需要用到存貯過程，但也很快完成了。于是，你的SQL就變成了4個：
??????? 1、?客戶訂單出庫率；
??????? 2、?客戶區(qū)域訂單出庫率；
??????? 3、?總的訂單出庫率同比；
??????? 4、?每個月的訂單出庫率。
??????? 在另外一個平行宇宙里，小芳和你說完她的想法，剛要離開，你叫住了她，說：“小芳別走，強哥這就教你在BI軟件中怎么做。”
??????? 第一步，構(gòu)建一個同時包含訂單事實表與出庫事實表的分析模型。
?
??????? 通過拖拉、點擊將銷售訂單、銷售出庫分別和客戶表關(guān)聯(lián)起來。
??????? 第二步，就可以直接做相應(yīng)的報表了。
??????? 1、添加表格，視圖選擇剛剛上一步建好的【訂單出庫率視圖】。
??????? 將訂單數(shù)量、出庫數(shù)量添加到【匯總】后，通過【fx】自定義表達式獲得訂單出庫率指標。
??????? 訂單數(shù)量：點擊【匯總】旁的【+】，打開銷售訂單，勾選【訂單數(shù)量】，點確定。
??????? 出庫數(shù)量：點擊【匯總】旁的【+】，打開銷售出庫，勾選【出庫數(shù)量】點確定。
??????? 訂單出庫率：點擊【匯總】旁的【+】，點擊【fx】，勾選【匯總區(qū)域】后，分別在計算成員1、自定義表達式中輸入訂單出庫率、出庫數(shù)量/訂單數(shù)量，點擊確定。
?????? 2、將客戶維度添加到行維度中：點擊【行維度】旁的【+】，打開客戶表，勾選【客戶名稱】，點確定。
??????? 3、復(fù)制表格
??????? 將該表格命名為【客戶訂單出庫情況】后，點擊選中該表格，點擊【復(fù)制黏貼】，點擊【復(fù)制到報表】，點確定。
??????? 點擊數(shù)據(jù)集【客戶】右側(cè)【…】，點擊【復(fù)制】后，重命名為【區(qū)域】。
??????? 點擊【行維度】中的【客戶名稱】旁的【……】，點擊【刪除】。點擊【行維度】旁的【+】，勾選【一級客戶名稱】，點確定。
??????? 最后將表格名稱修改為【區(qū)域訂單出庫情況】即可獲得以下效果，這樣就可以按客戶區(qū)域來看訂單出庫率了。
??????? 用同樣的方法復(fù)制一個新表格，將數(shù)據(jù)集修改成【月份】，行維度修改成【時間年】、【時間月】，這樣就可以按年月來看訂單出庫率。
??????? 最后用同樣的方法再復(fù)制一個表格，計算去年的訂單出庫率。
??????? 總的思路為：數(shù)據(jù)集名稱改為【總】；刪除原定的行維度；計算去年訂單數(shù)和出庫數(shù)，然后再通過自定義表達式計算去年的訂單出庫率。
??????? 去年訂單數(shù)量：點擊【訂單數(shù)量】旁的【……】，點擊【同期】，選擇【范圍同比（上年范圍）】，點確定。
??????? 去年出庫數(shù)量：點擊【出庫數(shù)量】旁的【……】，點擊【同期】，選擇【范圍同比（上年范圍）】，點確定。
?????? 去年的訂單出庫率：點擊【匯總】旁的【+】，點擊【fx】，勾選【匯總區(qū)域】后，分別在計算成員1、自定義表達式中輸入去年訂單出庫率、上年出庫數(shù)量/上年訂單數(shù)量，點擊確定。
??????? 之后，將表格排版位置調(diào)整一下，再把按月查看的表格替換成折線圖，即點擊選中該表格，點擊【更換圖表】，點擊選擇一個折線圖。BI數(shù)據(jù)可視化系統(tǒng)就會自動替換。
??????? 最后，添加一個按時間日期查詢的篩選控件。即點擊上方的【+】，點擊【篩選】下的【公共篩選】，勾選【時間日期】，并在左下角勾選【日歷（范圍）】后點擊確定。最終獲得以下效果：
????? ? 增加圖表聯(lián)動功能：
??????? 單擊選定歷史訂單出庫情況表，點擊【聯(lián)動】，選擇【圖表聯(lián)動】，點擊選擇區(qū)域訂單出庫情況表、客戶訂單出庫情況表，點擊確定。
??????? 單擊選中客戶訂單出庫情況表，點擊【聯(lián)動】，選擇【圖表聯(lián)動】，點擊選擇區(qū)域訂單出庫情況表，點擊確定。
??????? 點擊數(shù)據(jù)集構(gòu)建器下篩選項【時間日期】旁的【…】，在條件篩選器中，將篩選條件改成篩選【近期（T+0）】、【-12】、【月】，點確定。如下圖：
??????? 這時，當我們點擊區(qū)域訂單出庫情況表中的【上海市】時，就能同時看到上海市的客戶訂單出庫情況，以及上海市歷史訂單出庫情況。如果我們點擊某個客戶，還可以看到這個客戶的歷史訂單情況。
??????? 小芳：“如果我還想按產(chǎn)品來分析訂單出庫率呢？”
??????? 小強：“沒問題，根據(jù)你的想法，我只需要在原來的分析模型上增加一個產(chǎn)品維度?！?/span>
?????? 刷新后，點擊選中區(qū)域訂單出庫情況表，點擊【復(fù)制粘貼】，選擇【復(fù)制到報表】，點確定。再次重復(fù)以下操作：復(fù)制數(shù)據(jù)集；點【…】刪掉原行維度，點【+】將物料表中的【物料名稱】設(shè)為行維度；
?????? 這時候就可以按產(chǎn)品來看每個區(qū)域的訂單出庫情況。其實有了這個分析模型，不僅可以分析訂單出庫率，還可以分析與訂單或出庫相關(guān)的其他分析，比如分析每個區(qū)域的銷售占比。
?????? 點擊【+】，添加表格，點擊【匯總】旁的【+】，勾選【銷售金額】點確定；點擊【行維度】旁的【+】，勾選【一級客戶名稱】，點確定，就可以看到每個區(qū)域的銷售數(shù)據(jù)。
??????? 然后點擊【銷售金額】旁的【…】，選擇【占比】，點擊【行占比】，即可看到每個區(qū)域的銷售占比情況。
??????? 如果要看每個區(qū)域不同月份的銷售情況，只需點擊【列維度】旁的【+】，勾選【時間年】、【時間月】，點確定。
??????? 只要你能想到的，都可以通過這種方式組合起來。
??????? 好，我們來回顧一下小強的操作，分為兩部分，一部分是構(gòu)建分析模型，第二部分就是根據(jù)這個分析模型制作報表，大家會發(fā)現(xiàn)，整個過程不需要寫任何SQL，只需要理解業(yè)務(wù)需求實現(xiàn)出來就可以了，并且，這個分析模型還可以快速迭代，如果后面小芳想按業(yè)務(wù)員這個維度來分析，就只要將業(yè)務(wù)員維度加進來就可以了。
??????? 例子講完了，大家會發(fā)現(xiàn)，通過分析模型來開發(fā)報表，靈活、高效，而且開發(fā)及運維的成本非常低。但在實踐中，要改變以往的開發(fā)習(xí)慣還是有些困難的。
敲黑板，講重點
??????? 對于寫SQL的“高手”來說，需要進行以下的觀念轉(zhuǎn)變，才能跳出“報表開發(fā)”的泥沼，讓開發(fā)變得更輕松：
??????? 首先要理解，自定義SQL是用個性化的開發(fā)去滿足個性化的需求，而分析模型則是用共性的開發(fā)去滿足個性化的需求。
??????? 開發(fā)時，要還原到最原始業(yè)務(wù)單據(jù)，用最明細的業(yè)務(wù)顆粒度來構(gòu)建分析模型，盡量不要根據(jù)需求來構(gòu)建中間結(jié)果表。很多開發(fā)人員會說，為什么不將用戶想要的結(jié)果直接計算出來，用戶直接查詢就可以，這樣查詢效率最高，同時，用戶還沒有學(xué)習(xí)成本。但在這里，我是建議千萬別這樣。為什么呢？舉個簡單的例子。假如我們是想看各區(qū)域的銷售占比，這時，我們用group by區(qū)域并計算出其占比。但如果用戶又突然想從產(chǎn)品線的維度來看銷售占比，怎么辦？這時，開發(fā)人員又得重新完成中間結(jié)果表的開發(fā)。換言之，只要用戶的需求發(fā)生了一點變化，開發(fā)人員都必須去響應(yīng)。而如果我們只是用最原始的交易單據(jù)加上主數(shù)據(jù)構(gòu)建的分析模型去響應(yīng)用戶需求，就非常簡單，用戶想按什么維度去分析銷售占比，都只要在前端構(gòu)建器中更換維度字段即可，完全可以通過自己的鼠標秒級切換，開發(fā)人員根本不需要介入！
??????? 還有一個就是，公共的維度要拿出來獨立，不要join在大表里：比如說客戶名稱，不要在銷售訂單里有客戶名稱，出庫單里也有客戶名稱，雖然大家可以理解到訂單里的客戶名稱與出庫單里的客戶名稱是同一個東西，但對于系統(tǒng)來說，它卻并不能這么聰明地理解。這也就是為什么很多報表工具，在不同的報表之間跳轉(zhuǎn)時，如果要從上一張報表將客戶名稱傳遞到下一張報表，需要強制進行參數(shù)的映射。但奧威BI軟件可以不需要這樣操作，后續(xù)再找個專題講一下奧威BI軟件最有特色的一個功能，就是智能分析路徑功能，它可以在系統(tǒng)中實現(xiàn)任意報表之間的智能穿透鉆取，參數(shù)是完全自動完成匹配傳遞的。
??????? 另外再請大家記住以下幾個常用概念：
??????? l?維度：就是觀察數(shù)據(jù)的一種角度。如客戶，時間都是維度，
??????? l?維度值：就是構(gòu)成某個維度的基本內(nèi)容。如客戶分類維度中的分類1、分類2，或時間季度維度中Q1、Q2等。
??????? l?度量值：就是要分析展示的數(shù)據(jù)，即指標。如銷售數(shù)量。
??????? l?計算成員：基于度量值進行再計算的分析指標，如本年累計銷售數(shù)量。
??????? l?事實表：存放交易記錄的表，包括度量值和維度ID。
??????? l?維度表：一個維度對應(yīng)一個或者多個維度表。比如客戶維度對應(yīng)客戶表和客戶分類表。
?
??????? 作為BI開發(fā)人員，只有你真正理解了分析模型怎么構(gòu)建，怎么使用，你才算是從報表認知躍遷到BI認知，從一個報表開發(fā)人員升級成為一個BI開發(fā)人員。?
??????? 老周道數(shù)據(jù)，和你一起，用常人思維+數(shù)據(jù)分析，通過數(shù)據(jù)講故事，我們下一講再見！
