Introduction
Recently I posted a blog titled “How to implement Analysis Services Dynamic Security based on Microsoft CRM User Access Rights.” That post talked about how to implement the back-end structure needed to put it in place but didn’t show the results of all that hard work.
The Dynamic Row Level Security (RLS) that we set up inside Analysis Services will work within reports and dashboards built inside Reporting Services, Power BI, and Excel. Power BI has other ways of deploying RLS when not using an Analysis Services connection. You can learn more about Dynamics security and configuration in Power BI.
The Result
We built our Dashboard in Power BI Designer and published the Dashboard to our End-Users. One of the End-Users, “Tom,” has access to 558 CRM Customer account within the US. When he accesses the Dashboard, his can view results that he has privileges to. (See Figure 1 below)
Figure 1 – Tom’s Access View
If “Tom” tries to access an area that he doesn’t have privileges to, say Canada, the dashboard allows him to select the filter and changes the chart(s) results to show no data. (See Figure 2 below)
Figure 2 – Tom’s view with Canada selected
Additionally, the chart(s) change based on the End-User that’s accessing the dashboard. In Figure 3 below, I show an Administrative User accessing the dashboard and the result shown to them when selecting the “Canada” filter.
Figure 3 – Administrative user view of Canadian Sales
Conclusion
I hope this helps you with your future Analysis Services development and Dashboard projects. Please contact KTL Solutions to help kick starting your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
[avatar user=”bcrowell” size=”thumbnail” align=”left” /]BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360. His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University. Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.
