使用 Excel 和 Power Pivot 載入宏建立有效使用記憶體的數據模型

在 Excel 中，您可以建立包含數百萬列的數據模型，然後針對這些模型執行強大的數據分析。數據模型可以使用或不含 Power Pivot 載入宏來建立，以支援同一個活頁簿中任意數量的數據透視表、圖表和 Power View 視覺效果。

雖然您可以輕鬆地在 Excel 中建立大型數據模型，但有幾個原因無法建立。首先，包含許多數據表和數據行的大型模型在大部分分析中都是過度取代的，並導致麻煩的欄位清單。第二，大型模型會使用寶貴的記憶體，對共用相同系統資源的其他應用程式和報表造成負面影響。最後，在 Microsoft 365中，SharePoint Online 和 Excel Web App 都會將 Excel 檔案的大小限制為 10 MB。對於包含數百萬列的活頁簿數據模型，您將會很快地遇到 10 MB 的限制。請參閱數據模型規格與限制。

在本文中，您將瞭解如何建立結構緊密且更容易使用且使用較少記憶體的模型。無論您是在 Excel、 Microsoft 365 SharePoint Online、 Office Web Apps Server 或 SharePoint 中檢視模型，在有效模型設計上花點時間學習最佳做法，就能為您建立和使用的任何模型付費。

請考慮同時執行活頁簿大小最佳化工具。此工具可分析您的 Excel 活頁簿，並且盡可能地加以壓縮。下載活頁簿大小優化器。

本文內容

壓縮比例和記憶體內分析引擎
記憶體使用量過低時，沒有比起不存在的數據行
- 兩個應一律排除的欄範例
- 如何排除不必要的欄
只篩選必要的列呢？
如果我們需要該欄該怎麼辦;我們仍然可以降低空間成本嗎？
- 修改 [日期時間] 欄
- 修改 SQL 查詢
使用 DAX 計算量值，而非數據行
- 您應該保留哪 2 欄？
總結
相關連結

壓縮比例和記憶體內分析引擎

Excel 中的數據模型使用記憶體分析引擎將數據儲存在記憶體中。引擎會實作強大的壓縮技術來降低儲存需求，並縮小結果集直到其原始大小的一小部分為止。

平均而言，您預期數據模型在來源點會比相同數據小 7 到 10 倍。例如，如果您要從 SQL Server 資料庫匯入 7 MB 的數據，Excel 中的數據模型可能很容易為 1 MB 或以下。實際達到的壓縮程度主要取決於每一欄中的唯一值數目。唯一值越多，儲存這些值所需的記憶體就越多。

為什麼我們要談論壓縮和唯一值？建置有效模型以最小化記憶體使用量，其目的在於壓縮最大化，而最簡單的方法就是移除您不需要的任何欄，尤其是那些欄包含大量唯一值時。

附註: 個別數據行的儲存需求差異可能很大。在某些情況下，最好有多個數據行的唯一值較低，而非單欄具有高唯一值的數目。 Datetime 優化一節詳細說明這項技巧。

記憶體使用量過低時，沒有比起不存在的數據行

最有用記憶體的欄是您一開始就從未匯入的欄。如果您想要建立有效率的模型，請查看每個數據行，並詢問您自己是否為您要執行的分析提供貢獻。如果沒有，或您不確定，請將它留意。如果您需要，隨時都可以新增欄。

兩個應一律排除的欄範例

第一個範例與來自數據倉儲的數據有關。在數據倉儲中，很常尋找 ETL 處理程式的成品，以載入及重新整理倉庫中的數據。載入數據時，會建立像是「建立日期」、「更新日期」和「ETL 執行」等欄。模型中不需要這些欄，當您匯入數據時，應該取消選取這些欄。

第二個範例涉及匯入事實數據表時省略主鍵數據行。

包括事實數據表在內的許多數據表都有主鍵。對於大部分的數據表，例如包含客戶、員工或銷售數據的數據表，您會想要數據表的主鍵，以便用來在模型中建立關聯。

事實數據表不同。在事實數據表中，主鍵是用來唯一識別每一列。雖然正規化是必要的，但在數據模型中，您只想要將這些數據行用於分析或建立數據表關聯，這會比較不實用。因此，從事實數據表匯入時，請不要包含其主鍵。事實數據表中的主鍵會耗用模型中大量的空間，但不提供任何好處，因為這些索引鍵無法用來建立關聯。

