مدل سازی داده : ایجاد Shared Dimension در Power BI

در این مطلب با مفهوم Dimension مشترک (Shared Dimension) و ایجاد آن در Power Query آشنا خواهید شد. برای افرادی که با طراحی انبار داده آشنا هستند، مفهوم Dimension مشترک (Shared Dimension)، بسیار واضح و روشن است و طراحی ستاره ای معمولا شامل تعدادی از این Dimension هاست.

برای آشنایی با مفاهیم مدل سازی داده، طراحی انبار داده و مدل ستاره ای مطالب زیر را مطالعه نمایید.

بسیاری از کاربران Power BI آشنایی چندانی در زمینه طراحی و مفاهیم انبار داده ندارند و خیلی ضروری است که برخی از مفاهیم پایه ای طراحی انبار داده را بدانند و بتوانند مدل های بهتری طراحی کنند. در ادامه با یکی از مفاهیم پایه ای انبار داده آشنا خواهید شد.

برای این منظور از یک مجموعه داده شامل 3 جدول استفاده شده است. اولین جدول، جدول Inventory است که شامل اطلاعات موجودی در هر انبار در تاریخ های مختلف است.

دومین جدول، جدول Sales اطلاعات فروش را در خود ذخیره کرده است.

سومین جدول، جدول Manufacturing هزینه ساخت محصولات مختلف در هر تاریخ را نمایش می دهد.

چالش

وقتی این مجموعه داده را بارگذاری می کنید همان طور که در تصویر مشاهده می کنید، این 3 جدول هیچ ارتباطی با یکدیگر ندارند.

چالش روابط چند به چند (Many-To-Many Relationships)

اگر در مورد روابط در Power BI اطلاعی ندارید، مطلب زیر را مطالعه نمایید.

ابتدا به این پرسش پاسخ دهید. چگونه بین دو جدول در Power BI ارتباط برقرار می کنید؟ بله! همان طور که همه شما می دانید از طریق فیلد مشترک می توان بین 2 جدول ارتباط برقرار نمود. حال سعی کنید رابطه ای بین 2 جدول Inventory و Manufacturing ارتباط برقرار کنید. همان طور که در تصویر مشاهده می کنید هنگامی که سعی می کنید بین این 2 جدول ارتباط برقرار کنید با پیامی مواجه می شوید. این پیام به شما می گوید که شما نیاز به ایجاد رابطه چند به چند (Many-To-Many Relationship) دارید.

اما سعی بر ایجاد رابطه چند به چند نکنید، چرا که این نوع رابطه خیلی مفید نیست و تحت شرایط خاصی به کار گرفته می شود. اما چرا با این پیام مواجه شدیم؟ به خاطر داشته باشید برای ایجاد رابطه یک به چند که متداول ترین نوع رابطه به حساب می آید، حتما یکی از دو طرف رابطه باید یکتا باشد. در واقع یکی از دو ستونی که قصد ایجاد رابطه از طریق آن ها را داریم باید یکتا باشد. اما همان طور که در تصویر مشاهده می کنید ستون Product که ستون مشترک محسوب می شود و قصد ایجاد رابطه بر اساس آن را داریم در هر دو جدول مقادیر تکراری دارد و این باعث می شود که امکان ایجاد رابطه یک به چند، وجود نداشته باشد.

پس نتیجه می گیریم هنگامی که بخواهیم بین 2 ستون از 2 جدول که هیچ کدام از دو ستون دارای مقادیر یکتا و منحصر به فرد نباشند (مثل ستون Product)، رابطه برقرار کنیم، Power BI این امکان را به ما نمی دهد و از ما می خواهد رابطه چند به چند ایجاد کنیم، که ما معمولا از انجام این کار و ایجاد این نوع از رابطه صرف نظر می کنیم.

چالش رابطه دو طرفه

حال سعی کنیم بین 2 جدول Manufacturing و Inventory بر اساس ستون تاریخ رابطه برقرار کنیم. همان طور که در تصویر مشاهده می کنید، رابطه خود به خود به صورت دو طرفه (bi-directional) تعریف می شود. دلیل این اتفاق این است که هر دو ستون در دو طرف رابطه (Date) دارای مقادیر یکتا و منحصر به فرد هستند. بدانید که رابطه های دو طرف اثر منفی بسیار زیادی بر عملکرد مدل خواهند داشت، لذا تا حد امکان از ایجاد آن ها جلوگیری کنید.

حتی اگر مجبور به استفاده از روابط دو طرفه شدید، به صورت دستی و فیزیکی این کار را انجام ندهید بلکه از تابع CROSSFILTER استفاده کنید. برای آگاهی از نحوه استفاده از این تابع مطلب روابط در Power BI را مطالعه نمایید.

چالش عدم وجود Master List

سومین چالش این مدل عدم وجود Master List است. ممکن است کالایی در یکی از جداول باشد که در جداول دیگر موجود نیست. ممکن است تاریخی در یکی از جداول وجود داشته باشد که در جداول دیگر موجود نباشد. به این چالش، چالش عدم وجود Master List گفته می شود.

حال بیایید بین جداول بر اساس تاریخ رابطه 2 طرفه ایجاد کنیم. مدل ایجاد شده را در تصویر زیر مشاهده می کنید.

حال یک Slicer ایجاد کنید که در آن از ستون تاریخ و از جدول Sales استفاده شده است. همان طور که در تصویر زیر مشاهده می کنید لیستی از تمامی تاریخ ها در همه جداول وجود ندارد. این چالش برای کالا ها نیز وجود دارد.

اگر تاریخی را از Slicer تاریخ انتخاب کنید، هر 3 جدول را فیلتر می کند اما 2 تاریخ موجود در جداول Inventory و Manufacturing که در تصویر مشخص شده است، در Slicer وجود ندارد، چرا که Slicer تاریخ از ستون تاریخ جدول Sales استفاده می کند، لذا فیلتر کردن بر اساس این تاریخ ها امکان پذیر نمی باشد. این چالش در صورت انتخاب فیلد تاریخ از هر جدول برای جداول دیگر به وجود خواهد آمد. بعلاوه این چالش برای ستون Product نیز وجود دارد.

پس اگر از ستونی که همه ی مقادیر ممکن در آن وجود ندارد در Slicer استفاده کنیم، امکان فیلتر کردن داده از بین می رود و داده های صحیح نشان داده نمی شود. پس باید به دنبال راهی اصولی برای حل این مشکل باشیم.

استفاده از Dimension مشترک (Shared Dimension)

با برخی از چالش ها که با مدل سازی غیر اصولی به آن بر می خوریم آشنا شدید. بدانید که مدل شما 3 جدول نخواهد داشت و بسیار پیچیده تر خواهد بود و برخورد با این چالش ها بسیار بیشتر خواهد بود، لذا با مدل سازی بهینه از بروز این مشکلات جلوگیری کنید. راه حل این چالش ها استفاده از Shared Dimension است. Shared Dimension به نوعی از Dimension گفته می شود که بین چند Fact مشترک است.

اگر در مورد مباحث پایه ای مدل سازی اطلاعات کافی ندارید مطالب زیر را مطالعه نمایید.

Dimension ها جداولی هستند که اطلاعات توصیفی در آن ها ذخیره می شود. مانند جدول کالا که شامل اطلاعات نام کالا، وزن کالا، سازنده و این قبیل از اطلاعات است. یا جدول کارمندان که شامل اطلاعات نام کارمند، محل تولد، سال تولد، دپارتمان، سال استخدام و … می باشد. جداولی مانند جدول پزشکان، تاریخ، فروشگاه و … از جمله جداولی هستند که ممکن است به عنوان Dimension به کار گرفته شودند. Dimension ها معمولا در Slicer ها و همچنین برای برش دادن داده های جدول Fact به کار گرفته می شوند.

Fact ها معمولا شامل مقادیر عددی مثل میزان فروش، میزان تخفیف، هزینه و …  می باشند که قابل تجمیع در ابعاد مختلف هستند. این اعداد معمولا در ابعاد مختلف (Dimension)، برش داده می شوند می شوند. به مثال های زیر توجه نمایید.

میزان فروش (Fact) به تفکیک شهر های مختلف (Dimension)

میزان تخفیف (Fact) به تفکیک کالا های مختلف (Dimension)

میزان خرابی ها (Fact) به تفکیک دستگاه های مختلف (Dimension)

خب! به مثال باز برگردیم. در مثال ما Dimension کدام است؟ محصولات و تاریخ، به عنوان Dimension در مثال ما شناخته می شوند. Fact کدام است؟ فروش، موجودی و تولید در حقیقت Fact به شمار می روند. شاید برای شما سوال ایجاد شود که چرا جدول Dimension نداریم؟ پاسخ این است. چون مدل ما اصولی طراحی نشده است و Dimension ها به عنوان ستون هایی در جداول Fact قرار داده شده اند.

در واقع روش صحیح مدل سازی این است که 2 جدول Product و Date به عنوان جداول Dimension ایجاد و در نظر گرفته شوند. چرا لازم است که این 2 جدول، جداگانه ایجاد شوند؟ در واقع این جداول جداولی هستند که اعداد و ارقام جداول Fact بر اساس آن ها برش داده می شود. این جداول بین همه Fact ها مشترک هستند و به آن ها Shared Dimension یا Dimension مشترک گفته می شود. همان طور که پیش تر گفته شد، Shared Dimension نوعی از Dimension است که بین چندین Fact به اشتراک گذاشته می شود.

ایجاد Shared Dimension

اکنون می دانید Shared Dimension چیست و نبود آن ها چه چالش هایی ایجاد می کند؟ حال با هم ببینیم که این نوع Dimension چگونه ایجاد می شود. باید بدانید که شما در هر مرحله می توانید Shared Dimension را ایجاد کنید. می توانید در SQL Server، از طریق Power Query و حتی DAX آن ها را ایجاد کنید. با توجه به برتری های M بر DAX در موارد تبدیل و پاکسازی داده، ما این عمل را در  در Power Query انجام می دهیم.

برای اطلاعات بیشتر در مورد M و DAX مطالب زیر را مطالعه نمایید.

مطابق تصویر به Edit Queries بروید.

بر روی جدول Inventory کلیک راست کرده و عمل Reference را برای آن انتخاب کنید.

اگر در مورد عمل Reference و تفاوت های آن با Duplicate اطلاع ندارید مطلب زیر را مطالعه نمایید.

اما به طور خلاصه بدانید که Reference یک کپی از جدول موجود را ایجاد می کند و شما می توانید گام های دیگری نیز در راستای تغییر، تبدیل و پاکسازی داده در جدول جدید ایجاد کنید. در واقع Reference گام های پاکسازی داده را در جدول جدید کپی نمی کند در حالی که Duplicate گام های ایجاد شده در جدول مبدا را به جدول جدید منتقل می کند.

حال بر روی ستون product از جدول جدید ایجاد شده کلیک راست کرده و گزینه Remove Other Columns را انتخاب کنید.

نتیجه نهایی در تصویر زیر قابل مشاهده است.

بر روی جدول Inventory(2) راست کلیک کرده و گزینه Enable Load را انتخاب کنید. این عمل باعث جلوگیری از بارگذاری جداول اضافه در RAM می شود. برای مطالعه بیشتر در مورد Performance مطلب زیر را مطالعه نمایید.

همین گام ها را برای جداول Manufacturing و Sales نیز انجام دهید. یک Reference از هر جدول ایجاد کنید. ستون Product را نگه داشته و بقیه ستون ها را حذف کنید و در نهایت گزینه Enable Load را برای آن ها فعال کنید. در نهایت 6 جدول خواهید داشت.

