Articles for the Month of January 2016

Jan 24 2016

DIY Guide to Content Packs

Author: Ginger Grant • Discussion: 1 Comment
SQL Saturday Austin - 461 2016

As I was rehearsing my Using Power BI when Implementing Data Analytics Management Practices presentation for SQL Saturday Austin, I realized that I wasn’t going to have time to cover everything I wanted to cover. One of the important methods for implementing data management practices in Power BI is using content packs. Content packs are a method of sharing reports and data throughout your organization so everyone doesn’t need to create data model and those people who do can share them with everyone else. For this reason I highly recommend using content packs in Power BI. This guide will walk you through the steps needed to create content packs. If you don’t have Power BI Pro, you can stop reading now, as content packs are a feature only available in the Power BI Pro version. There are a number of steps which will need to be completed to use a content pack within Power BI, and I’ve listed them all in order below. Depending on where you are in the process you might want to skip to the Creating a Content Pack section, but I thought it was important to include everything that should be completed first.

Create a Data Model in Power BI Desktop

For this example, you will need to create a data model in Power BI Desktop, and for this demo, create one report too. I’m not going to review how to do that here, but Microsoft has a video guide to creating Power BI Desktop models here. Save the .pbix file. After saving the file, go to PowerBI.com and login.

Recommendation: Use a Group Workspace

The next step is optional, but if you work with other people, I recommend it. If you don’t have one already, I highly recommend that you create a group workspace. That way the ownership is shared, meaning that if you take a day off, someone else has the ability to access the information. Click on the My Workspace item on the left hand side of the Power BI page. Click on the + (plus) adjacent to the Group Workspaces item. On the right hand side of the screen a form will pop up with fields for Group Name, Privacy, and Group Members. Make sure that you complete the form and save it. Double click on the new group workspace item to open it. If you happen to have an Office 365 Exchange license, creating a group workspace will also create a Group One Drive. This is a great place to put data so that you can all share it and see the file from within Power BI. Now that I have this one drive location created, I am going to copy my newly created Power BI file to it.

Using a Desktop file on Power BI.com

To use the Power Desktop file within PowerBI.com, the next step is to upload the Power BI Desktop file to the web as a dataset. Either clicking on the + (plus) button next to the words Datasets, or click on the Get Data button on the bottom of the screen. Both options will get you to the Get Data screen. We want to Import the Power BI Desktop file, so click on the get button in the Files box. The screen will change to the file location section. Select Local file and upload the Power BI file.

Data Refresh

PersonalGatewayPowerBIConfigurationScreenEnsuring that the data set refreshed, which allows everyone to have current data, requires updating the data with a gateway. For this example I am going to use the Power BI Personal Gateway because I plan to include multiple data sources instead of just SQL Server  and Power BI web application to schedule the data refresh. Assuming I have already installed the Power BI Gateway, Click on the … (ellipse) next to the Power BI Desktop file just loaded to the data set, and a box will pop up with a list of features on the bottom of the popup box. Select Schedule Refresh, which will bring up the screen shown.

Assuming the Personal Gateway is online and the Data Source Credentials are ok, change the Schedule Refresh from the default Off to On. Set the Refresh Frequency to one of the available options. If you want to update the data more than once a day, click on the option Add another time. When you have finished adding times, click on the Apply button to save the contents.

Creating a Content Pack

To create a content pack, ideally you want to share a data model which has working appropriately scheduled updates. That way anyone who wants to create a report doesn’t have to worry about having valid working data. Instead they can work on providing meaningful visualizations to business problems.

If you want to create a content pack or use one, the step is the same. Click on the yellow Get Data button on the bottom left corner of the screen. That will change the active window to the Get Data window. On the left hand side of the screen under the words Content Pack Library, there are two options. Click on the Get button from the one on the left, My Organization. Click on the button labeled Create Content Pack. The following screen will be displayed.CreateContentPacks

There are a number of options on the Create content pack screen, starting with the Choose who will have access to this content Pack button. I have selected the option My entire organization. You may want to create different content packs for different groups of users. If you have exchange groups set up, such as Accounting@desertislesql.com which would send an email to everyone listed in the email group, you can enter that email group. If you just want to add a list of emails for people within your organization, you can do that as well.

In the sample Create Content Pack screen shown, I have filled in the blanks, selected my Power BI Desktop file I just added and uploaded a company logo. Once you click on the Publish button, the screen will close and you will get a success window which briefly appears on the top of the screen. The content pack is now ready to use. Click on the Get Data button again, and the new content pack is available to use. When I select the newly created content pack AWDW, I am provided a new window with a big Connect button in it. Click on the Connect button. The data set and any reports connected to it will have yellow stars next to them.

Every one who uses this new data set can be guaranteed a data set which updated on the same schedule, and different people can now create visualizations with one shared dat aset which can be used many different times.

Data Management within an Organization

