Articles

DIY Guide to Content Packs

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

Non-technical Issues Impacting Data Based Decision Making

 Having worked with a number of clients to implement Power BI in their respective environments I noticed that one factor appeared to be common to all. The success of the project depended greatly upon the relationship between the business analyst and the database team. Since this seems to be an issue which greatly impacts the ability to implement Data Based Decision Making, I decided to talk about it in my recent webinar PASS BA Marathon. Too often I see companies which decide to join data together in an analytics platform, such as Power BI, and fail to take advantages of the separate skillsets in the organization. The data team has spent a considerable amount of effort and energy determining the best ways to combine datasets together. Logically one would assume that this expertise would be leveraged to help the business team analyze data. Instead the business teams are tasked with joining data together. While this approach can work, it will take longer to train the business in areas in which they may not be familiar, and the results will be mixed, especially when considering scalability and maintenance needs over time. To leverage the capabilities of the self-service business tool, which tool doesn’t really matter as the same issues will exist in for example Tableau as well as Power BI, the data team needs to be engaged. The skills they have gathered over time allow them to design a plan a data model which can be refreshed automatically without causing issues.

Using Areas of Expertise

Business Analysts time is best spent using the unique skills they have gathered over time too. Their familiarity with the data values allows them to determine at a glance how the business is doing. Codifying this knowledge into meaningful reports which can disseminate this information throughout the organization provides the basis for data based decision making. To make them successful, they need a data model which has all of the information they need which is well documented so that they can find the values they need to provide meaningful data visualizations. Too often the report generation is left to the data team, and many times there is a reporting backlog of items as there are not enough resources to do provide all of the information a business needs.

Team Collaboration

Data Based Decision Making should be an organizational goal, as it has been shown to be a major tool for business success. When the Data Team and Business Analysts work collaboratively by using their specialized skills to create and implement a solution, this solution will be successful. The result will be a model which provides the a path for the Business Analyst to continue to use the data to answer either routine questions, such as “How successful was the business last month” to more obscure questions, such as “What happened to sales volumes after a bad story in the press?”. These and many other questions are answered using the model and tools, like Power BI to implement an enterprise wide solution.

Implementing Successful Data Analytics Management PracticesPASS Business Analytics

There is more to implementing a self-service BI Tool such as Power BI than merely knowing how to make the tool work. A process and a commitment to work among teams is required as well. I enjoyed the opportunity to talk about integrating the tools with the company data management polices at the BA Marathon. If you would like to know more about this topic, please come join me at the PASS Business Analytics Conference in San Jose May 2-4 as I will be going into more depth than was possible in the webinar.

 

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Data Access and Self-Service Business Intelligence

There is more to implementing Self-Service Business Intelligence Business Intelligence than getting new software like Power BI, mindsets and practices also need to change. The data teams in many companies formed their policies based on history with previous technologies. One of those policies that is fraught with contention is letting the users have access to the data in order to do their own analysis. The reasons for this are based on a story like this one.  Like many a data professional, I worked at a company where we gave a team of users access to the database in order for them to do analysis. It was a replicated database, as we didn’t want to impact production. As these analysts primary skill was marketing not SQL, they wrote a query that took all the resources so no one had the ability to do anything else with the database, and we were required to intervene and kill the query to make the database useful again. After that, we changed their access to only being able to use views created for them to prevent this from happening again. Variations of this story exist all over.

Data Access has changed and so has the need for a 64-bit OS

Self-Service BI is supposed to be a way for Analysts to answer ad-hoc questions from Management about the business. While data professionals certainly could and do answer these questions, at some point a DatabaseAccessfocus line is drawn. If the primary focus is to determine the best way to write a query or implement an appropriate indexing scheme, this person has a technical focus and not a business focus. People with a business focus probably should be the person who use data to drive decision making. While technical people can write reports very efficiently, given the continual requests for answers from the data, keeping up with what the business people want to do can be extremely difficult as the numbers of reports required in various formats can be overwhelming. Like the old argument that “You don’t need a 64-bit OS” have become obsolete, so have the reasons for not giving business users access to the data. Now is the time to give them access. If you only have a 32-bit operating system, you don’t have the memory needed to do much data analysis. Data Analyst need 64 bit OS and access to the data.

What kind of Access should Analyst Have?

Most Analyst use Excel, which has become the de-facto tool of choice for data analysis. One doesn’t need to have a working knowledge of the SQL language to analyze data, and the scenario referenced above still happens. Instead data should be provided in a manner which is easy to consume in a Pivot table, allowing users to select, sort and filter the data at will. Analysis Services cubes, whether they be tabular or multidimensional provided this capability. Using a cube in an excel spreadsheet has very little chance of ever crashing a server, so go ahead and grant access. Give analysts the tools they need to provide the answers they need. Create a collaborative environment to grant access and provide the analyst what they need. In this kind of environment true data based decision making can really happen.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Creating a Successful BI Project starts with Data Modeling