附註: 在數據倉儲和多維度資料庫中，主要包含數值數據的大型數據表通常稱為「事實數據表」。事實數據表通常包括業務效能或交易數據，例如匯總並對齊組織單位、產品、市場區段、地理區域等的銷售和成本數據點。事實數據表中包含商務數據或可用來交互參照其他數據表中之數據的所有數據行應包含在模型中，以支持數據分析。您要排除的數據行是事實數據表的主鍵數據行，它包含唯一值，只存在於事實數據表中，沒有其他任何位置。由於事實數據表如此龐大，模型效率的一些最大收益是衍生自事實數據表中排除數據列或數據行。

如何排除不必要的欄

有效率的模型只包含您在活頁簿中實際需要的欄。如果您想要控制模型中包含哪些數據行，您必須使用 Power Pivot 載入巨集中的 [資料表匯入精靈] 來匯入數據，而非 Excel 中的 [匯入數據] 對話框。

當您啟動 [資料表匯入精靈] 時，您會選取要匯入哪些數據表。

PowerPivot 增益集中的 [資料表匯入精靈]

您可以針對每個數據表單擊 [預覽 & 篩選] 按鈕，然後選取您真正需要的數據表部分。我們建議您先取消核取所有數據行，然後在考慮是否需要欄進行分析后，繼續檢查您要的欄。

[資料表匯入精靈] 中的 [預覽] 窗格

只篩選必要的列呢？

公司資料庫和數據倉儲中的許多數據表都包含長時間累計的歷史數據。此外，您可能會發現您感興趣的數據表包含不需進行特定分析之商務領域的資訊。

您可以使用 [數據表匯入精靈] 篩選出歷史或不相關的數據，因此在模型中節省大量空間。在下列影像中，日期篩選僅用於擷取包含今年數據的數據列，但不包括不需要的歷史數據。

[資料表匯入精靈] 中的 [篩選] 窗格

如果我們需要該欄該怎麼辦;我們仍然可以降低空間成本嗎？

您可以套用一些額外的技巧，讓欄成為更適合壓縮的候選字段。請記住，影響壓縮的欄唯一特性是唯一值的個數。在本節中，您將瞭解如何修改某些欄以減少唯一值的數目。

修改 [日期時間] 欄

在許多情況下，[日期時間] 欄會佔用大量空間。幸好，有許多方法可以降低這種數據類型的儲存需求。技術會因您使用欄的方式，以及您在建置 SQL 查詢時的舒適程度而有所不同。

日期時間列包含日期部分和時間。當您詢問自己是否需要欄時，請針對 [日期時間] 欄多次詢問相同的問題：

我需要時間部分嗎？
我需要時數層級的時間部分嗎？紀要？秒？毫秒？
我是否有多個 [日期時間] 欄，因為我想計算它們之間的差異，或只是要根據年、月、季等項目匯總數據。

每個問題的回答方式會決定處理 [日期時間] 資料行的選項。

所有這些解決方案都需要修改 SQL 查詢。若要更輕鬆地修改查詢，您應該篩選掉每個數據表中至少一個數據行。藉由篩選出數據行，您可以將查詢結構從縮寫格式 (SELECT *) 變更為包含完整數據行名稱的 SELECT 語句，以便修改。

讓我們來看看為您建立的查詢。您可以從 [資料表屬性] 對話框切換到 [查詢編輯器]，並查看每個數據表目前的 SQL 查詢。

PowerPivot 視窗中顯示 [資料表屬性] 命令的功能區

從 [資料表屬性] 選取 [查詢編輯器]。

從 [資料表屬性] 對話方塊開啟 [查詢編輯器]

[查詢編輯器] 會顯示用來填入數據表的 SQL 查詢。如果您在匯入期間篩選掉任何資料列，您的查詢會包含完整資料行名稱：

用來擷取資料的 SQL 查詢

相反地，如果您匯入的數據表完整，但未取消選取任何數據行或套用任何篩選，您會看到查詢為「從中選取 *」，這會比較難修改：

使用預設為較短語法的 SQL 查詢

修改 SQL 查詢

現在您知道如何尋找查詢，您可以修改查詢以進一步縮減模型的大小。

對於包含貨幣或小數數據的數據行，如果您不需要小數字數，請使用此語法來移除小數字數：

“SELECT ROUND ([Decimal_column_name]，0) ... .”

如果您需要的不是小數的美分，請將 0 取代為 2。如果您使用負數，可以四捨五入到單位、十位數、上百個等。
如果您有一個名為 dbo 的日期時間列。Bigtable。[日期時間]，而且您不需要 [時間] 部分，請使用語法來移除時間：

「選取投射 (dbo。Bigtable。[日期時間] 日期) AS [日期時間]) 」
如果您有一個名為 dbo 的日期時間列。Bigtable。[日期時間]，而且您需要 [日期] 和 [時間] 部分，請在 SQL 查詢中使用多個數據行，而非單一 [日期時間] 數據行：

「選取投射 (dbo。Bigtable。[日期時間] 日期 ) AS [日期時間]，

datepart (hh， dbo.Bigtable。[日期時間]) 為 [日期時間時]，

datepart (mi， dbo.Bigtable。[日期時間]) 為 [日期時間分鐘]，

datepart (ss， dbo.Bigtable。[日期時間]) 為 [日期時間秒]，

datepart (ms， dbo.Bigtable。[日期時間]) 為 [日期時間毫秒]」

視需要使用任意數量的欄，將每個部分儲存在個別的欄中。
如果您需要小時和分鐘，且偏好將它們當作一個時間欄，您可以使用以下語法：

timefromparts (datepart (hh， dbo.Bigtable。[日期時間]) ，datepart (mm， dbo。Bigtable。[日期時間]) ) 為 [日期時間小時]
如果您有兩個日期時間數據行，例如 [開始時間] 和 [結束時間]，而您真正需要的是以秒為名的欄 [持續時間] 之間的時間差異，請從清單中移除這兩個數據行，然後新增：

“datediff (ss，[Start Date]，[End Date]) 為 [Duration]」

如果您使用關鍵詞 ms 而非 ss，則會得到毫秒為毫秒的持續時間

使用 DAX 計算量值，而非數據行

如果您之前使用過 DAX 表達式語言，您可能已經知道計算結果列是根據模型中的一些其他數據行來衍生新的數據行，而計算量值在模型中定義一次，但只有在數據透視表或其他報表中使用時才會進行評估。

一種記憶體技術是以計算量值取代一般或計算結果列。傳統範例是[單價]、[數量] 和 [合計]。如果您擁有全部三個，只要維護兩個，然後使用DAX計算第三個，即可節省空間。

您應該保留哪 2 欄？

在上述範例中，保留 [數量] 和 [單價]。這兩個值的值會比 [總計] 少。若要計算總計，請新增計算量值，例如：

“TotalSales：=sumx ('Sales Table'，'Sales Table'[Unit Price]*'Sales Table'[Quantity]) 」

計算結果列就像一般欄，兩者都會佔用模型中的空間。相反地，計算量值是以動態方式計算，不會佔用空間。

總結

在本文中，我們討論幾種方法可協助您建立更有效率的記憶體模式。減少數據模型檔案大小和記憶體需求的方法，是減少欄和列的整體數目，以及每一欄中顯示的唯一值數目。以下是我們涵蓋的一些技巧：

拿掉欄當然是節省空間的最佳方式。決定您真正需要哪些欄。
有時候您可以移除數據行，並以表格中的計算量值取代欄。
您可能不需要表格中的所有資料列。您可以在 [數據表匯入精靈] 中篩選掉列。
一般說來，將單一欄分成多個不同部分，是減少欄中唯一值數目的好方法。每個部分都會有少量的唯一值，合併后的總計會小於原始的整合數據行。
在許多情況下，您也需要在報表中使用不同的部分做為交叉分析篩選器。在適當的時候，您可以從 [小時]、[分鐘] 和 [秒] 等部分建立階層。
很多時候，欄所包含的資訊也比您需要的多。例如，假設某欄儲存小數字數，但您已套用格式設定來隱藏所有小數字數。四捨五入可以非常有效地減少數值欄的大小。

現在您已完成縮減活頁簿大小的動作，請考慮同時執行活頁簿大小優化器。此工具可分析您的 Excel 活頁簿，並且盡可能地加以壓縮。下載活頁簿大小優化器。