使用 Excel 和 Power Pivot 增益集建立有效使用記憶體的資料模型

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

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

雖然您可以輕鬆地在 Excel 中建立大量的資料模型,但有幾個原因無法實現。 首先,包含資料表和資料行 multitudes 的大型模型是針對大多數分析進行 overkill,並產生不重要的欄位清單。 第二,大型模型會佔用寶貴的記憶體,對共用相同系統資源的其他應用程式和報告產生負面影響。 最後,在 Office 365中,SharePoint Online 和 Excel Web App 會將 Excel 檔案的大小限制為 10 MB。 對於包含上百萬列的活頁簿資料模型,您可以快速執行 10 MB 的限制。 請參閱資料模型的規格與限制

在本文中,您將瞭解如何建立更容易使用且使用較少記憶體的緊密構造模型。 無論您是在 Excel 2013、 Office 365 SharePoint Online、 Office Web Apps 伺服器或 SharePoint 2013 中查看,花時間瞭解有效模型設計中的最佳做法,都將會減少您所建立及使用的任何模型的道路。

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

本文內容

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

在記憶體使用量較低的情況下,不存在的資料會有任何節拍

應永遠排除的兩個欄範例

如何排除不需要的欄

只篩選所需的資料列呢?

如果我們需要資料行,該怎麼辦;我們仍能降低其空間成本嗎?

修改日期時間欄

修改 SQL 查詢

使用 DAX 匯出量值,而不是欄

您應該保留哪兩欄?

總結

相關連結

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

Excel 中的資料模型會使用記憶體內分析引擎,將資料儲存在記憶體中。 引擎實現功能強大的壓縮技術以減少儲存需求,收縮結果集,直到它是原始大小的分數。

根據平均值,您預計資料模型的速度必須小於原始位置的相同資料。 例如,如果您要從 SQL Server 資料庫匯入 7 MB 的資料,Excel 中的資料模型可能會輕鬆為 1 MB 或更少。 實際達到的壓縮程度,主要取決於每個資料行中唯一值的數目。 不重複的值越多,儲存所需的記憶體就越多。

為什麼我們要談論壓縮和唯一值? 因為組建能最大限度地減少記憶體使用量的有效模型完全是關於壓縮 maximization,所以最簡單的方法就是去掉您不需要的任何欄,尤其是當那些欄中包含大量的唯一值時。

附註:  個別資料行的儲存需求差異可能很大。 在某些情況下,您可以讓多個資料行具有較少的唯一值,而不是一個具有大量唯一值的資料行。 [日期時間] 優化區段中的章節將詳細說明這項技術。

在記憶體使用量較低的情況下,不存在的資料會有任何節拍

[記憶體效率最高] 的資料行是您第一次不會匯入的資料行。 如果您想要建立有效的模型,請查看每一欄,並詢問其是否對您想要執行的分析有貢獻。 如果不確定,請將它關閉。 如果您需要的話,您可以隨時新增欄。

應永遠排除的兩個欄範例

第一個範例與源于資料倉儲的資料有關。 在資料倉儲中,您通常會發現在倉庫中載入和重新整理資料的 ETL 程式專案。 載入資料時,會建立例如「建立日期」、「更新日期」和「ETL 執行」等欄。 在模型中不需要這些資料行,因此當您匯入資料時,應該取消選取這些資料行。

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

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

事實資料表是不一樣的。 在事實資料表中,主鍵是用來唯一識別每個資料列。 在需要標準化的情況下,在資料模型中不會有任何用處,您只想要使用這些欄進行分析或建立資料表關聯。 基於這個原因,從事實資料表匯入時,請勿包含其主鍵。 事實資料表中的主鍵會在模型中佔用大量空間,但不提供任何好處,因為它們無法用來建立關聯。

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

如何排除不需要的欄

[高效模型] 只包含您在活頁簿中實際需要的欄。 如果您想要控制包含在模型中的欄,您必須使用 Power Pivot 增益集中的 [資料表匯入嚮導],以匯入資料,而不是 Excel 中的 [匯入資料] 對話方塊。