3 جدول جدید ایجاد شده تنها دارای یک ستون Product هستند. مطمئن شوید ستون Product در 3 جدول هم نام باشند و اگر نیستند نام آن ها را یکسان سازی کنید، چرا که قصد ما Append کردن این 3 جدول است و برای این عمل ستون ها باید هم نام باشند و به یاد داشته باشید Power Query به حروف کوچک و بزرگ حساس است. در Power Query، کلمه product با Product متفاوت است. در صورتی که نام ستون ها را دقیقا یکسان انتخاب نکنید Power Query ستون های اضافی ایجاد خواهد کرد.

حال با انتخاب گزینه Append Queries و در ادامه Append Queries As New به پنجره مربوطه منتقل می شویم.

اکنون در پنجره Append Queries مطابق تصویر زیر عمل می کنیم.

نتیجه این عمل جمع آوری تمامی مقادیر مربوط به ستون Product در یک جدول تحت عنوان Append1 است که در تصویر قابل مشاهده می باشد. نام این جدول را به Product تغییر دهید. همان طور که مشاهده می کنید جدول Product جدول جدید ایجاد شده بعد از تغییر نام می باشد. چون این جدول حاصل 3 جدول (Manufacturing2- Inventory2- Sales2) و ما به این جداول نیازی نداریم گزینه Enable Load را برای آن غیر فعال می کنیم. این عمل باعث می شود از بارگذاری جداول غیر ملزوم در RAM جلوگیری می کنیم و این عمل باعث بهبود عملکرد مدل می شود.

می دانید که جدول ایجاد شده مجموعه ای از مقادیر 3 ستون از 3 جدول مختلف است که ممکن است حاوی مقادیر تکراری نیز باشد. یک Dimension باید فاقد مقادیر تکراری باشد. لذا به ترتیب زیر و با استفاده از Remove Duplicate مقادیر تکراری را حذف می نماییم.

اکنون Shared Dimension آماده است. این عمل را برای هر ستونی که باید به عنوان Dimension مورد استفاده قرار گیرد باید انجام دهیم. البته برای تاریخ بهتر است یک Dimension کامل و جامع ایجاد شود و برای ایجاد Dimension زمان از این روش استفاده نشود.

همان طور که در روش ابتدایی و غیر اصولی مشاهده نمودید برای دستیابی به گزارشات خود مجبور به ایجاد روابط دو طرفه (Bi-Directional) شدیم. این روابط عملکرد مدل را به شدت کاهش می دهند، لذا از ایجاد این گونه روابط تا حد امکان خودداری نمایید. حال با ایجاد Shared Dimension نیاز ما به روابط دو طرفه از بین رفت. ضمن اینکه ما یک Dimension کالا که بین تمام جداول مشترک است استفاده می کنیم.

حال اگر نیاز به ایجاد Slicer داشته باشیم آن را با استفاده از Dimension کالا ایجاد می کنیم که همه مقادیر ممکن در آن موجود است. مدل نهایی و اصولی به صورت زیر خواهد بود که یک مدل ستاره ای است. همان طور که مشاهده می نمایید در این مدل جداول Fact با یکدیگر ارتباط ندارند ولی همه آن ها با جدول Dimension در ارتباط هستند. در واقع در این مدل با 3 مدل ستاره ای روبرو هستیم که هر کدام شامل یک Fact و یک Dimension است.

جمع بندی

از مدل سازی غیر اصولی داده اکیدا پرهیز کنید. دیتا مدل غیر اصولی شما را نیازمند ایجاد روابطی می کند که عملکرد مدل را به شدت کاهش می دهد. مدل سازی غیر اصولی باعث می شود برای پوشش اشتباهات مدل سازی خود نیازمند به نوشتن کدهای پیچیده DAX شوید. مدل سازی اصولی شما را از ایجاد روابط چند به چند و دو طرفه بی نیاز می کند و سرعت و عملکرد مدل شما را بهبود می بخشد.