مدل سازی داده؛ به سوی مدل ستاره ای

آیا داشتن تعداد زیادی جدول Dimension مناسب است؟ آیا ادغام کردن چندین جدول و ساخت یک Flat Dimension رویکرد مناسبی است؟ اگر Flat کردن مناسب است تا چه میزان این کار باید صورت پذیرد؟ آیا داشتن جدول بسیار بزرگ و Flat که شامل همه اطلاعات باشد مناسب است؟ در این مطلب در مورد ادغام کردن جداول و ساخت Dimension به منظور رسیدن به مدل ستاره ای صحبت خواهیم کرد.

برای این مطلب از پایگاه داده AdventureWorks استفاده شده است. 4 جدول DimProduct که شامل اطلاعات کالا، جدول DimProductSubcategory که شامل اطلاعات زیر گروه کالا، جدول DimProductCategory که شامل اطلاعات گروه کالا و جدول FactInternetSale که شامل اطلاعات فروش می باشد در مدل بارگذاری شده اند.

نمایی از جدول DimProduct حاوی اطلاعات کالا در تصویر زیر قابل مشاهده است:

نمایی از جدول DimProductSubcategory حاوی اطلاعات زیر گروه کالا در تصویر زیر قابل مشاهده است:

نمایی از جدول DimProductCategory حاوی اطلاعات گروه کالا در تصویر زیر قابل مشاهده است:

نحوه ارتباط جداول را در تصویر زیر مشاهده می نمایید:

اگر مطالب قبلی در مورد Data Modeling را دنبال کرده باشید با مفاهیم Fact و Dimension و همچنین طراحی ستاره ای آشنا هستید. اما اگر مطالب قبلی را مطالعه نکرده اید حتما کمی وقت بگذارید و مطالب زیر را مطالعه نمایید:

حال که با مفاهیم آشنا شده اید به این سوال پاسخ دهید. آیا مدل فوق یک مدل ستاره ای است؟ درست حدس زده اید! این مدل یک مدل ستاره ای نیست. چرا که در مدل ستاره ای جداول Dimension با هم ارتباط نداند و فقط به جدول Fact متصل هستند. اما همان طور که در تصویر مشاهده می کنید در این مدل 3 جدول Dimension با یکدیگر ارتباط برقرار کرده اند. این نوع از مدل سازی که به صورت زنجیره ای از روابط طراحی می شود مدل Snowflake یا دانه برفی نامیده می شود. در این نوع از طراحی برخی از جداول Dimension به صورت زنجیره ای و البته در یک جهت (یک به چند – یک به چند) با یکدیگر رابطه برقرار گرده اند.

نیاز به روابط دو طرفه (Bi-directional)

اگر در مورد جهت روابط در Power BI اطلاعات کافی ندارید ابتدا مطلب زیر را مطالعه نمایید:

اصولا  جهت روابط در Power BI از سمت یکِ رابطه به سمت چند است. یعنی به طور مثال از سمت DimProductCategory به سمت DimProductSubcategory و از DimProductSubcategory به DimProduct و در نهایت به جدول FactInternetSale می باشد. نحوه ی تعیین سمت یک و چند رابطه به این صورت است که بر فرض مثال ما به ازای هر گروه کالا، چند زیر گروه کالا داریم، پس جدول گروه کالا (DimProductCategory) سمت یکِ رابطه و جدول زیر گروه کالا (DimProductSubcategory) سمت چند رابطه می باشد. به همین ترتیب به ازای هر زیر گروه کالا (DimpRODUCTsUBCATEGORY) چندین کالا داریم پس زیر گروه کالا سمت یکِ رابطه و کالا (DimProduct) سمت چند رابطه است.

وقتی می گوییم جهت روابط از سمت یکِ رابطه به سمت چند رابطه است، یعنی چه؟ یعنی شما می توانید اعداد و ارقام جدول FactInternetSale را با استفاده از هر کدام از فیلد های جداول Dimension برش بزنید. یعنی هر کدام از ستون های جداول DimProduct-DimProductSubcategory-DimCategory را انتخاب کنید اعداد FactInternetSales بر اساس آن ها فیلتر خواهند شد.

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

حال بیاید برعکس عمل کنیم. ینی زیر گروه کالا در Slicer و گروه کالا را در جدول قرار دهیم. همان طور که مشاهده می کنید با انتخاب یک زیر گروه کالا هیچ گونه فیلتری اعمال نمی شود و نمی توانیم متوجه شویم این زیرگروه کالا متعلق به چه گروهی از کالاها است. چرا که زیر گروه کالا سمت چند رابطه است و نمی تواند سمت یکِ رابطه را فیلتر کند.

اگر به تصویر زیر و جهت روابط دقت کنید در می یابید فیلتر ها چگونه انجام می شوند.

ساخت سلسله مراتبی از 3 جدول

