تحلیل جامع و تخصصی Group By در Power Query

در دنیای هوش تجاری، انتخاب لایه مناسب برای انجام عملیات تجمیع می‌تواند به‌طور چشمگیری کارایی و مقیاس‌پذیری راهکار را تحت‌تأثیر قرار دهد. این مقاله، به بررسی دستور Group By در Power Query می‌پردازد؛ عملکرد و نگاشت آن را در مقابل سایر لایه‌های معماری BI (SQL، ETL، DAX) مقایسه می‌کنیم و با مثال‌های عملی از AdventureWorksDW تکنیک‌های پیشرفته بهینه‌سازی را ارائه می‌دهیم. در پایان، با نکات و سناریوهای کاربردی، شما برای تصمیم‌گیری درباره بهترین مکان اجرای تجمیع داده‌ها مسلح خواهید شد.

جایگاه Group By در معماری BI

1. لایه منبع (SQL Server / Oracle / Azure SQL)
2. لایه ETL (SSIS / Azure Data Factory)
3. لایه ‌Power Query (پیش‌پردازش در Power BI Desktop یا Dataflows)
4. لایه مدل‌سازی (DAX Aggregations)

هر لایه مزایا و محدودیت‌های خود را دارد. به‌عنوان مثال:

• اجرای GROUP BY در لایه منبع باعث می‌شود منابع سرور دیتابیس به‌کار گرفته شود و حجم داده منتقل‌شده کاهش یابد.
• در ETL حجم داده را در انتقال میان سیستم‌ها کاهش می‌دهد اما مدیریت خطاها/بازیابی پیچیده‌تر است.
• در Power Query سرعت توسعه و انعطاف بالاست و تغییرات کاربر محور ساده است ولی برای داده‌های حجیم ممکن است حافظه محلی را تحت فشار قرار دهد.
• در مدل‌سازی با DAX می‌توان از Aggregation Tables استفاده کرد اما بار اجرا در زمان پرس‌وجو (Query Time) خواهد بود.

مقایسه عملکرد و مقیاس‌پذیری

بهترین سناریوها برای استفاده از Group By در Power Query

1. ماشه‌سازی سریع (Prototype)
   وقتی نیاز دارید در چند دقیقه یک گزارش نمونه بسازید و داده‌ها را خلاصه کنید.
2. Dataflows در Power BI Service
   انجام خلاصه‌سازی روی سرور برای بهبود اشتراک‌گذاری و نگهداری متمرکز.
3. وقتی Query Folding برقرار است
   اگر Group By به سرور اصلی پاس‌خورده باشد، سرعت و کارایی بالا خواهند بود.
   • تابع‌هایی مانند Table.Group که با Value.NativeQuery سازگارند، Folding را نگه می‌دارند.
4. گرفتن داده‌ اولیه برای مدل‌های ستاره‌ای
   استخراج جداول میانی Aggregation برای بارگذاری سریع در Tabular Model.

سناریوهای نامناسب

• داده‌های بسیار حجیم (میلیون‌ها رکورد) بدون Query Folding
• زمانی که نیاز به جزئیات Row-level دارید (حتی تحلیل پیشرفته)
• عملیات پیچیده چند مرحله‌ای که به Batch Processing یا Retry Logging نیازمندند.

نکات پیشرفته برای بهینه‌سازی

1. Query Folding
   • همیشه حالت folding را در Diagnostics بررسی کنید.
   • از مراحل ساده و توابع استاندارد (Filter, Remove Columns, Group By ساده) استفاده کنید تا Folding حفظ شود.
2. Buffer کردن جدول پیش از Group By
   در صورت ارور «DataFormat.Error» یا تغییر مداوم در Source:

let
  Buffered = Table.Buffer(RawTable),
  Grouped = Table.Group(Buffered, {"Key"}, {{"Sum", each List.Sum([Value]), type number}})
in
  Grouped

1. تقسیم‌بندی (Partitioning) در Dataflows
   گروه‌بندی را روی Partition‌های منطقی (مثلاً سال یا ماه) انجام دهید تا حجم حافظه کاهش یابد.
2. Nested Group By
   وقتی Aggregation چند سطحی لازم است:

