امروز اولین دستورات MDX را خواهیم نوشت. قبل از شروع کار فراموش نکنید موارد زیر را حتما انجام داده
باشید :
- نصب پایگاه داده ی Adventure Work DW 2008 و همچنین نصب پایگاه داده‌ی چند بعدی Adventure Work DW 2008 روی SSAS
- مطاله قسمت‌های قبلی برای آشنایی با مفاهیم پایه .
در صورتیکه پیش شرایط فوق را نداشته باشید، احتمالا در
ادامه با مشکلاتی مواجه خواهید شد؛ زیرا برای آموزش MDX Query ها از پایگاه داده‌ی Adventure Work DW 2008 استفاده شده است.
دقت داشته باشید که MDX Query ها تا حدودی شبیه T/SQL می‌باشند؛ اما مطلقا از نظر مفهومی با هم شباهت ندارند. به عبارت
دیگر ما در T/SQL با یک مدل
رابطه‌ای سرو کار داریم در حالیکه در MDX ها با یک پایگاه داده چند بعدی کار می‌کنیم. به بیان دیگر در
پایگاه داده‌های رابطه‌ای صحبت از جداول، ردیف‌ها، ستون‌ها و ضرب دکارتی مجموعه‌ها می‌باشد، اما در پایگاه داده‌های چند بعدی در خصوص Dimension,Fact,Cube,Tuple و ... صحبت می‌کنیم. البته ماکروسافت تلاش کرده‌است تا حد
زیادی Syntax ها شبیه به
یکدیگر باشند.
نحوه‌ی نوشتن یک Select در MDX ها به صورت زیر می‌باشد :
Select
{} On Columns ,
{} On Rows
From <Cube_Name>
Where <Condition> در ادامه با اجرای هر کوئری، توضیحات
لازم در خصوص آن ارایه می‌گردد و با پیگیری این آموزش‌ها می‌توانید مفاهیم، توابع
و ... را در MDX Query ها بیاموزید.
برای اجرای دستورات زیر باید Microsoft SQL
Server Management Studio را باز نمایید و به سرویس SSAS متصل شوید. سپس پایگاه داده‌ی Adventure
Works DW 2008R2 را انتخاب
نمایید و از Cubes Adventure
Works را انتخاب
نمایید.
حال دکمه‌ی New Query را در بالای صفحه بزنید ( Ctrl + N )
سپس در صفحه‌ی باز شده می‌توانید Cube یا SubCube های آن Cube را انتخاب کرده و کمی پایین‌تر Measure Group را خواهیم داشت و در انتها Measure ها و Dimension ها قرار گرفته‌اند. (در هنگام نوشتن Select میتوان از عمل Drag&Drop برای آسان‌تر شدن نوشتن MDX Query ها نیز استفاده کنید)
متاسفانه هنوز در IDE مربوط به SQL Server کلیدی برای مرتب سازی دستورات MDX وجود ندارد و البته در نرم افزار هایی مانند SQL Toll Belt هم چنین چیزی قرار داده نشده است . بنابر این توصیه می‌شود در نوشتن دستورات MDX تمام تلاش خود را بکنید تا دستوراتی مرتب و خوانا را تولید کنید.
با اجرای دستور زیر اولین کوئری خود
را در پایگاه داده‌ی چند بعدی بنویسید (برای اجرا کلید F5 مانند T/SQL کار خواهد کرد.)
Select
From [Adventure Works]
شاید تعجب کنید. کوئری فاقد قسمت Projection می‌باشد! در MDX ها میتوان هیچ سطر یا ستونی را انتخاب نکرد. اما
چگونه؟ و خروجی نمایش داده شده چیست؟
برای توضیح مطلب فوق باید در خصوص Default
Measure کمی اطلاعات
داشته باشید. در هنگام Deploy کردن پروژه در SSAS برای هر Cube یک Measure به عنوان Measure پیش فرض انتخاب شده. بنابر این در صورتیکه هیچ گونه Projection یا Where ایی اعمال نشده باشد، SQL Server به صورت پیش فرض مقدار Mesaure پیش فرض را بدون اعمال هیچ بعدی نمایش می‌دهد.
خروجی دستور بالا مشابه تصویر زیر می‌باشد.
حال دستور زیر را اجرا می‌کنیم :
Select
From [Adventure Works]
Where [Measures].[Reseller Sales Amount]
تصویر خروجی به صورت زیر می‌باشد :
شاید باز هم تعجب کنید. نوشتن نام یک
شاخص به جای عبارت شرط؟! آیا خروجی عبارات شرطی نباید Boolean باشند؟
خیر. اگر چنین پرسش هایی در ذهن شما
ایجاد شده باشد، به دلیل مقایسه‌ی MDX با T/SQL می‌باشد. در اینجا شرط Where بر روی ردیف‌های جدول مدل رابطه ای اعمال نمی‌شود و عملا بیانگر
واکشی اطلاعات از مدل چند بعدی می‌باشد. با اعمال شرط فوق به SSAS اعلام کرده ایم که خروجی بر اساس شاخص [Measures].[Reseller
Sales Amount] باشد. با توجه به
این که شاخص انتخاب شده با شاخص پیش فرض یکی می‌باشد خروجی با حالت قبل تفاوتی
نخواهد کرد.
برای درک بهتر،
کوئری زیر را اجرا کنید :
Select
From [Adventure Works]
where [Measures].[Internet Sales Amount]
استفاده از این شرط سبب استفاده نشدن از شاخص پیش فرض می شود .
به عبارت دیگر این کوئری دارای سرجمع مبلغ فروش اینترنتی می باشد.
دستور زیر را
اجرا کنید :
Select
[Measures].[Reseller Sales Amount] on columns
From [Adventure Works]
با اعمال یک شاخص خاص در ستون ، عملا فیلترینگ انجام می شود
استفاده از یک
دایمنشن در ستون :
دستور زیر را
اجرا کنید
Select
[Date].[Calendar].[Calendar Year] on columns
From [Adventure Works]
خروجی به شکل
زیر خواهد بود
همان طور که
مشاهده می‌کنید خروجی دارای چندین ستون می‌باشد و دارای مقادیری در هر ستون. اما
این مقادیر از کجا آمده اند؟
همواره این
نکته را به خاطر بسپارید که در صورت عدم ذکر نام یک Measure در کوئری ، SSAS از Measure پیش فرض استفاده میکند. حال کوئری
فوق میزان فروش نمایندگان ( Reseller Sales Amount )
را در هر سال نمایش می‌دهد.
سوال بعدی این می‌باشد که این سال‌ها از کجا آمده اند؟ خوب برای درک بهتر این مورد می‌توانیم
مانند تصویر زیر به دایمنشن Date
رفته و در ساختار سلسله مراتبی ، اعضای سطح [Date].[Calendar].[Calendar Year] را مشاهده کنیم.
ایجاد سرجمع
ستون‌ها :
کوئری زیر را
اجرا نمایید
Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns
From [Adventure Works] بعد از اجرا
تصویر زیر را خواهید دید :
سوال اول این می‌باشد که کاربرد {} در انتخاب دایمنشن‌ها چیست؟ در پاسخ میتوان گفت که اگر شاخص ها یا بعد ها ، مرتبط به یک سلسله مراتب باشند آنها را در یک {} قرار می دهیم ولی اگر سلسله مراتب متفاوت باشد، یا بعد و شاخص باشند باید در () قرار بگیرند .
خوب همان طور که مشخص است در ساختار
سلسله مراتبی ابتدا سال و بعد یک سطح بالا‌تر را انتخاب کرده ایم این به معنی
نمایش سرجمع در سطح بالا‌تر از سال می‌باشد(سرجمع تمامی سال ها).
استفاده از دایمنشن و Measure در سطر و ستون مجرا :
کوئری زیر را اجرا نمایید
Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
[Product].[Product Categories].[Category] on rows
From [Adventure Works]
خروجی مشابه شکل زیر می‌باشد
در مثال فوق از بعد‌ها در ستون و
همزمان، نمایش نوع دسته بندی محصولات در ردیف‌ها استفاده شده است. به عبارت دیگر
نتیجه عبارت است از فروش نماینگان فروش ( Reseller Sales Amount ) براساس هر سال به تفکیک نوع دسته بندی محصول فروخته
شده.
(کسانی که چنین گزارشی را با استفاده
از T/SQL نوشته اند،
احتمالا از آسانی نوشتن این گزارش توسط MDX ها شگفت زده شده اند.)
قراردادن فیلد سرجمع در ردیف :
برای این منظور کوئری زیر را اجرا
نمایید
Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}on rows
From [Adventure Works] خروجی به صورت زیر می‌باشد
نحوه‌ی نمایش سرجمع در ردیف، مشابه
نمایش سرجمع در ستون می‌باشد.
استفاده از تابع non empty :
برای حذف ستون هایی که کاملا دارای
مقدار null می‌باشند به
صورت زیر عمل می‌کنیم :
Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns ,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works] خروجی به صورت زیر می‌باشد:
انتخاب دو دایمنشن در سطر و ستون و مشخص
نمودن یک Measure خاص برای کوئری
:
برای این کار به صورت زیر عمل خواهیم
کرد:
Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount] در اینجا با اعمال شرط Where عملا از SSAS خواسته‌ایم خروجی برای شاخص مشخص شده واکشی شود.
در بالا میزان فروش اینترنتی برای
دسته بندی محصولات و در سال‌های مختلف ارائه و همچنین سرجمع ستون و سطر نیز
نمایش داده شده است.
در صورتیکه بخواهیم ستون و سطرهایی
را که دارای مقدار null در تمامی آن سطر
یا ستون می‌باشند، حذف کنیم به صورت زیر عمل می‌کنیم:
Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
non empty {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]
اگر در یک دایمنشن فقط یک سلسله مراتب باشد یا اصلا سلسله مراتبی وجود نداشته باشد، می توان از نام خود دایمنشن استفاده کرد
Select
[Sales Channel] on columns
From [Adventure Works]
و دقت داشته باشید دایمنشنی که دارای بیش از یک سلسله مراتب باشد، حتما باید در Select مشخص شود که از کدام سلسله مراتب می خواهیم استفاده کنیم .در غیر این صورت با خطا مواجه خواهیم شد.
Select
[Product] on columns
From [Adventure Works]
استفاده از فیلدهای یک دایمنشن که دارای سلسه مراتب می باشد نیز جایز می باشد
Select
[Product].[Category] on columns
From [Adventure Works]
Select
[Product].[Category].[all] on columns
From [Adventure Works]
--
Select
[Product].[Category].[All] on columns
From [Adventure Works]
--
Select
[Product].[Category].[(all)] on columns
From [Adventure Works]
--
Select
[Product].[Category].[all products] on columns
From [Adventure Works]
برای به دست آوردن سرجمع کل روی یک صفت از دایمنشن، باید از سه حالت آخر استفاده کرد. حالت اول خطا دارد و خروجی خالی نمایش داده می شود .
در صورتی که بخواهیم از یک دایمنشن
تمامی Member های آن را واکشی کنیم به صورت زیر
عمل خواهیم کرد
Select
{[Product].[Category].members} on columns
From [Adventure Works] استفاده از Members روی یک خصوصیت در دایمنشن به معنی دریافت سرجمع آن صفت و سپس تک تک اجزای آن صفت می‌باشد.
اگر از یک صفت واکشی اطلاعات انجام شود در سطح اعضای آن، در آن صورت دیگر سرجمع نمایش داده نمی شود و فقط جمع هر عضو در آن صفت نمایش داده می شود .
Select
[Product].[Category].[Category].members
-- dimension.hierarchy.level.members
on columns
From [Adventure Works]
اگر بخواهیم
دو ستون را داشته باشیم که هر دو برای یک دایمنشن می‌باشند باید از {} استفاده کرد
. دستور اول خطا خواهد داشت.
Select
[Product].[Category].[Category].members,[Product].[Category].[All Products] on columns
From [Adventure Works]
در دستور دوم
با استفاده از {} خروجی نمایش داده می‌شود که عبارت است از تمامی اعضای سطح [Product].[Category].[Category]. به همراه سرجمع تمامی محصولات.
Select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns
From [Adventure Works]
یک راه کوتاه‌تر برای انتخاب تمامی اعضا و سرجمع آنها
Select
{[Product].[Category].[Category],[Product].[Category]}
on columns
From [Adventure Works] می توان از
کلمات Members, All X استفاده نکرد.
انتخاب اولین دسته بندی محصول البته این ترتیب بر اساس Key
Columns در SSAS می باشد .
Select
[Product].[Category].&[1]
on columns
From [Adventure Works]
انتخاب دقیق
یک عضو در خروجی
Select
[Product].[Category].[Bikes]
on columns
From [Adventure Works]
انتخاب دو عضو از یک دایمنشن
Select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}
on columns
From [Adventure Works]
واکشی تمامی دسته بندی محصولات بر
اساس Measure پیش فرض :
Select
[Product].[Product Categories].members
on columns
From [Adventure Works]
در صورتیکه
بخواهیم دو Dimension
مختلف را در یک ستون یا سطر بیاوریم باید از Join استفاده کنیم. بنابر این دو دستور
زیر با خطا روبرو می‌شوند
Select
[Product].[Product Categories],[Product].[Category]
on columns
From [Adventure Works]
Go
Select
{[Product].[Product Categories],[Product].[Category]}
on columns
From [Adventure Works] 
تعریف Axis : به هر کدام از ستون یا سطر یک محور
یا Axis گفته می‌شود.
با بررسی مثال
فوق به نتایج زیر خواهیم رسید.
1. امکان استفاده از دو سلسله مراتب مختلف از یک دایمنشن در یک Axis
وجود ندارد . مگر اینکه آنها را باهمدیگر CrossJoin
کنیم .
2. امکان استفاده از دو سلسله مراتب مختلف از یک دایمنشن در دو Axis مختلف وجود دارد .
ترتیب انتخاب Axis ها به صورت زیر می‌باشد:
1.
Columns
2.
Rows
برای مشخص شدن
موضوع کوئری زیر را اجرا کنید
Select
[Product].[Product Categories].members
on rows
From [Adventure Works]
نمی‌توانیم ردیفی را
واکشی کنیم بدون اینکه ستونی برای کوئری مشخص کرده باشیم.
البته میتوان ستون خالی
ایجاد نماییم مانند مثال زیر :
Select
{} on columns,
[Product].[Product Categories].members
on rows
From [Adventure Works] البته در این صورت خروجی
فقط نام دسته بندی محصولات خواهد بود زیرا هیچ ستونی مشخص نشده .
در مقالات
بعدی به ادامه‌ی مطالب MDX Query
خواهیم پرداخت.