Recently I was helping someone debug an Analysis Services Multidimensional project, and didn’t come up with much to help the performance. Why? The underlying data model was completely unwieldy and the fix, which no one wanted to do, was to redo it completely. Having worked recently with a number of business analysts to migrate there Excel spreadsheets to Power BI to support the growing trend to Self-Service Business Analysis, has made me think a lot about what makes a project a success.  Self-Service BI has been hyped as the way that analysis can better do their job and not involve technical resources.  While I support the move to the Analysts being more involved in with the data to make good decisions using the data, these kind of projects still need experienced data professions help them make a the project a success. There isn’t a tool which can fix a project with a bad data model. The problems the analyst have are not so much with learning the tool, as Power BI was designed to be easy to use. The problem is with data modeling.

Reporting Views; Modeling for the Moment

A lot of business reporting is developed by using the following process, which you may find where you work. The database team can’t keep up with the report requests, so they create a number of views and provide business analysis with some tool, be it Report Builder, Excel or Access to gather the data to do reports. This method provides the ability for analysts who don’t know much about data modeling to create reports based on the information is provided. This process works for a while. As long as the data people need to do their jobs is provided, reports are created and the Database team doesn’t have to be involved. This whole methodology starts blowing up over time. Why? The reporting time starts to increase.

The Reporting Time Explosion

Once I was working at a company where the person in charge of doing the performance reporting went on an extended medical leave, and trained someone else on what was required to get the data and create the reports. She gathered data from this system and that system, added in some information on a spread sheet, ran some macros did some queries, updated some Excel spreadsheets and after that the reports were generated. This process required three hours every day to do this complicated series of task and a full week for monthly reporting. It took all of about two days for her replacement to be overwhelmed, and the task of doing the reports came to me. After a week, I had gathered all of the data together for the daily reporting and automated it, which took the daily reporting process from three hours a day to seconds of computer time. It took a couple of stored procedures, some SSRS reports and a new process for storing the data not in an Excel Spreadsheet, but in the application where it was supposed to be entered. By the end of the second week, the monthly reporting was completed as well. A task which took the majority the time person spent her day, was automated to button clicks in less than two weeks. Why? The task of gathering the data was given to someone who understood databases and data modeling. That’s the knowledge that is needed to set up a successful BI Project.

Business Knowledge needs to be combined with Technical Knowledge

To be an expert at something takes time and focus. There are only so many hours in a day, and if you are focused on spending those hours on creating technical solutions, you are bound to get really good at applying technical knowledge gained to solving problems. Likewise, if you spend all of your day looking at the data trying to solve business problems and answer questions about how the decisions made impact the data, you are going to get really good at analyzing business data. Tools help provide the ability to answer questions, which can be answered because the data model supports the type of analysis needed. To figure that out, someone who knows about data modeling needs to be involved to ensure the Self Service business intelligence project has a good foundational data model.  If that’s not there, it doesn’t matter what the tool is, the project won’t be successful.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Getting started with PowerBI in Office 365

When speaking at SQL Saturday #292 – Detroit, I received a question which I thought more people might have. How do I get started using Power BI or PowerPivot? I have Office 365 and I can’t get to the PowerPivot Menu and I don’t see Power Query either. What’s wrong?
I am assuming in that case your version of Excel looks like this.

image 1

 

Power BI was officially released on February 10th 2014. You might think that would mean that if you installed Office 365 after February 10th, which I did here, that Power BI would work out of the box. Sorry, that’s just not the case.

Turn on the Power

In order to get PowerPivot, Power Map, Power View or Power Query working you need to turn them on in Excel first. Click on the file tab in Excel and you’ll see this screen

image 2
 

 

 

 

 

 

 

 

 

 

Click on the Options menu item and you’ll see the following screen

image 3
Notice there are two sections to this window, Active Application Add-ins and Inactive Application Add-Ins. I’ve highlighted Power Pivot, which is listed under Inactive Application Add-Ins. This is why you don’t see it, it’s not active. Sounds reasonable, just click on the GO button and fix it right? No, wrong.

Type Tricky

The trick to adding add-ins is looking at the type. PowerPivot is type COM Add-in. If you click on the Go button you will see all of the Excel Add-ins, won’t turn on PowerPivot. When is an Excel Add-in not an Excel Add-in? When the Type says so. If you click on Go, this is what you will see

image 4

 

 

 

 

 

 

 

 

 

 

 

PowerPivot is a COM Add-in, so it isn’t on this menu. Cancel out of here and go back to the Add-In screen and change the drop down box to COM Add-in before clicking on the Go Button. This menu is what you need for getting 3 out of the 4 powers.
image 5

 

 

 

 

 

 

 

