在 Excel 2013 或更新之後,您可以建立包含數百萬列的資料模型,然後針對這些模型執行強大的資料分析。 您可以使用或不使用 Power Pivot 建立資料模型,以支援同一個活頁簿中任何數目的樞紐分析表、圖表和 Power View 視覺效果。

附註: 本文描述 Excel 2013 中的資料模型。 不過,於 Excel 2013 中導入的資料模型和 Power Pivot 功能也同樣適用於 Excel 2016。 這些版本的應用程式之間實際上沒有Excel。

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

在本文中,您將瞭解如何建立結構緊密的模型,更容易使用,且使用較少的記憶體。 無論您在 Excel 2013、 Microsoft 365 SharePoint Online、 Office Web Apps Server 或 SharePoint 2013 中檢視模型,花時間瞭解有效模型設計的最佳作法,都會獲得回報。

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

本文內容

壓縮比和記憶體分析引擎

資料模型中Excel使用記憶體中分析引擎將資料儲存在記憶體中。 引擎會採用強大的壓縮技術來降低儲存需求,縮小結果集,直到其原始大小小一點。

平均而言,您可以預期資料模型在其來源點比相同資料小 7 到 10 倍。 例如,如果您從資料庫SQL Server 7 MB 的資料,Excel中的資料模型可能很容易是 1 MB 或更小於 1 MB。 實際達到的壓縮程度主要取決於每個資料行中唯一值的數量。 值越獨特,儲存所需的記憶體就越高。

為什麼我們要討論壓縮和唯一值? 因為建立能最小化記憶體使用量的有效率模型,完全與壓縮最大化有關,而最簡單的方法是刪除任何您不需要的欄,尤其是當這些欄包含大量的唯一值時。

附註:  個別欄的儲存需求差異可能很大。 在某些情況下,最好使用多個唯一值數目較低的欄,而不是一個唯一值數量高的欄。 Datetime 優化的一節會詳述這項技巧。

記憶體使用量不足時,沒有任何專案比不上不存在的欄

最有記憶體效率的欄就是您一開始從未導入的欄。 如果您想要建立有效率的模型,請查看每一欄,並詢問自己是否有助於您想要執行的分析。 如果沒有,或您不確定,請離開。 如果需要,您之後隨時都可以新增欄。

應永遠排除的兩個欄範例

第一個範例與來自資料倉儲的資料相關。 在資料倉儲中,通常可以找到載入及重新處理倉庫資料之 ETL 程式的專案。 載入資料時,會建立「建立日期」、「更新日期」和「ETL 執行」等欄。 模型不需要這些欄,因此在資料輸入時應該取消選擇。

第二個範例涉及在輸入事實資料表時省略主鍵欄。

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

事實資料表不同。 在事實資料表中,主鍵是用來唯一識別每一列。 雖然需要用於標準化用途,但資料模型卻不太實用,因為您只想要用於分析或建立資料表關係的資料行。 因此,從事實資料表進行輸入時,請勿包含其主鍵。 事實資料表中的主鍵會耗用模型中大量的空間,但無法提供任何好處,因為它們無法用來建立關係。

附註:  在資料倉儲和多維度資料庫中,大部分為數值資料的大型資料表通常稱為「事實資料表」。 事實資料表通常包含業務績效或交易資料,例如匯總並對齊組織單位、產品、市場區段、地理區域等的銷售與成本資料點。 包含商務資料或可用來交叉參照儲存在其他資料表中的事實資料表中的所有資料行,都應該包含在模型中,以支援資料分析。 您想要排除的欄是事實資料表的主鍵欄,它由唯一值所組成,只存在於事實資料表中,而不存在於其他位置。 由於事實資料表非常龐大,因此模型效率的一些最大收益來自于排除事實資料表中的列或欄。

如何排除不必要的欄

有效的模型只包含您實際需要于活頁簿中的欄。 如果您想要控制模型中包含哪些欄,您必須使用Power Pivot附加元件中的資料表匯出精靈來導入資料,而不是在 Excel 中輸入資料。

當您啟動資料表匯出精靈時,會選取要匯出的資料表。

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

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

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

只要篩選必要的列呢?

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

使用資料表匯出精靈,您可以篩選掉歷史資料或不相關的資料,進而節省模型中的大量空間。 在下列影像中,日期篩選只會用來提取包含目前年份資料的列,但不需要歷史資料。

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

如果我們需要該欄,該做什麼?我們仍然可以降低其空間成本嗎?

您可以運用一些額外的技巧,讓欄更適合壓縮。 請記住,影響壓縮的欄唯一特性是唯一值的數量。 在本節中,您將瞭解如何修改某些欄以減少唯一值的數量。

修改日期時間欄

在許多情況下,Datetime 欄會佔用大量的空間。 幸好,有許多方法可以減少此資料類型的儲存需求。 技巧會視您如何使用欄,以及您建立查詢的舒適程度SQL不同。

