صفر تا صد JOIN در DAX
استفاده از Relationships در Data Model
رویکرد رایج برای به دست آوردن رفتار JOIN در DAX به طور ضمنی از Relationships موجود استفاده می کند. به عنوان مثال، یک مدل ساده با جداول فروش، محصول و تاریخ در نظر بگیرید. بین فروش و هر یک از سه جدول دیگر Relation وجود دارد. اگر می خواهید تعداد فروش تقسیم بر سال و رنگ محصول را ببینید، می توانید بنویسید:
EVALUATE
ADDCOLUMNS )
SUMMARIZE )
Sales,
‘Date'[Year],
Product[Color]
(,
“Total Quantity”, CALCULATE ) SUM ( Sales[Quantity] ) )
(
سه جدول به طور خودکار با استفاده از یک JOIN چپ بین جدول فروش (که در عبارت ستون مقدار کل استفاده می شود) و دو جدول دیگر، تاریخ و محصول، به یکدیگر متصل می شوند.
| SELECT d.Year, p.Color, SUM ( s.Quantity ) AS [Total Quantity] FROM Sales s LEFT JOIN Date d ON d.DateKey = s.DateKey LEFT JOIN Product p ON p.ProductKey = s.ProductKey GROUP BY d.Year, p.Color |
لطفاً توجه داشته باشید که جهت LEFT JOIN بین فروش و تاریخ است، بنابراین تمام ردیف های موجود در جدول فروش که ردیف متناظری در تاریخ یا محصول ندارند در یک مقدار خالی (که با مفهوم مطابقت دارد ) گروه بندی می شوند. NULL در SQL). اگر نمی‌خواهید ردیف‌ها را جمع کنید، می‌توانید به سادگی از RELATED برای دسترسی به ستون‌ها در جداول جستجو استفاده کنید – در سمت “یک” Relation. به عنوان مثال، نحو زیر را در SQL در نظر بگیرید:
SELECT
s.*, d.Year, p.Color
FROM
Sales s
LEFT JOIN Date d ON d.DateKey = s.DateKey
LEFT JOIN Product p ON p.ProductKey = s.ProductKey
شما با استفاده از پرس و جوی DAX زیر همین رفتار را به دست می آورید:
EVALUATE ADDCOLUMNS Sales, “Year”, RELATED ( ‘Date'[Year] ), “Color”, RELATED ( Product[Color] ) |
ممکن است با اعمال یک فیلتر بر روی نتیجه ADDCOLUMNS که تاکنون دیده‌اید، رفتاری شبیه به INNER JOIN به دست آورید و ردیف‌هایی را که دارای مقدار خالی در جدول جستجو هستند حذف کنید – با این فرض که خالی مقداری نیست که شما ممکن است داشته باشید. در داده های آن ستون شما نمی توانید رفتار CROSS JOIN را در DAX فقط با استفاده از Relationships در Data Model بدست آورید.
زبان SQL انواع زیر را از JOIN ارائه می دهد:
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
نتیجه یک JOIN به وجود Relation در Data Model بستگی ندارد. می توانید از هر ستون جدول در شرایط JOIN استفاده کنید.در DAX دو راه وجود دارد که می توانید رفتار JOIN را بدست آورید. ابتدا، می‌توانید از Relationships موجود در Data Model استفاده کنید تا داده‌های موجود در جداول مختلف را پرس و جو کنید، درست همانطور که شرایط JOIN مربوطه را در کوئری DAX نوشتید. دوم، می توانید عبارات DAX را بنویسید که نتیجه ای معادل انواع خاصی از JOIN ایجاد کند. در هر صورت، همه عملیات JOIN موجود در SQL در DAX پشتیبانی نمی شوند.می‌توانید نمونه‌های نشان‌داده‌شده در این مقاله را با استفاده از DAX Studio برای اجرای کوئری‌های DAX آزمایش کنید.
استفاده از NATURALLEFTOUTERJOIN و NATURALINNERJOIN با Relationships
این دستورات را در SQL در نظر بگیرید:
SELECT * FROM a LEFT OUTER JOIN b ON a.key = b.key SELECT * FROM a INNER JOIN b ON a.key = b.key |
اگر Relation ای وجود داشته باشد که دو جدول درگیر را به هم وصل می کند، به ترتیب با استفاده از توابع NATURALLEFTOUTERJOIN و NATURALINNERJOIN ، می توانید سینتکس های معادل را در DAX بنویسید .
بیشتر بخوانید :آموزش زبان Dax
JOIN به جداول بدون Relation در DAX
با استفاده از CROSSJOIN
این نحو را در SQL در نظر بگیرید:
| SELECT * FROM a CROSS JOIN b |
با استفاده از تابع CROSSJOIN می توانید یک نحو معادل در DAX بنویسید :
| EVALUATE CROSSJOIN ( a, b ) |
استفاده از NATURALLEFTOUTERJOIN و NATURALINNERJOIN بدون Relation
توابع NATURALLEFTOUTERJOIN و NATURALINNERJOIN می توانند جداولی را که هیچ Relation ای ندارند نیز بپیوندند. در این حالت، شرط JOIN بر اساس ستون‌هایی است که در جداول مربوطه نام یکسانی دارند، اما ستون‌ها نباید خط داده‌ای مطابق با ستون‌های فیزیکی Data Model داشته باشند. این می تواند جداول فیزیکی پرس و جوی سردرگمی یک Data Model را ایجاد کند.برای مثال، دو جدول فیزیکی به نام‌های P_A (ستون‌های ProductKey، Code و Color) و P_B (ProductKey، Name و Brand) را بدون هیچ Relation ی در نظر بگیرید.
شما نمی توانید این دو جدول را با استفاده از ProductKey به هم بپیوندید، زیرا این ستون ها نام یکسانی دارند اما خط داده های متفاوتی در مدل دارند. در واقع کد زیر یک خطا ایجاد می کند:
EVALUATE NATURALLEFTOUTERJOIN( P_A, P_B ) |
Top of Form
Bottom of Form
خطای ایجاد شده می گوید: «هیچ ستون مشترک مشترکی شناسایی نشد. تابع JOIN ” NATURALLEFTOUTERJOIN ” به حداقل یک ستون مشترک نیاز دارد. در صورتی که یک NATURALINNERJOIN اجرا شود، یک پیام مشابه نمایش داده می شود.
برای به هم JOIN دو ستون با نام مشابه و بدون Relation، لازم است که این ستون ها خط داده ای نداشته باشند. برای به دست آوردن آن، لازم است ستون را با استفاده از عبارتی بنویسید که خط داده را می شکند، مانند مثال زیر.
EVALUATE VAR A = SELECTCOLUMNS ( P_A, “ProductKey”, P_A[ProductKey]+0, “Code”, P_A[Code], “Color”, P_A[Color] ) VAR B = SELECTCOLUMNS ( P_B, “ProductKey”, P_B[ProductKey]+0, “Name”, P_B[Name], “Brand”, P_B[Brand] ) VAR Result = NATURALLEFTOUTERJOIN ( A, B ) RETURN Result |
Top of Form
Bottom of Form
از نقطه نظر عملکرد، یک راه حل بهتر شامل استفاده از TREATAS است :
EVALUATE VAR B_TreatAs = TREATAS ( P_A, P_B[ProductKey], P_A[Code], P_A[Color] ) VAR Result = NATURALLEFTOUTERJOIN ( B_TreatAs, P_B ) RETURN Result |
Top of Form
Bottom of Form
این دو راه حل یک هدف مشترک دارند: ارائه دو جدول به تابع join در DAX که دارای یک یا چند ستون با خط داده یکسان هستند. از این ستون(ها) برای JOIN به دو جدول و ایجاد نتیجه استفاده می شود.
بیشتر بخوانید : آموزش زبان DAX
استفاده از DAX در Excel 2013 و Analysis Services 2012/2014
نسخه های قبلی DAX NATURALLEFTJOIN و NATURALINNERJOIN ندارند. شما می توانید معادل یک INNER را با تعبیه عبارت CROSSJOIN در یک فیلتر به دست آورید، اگرچه در صورتی که مجبور باشید نتیجه را جمع آوری کنید (همانطور که بعدا خواهیم دید) این پیشنهاد نمی شود. JOIN داخلی زیر را در SQL در نظر بگیرید:
SELECT * FROM a INNER JOIN b ON a.key = b.key |
شما می توانید یک نحو معادل در DAX با استفاده از عبارت زیر بنویسید:
EVALUATE FILTER ( CROSSJOIN ( a, b ), a[key] = b[key] ) |
Bottom of Form
هیچ راه ساده ای برای به دست آوردن یک نحو در نسخه های قدیمی DAX – تا سال 2014 – مطابق با یک LEFT JOIN در SQL وجود ندارد. با این وجود، اگر بتوانید فرض کنید که یک Relation چند به یک بین جدول سمت چپ و جدول سمت راست دارید، گزینه دیگری دارید. این مورد LEFT JOIN با استفاده از Relationships در DAX بود، و شما راه حل را در DAX با استفاده از RELATED مشاهده کرده اید . اگر Relation وجود نداشته باشد، می توانید به جای آن از تابع LOOKUPVALUE استفاده کنید.
به عنوان مثال، همان پرس و جوی SQL را در نظر بگیرید که قبلا دیده شده بود.
SELECT s.*, d.Year, p.Color FROM Sales s LEFT JOIN Date d ON d.DateKey = s.DateKey LEFT JOIN Product p ON p.ProductKey = s.ProductKey |
می توانید آن را در DAX به صورت زیر بنویسید:
EVALUATE ADDCOLUMNS ( Sales, “Year”, LOOKUPVALUE ( ‘Date'[Year], ‘Date'[DateKey], Sales[DateKey] “Color”, LOOKUPVALUE ( Product[Color], Product[ProductKey], Sales[ProductKey] ) ) |
Bottom of Form
نسخه ای که از RELATED استفاده می کند کارآمدتر است، اما این دومی می تواند جایگزین خوبی در صورت عدم وجود Relation باشد.
در نهایت، پرس و جوی را در نظر بگیرید که نتیجه یک LEFT JOIN را در SQL جمع می کند، مانند آنچه قبلا دیده شد (ما فقط عبارت ORDER BY را اضافه کردیم):
| SELECT d.Year, p.Color, SUM ( s.Quantity ) AS [Total Quantity] FROM Sales s LEFT JOIN Date d ON d.DateKey = s.DateKey LEFT JOIN Product p ON p.ProductKey = s.ProductKey GROUP BY d.Year, p.Color ORDER BY d.Year, p.Color |
در اینجا می توانید از دو روش استفاده کنید. اولین مورد این است که از نحو LOOKUPVALUE استفاده کنید و نتیجه را همانطور که در نحو DAX زیر نشان داده شده است جمع کنید:
| EVALUATE SUMMARIZE ( ADDCOLUMNS ( Sales, “Sales[Year]”, LOOKUPVALUE ( ‘Date'[Year], ‘Date'[DateKey], Sales[DateKey] ), “Sales[Color]”, LOOKUPVALUE ( Product[Color], Product[ProductKey], Sales[ProductKey] ) ), Sales[Year], Sales[Color], “Total Quantity”, CALCULATE ( SUM ( Sales[Quantity] ) ) ) ORDER BY Sales[Year], Sales[Color] |
Top of Form
Bottom of Form
با این حال، اگر تعداد ترکیب‌های ستون‌های جمع‌آوری شده کم و تعداد ردیف‌های جدول جمع‌آوری شده زیاد باشد، ممکن است این رویکرد را در نظر بگیرید – پرمخاطب، اما در شرایط خاص سریع‌تر:
| DEFINE MEASURE Sales[Total Quantity] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALL ( Sales[ProductKey] ), CONTAINS ( VALUES ( Product[ProductKey] ), Product[ProductKey], Sales[ProductKey] ) ), FILTER ( ALL ( Sales[DateKey] ), CONTAINS ( VALUES ( ‘Date'[DateKey] ), ‘Date'[DateKey], Sales[DateKey] ) ) ) EVALUATE FILTER ( ADDCOLUMNS ( CROSSJOIN ( ALL ( ‘Date'[Year] ), ALL ( Product[Color] ) ), “Total Quantity”, [Total Quantity] ), NOT ISBLANK ( [Total Quantity] ) ) ORDER BY ‘Date'[Year], Product[Color] |
نتیجه گیری
در DAX بهترین راه برای JOIN به جداول همیشه استفاده از Relationships فیزیکی در Data Model است، زیرا منجر به کد DAX ساده‌تر و سریع‌تر می‌شود. چندین تکنیک در DAX برای JOIN به جداول موجود است. اینها می توانند برای ایجاد جداول محاسبه شده یا جداول کوچک در عبارات پیچیده که در اندازه گیری ها و در ستون های محاسبه شده استفاده می شوند مفید باشند. با این حال، این تکنیک ها از نقطه نظر عملکرد گران تر هستند و همچنین منجر به کد DAX پیچیده تر می شوند.
دوره های مرتبط
دوره کامل تضمینی هوش تجاری با رویکرد کسب درآمد در داخل و خارج از کشور
آنالیز داده و هوش تجاری یکی از پردرآمدهای شغل های دنیا چه در داخل کشور و چه خارج از کشور است. ما در دوره آموزشی صفر تا صد هوش تجاری را به صورت تضمینی آموزش میدهیم.
آموزش صفر تا صد داشبورد سازی در سی شارپ
آموزش کامل و پروژه محور مباحث به همراه مثال کاملا عملی در محیط سی شارپ که یک محیط برنامه نویسی ماکروسافتی می باشد. دوره آموزش داشبورد سازی در سی شارپ شامل ساخت یک داشبود زیبا در محیط سی شارپ به کمک کامپوننتهای ssrs و Chart.js می باشد.
آموزش کاملا حرفه ای ویدیویی هوش تجاری در Oracle
آموزش ویدیویی هوش تجاری در Oracle ابتدا به بررسی ساختار کلی هوش تجاری اوراکل پرداخته و سپس به مراحل نصب نرم افزارهای مرتبط با کار خود می پردازیم. سپس مباحث آنالیز داده و فاندامنتال کار را بررسی کرده و در خصوص ریپازیتوری و مسایل مربوط به ان صحبت می کنیم.
آموزش ویدیویی هوش تجاری در SQL
شما در این دوره آموزشی (آموزش ویدیویی هوش تجاری در SQL) با مفاهیم و اصول هوش تجاری در SQL آشنا می شوید.
آموزش صفر تا صد طراحی؛ تحلیل و پیاده سازی سامانه های حرفه ای هوش تجاری با پاور بی آی
آموزش صفر تا صد طراحی؛ تحلیل و پیاده سازی سامانه های حرفه ای هوش تجاری با پاور بی آی در 91 ساعت و توسط اساتید مجرب آماده شده است.
دیدگاهتان را بنویسید