使用PowerPivot建立簡單的分析模型

樞紐分析表是一個很靈活的工具，通過這個工具使用者可以很容易的產生自己需要的報表。無論是對於專業的IT使用者還是業務部門的使用者，他們都很熟悉Excel這個工具，並且對於PowerPivot的使用方法也相當的"爐火純青"。

傳統透視表的資料來源可以是Excel工作表，也可以是分析服務中的Cube這兩種主要的方式。相對前者由於資料是儲存在Excel的工作表中，所以業務操作人員很容易上手，很適合小規模的資料統計分析。後者分析服務的Cube這種方式，由於資料是以一種特殊的方式彙總在獨特的檔案系統中，所以適合大規模的資料量分析，缺點是分析服務的開發對於IT的要求比較高，只能由IT人員完成，所以業務人員的一個需求往往會等待很長的時間才會得到響應。

那麼，業務操作人員是否可以有一種高效能的去分析稍微大一點的規模的資料呢？PowerPivot就是微軟提供的一個方案。在這個方案中，資料直接載入到記憶體當中，並且經過一定的最佳化，保證了通過透視表的統計有一個很高的效能。

 

 

首先，在Excel 2013之前的版本中，這個工具是需要單獨下載的。如果你沒有Office 2013，那麼我建議你的版本不要低於2010，在這個版本之中PowerPivot的版本得以演化。

：

http://www.microsoft.com/en-us/download/details.aspx?id=29074

下載需要留意Excel對應的語言版本還有是32位版還是64位版。

還有需要注意的一個地方是，這個是PovitTable是針對 Excel 2010的第二個版本，之前還有一個版本，在微軟目前的教程以及本文的介紹中缺失了部分功能。所以如果你已經先前安裝了PowerPivot，請務必確認這個版本是否正確。

安裝完畢後，開啟Excel後，可以看到Ribbon菜單中多了一項：

使用這個工具前，需要先準備資料。你可以直接使用在 Excel工作表裡面的資料，也可以使用SQLServer等其它資料來源的資料。

這裡假定一個銷售部門的資料，已經在IT部門的資料倉儲中存在了，而銷售分析人員，只需要把相關的資料匯入到PowerPivot中，然後通過簡單的設定就可以產生自己的分析模型了。

在PowerPivot選項卡中單擊PowerPoint Window，會開啟PowerPivot工具：

假定IT部門已經授予了銷售分析部門的資料倉儲系統部分響應表的存取權限，那麼這裡分析人員需要做的就是把相應的表匯入到PivotTable工具中。

點擊工具列中的From Database：

選擇From SQL Server。從這裡可以看到，PowerPivot支援的資料來源很多，還有Access和SSAS等。

在彈出的表匯入工具中，輸入資料倉儲所在的伺服器名稱和資料倉儲的名稱。

這裡我們使用微軟的樣本資料庫Adventure Works來做示範，關於如何擷取和部署這些樣本，可以參考我的這篇隨筆。

設定好串連資訊後，點擊Next。

接下來的介面會指定如何匯入資料，是通過選取表或者視圖的方式，還是一個查詢的方式。這裡選擇第一個，點Next。

在資料倉儲下的所有表被列了出來。在這個介面中，可以通過Friendly Name來指定一個易記名稱，然後通過Filter Details指定需要表裡的哪些列。

這裡假定銷售人員要做Internet Sales分析，在列表裡直接找到FactInternetSales表：

這張表是分析用的事實表，然後需要指定相關的維度資料表。

在PowerPivot有一個很贊的功能就是Selected Related Tables，選擇相關表。假如在資料倉儲中已經定義好了主外鍵關係（現在似乎很少有人願意這麼做，但我覺得定義好還是一個不錯的習慣），那麼在這裡面會直接檢測到，並且自動勾選上那些維表。點擊這個按鈕後，可以發現很多Dim開頭的維表已經都被選中了。

實際的操作中，還是建議這裡給每一個表都指定一個Friendly Name，並且做適應的Filter。但這裡為了示範方便直接點Finish開始匯入資料。

工具開始把資料倉儲裡的資料載入到PowerPivot中。完成後點擊Close關閉這個介面。

然後就可以看到被匯入進來的表。

在實際環境中，資料倉儲裡額資料是每天都在發生變化的，那麼如何保持PowerPivot裡的資料跟資料倉儲的資料保持同步呢？

單擊Refresh All，PowerPivot就會根據先前的串連設定重新載入這些資料。

匯入完畢後，把介面切換到Diagram模式：

介面會從資料檢視切換到Diagram模式（順便說一下，Excel 的第一個PowerPivot版是沒有這個Diagram功能的，這也就是為什麼前邊提到一定要確定是第二版）：

