Articles

Jan 14 2018

Analyzing Salary Data with Power BI and R – Part 1

Author: Ginger Grant • Discussion: 3 Comments

CRISP-DM Diagram

The standard method for analyzing data is the CRoss Industry Standard Process for Data Mining [CRISP-DM].    Rather than describe the method, this post will walk through the process to illustrate how to analyze data using it. The data that I selected for analysis is the Brent Ozar Salary Survey information.The data is available under open source license and contains two years of answers to salary data with a total of over 6,000 responses. Understanding what is in the data determines what kind of answers it can provide. What can the data reveal?  Prior to drawing any conclusions, one needs to examine the data to determine the level of completeness, correctness and whether or not you have enough data to make decisions based upon the data.

Data Understanding

The first step in the process is to analyze the data to evaluate what kind of knowledge you can gather from the data.  The primary perspective of the data is salary, and the survey describes the characteristics which people with a certain salary level have.  The survey used to gather the data contained a number of drop down boxes and those fields can be used as categorical variables as there are a fixed number of possible responses.  Other survey items allowed people to freely enter anything, which makes it harder to statistically analyze some of the data.

 

Where Do Data Professionals Make the Most Money?

In the survey for 2018, the people who made the most money were from Hong Kong with an average salary of $263,289.  Before you start planning on moving, you will might want to look at the data a little closer.  There were 2 people who responded from Hong Kong.  One of them said he was making over 1.4 million dollars, the highest amount reported in the survey.  Given the fact that we only have two responses from Hong Kong, we will be unable to draw a definitive conclusion with 2 records. To be able to answer that question, more analysis will need to be done on the location and salary information and you will probably want to add market basket criteria because a dollar say in Hong Kong doesn’t go as far as the average apartment rental is $3,237 a month as it does say in Uganda where the rent is around $187 a month.

Using Power BI to Provide Data Understanding

The data modeling step of the CRISP-DM process anticipates that you will want to modify the raw data.  There may be records containing null or erroneous values that you will need to eliminate the entire record or substitute entries for a particular value.  You can also use this analysis to determine what conclusions you will be able to derive from the data.  For example, if you wanted to analyze what criteria are required for Microsoft Access Developers to make over $100,000 a year, you could easily find out in Power BI that it is not possible to do that analysis as there is no data for that set of criteria. If you want to do a year over year analysis of people who are working as DBAs, which I show in the second tab, you will need to change some of the categories as they changed from 2017 to 2018.

Analyzing Data with R and Power BI

Many times when providing a final report to explain your analysis, you will need to provide some documentation to demonstrate your conclusions.  In addition to creating some visualizations in Power BI, I also created some in R to include visualizations and analysis with R.  While I can include any R library I wish in Power BI Desktop, there are only 364 currently added to the Power BI Service in Azure.  If there is an R visualization you would like to add, you can send an email request to rinpbifb@microsoft.com and ask for it to be added.

Power BI Salary Data

For more information on Analyzing Data with Power BI and R, I recorded a video for Microsoft’s Power BI team which is available here.  The video shows the cleaning process some information regarding the analysis of the process. The analysis of the Salary data itself will be included in another post.  If you would like to find out when the next post in this series is available, please subscribe to my website for all of the latest updates.

*** UPDATE: My next post on further analysis of this data is available here.

Yours Always,

Ginger Grant

Data aficionado et SQL Raconteur

 

 

 

 

Aug 04 2017

What to use to Dynamically Write a Date Dimension in Power BI: M or DAX?

Author: Ginger Grant • Discussion: 4 Comments

Calendar-clip-artRecently I needed to create a date dimension for a Power BI model as there was not one in the source database. There are two different ways that I could do this, using DAX from the Modeling Tab within the Data View or using M via the Query Editor window.  As a general rule, when it is possible data manipulation should be done in M as it offers a greater level of compression.  In this case though I am using a function in DAX, which is not the same as creating a calculated column.

Create a Date Table in DAX

To create a date table in DAX,  in Power BI go to the Data View->Modeling Table.  Click on the  New Table button on the ribbon.  For those who are wondering how you would go about writing either one, here is the source code for the DAX version.

 

