使用 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 或更小。 實際達到的壓縮程度主要取決於每一欄中唯一值的數目。 唯一值越多，儲存它們所需的記憶體就越多。

為什麼我們要談論壓縮和唯一值？ 因為建置一個能將記憶體使用量降至最低的高效模型，就是要做到這一點，最簡單的方法就是刪除任何您並不真正需要的欄，特別是當這些欄包含大量唯一值時。

沒有什麼比不存在的欄更能降低記憶體使用量了

最節省記憶體的欄是您一開始就沒有匯入過的欄。 如果您想建立一個有效的模型，請查看每一欄，並問問自己它是否有助於您想要執行的分析。 如果沒有或您不確定，請將其排除在外。如有需要，您之後隨時都可以新增資料欄位。

應一律排除的兩個欄範例

第一個範例與源自資料倉儲的資料相關。 在資料倉儲中，通常會找到載入和重新整理倉儲中資料的 ETL 程序成品。 載入資料時，會建立「建立日期」、「更新日期」和「ETL 執行」等欄。 模型中不需要這些欄，並且在匯入資料時應取消選取。

第二個範例涉及在匯入事實資料表時省略主索引鍵資料行。

許多資料表 （包括事實資料表） 都有主索引鍵。 對於大部分的資料表，例如包含客戶、員工或銷售資料的資料表，您需要資料表的主索引鍵，以便使用它在模型中建立關聯。

事實資料表則不同。 在事實資料表中，主索引鍵是用來唯一識別每個資料列。 雖然對於正規化目的為必要，但在您只想將那些欄用於分析或建立資料表關聯的資料模型中，它就不太有用了。 因此，從事實資料表匯入時，請勿包含其主索引鍵。 事實資料表中的主索引鍵會在模型中佔用大量空間，但沒有任何好處，因為它們無法用來建立關聯。

如何排除不必要的資料欄位

有效模型只包含您在活頁簿中實際需要的那些欄。 如果您想要控制模型中包含的欄，您必須 使用 Power Pivot 增益集中的資料表匯入精靈來匯入資料 ，而不是使用 Excel 中的「匯入資料」對話方塊。

當您啟動 [資料表匯入精靈] 時，您可以選取要匯入的資料表。

針對每個資料表，您可以按一下 [預覽 & 篩選] 按鈕，然後選取您真正需要的資料表部分。 建議您先取消選取所有資料欄位，然後在考慮分析是否需要這些資料欄位之後，繼續檢查您想要的資料欄位。

那麼只篩選必要的資料列呢？

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

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

如果我們需要專欄怎麼辦？我們還能降低它的空間成本嗎？

您可以應用一些其他技術來使柱子成為更好的壓縮候選者。 請記住，影響壓縮的欄的唯一特性是唯一值的數目。 在本節中，您將了解如何修改某些欄以減少唯一值的數量。

修改日期時間欄

在許多情況下，日期時間欄會佔用大量空間。 幸運的是，有許多方法可以減少此資料類型的儲存需求。 這些技術會根據您使用欄的方式以及您構建 SQL 查詢的舒適程度而有所不同。

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

• 我需要時間部分嗎？
• 我需要以小時為單位的時間部分嗎？ 、分鐘？ 、秒？ 、毫秒？
• 我有多個 [日期時間] 欄是因為要計算它們之間的差異，還是只是想依年、月、季等彙總資料。

您如何回答每個問題將決定您處理 [日期時間] 欄的選項。

所有這些解決方案都需要修改 SQL 查詢。 若要簡化查詢修改，您應該在每個資料表中至少篩選掉一欄。 藉由篩選掉欄，您可以將查詢建構從縮寫格式 (SELECT *) 變更為包含完整欄名稱的 SELECT 陳述式，這更容易修改。

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

從 [資料表內容]，選取 [查詢編輯器]。

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

相反的，如果您匯入了整個資料表，而未取消選取任何欄或套用任何篩選，則會看到查詢顯示為「選取 * 來源」，這將更難以修改：

修改 SQL 查詢

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

1. 對於包含貨幣或小數資料的欄，如果您不需要小數，請使用下列語法來刪除小數：
   「選取回合 ([Decimal_column_name]，0) ... .”
   如果您需要美分而不是美分的分數，請將 0 替換為 2。 如果使用負數，則可以四捨五入為單位、十、百等。
2. 如果您有名為 dbo 的日期時間資料行。Bigtable。[Date Time] 且您不需要 Time 的部分，請使用語法來刪除時間：
   「選取投 (dbo。Bigtable。[日期時間] 作為日期) 作為 [日期時間]) ”
3. 如果您有名為 dbo 的日期時間資料行。Bigtable。[Date Time] 且您需要日期和時間的部分，請在 SQL 查詢中使用多個資料行，而不是單一的 Datetime 資料行：
   「選取投 (dbo。Bigtable。[日期時間] 作為日期 ) 作為 [日期時間]，
   datepart (hh， dbo。Bigtable。[日期時間]) 作為 [日期時間小時]，
   datepart (mi， dbo。Bigtable。[日期時間]) 作為 [日期時間分鐘]，
   datepart (SS， DBO。Bigtable。[日期時間]) 作為 [日期時間秒]，
   datepart (MS，DBO。Bigtable。[日期時間]) as [日期時間毫秒]」
   視需要使用任意數量的欄，將每個部分儲存在不同的欄中。
4. 如果您需要小時和分鐘，而且您更喜歡將它們一起作為一次性欄，您可以使用語法：
   Timefromparts (datepart (hh， dbo。Bigtable。[日期時間]) ， datepart (mm， dbo。Bigtable。[日期時間]) ) 作為 [日期時間 小時分鐘]
5. 如果您有兩個日期時間資料行，例如 [開始時間] 和 [結束時間]，而您真正需要的是它們之間的時差（以秒為單位）作為名為 [持續時間] 的欄位，請從清單中移除這兩個資料行，並新增：
   “datediff (ss，[開始日期]，[結束日期]) as [Duration]”
   如果您使用關鍵字 ms 而非 ss，則會取得以毫秒為單位的持續時間

使用 DAX 計算量值而非資料行

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

其中一種節省記憶體的技術是以計算量值取代一般或計算結果欄。 典型的範例是 [單價]、[數量] 和 [總計]。 如果您具備這三個，您可以只保留兩個並使用 DAX 計算第三個來節省空間。

您應該保留哪 2 欄？

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

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

計算資料行類似於一般資料行，兩者都會佔用模型中的空間。 相比之下，計算的測量值是即時計算的，不佔用空間。

總結

在本文中，我們討論了幾種可以幫助您構建更內存效率的模型的方法。 減少資料模型的檔案大小和記憶體需求的方法是減少欄和列的總數，以及每個欄中出現的唯一值數目。 以下是我們介紹的一些技術：

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

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

相關連結

資料模型的規格與限制

活頁簿大小最佳化工具

PowerPivot：Excel 中的強大資料分析與資料模型