使用樞紐分析表和 Power Pivot Excel建立具有記憶體效率的資料模型

應永遠排除的兩個欄範例

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

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

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

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

如何排除不必要的欄

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

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

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

修改日期時間欄

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

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

• 我需要時間部分嗎？

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

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

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

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

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

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

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

修改SQL查詢

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

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

   「選取 [四 ([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。[Date Time]) ， datepart (mm， dbo.Bigtable。[日期時間]) ) 為 [Date Time HourMinute]

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

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

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

您應該保留哪 2 欄？

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

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

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