使用 Excel 和 Power Pivot 載入宏 Create 有效運用記憶體的數據模型

兩個應一律排除的欄範例

第一個範例與來自數據倉儲的數據有關。 在數據倉儲中，很常尋找 ETL 處理程式的成品，以載入及重新整理倉庫中的數據。 載入數據時，會建立像是「建立日期」、「更新日期」和「ETL 執行」等欄。 模型中不需要這些欄，當您匯入數據時，應該取消選取這些欄。

第二個範例涉及匯入事實數據表時省略主鍵數據行。

包括事實數據表在內的許多數據表都有主鍵。 對於大部分的數據表，例如包含客戶、員工或銷售數據的數據表，您會想要數據表的主鍵，以便用來在模型中建立關聯。

事實數據表不同。 在事實數據表中，主鍵是用來唯一識別每一列。 雖然正規化是必要的，但在數據模型中，您只想要將這些數據行用於分析或建立數據表關聯，這會比較不實用。 因此，從事實數據表匯入時，請不要包含其主鍵。 事實數據表中的主鍵會耗用模型中大量的空間，但不提供任何好處，因為這些索引鍵無法用來建立關聯。

如何排除不必要的欄

有效率的模型只包含您在活頁簿中實際需要的欄。 如果您想要控制模型中包含哪些數據行，您必須 使用 Power Pivot 載入巨集中的 [資料表匯入精靈] 來匯入數據，而非 Excel 中的 [匯入數據] 對話框。

當您啟動 [資料表匯入精靈] 時，您會選取要匯入哪些數據表。

您可以針對每個數據表單擊 [預覽 & 篩選] 按鈕，然後選取您真正需要的數據表部分。 我們建議您先取消核取所有數據行，然後在考慮是否需要欄進行分析后，繼續檢查您要的欄。

修改 [日期時間] 欄

在許多情況下，[日期時間] 欄會佔用大量空間。 幸好，有許多方法可以降低這種數據類型的儲存需求。 技術會因您使用欄的方式，以及您在建置 SQL 查詢時的舒適程度而有所不同。

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

• 我需要時間部分嗎？

• 我需要時數層級的時間部分嗎？ 分鐘？ 秒？ 毫秒？

• 我是否有多個 [日期時間] 欄，因為我想計算它們之間的差異，或只是要根據年、月、季等項目匯總數據。

每個問題的回答方式會決定處理 [日期時間] 資料行的選項。

所有這些解決方案都需要修改 SQL 查詢。 若要更輕鬆地修改查詢，您應該篩選掉每個數據表中至少一個數據行。 藉由篩選出數據行，您可以將查詢結構從縮寫格式 (SELECT *) 變更為包含完整數據行名稱的 SELECT 語句，以便修改。

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

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

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

修改 SQL 查詢

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

1. 對於包含貨幣或小數數據的數據行，如果您不需要小數字數，請使用此語法來移除小數字數：

   “SELECT ROUND ([Decimal_column_name]，0) ... .”

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

2. 如果您有一個名為 dbo 的日期時間列。Bigtable。[日期時間]，而且您不需要 [時間] 部分，請使用語法來移除時間：

   「選取投射 (dbo。Bigtable。[日期時間] 日期) AS [日期時間]) 」

3. 如果您有一個名為 dbo 的日期時間列。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。[日期時間]) ) 為 [日期時間小時]

5. 如果您有兩個日期時間數據行，例如 [開始時間] 和 [結束時間]，而您真正需要的是以秒為名的欄 [持續時間] 之間的時間差異，請從清單中移除這兩個數據行，然後新增：

   “datediff (ss，[Start Date]，[End Date]) 為 [Duration]」

   如果您使用關鍵詞 ms 而非 ss，則會得到毫秒為毫秒的持續時間

您應該保留哪 2 欄？

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

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

計算結果列就像一般欄，兩者都會佔用模型中的空間。 相反地，計算量值是以動態方式計算，不會佔用空間。