Having helped a number of organizations implement Power BI, one of the big issues I have seen are not related to the product but related to the processes within the organization which are used to support the data needs of a variety of different users. These processes tend to be the reason a self-service business intelligent process is successful or not. Using Content packs can be a part of that solution which is why I look forward to sharing what I’ve learned at SQL Saturday Austin – 461. I hope to see you Deep in the Heart of Texas!

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jan 20 2016

I’m on TV!

Author: Ginger Grant • Discussion: No Comments

Old-fashioned four legged TV set isolatedI’m on TV! Not just any TV, Excel.TV, which unless you stream the internet on your TV set like I do, probably is on a computer monitor, but in my world it still counts. Please check out Episode 40 if you want to see a recording of the show where I talk about Power BI.

There is kind of an interesting story as to how I ended up being asked to be on the show. I was teaching a Power BI class, and Jordan Goldmeier b | t was talking it. Jordan started following me on twitter, and tweeted that he was in my class. I was really surprised and intimidated that an Excel MVP who has written a number of books on Excel was taking a class from me. My class was in the pre-Power BI desktop days, so I was going over the 4 Powers in Excel, Power Pivot, Power Query, Power View and Power Map. Jordan later told me that he learned something in my class. I was relieved. We’ve kept in contact since then, mainly via twitter, which is how I got asked to be on the show.

I was really impressed by Excel.TV and everything that Rick Grantham b | t , Szilvia Juhasz b | t and Jordan do to make the very professional, with graphics and sound effects. They asked me on to talk about Power BI. While on the ExcelTVshow, Rick asked me about the variety of things I have on my blog, and I got to thinking about it. Whether I use Power BI, Excel, Machine Learning, SSIS, SSAS or R, I am trying to do the same thing, make sense of the data and use the data to provide answers. You can call that data science or business analysis or business intelligence, but whatever the label or the tool, I think that really covers what I like to do.

I really enjoyed being interviewed, and I look forward to catching up with Rick and Jordan at the PASS Business Analytics Conference in May where we will all be speaking May 3-4. Maybe I’ll see you there too?

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 17 2016

Which Power BI Gateway: Personal or Enterprise?

Author: Ginger Grant • Discussion: 5 Comments

UPDATE: Power BI now has one Gateway, with the option to use a personal gateway. Check out this post on the new gateway for more information.

Power BI has two data gateways, Personal and Enterprise.  Since I assist companies with Power BI, the name powerBIGatewayPersonal Gateway made no sense to me, especially when I used a personal gateway to update an Organizational Pack. While this is a valid reason for a name change, after all Power Query is now called Get & Transform, so why not just rename Personal Gateway? I digress. Enterprise Gateway is not a replacement for Personal Gateway. It is partially an upgrade of the Power BI Analysis Services Connector, as it contains the features in that app and more. In the future, Enterprise Gateway going to be a way to manage all of the data connections within Power BI. I look forward to writing about that once it is available. The Enterprise Gateway, which I should mention is a Preview Release, supports three different Data Source Types: SQL Server, SAP HANA, and Analysis Services. One enterprise feature which is available is the ability to add users to the gateway you just added, rather than relying on one ID to grant access to everyone. For connections to SQL Server or SAP, the connection to the database is made via the user you entered, so that user should have appropriately limited reporting connections to the data source.

Power BI Data Access Based on User Security

Please note that right now, data access based upon user credentials only works for Analysis Services. For reports with an Analysis Services data source, the information passed to the server is the User Name of the user accessing the report. Using Active Directory, this user is granted the same access to the data on the server that they have on the on-premises network. Here’s an example, let’s say Jason is the sales manager for the Eastern Region, and doesn’t have access to the Western Region within Analysis Services security. Jennifer is the sales manager for the Western Region and has only been granted the ability to see the Western Region information in Analysis Services. If a sales report is created in Power BI which uses Analysis Services as it’s data connection via the enterprise gateway, Jason will only see the information on the Power BI report for the Eastern Region and Jennifer will see the same report with only the information for the Western Region. If Jason gets promoted to National Sales Manager and needs to see everything, once the security in Analysis Services is updated granting him access to all sales regions, he will see everything. Unfortunately, if you have 2008R2 or Standard Edition for SQL Server 2012 or greater, you won’t be able to connect to the server via the Enterprise Gateway.

Factors for selecting  Power BI’s Personal Gateway

The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t.   Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.

When might one want to use Power BI’s Enterprise Gateway?

All of the connections via the Enterprise Gateway are live connections to the underlying server, so there is no need to have a scheduled refresh. After all you are always using the connection to live connect to the server accessing the data. Enterprise Gateway imports nothing, so if you have really large databases which you are reporting upon, it probably makes sense to user the Enterprise Gateway as nothing gets copied. If you have a security policy which forbids storing data in the cloud, Enterprise Gateway meets that requirement as all of the data is stored locally and is merely accessed when needed, like a web page.

Future Plans for the Enterprise Gateway

Microsoft is planning on providing the ability to monitor and audit all of the data sources in the Enterprise Gateway. That would make it truly enterprise as it will provide the ability to see what data is being used throughout Power BI. When that happens, it might be time to get rid of the Personal Gateway. Right now, if the Personal Gateway is working for you for connecting to SQL Server, Analysis Services or SAP and the data refreshes are working, I would hold off upgrading. It’s not terribly easy to what refresh methods are configured in Power BI right now, which is also something that I hope gets fixed in the future as part of the Admin features. When Microsoft releases new versions of the Enterprise Gateway, I will definitely discuss them here. To get future updates, please subscribe to my blog to be notified when they happen.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 12 2016

Custom Visualizations in Power BI – Use with Caution

Author: Ginger Grant • Discussion: 4 Comments

*UPDATE* The Power BI Update team has resolved this problem. To read about how Power BI Works now, check out my blog post Time to Use Custom Visualizations in Power BI.

PowerBIVisualMessage-click

After looking at some of the neat custom visualizations with Power BI, like the fish or something like the synoptic panel, I was sold. Time to start using these neat visualizations in my reports in Power BI. I rather regret the decision. Why? I’ve provided my reasons here.

Warning Messages Every Time the Power BI Report is Viewed

Neither I nor the client is interested in seeing a message which looks like something sketchy is in the report. Reports are supposed to answer questions not leave people wondering what is wrong every time they look at the report. PowerBIVisualMessageThe message looks like some kind of a legal cover just in case the code is really bad. Worse the report is broken as the visualization will not show until the yellow button is clicked. Yes I can click on the button and the message will go away and look like the first report image shown above with the fish, but not for long. Like this meme, the message will never go away forever, but appear every time I or someone else opens the report.

Good Luck Getting Rid of the Custom Visualization in Power BI

If you were smart enough to add the custom visualization to the Power BI using the Power BI Desktop, you are in luck. I don’t have that kind of luck. I loaded it to the web application. Why does it matter where you loaded the visualization? Because you can’t get rid of it in the web version. Check out this link from Microsoft and scroll down to this part “Once you import a custom visual you cannot remove it from the report.” Even when I got rid of any use in the report to the custom visualization the link to the custom visualization is still there. The warning message never goes away. If you added the custom visualization to Power BI Desktop, you are in luck as Teo Latchev has posted instructions for you to get rid of the visualization in Power BI Desktop. It’s not a straight-forward process, but at least there it is possible.

So Long and Thanks for All the Fish

Until I have the ability to change my mind with design issues, which I though was sort of a prerogative, I’ll be staying away from custom visualizations. If I can’t turn the warning message off, I probably will never use custom visualizations. Hopefully you will read this before you had to do what I did, deleted the report and started over.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jan 03 2016

How do I Update my Power BI files?

Author: Ginger Grant • Discussion: 3 Comments

OneDriveIf you have been working in Power BI and use Power BI Desktop to create a data model and perhaps some of your reports, chances are from time to time you will want to change the Power BI Desktop file. Over time the data model created in the file, commonly known as a pbix file after the file suffix, may need to change. Updating the pbix file may not work how you expect.  If you want to be able to update pbix files, you need to save them on your One Drive. If you want multiple people to be able to maintain the Power BI Files, the best way to accomplish this is to use the Power BI Workgroup One Drive. While you can share a One Drive file with other people without creating a workspace, that file will not be visible to others from within Power BI as the location isn’t exposed to other people in the web interface.

What features do you get with Workspace and an Exchange License?

I heard recently from someone who listened to my PASS BA Webinar and didn’t understand why I said that one need to have to have an Office 365 Exchange client to use workspaces in Power BI. It is possible to create workspaces without an Exchange License, as the only thing required to create workspaces is a Power BI Pro License. While one can create workspaces without having an Office 365 Exchange client, chances are that isn’t all that you want for a team to be able to work together. When a workspace is created, often times it is created because you want a team of people to collaborate on the model and the reports. Collaboration can include modification to existing reports, data models or using shared content packs. To modify the data model, everyone needs to be able to edit the pbix file containing the data model and view the files from within Power BI. If a group of people want to edit each other’s the pbix files, a One Drive location where Power BI’s web client and all of the members of the workspace can access the file is necessary to make this collaboration happen. If you already have an Exchange server, you can still continue to use that as you don’t need to use the mail features of the Exchange license. However, since the ability to create groups which share One Drives is an Exchange feature, not a Power BI feature, you will need to purchase a license for each user in the group in order to have a One Drive area to share files. If you do not have an Exchange license, members of the group will be able to share Content Packs and Power BI Reports, but not the Power BI data model created in Power BI Desktop.

Let me know if you find this information helpful or if you have any other questions regarding team collaboration with Power BI. Feel free to contact me on twitter or by posting a comment on the blog.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur