數據加載和轉換最佳實踐

我想介紹我在準備 LuckyTemplates 報告時採用的最佳做法。在過去的幾年裡，我使用了很多關於數據加載和轉換的在線資源，這些資源對我的旅程很有幫助，但我很快就被這些內容淹沒了，所以我最終做了自己的筆記。您可以在本博客底部觀看本教程的完整視頻。

這絕不是一個詳盡的列表，也不是最佳實踐——它們只是我已經融入到我自己的開發中的一些。此外，隨著 LuckyTemplates 應用程序中引入新的和增強的功能並由 LuckyTemplates 社區提供，最佳實踐總是隨著時間的推移而發展。

我想介紹 LuckyTemplates 開發四大支柱中的第一條。四大支柱是數據加載和數據轉換、數據建模、DAX 計算以及報告和可視化。我們將在這篇博文中處理開發前設置和第一個支柱。

目錄

• 禁用自動日期/時間
• 禁用自動檢測關係
• 與 LuckyTemplates 報表中的視覺對象交互
• 從報告中分離數據集
• 創建專用日期表
• 日期表的其他提醒
• 就報告提出問題
• 登台和參考數據
• 減少數據負載
• 查詢折疊
• 使用數據源進行查詢折疊
• 選擇正確的連接模式
• 定位轉換
• 命名和數據類型的最佳實踐
• 以一致的粒度獲取數據
• 結論

禁用自動日期/時間

強烈建議您禁用中的自動日期和時間功能。您可以通過兩種方式執行此操作。

您可以通過以下步驟對所有文件全局執行此操作：

您還可以通過更改其設置為當前文件禁用它。

禁用自動檢測關係

還強烈建議您禁用自動檢測關係。您可以轉到文件，然後查看選項和設置以在加載數據後禁用自動檢測新關係。

與 LuckyTemplates 報表中的視覺對象交互

接下來我要談的是可以與 LuckyTemplates 報表中的視覺對象交互的兩種方式。這些交互可以交叉過濾或交叉突出顯示。

LuckyTemplates 中默認啟用交叉突出顯示。您可以通過進入文件然後進入選項和設置來更改它。對於報告設置部分中的當前文件，您可以將視覺交互從交叉突出顯示更改為交叉過濾。

讓我們來看看我們的報告。您可以看到，默認情況下，LuckyTemplates 在相關視覺對像上使用交叉突出顯示。如果我單擊左側條形圖中的一個通道，您會看到所選部分以深色突出顯示，而未選擇的部分在第二個條形圖中保持淺色。

您可以看到圖中的完整形狀沒有變化，您只看到突出顯示的部分。您可以從報告設置中更改此設置，並將默認的視覺交互從交叉突出顯示更改為交叉篩選。

完成此操作後，您會發現每次進行一個或一組選擇時，相關條形圖的形狀都會發生變化。這是一個很好的實踐。

從報告中分離數據集

接下來我想談的是將數據集開發與報告開發分開。在這裡，您可以將厚數據集文件發佈為沒有視覺效果的獨立數據集，以及將使用這些已發布數據集的薄報告文件開發。

單獨發布數據集的優勢之一是您可以使用 LuckyTemplates 服務中的認可流程來推廣和認證數據集。在開發新報告時，最好選擇認可的數據集，最好是經過認證的數據集。

創建專用日期表

確保您的報告中有專門的日期表。您可以使用LuckyTemplates 論壇上提供的您可以將 M 代碼複製並粘貼到空白查詢中。

一旦您有了專用的 Dates 表，要做的第二件事就是將其標記為這樣。這將使它能夠被 LuckyTemplates 用於時間智能計算。

日期表的其他提醒

始終確保您的數據表是連續的，並且每天只有一行。此外，檢查它是否完全覆蓋了事實表的邊緣。

此外，將完整的年份添加到 Dates 表中。將額外的未來年份添加到 Dates 表以啟用任何未來或預測時間智能計算也是一個好主意。

如果日期表中的天數多於要在報告中顯示的天數，則可以使用 IsAfterToday 列或篩選器窗格中可用的偏移列來控制切片器中顯示的內容。

就報告提出問題

接下來我要談的是你是否應該做一份報告。在執行此操作時，請檢查是否有可用於滿足您需求的現有報告。如果沒有報告，是否有可用於滿足需求的現有數據模型？是否有可以使用的現有數據集，或者您是否真的需要從頭開始？

登台和參考數據

數據加載時接下來要談的是暫存和引用數據。當您加載平面數據表時，您可能在同一個表（如 Excel 文件）中包含事實和維度。當您執行加載並將其移動到暫存查詢部分並重命名時，您可以取消選中Enable load。

下一步是為每個事實和查找維度創建該表的引用，並適當地重命名這些表。編輯每個引用，只保留需要的列，並刪除重複項。

組織您的數據模型，並將其放在自己的組中。我的數據模型中總是有一個名為數據模型的組，其中包含我將在視覺效果中使用的所有表格。

減少數據負載

