ساختار Parent Child Hierarchies در Power BI
ساختارهای سلسله‌مراتبی والد-فرزندی (Parent-child hierarchies) اغلب برای نمایش ساختارهایی مانند نمودار حساب‌ها، فروشگاه‌ها، نمایندگان فروش و موارد مشابه استفاده می‌شوند.
این نوع سلسله‌مراتب، به دلیل داشتن عمق متغیر، روش خاصی برای ذخیره‌سازی ساختار سلسله‌مراتبی دارند.
در این الگو (pattern)، نحوه استفاده از سلسله‌مراتب والد-فرزندی برای نمایش مقادیر بودجه، عملکرد واقعی و پیش‌بینی‌شده در یک گزارش را نشان می‌دهیم. در اینجا هم از یک نمودار حساب‌ها و هم از یک سلسله‌مراتب جغرافیایی استفاده شده است.
مقدمه
در الگوی والد-فرزندی، سلسله‌مراتب توسط ستون‌های جداگانه در جدول منبع داده اصلی تعریف نمی‌شود. بلکه این سلسله‌مراتب بر پایه‌ی ساختاری استوار است که در آن، هر گره (node) از سلسله‌مراتب به کلید گره والد خود مرتبط است.
به‌عنوان مثال، شکل 1 اولین ردیف‌های یک سلسله‌مراتب والد-فرزندی را نشان می‌دهد که ساختار جغرافیایی فروش را تعریف می‌کند.
بر اساس این ساختار داده، ما باید یک سلسله‌مراتب نمایش دهیم که در آن Contoso United States در زیرمجموعه‌ی Contoso North America قرار گیرد، همانطور که در شکل ۲ نشان داده شده است.
الگوی والد-فرزندی نوعی خود-پیوستگی (self-join) از جدول شامل موجودیت‌ها را پیاده‌سازی می‌کند. با این حال، در مدل Tabular این نوع خود-پیوستگی به‌صورت مستقیم پشتیبانی نمی‌شود.
به دلیل ویژگی‌های خاص این ساختار، سلسله‌مراتب والد-فرزندی ممکن است دارای عمق متغیر باشد؛ یعنی تعداد سطوح از بالای سلسله‌مراتب تا پایین آن می‌تواند بسته به مسیر پیمایش، متفاوت باشد.
به همین دلایل، برای پیاده‌سازی سلسله‌مراتب والد-فرزندی باید از تکنیکی استفاده کرد که در این الگو توضیح داده شده است.
سلسله‌مراتب‌های والد-فرزندی معمولاً در نمودار حساب‌ها (Chart of Accounts) کاربرد دارند.
در این حالت، هر گره (node) همچنین علامتی را مشخص می‌کند که هنگام تجمیع مقادیر در گره والد باید استفاده شود.
برای مثال، نمودار حساب‌های شکل ۳ نشان می‌دهد که هزینه‌ها از مجموع کم می‌شوند – با وجود اینکه همه مقادیر به‌صورت مثبت نمایش داده شده‌اند – در حالی که درآمدها به مجموع اضافه می‌شوند.
عبارات DAX که داده‌ها را در یک سلسله‌مراتب والد-فرزندی تجمیع می‌کنند، باید علامت (sign) مورد استفاده در سطوح پایین‌تر هر گره را نیز در نظر بگیرند تا محاسبات درست انجام شوند.
الگوی پایه والد-فرزندی
در مدل Tabular، نه سلسله‌مراتب‌هایی با عمق متغیر و نه self-join (پیوست به خود جدول) به‌طور مستقیم پشتیبانی می‌شوند.
بنابراین، اولین گام برای مدیریت سلسله‌مراتب‌های والد-فرزندی این است که ساختار سلسله‌مراتبی را به یک سلسله‌مراتب «تخت‌شده» (flattened) تبدیل کنیم.
در این نوع تبدیل، ما باید ساختار سلسله‌مراتبی را به یک ساختار منظم تبدیل کنیم که در آن، برای هر سطح ممکن از سلسله‌مراتب، یک ستون جداگانه وجود دارد.
به عبارت دیگر، باید از ساختار داده‌ای مانند شکل ۴ (که فقط شامل سه ستون مورد نیاز برای تعریف یک سلسله‌مراتب والد-فرزندی است)
به سمت ساختاری مشابه شکل ۵ برویم، که در آن سلسله‌مراتب در قالب چندین ستون مجزا – هر یک نمایانگر یک سطح – نمایش داده شده است.
برای گسترش کامل سلسله‌مراتب والد-فرزندی در این مثال، چهار سطح مورد نیاز است.
شکل ۵ نشان می‌دهد که برای هر سطح از سلسله‌مراتب، یک ستون مجزا در نظر گرفته شده است که با نام‌های Level1 تا Level4 نام‌گذاری شده‌اند.
تعداد ستون‌های مورد نیاز برای این نوع ساختار وابسته به داده‌ها است. بنابراین، می‌توان تعداد سطوح بیشتری را نیز در نظر گرفت تا در آینده، در صورت اضافه شدن سطوح جدید به سلسله‌مراتب، نیازی به تغییر اساسی در مدل نباشد.
گام اول برای پیاده‌سازی سلسله‌مراتب والد-فرزندی، ایجاد یک ستون فنی (technical column) به نام EntityPath است. این ستون با استفاده از تابع PATH در DAX ساخته می‌شود.
ستون EntityPath مسیر کامل برای رسیدن به گره‌ای که با ردیف جدول مطابقت دارد را در خود نگه می‌دارد، همانطور که در شکل ۶ نشان داده شده است. این ستون فنی برای تعریف ستون‌های Level مفید است.
کد برای تمام ستون‌های Level مشابه است و تنها در مقداری که به متغیر LevelNumber اختصاص داده می‌شود، تفاوت دارد. این کد برای ستون Level1 است:
ستون‌های دیگر نام متفاوتی دارند و مقداری متفاوت به LevelNumber اختصاص داده می‌شود که با موقعیت نسبی سطح آن‌ها در سلسله‌مراتب مطابقت دارد. پس از تعریف تمام ستون‌های Level، این ستون‌ها را مخفی کرده و یک سلسله‌مراتب منظم در جدول ایجاد می‌کنیم که شامل تمامی آن‌ها باشد – یعنی تمام ستون‌های Level. تنها نمایش این ستون‌ها از طریق یک سلسله‌مراتب مهم است تا اطمینان حاصل شود که کاربر هنگام پیمایش در گزارش، از آن‌ها به‌درستی استفاده می‌کند.
اگر این سلسله‌مراتب به‌طور مستقیم در یک گزارش استفاده شود، هنوز نتیجه بهینه‌ای ارائه نمی‌دهد. در واقع، تمام سطوح همیشه نمایش داده می‌شوند، حتی اگر ممکن است مقداری در آن‌ها وجود نداشته باشد. شکل ۷ یک ردیف خالی را زیر Contoso Asia Online Store نشان می‌دهد، حتی اگر ستون Level4 برای آن گره خالی باشد – که به این معناست که آن گره تنها می‌تواند سه سطح را گسترش دهد، نه چهار سطح.
برای مخفی کردن ردیف‌های غیرضروری، باید برای هر ردیف بررسی کنیم که آیا سطح جاری توسط گره بازدید شده در دسترس است یا خیر. این کار با بررسی عمق هر گره قابل انجام است. ما به یک ستون محاسباتی در جدول سلسله‌مراتب نیاز داریم که عمق گره را برای هر ردیف تعریف کند:
ما به دو معیار (measure) نیاز داریم:
EntityRowDepth: این معیار عمق حداکثری گره جاری را باز می‌گرداند.
EntityBrowseDepth: این معیار عمق جاری ماتریس را با استفاده از تابع ISINSCOPE باز می‌گرداند.
در نهایت، از این دو معیار برای خالی کردن نتیجه استفاده می‌کنیم اگر EntityRowDepth از عمق مرور بیشتر باشد.
گزارشی که با استفاده از معیار Total Base به‌دست آمده است، دیگر شامل ردیف‌هایی با توضیحات خالی نمی‌شود، همانطور که در شکل زیر نشان داده شده است.
همان الگو باید برای هر مژری که ممکن است با استفاده از سلسله‌مراتب والد-فرزندی گزارش شود، اعمال شود.
سلسله‌مراتب نمودار حساب‌ها
الگوی نمودار حساب‌ها یک تغییر از الگوی پایه سلسله‌مراتب والد-فرزندی است، جایی که سلسله‌مراتب همچنین برای انجام محاسبات استفاده می‌شود. هر ردیف در سلسله‌مراتب به‌عنوان درآمد، هزینه یا مالیات علامت‌گذاری می‌شود. درآمدها باید جمع شوند، در حالی که هزینه‌ها و مالیات باید از مجموع کسر شوند. شکل ۹ محتوای جدول شامل اقلام سلسله‌مراتبی را نشان می‌دهد.
پیاده‌سازی مشابه الگوی والد-فرزندی است، به‌طوری که محاسبات بر اساس AccountType گروه‌بندی می‌شوند و علامت مناسب به محاسبات بستگی به مقدار AccountType اعمال می‌شود.
مژر Total می‌تواند از هر دو سلسله‌مراتب والد-فرزندی استفاده کند: سلسله‌مراتبی که در جدول Entity تعریف شده است – که در مثال قبلی نشان داده شد – و سلسله‌مراتبی که در جدول Account تعریف شده است، که موضوع این بخش است.
فرمول در Total نتیجه صحیح را برای هر گره از سلسله‌مراتب باز می‌گرداند. با این حال، در این نوع گزارش‌ها معمولاً درخواست می‌شود که اعداد به‌صورت مثبت نمایش داده شوند، حتی اگر هزینه‌ها باشند.
برای برآورده کردن این نیاز، می‌توان علامت نتیجه را در سطح گزارش تغییر داد. معیار Total No Signs محاسبات را به این شکل پیاده‌سازی می‌کند: ابتدا علامت مورد استفاده برای گزارش را تعیین می‌کند و سپس علامت نتیجه را تغییر می‌دهد تا هزینه‌ها به‌صورت اعداد مثبت نمایش داده شوند، حتی اگر به‌صورت داخلی به‌عنوان اعداد منفی مدیریت شوند.
گزارشی که با استفاده از Total No Signs به‌دست آمده است، در شکل زیر قابل مشاهده است.
الگوی نشان‌داده‌شده در بالا به‌خوبی کار می‌کند اگر نمودار حساب‌ها شامل ستون AccountType باشد که هر مورد را به‌عنوان درآمد یا هزینه تعریف می‌کند.
گاهی اوقات، نمودار حساب‌ها روش دیگری برای تعریف علامت مورد استفاده دارد. برای مثال، ممکن است ستونی وجود داشته باشد که علامت مورد استفاده هنگام تجمیع یک حساب به والد آن را تعریف کند. این حالت مربوط به ستون Operator است که در شکل زیر نشان داده شده است.
در این حالت، کد برای نویسنده پیچیده‌تر می‌شود. ما به یک ستون برای هر سطح از سلسله‌مراتب نیاز داریم که بیان کند هر حساب چگونه باید زمانی که در هر سطح سلسله‌مراتب تجمیع می‌شود، نمایش داده شود. یک حساب می‌تواند در یک سطح با علامت مثبت تجمیع شود، اما در سطح دیگری با علامت منفی.
این ستون‌ها باید از پایین سلسله‌مراتب ساخته شوند. در این مثال، ما به هفت ستون نیاز داریم زیرا هفت سطح وجود دارد. هر ستون علامتی را نشان می‌دهد که باید هنگام تجمیع آن مورد خاص از سلسله‌مراتب در سطح مورد نظر استفاده شود. شکل زیر نتیجه این هفت ستون را در این مثال نشان می‌دهد.
برای مثال، ردیف‌های با AccountKey 4 و 5 را بررسی کنید: حساب 4 (درآمد فروش) باید زمانی که در سطوح 1، 2، 3 و 4 تجمیع می‌شود، جمع شود، در حالی که در سطوح دیگر قابل مشاهده نیست. حساب 5 (هزینه کالاهای فروخته‌شده) باید زمانی که در سطح 4 تجمیع می‌شود جمع شود، اما باید زمانی که در سطوح 1، 2 و 3 تجمیع می‌شود، از مجموع کسر گردد.
فرمول DAX که علامت را در هر سطح محاسبه می‌کند، از دقیق‌ترین سطح شروع می‌شود – سطح 7 در مثال ما. در این سطح دقیق‌ترین، علامت مورد استفاده فقط عملگر است که به +1 یا -1 تبدیل می‌شود، به‌منظور راحتی در محاسبات بعدی:
تمام ستون‌های دیگر (از سطح 1 تا سطح 6) الگوی مشابهی را دنبال می‌کنند، با این حال برای هر سطح، عبارت DAX باید علامت سطح دقیق‌تر و مجاور (که در متغیر PrevSign ذخیره شده است) را در نظر بگیرد و نتیجه را زمانی که آن سطح علامت “-” را نشان می‌دهد، معکوس کند، همانطور که در ستون سطح 6 نشان داده شده است.
زمانی که ستون‌های سطح آماده شدند، مژر Signed Total که مجموع را با علامت‌های سفارشی محاسبه می‌کند، به شکل زیر است:
می‌توانیم نتیجه‌ی این مژر نهایی Signed Total را با معیار قبلی Total در شکل زیر مقایسه کنیم.
دو فرمول برای یک node مشابه در سلسله‌مراتب، علامت متفاوتی را باز می‌گردانند.
مقدار مربوط به “Internet” در معیار Total منفی است، زیرا یک هزینه محسوب می‌شود. با این حال، در Signed Total همان ردیف دارای عددی مثبت است و تنها زمانی منفی می‌شود که از طریق گره Expense عبور می‌کند؛ چرا که آن گره با علامت منفی به والد خود تجمیع می‌شود.
الگوی امنیتی برای سلسله‌مراتب والد-فرزندی
یکی از نیازهای رایج امنیتی برای سلسله‌مراتب والد-فرزندی این است که دیدپذیری تنها به یک گره (یا مجموعه‌ای از گره‌ها) و تمام فرزندان آن محدود شود. در این سناریو، تابع PATHCONTAINS بسیار مفید است.
با اعمال عبارت زیر به یک نقش امنیتی (Security Role) روی جدول Account، دیدپذیری فقط به گره‌ای که به‌عنوان آرگومان دوم PATHCONTAINS ارائه شده محدود می‌شود. به این ترتیب، تمام فرزندان آن گره نیز برای کاربر قابل مشاهده می‌شوند، زیرا گره مورد نظر (برای مثال ۲ که مربوط به “Income” است) بخشی از مقدار AccountPath در همه گره‌های فرزند نیز هست:
اگر از ستون AccountKey برای محدود کردن دیدپذیری استفاده کنیم، در نهایت دیدپذیری تنها به یک ردیف محدود می‌شود و کاربر فرزندان آن گره را نخواهد دید. اما با استفاده از ستون مسیر (Path)، می‌توانیم به‌راحتی چندین ردیف را انتخاب کنیم، زیرا همه گره‌هایی که در مسیر عبوری از گره فیلترشده قرار دارند، در نظر گرفته می‌شوند.
زمانی که نقش امنیتی فعال باشد، کاربر فقط گره‌ها (و مقادیر مربوط به آن‌ها) را می‌بیند که در درختی قرار دارند که از گره Income شروع می‌شود، همانطور که در شکل زیر نشان داده شده است.
گره‌های بالاتر از گره Income (یعنی Level3) دیگر سایر گره‌های فرزند را در معیار Total لحاظ نمی‌کنند. اگر این موضوع باعث برداشت نادرست در گزارش شود، بهتر است سطوح اولیه را از گزارش حذف کنید (در این مثال Level1 و Level2) یا توضیحات متفاوتی برای گره‌های این سطوح ارائه دهید تا نتیجه بهتر و واضح‌تری نمایش داده شود.
همچنین شایان ذکر است که نقش امنیتی‌ای که با استفاده از PATHCONTAINS تعریف می‌شود، ممکن است عملکرد را کند کند، به‌ویژه اگر سلسله‌مراتب شامل هزاران گره باشد. عبارت موجود در نقش امنیتی باید برای هر گره از سلسله‌مراتب در زمان اتصال کاربر نهایی ارزیابی شود، و تابع PATHCONTAINS در صورتی که روی هزاران ردیف یا بیشتر اعمال شود، می‌تواند پردازشی سنگین باشد.
دیدگاهتان را بنویسید