在 Excel 中，你可以建立包含數百萬筆資料的資料模型，並對這些模型進行強大的資料分析。 資料模型可搭配或不使用 Power Pivot 外掛建立，以支援同一工作簿中任意數量的樞紐分析表、圖表及 Power View 視覺化。
雖然你可以輕易地在 Excel 中建立龐大的資料模型，但有幾個理由不這麼做。 首先，包含大量表格和欄位的大型模型對大多數分析來說是過度設計，且會造成一個繁瑣的欄位清單。 其次，大型模型會消耗寶貴的記憶體，對共用相同系統資源的其他應用程式和報表產生負面影響。 最後，在 Microsoft 365 中，SharePoint Online 和 Excel 網頁應用程式都限制 Excel 檔案大小為 10 MB。 對於包含數百萬筆資料的活頁簿資料模型，你很快就會遇到 10 MB 的限制。 請參閱 資料模型規範與限制。
在本文中，你將學習如何建立一個更易操作且記憶體消耗更低的緊密模型。 花時間學習高效模型設計的最佳實務，無論你是在 Excel、Microsoft 365 SharePoint Online、Office Web Apps Server 或 SharePoint 中瀏覽，將來都會帶來回報。
請考慮同時執行活頁簿大小最佳化工具。 此工具可分析您的 Excel 活頁簿，並且盡可能地加以壓縮。 下載工作簿大小優化器。
本文內容
壓縮比與記憶體內分析引擎
Excel 中的資料模型使用記憶體內分析引擎將資料儲存在記憶體中。 引擎實施強大的壓縮技術以降低儲存需求，將結果集縮小至原始大小的一小部分。
平均而言，一個資料模型在起點會比同一資料小7到10倍。 舉例來說，如果你從 SQL Server 資料庫匯入 7 MB 的資料，Excel 裡的資料模型很可能只有 1 MB 或更少。 實際壓縮程度主要取決於每欄中唯一值的數量。 唯一值越多，儲存所需的記憶體就越多。
為什麼我們要談壓縮和唯一值？ 因為建立一個能最大化記憶體使用的高效模型，就是壓縮最大化，而最簡單的方法就是刪除那些你其實不需要的欄位，尤其是那些欄位包含大量獨特值時。
注意
單根柱的儲存需求差異可能非常大。 在某些情況下，擁有多個獨特值較少的欄位，比起單一欄位中獨特值數量較多更為理想。 Datetime 優化的章節詳細介紹了這項技術。
沒有什麼比不存在的欄位更能降低記憶體使用量了
最省記憶體的欄位是你根本沒匯入的那欄。 如果你想建立一個有效率的模型，請檢視每一欄，問問自己它是否有助於你想執行的分析。 如果沒有，或你不確定，就不要寫。如果需要，之後也可以再加新欄位。
兩個應該永遠排除的欄位範例
第一個例子與來自資料倉儲的資料有關。 在資料倉儲中，常見ETL流程的產物會載入並刷新倉庫中的資料。 像是「建立日期」、「更新日期」和「ETL 執行」這類欄位會在資料載入時建立。 這些欄位在模型中都不是必需的，匯入資料時應該取消選取。
第二個例子是匯入事實表時省略主鍵欄位。
許多資料表，包括事實表，都有主鍵。 對於大多數資料表，例如包含客戶、員工或銷售資料的資料表，你會想要資料表的主鍵，以便用它來建立模型中的關聯。
事實表則不同。 在事實表中，主鍵用來唯一識別每一列。 雖然對正規化來說是必要的，但在只想用來分析或建立資料表關係的欄位資料模型中，它的實用性較低。 因此，從事實表匯入時，不要包含其主鍵。 事實表中的主鍵佔用模型大量空間，卻無法帶來任何好處，因為它們無法建立關係。
注意
在資料倉儲與多維資料庫中，主要由數值資料組成的大型資料表通常被稱為「事實表」。 事實表通常包含業務績效或交易資料，例如銷售與成本資料點，這些資料點彙整並對齊至組織單位、產品、市場區隔、地理區域等。 事實表中所有包含商業資料或可用來交叉參照其他資料表資料的欄位，都應包含在模型中，以支援資料分析。 你要排除的欄位是事實表的主鍵欄位，該欄位包含了只存在於事實表中、其他地方沒有的獨特值。 由於事實表龐大，排除行列或欄位，能帶來最大模型效率提升。
如何排除不必要的欄位
高效模型只包含你在工作簿中實際需要的欄位。 如果你想控制模型中包含哪些欄位，必須 使用 Power Pivot 外掛中的表格匯入精靈來匯入資料 ，而不是在 Excel 裡的「匯入資料」對話框。
當你啟動資料表匯入向導時，你可以選擇要匯入哪些資料表。
![PowerPivot 增益集中的 [資料表匯入精靈]](media/x15ppvt-optimizemodel-tableimportwizard.png)
對於每個表格，你可以點擊預覽 & 篩選按鈕，選擇你真正需要的表格部分。 我們建議您先取消勾選所有欄位，然後考慮是否需要檢查，再檢查你想要的欄位。
![[資料表匯入精靈] 中的 [預覽] 窗格](media/x15ppvt-optimizemodel-preview.png)
那只篩選必要的列怎麼樣？
企業資料庫和資料倉儲中的許多資料表都包含了長期累積的歷史資料。 此外，你可能會發現你感興趣的表格包含了業務中不需特定分析的相關資訊。
透過資料表匯入精靈，你可以過濾掉歷史資料或無關資料，從而節省大量模型空間。 在下圖中，使用日期過濾器只檢索包含當前年份資料的資料列，排除不需要的歷史資料。
![[資料表匯入精靈] 中的 [篩選] 窗格](media/x15ppvt-optimizemodel-filter.png)
如果我們需要那篇專欄怎麼辦;我們還能降低它的空間成本嗎？
還有幾種額外的技巧可以讓柱子更適合壓縮。 請記住，柱唯一影響壓縮的特徵是唯一值的數量。 在本節中，您將學習如何修改某些欄位以減少獨特值的數量。
修改日期時間欄位
在許多情況下，Datetime 欄位佔用了大量空間。 幸運的是，有多種方法可以降低此資料類型的儲存需求。 這些技巧會依你如何使用欄位，以及你建立 SQL 查詢的舒適度而有所不同。
Datetime 欄位包含日期部分和時間。 當你問自己是否需要專欄時，請多次問同一個問題來寫Datetime專欄：
- 我需要時間這部分嗎？
- 我需要以工時為單位的時間部分嗎？ ，分鐘？ ，秒鐘？ 毫秒？
- 我是因為想計算它們之間的差異而設多個 Datetime 欄位，還是只是想依年份、月份、季度等方式彙整資料？
你如何回答這些問題，決定了你如何應對Datetime欄位的選擇。
這些解決方案都需要修改 SQL 查詢。 為了讓查詢修改更簡單，你應該在每個資料表中至少過濾掉一欄。 透過篩選欄位，您可以將查詢結構從簡化格式 (SELECT *) ，改成包含完整限定欄位名稱的 SELECT 語句，這類欄位名稱更易於修改。
讓我們來看看為你建立的查詢。 從資料表屬性對話框中，你可以切換到查詢編輯器，查看每個資料表目前的 SQL 查詢。
![PowerPivot 視窗中顯示 [資料表屬性] 命令的功能區](media/x15ppvt-optimizemodel-ribbontableprop.png)
從資料表屬性中，選擇查詢編輯器。
![從 [資料表屬性] 對話方塊開啟 [查詢編輯器]](media/x15ppvt-optimizemodel-tablepropqueryeditor.png)
查詢編輯器顯示用來填入資料表的 SQL 查詢。 如果你在匯入時過濾掉任何欄位，你的查詢包含完全限定的欄位名稱：
相較之下，如果你匯入整個資料表，且未取消勾選任何欄位或套用任何篩選條件，查詢會顯示為「Select * from」，這會較難修改：
|
|---|
修改 SQL 查詢
既然你已經知道如何找到查詢，就可以修改它，進一步縮小模型的大小。
- 對於包含貨幣或十進位資料的欄位，如果你不需要小數，可以用以下語法去除小數：
「選擇回合 ([Decimal_column_name]，0) ...... .”
如果你需要分數但不需要分數，請把0換成2。 如果你用負數，可以四捨五入為單位、十、百等等。 - 如果你有一個名為 dbo 的 Datetime 欄位。大桌。[日期時間] 且你不需要時間部分，請使用語法來去除時間：
「精選演員 (dbo。大桌。[約會時間] 作為日期) AS [約會時間]) ” - 如果你有一個名為 dbo 的 Datetime 欄位。大桌。[Date Time] 並且你需要同時包含日期和時間兩個部分，請在 SQL 查詢中使用多個欄位，而不是單一的 Datetime 欄位：
「精選演員 (dbo。大桌。[日期時間] 作為日期 ) 作為 [日期時間]，
Datepart (HH，DBO。大桌。[日期時間]) 為[日期時間小時]，
Datepart (MI，DBO。大桌。[日期時間]) 為[日期時間分鐘]，
日期部分 (SS，DBO。大桌。[日期時間]) 為[日期時間秒]，
日期部分 (MS，DBO。大桌。[日期時間]) [日期時間毫秒]」
使用所需欄位數量，將每個零件分別存放在獨立欄位。 - 如果你需要小時和分鐘，且偏好將它們合併為同一時間欄位，你可以使用語法：
時間從部分 (日期部分 (HH，DBO。大桌。[約會時間]) ，約會部分 (mm，dbo。大桌。[日期時間]) ) 作為 [約會時間 小時 分鐘] - 如果你有兩個日期時間欄位，例如 [開始時間] 和 [結束時間]，而你真正需要的是它們之間的秒數時間差，作為一個稱為 [Duration] 的欄位，請從列表中移除這兩個欄位並新增：
「datediff (ss，[開始日期]，[結束日期]) 作為 [持續時間]」
如果你用關鍵字 ms（ms）而不是 ss，會得到毫秒的持續時間
使用 DAX 計算的度量值而非欄位
如果你之前用過 DAX 表達式語言，可能已經知道計算欄位用來根據模型中其他欄位推導出新欄位，而計算出的指標在模型中定義一次，但只有在樞紐分析表或其他報告中使用時才會評估。
一種節省記憶體的方法是將常規或計算過的欄位替換為計算過的測度。 經典例子是單價、數量與總價。 如果你有三個，只維護兩個，並用 DAX 計算第三個，可以節省空間。
你應該保留哪兩欄？
在上述例子中，保留數量和單位價格。 這兩個的數值比總數還少。 要計算總額，可以加上一個計算出來的指標，例如：
「總銷售額：=sumx (『銷售表』，『銷售表』[單價]*『銷售表』[數量]) 」
計算的欄位與一般欄位相同，兩者都會佔用模型空間。 相較之下，計算出的度量是即時計算的，不會佔用空間。
總結
在本文中，我們討論了幾種能幫助你建立更高效記憶體模型的方法。 減少資料模型檔案大小與記憶體需求的方法是減少整體欄位與列數，以及每欄中出現的獨特值數量。 以下是我們討論過的一些技巧：
- 移除柱子當然是節省空間的最佳方式。 決定你真正需要哪些欄位。
- 有時你可以移除一欄，然後用表格中計算出來的測度來取代。
- 你可能不需要所有資料表的列。 你可以在資料表匯入精靈中過濾出列。
- 一般來說，將單一欄位拆分成多個不同部分，是減少欄位中唯一值數量的好方法。 每個部分會有少量獨特值，且總數會比原本統一的欄位小。
- 在許多情況下，你還需要這些不同的部分作為報告中的切片工具。 適當時，你可以從小時、分鐘和秒等部分建立階層結構。
- 很多時候，欄位包含的資訊比你需要的還多。 舉例來說，假設一欄儲存小數點，但你套用格式來隱藏所有小數點。 四捨五入對於縮小數值欄位大小非常有效。
既然你已經盡力縮小工作簿大小，不妨考慮執行工作簿大小優化器。 此工具可分析您的 Excel 活頁簿，並且盡可能地加以壓縮。 下載工作簿大小優化器。