Categories
Datawarehouse Power BI

Bringing Security where data lives

The following is a series of articles that explain how we can use Row Level Security (RLS) and Active Directory across all BI technologies, database and tools in the corporate environment.

The goal is to create an integrated and a comprehensive data access control across multiple database technologies using different BI Tools or Apps.

SQL Server 2016 and Power BI started to support Row-Level-Security last year. RLS was already supported by SQL Server Analysis Services (SSAS) with Tabular and Multidimensional for some time.

The following picture shows some BI Tools and Databases used in the Microsoft BI Ecosystem.

On the top we have BI Tools used for authoring, publishing and hosting reports like:

  • Excel, number one BI Tool in the world, which can get data from a large variety of data sources and use it in Excel with Power Pivot or Power Query.
  • Report Builder to author printed paginated reports that can be share and published using the in SSRS Portal.
  • Power BI Desktop to author interactive visualizations with analytics capabilities.
  • Power BI Service, which is the portal that host Dashboards and Power BI reports.
  • We also can have Web Applications using Microsoft .NET MVC or Web Forms with Entity Framework that connect to any of these data sources.

On the bottom we have a variety of databases:

  • SQL Server Relational database, which could be an online transactional processing (OLTP) databases or Online analytical processing (OLAP) database, when use it as a data warehouse.
  • SQL Server Analysis Services (SSAS) Tabular Mode
  • SQL Server Analysis Services (SSAS) Multidimensional Mode
  • Power BI Imported Datasets, which are very similar to Tabular databases, but hosted on the cloud (Power BI Service) instead of on premise.

Traditionally, BI applications used to implement all rules regarding the data security within the application. For example we can have a BI App that implement security that allow users or group of users to access only to some specific set of data. The problem with this approach is it does not allow to reuse this security among other BI Tools. For example we can have security implemented in SSRS for folders and report level permissions or connections. Even though this SSRS security cannot be reused by Power BI nor Excel.

The best place to implement the security is right within the database, where the security can be centralized and maintained in only one place. On this way, any BI Tool or any App that uses the database, will also use the implemented security without the need to implement security in every single Web App or Tool.

The following picture shows a comparison of a database and application features. Business Intelligence and Security used to be common responsibilities of and application. Now databases have more powerful features in terms of business intelligence and security and these responsibilities can be implemented now in the database.

In order to implement a centralized and reusable security, we have to use these two security features available in all databases (Relational SQL, SSAS Tabular, SSAS Multidimensional):

  • Row Level Security (RLS) and
  • Active Directory (AD) Groups.

Implementing security in the database using RLS and AD give us the flexibility to use any BI tool that can connect to our database using an Active Directory account and our database will be protected using centralized security rules implemented right in the database.

In the following articles, I will implemented RLS and AD security across all these technologies and all BI tools. In these articles I will be share code samples, I’ll show How to unit test the security and also How to troubleshoot the security.

The following list is the series of articles to be publish related with this topic:

SQL Server 2016 On-Premise with Row-Level Security with Active Directory (Part I)

SQL Server 2016 On-Premise with Row-Level Security with Active Directory (Part II)

SSAS 2016 Tabular On Premise with Row-Level Security and Active Directory.

Client App Security (MVC, Web Forms, Web API) with SQL 2016 RLS and Active Directory

Power BI Security with Active Directory Synch and On-Premise Data Gateway.

Power BI using SSAS Tabular with Row Level Security.

Power BI using SSAS Multidimensional with Row Level Security.

Power BI – Imported Data with Row Level Security.

Power BI – Direct Query with Row Level Security.

Power BI: Integrate Power BI dashboard with a web application.

Categories
Power BI

Table or Matrix for Side by Side Column Comparison with columns with different formats in the same column.

Note: This article was updated with a new formatting feature released. See the updated version of this article:

http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-formats-in-the-same-column-updated

 

Note: As of now, April 2017, Power BI does not support different formats for the same column in a Table or a Matrix. This article shows a work around to achieve different formats for the same column. This work around can be applied to a Table or to a Matrix Report Visual.

You can download this Power BI desktop file from this link: https://1drv.ms/u/s!AjlEiXFjD5fu2SehP8jKkcpwJacf

There are many cases where we need to use a table or a matrix and we need to specify different formats in the same column. Showing the format with the numeric values makes the report easier to read than a simple number with no units.

In the case of this financial report below, we can see three different formats in the same column:

Currency ($), Currency and Thousand ($K), and Percent (%).

Another example is a side by side comparison of products. In the case below, we have different formats like: text, mAh, decimal number, Whole number and Amp formats, all of them in the same column.

https://media.nngroup.com/media/editor/2017/02/24/amazon-powerbanks-2.png

I’ll show you this solution based on financial sample data downloaded from:

https://powerbi.microsoft.com/en-us/documentation/powerbi-sample-download-the-financial-sample-workbook/

This spreadsheet will be imported using Power BI desktop and you have to choose the worksheet: financial. Notice that this solution works for both: Power BI imported mode and live connection mode to SSAS Tabular. In the case of SSAS Tabular, you would have to import the excel spreadsheet in the Visual Studio Project.

I created a flag URL for the image, which is not part of the objective for this article. Even though, I’ll show you the calculated column formula created in the Financial Table:

 Flag = "https://image.ibb.co/" & SWITCH([Country],"France","d4vwba/fr.png","Mexico","h8uHhF/mx.png","Germany","nGmV2F/de.png","Canada","dsjuUv/ca.png","United States of America","c023NF/us.png")

The following table was easy to create. Each country has a row and we can do a row by row comparison.

Even though, this is not what we are looking for. We are trying to get a side by side column comparison having all country fields in one column. We can create a matrix moving the fields to the matrix sections: Column, Rows and Values as shown below:.

This is the Matrix Report version, after changing the flag and Country fields to the Column section in the Matrix. All fields under the configured Values section are shown as separate columns.

What I really want is only one column per country and also the columns header in the left side of the table as the very first column.

There are few challenges we have to overcome here:

  1. In Power BI, the visual table and matrix do not support custom value formatting on rows. We have different formats in the same column for the report we are trying to create:
  2. The Flag field has Image URL format.
  3. The Country field has Text format.
  4. The Unit Sold field has a Whole Number format.
  5. The Gross Sales and Profit field have Currency format.
  6. The Gross Profit Margin field has Percentage format.
  7. The order should be specific and we should have control over it. Meaning, if I need a different order in the rows, I should able to change it.

The Solution

This are the steps we need to create a comparison report with only one column per country,

  1. I need to create a ReportTable in Power Query. If you are using import mode in Power BI Desktop, you can use the Enter Data option in Power Query.

This ReportTable holds the structure, order and description of every row in the report, we do not include the Flag field, since we’ll use this field as report header.

If you are using Tabular SSAS with live connection, you can create the report table in your model by importing this from Excel or from a SQL Server database or you can also create the table with DAX using the following:

select 1 as [Index], 'Country' as [Description]

union all

select 2 as [Index], 'Units Sold' as [Description]

union all

select 3 as [Index], 'Gross Sales' as [Description]

union all

select 4 as [Index], 'Profit' as [Description]

union all

select 5 as [Index], 'Gross Profit Margin' as [Description]

 

Now, this is the trick: we need to create a Measures that will change dynamically based on the context of the row and it will calculate the corresponding measure with the correct format.

This measure will be created in the ReportTable.

ComparisonMeasure =

SWITCH(MAX(ReportTable[Index]),

1,

FIRSTNONBLANK(financials[Country],1),

2,

FORMAT(SUM(financials[Units Sold]),"###,###,###" ),

3,

FORMAT(SUM(financials[Gross Sales]),"$###,###,###"),

4,

FORMAT(SUM(financials[Profit]), "Currency"),

5,

FORMAT([Gross profit margin], "Percent"))

 

Then, we’ll use ReportTable[ComparisonMeasure] measure in the Values section of the Matrix. The Rows section will have the ReportTable[Description] field. Last, we’ll use the Financial[Flag] calculated column in the Columns section, as shown below.

This is how the report looks like now:

The table looks good. Side by side comparison with different formats in the columns was achieved! But there are still two issues with this Report.

  1. All the values are aligned to the left and Power BI does not have a feature to align column or cells in Tables nor in Matrices. The values in the columns are all text now, aligned to the left by default, so we need to align the currency, number and percentages to the right.
  2. Also, the order of the Matrix is alphabetically by ReportTable[Description].

Solution:

In order to fix the alignment, we’ll change the measure again and add empty spaces to force alignment to the right. This kind of alignment workaround is not pixel perfect, but we need to do this way until Power BI release a new alignment feature for table or matrix reports.

ComparisonMeasure =

VAR GrossSales=FORMAT(SUM(Financials[Gross Sales]),"$###,###,###")

VAR GrossSalesAligned=CONCATENATE(REPT(" ",20-LEN(GrossSales)),GrossSales)

VAR UnitsSold=FORMAT(SUM(Financials[Units Sold]),"###,###,###" )

VAR UnitsSoldAligned=CONCATENATE(REPT(" ",22-LEN(UnitsSold)),UnitsSold)

VAR Profit=FORMAT(SUM(Financials[Profit]), "$###,###,###")

VAR ProfitAligned=CONCATENATE(REPT(" ",20-LEN(Profit)),Profit)

VAR GrossProfitMargin=FORMAT([Gross profit margin], "Percent")

VAR GrossProfitMarginAligned=CONCATENATE(REPT(" ",23-LEN(GrossProfitMargin)),GrossProfitMargin)

RETURN

SWITCH(MAX(ReportTable[Index]),

1,

CONCATENATE(REPT(" ",4),FIRSTNONBLANK(Financials[Country],1)),

2,

UnitsSoldAligned,

3,

GrossSalesAligned,

4,

ProfitAligned,

5,

GrossProfitMarginAligned

)

 

After this change the Report looks better aligned.

Now in order to solve the order issue, we verify that the ReportTable[Index] field is selected as default in “Sort by Column” option.

Even though, after this the Matrix is still sorting by Description and not by Index. Then, I’ll create a calculated column in ReportTable, to concatenate the Index and Description in only one column:

 

SortedDescription = [Index] & " - " & [Description]

Now, the ReporTable shows the new column:

When SortedDescription replaces Description in the Rows section, the Matrix displays the correct sorting.

We add some slicers and we have both versions side by side matrix and simple table.

There is extra effort to create this kind of report in PowerBI. Even though, there are some cases where the side by side comparison in column and the formatted values are much easier to read than table with rows by row comparison. Especially, when there are multiple items or rows, it is easier for the end-user to compare columns side by side, where you can scroll up and down rather than scroll the table horizontally in a row by row comparison.

You can download this Power BI desktop file with this link: https://1drv.ms/u/s!AjlEiXFjD5fu2SehP8jKkcpwJacf