let
  Level1 = Table.Group(Source, {"Country"}, {{"Data", each _, type table}}),
  Level2 = Table.TransformColumns(Level1, {"Data", each Table.Group(_, {"City"}, {{"Count", each Table.RowCount(_), Int64.Type}})})
in
  Level2

مثال:

با استفاده از AdventureWorksDW

هدف: محاسبه ماهانه میانگین فروش محصولات دسته‌بندی‌شده بر اساس کلاس محصول و ناحیه.

let
  Sales = FactInternetSales,
  Product = DimProduct,
  Customer = DimCustomer,
  
  // مرتب‌سازی و Merge
  Merged1 = Table.NestedJoin(Sales, "ProductKey", Product, "ProductKey", "Prod", JoinKind.Inner),
  Expanded1 = Table.ExpandTableColumn(Merged1, "Prod", {"EnglishProductSubcategoryName", "EnglishProductCategoryName"}),
  Merged2 = Table.NestedJoin(Expanded1, "CustomerKey", Customer, "CustomerKey", "Cust", JoinKind.Inner),
  Expanded2 = Table.ExpandTableColumn(Merged2, "Cust", {"EnglishCountryRegionName"}),
  
  // افزودن ستون تاریخ ماهانه
  AddMonth = Table.AddColumn(Expanded2, "YearMonth", each Date.ToText([OrderDate], "yyyy-MM"), type text),
  
  // گروه‌بندی دو سطحی: (Category, Region) -> هر ماه
  Grouped = Table.Group(
    AddMonth,
    {"EnglishProductCategoryName","EnglishCountryRegionName","YearMonth"},
    {
      {"TotalSales", each List.Sum([SalesAmount]), type number},
      {"AvgSales", each List.Average([SalesAmount]), type number},
      {"OrderCount", each List.Count([SalesOrderNumber]), Int64.Type}
    }
  )
in
  Grouped

نتیجه گیری و توصیه‌های نهایی

• برای داده‌های حجیم و عملیات سنگین، ابتدا در لایه منبع یا ETL گروه‌بندی کنید.
• برای انعطاف‌پذیری توسعه و نمونه‌سازی سریع، از Power Query بهره ببرید.
• همیشه Folding را بررسی کنید و در صورت نیاز، از Buffer و Partition استفاده کنید.
• در مدل‌سازی پیشرفته، از ترکیب Group By در Power Query با Aggregation Tables در Tabular Model استفاده کنید تا بهترین توازن بین سرعت بارگذاری و سرعت واکنش گزارش بدست آید.

سوالات متداول (FAQ)

۱. چگونه Folding را در مراحل Group By حفظ کنیم؟

اجتناب از توابع M سفارشی پیش از Group By، استفاده از Viewهای SQL Server و کدنویسی Table.Group بدون توابع پیچیده، کمک می‌کند عملیات روی سرور اجرا شود.

۲. آیا Group By در Power Query می‌تواند جایگزین SSAS Aggregation Tables شود؟

برای مقیاس‌های بزرگ و کاربران همزمان زیاد، SSAS/Staged Aggregations معقول‌تر است. Power Query Dataflows برای محاسبات دوره‌ای متوسط مناسب است.

۳. حافظه ناکافی در Power Query هنگام Group By را چگونه مدیریت کنم؟

• از Partitioning و Buffer کردن صحیح استفاده کنید.
• داده‌های ورودی را با فیلترهای اولیه کاهش دهید (مثلاً فقط دو سال اخیر).

۴. چطور Nested Group By را پیچیده‌تر کنم؟

ابتدا روی ستون‌های مرجعی گروه‌بندی کنید، سپس روی هر گروه با تابع Table.TransformColumns‌ گروه‌بندی ثانویه انجام دهید.

۵. تفاوت هزینه محاسباتی Group By در Power Query با DAX چیست؟

• Power Query: وقت Design Time پردازش می‌کند، بار اجرایی مدل سبک می‌شود.
• DAX: وقت Query Time پردازش انجام می‌دهد و ممکن است زمان واکنش گزارش را افزایش دهد.

ارتباط و مشاوره

برای اطلاعات بیشتر و مشاوره می‌توانید از طریق زیر با ما در ارتباط باشید:

• تماس ✆ با شرکت لاندا برای مشاوره، اجرا و یا آموزش تخصصی.