在這個關係視圖裡繼承了資料倉儲中定義的主外鍵結構（熟悉SSAS的同學可以把這裡理解為資料來源檢視的定義）。

假如實際環境中，資料倉儲沒有定義這部分內容，就需要自己來指定表之間的關係（這個過程對於開發SSAS的朋友來說，更像是在指定"維度用法"）。而方法很簡單，假如我要建立FactInternetSales表中ProductKey和DimProduct中的ProductKey列的主外鍵關係，只需拖拽FactInternetSales表中的ProductKey欄位到DimProduct表中的ProductKey欄位就可以了。

接下來指定一個階層。建立階層的好處在於，可以方便在後續的透視表操作中，方便維度屬性的導航，比如對於地區維度，從大洲到國家到省再到市，或者一個時間維度的從年到半年再到季度然後月份和天的導航。這裡我們在DimDate表中定義一個年月日的階層導航關係。

右鍵DimDate表，選擇Create Hierarchy：

然後，可以看到在表的後面加入了一個新"列"。

重新命名這個Hierarchy的名稱為DateHierarchy。

然後，一次拖拽表中的如下列到這個建立的層次中：

CalendarYear

EnglishMonthName

DayNumberOfMonth

為了顯示的友好性，右鍵層次中的CalendarYear，選擇Rename將其重新命名為Year，然後依次命名其它層次為Month和Day。

基本的分析模型建立完畢之後，就可以在透視表中瀏覽這些資料了。

，在PivotTable介面中Home標籤點擊PivotTable然後選擇其下的PivotTable。

系統會提示問透視表在建立一個工作表中還是在現有工作表的一個地區，這裡選擇建立。

然後，可以看到熟悉的透視表，並且這個透視表自動連接到了PowerPivot裡的資料。

實際上這種模式中還有一個PowerPivot Filed List，點擊中的Filed List：

可以看到PowerPivot的Filed List要比傳統的透視表Filed List多了兩個切片器。通過它們可以更明了的進行資料切片分析。

比如，要分析銷售出去的產品中，各個顏色的資料以分析使用者對於顏色的偏好：

拖拽DimProduct的Color到Slicers Vertial，DimDate的DateHierarchy到Row Labels，FactInternetSales的Sum of SalesAmount到Values。

圖中可以看到Color切片器，通過這個切片器裡不同顏色的選擇，可以在透視表中依次看到不同顏色的產品分別的銷售額是多少。通過這種切片分析的方法，比透視表中的Report Filter會更直觀一些。

並且可以看到，由於剛才對DimDate建立了一個層次，所以在透視表中使用它的時候，時間變成了可以展開的模式。

 

 

以上，一個簡單的分析模型建立完畢，接下來的分析操作跟傳統的透視表操作是一樣的了，這裡不做詳細介紹。

如本文開頭所描述，跟傳統的透視表相比，PowerPivot是把資料載入到記憶體中的，從工作管理員中我們可以看到Excel此時的記憶體消耗：

正因為資料是被載入到了記憶體，所以可以保證在資料量很多的情況下，通過透視表也可以進行快速的分析。但是，PowerPivot對資料量還是有一定的要求的，參考PowerPivot容量規範：

http://technet.microsoft.com/zh-cn/library/gg413465.aspx

裡面有如下描述：

也就是說，PowerPivot能應付差不多20億條的資料，但還是需要留意這個還要取決於你機器的記憶體大小。所以，對於中等規模的資料分析，PowerPivot還是很合適不過的，而對於更大一點規模額資料，自然用PowerPivot去串連分析服務資料庫是最合適不過的了。具體採用哪一種方案，還需要根據這些方案不同的特點具體情況具體分析。

 

 

BTW:

PowerPivot跟SQL Server 2012 SSAS的Tabular Model很像，它們都是把資料載入到記憶體中做統計分析。這兩個方案，我個人覺得，首先，它們都不是面向IT人員的，都是面向業務分析人員的。前者操作相對容易些，全部在Excel環境裡完成，後者稍微複雜些，需要在Visual Studio的一個Shell中完成，而且需要Tabular Model的特殊的分析服務執行個體做支援，不過它支援的資料量會更大一些。有了這些工具，確實可以滿足業務分析人員的自服務式的分析需求。但在這樣的一個運作模型中，我認為IT的工作還是很重要的，除了給相應的使用者授權之外，還需要組織和維護資料倉儲，而從業務系統到資料倉儲的過程，基本就佔了一個BI項目的大半內容。所以即使PowerPivot的操作是十分方便的，那也是需要IT團隊在後面做很大的支援的。