接下來要說的是工具在數據加載時的表現。影響性能的最大因素之一是數據量，因此盡量減少要加載的數據量。

遵循這個公理：

如果您不需要它，請不要取回它。

向報告中添加新內容比從報告中刪除內容要容易得多。此外，當您在報告中放入太多內容時，您將為您不需要體驗的性能付出代價。

最好在源代碼中進行過濾。如果您無法在源代碼中執行此操作，請使用高級查詢中的自動篩選器下拉箭頭來篩選您感興趣的字段。

同樣在源代碼中，您還可以通過使用“選擇列”下拉菜單並僅選擇您感興趣的列來減少顯示的列數。

您可以使用Choose columns或Remove columns。我建議使用選擇列作為最佳實踐，因為它很容易返回。

如果您以後改變主意，它允許您通過 UI 快速完成，而不是進入高級編輯器並直接編輯 M 代碼。

查詢折疊

查詢折疊是 LuckyTemplates 將多個數據選擇和轉換步驟組合到單個數據源查詢中的另一種數據加載嘗試。

要確定查詢是否被折疊，請右鍵單擊查詢的應用步驟。您可以查看本機查詢是否顯示為灰色。

如果它是灰色的，那麼查詢沒有被折疊。如果它沒有變灰，則查詢可以折疊。

讓我們轉到Transform data，然後是power query。在此表中，如果我右鍵單擊導航步驟，我可以看到View Native Query沒有變灰，因此我可以選擇它。

我們將在這裡看到一個簡單的 SQL select 語句。

如果我們在完成過濾後轉到下一步並查看本機查詢，我們可以看到查詢已略有更改，並且在該查詢的底部有一個“where”子句。

如果我們進入第三步，右擊它，我們可以看到原生查詢沒有變灰，所以我們可以再次選擇它。

選擇後，我們可以看到三個查詢合併為一個查詢的程度更大。這是 LuckyTemplates 所說的將數據導入模型的最佳方式是由數據源來完成工作，而不是我來完成工作。

使用數據源進行查詢折疊

通常可用於查詢折疊的數據源是關係數據庫中的標準對象，如表和視圖。

對關係數據庫的自定義 SQL 查詢不能使用查詢折疊。平面文件和網絡數據也不能折疊。

一些可以與查詢折疊一起使用的轉換是過濾行、刪除列、重命名列以及連接到來自同一數據源的其他查詢。

一些不能與查詢折疊一起使用的轉換包括添加索引列、更改列數據類型以及合併或附加來自不同數據源的查詢。

選擇正確的連接模式

提高性能的下一步是選擇正確的連接模式。導入模式是默認模式，應盡可能使用，因為它提供最佳報告性能。

如果需要最新數據，可以使用直接查詢模式，但請注意，它可能而且很可能會對性能產生負面影響。

最後，當您訪問 SSAS 多維多維數據集等數據倉庫時，可以使用實時連接模式。

定位轉換

接下來我要談的是這些轉換的位置。執行它們的最佳位置是在源代碼中。如果您不能在源代碼中執行它們，請在power query中執行它們。如果您不能在 power query 中執行它們，則在DAX中執行它們。

盡可能在上游執行數據轉換，並在必要時盡可能在下游執行數據轉換。

如果您可以在 power query 中做一些事情，那麼您可能應該這樣做。

同樣，如果它在報表會話上下文中不是動態的，請考慮在 power query 中執行它以簡化 DAX 並提高報表性能。

就表格的形狀而言，努力使事實表又長又薄，並努力使維度表又短又寬。

命名和數據類型的最佳實踐

使用易於報表用戶理解的一致命名和大小寫方案。根據需要重命名表和查詢，以符合報表的命名和大小寫標準。

根據需要重命名您的列以符合這些命名和外殼標準，並根據需要重命名您的強大查詢步驟以使這些步驟自我描述，因為您可能不是維護報表的人。

LuckyTemplates 在導入數據時可以很好地分配正確的數據類型，但有時也需要進行一些調整。您應該確保不同表中的列將用作具有相同數據類型的兩個表之間的鏈接列。

確保所有日期列都是日期而不是文本。將您的日期/時間列拆分為單獨的日期和單獨的時間列。

作為最後一步，重新檢查您的數據類型。在 power query 中點擊Close 和 Apply之前養成習慣，總是重新檢查你的數據類型，因為某些轉換可以悄無聲息地將數據類型切換為文本。

以一致的粒度獲取數據

最後要討論的是粒度。在數據加載和轉換過程中，您應該努力以一致的粒度獲取數據。當您在解決方案中組合不同的粒度時，使用 power query（最好）或 DAX 來適當地分配參考數據。

比如你的主要銷售數據是日級別的，那麼它的粒度就是日。如果你的預算參考數據是月度級別的，那麼它的粒度是月度的。以下是預算分配 DAX 公式的示例，您可以在進行粒度調整時查看。

結論

我希望您發現本關於數據加載和轉換的教程很有用。如果您這樣做了，請不要忘記訂閱 LuckyTemplates YouTube 頻道，以確保您收到任何新內容的通知。

格雷格