Excel中的Pivot Table/Pivot Chart可以說是一個(gè)即強(qiáng)大，使用起來又很簡(jiǎn)單的功能。有時(shí)候結(jié)合函數(shù)來使用，基本上都可以解決我在工作中遇到的數(shù)據(jù)分析問題。
在Excel 2010 中又推出了PowerPivot ，是EXCEL 2010的一個(gè)插件，顧名思義強(qiáng)大版的Pivot。根據(jù)官網(wǎng)的介紹，可以處理在短時(shí)間內(nèi)處理很大的數(shù)據(jù)，可以使用DAX(數(shù)據(jù)分析表達(dá)式)來對(duì)數(shù)據(jù)進(jìn)行分析處理，同時(shí)在擁有很多強(qiáng)大功能的同時(shí)，不會(huì)占用很大的內(nèi)存和CPU,可以有效地利用內(nèi)存和CPU.
傳統(tǒng)透視表的數(shù)據(jù)來源可以是Excel工作表，也可以是分析服務(wù)中的多維數(shù)據(jù)集這兩種主要的方式。相對(duì)前者由于數(shù)據(jù)是存儲(chǔ)在Excel的工作表中，所以業(yè)務(wù)操作人員很容易上手，很適合小規(guī)模的數(shù)據(jù)統(tǒng)計(jì)分析。后者分析服務(wù)的多維數(shù)據(jù)集這種方式，由于數(shù)據(jù)是以一種特殊的方式聚合在獨(dú)特的文件系統(tǒng)中，所以適合大規(guī)模的數(shù)據(jù)量分析，缺點(diǎn)是分析服務(wù)的開發(fā)對(duì)于IT的要求比較高，只能由IT人員完成，所以業(yè)務(wù)人員的一個(gè)需求往往會(huì)等待很長的時(shí)間才會(huì)得到響應(yīng)。
那么，業(yè)務(wù)操作人員是否可以有一種高性能的去分析稍微大一點(diǎn)的規(guī)模的數(shù)據(jù)呢？PowerPivot就是微軟提供的一個(gè)方案。在這個(gè)方案中，數(shù)據(jù)直接加載到內(nèi)存當(dāng)中，并且經(jīng)過一定的優(yōu)化，保證了通過透視表的統(tǒng)計(jì)有一個(gè)很高的性能。
首先，在Excel 2013之前的版本中，這個(gè)工具是需要單獨(dú)下載的。如果你沒有Office 2013，那么我建議你的版本不要低于2010，在這個(gè)版本之中PowerPivot的版本得以演化。
下載需要留意Excel對(duì)應(yīng)的語言版本還有是32位版還是64位版。
還有需要注意的一個(gè)地方是，這個(gè)是PovitTable是針對(duì) Excel 2010的第二個(gè)版本，之前還有一個(gè)版本，在微軟目前的教程以及本文的介紹中缺失了部分功能。所以如果你已經(jīng)先前安裝了PowerPivot，請(qǐng)務(wù)必確認(rèn)這個(gè)版本是否正確。
安裝完畢后，打開Excel后，可以看到Ribbon菜單中多了一項(xiàng)：
使用這個(gè)工具前，需要先準(zhǔn)備數(shù)據(jù)。你可以直接使用在 Excel工作表里面的數(shù)據(jù)，也可以使用SQLServer等其它數(shù)據(jù)源的數(shù)據(jù)。
這里假定一個(gè)銷售部門的數(shù)據(jù)，已經(jīng)在IT部門的數(shù)據(jù)倉庫中存在了，而銷售分析人員，只需要把相關(guān)的數(shù)據(jù)導(dǎo)入到PowerPivot中，然后通過簡(jiǎn)單的設(shè)置就可以生成自己的分析模型了。
在PowerPivot選項(xiàng)卡中單擊powerpoint Window，會(huì)打開PowerPivot工具：
假定IT部門已經(jīng)授予了銷售分析部門的數(shù)據(jù)倉庫系統(tǒng)部分響應(yīng)表的訪問權(quán)限，那么這里分析人員需要做的就是把相應(yīng)的表導(dǎo)入到PivotTable工具中。
點(diǎn)擊上圖工具欄中的From Database：
選擇From sql server。從這里可以看到，PowerPivot支持的數(shù)據(jù)源很多，還有Access和SSAS等。
在彈出的表導(dǎo)入工具中，輸入數(shù)據(jù)倉庫所在的服務(wù)器名稱和數(shù)據(jù)倉庫的名稱。
這里我們使用微軟的示例數(shù)據(jù)庫Adventure Works來做演示，關(guān)于如何獲取和部署這些示例，可以參考我的這篇隨筆。
設(shè)置好連接信息后，點(diǎn)擊Next。
接下來的界面會(huì)指定如何導(dǎo)入數(shù)據(jù)，是通過選取表或者視圖的方式，還是一個(gè)查詢的方式。這里選擇第一個(gè)，點(diǎn)Next。
在數(shù)據(jù)倉庫下的所有表被列了出來。在這個(gè)界面中，可以通過Friendly Name來指定一個(gè)友好名稱，然后通過Filter Details指定需要表里的哪些列。
這里假定銷售人員要做Internet Sales分析，在列表里直接找到FactInternetSales表：
這張表是分析用的事實(shí)表，然后需要指定相關(guān)的維度表。
在PowerPivot有一個(gè)很贊的功能就是Selected Related Tables，選擇相關(guān)表。假如在數(shù)據(jù)倉庫中已經(jīng)定義好了主外鍵關(guān)系（現(xiàn)在似乎很少有人愿意這么做，但我覺得定義好還是一個(gè)不錯(cuò)的習(xí)慣），那么在這里面會(huì)直接檢測(cè)到，并且自動(dòng)勾選上那些維表。點(diǎn)擊這個(gè)按鈕后，可以發(fā)現(xiàn)很多Dim開頭的維表已經(jīng)都被選中了。
實(shí)際的操作中，還是建議這里給每一個(gè)表都指定一個(gè)Friendly Name，并且做適應(yīng)的Filter。但這里為了演示方便直接點(diǎn)Finish開始導(dǎo)入數(shù)據(jù)。
工具開始把數(shù)據(jù)倉庫里的數(shù)據(jù)加載到PowerPivot中。完成后點(diǎn)擊Close關(guān)閉這個(gè)界面。
然后就可以看到被導(dǎo)入進(jìn)來的表。
在實(shí)際環(huán)境中，數(shù)據(jù)倉庫里額數(shù)據(jù)是每天都在發(fā)生變化的，那么如何保持PowerPivot里的數(shù)據(jù)跟數(shù)據(jù)倉庫的數(shù)據(jù)保持同步呢？
如圖單擊Refresh All，PowerPivot就會(huì)根據(jù)先前的連接設(shè)置重新加載這些數(shù)據(jù)。
導(dǎo)入完畢后，把界面切換到Diagram模式：
界面會(huì)從數(shù)據(jù)視圖切換到Diagram模式（順便說一下，Excel 的第一個(gè)PowerPivot版是沒有這個(gè)Diagram功能的，這也就是為什么前邊提到一定要確定是第二版）：
在這個(gè)關(guān)系視圖里繼承了數(shù)據(jù)倉庫中定義的主外鍵結(jié)構(gòu)（熟悉SSAS的同學(xué)可以把這里理解為數(shù)據(jù)源視圖的定義）。
假如實(shí)際環(huán)境中，數(shù)據(jù)倉庫沒有定義這部分內(nèi)容，就需要自己來指定表之間的關(guān)系（這個(gè)過程對(duì)于開發(fā)SSAS的朋友來說，更像是在指定"維度用法"）。而方法很簡(jiǎn)單，假如我要建立FactInternetSales表中ProductKey和DimProduct中的ProductKey列的主外鍵關(guān)系，只需拖拽FactInternetSales表中的ProductKey字段到DimProduct表中的ProductKey字段就可以了。
接下來指定一個(gè)層次結(jié)構(gòu)。建立層次結(jié)構(gòu)的好處在于，可以方便在后續(xù)的透視表操作中，方便維度屬性的導(dǎo)航，比如對(duì)于區(qū)域維度的，從大洲到國家到省再到市，或者一個(gè)時(shí)間維度的從年到半年再到季度然后月份和天的導(dǎo)航。這里我們?cè)贒imDate表中定義一個(gè)年月日的層次結(jié)構(gòu)導(dǎo)航關(guān)系。
右鍵DimDate表，選擇Create Hierarchy：
然后，可以看到在表的后面加入了一個(gè)新"列"。
重命名這個(gè)Hierarchy的名稱為DateHierarchy。
然后，一次拖拽表中的如下列到這個(gè)新建的層次中：
CalendarYear
EnglishMonthName
DayNumberOfMonth
為了顯示的友好性，右鍵層次中的CalendarYear，選擇Rename將其重命名為Year，然后依次命名其它層次為Month和Day。
基本的分析模型建立完畢之后，就可以在透視表中瀏覽這些數(shù)據(jù)了。
如圖，在PivotTable界面中Home標(biāo)簽點(diǎn)擊PivotTable然后選擇其下的PivotTable。
系統(tǒng)會(huì)提示問透視表在新建一個(gè)工作表中還是在現(xiàn)有工作表的一個(gè)區(qū)域，這里選擇新建。
然后，可以看到熟悉的透視表，并且這個(gè)透視表自動(dòng)連接到了PowerPivot里的數(shù)據(jù)。
實(shí)際上這種模式中還有一個(gè)PowerPivot Filed List，點(diǎn)擊上圖中的Filed List：
可以看到PowerPivot的Filed List要比傳統(tǒng)的透視表Filed List多了兩個(gè)切片器。通過它們可以更明了的進(jìn)行數(shù)據(jù)切片分析。
比如，要分析銷售出去的產(chǎn)品中，各個(gè)顏色的數(shù)據(jù)以分析用戶對(duì)于顏色的偏好：
拖拽DimProduct的Color到Slicers Vertial，DimDate的DateHierarchy到Row Labels，F(xiàn)actInternetSales的Sum of SalesAmount到Values。
圖中可以看到Color切片器，通過這個(gè)切片器里不同顏色的選擇，可以在透視表中依次看到不同顏色的產(chǎn)品分別的銷售額是多少。通過這種切片分析的方法，比透視表中的Report Filter會(huì)更直觀一些。
并且可以看到，由于剛才對(duì)DimDate建立了一個(gè)層次，所以在透視表中使用它的時(shí)候，時(shí)間變成了可以展開的模式。
以上，一個(gè)簡(jiǎn)單的分析模型創(chuàng)建完畢，接下來的分析操作跟傳統(tǒng)的透視表操作是一樣的了，這里不做詳細(xì)介紹。
如本文開頭所描述，跟傳統(tǒng)的透視表相比，PowerPivot是把數(shù)據(jù)加載到內(nèi)存中的，從任務(wù)管理器中我們可以看到Excel此時(shí)的內(nèi)存消耗：
正因?yàn)閿?shù)據(jù)是被加載到了內(nèi)存，所以可以保證在數(shù)據(jù)量很多的情況下，通過透視表也可以進(jìn)行快速的分析。但是，PowerPivot對(duì)數(shù)據(jù)兩還是有一定的要求的，參考PowerPivot容量規(guī)范：
http://technet.microsoft.com/zh-cn/library/gg413465.aspx
里面有如下描述：
也就是說，PowerPivot能應(yīng)付差不多20億條的數(shù)據(jù)，但還是需要留意這個(gè)還要取決于你機(jī)器的內(nèi)存大小。所以，對(duì)于中等規(guī)模的數(shù)據(jù)分析，PowerPivot還是很合適不過的，而對(duì)于更大一點(diǎn)規(guī)模額數(shù)據(jù)，自然用PowerPivot去連接分析服務(wù)數(shù)據(jù)庫是最合適不過的了。具體采用哪一種方案，還需要根據(jù)這些方案不同的特點(diǎn)具體情況具體分析。
喜歡 
頂
難過
囧
圍觀
無聊
