Tag Archive for Power BI

Using Real Time Data Analytics and Visualization Tools to Drive Your Business Forward

Businesswoman work with documents and tablet pc

Business leaders need timely information about the operations and profitability of the businesses they manage to help make informed decisions. But when information delivery is delayed, decision makers lose precious time to adjust and respond to changing market conditions, customer preferences, supplier issues or all three. When thinking about any business analytics solution, a critical question to ask is: how frequently can we (or should we) update the underlying data? Often, the first answer from the business stakeholders is “as frequently as possible.” The concept of “real time analytics,” with data being provided up-to-the minute, is usually quite attractive. But there may be some confusion about what this really means.

While the term real time analytics does refer to data which is frequently changing, it is not the same as simply refreshing data frequently. Traditional analytics packages which take advantage of data marts, data warehouses and data cubes are often collectively referred to as a Decision Support System (DSS). A DSS helps business analysts, management and ownership understand historical trends in their business, perform root cause analysis and enable strategic decisions. Whereas a DSS system aggregates and analyzes sales, costs and other transactions, a real time analytics system ingests and processes events. One can imagine a $25 million business recording 10,000 transactions a day. One can imagine that same business recording events on their website: login, searches, shopping cart adds, shopping card deletes, product image zoom events. If the business is 100% online, how many events would that be? The answer may astonish you.

Why Real Time Analytics?

DSS solutions answer questions such as “What was our net income last month?”, “What was our net income compared to the same month last year?” or “Which customers were most profitable last month?” Real time analytics answers questions such as “Is the customer experience positive right now?” or “How can we optimize this transaction right now?” In the retail industry, listening to social media channels to hear what customers are saying about their experience in your stores, can drive service level adjustments or pricing promotions. When that analysis is real-time, store managers can adjust that day for optimized profitability. Some examples:

  1. Social media sentiment analysis – addressing customer satisfaction concerns
  2. Eliminating business disruption costs with equipment maintenance analytics
  3. Promotion and marketing optimization with web and mobile analytics
  4. Product recommendations throughout the shopping experience, online or “brick and mortar”
  5. Improved health care services with real time patient health metrics from wearable technology

In today’s world, customers expect world class service. Implicit in that expectation is the assumption that companies with whom they do business “know them”, anticipate their needs and respond to them. That’s easy to say, but harder to execute. Companies who must meet that expectation need technology leaders to be aware of three concepts critical to making real time analytics a real thing.

The first is Internet of Things or IoT. The velocity and volume of data generated by mobile devices, social media, factory floor sensors, etc. is the basis for real time analytics. “Internet of Things” refers to devices or sensors which are connected to the internet, providing data about usage or simply their physical environment (where the device is powered on). Like social media and mobile devices, IoT sensors can generate enormous volumes of data very, very quickly – this is the “big data” phenomenon.

The second is Cloud Computing. The massive scale of IoT and big data can only be achieved with cloud scale data storage and cloud scale data processing. Unless your company’s name is Google, Amazon or Microsoft, you probably cannot keep up. So, to achieve real-time analytics, you must embrace cloud computing.

The third is Intelligent Systems. IBM’s “Watson” computer achieved a significant milestone by out-performing humans on Jeopardy. Since then, companies have been integrating artificial intelligence (AI) into large scale systems. AI in this sense is simply a mathematical model which calculates the probability that data represents something a human would recognize: a supplier disruption, a dissatisfied customer about to cancel their order, an equipment breakdown. Using real time data, machine learning models can recognize events which are about to occur. From there, they can automate a response, or raise an alert to the humans involved in the process. Intelligent systems help humans make nimble adjustments to improve the bottom line.

What technologies will my company need to make this happen?

From a technology perspective, a clear understanding of cloud computing is essential. When evaluating a cloud platform, CIO’s should look for breadth of capability and support for multiple frameworks. As a Microsoft Partner, BlumShapiro Consulting works with Microsoft Azure and its Cortana Intelligence platform. This gives our clients cloud scale, low cost and a wide variety of real time and big data processing options.

CIO Article 1

This diagram describes the Azure resources which comprise Cortana Intelligence. The most relevant resources for real time analytics are:

  1. Event Hubs ingest high velocity streaming data being sent by Event Providers (i.e. Sensors and Devices)
  2. Data Lake Store provide low cost cloud storage which no practical limits
  3. Stream Analytics perform in-flight processing of streaming data
  4. Machine Learning, or AzureML, supports the design, evaluation and integration of predictive models into the real-time pipeline
  5. Cognitive Services are out-of-the-box Artificial Intelligence services, addressing a broad range of common machine intelligence scenarios
  6. Power BI supports streaming datasets made visible in a dashboard context

Four Steps to Get Started with Real Time Analytics

Start with the Eye Candy – If you do not have a dashboard tool which supports real-time data streaming, consider solutions such as Power BI (link to our web page). Even if you are not ready to implement an IoT solution, Power BI makes any social media or customer marketing campaigns much more feasible. Power BI can be used to connect databases, data marts, data warehouses and data cubes, and is valuable as a dashboard and visualization tool for existing DSS systems. Without visualization, it will be very difficult to provide human insights and actions for any kind of data, slow or fast.

Get to the Cloud – Cloud storage costs and cloud processing scale are the only mechanisms by which real time analytics is economically feasible (for most companies). Learn how investing in technologies like Cloud Computing can really help move your business forward.

Embrace Machine Intelligence – To make intelligent systems a reality, you will need to understand machine learning technologies, if only at a high level. Historically, this has meant developing a team of data scientists, many of whom have PhD’s in Mathematics or Statistics, and open source tools like R or Python. Today, machine learning is much more accessible then it has ever been. AzureML helps to fast track both the evaluation and operationalization of predictive models.

Find the Real-Time Opportunity – As the technology leader in the organization, CIO’s will need to work closely with other business leaders to understand where real-time information can increase revenue, decrease costs or both. This may require imagination. Start with the question – what would we like to know faster? If we knew our customer was going to do this sooner, how would we respond? If we knew our equipment was going to fail sooner, how would we respond? If we knew there was an opportunity to sell more, how would we respond?

Berry_Brian-240About Brian: Brian Berry leads the Microsoft Business Intelligence and Data Analytics practice at BlumShapiro. He has over 15 years of experience with information technology (IT), software design and consulting. Brian specializes in identifying business intelligence (BI) and data management solutions for upper mid-market manufacturing, distribution and retail firms in New England. He focuses on technologies which drive value in analytics: data integration, self-service BI, cloud computing and predictive analytics

Power BI Demo CTA

 

Row Level Security in Power BI – Part 2

In Part 1 of this series, we discussed Row Level Security in Power BI, that it is different from RLS in SQL Server 2016, and then went on to demonstrate two simple scenarios where RLS can be used to filter data in a model based on Role assignment utilizing some DAX filter expressions. We introduced the USERNAME() DAX function and demonstrated its usefulness. In this second article, we’ll be diving a little deeper into RLS.

Row Level Security in Power BI Scenario 3:

Those first two scenarios from Part 1 were not that bad to implement. One line of a DAX expression and you’ve got a simple filter covered. But life seldom ever hands us a scenario that is so cut and dry. For this scenario, we’re going to add some requirements that might get handed down by the business users such as:

  • Ability to grant access to a group of countries, like continents, or regions.
  • Ability to have any one country be in multiple geo-political groups.
  • Ability to grant and revoke access to an entire group of people at once rather than needing to address each person individually
  • Ability to grant access on any one country to any one individual

For this we’re going to again go back to the database and create a few tables to help us. We already have the Country table so no need to do anything there. But we’ll add four more:

  • Users
  • Groups
  • User Group Membership
  • Country Group Membership

The T-SQL code for this can be found in the attachment, and a simple database drawing is shown here:

Row Security 3-1-1

We’ll also add some rows to the respective tables via basic INSERT statements:

For those of you who, like me, are full-blooded SQL nerds, I have included the CREATE TABLE, INSERT INTO, and CREATE VIEW scripts that can be executed in your database.  But we don’t need to import all four additional tables into your model, we simply need the distinct list of Users and the Countries to which each has access. This is represented in the [dbo].[SecurityQuery] view (Script #05). If you look at the view definition, note the DISTINCT key word in the SELECT clause, the optional fields to show the User’s Full Name and the Country Name, the absence of any fields from the [Group] or[ GroupUser] tables, and the WHERE clause at the bottom that filters for only Active records.

First, we’ll need to remove the Continent table from the model, then import the SecurityQuery dataset. Since this is not an exercise in how to import data, I’ll leave it up to you to get that done. And while you’re adding it, you might as well add the [SecurityReference] view as well and we’ll cover its usage at the end of the article.

Once you have the [SecurityQuery] in your model, it needs to be joined to the Country table, on Country code:

Row 3-1-2

 

Hint: If you have sufficient rows in the Security Query dataset, when you set up the relationship between [Country Code] in the Country table and the [Country Code] in the Security Query table, Power BI will recognize that this is a one-to-many relationship with the Country table being at the “one” side.

As before, we will use the Manage Roles button on the Modelling tab to configure the DAX Filter for the Roles. We’ll start with what we learned from before:  a filter on the [UserName] field and making use of the USERNAME() DAX function as before:

Row 3-1-3

But if we were to browse the model now for any one member in our set, we’ll see that he or she is limited in the rows returned by the Security Query, but NOT limited by rows returned in the Country table. Why not? It worked before. It is because the table on which the USERNAME() filter is applied is not at the top of the hierarchy, it’s at the bottom. To make things more complicated, there are two branches from the top of that hierarchy, one branch to sales, through the country table, and one branch directly to the security table. If only there was a way to filter the countries in that table based on those listed in the Security Query table. We need something more.

Enter the CONTAINS() DAX function. The description from MDSN is as follows:

Syntax

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

Return Value

A value of TRUE if each specified value can be found in the corresponding columnName, or are contained, in those columns; otherwise, the function returns FALSE.

If you’re like me, you’ll read that and think, “say WHAT?” Let’s implement it first, then we’ll sort out and explain how the parameters are used. Back under the Manage Roles, add a Filter to the Country table as the following (hint: it doesn’t matter which column you select as you will be replacing it with the entire text below):

CONTAINS (

‘SecurityQuery’,

‘SecurityQuery'[UserName],

USERNAME(),

‘SecurityQuery'[CountryCode],

‘Country'[CountryCode]

)

Note: The line breaks are not required in this, but are added for clarity of reading.

Now here’s the layman’s description of how the DAX statement above works with the five parameters, in order of their appearance:

Go to the ‘SecurityQuery’ table (1st parameter), and in the column ‘SecurityColumn’[UserName] (2nd parameter), look for any rows that match the value returned by the function USERNAME() (3rd parameter).  Then for those rows, take the values in the column ‘SecurityQuery’[CountryCode] (4th), and see if those values exist in the column ‘Country’[CountryCode] (5th). If that [CountryCode] value is found return TRUE for that row, and allow it to be viewed in this context.

There are a couple of things to watch for when implementing this approach. First, you need to have a relationship defined between the Country table and the SecurityQuery table. Second, the <value> parameter (3rd parameter) of the CONTAINS() function can be just that, a single value, not a list or table. But the USERNAME() function fits this bill nicely. Third, you still need the [UserName] = USERNAME() security filter on the Security Query dataset.

If you followed the INSERT scripts included, you will recall that there were only two people involved: Fred and Bob. And you may recall that we granted Fred access to a Group called “Pacific Rim” (which included countries such as Japan, Hong Kong, and the Philippines) but that [GroupMembership] row was NOT flagged with [IsActive] = 0. Fred’s mapped data looks like this:

row 3-1-4

 

Now here’s where the [SecurityRefernce] dataset comes in. After adding it as part of the model, you need to make sure that A) it has NO relationships to any other tables and that B) it also has a [UserName] = USERNAME() DAX Filter expression applied.

I added a report page to my Power BI model and a simple Matrix visualization based on this table and configured it as follows:

  • Rows = [Country Name]
  • Columns = [GroupName]
  • Values = [IsActive], with a SUM aggregation
  • For the [IsActive], I set up Conditional Formatting with the following properties

row 3-1-5

The resulting visualization is a nice way to see exactly HOW a person has gotten access to any one particular country (via membership in which Group), and what countries are in any Group they have are also a member of, even if that membership is not active.

Row 3-1-6

 

It is true that there is a lot introduced here that may not specifically be “Row Level Security” stuff, but rather T-SQL overhead. After all, all you really need for this 3rd scenario to work is the distinct list of Users and Countries that are allowed access. But given the 173 countries in the world and say, two dozen people to which access control is required, that’s potentially up to 4,000 rows of data controlling who has access to what. Breaking it into Users and Groups and Memberships is a way to manage the mess.

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

 

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

shutterstock_190884689

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