Archive for Software

Row Level Security in Power BI – Part 1

The folks at Microsoft have been steadily adding features to both the Power BI service and the downloadable Power BI Desktop over the past eighteen months since it went General Availability in July of 2015. Row Level Security is one such feature, allowing the developer to restrict which data is seen by users. Like most other major features, it was introduced into Power BI Service first, then eventually added to the Desktop. However, unlike other features, upon its addition to the Desktop, it, or most of it, was removed from the Service. After all, you really only need it in one place. Having it in both places would just cause confusion and conflicts. However, there are some lingering aspects in the Power BI service that require attention after deploying a model that has Row Level Security defined. In this post, we’ll look at this feature of Power BI, how to get it up and running in the Desktop, what needs to be done on the Service, and some common scenarios for when it might be useful.

But before we get started, you need to be made aware of two points: First is that RLS in Power BI is NOT the same as RLS in SQL Server 2016. Yes, both are great new feature in their respective products, and do pretty much the same thing of restricting user access to certain rows of data. They even share the same acronym, and the results are pretty much the same, but the implementation methods of each are quite different. RLS in SQL Server uses T-SQL functions and other artifacts inside the SQL database, while Power BI uses DAX. Another major difference is that RLS in Power BI can be used regardless of the data source type.

The second major point is that RLS and Power BI Dashboard’s Q&A feature are mutually exclusive. You can have one or the other, but not both. While some readers may not really care about that, others may simply stop reading right here based on that revelation alone. But before you click off the page because of this limitation, take a moment to voice your opinion at the Power BI Feedback site for this particular issue. Even if you don’t care, some day you might and I encourage you to vote on the item. Go ahead, we’ll wait.

Ok, you’re back? Good, let’s get started.

Row Level Security in Power BI Scenario 1

The first scenario we’re going to explore is one where you have hard-coded roles for each particular segment of data. For the demo model for all these scenarios, I generated data that was spread across the globe, and tied the access directly to continents, then later to countries. This makes it very easy to determine at a glance if data is being filtered properly. The Power BI map visualization is the ideal choice for this as your eyes can easily determine which geographic entities are represented by the data. We’ll see the map again later on, but for starters, the unfiltered data maps like this:

Row Level Security 1


Before we set the security for this data, I’ll explain a little about this particular model’s data structure: It is made up of three tables as shown here: country, customer, and Salessales. The country table lists all of the countries in the world, and which continent they are on. Also, that table is at the “one” side of a one-to-many relationship to Customer (on [CountryCode]), which is in turn on the “one” side of its relationship to the Sales table.

Row Level Security 21


If we can limit the countries to only those in, say, North America, that would limit the customers to those that are located there, and that would limit the sales, in a cascading filter sort of way. We end up seeing only sales to Customers that are in North America. It would be like adding a WHERE predicate to a SQL query which filters as follows:

SELECT * FROM dbo.Country WHERE Continent = ‘North America’

Let’s get started.

  • On the Modelling tab, click Manage Roles.
  • Click Create to add a new Role, and name it “North America”.
  • Under Tables, click the ellipsis next to Country and select Add Filter, then select the [Continent] field
  • Edit the Table Filter DAX Expression it generates, substituting “Value” for “North America”

row 3


To test out this Role, back on the Modelling tab, click View As Roles. Select the North America role. My resulting map looks like this: Row 4


Notice the yellow band at the top of the report page that not only shows the security context under which the data is being viewed, but also offers a link to go back to unfiltered data.

We’ve successfully created a Security Role for North America. With only six distinct continents in the Country table, it is a simple operation to add additional roles for the remaining five, each appropriately named.

After deploying the model to the Power BI service, we have some additional work. Locate the data set for the model, click the ellipsis next to it and select security. This is where you would specify who has membership in which role. And obviously, membership in any one role gives you access to see the data associated with that particular continent. There is nothing stopping you from adding any one name to multiple roles. As you can see from the screenshot below, I have already added someone to the North America role.

row 5


Get used to this operation as it will be referenced in each of the other two scenarios, but not directly explained again as the operation is exactly the same. Only the name of the Role(s) will change.

This Row Level Security approach is useful if you have a relatively small number of distinct values at the top of a hierarchy (Continent / Country / Sales in this case), and that list is not likely to change. Changes in that list of values would dictate corresponding changes to the Roles, and re-deployments of the Power BI model. But I think we’re safe with our six continents on this earth for at least another googol years or so.

Row Level Security in Power BI Scenario 2

In this scenario, we’re going to take the security role assignments out of the Power BI Service and hold them at the database layer. We’ll use the same model from the previous scenario, minus the six roles we defined before. This model includes one additional table, Continent as shown below: row 6

And as you can imagine, this table now moves the top of our de-facto hierarchy, with a one-to-many relationship between it and the Country table, on field [Continent]:

row 7

To set security in the model we will do the same thing using the Manage Roles dialog box. If you are continuing on from Scenario 1 and have added the Continent table, you can delete the six Roles you added before as they are not needed here. But this time the single role of “Users” will filter on the Continent table, with the DAX expression of: [UserName] = USERNAME()

Row 8


The DAX USERNAME() function returns the login name and domain of the logged in user in the format of <name>@<domain>. This returned value ties nicely with the [UserName] column of the Continent table.

To test out this method of securing the data, we will again go to the View As Roles button on the Modelling tab, but this time we’ll check off both the Role of “Users”, and also fill in the “Other user” option by supplying a user name. If you recall the Continent table above, Eustice is assigned to Europe, so she only sees data for Europe. Notice below also how the Continent slicer gets filtered to just one value, and the Country slicer below it gets filtered as well to only Countries to which Eustice has access — those in Europe.

Row 9

After deploying the model, we still need to add email addresses to our User role just like before, but this time we have one role to worry about, and so all six names can be added under that one Role. Control over who gets to see what continent is defined in the rows of the Continent table back in the database. By changing or removing a name and refreshing the data to Power BI, we will alter data access rights without the need to redeploy the model or make adjustments to the Dataset’s Security settings on the site.

This scenario is handy when you again have a clearly defined top-down hierarchy, and when the users’ access is mutually exclusive. One and only one person can have access to each continent. I foresee this being implemented in a model where a salesperson table is at the head of the hierarchy and by filtering the salesperson, the customers and their respective sales get filtered as well. One certainly would not want to be creating a role for each salesperson, re-deploying the model, and then assigning each salesperson to their respective role as we did in Scenario 1. Instead, this approach handles the security assignment dynamically with the USERNAME() function.

Stay tuned for Part 2 where we will look at a third scenario which is a bit more involved, with multiple users, multiple groups, multiple group memberships, and multiple geo-political regions.

About Todd: Todd Chittenden started his programming and reporting career with industrial maintenance applications in the late 1990’s. When SQL Server 2005 was introduced, he quickly became certified in Microsoft’s latest RDBMS technology and has added certifications over the years. He currently holds an MCSE in Business Intelligence . He has applied his knowledge of relational databases, data warehouses, business intelligence and analytics to a variety of projects for BlumShapiro since 2011. 

Power BI Demo CTA

A Digital Transformation – From the Printing Press to Modern Data Reporting

Imagine producing, marketing and selling a product that has only a four-hour shelf life! After four hours, your product is no longer of much value or relevance to your primary consumer. After eight hours, you would be lucky to sell any of the day’s remaining stock. Within 24 hours, nobody is going to buy it; you have to start fresh the next morning. There is such a product line being produced, sold and consumed to millions of people around the world every day. And it’s probably more common than you think.

It’s the daily newspaper.

With such a tight production schedule, news printers have always been under the gun to be able to take the latest news stories and turn them into a finished printed product quickly. Mechanization and automation have pretty much made the production of the modern daily paper a non-event, but it has not always been that way.

150 years ago, the typesetter (someone who set your words, or ‘type’, into a printing press) was the key to getting your printed paper mass produced. With typesetters working faster than your competitors, you could get your product, your story, out to your consumers faster, gaining market share. However, it was still very much a manual process. In the late 1800s the stage was set for a faster method of setting type. One such machine, the Paige Compositor, was as big as a mini-van and had about 15,000 moving parts. (Samuel Clemens, a.k.a. Mark Twain, invested hundreds of thousands of dollars in the failed invention, leading to his financial ruin.) On a more personal scale and at the modern end of the spectrum, we think nothing of sending our finished work, perhaps the big annual report, off to the color printer or ‘office machine’, or upload it to a local printing vendor who will print, collate and bind the whole job for us in a fraction of the time it would take a typesetter to layout even the first page!

So why am I telling you all this? It’s certainly not for a history lesson. The point is that the printed news industry went through a transformation from nothing (monks with quill pens), to ‘mechanization’ (Gutenberg’s printing press), to ‘automation and finally to ‘digitalization.’ And, they had to do so as the news consumer evolved from wanting their printed subscription on a monthly basis, down to the weekly, to the daily and even to the ‘morning’ and ‘evening’ editions. Remember, after four hours, the product is going stale and just about useless. (We could debate whether the faster technologies was what drove news consumers to want information faster, or if the needs of the consumer inspired the advancements in technology, but we won’t.)

Data and reporting has followed the same phases of transformation, albeit not along a much accelerated time span. The modern data consumer is no longer satisfied with having to request a green-bar, tractor fed report from the mainframe, then wait overnight for the ‘job’ to get scheduled and run. They’re not even satisfied with receiving a morning email report with yesterday’s data, or even being able to get the latest analytics report from the server farm on demand. No, they want it now, they want it in hand (smart phones), and they want it concise and relevant. To satisfy this market, products are popping up that fill this need in today’s data reporting market. Products like Microsoft’s Power BI can deliver data quickly and efficiently and in the mobile format demanded due to the industry’s transformation to digital processing. Technologies in Microsoft’s Azure cloud services such as Stream Analytics, coupled with Big Data processing, Machine Learning and Event Hubs have the capabilities to push data in real time to Power BI. I’ll never forget the feeling of elation I had upon completing a simple real-time Azure solution that streamed data every few seconds from a portable temperature sensor in my hand to a Power BI Dashboard. It must have been something like Johannes Gutenberg felt after that first page rolled off his printing press.

Gutenberg and Clemens would be amazed at the printing technology available today to the everyday consumer, yet we seem to take it for granted. Having gone through some of the transformation phases with regard to information delivery myself (yes, I do in fact recall 11×17 green-bar tractor-fed reports) I tend to be amazed at what technologies are being developed these days. Eighteen months ago (an eon in technology life) the Apple watch and Power BI teamed up to deliver KPI’s right on the watch! What will we have in another eighteen months? I can’t wait to find out.

About Todd: Todd Chittenden started his programming and reporting career with industrial maintenance applications in the late 1990’s. When SQL Server 2005 was introduced, he quickly became certified in Microsoft’s latest RDBMS technology and has added certifications over the years. He currently holds an MCSE in Business Intelligence. He has applied his knowledge of relational databases, data warehouses, business intelligence and analytics to a variety of projects for BlumShapiro since 2011. 

Power BI Demo CTA

How to Setup the Waterfall Chart in Power BI

In this article, I wanted to touch on a visualization that was part of the initial release of Power BI, but one I seldom see on Dashboards or requested by Business Users: The Waterfall Chart. What is it? How do I set it up? What is it useful at displaying? How does it differ from a simple Column chart? We’ll answer those questions below.

What is the Waterfall chart?

A Waterfall chart is a column type chart that shows aggregated data over time. It is useful for displaying the individual components of a measure and how they continually contribute to the overall number. Something like ‘Variance to Goal’ would make good use of a Waterfall chart. I’ll explain more of the features as we explore and set up a simple example.

For this exercise we’ll make up some data in the Power BI Desktop designer. Click the Enter Data button on the Home ribbon and enter values and Column Headers as follows:

Waterfall Data

After clicking OK and loading the table, format the Date column as “(MMMM, yyyy)” and the other two as your favorite currency. That’s it. That’s all we need to move on to some charting. And first up will be a Clustered Column chart showing both Sales and Sales Goal side-by-side for the 12 months:

Sales Goal Column Chart

Because the default for a Column chart is to show the full height, this particular comparison, where the variance between the two numbers is relatively small compared to the total height, the eyes may have a hard time differentiating the columns. Also, we have to work hard to find the four months where the Sales value was below the Goal.

I see this type of visualization often and every time I think, “There’s got to be a better way to show this.” Fortunately, there is, but we need to add a variance calculation. Let’s add a Calculated Column to the table with the following formula:

Sales Goal Variance = [Sales] – [Sales Goal]

This should also be formatted as currency. Charting this value in a simple Column chart will give us a little more insight into how the variance has changed over the course of those same 12 months:

Sales Goal Variance Column Chart

This is a little easier to understand, particularly when it comes to determining when the Variance is negative or positive. But it still lacks the ability to show at what point, for example, we recovered from the shortfall in January and broke even, or if we are above or below our Goal for the entire year.

As useful as this plot is, there is an even better way. Here’s what the Waterfall chart would do with these same data points:

  1. Plot negative numbers in a different color than positive ones.
  2. Start each succeeding month at the point where the prior month ended.
  3. Show a total (positive or negative) for the entire time frame.

For example, if we could slide down the February column so that it started at the end of January’s ($2K),

Waterfall 1

then slide March’s $500 down so it starts where February ended  at ($1k) ( = ($2K) at the end of January + $1K added by February ) and so on, the result would be a Waterfall chart as show here:

Waterfall ChartThe resulting plot is an easy to understand column-type chart showing how each monthly segment contributed to the overall value for the total period. From this chart, we can easily pick out the following tidbits from our data that were not readily available in either of the two previous chart styles:

  • January was disastrous we knew, but by the end of April we had recovered and were back on track.
  • We had four consecutive months of Sales greater than the corresponding monthly Goal (February through May), then settled into a back-and-forth for most of the rest of the year.
  • November was just as bad as January, and brought us slightly behind the Goal for the year.
  • December’s gain was the best, and brought us over our Goal by a wide margin.

How do I set up a Waterfall Chart?

The Waterfall chart is one of the easiest in Power BI to set up. There are three very basic steps, as outlined below, and which need not be done in the order specified.

Select Waterfall chart

  • Select the Waterfall chart from the Visualizations palette. If you have not already added any other elements, it will add a blank chart to the report canvas.
  • Select the Y Axis. In our case, we used a Calculated Column called Sales Goal Variance, which, being a numeric data type, was automatically summed by default. We could have just as easily selected a Measure that already had the appropriate aggregation defined.
  • Select the Category. Typically, a date type is used here, and be careful after selecting as Power BI tends to create Date Hierarchies which must then be manipulated. For this exercise, since my data was already at a monthly grain, I by-passed the hierarchies and went straight for the Date column.

select Date

There are some useful format options, too, that should be noted. The most prominent one is the Sentiment Colors option. With this, the designer can assign colors to the Increase, Decrease and Total bars if, for example, the data was such that a DECREASE was favored over INCREASE.

Sentiment Colors

How does a Waterfall chart differ from a Column chart?

As shown, the two are very close in layout, but there are some major differences:

  • The Waterfall chart cannot show a Series, such as Territory or Product, as either stacked or clustered, like a Column chart can. It is limited to one value only.
  • It does not allow superimposing of a line with a secondary Y axis that shares the same X axis.
  • It can only be displayed with vertical columns and does not allow horizontal bars.

What is a Waterfall chart useful for?

Limitations above aside, the Waterfall chart is still quite useful in the right situation and with the right data. As already shown, when plotting a variance, numbers where there is a potential for negative values, the Waterfall chart excels. It gives somewhat of a Year-to-Date look and feel of the data without the need to write the DAX expression. Note that the ending point for each month (the top of each green column and the bottom of each red column) represents the point of YTD Variance for that month. (I sometimes refer to a Waterfall chart as a ‘poor man’s YTD’.)

Consider the following Column chart that plots the DAX Measure:

YTD Variance = TOTALYTD(SUM(‘Table1′[Sales Goal Variance]),’Table1′[Date])

YTD Variance

This plot is, I believe somewhat misleading to the viewer, even though the ENDS of each monthly column correspond with the ENDS of the columns in the Waterfall. All we can tell for any one month is where we are in YTD Variance, but not how we got there. To arrive at July’s YTD Variance value of $750, for example, we had to DROP from June’s point of $1,750, something that the Waterfall chart clearly indicates, but something we need to derive in the above plot based on the position of July’s end point relative to June’s. Our Sales Goal Variance was NEGATIVE for July, but the above plot seems to indicate that July was POSITIVE!

And lastly, YTD plots always end at the end of the year because, well, that’s in their definition. A Waterfall chart can span multiple years with ease, or even be sliced or filtered to start at a different point along the X (Category) axis. The plot below charts the same data, but shows April through October.

April Waterfall

Hopefully, now that you understand the benefits and limitations of the Waterfall chart, you’ll be more inclined to introduce it and explain its benefits to Business Users for displaying variance data.

Learn more about Blum Shapiro Technology Consulting

Also by this author: KPI’s in Power BI, Not as Hard as You Think.

5 Warning Signs Your Company Has Outgrown QuickBooks

Cloud accounting image

If you’re a small business, you’re most likely running QuickBooks. In fact, millions of businesses use QuickBooks as their very first accounting system. Why?

When companies are starting out, they can’t afford to make huge investments in finance systems. They spend $100 dollars or so and they are all set. They don’t have to invest in training and can be up and running in a matter of days.

For a lot of businesses, QuickBooks is the last accounting system they will ever have to buy because their needs don’t change.

What if Your Business is Growing?

But if your business is growing and evolving, your finance system needs to adapt. Your reporting requirements evolve. More and more people depend on critical reports and financials and need them quicker.

Most businesses respond to these increased expectations by compensating with manual entries, workarounds and the biggest crutch of all…spreadsheets.

As they continue to evolve, they add more workarounds, and more manual, duplicate data entry and more band aids. Now they are so in the weeds trying to get basic financials issued each month that they lose sight of how difficult their life has become each month.

The Key Warning Signs You Have Outgrown QuickBooks

Based on my experience with hundreds of companies, I would like to share with you 5 Warning Signs That Your Company Has Outgrown QuickBooks. We’ll cover the first two warning signs in this post and discuss the last three in our next post.

Warning Sign #1 – Monthly Reporting Nightmares
QuickBooks designed its reporting capabilities for very small businesses. In order to get more complex, meaningful financial reports, all of the data must be exported to Excel, summarized, formatted and printed manually each month.

Change a single amount and you need to repeat the entire process again.

How much time are you wasting taking all of these extra steps each month? What more productive and valuable activities could you be working on instead?

Warning Sign #2 – Disconnected Critical Systems
Let’s face it, as your company grows, your systems become more complex.

You need more systems to manage the operational aspects of your business: customers, time and billing, sales, orders, production, payroll, scheduling, delivery and fulfillment.

QuickBooks was designed to stand alone, creating disconnected silos of information that are very difficult to manage as you grow.

If you are only entering your sales data once a month from your billing system, it’s hard to have any visibility into operations during the month.

Want to Learn More?

If you would like to learn more, we have compiled a free e-Book for you. Simply click here to download.

Please see the original post on the Cloud Accounting Blog >>