پیدا کردن محصولاتی که فروش نداشته‌اند با استفاده از DAX
این مقاله عملکرد تکنیک‌های مختلف DAX را برای شناسایی محصولاتی که در یک منطقه یا بازه زمانی خاص هیچ فروشی نداشته‌اند، مورد بررسی قرار می‌دهد.
چه محصولاتی در یک منطقه، فروشگاه یا بازه زمانی خاص فروشی نداشته‌اند؟ این می‌تواند یک تحلیل مهم برای بسیاری از کسب‌وکارها باشد. راه‌های مختلفی برای رسیدن به این هدف وجود دارد. گاهی ممکن است پیاده‌سازی خاصی به خاطر نیازهای کاربران یا مدل داده لازم باشد، در حالی که در برخی موارد توسعه‌دهنده‌ها می‌توانند از بین چند فرمول مختلف یکی را انتخاب کنند. یا ممکن است صرفاً یک راه‌حل را در وب پیدا کنید و بدون بررسی اینکه آیا روش بهتری برای دستیابی به هدف وجود دارد یا نه، آن را کورکورانه اجرا کنید.
واقعیت این است که فرمول‌های مختلف عملکرد بسیار متفاوتی دارند. انتخاب فرمول درست در سناریوی شما می‌تواند گزارشی کند و کند را سریع کند. این مقاله عملکرد فرمول‌های مختلفی را که همگی در اصل یک الگوریتم مشابه را پیاده‌سازی می‌کنند، بررسی می‌کند. برخی از این فرمول‌ها ساده و برخی دیگر پیچیده‌تر هستند. نکته اصلی مقاله این نیست که کدام فرمول سریع‌تر اجرا می‌شود، بلکه این است که چگونه عملکرد معیارهای (measures) خود را اندازه‌گیری کنیم و اهمیت انجام تحلیل عملکرد پیش از به‌کارگیری نهایی معیار در محیط تولیدی چقدر است.
ما به یک معیار (measure) نیاز داریم که بررسی کند آیا یک محصول فروش نداشته است یا نه. اولین پیاده‌سازی که به ذهن می‌رسد این است که از معیار «مقدار فروش» (Sales Amount) استفاده کنیم و مقدار آن را با صفر مقایسه کنیم:
گزارش نهایی محصولاتی را نمایش می‌دهد که – در یک ماه مشخص – هیچ فروشی نداشته‌اند.
ما از Contoso 100M استفاده می‌کنیم، که نسخه‌ای از دیتابیس Contoso با حدود ۲۰۰ میلیون ردیف در جدول Sales است. اگر شما کوئری‌ها را با فایل دموی قابل دانلود (که فقط چند هزار ردیف در جدول Sales دارد) تست کنید، ممکن است نتایج متفاوتی بگیرید، اما ما به یک پایگاه داده‌ی بزرگ نیاز داشتیم تا تست‌های مربوط به عملکرد را اجرا کنیم.
قبل از اینکه این اولین راه‌حل را به عنوان راه‌حل نهایی بپذیریم، می‌خواهیم فرمول‌های دیگری را هم برای رسیدن به همین نتیجه بررسی کنیم. ما در مجموع شش راه‌حل مختلف داریم. ممکن است شما خلاق‌تر باشید و راه‌حل‌های بیشتری پیدا کنید، اما ما در عدد شش متوقف شدیم.
در ادامه، کوئری کامل شامل این شش معیار (Measure) را می‌بینید. ما این کوئری را با تغییر تنها یک خط در تابع SUMMARIZECOLUMNS که به معیار مورد آزمایش اشاره دارد، اجرا می‌کنیم:
چند نکته درباره‌ی معیارهای مختلف (Measures):
همه‌ی این معیارها با عبارت HasNoSales شروع می‌شن و بعدش یک پسوند (suffix) دارن که نوع یا روش خاص اون معیار رو مشخص می‌کنه:
• Sales Amount: اولین تست ما بررسی می‌کند که آیا مقدار Sales Amount برابر صفر است یا نه.
• COUNTROWS: به‌جای محاسبه‌ی Sales Amount، تعداد ردیف‌های جدول Sales را می‌شمارد تا بررسی کند که آیا ردیفی در زمینه فیلتر (Filter Context) وجود ندارد.
• ISEMPTY: مشابه COUNTROWS عمل می‌کند، ولی از تابع ISEMPTY استفاده می‌شود تا نبود ردیف‌ها بررسی شود.
• INTERSECT: اشتراک بین کلیدهای محصول در جدول Sales و جدول Product را بررسی می‌کند تا ببیند که هیچ ردیف مشترکی وجود ندارد.
• EXCEPT: مشابه INTERSECT است، اما از تابع EXCEPT استفاده می‌کند تا بررسی کند که آیا ردیف‌هایی در Product وجود دارند که در Sales مرجع داده نشده‌اند.
• SELECTEDVALUE: از تابع SELECTEDVALUE و عملگر IN استفاده می‌کند تا بررسی کند که آیا محصول انتخاب‌شده فعلی در بین محصولات فروخته‌شده نیست.
همانطور که می‌بینید، حتی برای یک معیار ساده مثل HasNoSales چندین پیاده‌سازی مختلف وجود دارد. انتخاب بهترین روش، نیاز به تست‌های گسترده دارد. ما الگوریتم هر معیار را بررسی کرده و نکاتی را در مورد آن‌ها مطرح خواهیم کرد.
تست معیار HasNoSales با استفاده از Sales Amount
اولین معیار فقط بررسی می‌کند که آیا مقدار Sales Amount برابر صفر است یا نه:
پنل Server Timings (زمان‌بندی‌های سرور) سه کوئری مختلف VertiPaq را نمایش می‌دهد.
(VertiPaq یک موتور ذخیره‌سازی ستونی (columnar storage engine) در Power BI و Analysis Services است که کوئری‌ها را به شکل بهینه اجرا می‌کند. مشاهده‌ی چند کوئری متفاوت در پنل Server Timings به این معناست که اجرای این Measure (معیار) به شکلی انجام شده که نیاز به چندین کوئری مجزا به مدل داده‌ها داشته است.)
دو کوئری اول ساده و پیش‌پا افتاده هستند؛ آن‌ها فقط نام محصولات و ترکیب‌های سال و شماره ماه را بازیابی می‌کنند. این کوئری‌ها آنقدر سریع اجرا می‌شوند که ارزش تحلیل کردن ندارند.
اما کوئری سوم یعنی xmSQL query، جایی‌ست که بخش اصلی کار انجام می‌شود:
(💡در Power BI، کوئری‌های xmSQL زبان میانی‌ای هستند که بین فرمول‌های DAX و موتور VertiPaq ارتباط برقرار می‌کنند. وقتی می‌خواهیم عملکرد دقیق یک Measure را بررسی کنیم، بررسی این کوئری‌ها بسیار اهمیت دارد چون مشخص می‌کنند موتور چطور داده‌ها را فیلتر و جمع‌آوری می‌کند.)
این کوئری پیاده‌سازی کامل SUMMARIZECOLUMNS است، چرا که مقدار فروش را برای تمام ترکیب‌های سال، ماه و نام محصول محاسبه می‌کند. پس از اینکه موتور ذخیره‌سازی نتیجه را محاسبه کرد، موتور فرمول‌ها صفرها را حذف کرده و نتیجه را برمی‌گرداند.
چون ما در حال ارزیابی عملکرد هستیم، دو عدد مهم که باید بررسی شوند SE CPU (2,766 میلی‌ثانیه) و FE (296 میلی‌ثانیه) هستند. زمان اجرای کل به‌شدت تحت تأثیر موازی‌سازی قرار می‌گیرد، بنابراین تنها نگاه کردن به زمان کل اجرا کافی نیست، زیرا این می‌تواند شاخص ضعیفی برای کارایی باشد.
آزمایش HasNoSales با استفاده از COUNTROWS
اندازه‌گیری دوم از الگوریتم متفاوتی استفاده می‌کند. فرض می‌کنیم که فقط بررسی حضور ردیف‌ها در جدول Sales کافی است تا تشخیص دهیم آیا محصولی فروش داشته است یا نه. این الگوریتم با شمارش تعداد ردیف‌های موجود در جدول Sales و بررسی اینکه آیا تعداد ردیف‌ها صفر است، پیاده‌سازی می‌شود:
این فرضیه زمانی معتبر است که جدول Sales فقط شامل تراکنش‌های فروش باشد. به عنوان مثال، اگر جدول Sales همچنین شامل بازگشت‌ها (با مقادیر منفی) باشد، این نسخه دوم گزینه مناسبی نخواهد بود. با این حال، اگر فرضیه درست باشد، انتظار داریم که این اندازه‌گیری دوم سریع‌تر باشد، زیرا نیازی به ضرب تعداد کالا در قیمت خالص ندارد.
در واقع، این نسخه دوم سریع‌تر اجرا می‌شود.
تفاوت اصلی در بخش CPU موتور ذخیره‌سازی است: زمان از ۲,۷۶۶ میلی‌ثانیه به ۱,۲۰۳ میلی‌ثانیه کاهش یافته است، که کمتر از نصف زمان قبلی است. دو کوئری اول همانند کوئری‌های قبلی هستند؛ اما کوئری سوم xmSQL نشان می‌دهد که این بار مقدار Sales Amount محاسبه نمی‌شود:
هنوز جا برای بهبود وجود دارد. برای بررسی صفر با استفاده از COUNTROWS، موتور DAX باید تعداد تراکنش‌های فروش را برای هر ترکیب سال، ماه و محصول موجود در گزارش محاسبه کند. تنها هدف این معیار مقایسه آن تعداد با صفر است تا نتیجه True یا False بازگرداند. تابع ISEMPTY در DAX به طور ویژه برای بررسی حضور ردیف‌ها در یک جدول بهینه‌سازی شده است. ISEMPTY تعداد ردیف‌ها را محاسبه نمی‌کند؛ حضور حداقل یک ردیف به این معنی است که ISEMPTY مقدار FALSE را برمی‌گرداند. ما از این رویکرد در نسخه‌ی بعدی معیار استفاده می‌کنیم.
تست HasNoSales ISEMPTY
سومین معیار از همان الگوریتمی استفاده می‌کند که معیار دوم، اما این بار از ISEMPTY برای بررسی حضور ردیف‌ها در جدول فروش استفاده می‌کند:
این معیار همان فرضیات قبلی را دارد. ما امیدواریم که ISEMPTY یک برنامه اجرایی بهتر تولید کند. متاسفانه، اینطور نیست.
تفاوت از نظر سرعت بی‌اهمیت است. یک تفاوت کوچک در کوئری‌های xmSQL وجود دارد، اما این تفاوت کافی نیست که سرعت کلی را بهبود بخشد. این سومین کوئری xmSQL نشان می‌دهد که موتور محاسباتی تعداد ردیف‌ها را حساب نمی‌کند زیرا فقط کافی است بررسی کند که آیا ردیفی وجود دارد یا خیر.
تعداد ردیف‌ها از xmSQL حذف شد. به دلیل نحوه فشرده‌سازی داده‌ها در VertiPaq، شمارش ردیف‌ها در یک جدول بسیار سریع است. آنقدر سریع است که تفاوت قابل توجهی بین این که شمارش انجام شود یا نه وجود ندارد. با این حال، با توزیع داده‌ای متفاوت یا نوع دیگری از فشرده‌سازی، زمان برای شمارش تعداد ردیف‌ها ممکن است مهم باشد. بنابراین، ما این نسخه سوم را نسبت به نسخه‌های قبلی ترجیح می‌دهیم زیرا پیاده‌سازی آن یک طرح کوئری ساده‌تر تولید می‌کند.
سه نسخه آخر measure از الگوریتم متفاوتی استفاده می‌کنند. ایده این است که از توابع مجموعه‌ای در DAX استفاده کنیم تا بررسی کنیم که آیا آن‌ها بهتر یا بدتر از توابع پایه‌ای که قبلاً استفاده کرده‌ایم عمل می‌کنند. همانطور که به زودی متوجه خواهیم شد، این توابع تقریباً همیشه عملکرد بدتری دارند. با این حال، همیشه باید عملکرد پیاده‌سازی‌های مختلف را قبل از تصمیم‌گیری تست کنیم.
آزمایش HasNoSales INTERSECT
نسخه چهارم از الگوریتم متفاوتی استفاده می‌کند. ما قبلاً می‌دانیم که ISEMPTY یک تابع خوب است، بنابراین از آن استفاده می‌کنیم تا بررسی کنیم آیا مجموعه محصولات انتخاب شده با مجموعه محصولات فروخته شده یک مجموعه خالی تولید می‌کند یا خیر. اگر محصول فروش داشته باشد، در VALUES(Product[ProductKey]) گنجانده می‌شود و در نتیجه وقتی با VALUES(Sales[ProductKey]) تقاطع می‌کند، بخشی از نتیجه خواهد بود. ISEMPTY بررسی می‌کند که آیا نتیجه‌ی INTERSECT ردیفی دارد یا خیر.
ما می‌دانیم که موتور فرمول در DAX توابع مجموعه‌ای را محاسبه می‌کند. با این حال، ممکن است اینطور به نظر برسد که طرح کوئری بهبودهایی را نشان دهد. متاسفانه، اینطور نیست.
زمان CPU موتور ذخیره‌سازی به طور قابل توجهی افزایش یافته و اکنون چهار کوئری xmSQL به جای سه کوئری قبلی وجود دارد. دو کوئری اول هنوز محصولات و ماه‌ها را بازیابی می‌کنند. کوئری سوم جالب است:
این کوئری ارتباط بین کلیدهای محصول و نام‌های محصول را بازیابی می‌کند. در واقع، کوئری DAX بر اساس Product[Product Name] گروه‌بندی می‌کند، اما این معیار به درستی مجموعه‌ای از کلیدهای محصول را بازیابی می‌کند. بنابراین، موتور به نقشه‌برداری از نام‌های محصول به کلیدهای محصول می‌پردازد که باعث افزایش پیچیدگی در طرح کوئری می‌شود.
کوئری آخر بسیار شبیه به نسخه سوم است، با این تفاوت که پیچیدگی بیشتری دارد چرا که ترکیب‌های سال، ماه، نام محصول و کلید محصول را بازیابی می‌کند، در حالی که نسخه‌ی استفاده شده در HasNoSales ISEMPTY به کلید محصول نیازی نداشت.
این تفاوت کوچک کافی است تا زمان اجرای کل کوئری افزایش یابد. طرح کوئری موتور فرمول به نسخه قبلی بسیار نزدیک است.
از تحلیل‌ها، مشخص می‌شود که مشکل با توابع مجموعه‌ای این است که ما مجبور بودیم به ستون Product[ProductKey] در کد اشاره کنیم. در سه فرمول قبلی، کد DAX معیارها به ستونی خاص وابسته نبود. بنابراین، بهینه‌ساز کوئری‌های موتور ذخیره‌سازی را تنها با استفاده از ستون‌های موجود در SUMMARIZECOLUMNS ایجاد می‌کند. توابع مجموعه پیچیدگی را اضافه می‌کنند چرا که مجبور می‌کنند موتور کلیدهای محصول را بازیابی کند. به‌طور غیرمحتملی، SUMMARIZECOLUMNS در کوئری از ProductKey استفاده می‌کند (کاربران باید از این ستون در نمایش‌های خود استفاده کنند). بنابراین، به هر حال، معیارهای مبتنی بر توابع مجموعه‌ای به طور معمول کندتر خواهند بود.
آزمایش HasNoSales با استفاده از EXCEPT
پنجمین معیار یک تغییر جزئی از نسخه قبلی است. تفاوت تنها در استفاده از EXCEPT به جای INTERSECT است که نیاز به منفی کردن ISEMPTY دارد.
به جز تابعی که برای پردازش نتایج دو تابع VALUES استفاده می‌شود، این فرمول تقریباً مشابه نسخه قبلی است. به همین دلیل، انتظار داریم که سطح عملکرد مشابهی را مشاهده کنیم.
آخرین معیار برای تجزیه و تحلیل از اپراتور IN استفاده می‌کند به جای یک تابع مجموعه. این فرض را دارد که پرس و جوی خارجی فقط نتیجه را برای یک محصول محاسبه می‌کند — به این معنی که SUMMARIZECOLUMNS خارجی در سطح محصول گروه‌بندی می‌شود:
با وجود اینکه این نسخه به نظر شبیه به نسخه‌های قبلی است، عملکرد آن بسیار ضعیف است.
زمان مصرف شده در CPU موتور ذخیره‌سازی به شدت به ۴۵،۶۷۲ میلی‌ثانیه افزایش یافت. دلیل این امر این است که حالا کوئری xmSQL اصلی شامل یک کال‌بک است.
این معیار آنقدر کند است که بررسی بیشتر آن ارزش ندارد.
حال زمان آن رسیده که نتیجه‌گیری کنیم. یافته‌های خود را در یک جدول خلاصه کرده‌ایم که فرمول مورد علاقه‌مان را برجسته می‌کند:
فرمول HasNoSales ISEMPTY کمی کندتر از HasNoSales COUNTROWS به نظر می‌رسد. با این حال، باید توجه داشت که این تفاوت بی‌اهمیت است زیرا بسیار کمتر از تغییرات استاندارد بین اجرای‌های مختلف است. نسخه‌ی ISEMPTY یک پرس‌وجوی xmSQL ساده‌تر از نسخه‌ی COUNTROWS را نشان می‌دهد، که این توضیح‌دهنده انتخاب ماست.
درس مهمی که از این مقاله باید بگیریم این است که ISEMPTY بهترین گزینه نیست. نکته‌ی اصلی این است که قبل از انتخاب یک الگوریتم نسبت به دیگری، باید آزمایش‌های گسترده‌ای انجام دهید. نسبت بین بهترین و بدترین گزینه‌ها در مجموعه الگوریتم‌های ما حدود 40 است، به این معنی که اگر ما گزینه بدترین را به طور کورکورانه انتخاب کنیم، ممکن است از 40 برابر بیشتر از قدرت پردازش CPU استفاده کنیم که در صورت انتخاب بهترین گزینه، استفاده می‌کردیم.
علاوه بر این، این‌ها یافته‌های ما در پایگاه داده دمو هستند. ما تعجب نمی‌کنیم اگر شما نتایج متفاوتی را در مدل خود پیدا کنید به دلیل اندازه، توزیع داده، سطح فشرده‌سازی و غیره. وقتی عملکرد بحرانی است، آماده باشید تا قبل از اجرای یک معیار در تولید، آزمایش‌های گسترده‌ای انجام دهید، حتی اگر آن معیار به نظر ساده بیاید.
دیدگاهتان را بنویسید