日期時間欄包含日期部分和時間。 當您詢問自己是否需要欄時,請針對 Datetime 資料行多次詢問相同的問題:

  • 我需要時間部分嗎?

  • 我需要小時層級的時間部分嗎? 分鐘? 秒? 毫秒?

  • 我有多個 Datetime 資料行是因為我想要計算兩者的差異,或只是根據年、月、季等來匯總資料。

如何回答這些問題,會決定處理 Datetime 資料行的選項。

所有這些解決方案都需要修改SQL查詢。 若要更輕鬆地修改查詢,您應該篩選出每個資料表中的至少一欄。 篩選資料行後,查詢結構會從縮寫格式 (SELECT *) 變更為 SELECT 語句,其中包含完全限定的欄名稱,更易於修改。

讓我們來看看您建立查詢。 從 [資料表屬性SQL,您可以切換到 [查詢編輯器>,並查看每個資料表SQL查詢。

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

從資料表屬性中,選取 查詢編輯器

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

查詢編輯器會顯示用來SQL資料表的查詢。 如果您在輸入期間篩選掉任何欄,查詢會包含完全合格的欄名稱:

用來擷取資料的 SQL 查詢

相反地,如果您完整匯入資料表,而不取消選取任何資料行或適用任何篩選,就會看到查詢為「選取 * from",這樣會比較難以修改:

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

修改SQL查詢

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

  1. 若是包含貨幣或小數資料的欄,如果您不需要小數,請使用此語法來刪除小數:

    「選取 [round ([Decimal_column_name],0) ... .”

    如果您需要美分,但不需要分數,請將 0 取代為 2。 如果您使用負數,可以四進位到單位、十、百等。

  2. 如果您有名為 dbo 的 Datetime 資料行。Bigtable。[日期時間],而您不需要 [時間] 部分,請使用語法來排除時間:

    「選取強制轉換 (dbo。Bigtable。[日期時間]) AS [日期時間]) "

  3. 如果您有名為 dbo 的 Datetime 資料行。Bigtable。[日期時間] 而且您需要 [日期] 和 [時間] 兩個部分,請使用 [日期] SQL查詢中的多個資料行,而不是單一的 [日期時間] 欄:

    「選取強制轉換 (dbo。Bigtable。[日期時間] ) AS [日期時間],

    datepart (hh, dbo.Bigtable。[日期時間]) [日期時間時數],

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

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

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

    使用所需的欄數,以將每個部分儲存在個別的欄。

  4. 如果您需要時數和分鐘,而您偏好將它們一起做為一個時間欄,您可以使用語法:

    Timefromparts (datepart (hh, dbo.Bigtable。[日期時間]) , datepart (mm, dbo.Bigtable。[日期時間]) ) [Date Time HourMinute]

  5. 如果您有兩個日期時間欄,例如 [開始時間] 和 [結束時間],而您真正需要的是以秒數表示的兩個日期時間欄之間的時間差,稱為 [Duration],請從清單中移除這兩欄,然後新增:

    "datediff (ss,[開始日期],[結束日期]) [Duration]"

    如果您使用關鍵字 ms 而不是 ss,就會獲得以毫秒為單位的持續時間

使用 DAX 計算量值而非欄

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

其中一種節省記憶體的技術是以計算量值取代一般或計算結果欄。 傳統範例為單價、數量和總計。 如果您擁有這三個,只要維護兩個,然後使用 DAX 計算第三個,就可以節省空間。

您應該保留哪 2 欄?

在上例中,保留數量和單價。 這兩個值比 Total 少。 若要計算總計,請新增計算量值,例如:

「TotalSales:=sumx ('Sales Table','Sales Table'[單價]*'Sales Table'[Quantity]) 」

計算結果欄與一般欄類似,兩者會佔用模型中的空間。 相反地,計算量值是飛航計算,不會佔用空間。

總結

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

  • 移除欄當然也是節省空間的最佳方式。 決定您真正需要哪些欄。

  • 有時候,您可以移除資料行,並將其取代為表格中的計算量值。

  • 您可能不需要表格中的所有列。 您可以在資料表匯出精靈中篩選出列。

  • 一般而言,將單一資料行分成多個不同的部分,是減少欄中唯一值數目的一個好方法。 每一個元件都會有一小部分的唯一值,而合併總和會小於原始的整合資料行。

  • 在許多情況下,您也需要不同的部分,以做為報表的切分器使用。 您可以在適當的時候,從小時、分鐘和秒等部分建立階層。

  • 許多時候,欄包含的資訊也超過您需要的資訊。 例如,假設欄會儲存小數點,但您已使用格式設置來隱藏所有小數點。 進位可以非常有效地減少數值欄的大小。

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

相關連結

資料模型的規格與限制

活頁簿大小優化程式 下載

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×