鏈接、鏈接回表

? ? Excel與Powerpivot交互的鏈接表

? ? （部分內(nèi)容依據(jù)簡體筆記修改）

? ? ? 鏈接表并不屬于準(zhǔn)數(shù)據(jù)源，但這可能是將數(shù)據(jù)加載到PowerPivot 的最簡單直接的方式。如果你需要一個尚未加載到任何數(shù)據(jù)庫中的表格，則可以簡單地創(chuàng)建一個Excel 工作表，并將其鏈接加載到PowerPivot 表。
? ? ? 由于創(chuàng)建鏈接表如此容易，我們不僅要展示給你如何創(chuàng)建鏈接表，還要告訴你如何使用鏈接表立即更新數(shù)據(jù)模型，并創(chuàng)建交互式報告需要的維度或度量。
? ? ? 要加載表格到PowerPivot ，只需單擊ExceⅠ數(shù)據(jù)區(qū)內(nèi)任意單元格，然后單擊PowerPivot 功能區(qū)的“ 添加到數(shù)據(jù)模型” 按鈕（見圖) 。表格就立即載入到Power Pivot 數(shù)據(jù)庫中，且模型中的名稱與Excel 表格名稱相同。一旦該數(shù)據(jù)加載到數(shù)據(jù)模型中，就能被看到，如圖所示：

? ? ? 此時所有加載進(jìn)Powerpivot的表之間缺失的一環(huán)就是關(guān)系，你可通過在關(guān)系圖視圖中拖動關(guān)系列來創(chuàng)建表關(guān)系，并由此生成一個數(shù)據(jù)模型。這里從略。你巳看到，將數(shù)據(jù)從Excel直接置于數(shù)據(jù)模型內(nèi)部時，鏈接表是很便捷的方式。
? ? ? 此時，你可能想知道，使用Excel自動加載到模型中的表格（例如出于創(chuàng)建關(guān)系的目的）和使用PowerPivot鏈接表添加的表格之間有何異同？畢竟它們似乎執(zhí)行完全相同的操作。但實際上這是不完全正確的。而且這種區(qū)別有些微妙，說實話，大部分時間你是不會注意到這種區(qū)別的。

? ? ? 以Excel為源文件加載到數(shù)據(jù)模型的Excel表格，需要通過單擊“ 刷新” 來手動刷新。而鏈接表在打開Power Pivot窗口或刷新數(shù)據(jù)透視表時就會自動刷新，其交互性更高一點。除了這種微小差異之外，兩種方式的行為基本相同，都是將來自Excel 中的數(shù)據(jù)加載到數(shù)據(jù)模型的有效手段。

? ? 鏈接回--DAX查詢結(jié)果表

? ? ? 盡管我們尚未討論用于創(chuàng)建查詢的DAX函數(shù)，這里還是要先說明學(xué)習(xí)DAX作為查詢語言的主要原因：你可以選擇鏈接回DAX查詢的結(jié)果（銜接回表）。
? ? ? 什么是鏈接回？在詳細(xì)探討之前讓我們了解兩個術(shù)語：
? ? （1）鏈接表。一個鏈接到Power Pivot模型的Excel表格，也就是說，Excel表格的內(nèi)容被復(fù)制到了PowcrPivot數(shù)據(jù)模型中，且PowcrPivot數(shù)據(jù)模型中的該數(shù)據(jù)會隨著原始Excel表格的更新而更新。
? ? （2）逆向鏈接表（鏈接回表）。一個使用Excel表作為源的DAX查詢。每當(dāng)Power Pivot數(shù)據(jù)模型更新時，會對新的數(shù)據(jù)再次執(zhí)行查詢并刷新Excel表格。

? ? ? 因此，鏈接表是將數(shù)據(jù)從Excel移動到PowePr ivot，而將數(shù)據(jù)從數(shù)據(jù)模型（PowePrivot）移動到Excel則是鏈接回表。實際運(yùn)用中，可能需要采取逆向鏈接表并基于其創(chuàng)建一個新的鏈接表。事實上也可以這么做，而且可通過創(chuàng)建一個鏈接回表格來實現(xiàn)。
? ? ? ? 鏈接回表格是一個用于填充為Excel表格的DAX查詢，該Excel表隨后還可以作為一個鏈接表，以便其內(nèi)容再次推送到Power Pivot數(shù)據(jù)模型內(nèi)部、或者加載到Powerqurey作進(jìn)一步的數(shù)據(jù)加工處理。實際上，你存儲于數(shù)據(jù)模型中的數(shù)據(jù)是一個DAX查詢結(jié)果。它可用來定義計算列或計算字段，或兩者兼而有之。? ? ? ? ?

