DAX (Data Analysis Expressions) زبان قدرتمند Power BI برای پرس و جو و محاسبه داده‌ها است. اما کوئری‌های DAX ضعیف می‌توانند به کندی قابل توجهی در بارگذاری گزارش‌ها و تعامل با داشبورد منجر شوند. بهینه‌سازی عملکرد کوئری‌های DAX یک مهارت کلیدی برای توسعه‌دهندگان Power BI است که به ارائه تجارب کاربری روان و کارآمد کمک می‌کند. این مقاله به بررسی تکنیک‌های مختلف برای بهینه‌سازی عملکرد کوئری‌های DAX می‌پردازد.

عوامل موثر بر عملکرد کوئری‌های DAX:

بیایید عوامل موثر بر عملکرد کوئری‌های DAX را با جزئیات بیشتری بررسی کنیم. درک دقیق این عوامل، کلید بهینه‌سازی موثر کوئری‌ها و بهبود عملکرد Power BI است.

1. اندازه مدل داده (Data Model Size):

• تاثیر: مدل‌های داده‌ای بزرگتر، حاوی میلیون‌ها یا میلیاردها ردیف داده، به زمان پردازش بسیار بیشتری نیاز دارند. DAX برای هر سطر داده در جدول محاسبه را انجام می‌دهد. افزایش حجم داده به طور خطی بر زمان پردازش تاثیر می‌گذارد.
• راهکارها: استفاده از تکنیک‌های فیلتر کردن موثر، به کارگیری شاخص‌ها (Indexes)، استفاده از نمونه‌گیری داده‌ها (Sampling) در صورت امکان، تجزیه و تحلیل داده‌ها در سطح جزئیات کمتر و خلاصه‌سازی داده‌ها پیش از بارگذاری به Power BI همگی از راهکارهای کاهش حجم داده برای پردازش هستند. انتخاب درست نوع داده‌ها (مثلا استفاده از داده‌های عددی به جای متنی زمانی که امکان‌پذیر است) نیز کمک می‌کند.

2. پیچیدگی کوئری (Query Complexity):

• تاثیر: کوئری‌های پیچیده با چندین تابع تو در تو، محاسبات پیچیده، و بخصوص استفاده از توابع جدول به‌طور قابل‌توجهی زمان پردازش را افزایش می‌دهند. هر لایه از توابع جدول می‌تواند به درجه پیچیدگی و زمان پردازش بیفزاید.
• راهکارها: سعی کنید کوئری‌ها را به بخش‌های کوچک‌تر و ساده‌تر تقسیم کنید و از توابع ساده‌تر و بهینه‌تر استفاده کنید. از VAR برای ذخیره‌سازی مقادیر موقت استفاده کنید تا از تکرار محاسبات جلوگیری شود. از توابع بهینه‌تر مانند CALCULATE به جای SUMX (مگر در مواقع لازم) استفاده کنید. استفاده از Profiler برای شناسایی نقاط کند و پیچیده در کوئری نیز بسیار موثر است.

3. ساختار مدل داده (Data Model Structure):

• تاثیر: روابط ضعیف یا نادرست بین جداول، عدم وجود شاخص‌ها روی ستون‌های کلیدی، و مدل داده‌ای غیر بهینه می‌تواند به کاهش عملکرد منجر شود. اگر جداول به درستی به هم مرتبط نباشند، DAX نیاز به زمان بیشتری برای پیدا کردن داده‌های مرتبط دارد.
• راهکارها: مطمئن شوید که روابط بین جداول به درستی تعریف شده‌اند و از نوع رابطه مناسب استفاده می‌شود. ایجاد شاخص‌ها روی ستون‌های کلیدی در جداول بزرگ بسیار موثر است. استفاده از تکنیک‌های مدل‌سازی داده‌ای بهینه مانند استفاده از جداول کمکی (Summary Tables) و بهینه‌سازی طرح مدل داده، بسیار مفید است.

4. نوع و حجم داده (Data Type and Volume):

• تاثیر: نوع داده‌ها و حجم آن‌ها در سرعت پردازش تاثیرگذار هستند. داده‌های عددی به طور معمول سریعتر از داده‌های متنی پردازش می‌شوند. داده‌های متن طولانی یا فیلدهای محاسباتی که string‌های طولانی ایجاد می‌کنند می‌توانند عملکرد را به شدت کاهش دهند. حجم بالای داده‌ها نیز باعث افزایش زمان پردازش می‌شود.
• راهکارها: از نوع داده مناسب برای هر ستون استفاده کنید. اگر ممکن است، از داده‌های عددی به جای داده‌های متنی استفاده کنید. استفاده از فیلترها برای محدود کردن حجم داده پیش از محاسبات، بسیار موثر است. بهینه‌سازی انواع داده‌ها و فیلتر کردن داده‌های نامرتبط، باعث کاهش حجم داده‌های پردازشی می‌شود.

5. استفاده از توابع (Function Usage):

• تاثیر: برخی توابع DAX به دلیل ماهیت محاسباتی خود از سایرین زمان‌بر ترند. استفاده بیش از حد از توابع جدول، توابع مقایسه‌ای پیچیده، یا توابع ایجاد کننده جدول جدید می‌تواند به کاهش سرعت منجر شود. بعضی از توابع به طور inherent زمان‌بر تر هستند و در حجم بالای داده‌ها این موضوع بیشتر به چشم می‌آید.
• راهکارها: از توابع بهینه شده استفاده کنید، از تکرار محاسبات اجتناب کنید و در صورت امکان از توابع ساده تر و کارآمدتر به جای توابع پیچیده استفاده نمایید. اگر از یک تابع جدول استفاده می‌کنید، مطمئن شوید که تنها داده‌های ضروری را پردازش می‌کند و از فیلترها به درستی استفاده شده است. بعضی مواقع نوشتن کوئری به صورت مختلف می‌تواند در زمان پردازش بسیار تاثیرگذار باشد.

6. منابع سیستم (System Resources):

• تاثیر: قدرت پردازش CPU، حافظه RAM، و فضای دیسک سیستم می‌تواند در عملکرد DAX نقش داشته باشد. اگر سیستم به اندازه کافی قوی نباشد، پردازش کوئری‌های بزرگ و پیچیده می‌تواند به کندی یا حتی کَرش منجر شود.
• راهکارها: بهبود مشخصات سیستم مانند افزایش RAM یا استفاده از پردازنده قدرتمندتر می‌تواند به بهبود عملکرد کمک کند. همچنین باید به بسته شدن برنامه‌های غیر ضروری و رعایت بهینه‌سازی سیستم عملیاتی توجه شود.

درک و آگاهی از این عوامل و استفاده از راهکارهای پیشنهادی، به طور قابل توجهی در بهبود عملکرد کوئری‌های DAX و افزایش سرعت و راندمان گزارش‌ها و داشبوردهای Power BI موثر خواهد بود.

تکنیک‌های بهینه‌سازی عملکرد کوئری‌های DAX:

1. استفاده از فیلترهای موثر: فیلتر کردن داده‌ها پیش از انجام محاسبات، می‌تواند به طور قابل توجهی سرعت پردازش را افزایش دهد. از فیلترهای ROW-LEVEL به جای فیلترهای COLUMN-LEVEL استفاده کنید تا داده‌های کمتری پردازش شوند. محدود کردن دامنه داده‌ها قبل از اعمال محاسبات پیچیده، نقش موثری دارد.
2. استفاده از توابع بهینه شده: برخی توابع DAX به طور بهینه‌تری نوشته شده‌اند و سرعت پردازش بالاتری دارند. به عنوان مثال، SUMX به طور معمول از SUM کندتر است، مگر اینکه محاسبات اضافی در داخل SUMX ضروری باشند. استفاده از توابع مناسب و بهینه شده برای محاسبات، می‌تواند به بهبود عملکرد منجر شود.
3. اجتناب از استفاده بیش از حد از توابع جدول: توابع جدول مانند FILTER, SUMMARIZE, و GROUPBY می‌توانند مفید باشند اما بسیار پرهزینه هستند. اگر ممکن است، از توابع ساده‌تر و بهینه تر استفاده کنید. از ایجاد جدول‌های موقتی داخل کوئری‌ها خودداری کنید، زیرا این کار می‌تواند به افزایش زمان پردازش منجر شود.
4. بهینه سازی مدل داده: مدل داده‌ای خوب طراحی شده پایه و اساس عملکرد خوب است. مطمئن شوید که روابط بین جداول به درستی تعریف شده‌اند و شاخص‌های مناسب روی ستون‌های کلیدی وجود دارد. استفاده از مدل‌های ستونی (Columnstore Indexes) نیز می‌تواند عملکرد را بهبود دهد.
5. به کارگیری تکنیک‌های پیشرفته DAX: از تکنیک‌هایی مانند CALCULATE با فیلترهای مناسب، VAR برای ذخیره‌سازی محاسبات موقت، و استفاده از ITERATOR ها در محاسبات پیچیده استفاده کنید. استفاده از VAR می‌تواند خوانایی و عملکرد کوئری را بهبود دهد.
6. استفاده از DAX Profiler: DAX Profiler ابزاری برای تجزیه و تحلیل و شناسایی نقاط ضعف در کوئری‌های DAX است. با استفاده از این ابزار می‌توانید قسمت‌هایی از کوئری را که به بهینه‌سازی نیاز دارند، شناسایی کنید.
7. کاهش تعداد ستون‌های محاسباتی: در صورت عدم نیاز به تمام ستون‌های محاسباتی، از ایجاد ستون‌های اضافی خودداری کنید. ستون‌های محاسباتی زیاد می‌توانند عملکرد را کاهش دهند.
8. استفاده از حافظه بهینه: مدیریت حافظه در مدل داده‌ای بسیار مهم است. از توابع و محاسباتی که حافظه زیادی مصرف می‌کنند، خودداری کنید. به بهینه‌سازی حافظه در مدل داده توجه ویژه داشته باشید.
9. استفاده از تجزیه و تحلیل‌های پیشرفته: در مدل‌های داده‌ای بسیار بزرگ، می‌توانید از تکنیک‌هایی مانند تجزیه و تحلیل داده‌های نمونه (Sampling) استفاده کنید تا عملکرد را بهبود دهید.
10. بازنگری و سادگی کوئری‌ها: قبل از اعمال هرگونه بهینه‌سازی پیچیده، از سادگی و درستی کوئری اطمینان حاصل کنید. یک کوئری ساده و درست عموماً از یک کوئری پیچیده و ناکارآمد بهتر عمل می‌کند.

مثال‌های بهینه‌سازی:

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

کوئری ناکارآمد:

کوئری بهینه شده:

در این مثال، کوئری بهینه شده به سادگی از تابع AVERAGE استفاده می‌کند و به مراتب سریعتر از کوئری ناکارآمد که از AVERAGEX استفاده می‌کند، خواهد بود. AVERAGEX در این مورد نیازی ندارد و عملکرد را کاهش می‌دهد.

جمع‌بندی:

بهینه‌سازی عملکرد کوئری‌های DAX یک فرآیند تکراری و نیازمند تجربه و تمرین است. با استفاده از تکنیک‌های مختلف بهینه‌سازی و ابزارهایی مانند DAX Profiler، می‌توان به طور قابل توجهی عملکرد Power BI را بهبود داد و تجربه کاربری بهتری ارائه داد. به خاطر داشته باشید که هدف اصلی نوشتن کوئری‌های DAX، نه فقط دستیابی به نتیجه درست، بلکه انجام آن به طور کارآمد و سریع است. ترکیبی از طراحی خوب مدل داده‌ای و بهینه‌سازی کوئری‌ها، کلید رسیدن به عملکرد بهینه در Power BI است.

https://empathyfy.com/