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