? ? ? ? 此選項將為你帶來一種全新的考慮有關(guān)DAX的業(yè)務(wù)解決方案的方式。事實上：

? ? （1）你可從Excel中的數(shù)據(jù)開始，將其推送到Power Pivot內(nèi)部并執(zhí)行一些計算。
? ? （2）然后，建立一個查詢來檢索Power Pivot模型中的數(shù)據(jù)，并使用其結(jié)果來填充為一個新的Excel表。此時，由于新表格是一個Excel表，你將擁有Excel 語言的全部功能：例如，可以用Excel公式來建立新單元格或新建列，最后將結(jié)果返回到PowePrivot。

? ? ? ? 因此，新表格可輸出為數(shù)據(jù)透視表等，正如它原本就存在于數(shù)據(jù)模型里一樣（可當(dāng)? 作數(shù)據(jù)模型的一部分）。而且，刷新操作的所有復(fù)雜性都由Excel以正確順序進(jìn)行處理，能確保正確計算。

? ? 鏈接回表的操作步驟：

? ? ? 第一步：Excel菜單-->數(shù)據(jù)-->現(xiàn)有連接表格-->選擇時期表（任何一個數(shù)據(jù)量小一點的表，目的只是用于引出一個鏈接回表）。這一步又分成幾個小步驟：

? ? ? 結(jié)果生成一個鏈接回表（即整個時期表），這一步主要是建立起一個從Powerpivot數(shù)據(jù)模型到Excel的鏈接回表。

? ? ? 第二步：生成的銜接表，可能并不是我們需要的數(shù)據(jù)內(nèi)容。單擊表里任意單元格，右鍵屬性-->表格 -->編輯DAX-->下拉選擇DAX， 來到鏈接表代碼編寫區(qū)。

? ? ? 我們輸入查詢代碼，這需要注意：

? ? （1）總是使用Excel表函數(shù) EVALUATE 定義查詢表；
? ? （2）使用結(jié)果為列表的函數(shù)定義表。

? ? ? 例如，我們從數(shù)據(jù)模型里鏈接回一個時期表：
EVALUATE
? '時期表'

? ? ? ? 這就是前面第一步里我們選擇的鏈接回表--時期表，現(xiàn)在我們使用計算結(jié)果為列表的DAX計算式查詢代碼：比如， FILTER條件的結(jié)果表：? ?
EVALUATE
FILTER（'時期表'，'時期表'[ 年份]=”2018”）

? ? ? 再試想一個作為鏈接回的例子：比如要看到產(chǎn)品中排名前25位的暢銷品。你可以很容易地使用TOPN函數(shù)來計算它，以下查詢返回表格的前25項：

