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

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

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

附註: 本文將說明 Excel 2013 中的資料模型。不過,相同的資料模型和在 Excel 2013 Power Pivot 功能也適用於 Excel 2016。有些許差異有效之間的 Excel 版本。

雖然您可以輕鬆地建立大量的資料模型,在 Excel 中,有幾個原因不到。包含許多的資料表和資料行的第一個、 大型模型對大部分的分析,而讓的麻煩的欄位清單。第二個、 大型模型使用寶貴的記憶體,產生負面影響其他應用程式和共用相同的系統資源的報表。最後,在 Office 365 中 SharePoint Online 與 Excel Web App 限制為 10 MB 的 Excel 檔案的大小。對於包含數百萬個資料列的活頁簿資料模型,會遇到 10MB 的限制很快。請參閱資料模型的規格與限制

在本文中,您將瞭解如何建立結構緊密的模型,不但更易於使用,也會使用較少的記憶體。花費時間瞭解有效模型設計中的最佳作法,對您未來所建立及使用的任何模型都很有效益,無論您是在 Excel 2013 ,還在是 Office 365 SharePoint Online、Office Web Apps Server,或在 SharePoint 2013 中進行檢視。

請考慮也執行活頁簿大小最佳化工具。它會分析您的 Excel 活頁簿,並可能的話,請進一步壓縮。下載活頁簿大小最佳化工具

本文內容

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

不問倒不存在於欄記憶體使用量

兩個資料行,應該一律排除的範例

如何排除不需要的欄

篩選只需要的列呢?

如果需要欄;我們仍降低其空間成本嗎?

修改日期時間欄

修改 SQL 查詢

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

2 您應該保留哪欄嗎?

結束時

相關的連結

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

Excel 中的資料模型使用記憶體內分析引擎將資料儲存在記憶體中。此引擎會執行功能強大的壓縮技術以降低儲存需求,並將結果集壓縮到只有原始大小的一小部分。

平均而言,您可以預期資料模型會比其原點內相同資料小 7 到 10 倍。舉例來說,如果您從 SQL Server 資料庫匯入7 MB 的資料,Excel 中的資料模型可能是 1 MB 或更小。實際達成的壓縮程度主要取決於各欄中唯一值的數目。唯一值愈多,需要用來進行儲存的記憶體也愈多。

為什麼我們要提到壓縮和唯一值? 因為建立一個可降低記憶體使用量的有效模型,重點就是將壓縮極大化,執行這項作業最簡單的方法,是移除您不真正需要的任何欄,特別是如果這些欄含有大量的唯一值。

附註: 個別欄對儲存需求的差異可以很大。在某些情況下,最好是使用多個欄,但唯一值的數目較少;而不要只有一個欄,但卻含有大量唯一值。<日期時間最佳化>一節會詳細說明這項技術。

不存在的欄最能有效減少記憶體使用量

最能有效使用記憶體的欄是您在第一開始就未曾匯入的欄。如果您想要建立有效的模型,請查看每一欄,並自問這能否對您想要執行的分析有所貢獻。如果沒有貢獻或您並不確定,請略過此欄。稍後你可以視需要隨時新增欄。

一律必須排除的兩個欄範例

第一個範例與源自資料倉儲的資料有關。在資料倉儲中,ETL 處理序的成品很常見,這些處理續會載入並重新整理資料倉儲。載入資料時,即會建立 [建立日期]、[更新日期] 及 [ETL 執行] 等欄。模型中完全不需要這些欄,當您匯入資料時,請加以取消選取。

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

許多資料表 (包括事實資料表) 都有主索引鍵。對大多數資料表 (例如含有客戶、員工或銷售資料的資料表) 來說,您會想要使用資料表的主索引鍵,方便您用來建立模型中的關聯。

事實資料表並不相同。在事實資料表中,主索引鍵會用來唯一識別每一列。雖然對正規化是必要的,但在資料模型中卻較不實用,因為此時您只想要用於分析或建立資料表關聯的這些欄。基於這個理由,從事實資料表匯入時,請不要包含主索引鍵。事實資料表中的主索引鍵會耗用模型中大量的空間,但卻沒有任何效益,因為這些無法用來建立關聯。

附註: 在資料倉儲和多維度資料庫中,主要由數值資料組成的大型資料表通常會稱為「事實資料表」。事實資料表一般包括企業績效或交易資料,例如彙總的銷售和成本資料點,並配合組織單位、產品、市場區隔、地理區域等。事實資料表中所有的欄都必須包含在模型中以支援資料分析,因為事實資料表中含有業務資料或可用於交互參照其他資料表中儲存的資料。您想要排除的欄是事實資料表的主索引鍵資料行,其中包含只存在事實資料表中、但其他地方都沒有的唯一值。因為事實資料表非常龐大,模型效率提高的原因之一來自於排除事實資料表的列或欄。

如何排除不需要的欄

