سطح دسترسی داده‌ها با SSAS در Power BI

این آموزش گام‌های مورد نیاز را برای پیاده سازی سطح دسترسی داده‌ها با SSAS عنوان می‌کند و نشان می‌دهد که چگونه از آن در یک گزارش Power BI استفاده کنید. این گام‌ها طوری طراحی شده‌اند که به راحتی اجازه می‌دهند آنها را دنبال کنید و بر روی یک مجموعه داده‌ای نمونه تست نمایید.

در حین این آموزش، گام‌های پیش رو با جزئیات شرح داده شده‌اند:

• ساخت یک جدول امنیتی در پایگاه داده AdventureworksDW2012
• ایجاد مدل جدولی با Fact ها و جداول Dim ضروری
• تعریف قوانین و مجوزها برای کاربران
• قرار دادن مدل در Analysis Services
• استفاده از Power BI برای تولید گزارشی که داده‌های مورد نیازِ کاربرِ دارای دسترسی به گزارش را فراهم کند.
• قرار دادن گزارش در Power BI Service
• ساخت یک داشبورد جدید بر اساس گزارش
• و در نهایت به اشتراک گذاری داشبورد با همکاران

برای دنبال کردن آموزش، باید پایگاه داده AdventureworksDW2012 را از اینجا دانلود کنید.

وظیفه اول: ساخت جدول امنیتی کاربر و تعریف روابط داده‌ای

مقالات زیادی در رابطه با چگونگی تعریف سطح دسترسی داده‌‌ها با مدل جدولی (SQL Server Analysis Services (SSAS وجود دارد. گام‌های زیر را دنبال کنید:

1. در مجموعه داده‌ای AdventureworksDW2012، جدول DimUserSecurity را ایجاد کنید. ما از (SQL Server Management Studio (SSMS برای ساخت جداول استفاده می‌کنیم.

2. به محض اینکه جدول ساخته و ذخیره شد، باید ارتباط میان جدول DimUserSecurity ستون SalesTerritoryID با جدول DimSalesTerritory ستون SalesTerritoryKey را مشخص کنیم. این کار می‌تواند با کلیک راست بر روی جدول DimUserSecurity و انتخاب Design و سپس مسیر Table Designer -> Relationsips صورت پذیرد.

3. جدول را ذخیره کنید، سپس تعدادی سطر از اطلاعات کاربری وارد نمایید به این صورت که روی نام جدول کلیک راست کرده و گزینه Edit Top 200 Rows را بزنید. جدول همانند تصویر زیر می‌شود:

4. سپس یک inner join با جدول DimSalesTerritory انجام دهید تا جزئیات ناحیه مرتبط با کاربر را داشته باشیم. کد زیر، دستور inner join را مشخص کرده و در تصویر نتیجه کد نمایش داده شده است.

select b.SalesTerritoryCountry, b.SalesTerritoryRegion, a.EmployeeID, a.FirstName, a.LastName, a.UserName from [dbo].[DimUserSecurity] as a join  [dbo].[DimSalesTerritory] as b on a.[SalesTerritoryKey] = b.[SalesTerritoryID]

5. توجه کنید که تصویر بالا نشان می‌دهد که مثلا کدام کاربر مسئول کدام ناحیه فروش است. این داده‌ها بخاطر ارتباطی که در گام دوم ایجاد کردیم، نمایان شده است. همچنین Jon Doe مسئول ناحیه فروش استرالیا است که در وظایف بعدی از این اطلاعات استفاده خواهیم کرد.

وظیفه دوم: ساخت مدل جدولی با Fact ها و جداول Dim

1. پس از آن که انبار داده رابطه‌ای شما در محل مناسب قرار گرفت، نوبت به تعیین مدل جدولی می‌رسد. مدل با (SQL Server Data Tools (SSDT ایجاد می‌شود.
2. تمام جدول‌های مورد نیاز را به مدل وارد کنید.

3. در قدم بعدی باید یک نقش به نام SalesTerritoryUsers با مجوز Read ایجاد کنید. این کار با کلیک بر روی منوی Model، و سپس انتخاب Roles انجام می‌شود. در Role Manager گزینه New را بزنید.
4. در تب Members، کاربرانی را که در جدول DimUserSecurity تعریف کردیم (وظیفه اول – گام 3)، اضافه نمایید.

5. بعد توابع مناسب هر دو جدول DimSalesTerritory و DimUserSecurity را همانند تصویر، زیر تب Row Filters اضافه کنید.

6. در این گام، از تابع LOOKUPVALUE برای برگرداندن مقادیر ستونی که نام کاربری ویندوز برابر با نام کاربری دریافت شده از تابع USERNAME است، استفاده می‌کنیم. کوئری‌ها می‌توانند محدود شوند که مقدارهای برگردانده شده از LOOKUPVALUE با مقادیر همان جدول یا جدول مرتبط، یکی باشند. در ستون DAX Filter، فرمول زیر را بنوسید:

=DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])

در این فرمول، تابع LOOKUPVALUE تمام مقادیر برای [DimUserSecurity[UserName را برمی‌گرداند که [DimUserSecurity[SalesTerritoryID همان نام کاربر وارد شده به ویندوز است و [DimUserSecurity[SalesTerritoryID همان [DimSalesTerritory[SalesTerritoryKey است.

مجموعه برگردانده شده، برای محدود کردن سطرهای نشان داده شده در DimSalesTerritory استفاده می‌شوند. تنها سطرهایی نمایش داده می‌شوند که SalesTerritoryKey آنها در مجموعه برگردانده شده از LOOKUPVALUE موجود باشد.

7. برای جدول DimUserSecurity، در DAX Filter، فرمول زیر را بنویسید:

=FALSE()

این فرمول مشخص می‌کند که تمام ستون‌ها‌ تحت شرایط بولی False حذف می‌شوند؛ در نتیجه، هیچ ستونی برای جدول DimUserSecurity قابل کوئری زدن نیست.

8. اکنون باید مدل را پردازش و منتشر کنیم. (به این لینک مراجعه شود)

وظیفه سوم: اضافه کردن منابع داده‌ای در Data Gateway On-premises

1. به محض اینکه دیتا مدل شما آماده استفاده شد، نیاز دارید تا یک اتصال منبع داده‌ای به on-premises Analysis Services tabular server خود در پورتال Power BI اضافه کنید.
2. برای اینکه به Power BI Service اجازه دسترسی به سرویس تحلیل داخلی (on-premises analysis service) خود را بدهید، باید On-premises data gateway را در محیط خود نصب و تنظیم کنید.
3. پس از پیکربندی صحیح gateway، باید اتصال برقرار کنید. مطلب زیر را در این رابطه ببینید.

https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-ssas

4. با تکمیل مراحل قبل، gateway کاملا پیکربندی شده و برای تعامل با منبع داده‌ای سرویس تحلیلی داخلی، آماده است.

وظیفه چهارم: ساخت گزارش

1. در Power BI مسیر زیر را بروید: Get Data -> Database
2. از لیست منابع داده‌ای، SQL Server Analysis Services Database را انتخاب کنید و Connect را بزنید.

3. فرم را با اطلاعات مورد نیاز پر کنید و بعد از انتخاب Connect Live، دکمه OK را بزنید.

4. مشاهده می‌کنید که مدل در نمونه داده Analysis Services برقرار شده است. دکمه OK را بزنید.

5. اکنون Power BI تمام فیلدهای موجود را در سمت راست بخش Report نشان می‌دهد.
6. در بخش Fields، ستون SalesAmount را از جدول FactInternetSales و بعد SalesTerritoryRegion را از جدول SalesTerritory انتخاب کنید.
7. برای ساده نگه داشتن این گزارش، ستون دیگری اصافه نمی‌کنیم. برای رسیدن به یک نمایش معنی ‌دار از داده‌ها، نمودار را به Donut chart تغییر می‌دهیم.

8. پس از آماده شدن گزارش، می‌توانید در پورتال Power BI آن را منتشر کنید. از تب Home مورد Publish را انتخاب کنید.

وظیفه پنجم: ساخت و انتشار یک داشبورد

1. هم اکنون گزارش شما در Power BI Service قرار دارد. سناریوی امنیت مدلی که در گام‌های قبلی ایجاد کردیم، ثابت می‌شود.

نقش Sales Manager با نام Sumit می‌تواند داده‌ها را از تمام نواحی فروش مشاهده کند. پس او این گزارش را می‌سازد و منتشر می‌کند. در ادامه، او داشبورد TabularDynamicSec را بر اساس آن گزارش می‌سازد.

2. اکنون Sumit داشبورد را با همکار خود Jon Doe که مسئول فروش استرالیا است، به اشتراک می‌گذارد.

3. پس از آنکه Jon به Power BI Service وارد شود و داشبورد را ببیند، باید تنها مجاز به دیدن میزان فروش ناحیه‌ای که خود مسئول آن است، یعنی استرالیا، باشد.

4. تبریک می‌گویم! سطح دسترسی داده‌‌ها که شما ایجاد نمودید به درستی کار می‌کند. Power BI از قابلیت effectiveusername استفاده می‌کند تا اطلاعات و دسترسی‌های کاربر را برای اجرای کوئری‌ها، ارسال کند.

وظیفه ششم: فهم آنچه پشت صحنه اتفاق می‌افتد

این قسمت فرض می‌کند که شما با SQL Profiler آشنایی دارید، زیرا باید یک پیمایش بوسیله آن بر روی نمونه جدولی SSAS خود انجام دهید.

1- به محض اینکه Jon به داشبورد در Power BI Service دسترسی پیدا می‌کند، نشستی برای او آغاز می‌شود. می‌توانید ببینید که نقش salesterritoryusers اثر بلافاصله‌ای با نام کاربری jondoe@moonneo.com می‌گذارد.

2- بر اساس درخواست نام کاربری مؤثر، Analysis Services درخواست را تبدیل به دسترسی‌های واقعی moonneo\jondoe، پس از کوئری زدن در Local Active Directory، می‌کند. بر اساس همین مجوز‌ها و دسترسی‌هایی که برای کاربر ارسال می‌شود، Analysis Services داده‌هایی که کاربر به آنها دسترسی دارد را به او برمی‌گرداند.

3- اگر فعالیت‌های دیگر بر روی داشبورد انجام شود، مثلا اگر Jon بخواهد از داشبورد به گزارش‌های زیرین آن برود، با SQL Profiler، شما یک کوئری که به Analysis Services برگردانده شده را بعنوان DAX Query می‌بینید.

4- شما همچنین می‌توانید ببینید که DAX Query، برای جمع آوری داده مورد نیاز گزارش، اجرا می‌شود.

EVALUATE

  ROW(

    “SumEmployeeKey”, CALCULATE(SUM(Employee[EmployeeKey]))

  )

ملاحظات:

1) سطح دسترسی داده‌‌ها در Power BI تنها تحت Live Connection امکان پذیر است.

2) هر تغییری در داده‌ها، پس از پردازش مدل، لحاظ شود، فورا برای کاربر در دسترس قرار می‌گیرد.