DimDate = ADDCOLUMNS( CALENDAR(DATE(2017,1,1), DATE(2020,12,31)) ,
"Date Key", FORMAT ( [Date], "YYYYMMDD" ), //NumericDate
"Year", YEAR([Date]),
"Qtr Number", "Qtr " & FORMAT( [Date], "Q"),
"Q Number", "Q " & FORMAT( [Date], "Q"),
"Month Name" , FORMAT ( [Date], "mmmm" ) ,
"Month Short Name" , FORMAT ( [Date], "mmm" ) ,
"Month Number", MONTH([Date]),
"Month Year", FORMAT ( [Date], "mmm " ) & YEAR([Date]),
"Day Name", FORMAT ( [Date], "dddd" ), //Name for Each day of the week
"Day Short Name", FORMAT ( [Date],  "ddd" ),
"Day Number" , WEEKDAY ( [Date] ) //Sunday is 1
)  

This code uses the DAX CALENDAR function to create a contiguous set of dates between January 1, 2017 and December 31, 2020 with a field is named “Date”.  The remaining fields need field names and comments were added for clarity

Create a Date Table in M

In the Power BI Query Editor, click on the New Source button and select Blank Query. Select the View tab and click on the Advanced Editor button.  The Advanced Editor is where the M query is stored.  Paste the following code in to create a new table called Query1, which of course you can rename.