Go ahead and check the boxes next to PowerPivot, PowerMap and Power View like you see here. Click ok and then you’ll see the PowerPivot tab in Excel.

image 6

You still won’t see the Power Query Tab, because that has to be downloaded separately, which you can do for free here
https://www.microsoft.com/en-us/download/details.aspx?id=39379
Pick the one version you need, either the 64 or 32 bit version and follow the installation instructions, which means getting out of excel first as it will only install when it is not loaded.

image 7

 

 

 

 

 

 

 

 

 

 

 

After you finish the installation, go ahead and open Excel.

Power Signs

The Power Query tab is magically added, as the install takes care of adding the add-in for you.

image 8

 

When Excel looks like this, it’s time to get started with PowerBI!

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Power BI Licensing and Constraints

Licensing is a topic which most people reflexively ignore. They just scroll down until they see the I agree button and click it so they might attain the object of their desire, software with which to amuse themselves.  With respect to Power BI, now might be the time to read the fine print.  The license dictates how much data you can display, which you need to evaluate to see if this is the best tool for your environment. Let’s go to the authoritative source regarding such matters, Microsoft.  Here’s a link to their Power BI release and pricing page http://bit.ly/1e9dAEB .  If you didn’t know, as of right now, Microsoft is only licensing Power BI with Office 365.  Based on their page it appears if you don’t have Office 365, you can’t have Power BI.  If you have been playing around with Power BI, you know that’s not really the case.  

Power BI Components

To ensure that we are all starting from the same place, here’s a summary of the Power BI Preview components. Power BI consists of four Excel add-ins, Power View, Power Pivot, Power Query and Power Map.  Power windows are not included.  Together these tools are used to create a Power BI document to be shared on to the Power Cloud, SharePoint on Office 365.  The add-ins Power Query and Power Map were created for the Power BI preview, because two powers are just not enough you need a quad. If you want to be able to deploy a Power BI report to your tablet or smart phone with HTML 5 or use the natural language query feature Q&A to manipulate the data, both of which are part of the preview, you can only do this from Office 365 SharePoint.  If you desire to distribute your Power BI creation to a mobile device such as a smart phone or tablet, you will then need to send the app to the Windows Store so you can distribute your report app to said devices. 

Help! I don’t have Office 365

If you have loaded Office 2013 or Office 2010 service pack 1 to your computers can you still use Power BI? This is where licensing gets tricky. Sure, you can do it, at least at present. Power BI is in Preview Release mode, and as part of the preview you can download the preview add-ins for both of those versions of Excel. Can you use the add-ins you have once it goes into full release?  This remains unclear.  Excel 2010 has Power Pivot included and Excel 2013 has Power View also so both of those components don’t require a Power BI license.  But Power Map and Power Query add-ins both say they are Preview Editions. What happens after the preview is over? Yet another ambiguity.

If you have seen Power View back when SQL Server 2012 was released you may think that you might have all of the same features of Power BI without having Office 365, but that isn’t the case. Power View was released as part of SQL Server 2012, so if you have SQL Server 2012 you can use Power View with SharePoint 2010 or 2013.  Power View for SharePoint has a map feature as well but the feature set is not the same as Power Map. What about Power Query on SharePoint? Power Query is part of Power BI and right now it can’t be loaded on your current SharePoint server.     

Preview Edition

As of this writing, Microsoft has yet to offer Power BI for sale to end users, although you can easily obtain a preview of it.  If you do decide to give Power BI a whirl, you will be granted a preview license.  The preview license terms differ in significant ways from the proposed release version.  Keep this in mind when you noodling around with Power BI.  If you want to load a file to the Cloud (aka SharePoint on Office 365) the maximum file size is 10 MB.  I divined that tidbit here http://bit.ly/1cajntG .

Implications of Excel File Size or Why my Power BI won’t load

You may have learned, like I did in an online presentation, that the maximum size for a Power BI Excel file is 250MB. .  I discovered the file size is dependent on your license, which creates some interesting things to consider. The 250 MB file size does not apply to the preview license version, which seriously dampened my enthusiasm. I was rather chagrined to discover this inconvenient limitation, after my carefully crafted 45 MB presentation file failed to load.  I started looking around for the size that would load, as it was clear mine was too large, making it clear the 250MB limit isn’t true,  as the preview license limits you to 10 MB.  When you buy Office 365 you pay for how much space you have in SharePoint and there are limits which Microsoft reveals to you here http://bit.ly/1bCeI72 . This website states that licensees only have 500MB per user regardless the plan for Office 365.

Power BI is meant to be the tool for all business users to query their data. If you are designing a model for use in Power BI your space is limited.  Given it’s current configuration, it is assumed your entire operational data store could not possibly exceed 250 MB.  If you have a large tabular model, you may find using Power View in SharePoint will provide a better solution as these data model constraints don’t exists there. Power BI is great tool, but the scalability limitations cannot be overlooked.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur