بستیفای | مرجع فروش فایل های کاربردی در حوزه کسب و کار
منظور از توابع ممنوعه DAX چیست ؟
در واقع هیچ تابع یا Function برای استفاده در کد نویسی زبان DAX ممنوع نیستند ولی اینکه چگونه و برای چه حجم داده ای توابع رو استفاده کنید، تاثیر بسازی در عملکرد لودینگ و Performance گزارشات دارد.
به طور خلاصه، فهرستی از توابع DAX که ممکن است باعث کاهش عملکرد شوند به شرح زیر است :
- CALCULATE: زمانی که به شکل بیرویه استفاده شود یا چندین بار درون یکدیگر تو در تو شود، میتواند باعث کاهش عملکرد شود.
- EARLIER: این تابع هنگام استفاده در ستونهای محاسبهشده میتواند کند باشد چرا که به صورت سطر به سطر عمل میکند.
- RELATEDTABLE: این تابع کل جدول را بازیابی میکند. در صورتی که جدول بزرگ باشد، میتواند عملکرد را کاهش دهد.
- توابعی که عملیات سطر به سطر را اجبار میکنند: توابعی مانند SUMX، RANKX و FILTER که از جداول به صورت سطر به سطر استفاده میکنند و میتوانند در صورت استفادهی بیرویه باعث کاهش عملکرد شوند.
- ALL و ALLSELECTED: این توابع میتوانند کند باشند زمانی که بر روی جداول بزرگ استفاده شوند، چرا که تمام سطرها/ستونها را برمیگردانند.
- توابع متغیر: توابعی مانند TODAY() و NOW() که یک سنجه یا ستون را غیرقطعی میکنند و گاهی میتوانند باعث مشکلات عملکردی شوند.
همچنین برای بهینهسازی عملکرد DAX، طراحی مدل داده با استفاده از مدل “star schema”، کاهش استفاده از ستونهای محاسبهشده و شکستن سنجههای پیچیده به سنجههای واسطهای سادهتر است.
همیشه باید تست و پروفایل کنید تا بهترین عملکرد را کسب کنید.
در ادامه برای مواردی که ذکر کردیم به صورت جزئی تر بررسی خواهیم کرد که در چه صورت باعث Low Performance گزارشات می شوند.
()TODAY و ()NOW
توابع TODAY() و NOW() در DAX به ترتیب تاریخ فعلی و تاریخ و زمان فعلی را برمیگردانند. از نظر عملکرد، استفاده از این توابع میتواند موارد زیر را به همراه داشته باشد:
- غیر قطعیت: هر دفعه که یک measure یا calculated column با استفاده از این توابع مورد ارزیابی قرار میگیرد، مقدار تابع تغییر میکند (بسته به تاریخ و زمان فعلی). این موضوع میتواند باعث کاهش بهرهوری کش و افزایش بازآراییها شود.
- کاهش بهرهوری کش: در برخی موارد، استفاده از این توابع میتواند باعث شود تا نتایج قابل کشکردن نباشند. به همین دلیل، هر دفعه که یک پرس و جو اجرا میشود، ممکن است نیاز به محاسبات مجدد داشته باشد، که این مسأله میتواند به کاهش عملکرد منجر شود.
- موارد استفاده نادرست: در برخی موارد، توسعهدهندگان ممکن است از این توابع برای مواردی استفاده کنند که نیاز به آن ندارند، و به طور غیرلازم به پیچیدگی و کاهش عملکرد منجر میشوند.
البته این نکته مهم است که توابع TODAY() و NOW() خود به خود بد نیستند و در بسیاری از سناریوها مفید هستند. ولی مهم است که به نحو مناسب و با در نظر گرفتن تأثیرات بالقوه آنها بر عملکرد استفاده شوند.
ALL و ALLSELECTED
توابع ALL و ALLSELECTED در DAX از توابع مهم و پایهای هستند که برای مدیریت و تعیین محدوده فیلترها در مدلها استفاده میشوند. این توابع میتوانند در برخی شرایط به کاهش عملکرد منجر شوند، و در اینجا دلایل مختلفی را میتوان مطرح کرد:
- بازیابی دادههای زیاد: ALL تمام دادههای یک جدول یا ستون را بدون در نظر گرفتن فیلترها برمیگرداند. اگر جدول حاوی مقادیر زیادی باشد، این موضوع میتواند به کاهش عملکرد منجر شود، زیرا باید تمام دادههای جدول پردازش شود.
- عدم استفاده بهینه: اگر ALL یا ALLSELECTED به صورت نادرست یا غیرلازم در فرمولها استفاده شود، میتواند باعث پیچیدگی غیرضروری و کاهش عملکرد شود.
- ALLSELECTED و تأثیرات بر فیلترها: ALLSELECTED فیلترهای فعلی را با توجه به انتخابات کاربر در یک نمودار یا جدول بر میگرداند. در مواردی که با مقادیر بزرگ کار میکنید یا نیاز به تعاملهای پیچیده دارید، این موضوع میتواند باعث پیچیدگی و کاهش عملکرد شود.
- پیچیدگیهای ترکیبی: در برخی موارد، ترکیب ALL یا ALLSELECTED با توابع دیگر میتواند به پیچیدگیهای محاسباتی منجر شود که باید به صورت ویژه مدیریت شوند.
به طور کلی، همچنین ALL و ALLSELECTED توابع مفیدی هستند و در بسیاری از سناریوها از آنها استفاده میشود. ولی مانند هر ابزار دیگری در DAX، بهینهسازی و استفاده مناسب از آنها کلید به حفظ عملکرد خوب است.
توضیح با یک مثال میتواند بسیار کمککننده باشد. بیایید برای هر دو تابع یک مثال بزنیم:
- ALL:
فرض کنید ما یک جدول فروش داریم به نام Sales و میخواهیم سنجهای بسازیم که نسبت فروش جاری به کل فروش را محاسبه کند:
Sales Ratio = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)) )
در این فرمول، ما برای محاسبه کل فروش از تابع ALL استفاده کردهایم تا فیلترهای فعلی را حذف کنیم و مجموع کل فروش را بدست آوریم.
مشکل: اگر جدول Sales حاوی میلیونها رکورد باشد، استفاده از ALL باعث میشود که برای هر محاسبه، میلیونها رکورد بدون فیلتر مورد ارزیابی قرار بگیرند، که این میتواند به کاهش عملکرد منجر شود.
- ALLSELECTED:
فرض کنید در یک گزارش Power BI، یک نمودار میپایند که فروش را بر اساس کشور نشان میدهد. اگر کاربر برخی از کشورها را انتخاب کند، ما میخواهیم مجموع فروش انتخابشده را نسبت به کل فروش کشورهای نمایشدادهشده در نمودار محاسبه کنیم:
Selected Sales Ratio = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales[Country])) )
مشکل: اگر تعداد کشورها زیاد باشد و کاربر فقط چند کشور را انتخاب کند، ALLSELECTED باز هم همه کشورهای نمایشدادهشده در نمودار (و نه فقط انتخابشده) را برای محاسبه در نظر میگیرد. در صورتی که دادهها بسیار زیاد باشند، این میتواند منجر به کاهش عملکرد شود.
در هر دو مثال، از توابع ALL و ALLSELECTED به شکل مناسب و معقول استفاده شدهاست. ولی مشکلات عملکردی میتوانند در سناریوهایی با دادههای حجیم ایجاد شوند. در نتیجه، همیشه میبایست با دقت و با توجه به حجم دادهها از این توابع استفاده کنیم و در صورت لزوم بهینهسازیهای لازم را انجام دهیم.
راهکارهای جایگزین بستگی به نوع مسئله، سناریوی کاربردی و انتظارات عملکردی دارد. ولی در اینجا برخی از راهکارهای عمومی برای مواردی که ALL و ALLSELECTED ممکن است کاهش عملکرد ایجاد کنند، به شرح زیر است :
- استفاده از توابع فیلتر دیگر: در برخی موارد، توابعی مانند ALLEXCEPT میتوانند جایگزین مناسبی باشند. برای مثال، اگر میخواهید تمام فیلترها را حذف کنید به جز یک ستون خاص، ALLEXCEPT از نظر عملکردی بهتر میتواند باشد.
- محدود کردن حجم داده: در مواردی که میخواهید از ALL استفاده کنید، اگر بتوانید با استفاده از فیلترهای دیگر محدوده دادهها را کاهش دهید، میتواند کمک کند. برای مثال، به جای استفاده از ALL(Sales)، میتوانید از ALL(Sales[Year]) استفاده کنید اگر فقط به دادههای یک سال خاص نیاز دارید.
- استفاده از measure از پیش محاسبه شده: در برخی موارد، میتوانید نتایج مورد نظر را در measure جداگانه محاسبه کنید و سپس از آنها برای محاسبات نهایی استفاده کنید. این کمک میکند تا محاسبات پرهزینه در زمان اجرای پرس و جوهای مختلف اجتناب شود.
- بهینهسازی مدل داده: اطمینان حاصل کنید که مدل داده شما بهینه و کارآمد است. استفاده از ستونهای فهرست و بهینهسازی روابط میتواند در افزایش عملکرد بسیار موثر باشد.
- از نظرگیری و بررسی عملکرد استفاده کنید: ابزارهایی مانند Performance Analyzer در Power BI را استفاده کنید تا فهمیده شود کدام بخشها از مدل یا پرس و جوها دچار مشکلات عملکردی هستند و نیاز به بهینهسازی دارند.
EARLIER
تابع EARLIER در DAX یک تابع است که به شما امکان میدهد به مقدار پیشین یک ستون در همان جدول، طی یک محاسبه ایجاد شده، دسترسی پیدا کنید. این تابع در مواردی مانند محاسبات سطر به سطر در جداول با استفاده از توابع مانند FILTER یا در حالتهایی که یک محاسبه بر اساس گروهبندی خاص انجام میشود، مفید است.
وقتی از EARLIER استفاده میکنید، عملکرد ممکن است کم شود چرا که برای هر سطر، DAX باید به سطرهای پیشین مراجعه کند، که این موضوع در جداول با تعداد زیادی رکورد میتواند زمانبر باشد.
مثال: فرض کنید شما یک جدول فروش دارید و میخواهید برای هر سطر، مجموع فروش قبل از تاریخ آن سطر را محاسبه کنید:
Cumulative Sales Until Date = SUMX( FILTER( Sales, Sales[Date] < EARLIER(Sales[Date]) ), Sales[Amount] )
در این مثال، برای هر سطر از جدول Sales، ما از FILTER استفاده میکنیم تا سطرهایی را که دارای تاریخ قبل از تاریخ سطر جاری هستند، انتخاب کنیم. سپس، با استفاده از SUMX، مجموع فروش را برای این سطرهای انتخاب شده محاسبه میکنیم. تابع EARLIER در اینجا به ما امکان میدهد تا به تاریخ سطر جاری دسترسی پیدا کنیم.
اگرچه این مثال در موارد کمی مفید است، ولی در جداول با تعداد زیادی رکورد، این نوع محاسبات میتواند زمانبر باشد و به کاهش عملکرد منجر شود، زیرا برای هر سطر، ممکن است هزاران یا حتی میلیونها سطر دیگر مورد بررسی قرار گیرند.
برای جلوگیری از کاهش عملکرد با استفاده از تابع EARLIER، معمولاً میتوانید با استفاده از ترکیب توابع و فنیکهای DAX دیگر، محاسبات خود را بهینهتر انجام دهید. در مثالی که ارائه شد (محاسبه فروش تجمعی تا یک تاریخ معین)، یکی از روشهای معمول برای محاسبه فروش تجمعی استفاده از تابع CALCULATE و تابع فیلتر FILTER است، ولی به صورت متفاوت:
Cumulative Sales = CALCULATE( SUM(Sales[Amount]), FILTER( ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date]) ) )
در اینجا، ما از تابع MAX(Sales[Date]) استفاده میکنیم تا تاریخ جاری را دریافت کنیم به جای استفاده از EARLIER.
از طرفی، اگر شما با جداول زمان (DimDate) کار میکنید (که در بسیاری از مدلهای Power BI معمول است)، میتوانید فروش تجمعی را به طور کارآمدتر با استفاده از روابط بین جدول فروش و جدول تاریخ محاسبه کنید.
نکته کلیدی در اینجا این است که با درک بهتر از امکانات و توابع DAX و نحوه کار با آنها، میتوانید راهحلهای جایگزینی پیدا کنید که از نظر عملکردی بهتر و کارآمدتر باشند. همچنین، تست و سنجش عملکرد قبل و بعد از اعمال تغییرات، برای اطمینان از بهینهسازیهای انجام شده، ضروری است.
RELATEDTABLE
تابع RELATEDTABLE در DAX برای بازیابی یک جدول مرتبط با یک سطر از جدول دیگر استفاده میشود. این تابع معمولاً وقتی مورد استفاده قرار میگیرد که شما به تمام سطرهای مرتبط در یک جدول دیگر با توجه به یک سطر خاص در جدول جاری نیاز دارید.
اما اگر نادرست استفاده شود، RELATEDTABLE میتواند منجر به مشکلات عملکردی شود، به ویژه وقتی جدول مرتبط حاوی تعداد زیادی سطر است.
مثال:
فرض کنید دو جدول داریم: جدول Customers و جدول Orders. هر مشتری ممکن است چندین سفارش داشته باشد. حال فرض کنید میخواهید برای هر مشتری، مجموع قیمت تمام سفارشاتی که داده است را محاسبه کنید.
اگر از RELATEDTABLE استفاده کنید به این صورت:
Total Orders Amount = SUMX( RELATEDTABLE(Orders), Orders[OrderAmount] )
در این مثال، برای هر مشتری، RELATEDTABLE تمام سطرهای مرتبط در جدول Orders را بازیابی میکند و سپس با استفاده از SUMX مجموع قیمت آنها را محاسبه میکند. اگر جدول Orders حاوی تعداد زیادی سطر باشد، این محاسبه میتواند زمانبر و ناکارآمد باشد.
یکی از بهترین راهها برای محاسبههای مشابه استفاده از توابع انباشتی DAX است. در این مثال، به جای استفاده از RELATEDTABLE و SUMX، میتوانید از تابع RELATED و یک سنجه جداگانه در جدول Orders استفاده کنید:
ایجاد یک سنجه در جدول Orders:
OrderAmountSum = SUM(Orders[OrderAmount])
استفاده از سنجه ایجاد شده در جدول Customers:
Total Orders Amount = RELATED(Orders[OrderAmountSum])
با این روش، محاسبات بهینهتر و کارآمدتر انجام میشوند، به خصوص وقتی با دادههای بزرگ کار میکنید.
RANKX
تابع RANKX در DAX یکی از توابع پرکاربرد است که برای محاسبه رتبه یک مقدار در یک جدول یا مجموعه مورد استفاده قرار میگیرد. اگرچه این تابع بسیار قدرتمند و مفید است، اما در برخی موارد ممکن است منجر به مشکلات عملکردی شود، به ویژه در موارد زیر:
- جداول بزرگ: اگر از RANKX بر روی یک جدول با تعداد زیادی سطر استفاده کنید، این تابع باید برای هر سطر جدول مقدار رتبه را محاسبه کند، که میتواند زمانبر باشد.
- استفاده از توابع پیچیده به عنوان ورودی: اگر در ورودی RANKX از توابع محاسباتی پیچیده استفاده کنید، محاسبات ممکن است به طور قابل توجهی کند شود.
مثال: فرض کنید میخواهید رتبه فروش کل هر محصول را در جدول محصولات محاسبه کنید:
Product Rank = RANKX( ALL(Products), CALCULATE(SUM(Sales[Amount])) )
در این مثال، RANKX برای هر محصول، مجموع فروش را با استفاده از CALCULATE محاسبه میکند و سپس رتبه آن را در میان تمام محصولات تعیین میکند. اگر جدول محصولات یا فروش حاوی تعداد زیادی سطر باشد، این محاسبه میتواند زمانبر باشد.
برای بهینهسازی RANKX:
- پیشپردازش دادهها: در برخی موارد، میتوانید با استفاده از مراحل پیشپردازش در Power Query یا سایر ابزارها، دادهها را از قبل مرتب کنید.
- استفاده از فیلترها: محدود کردن تعداد سطرهای مورد بررسی با استفاده از فیلترها میتواند کمک کند به تسریع محاسبات.
- استفاده از جداول محاسباتی: اگر ممکن است، یک جدول محاسباتی ایجاد کنید که مقادیر مورد نیاز برای محاسبه رتبه را دارد و سپس بر اساس آن جدول محاسباتی رتبه را محاسبه کنید.
CALCULATE
تابع CALCULATE یکی از قدرتمندترین و پرکاربردترین توابع در DAX است. این تابع امکان محاسبات در یک محیط فیلتر مختلف را فراهم میکند. با این حال، استفاده نادرست یا بیاحتیاط از این تابع میتواند به مشکلات عملکردی منجر شود. در زیر چند دلیل برای این موضوع آورده شدهاند:
- تغییر محیط فیلتر بدون نیاز: اگر بدون دلیل خاصی از CALCULATE استفاده کنید تا فیلترها را تغییر دهید، ممکن است منجر به محاسبات غیرضروری یا تکراری شود.
- استفاده از توابع سنگین در داخل CALCULATE: استفاده از توابع محاسباتی پیچیده یا زمانبر در داخل CALCULATE میتواند به کندی محاسبات منجر شود.
- تداخل با فیلترهای دیگر: در برخی موارد، استفاده از CALCULATE ممکن است فیلترهای موجود را بازنویسی کند یا با آنها تداخل داشته باشد، که ممکن است نتایج غیرمنتظره یا محاسبات بیمعنی ایجاد کند.
- استفاده از فیلترهای ALL: استفاده از توابع مانند ALL یا ALLSELECTED در داخل CALCULATE برای حذف فیلترها، ممکن است بر محدودیتهای مورد نظر شما تأثیر بگذارد و منجر به محاسبات بیشتر شود.
مثال: فرض کنید میخواهید میانگین فروش کل را در میان تمام محصولات محاسبه کنید، حتی اگر فیلتری روی محصولات اعمال شده باشد:
Average Total Sales = CALCULATE( AVERAGE(Sales[Amount]), ALL(Products) )
در این مثال، با استفاده از CALCULATE و ALL, ما همه فیلترها روی جدول محصولات را حذف میکنیم تا میانگین فروش کل را محاسبه کنیم. اگر محصولات زیادی داشته باشید، این محاسبه میتواند زمانبر باشد.
بهتر است همیشه قبل از استفاده از CALCULATE، نیاز واقعی خود را بررسی کنید. آیا واقعاً نیاز به تغییر محیط فیلتر دارید؟ آیا میتوانید با فیلترهای سادهتر یا با تغییر نحوه نمایش دادهها نتیجه مورد نظر را بدست آورید؟
بیایید با یک مثال ساده این موضوع را بررسی کنیم.
مثال : فرض کنید میخواهید مجموع فروش یک محصول خاص در یک بازه زمانی خاص را محاسبه کنید، حتی اگر فیلترهای دیگری روی دادهها اعمال شده باشد.
راه حل با استفاده از CALCULATE:
Total Sales For Product A = CALCULATE( SUM(Sales[Amount]), Products[ProductName] = "Product A", Dates[Date] >= DATE(2023,1,1), Dates[Date] <= DATE(2023,12,31) )
در اینجا با استفاده از CALCULATE, ما فیلتر محصول و بازه زمانی را اعمال میکنیم، حتی اگر فیلترهای دیگری روی جدول اعمال شده باشد.
راه حل جایگزین با استفاده از فیلترها در جدول: میتوانید به جای استفاده از CALCULATE, فیلترها را مستقیماً در جدول نمایش (مانند جدول یا چارت) اعمال کنید. برای این منظور:
- فیلتر محصول را در قسمت فیلترهای نمایش اعمال کنید و “Product A” را انتخاب کنید.
- با استفاده از فیلتر بازه زمانی، تاریخهای مورد نظر (از 1 ژانویه 2023 تا 31 دسامبر 2023) را انتخاب کنید.
سپس فرمول را به شکل سادهتری بنویسید:
Total Sales = SUM(Sales[Amount])
در بسیاری از موارد، راه حل دوم با استفاده از فیلترهای نمایش میتواند به یک عملکرد بهتر منجر شود، زیرا تغییر محیط فیلتر به صورت دستی میشود و به جای تغییر مکرر محیط فیلتر با CALCULATE, فقط یک بار فیلترها اعمال میشوند.
لطفا برای ما از تجربه بهبود کدهای Dax برای بهبود Performance بنویسید!
«بستیفای» ارائه فایلهای کاربردی در زمینه های مختلف کسب و کار است. در صورتی که نیاز به سفارشی سازی یا طراحی فایل اختصاصی جدید میباشید، میتوانید نسبت به ارائه درخواست با بستیفای در ارتباط باشید.