let
Source = List.Dates( #date(2017,1,1), Number.From( #date(2020, 12,31) - Date.From( #date(2017,1,1) ))+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"AddYear" = Table.AddColumn(#"Renamed Columns", "Year", each Text.End(Text.From([#"Date"], "en-US"), 4), type text),
#"AddMonth" = Table.AddColumn(AddYear, "Month Number",   each Date.ToText([Date], "MM")),
#"AddQuarter" = Table.AddColumn(AddMonth, "Quarter Number", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
#"AddMonthName" = Table.AddColumn(#"AddQuarter", "Month Name", each Date.ToText([Date], "MMMM")),
#"AddMonthShortName" = Table.AddColumn(#"AddMonthName", "Month Short Name", each Date.ToText([Date], "MMM")),
#"AddShortMonthYear" = Table.AddColumn(#"AddMonthShortName", "Short Month Year", each [Month Short Name] &" " &  [Year]),
#"AddDayOfWeek" = Table.AddColumn(#"AddShortMonthYear", "Day of Week", each Date.ToText([Date], "dddd")),
#"AddDay" = Table.AddColumn(#"AddDayOfWeek", "Day", each Date.ToText([Date], "dd")),
#"AddDateKey" = Table.AddColumn(AddDay, "DateKey", each ([Year]&[Month Number]&[Day]))
in
#"AddDateKey"

To Create A Date Table Use Either M or DAX

Now the question is which one should you use?  To be honest it doesn’t matter.  I couldn’t see any difference when I tested it.  To validate this answer I consulted twitter, which sparked some very interesting comments and analysis. Marco Russo b | t  is planning on writing a blog on the details of it, but Jason Thomas b | t gave me this summary. “[The] Dictionary expands as needed as values are inserted–designed to reduce cost of re-alloc and re-org of hash buckets (at cost of memory waste).” The full explanation needs a post of it’s own to be sure, which I am looking forward to reading when Marco writes it.   I’ll quote Kasper De Jonge b | t who summed it up best “I don’t believe it will matter much, the date table will be so small regardless”. Whichever way you chose to add a date table, DAX or M, which are needed for time intelligence, you now have the code to do either one.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jul 30 2017

Missing Custom Power BI Visuals

Author: Ginger Grant • Discussion: 8 Comments

One of the great new features of the July release was the ability to now get all of the Power BI Custom Visuals from within Power BI.  I had a bookmark to get the visuals from the Office Store, but it always seemed kind of a kludgy solution.  Personally, I liked the visuals better when they were on the Power BI website prior to March of 2017.  The filters worked better and they also included a sample file.  Now I have a different and more technical reason to not like the visuals in the Office Store, some of the Power BI Custom Visuals are not there.

Some Power BI Visuals Are Not in the Office Store

PowerBIFishCustomVisualThis week I decided to do a demo using the Aquarium custom visual.  As readers of my blog know, I have used the custom visual before, but it has been a while and I have changed PCs since then.  No worries I can always go download the visual from the store, right? Wrong. The aquarium visual is not available on the new store. Neither is Image Viewer, if one is looking to add that into your latest Power BI report it is not available. What happened?

So Long and Thanks for All of the Fish

I found out from Adam Saxton b | t  that moving Power BI custom visuals was not the simple cut and paste process that I had always assumed that it was.  The people who write custom visuals had to re-write them.  What’s more unlike when the custom visuals were housed on the Power BI Website, custom visual creators also had to pay $25 to register or $99 for their company.  This means that some custom visuals may never appear in the store as the people who created them aren’t willing to pay money to give them away.

If you have the custom visuals, or as in my case you know someone who can give you a copy of a Power BI custom visual which was published prior to the move over to the Office Store, the visual will still work when you upload it to the service.  I have also been told that Microsoft is working on adding the aquarium visual to the Office Store so at some point it will again be available for download.  For those who have noticed that the Box and Whisker custom visual is not the same as the previous version, I doubt they will be able to download the old one. If they can find someone to give it to them, it will still work.

If I do find out when the Aquarium visual will be available from the store again, I will update this post. Until then, if there is a visual you want, I would try asking on Twitter, as that worked for me.

****UPDATE: On August 4, 2017 the Enlighten Aquarium is now available again! Here’s a link to the Office Store.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jul 02 2017

Power BI – Beyond the Basics

Author: Ginger Grant • Discussion: 2 Comments

When helping clients recently with their Power BI implementations, I have noticed that when talking to people about Power BI there seems to be some areas where there continues to be a log of questions.  While it is easy to find a plethora of information about getting started with Power BI, when it comes to implementing a solution, the information is scarce.  How do you handle releases? Should an implementation contain only one data model? Is Power BI’s data secured on the cloud? Is it required to have Office 365 use Power BI? Do you have to have Power BI Premier to have the Power BI run locally?

Advanced Power BI Techniques in Norway

While I have discussed some best practice techniques on my blog, as usual new features released in Power BI have a

Norway Parliament Building in Oslo

Norway Parliament Building in Oslo

tendency to change some of the available options.  For example, App workspaces, the updated take on Content Packs released a few months ago, now offer a new method for releasing not only dashboards but the reports behind them and the ability to easily migrate sources. I am excited that I will have the opportunity to discuss the answers to the questions received by doing a full day of training at SQL Saturday Oslo. I am looking forward to visiting Oslo, which is home to the best preserved Viking Ship, an Opera House designed to be walked on and the home of the guy who painted the Scream.  If you happen to reside somewhere where it is possible to make the journey to Norway, please register to attend this full day of interactive training.  We will cover all of these items and go into detail about Power BI administration, security and new features and design techniques which will improve Power BI implementation techniques.

sqlsat667_osloFor those of you who are unable to attend, I feel obliged to answer some of the questions I posed earlier.  Implementations generally require more than one data model.  Power BI is encrypted both in transit and at rest. You do not need to have Office 365 to run Power BI.  Power BI can be run locally with Power BI Report Server, which is part of SQL Server 2016 Enterprise with Software Assurance, and you do not need to sign up with Power BI Premier to install it.

I hope to see you in Norway.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jun 14 2017

On Premises Power BI with Power BI Report Server

Author: Ginger Grant • Discussion: 3 Comments

On June 12, Microsoft officially released the Power BI Report Server.  The version that was released had a different set of features than what was  promised when the product was announced earlier, which I discussed in a previous post. Some of the features and versions of SQL Server which are available to receive the Power BI Report Server upgrade were clarified at MS Data Summit.  This post contains everything you need to know to determine if you can upgrade from a current SQL Server Reporting Services Instance, what features are included in Power BI Report Server and what time frame those who want to use it should follow.

Power BI Report Server Only Connects to Analysis Services Data Sources

The most glaring change from what was announced earlier, is Power BI Report Server can only connect to analysis services data sources, both tabular and multidimensional.  If you want to connect to SQL Server, Oracle or Excel or all three, use the Power BI Web Service.  Only going to the cloud version will users be able to create a data mashup or connect to anything but SQL Server.

Connecting to one data source is not what was promised when the Power BI Report Server was announced in May.  Various Power BI Product members held a session at the Microsoft Data Summit where attendees were able to ask questions.  I asked,  “When are we going to be able to use Power BI Report Server with data sources other than analysis services?”  In a room full of people, I was assured that it was a top priority of the team to release the same data connectivity functionality for Power BI Report Server that currently exists for Power BI Services and the current plan was to release this functionality the next release.

Power BI Report Server Releases are Planned for Three Times a Year

Power BI Desktop currently has a monthly release schedule.  The Power BI Service is often updated more frequently than that, PowerBIRSas Microsoft tends to make changes when they are complete, rather than hold them for a given date.  In a corporate environment, it is sometimes difficult to accommodate such frequent releases.  Power BI Report Server has a planned release cycle of three times a year, with exceptions of hot fixes or security patches.  The next release of Power BI Report Server is planned for the fall.

To ensure that the version of Power BI Desktop matches Power BI Report Server, there is now a version of the Power BI Desktop for Power BI Reporting Server. The icon is exactly the same, but when you start the program the splash screen is different, as it shows you that you are running Power BI Report Server, in the top left corner.  When running the Power BI Desktop, the title also clearly says report server.  It is possible to run both, as I am presently doing on my PC.  One of the pitfalls of doing this, is when you click on a PBIX file, the Desktop version which loads is the last one you installed.  The Power BI Desktop Report Server version contains functionality which is not supported in Power BI Report Server, as it allows you to connect to other data sources and run R, neither of which will work in Power BI Report Server.  Since the next release of Power BI Report Server, the one which should support connectivity to more than analysis services, is going to be part of the next fall release, that release should contain the data mashup capabilities in the future Power BI Report Server Desktop version.

No Dashboards for Power BI Report Server

As I talked about in a previous post, there is no dashboard capability for Power BI Report Server, as it creates reports and other desktop features.  Power BI Service features, like Dashboards and Workspaces, are not available in the desktop or in Power BI Report Server. In the meeting that the product team held, someone else in the room asked a question which I promised to answer in a previous post. “Are there plans to add dashboards in a future release of Power BI Report Server?” The answer was no. Microsoft does not consider that a Power BI Report Feature and does not have the desktop feature in the product road map.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jun 13 2017

Power BI Data Insights

Author: Ginger Grant • Discussion: No Comments

 

2,500 people sat in the semi-darkness of the MS Data Insight Summit, joining who knows how many watching the live stream, watching and listening to the upcoming changes to  Power BI.  Some of the announcements were expected, like the General Availability [GA] release of Power BI Premium and Power BI Report Server on June 12.  Although there is a lot of documentation on both products, there was still more information to be learned now they are released.  Microsoft also announced they were creating a new product offering, Power BI Embedded.  As part of the product realignment, the ability to embed Power BI into applications was moved to only being a Premier feature.  This move caused an uproar in the marketplace as many companies wanted to continue using Power BI Embedded, but could not justify paying Power BI Premier pricing.  Power BI Embedded was created to address the sticker shock. This new Power BI product has two different pricing levels, EM1 and EM2, starting at $625 per month.  Not a whole lot of information has been publicly released regarding Power BI Embedded, but it is designed to have a limited feature set, focused on just embedding Power BI.

Power BI Upcoming Features

Microsoft demonstrated some upcoming features of desktop which were predictably very impressive.  They created an amazing time line custom visual which I really hope to use soon.  Another neat feature which was demonstrated in the keynote was drill down pages.  This feature allows users to create pages which will be displayed when the field is selected on the previous screen, and the data will reflect the selection.  As there can be a lot of different filters which can be created for Power BI, a new bookmark feature will be coming soon which will allow users to save the context of the report, which saves all of the selections made with all of the slicers. With this feature, the next time the report is viewed, only the selections people find important will be accessed.  These new features are scheduled for released in the next three months.

Power BI Community

KeynotePowerBICommunity

Art credit to Josh Sivey who was kind enough to send this

One of the last things that Microsoft did was to thank the user community for their involvement with Power BI. Since many of the new features added are based upon feedback from the user community, Microsoft really works hard to engage the larger user community to help share information regarding the product as well as mine the ideas from https://ideas.powerbi.com . It was nice of Microsoft to recognize people in the community. Even though the slide was not up for very long, lots of people notice who was recognized.

There is material for a number of other posts from this conference, so please subscribe to hear more information about Power BI very soon.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

May 21 2017

Power BI Premium: Power BI for the Enterprise

Author: Ginger Grant • Discussion: 2 Comments

When talking to clients who are implementing large implementations with Power BI, I have heard a lot of complaints. There is no good way to create a report which you just want to give to a client once. If you created the report with a workgroup, a pro feature, you cannot share with other users unless they also have a Pro License. The data size limits are too low for large users. The licensing model is really expensive for large users. Microsoft sought to resolve these problems with Power BI Premium, which allows companies to essentially buy their own Power BI Server.

Power BI Premium Pricing

The pricing model for Power BI Premium is a lot different than Power BI has been in the past as they are moving away from the per user model and moving more towards a company license model, with three PowerBIPremiertiers.  You will need to spend more money than listed in the three premium tiers. Pricing has become complicated and you might want to review the calculator site to figure it out. Premium Pricing covers the cost of the server, and unlimited read-only licenses. Everyone who creates reports will still need a Power BI Pro License.  If you have an Office 365 E5 subscription, you will have a license already.  If not, Power BI report creation requires a license. There is talk that Microsoft will develop additional tiers, for example something for education, development and for embedded only, but none of these have been Officially announced.  Currently there are the three tiers only.

Power BI Embedding Premium Only Feature

If you currently run Power BI Embedded, in the future you will need a Power BI Premium License as this feature will not be available for Pro.  Embedded is going to have one API, and that API is going to need to run on Power BI Premium. There has been a lot of discussion around this as there are a number of users who do not spend that much money on Power BI licenses, and they do not know what they are going to do going forward.  While there have been distinct cutoff dates published for the free features of Power BI, I have not found any hard cutoff dates when Power BI Embedded applications must be migrated to Premium or they start working. I have read rumors about a license of less than $1,000 a month for Power BI Embedded, but this has not been published, so is only speculation at this point.

More details will be coming out closer to the release date, which is targeted for sometime before July 1, 2017.  I anticipate that Microsoft will be releasing more information at the Data Insights Summit on June 12-13 and I will be there to find out what the latest information will be and will post it here.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

May 11 2017

Changes to the Power BI Free Version Include No Sharing

Author: Ginger Grant • Discussion: 2 Comments

NoSharingIncluded in the recent list of announcements Microsoft made about Power BI Local and Power BI Premium are a series of changes to the Power BI Free version which will go into effect on June 1. The free edition of Power BI will no longer be able to share reports. Currently free users could create reports and share them with others, which will be discontinued.  Only Power BI Pro Editions will be able to share reports.  Currently Power BI Pro users can create reports which can be shared with Free versions as long as no Pro features are used.  This means that if a Power BI report is set to automatically refresh the data, that report cannot be shared as Free versions do not have the ability to create reports which have data refreshed automatically. If the report was recreated to remove the automatic updates and instead refreshed manually, then the report could be shared with Free versions.  Starting June 1, the sharing feature will be removed. No longer can Power BI Pro users share anything to Power BI Free users.  If you have a Power BI Free account, there is no way to share information in the service. The Power BI Desktop will continue to be free but since you cannot print the content within it and sharing a PBIX file means that you will always be sharing the entire data model, this is of limited value.

Future Releases of the Free Version

Microsoft does plan on continuing the free version and improving it.  In the future, it will include features previous included only in the Pro version.  While previously the data sets which the Free version was able to connect with were limited, they will soon match all of the data sets included in the Pro version. Data refresh will be supported, as will streaming and higher data storage rates. Other than sharing and workgroups, which are pretty big features, Pro and Free will have the same feature set.

How Power BI Free Accounts Can Share for One Year

If you have a Free Power BI account and have logged into the account prior to May 2, you have a year to use a Pro license. It does not matter if you have previously used a Power BI Pro Trial.  This trial is a new one, and is available to anyone with a free account. After that, shared reports will not be accessible, unless the account starts paying for the Pro license.

There are a lot of conversations regarding the changes to the free account, and the other recent Power BI announcements.  In my next post, I will be discussing the Power BI Premier option.  To be notified of my latest posts, please subscribe to this blog using the link on this page.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

May 05 2017

Running Power BI Locally with the Power BI Report Server

Author: Ginger Grant • Discussion: 6 Comments
Power BI Now Available on your Local Server

Power BI: Now available without being on the cloud

Microsoft had an lot of announcements about Power BI this week, so many that it was easy to miss some of the finer details, including those which are going to be important in making decisions going forward.  Since the announcements are changes which will be effective soon, in the case of the free tier of Power BI on June 1, and released “… generally available late in the second quarter of 2017” this will give Power BI users time to adjust to the changes. In a nutshell, Microsoft has announced they are adding a cloud service called Power BI Premium which will allow people to create capacity instead of per-user licenses, the free edition will no longer to be able to share files, Power BI Embedded is going to be migrated to the Power BI Service from Azure, and finally, at long last, it will be possible to run Power BI reports locally and without needing anything in the cloud.

Running Power BI without a Cloud

It is not possible to run Power BI reports locally right now, but sometime before the 1st of July 2017,  users who have SQL Server 2016 Enterprise Edition per-core and active Software Assurance [SA] can deploy Power BI Report Server.  This means that no one is going to have to wait for SQL Server 2017 for Power BI on premise as it will be available sometime in June.  The functionality in SQL Server 2017 SQL Server Reporting Server [SSRS]. Community Technology Preview edition is going to be available in Power BI Report Server, with the addition of the ability to include custom visuals and many data sources, which the CTP version did not do. The Power BI Server includes all of the functionality of SSRS This means that users will not need an SSRS Server and a Power BI Server, as the Power BI Server will be able to do both.  If you want to migrate all of the reports created in SSRS from 2008 R2, and SSRS Mobile Reports, you can migrate these reports to the new Power BI Report Server. You can use Power BI Reporting Server for reports created on earlier versions, as long as you have a version of SQL Server 2016 Enterprise per-core edition with SA. The Power BI Report Server will be a separate install with separate release schedules, which currently are planned about once a quarter. Power BI Report Server will also be able to publish reports to mobile devices as well. If the reports uses data in the cloud, you can employ a Data Gateway as the Power BI Reporting Server can use the gateway to access cloud data. Of course if all of the data in the report is located on-premises, no gateway will be required.

Power BI Pro Licenses for On-Premise Reporting

While there is going to be no additional cost for running reports locally, or looking at them, creating and sharing reports for the Power BI Report will require a Power BI Pro License.  The Power BI Desktop is going to be free, and there is still going to be a free version of Power BI. There will also be a  new desktop version of Power BI for Reporting Services which will be on the same version as the Server, which will have fewer updates. This means if you support Power BI Service Reports and Power BI Report Server Reports you will have two versions of the Desktop, the Reporting Services Power BI Desktop and the Power BI Service Desktop.  Both are designed to run on the same machine. So far I have not had any problems having both other than remembering which is which as the icons are the same.  You have to load the software to see that the top line has (Report Server).

Starting June 1, free Power BI license holders will no longer be able to share reports.  Reports created with a free license can be viewed only by the person with the free account.

Power BI Desktop does not have Dashboards, and neither will Power BI

When it is released, Power BI Report Server will be displaying reports created from the Power BI Desktop.  Dashboards are not created in the Power BI Desktop application, meaning that there will be no Power BI Dashboards in the Power BI Report Server.  While this may change in a later release, it is not available in the first release, which also does not support R or custom visuals either.  To display and distribute dashboards, use the Power BI service.

I am sure there will be more announcements about this and other upcoming Power BI features. Many will most likely be announced at Microsoft’s Data Summit Conference in June, which I will fortunately have the opportunity to attend.  If you are going to be there as well, drop me a line or ping me on twitter at @desertislesql and perhaps we can meet in person.
 ***Update I have a post which covers the released version of Power BI Report Server.  Click here to find what was changed since this post was written.
Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

Mar 13 2017

Security Updates to Power BI

Author: Ginger Grant • Discussion: 1 Comment
Office 365 Admin Screen for granting Power BI Admin rights

Office 365 Admin Screen for granting Power BI Admin rights

In the past month, Microsoft has made a number of security changes to Power BI. The first one, is not really a feature update, but a PowerShell replacement. No longer do you need to use PowerShell to become a Power BI Admin. Any Office 365 Admin can grant Power BI Admin permissions via this screen in the Admin Center. The Power BI Admin role was first created in October, but the screen was not complete, which was just fixed in February.

Power BI Security Changed from Tenant Only

People who have been granted Power BI administrator rights will also notice a modification to the Admin screen. The March 2017 update to Power BI provides a major change to the security model in Power BI. Previously all the security settings were set at the Tenant Level, meaning that all the privileges were granted to all users. If I wanted to allow one group within the organization to be able to publish reports to the web, but I did not want to allow everyone to publish reports to the web, there was no way that this could be accomplished. All that has changed. It is now possible to include or exclude groups of users from having rights in Power BI. Users can be classified into security groups in Azure Active Directory, either through the Office 365 Admin Center or via the Azure AD Admin Center. Once created the security groups can be used in Power BI. Security Groups are not the same thing as the groups created in Power BI when a new work group is created.

Using Security Groups in Power BI Admin

PowerBINonTenantAdmin

Power BI Admin Portal

The new Power BI Screen looks different. It now lists which rights can be specified to different groups of users. Share content to external users, Export Data, Export reports as PowerPoint presentations, Printing dashboards and reports, Content pack publishing, and Use Analyze in Excel with on-premises datasets now have the ability to be assigned to security groups so that the rights do not have to be the same throughout the entire tenant.

Unfortunately, some of the permissions are still tenant based. For example, the setting Publish to web, which is one permission I would definitely like to turn on only for some users, is still only available as a tenant level option.  These security changes are a welcome improvement to the product as they provide more options for administrators to grant rights to Power BI.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur