Adatis BI Blogs

Row Level Security (RLS) using the Power BI Service

Following on from my last blog on RLS, I will now demonstrate how to initiate the roles created in Power BI Desktop.  Whilst the desktop version is great for testing security, you have to configure the report in the Power BI Service to enforce permissions.    Before doing anything, ensure you have published a report with RLS onto the Power BI Service.  If you would like to use my workbook as the example to follow, leave a comment below. Configure Power BI Service 1.       Navigate to the Datasets tab and click the ellipsis button (‘...’) on the ‘RLS Part 2’ dataset (or whatever you have called your published report).  The select the Security option.   2.       Let’s assign two email accounts to the users ‘Stephen’ and ‘Amy’.  These must use a work domain – Hotmail, Outlook, etc. accounts are not permitted with Power BI.  Click Add when you are happy.     3.       Ensure the RLS roles are saved.  You will end up with the following: 4.       The roles are now configured.  Both email accounts will only see data for the person they have been assigned to. NOTE:   You can add numerous email accounts to a specific role.  For this example, the roles are done by person, but you may want to add a whole department containing 10 people to a given role. Testing Roles using Power BI Service This is actually really simple.  Go back to the Security settings (shown above).  Click the Ellipsis button and select ‘Test as Role’. You will now only see sales data for the Sales Representatives that report to Stephen.  Conclusion RLS is now out of preview mode and released into General Availability.  Whilst the capabilities are still pretty new, the additional DAX layer over the GUI enables more complex security capabilities.  As with all features in Power BI, regular updates are to be expected – so if RLS currently isn’t fit for your scenario, it could be very soon. Recommended Reading  o   Reza Rad’s Blog - o   Power BI Community Blog - o   Devin Knight’s Blog -

Row Level Security in Power BI Desktop

In the June 2016 monthly Power BI release, Row Level Security (RLS) was introduced into Power BI desktop.  This is great news for people using the application, especially as the configuration is stored within the Power BI model.  Previously, you had to create the security in the web environment, which could easily be overwritten when publishing multiple times from a desktop workbook. In this blog, I will show you how to set up RLS in Power BI desktop and how to test it works. My example uses the AdventureWorksDW2014 database (download here), specifically applying permissions for a manager.  Each manager will only be able to see data for the Sales Representatives that report to them. NOTE:   This article assumes you have prior experience with creating reports and visuals in Power BI.  Please leave a comment if you would like a copy of the workbook. Report Setup From the AdventureWorksDW2014 database, import the the FactResellerSales table.  Then add another SQL dataset, pasting in the below query: SELECT EMP.EmployeeKey               ,EMP.FirstName + ' ' + EMP.LastName As SalesRepName               ,EMP.Title As SalesRepTitle               ,MGR.FirstName + ' ' + MGR.LastName As ManagerName               ,MGR.Title As ManagerTitle FROM   [dbo].[DimEmployee] EMP INNER JOIN               (                      SELECT EmployeeKey                                   ,FirstName                                   ,LastName                                   ,Title                      FROM   [dbo].[DimEmployee]               ) MGR ON     MGR.EmployeeKey = EMP.ParentEmployeeKey WHERE  EMP.Title = 'Sales Representative’ Create a basic bar chart and rep slicers – like shown below.  You should end up with 3 managers – Amy Alberts, Stephen Jiang and Syed Abbas. Create Roles To create a role, navigate to Modeling tab (at the top of the page) in Power BI Desktop. You will see a section named Security.  Click on Manage Roles. Next, we need to create a role for the three managers that were previously identified.  Follow the below annotation and necessary steps to create a specific role for Amy Alberts: 1.       Create a Role. 2.       Name the role as ‘Sales Manager Amy’. 3.       Click the ‘..’ on the QueryEmployee Table. 4.       Add filter (a DAX Expression). 5.       Choose [ManagerName], as we want to filter on the manager’s full name.  This defines what data they can see. 6.       Change the “Value” filter to “Amy Alberts”.  Only data containing Sales Representatives working for Amy will be shown in the report. 7.       Repeat steps 1-6 for Stephen Jiang and Syed Abbas, simply replacing the “Amy Alberts” filter with the applicable person’s name.  Click save to close down the RLS form.  You will end up with the following: Role Testing Navigate to the same Modeling tab and select View As Roles. Select ‘Sales Manager Amy’ and click OK.  You will notice only Amy’s Sales Representative’s show in the report. You can follow the same steps to impersonate a different manager or to remove the Roles filter completely. Conclusion The Row Level Security feature in Power BI Desktop makes it really easy to apply security around the data and what users can and cannot seen.  The ability to adopt DAX filter expressions through the UI enables the user to deploy an effective security model in a simple, easy manner. Currently, the Power BI desktop security in my example is not being used in a real life situation and the three managers can see all data in the online report.  Look out for the second part of my blog on RLS (coming soon), where I will bring this to life using the Power BI Service.  Tasks include tying emails to security roles/groups and actually impersonating a user to ensure they configured correctly.  Recommended Reading  o   Reza Rad’s Blog - o   Power BI Community Blog - o   Devin Knight’s Blog -

New Features in SQL Server 2016 – Part 1: Database Engine

In early May 2015, SQL Server 2016 was made available in Preview.  To download the CTP2 version, click here.  This blog will list all of the new Database Engine features coming next year.  In futures posts, I will pick a couple of the enhancements and demonstrate how they will benefit DBA’s, Developers and BI specialists.  In the meantime, I am aiming to give the SQL community a high level understanding of what is new in 2016 and help people think about how some of them could benefit their clients and stakeholders. Database Engine Feature Enhancements There are lots of new, exciting additions to the database engine in SQL Server 2016.  They can be found below.  I have picked out my favourite five and provided additional detail on them. · ·        Transact-SQL Enhancements o   Columnstore Indexes o   In-Memory OLTP o   Live Query Statistics o   Query Store o   Temporal Tables o   Backup to Microsoft Azure o   Managed Backup o   Trace Flag 4199 o   FOR JSON o   Always Encrypted o   PolyBase o   Stretch Database ·        Transact-SQL Enhancements ·        System View Enhancements ·        Security Enhancements o   Row-Level Security o   Dynamic Data Masking o   New Permissions o   Transparent Data Encryption (TDE) ·        High Availability Enhancements ·        Tools Enhancements Columnstore Indexes Whilst SQL Server 2014 offered some improvements on 2012, the 2016 version is taking Columnstore indexes to the next level.  In addition, Azure SQL Databases can incorporate almost every feature in a standard on premise database. 1.       A table can have one updateable nonclustered columnstore index. 2.       The nonclustered columnstore index definition now supports filtered conditions.  As a result, the performance impact pm am OLTP table will be minimal. 3.       An in-memory table can have one columnstore index. Previously, only a disk-based table could have a columnstore index. 4.       A clustered columnstore index can have one or more nonclustered indexes. Previously, the columnstore index did not support nonclustered indexes. 5.       Supports primary and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index. In-Memory OLTP The current In-Memory-OLTP offers up to 30x transactions.  In SQL Server 2016, you will also be able to apply it to more applications and benefit from increased concurrency.  In addition, you can use the new in-memory columnstore with in-memory OLTP, delivering 100x faster queries.    There are other benefits to the refined In-memory OLTP: 1.       Support ALTER operations for memory-optimized tables and natively compiled stored procedures. 2.       Support for natively compiled, scalar user-defined functions. 3.       Support for all Collations. 4.       Storage Improvements. a.       Ability to estimate memory requirements for memory optimised tables. b.       Once you determine the size, you need to provide disk space that is four times the size of durable, in-memory tables. 5.       Enhancements to transaction performance analysis reports. a.       Transaction performance collector in SQL Server Management Studio helps you evaluate if In-Memory OLTP. b.       Use the ‘Memory Optimization Advisor’ to help migrate table to in memory. 6.       Support for subqueries and query surface area in natively compiled stored procedures a.       E.g. BETWEEN, GROUP BY, ORDER BY, TOP, UPDATE, TRY/CATCH, etc. b.       The usual DISTINCT and ORDER BY do not work together. c.       Neither does combining TOP and PERCENT in a Select statement. System View Enhancements There are a number of views that will aid a developer, especially around security and query stats.  The enhancements are grouped into 3 sections. 1.       Row Level Security a.       sys.security_predicates – Returns a row for each security predicate in a database. b.       sys.security_policies – Returns a row for each security policy in the database. 2.       Query Store Catalog Views a.       7 new Query Store support views                                                                i.      sys.database_query_store_options                                                              ii.      sys.query_context_settings                                                            iii.      sys.query_store_plan                                                            iv.      sys.query_store_query                                                              v.      sys.query_store_query_text                                                            vi.      sys.query_store_runtime_stats                                                           vii.      sys.query_store_runtime_stats_interval 3.       Query Hints a.       MIN_GRANT_PERCENT b.      MAX_GRANT_PERCENT Row-Level Security (RLS) Microsoft’s explanation of row-level security reads: “Row level security (RLS) introduces a flexible, centralised, predicate-based evaluation that considers metadata or any other criteria the administrator determines as appropriate. This is used as a criterion to determine whether or not the user has the appropriate access to the data based on user attributes. “ This can be translated to something a little easier to ingest: 1.       Ability to control access to rows in a database table based on the characteristics of the user executing a query. a.       E.g. Group Memberships, such as company department or job status. 2.       Simplifies the design and coding of application security. 3.       Restriction logic is found in the database layer, as opposed to an application.  It helps limit the various levels of required security and therefore, increases reliability. 4.       Use ‘CREATE SECURITY POLICY’ T-SQL to set up RLS.   High Availability Enhancements If you are not familiar with High Availability in SQL Server 2012/14, click here for a quick introduction.  For everyone else, the below enhancements greatly enrich this feature: 1.       Load-balancing is now possible across a set of read-only replicas.  Previously, it used to always direct connections to the first available read-only replica. 2.       There are now 3 replicas that support automatic failover cluster.  It used to be 2. 3.       Group Managed Service Accounts now facilitate AlwaysOn Failover Clusters. * 4.       AlwaysOn Availability Groups can be configured to failover when a database goes offline.  You must change the setting ‘DB_FAILOVER’ option to ON.   *             You will need to update Windows Server R2 to avoid any downtime after a password change. Future Blogs As the Database Engine contains the majority of changes, I have included the other features in a series of other blog posts.  Click the links below to access them. 1.       Part 2 - Analysis Services - TBC 2.       Part 3 - Reporting Services - TBC 3.       Part 4 - Integration Services - TBC References For more information on all of the new SQL Server 2016 features, the below resources/blogs are highly recommended. ·        What’s New in SQL Server 2016 (Official Microsoft) - ·        T.K. Ranga Rengarajan’s SQL Server Blog - ·        Jen Underwood SQL Server BI Blog -