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)存當中，并且經(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)在似乎很少有人愿意這么做途乃，但我覺得定義好還是一個不錯的習慣）绍傲，那么在這里面會直接檢測到惶翻，并且自動勾選上那些維表撩笆。點擊這個按鈕后，可以發(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ù)這些方案不同的特點具體情況具體分析。
[quick-chat room=”PowerPivot”]
轉(zhuǎn)載于：http://www.17bigdata.com/使用powerpivot建立簡單的分析模型.html