當您啟動 [資料表匯入嚮導] 時,請選取要匯入的資料表。

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

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

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

只篩選所需的資料列呢?

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

您可以使用 [資料表匯入嚮導] 篩選出已記錄或不相關的資料,進而在模型中儲存大量空間。 在下圖中,日期篩選只是用來檢索包含目前年份資料的資料列,但不需要的歷史資料。

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

如果我們需要資料行,該怎麼辦;我們仍能降低其空間成本嗎?

您也可以套用一些其他方法,讓欄更適合壓縮。 請記住,影響壓縮之資料行的唯一特徵是唯一值的數目。 在本節中,您將瞭解如何修改某些欄來減少唯一值的數目。

修改日期時間欄

在許多情況下,Datetime 欄會佔用大量空間。 幸運的是,有許多方法可減少此資料類型的儲存需求。 這些技術會因您使用資料行的方式和您在建立 SQL 查詢的舒適層面而有所不同。

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

  • 我需要時間部分嗎?

  • 我需要幾個小時的時間部分嗎? 持續? 秒? 時間?

  • 我是否有多個 Datetime 欄,因為我想要計算它們之間的差異,或只是依年份、月份、季度等匯總資料。

您回答這些問題的方式,可決定處理 Datetime 資料行的選項。

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

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

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

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

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

[查詢編輯器] 會顯示用來填入資料表的 SQL 查詢。 如果您在匯入期間篩選掉了任何資料行,您的查詢就會包含完全限定的欄名稱:

用來擷取資料的 SQL 查詢

相反地,如果您已匯入完整的表格,而不取消選取任何欄或套用任何篩選器,您會看到查詢是「選取 * 寄件者」,而這會比較難以修改:

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

修改 SQL 查詢

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

  1. 針對包含貨幣或小數資料的資料行,如果您不需要小數,請使用此語法來去掉小數:

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

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

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

    「選取轉換(dbo)。Bigtable.[日期時間] 做為日期)做為 [日期時間]) "

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

    「選取轉換(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.[日期時間]))as [日期時間 HourMinute]

  5. 如果您有兩個 datetime 欄(例如 [開始時間] 和 [結束時間]),而且您真正需要的是在數秒內的時間差異,請從清單中移除兩個數據行,並新增:

    "datediff (ss,[開始日期],[結束日期])作為 [工期]"

    如果您使用的是關鍵字 ms 而不是 ss,則會以毫秒為單位取得工期

使用 DAX 匯出量值,而不是欄

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

一個儲存在記憶體中的技術是使用計算量值取代一般或計算欄。 [傳統] 範例是 [單價]、[數量] 和 [合計]。 如果您有三個,您可以只保留兩個,然後使用 DAX 計算第三個,以節省空間。

您應該保留哪兩欄?

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

"TotalSales: = sumx (" 銷售資料表 "," 銷售資料表 "[單位價格] *" 銷售資料表 "[數量])"

計算結果欄就像一般的資料行,兩者都佔用模型中的空間。 相反地,計算量值是在即時計算,不會佔用空間。

總結

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

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

  • 有時候,您可以移除欄,並以表格中的計算量值取代。

  • 您可能不需要表格中的所有資料列。 您可以在 [資料表匯入] 嚮導中篩選出列。

  • 一般來說,將單一欄分割成多個不同的部分是一種減少欄中的唯一值數目的好方法。 每個元件都會有少量的唯一值,而合併總計則會比原始的統一資料行小。

  • 在許多情況下,您也需要在報表中使用不同的部分做為交叉分析篩選器。 適當時,您可以從元件(例如小時、分鐘和秒)中建立階層。

  • 很多時候,欄包含的資訊比您需要的還要多。 例如,假設欄會儲存小數,但您已套用格式設定以隱藏所有小數。 在減少數值欄的大小時,舍入可能會非常有效。

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

相關連結

資料模型的規格與限制

活頁簿大小優化程式下載

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

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×