EVALUATE
SUMMARIZE (TOPN (25,
CALCULATE (SUM (FactinternetSales[SalesAmount])))，
"Sales",
SUM (FactlnternetSales[SalesAmount］）
ORDER BY [Sales]

? ? ? 當(dāng)然，我們還可以在公式的基礎(chǔ)上添加其他條件。此查詢返回 25 個最暢銷產(chǎn)品。由于結(jié)果是一個 Excel 表格，你可以使用 Excel 中的其他計算來豐富該表格。? ? ?

? ? ? ? 例如，可用Excel 中的 RANK 函數(shù)來為每行指定一個排名數(shù)字列，然后使用 IF 語句進(jìn)行簡單分組等等，經(jīng)Excel處理后獲得 一個新表格(添加了許多新的銜生列)。

? ? ? 表格中TOP25的公式是：
Position= RANK ([@Sales],
[Sales])Ranking= IF ([@Position] <=5, "TOP 5",
IF ([@Position]<=10, "TOPN", "TOP25"))

? ? ? 此時，如果使用該結(jié)果（即Ranking列）并將其作為DirnProduct表的一個屬性列來存儲，可能是個好主意，因為可使用Ranking屬性列作為數(shù)據(jù)透視表的篩選器。

? ? ? 要達(dá)到此目的，依據(jù)前面的鏈接關(guān)系，你通過將表格鏈接到數(shù)據(jù)模型并建立正確的列表關(guān)系來做到這一點。這可以使用關(guān)系視圖創(chuàng)建，或使用Excel功能區(qū)數(shù)據(jù)選項卡上的“關(guān)系”按鈕，直接從Excel內(nèi)部創(chuàng)建關(guān)系。在后一種情況下可創(chuàng)建新的關(guān)系，如圖示：

? ? ? 如果此時回到PowerPivot窗口，會看到Top25Products表巳添加到數(shù)據(jù)模型里，且同任何其他表一樣是可用的，Top25Products表格和所有其他表格之間的最大區(qū)別在于：這是一個計算結(jié)果表格，而非從任何數(shù)據(jù)庫導(dǎo)入的表格。此時，你可以輕松地創(chuàng)建數(shù)據(jù)透視表以顯示那些排行前五名的產(chǎn)品的訂單數(shù)和銷售數(shù)量等，如下圖所示：

? ? ? 可以看到，在數(shù)據(jù)透視表中顯示了按銷售金額大小排名前五的產(chǎn)品，且銷售金額列并不需要在數(shù)據(jù)透視表中顯示出來。由于Ranking-排名列是一個屬于數(shù)據(jù)模型的計算列。事實上無須出現(xiàn)，它并非使用數(shù)據(jù)透視表的TOPN函數(shù)計算得來。

? ? ? ? 這個例子很簡單，但是，你可能己想象到這種做法擁有令人難以置信的擴(kuò)展性。它將Excel 計算和DAX 建模選項融為一體，這打開了新的、令人興奮的無限可能性，而且利用這種擴(kuò)展性的關(guān)鍵是使用DAX 的查詢功能。
? ? ? ? 如果刷新數(shù)據(jù)源，Excel 從外部連接重新載人表格，并基于DAX 查詢對Excel 表進(jìn)行刷新。然后，如果這些表格是鏈接回的，將對這些鏈接到DAX 查詢的PowerPivot 表刷新。所有這些操作自動發(fā)生而無須執(zhí)行任何手動操作，即可刷新鏈接回表格。

? ? 鏈接回表計算ABC分析

? ? ? 作為鏈接回威力的一個例子，我們要展示一個你可能已解決了的場景，也就是產(chǎn)品ABC類的計算。

? ? ? 在前面已學(xué)習(xí)到使用DAX度量 或計算列來將ABC 分類屬性添加至產(chǎn)品表。該DAX 代碼并不復(fù)雜，但是在之前的DAX中也并不認(rèn)為這很容易。
? ? ? 使用鏈接回功能，現(xiàn)在僅使用Excel 代碼，并用一種簡單技術(shù)即可解決相同場景。你可能還記得，ABC 分析將A 類分配到占70%銷售額的產(chǎn)品，將B 類分配到接下來的20%，C 類分配到占銷售額的余下10%。
? ? ? ? 為了計算這些類別，首先需要一個表格來顯示每個產(chǎn)品的銷售總額，使用類似下面的DAX 查詢，很容易地完成：

EVALUATE
SUMMARIZE ( DimProduct,[ProdctKey],
"Total Sales",
SUM (FactinternetSales [SalesAmount])
ORDER BY DESC

? ? ? 使用ORDER BY 的原因在于，這將使得更易于計算累計合計。按照ABC分析的經(jīng)典三部曲（有很多這方面的介紹，這里不做重復(fù)）：
? ? ? 下一步是對每個產(chǎn)品計算累計合計，即該產(chǎn)品之前的所有產(chǎn)品的銷售總額。
? ? ? 其次，下一步是將累計合計轉(zhuǎn)換成銷售總計的百分比。
? ? ? 最后，操作更簡單：實用 一個簡單的IF 條件將RunningPct 列轉(zhuǎn)換成ABC 類，
? ? ? ? =IF ([@RunningPct]<=0.7, "A", IF ([@RunningPct]<=0.9, "B", "C"))

? ? ? 如圖：

? ? ? 此時，在Excel 中就有了一個表格，它包含了每個產(chǎn)品所指定的ABC 類。現(xiàn)在，是時候?qū)⒃摫砀裢扑偷綌?shù)據(jù)模型中，以便探索鏈接回的威力。使用ProductKey 列來創(chuàng)建一個從DimProduct到這個新表格之間的關(guān)系。
? ? ? 你可以立即使用來自數(shù)據(jù)透視表中新表的ABC類列。如果你是一個純粹的數(shù)據(jù)建模者，可在DimProduct中創(chuàng)建一個新的計算列并使用RELATED 函數(shù)來反規(guī)范化ABC類列。此時可將僅用作參數(shù)過渡的Product_ABC_Class表隱藏。

? ? ? 我們希望這個小例子已向你顯示了鏈接回的威力。但這并不是說你應(yīng)該總是嘗試將Exce l和DAX組合起來?？傆幸恍└澇墒褂肈AX，而其他更贊成Excel的理由。你所做的選擇將取決千你所面臨的具體業(yè)務(wù)場景。
? ? ? 通常，我們認(rèn)為最好始終用更易于創(chuàng)建的語言來編寫公式，這是因為編寫和調(diào)試的代碼量越小越好。在這種特定情況下在Excel中編寫累計合計更容易些，而DAX會需要一些復(fù)雜的工作來完成。因此，Excel解決方案看起來更容易實現(xiàn)些。

鏈接回表綜合案例

? ? ? 某些業(yè)務(wù)場景需要（從Powerpivot到Excel，再到Powerpivot）

? ? ? 問題：(這個案例為鏈接表行為)

? ? ? （1）如何將Powerpivot或Power BI里設(shè)置的多個度量值，將其結(jié)果引用到數(shù)據(jù)模型里作為新的列表（擴(kuò)展模型），參與計算？
? ? ? （2）有時候有這種需求：需要將只能放置在透視表數(shù)值區(qū)的度量值，用來做行、列篩選或切片器？
? ? ? （3）因為Power BI的強(qiáng)項還是列表關(guān)系構(gòu)建模型，而Excel則具有無法比擬的靈活性、動態(tài)性。
? ? ? ? 比如，你苦苦思索的一個動態(tài)性問題，可能在Excel里或許很容易被解決。所以，可以將它們結(jié)合起來--強(qiáng)強(qiáng)聯(lián)手。
? ? ? ? 這些情況下的兩者結(jié)合，就需要鏈接表或鏈接回表。

? ? ? 下面是步驟說明，你可以拿自己的一個業(yè)務(wù)案例試試。鏈接表過程：
? ? ? 第一步： Powerpivot或Power BI里設(shè)置好需要的度量值或計算列。
? ? ? 這里，我們需要的主要KPI度量值是下圖中的列字段：銷售、銷量、毛利、日均銷售、庫存等等（因業(yè)務(wù)需要構(gòu)建）。因為接下來由此生成很多業(yè)務(wù)度量值（衍生度量）都是根據(jù)這些條件搭建的。
? ? ? 下面是我們透視出來的一個透視表。如圖：

? ? ? 注意：透視結(jié)果最后包含你的關(guān)系列字段（這里為【條碼】列）

? ? ? 第二步：你可以依據(jù)這個透視表在后面添加需要的新列，比如庫存量為零的，標(biāo)注為“零庫存”，不為零的標(biāo)記為“有庫存”，負(fù)的標(biāo)記為“負(fù)庫存”，這很容易實現(xiàn)（IF判斷一下就好）。這樣你就得到一個新的列【庫存狀況列】（包含需要的列值屬性分組內(nèi)容：零庫存和有庫存等）。當(dāng)然，這個步驟也可以放在PQ里新建條件列得出。但是，有時候為什么不在PP里創(chuàng)建？
? ? ? ? 因為Powerqurey主要是數(shù)據(jù)處理，關(guān)鍵是透視出的內(nèi)容是你需要的（數(shù)據(jù)模型支持的任意維度變化與業(yè)務(wù)邏輯，而且這些關(guān)鍵度量值是動態(tài)變化的）。明白了這點，再看下面這個透視表。我們新建一列：【庫存狀況列】，如果還有需要的其他屬性列，都一并建好。

? ? ? 第三步：將這個新透視表轉(zhuǎn)化為Excel表。這一步的原因是：透視表不能直接加載到Powerqurey、Powerpivot中。

? ? ? 可能的轉(zhuǎn)換方法：
? ? （1）VAR代碼（錄個宏就好）；
? ? （2）GETPIVOTDATA函數(shù)構(gòu)建；
? ? （3）使用函數(shù)引用透視表區(qū)域值到另一個Excel區(qū)域。

? ? ? ? 第二種其實是透視表的多維數(shù)據(jù)引用公式，方法是：直接在待引用的Excel單元格寫上 “=”，再單擊透視比表里你需要引用的某個單元格值，就會生成一個包含GETPIVOTDATA的多維公式，該方法需要將每個引用的單元格內(nèi)容都設(shè)置一遍，好處是設(shè)置一次就行，后期數(shù)據(jù)會隨透視表自動更新。
? ? ? ? 我們采用的是第三種方式：這里使用INDEX函數(shù)引用透視表內(nèi)容：

? ? ? 第四步：將已轉(zhuǎn)換好的Excel表導(dǎo)入Powerqurey或Powerpovit成為一個銜接表。在Powerpivot里的這個表跟其他的數(shù)據(jù)模型里的表是一樣的。如果你只需要【庫存狀況】這一列，可將該列直接導(dǎo)入數(shù)據(jù)模型里就行。
? ? ? 當(dāng)然，也可將此Excel表加載到Powerqurey做進(jìn)一步的處理，再經(jīng)Powerqurey處理后加載到Powerpivot成為數(shù)據(jù)模型的一部分。

? ? ? 注意：不管結(jié)果是在Powerqurey里還是Excel里，都要導(dǎo)入到Powerpivot里，才能稱為鏈接表。

? ? ? 最后，可以用這個已導(dǎo)入模型里的表參與數(shù)據(jù)模型計算或作為輸出（如透視表），比如將由此表創(chuàng)建的新[ABC值]列作為透視表的行、列或切片器。