فرض کنید بخواهید سلسله مراتبی از 3 جدول بسازید. می دانید که ساخت سلسله مراتب از فیلدهای یک جدول امکان پذیر است، بدین گونه که با Drag & Drop فیلد ها زیر یکدیگر این Hierarchy ساخته می شود، اما ساخت سلسله مراتب از فیلد های جدول های مختلف امکان پذیر نیست. توجه کنید که ساخت سلسله مراتب (Hierarchy) در Visual امکان پذیر است، اما ساخت یک سلسله مراتب دائمی امکان پذیر نیست.

در تصویر زیر یک سلسله مراتب دائمی که در جدول ایجاد شده را مشاهده می کنید. این hierarchy با Drag کردن فیلد های مورد نظر زیر یکدیگر ساخته می شود. برای ساخت این Hierarchy باید فیلد ها از یک جدول انتخاب شوند. به طور مثال در تصویر زیر فیلد نام زیر گروه کالا (English Subcategory Name) به عنوان زیر مجموعه ای از نام مدل (Model Name) در نظر گرفته شده است.

اگر بخواهید سلسله مراتبی ایجاد کنید که فیلد های آن در چند جدول وجود دارند باید آن را در Visual ایجاد کنید. در تصویر زیر نمونه ای از ایجاد سلسله مراتب در Visual را مشاهده می کنید. در تصویر زیر نمونه ای از ایجاد سلسله مراتب در گزارش را مشاهده می کنید که نام کالا زیر مجموعه ای از زیر گروه کالا و زیر گروه کالا زیر مجموعه ای از گروه کالا در نظر گرفته شده است.

توجه داشته باشید که ایجاد سلسله مراتب در جدول بسیار بهتر از ایجاد آن در گزارش است و عملکرد بهتری ارائه می دهد. راه حل ایجاد Hierarchy که فیلد های آن از چندین جدول انتخاب می شوند، چیست؟

با استفاده از تابع RELATED می توان این کار را انجام داد. باید بدانید که تابع RELATED یک مقدار را از سمت یکِ رابطه استخراج می کند. پس باید در جدول سمت چند رابطه یک ستون محاسباتی ایجاد کنیم و مقدار مورد نیاز را از سمت یکِ رابطه استخراج کنیم. به مثال توجه کنید.

به جدول کالا می رویم و دو ستون محاسباتی ایجاد می کنیم و با استفاده از تابع RELATED مقادیر زیر گروه و گروه کالا را برای هر کالا استخراج می کنیم. نحوه استخراج نام زیر گروه کالا را در تصویر زیر مشاهده می نمایید. کافیست نام ستونی که مد نظرتان است را به عنوان ورودی تایع RELATED وارد می کنیم.

نکته ای که باید به آن توجه کنید این است که برای استفاده از تابع RELATED باید بین جداول، رابطه وجود داشته باشد. نکته دیگر این است که ستونی که به عنوان ورودی به تابع RELATED داده می شود باید در سمت یکِ زنجیره روابط باشد، در غیر این صورت با خطا مواجه خواهید شد.

در تصویر زیر نحوه ی استخراج نام گروه کالا را در تصویر مشاهده می کنید.

حال به راحتی می توانید سلسله مراتب (Hierarchy) را در جدول کالا ایجاد کنید.

داستان روابط اضافی

یکی از چالش های موجود در مدل ها وجود روابط اضافی در مدل است. در همین مدل ساده 3 رابطه وجود دارد. در دنیای واقعی 3 جدول وجود ندارد. در دنیای واقعی صدها جدول و رابطه وجود دارد. تعداد زیاد جداول و رابطه ها عملکرد مدل را تحت تاثیر قرار می دهد. مدل زیر را مورد توجه قرار دهید. این گونه مدل سازی نه تنها کاربر را سر در گم و گیج می کند بلکه عملکرد مدل را نیز تحت تاثیر قرار می دهد. این گونه مدل سازی به هیچ وجه مناسب ایجاد گزارش های تحلیلی نیست.

تصویر زیر را مشاهده کنید که با وجود تعداد کم جداول و روابط در مدل ساده ما با جستجوی کلمه Product چه تعداد ستون و جدول درگیر شده اند. این نوع مدل سازی تنها سردرگمی کاربر را در پی خواهد داشت.

دلیل این نوع از طراحی چیست؟

این نوع از طراحی که ذکر شد چالش های بسیاری به همراه خواهد داشت. اما دلیل این نوع طراحی چیست؟ این نوع طراحی از اصول طراحی پایگاه داده عملیاتی تبعیت می کند. پایگاه داده عملیاتی باید جوری طراحی شود که عملیات CRUD(CREATE, RETRIEVE, UPDATE, DELETE) را سریع و آسان به انجام رساند و بهترین روش انجام آن ایجاد یک جدول به ازای هر موجودیت است.

پس در این نوع طراحی برای موجودیت گروه کالا، زیر گروه کالا، کالا و همچنین برند کالا به عنوان یک موجودیت در نظر گرفته می شود. حتی برای رنگ کالا یک جدول جداگانه در نظر گرفته می شود. در این نوع طراحی احتمالا برای پوشش دادن اطلاعات کالا بیش از 10 جدول اختصاص داده می شود. این نوع طراحی اگر چه برای سیستم های عملیاتی (Transactional Database) بی نظیر عمل می کند اما پاسخگوی سیستم های هوش تجاری نیست.

ایجاد Flat Dimension

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

ادغام کردن جداول و ایجاد یک Dimension واحد

Join کردن جداول در SQL Server یا در DAX یا در Power Query امکان پذیر است. سعی کنید این کار را در SQL Server یا Power Query انجام دهید و از انجام آن در DAX بپرهیزید. در مطلب زیر می توانید دلایل انتخاب M را مطالعه کنید.

ابتدا به Power Query بروید.

روی جدول product کلیک کنید و Merge Queries را انتخاب کنید. در پنجره باز شده جدول Product که جدول انتخابی ما است به عنوان جدول اول قرار داده شده است . حال ما جدول زیر گروه کالا را به عنوان جدول دوم انتخاب کرده و ستون هایی که بین دو جدول ارتباط قرار کرده است (productSubcategoryKey) را انتخاب می کنیم.

عمل Merge یا Join در واقع عمل Flat کردن جداول بر اساس فیلد مشترک است. اگر در مورد Join و انواع آن اطلاعی ندارید مطلب زیر را مطالعه کنید.

بعد از انجام این عمل ستونی در انتهای جدول Product ایجاد می شود که به ما این امکان را می دهد که ستون های مورد نیاز از جدول Subcategory را انتخاب کنیم. ما تنها 2 ستون نام زیر گروه کالا (EnglishSubcategoryName) و کد گروه کالا (CategoryKey) را انتخاب کردیم. انتخاب کد گروه کالا به این دلیل است که در مرحله بعد به آن احتیاج داریم.

حال 2 ستون جدید به جدول product اضافه شده است. حال عمل Join جدول Product با جدول Category را بر اساس فیلد مشترک (CategoryKey) انجام می دهیم.

این بار تنها ستون EnglishCategoryName را انتخاب می کنیم.

حال می توانیم ستون CategoryKey را حذف کنیم. جدول نهایی در تصویر قابل مشاهده است. ستون های مورد نیاز را با عمل Join به جدول Product اضافه کردیم.

حال تمام اطلاعات مورد نیاز را در یک جدول واحد تحت عنوان DimProduct فراهم آوردیم.

عدم بارگذاری جداولی که لازم نداریم

حال ما یک Dimension کالا (DimProduct) داریم که تمامی اطلاعات مربوط به کالا و زیر گروه کالا و گروه کالا را در خود جای داده است. این به این معنی است که ما در گزارشات خود نیازی به جداول DimProductSubcategory و DimProductCategory نداریم.

آیا می توانیم این دو جدول را حذف کنیم؟ خیر! چرا که این 2 جدول به عنوان جداول میانی استفاده شده اند. اما می توانیم از بارگذاری آن ها در RAM جلوگیری کنیم. چگونه؟ با غیر فعال کردن گزینه Enable Load برای این 2 جدول. با غیر فعال کردن این گزینه، این 2 جدول در ساخت DimProduct مشارکت می کنند اما خودشان در RAM بارگذاری نمی شوند. در واقع این 2 جدول نقش تغذیه کننده را برای جدول DimProduct ایفا می کنند.

در نهایت مدل ما از مدل Snowflake به مدل ستاره ای تبدیل می شود.

یکی از مهمترین مزایای این مدل این است که برای ایجاد سلسله مراتب (Hierarchy) شما را به ایجاد ستون های محاسباتی بی نیاز می کند. حتما می دانید که ایجاد ستون های محاسباتی باعث کاهش عملکرد مدل شما می شود. اگر در مورد انتخاب بین ستون های محاسباتی و Measure ها تردید دارید مطلب زیر را مطالعه نمایید.

تا کجا به Flat کردن جداول ادامه دهیم؟

Flat کردن جداول را بین جداولی انجام دهید که رابطه معنا داری با هم دارند. در واقع تا جایی عمل Join را انجام دهید که تمام اطلاعات یک موجودیت در یک Dimension قرار گیرد. به طور مثال Join کردن جداول کالا، زیر گروه کالا و گروه کالا به منظور ایجاد DimProduct. یا Join کردن جدول مشتری با جدول شهر و ایجاد جدول DimCustomer. توجه کنید هدف ما این است که حتی الامکان به مدل ستاره ای دست پیدا کینم.

جمع بندی

Flat کردن جداول و ایجاد یک Dimension برای هر موجودیت از ایجاد بسیاری از چالش ها جلوگیری می کند. باید تمام جداولی که اطلاعات کالا را دارند از رنگ و سایز و برند و کالا و گروه کالا و … در یک جدول قرار گیرند. همچنین باید جداول میزان تحصیلات، شهر و مشتری در هم ترکیب شده  و یک جدول DimCustomer را ایجاد کنند. رسیدن به مدل ستاره ای بسیاری از مشکلات را حل خواهد کرد.