有效的模型包含您會在您的活頁簿中真的需要這些資料行。如果您想要控制模型中包含的資料行,您必須使用在 [Power Pivot 增益集匯入資料的 [資料表匯入精靈,而非 「 匯入資料 」] 對話方塊,在 Excel 中。

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

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

對每個資料表來說,您可以按一下 [預覽及篩選] 按鈕,然後選取您真正需要的資料表部分。我們建議您先將所有欄取消核取,然後考量分析時需要的欄,再繼續核取所需的欄。

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

只篩選所需要的列呢?

企業資料庫和資料倉儲中的許多資料表包含長時間累計的歷史資料。此外,您可能會發現自己想要的資料表包含特定分析不需要的各區域業務資訊。

使用 [資料表匯入精靈],您可以篩選掉歷史或不相關的資料,因而節省許多模型空間。下圖中使用了日期篩選,只擷取包含今年資料的列,排除不需要的歷史資料。

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

如果需要欄,我們仍然有辦法降低其空間成本嗎?

您可以運用幾個額外的技術來提高欄的壓縮效率。請記住,唯一會影響壓縮的欄特性是唯一值的數目。在本節中,您將瞭解如何修改某些欄,以減少唯一值的數目。

修改日期時間欄

在許多情況下,日期時間欄會耗費許多空間。幸運的是,您有數種方法可減少這種資料類型的儲存需求。各種技術會依您使用欄的方式,以及對建立 SQL 查詢的熟悉度而異。

日期時間欄包含日期和時間。當您自問是否需要某個欄時,也多問問自己是否需要日期時間欄:

  • 我需要時間的部分嗎?

  • 我需要時間的小時部分嗎? 分鐘部分呢? 秒數部分呢? 毫秒部分呢?

  • 我是否有多個日期時間欄,因為我想要計算它們之間的差異,或只依年、月、季等來彙總資料。

您對每個問題的答案會決定用來處理日期時間欄的選項。

這些解決方案全都需要修改 SQL 查詢。為簡化查詢修改,每一個資料表中應至少篩選出一欄。篩選出某欄後,請將查詢結構從縮寫格式 (SELECT *) 變更為包含完整欄名稱的 SELECT 陳述式,以方便修改。

現在就讓我們來看一看針對您所建立的查詢。從 [資料表屬性] 對話方塊,您可以切換至查詢編輯器並查看每個資料表目前的 SQL 查詢。

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

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

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

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

用來擷取資料的 SQL 查詢

相反地,如果您完整匯入某個資料表,沒有取消核取任何欄或套用任何篩選,您會看到查詢為 “Select * from ”,這就較難修改:

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

修改 SQL 查詢

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

  1. 針對包含貨幣或小數資料的欄,如果您不需要小數位數,請使用下列語法移除小數位數:

    “SELECT ROUND([Decimal_column_name],0)… .”

    如果您需要角,但不需要分,請以 2 取代 0。如果您使用負值,則可以四捨五入至十位數、百位數等單位。

  2. 如果您有一個名為 dbo.Bigtable.[Date Time] 的日期時間欄,但不需要時間部分,請使用此語法移除時間:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

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

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    根據需要使用數目不限的欄,以在不同欄中儲存每一個部分。

  4. 如果您需要小時及分鐘,而且想要組合為一個時間欄,您可以使用此語法:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

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

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

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

使用 DAX 導出量值,不使用欄

如果您之前用過 DAX 運算式語言,您可能已經知道計算結果欄會用來衍生模型中以其他欄為基礎的新欄,雖然導出量值會在模型中定義一次,但只有用在樞紐分析表或其他報表時才會進行評估。

節省記憶體的另一個技術是使用導出量值來取代一般資料行或計算結果欄。典型的範例是 [單價]、[數量] 和 [總計]。如果這三個您都有,則可以只保留兩個,然後使用 DAX 來計算第三個,以節省空間。

您應該保留哪兩欄?

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

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”

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

結論

在本文中,我們提到幾種方法,可協助您建立更能有效使用記憶體的模型。縮減資料模型檔案大小和記憶體需求的方式,是減少欄和列總數,以及每一欄中出現的唯一值的數目。以下是我們所討論的一些技術:

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

  • 有時候,您可以移除某欄,並以資料表中的導出量值取代。

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

  • 一般而言,將單一欄分成多個不同部分是很好的方法,可減少欄中唯一值的數目。每個部分會有少數唯一值,合併的總計也將小於原始整合欄。

  • 在許多情況下,您也需要不同部分,以做為報表中的交叉分析篩選器。在適當的時候,您可以從時、分及秒等部分建立階層。

  • 欄也經常包含超過需求的資訊。例如,假設某欄儲存小數,但您套用了格式設定隱藏所有的小數位數。四捨五入可以很有效地減少數值欄的大小。

現在您已完成您可以以減少活頁簿的大小,請考慮也執行活頁簿大小最佳化工具。它會分析您的 Excel 活頁簿,並可能的話,請進一步壓縮。下載活頁簿大小最佳化工具

相關連結

資料模型的規格與限制

活頁簿大小最佳化工具下載

Power Pivot︰ 強大資料分析與在 Excel 中的資料模型

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×