Power Pivot as the Core to your Power BI Solution

I’ve noticed a recent trend with many presentations and articles on Power BI and Excel functionality. Many of these presentations show how quickly you can take tools like Power Query to find and discover data and then quickly present it through tools like Power View and Power Map. These are really impressive demonstrations of what you can do in Excel in a matter of a few minutes to start with no data to quickly making business decisions through some form of a reporting layer.

While I really enjoy these demonstrations my one gripe is it doesn’t tell the full story. Take the example I just gave of discovering data with Power Query then presenting in in Power View.  In the excitement of taking the results of Power Query into Power View we often forget there is another important tool involved. I’m certainly guilty of this as well but I thought it was worth some explanation of what’s really going on behind the scenes.

By now you have probably learned that Power Query provides the option to put your data into Power Pivot immediately after you have finish transforming the data by selecting Load to Data Model.


But what if you don’t choose this option? You go with the default Load to worksheet setting and go about your business. Your data gets imported into an Excel worksheet and then you have to make a decision on how to visualize it.

This is what is often skipped in presentations. As soon as the decision is made to present your data through either Power View or Power Map the data in this Excel worksheet is automatically pushed into Power Pivot.

Let’s get a bit of an expanded view of how all these parts work together:


Another way of looking at this is based on the tool:

Power View

Power View reports can be developed from either Excel 2013 or SharePoint (On Premises) 2010 SP1 with SQL Server 2012.

If using Excel 2013 at some point the data must be placed in Power Pivot to visualize it in Power View.

If designing reports through SharePoint you can design reports on top of a Power Pivot workbook deployed to SharePoint, a connection to Analysis Services Tabular deployed to SharePoint or a connection to Analysis Services Multidimensional deployed to SharePoint.

Power Map

Power Map can currently only be done within Excel 2013 and Office 365 (Power Map preview is still available for On Premises Office). The data in Power Map must be based on top of a Power Pivot workbook.

Power BI Q&A

Power BI Q&A can only be done through a Power BI site and Office 365 subscription. The Q&A interaction is all based on top of a Power Pivot workbook that has been deployed to a Power BI site. You can optimize the Q&A interaction by adding things like synonyms to your Power Pivot workbook.


What you can get from this diagram is that Power Pivot is central to creating a complete Power BI solution. A well designed data model can only increase the chances of your reporting layer being successful. A poor design data model can make it difficult to make appropriate visualizations. So as you start exploring the Power BI tools make it a point to have a well designed Power Pivot data model.

Final Day for Power BI Voting, Can I Have Your?

Today is the final day for the Power BI Contest voting and I could use your help to move on in the competition!  Please take a moment and vote for my video here  http://tinyurl.com/PowerBIDevin

Thanks for voting!

Power BI Contest Entry

Today I submitted my Power BI contest entry and I’d love for you to take a look!  Here’s a short description of entry and the video itself:

In this submission I have used Power BI to help me plan a vacation with my wife. We both love thrill rides so I wanted to make the best decision on planning our next trip. Using Power Query I imported data from the web to find all possible roller coasters and amusement parks. I then took that raw data and made relationships and calculations using Power Pivot. Next, I visualized the results through Power View to help guide me through the data easier. Finally, I uploaded my workbook to a Power BI site and enabled Q&A to allow my wife to ask the workbook questions about our trip.


Find out more about this contest here and put in your own contest entry.

Understanding Microsoft Self-Service BI Recording and Q&A

I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on September 26, 2013.  If you weren’t you can now download the recording here.

Because I covered new material all the way to the end of the webinar i thought I’d also answer some of the top questions I didn’t have time to answer here.

Q: Can an Excel Power Pivot 2013 model be read by Excel 2010 or Excel 2007? What about Excel versions and their compatibility with SharePoint Versions?  I ask because we are upgrading our SharePoint environment to 2013 but most of our users have Excel 2010 (a few have 2007).

You can open an Excel 2013 workbook that has a Power Pivot model in older versions but you cannot modify them.  You can however deploy Excel 2010 and Excel 2013 workbooks to you SharePoint 2013 environment and it they interact the same.

Q: Is there a way for me in us my own map?  ex. wards/districts in my county

I assume this one is referring to Power Map and the ability to map geopolitical regions.  It doesn’t currently have the ability to your own regions yet.

Q: Where do we download power map?


Q: Can you change to use google maps instead of bing maps?

No, Microsoft tool = Microsoft maps! :)

Q: Could I use relationships using more than one field?

No, Power Pivot only has the ability to create relationships on one field at a time.  If you need to join on more than one field, which in the real world is often, then you have to get creative.  In the quick example I showed we merged two columns together to make a single join column that could be used for our relationship.

Q: Is Power Query available for Excel 2010?

It sure is!  This is one of the great things about Power Query is that it is compatible with both Excel 2010 and 2013.

Q: How can you share Power View reports? Do you have to share the excel file or can you display in SharePoint?

You have a couple options.  You can share the excel file like you’ve suggested or you can deploy it to SharePoint.  If you develop the Power View reports in SharePoint you can also export via PowerPoint.

Q: Will these slides be available

Sure thing!  Download them here.

Q: The maps features is very interesting.  Can it do counties within a state .. and neighboring states?

Yes, Power Map has the ability to regionally map counties and zip codes as well.

Whitepaper: Building Self-Service BI Solutions with Power Query

Self-Service BI with Power Query

Through Self-Service BI, business units can personalize Business Intelligence to their needs and solve problems at a much faster rate than any traditional BI solution. This is why businesses are looking to Self-Service BI to solve the smaller, but no less significant, problems that individual departments need addressed. The goal of this white paper is to focus on using and understanding one of Microsoft’s latest Self-Service BI tools called Power Query.

Building Self-Service BI Solutions with Power Query

Download the Whitepaper now!

Using Parameters in Power Query Extracts


By now you have likely heard a little about Power Query (formally known as Data Explorer).  While you may have heard of the tool many still have not gotten their hands on it and started experiencing the potential it has.

In case you’re new to Power Query here’s a couple quick points before continuing on:

My goal with this post is to show you how simple yet powerful Power Query really is.  I’ll do this by showing you an example of solving a problem that would be fairly complex using traditional ETL tools like SSIS (SQL Server Integration Services) but made simple with Power Query.


Power Query has the ability to do some basic “screen scraping” of data from web pages and add this as a new data source to your Self-Service BI solution. The problem that often occurs when doing this is that there are often dozens of pages or filters that need to be modified to get a full historical view of the data on the web page.

To solve this problem we can leverage parameters in the Power Query Formula Language to navigate through this data. Even though Power Query is new there are a couple very good resources for learning it that can be found here.

To make this example fun we will be pulling our data from data from the National Football League website (www.nfl.com). When pulling historical data about teams from the league the website only permits users to view one year at a time. However, our goal is to view how teams have performed across all time.


To solve this we will create a parameter using the Power Query Formula Language to dynamically pass in the years that are needed to extract data across all time. Next we’ll walk through a beginning to end example on solving this problem.


Once this data is collected I would like to prove or disprove a hypothesis of mine. I believe that the fewer penalty yards accumulated by a team’s offensive will result in better performance. To determine if I’m right we will apply a visualization to the data once we’ve completed importing it.

Step by Step

  1. Launch Excel 2010 or higher. My screenshots are all done using Excel 2013 but everything should look the same until the data visualization section.
  2. Select the Power Query tab.
  3. To complete this example we’ll need to enable advanced query editing. Under the Machine Settings section select Options.


  1. Check the option called Enable Advanced Query Editing then click OK. This setting is turned off by default.


  1. Click From Web under the Get External Data part of the Office Ribbon.
  2. Use the URL http://www.nfl.com/stats/categorystats?tabSeq=2&statisticCategory=GAME_STATS&conference=ALL&role=TM&season=2012&seasonType=REG then click OK.


  1. In the Navigator pane select Table 0, which has the content we need for this demonstration.


  1. Rename the query by double-clicking on the query name in the top left of the Query Editor. Change the name from Query1 to Team Stats.
  2. Now click the Edit Query button in the top right of the Query Edit.


  1. Add the following parameter definition above the existing query:

(getYear) =>

  1. Look at the URL string that is part of the query and replace the hardcoded year 2012 with the following expression:

” & Number.ToText(getYear) & “

Click Done. The full query after these two changes should look like this:

(getYear) =>


Source = Web.Page(Web.Contents(“http://www.nfl.com/stats/categorystats?tabSeq=2&statisticCategory=GAME_STATS&conference=ALL&role=TM&season=” & Number.ToText(getYear) & “&seasonType=REG”)),

Data0 = Source{0}[Data],

ChangedType = Table.TransformColumnTypes(Data0,{{“Rk”, type number}, {“Team”, type text}, {“G”, type number}, {“Pts/G”, type number}, {“TotPts”, type number}, {“Scrm Plys”, type number}, {“Yds/G”, type number}, {“Yds/P”, type number}, {“1st/G”, type number}, {“3rd Md”, type number}, {“3rd Att”, type number}, {“3rd Pct”, type number}, {“4th Md”, type number}, {“4th Att”, type number}, {“4th Pct”, type number}, {“Pen”, type number}, {“Pen Yds”, type number}, {“ToP/G”, type text}, {“FUM”, type number}, {“Lost”, type number}, {“TO”, type number}})



  1. You can now click the Invoke button to pass in any year value you want and get different results based on your selection. Click Invoke and type in the year 2009. Click OK.


  1. This returns all data for the year 2009 for all teams. Our next step is to return data for every year for all teams. Expand the Steps pane on the right side of the Query Editor and click the X next to InvokedTeam Stats. This removes the last action taken, which was passing in the value of 2009 into our parameter. Click Done.


  1. Select the Power Query tab.
  2. Select From Other Source > Blank Query


  1. In the Query Editor formula bar type the following expression:

= {2002..2012}

This will automatically create a list of the last 10 years from 2002 to 2012

  1. Right-click on the column header and select To Table to convert this list into a table. You will be prompted with some settings for changing this list to a table. Just accept the defaults and click OK.


  1. Right-click on the column header again and select Insert Column > Custom
  2. Use the following formula to pull in data from the parameter driven function we created earlier.

#”Team Stats”([Column1])

clip_image021 NOTE: The Power Query Formula Language is case sensitive


Double quotes are only needed here because there is a space in the name of the other query. Click OK.

  1. Click the Expand button next to the new custom column then click OK.


  1. This should give you a preview of all team stats across the last 10 years. Rename the query in the top left of the Query Editor from Query1 to Full Team Stats.
  2. Multi-select the columns Column1, Custom.Team, Custom.Pts/G, Custom.TotPts, Custom.Yds/G, Custom.Pen, Custom.Pen Yds then right-click and select Remove Other Columns.


  1. Rename Column1 to Year and rename all the other columns to remove the prefix of Custom. from the name. Columns can be renamed by right-clicking on them and selecting Rename.
  2. Multi-Select the columns that have our aggregate data in it then right-click and select Change Type > Number as shown in the screen shot.


  1. Finally, click Done to actually import the full dataset into Excel. Depending on the web site you’re extraction could take several minutes.
  2. With the data now in Excel click the Load to data model button inside the Query Settings pane on the right of your screen. This sends to data directly into Power Pivot.


This is a feature only available in Excel 2013. If you’re using Excel 2010 then you must launch Power Pivot and go to the Design tab and chose from Existing Connections to do the same behavior.

Visualizing Data (Cherry on top)

Now that we have the data in Power Pivot it should be fairly simple to visualize it. We could go with a straight forward approach and use PivotTables, but since this demonstration was done using Excel 2013 let’s use Power View.

If you’d like to replicate this demonstration but are using Excel 2010 you can do so by deploying your workbook to a Power Pivot gallery in SharePoint 2010 SP1 that uses the SQL Server 2012 Reporting Services add-in.

Remember this is the section where I should be able to prove or disprove my hypothesis about the team’s offensive performance being impacted by penalties.

  1. Go to the Insert tab in Excel 2013 and select Power View.
  2. Delete any visualizations that Power View may have automatically tried to create for you.
  3. Close the Filters section by hitting the next in the top right next to the Filters pane.
  4. In the Power View Fields list expand Full Team Stats table and select Pen Yds, Pts/G, Yds/G, and Team.
  5. With these fields selected change the visualization to a scatter chart by select Other Chart > Scatter in the Design tab.


  1. Expand the scatter chart so it takes up the entire design surface except for the title.
  2. From the Power View Fields list bring the Year column from the Full Team Stats table into the Play Axis of the Chart properties.


This chart is starting to tell us some interesting things. It looks like the least penalized team, the Atlanta Falcons, is on the upper half of offensive performance but not the best. The best offense is the New England Patriots and they’re about middle of the pack when it comes to penalties.

Probably the most interesting thing I find here is that the Super Bowl winner from 2012 was the most penalized team and about middle of the pack when it comes to offensive output. That just goes to tell you that in American Football there’s another half of the game we’re not analyzing here. Baltimore is well known for have a great defense, which would explain the discrepancy. Overall it looks like my hypothesis cannot be proven right.

  1. Now, if we hit the Play button in the bottom left of the chart we can see across the last 10 years.
  2. It looks like the most consistent offensive team has been the New England Patriots. We can focus on the New England Patriots by clicking on their bubble in the scatter chart and it shows over the last 10 years Patriots have been an outstanding offensive team.


  1. Click somewhere in the background of the chart to remove the filter on the Patriots.
  2. Finally, give the report a title of NFL Offensive Performance to complete this example.

This is the kind of amazing analysis you can do with Power BI. I hope you’ve found this useful and can apply it to your own work!

You can download the sample workbook for this example here.

Power View Reporting on Multidimensional Cubes Released

I’m a little late to the celebration but on Friday some there was some exciting news regarding Power View was announced. You can read about the details of it at the Analysis Services and PowerPivot team blog for Microsoft here.


This is part of a SQL Server Cumulative update pack that you can download here.


One thing to probably make mention of that you can ready on the Microsoft team blog is that this is only for SQL Server and SharePoint Power View.  If you’re using Power View with Excel 2013 only then you’ll have to wait a little longer for an update.

Either way this is great news and for those that were anticipating projects to convert Multidimensional cubes to Tabular or PowerPivot so they could use Power View.

Getting Started with Microsoft Data Explorer

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from http://www.microsoft.com/en-us/download/details.aspx?id=36803.

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go


Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.


Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.


Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx.

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

  1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
  2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.image
  3. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.image
  4. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.image
  5. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
  6. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.image
  7. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.image
  8. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.image
  9. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
  10. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:image

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

Building Your First Power View Report

This has been reposted from a guest blog post I wrote on the Microsoft Business Intelligence team blog.

Fulfilling User Needs

Developing a Power View report has been touted as being so simple that within a handful of clicks you can add rather impressive elements, like effective visualizations, to a report. This isn’t always the case with other tools in the market. The goal of this post is to walk you through Power View as if you were designing a report for the first time, and show you how simple it really is to build a Power View report.

Using the Development Environment

Before we start, it’s important to note that you will need SQL Server 2012 and SharePoint 2010 to create a Power View report. Power View also runs on Silverlight 5, which enables you to edit Power View reports in a browser window, as shown below.


The development interface retains the familiar look and feel of Office tools. If you know how to use Excel, picking up Power View for the first time should be a breeze.

Understanding your Data

Understanding your data and what you want to achieve with it is one of the most important aspects of building a Power View report. The better a report designer understands the layout of the data model, the easier it becomes to produce insightful results. Power View is designed to help you produce fast results in an easy to understand format.

When designing PowerPivot reports with images and visualizations, you may want to consider BISM Tabular data sources for Power View reports. Shown below is a report that leverages a data set with images enabled by BISM Tabular data sources.


Building your First Report

In this demonstration of building a basic Power View report I will be using a data set found on Microsoft’s live demo site. To follow along go here to access the blank Power View Report (this will require a Windows Live ID).

Upon launching this live example you will see a blank report in presentation view. To edit this blank report, select Edit Report in the top left of your screen.


You can create report elements by selecting the data you would like to visualize from the data pane on the right side of your screen. It’s helpful to know how your data model is organized because you may find yourself struggling to find the fields you need otherwise.

Next, from the Product table, select Image and then use the Visualizations toolbar to set the field as Tiles. This will place the product images in slide tile view. You may need to stretch the tiles across the width and the length of the report for layout purposes.


To capture the details of each product in the slide tile view, select a single product image and select Card from the Visualizations toolbar dropdown. Add the fields Product Name, Category, Channels, Item Group, SKU and Retail Pricefrom the Product table. To fit all of the information you may need to resize the Card as shown below. Think of the Card as a baseball card that captures all the key information about a product.


Next, we will add regional sales information to the report. To get the regional data to interact with the product image tiles, select a tile and pick Region Map and Region Name data elements from the Region table. From the Sales table selectRevenue. This gives a tabular view of the region sales for the selected product with an image that highlights the part of the country that makes up that region. Move this table to the bottom right of the report to improve the layout of the report.


To complete the regional view of our report, we will put together a bar chart of profitability metrics by product. Begin by selecting one of the tiles and pick the data elements Profitability from the Sales table and Wholesales Price from the Product table. Turn the new table into a chart by selecting Clustered Bar from the Visualizations toolbar. Resize and move the chart to the bottom left of the report. Lastly, name the report Product Sales Report. The final result of this first view should look like this.


Power View reports can have multiple views, which appear as multiple pages to a user. This allows users to easily flip through multiple reports in a single Power View document. To create a new view, select the View context menu on the left side of the design surface and click New View as shown below.


In the new view we will demo one of the most impressive visualizations in Power View –the Scatter chart. To begin, go to the Sales table and select Revenue, Quantity, and # Products, and from the Product table select Category. Once the data elements are selected, choose Scatter Chart from the visualizations toolbar to turn the table into a chart. The result is a nice scatter that shows which product categories generate the most revenue for the business. To take this chart to the next level, we could add animation to it by dragging Month from the Date table to the Play Axis of the chart. Resize the chart to fill the entire report view and hit the play button on the bottom left to view product category performance over time. Lastly, name the report Sales Analysis by Month.


With this report complete, you can view it as a user by selecting Full Screen under the Home tab of the toolbar. This allows you to flip through multiple views of the report and preview the report views by selecting the button in the bottom left of your screen.


Hovering over the views, you get a larger preview on the top report. When you select a report, it appears in full screen mode.

Going through this demo should have given you the idea of how with just a few clicks, you can create impactful, insight-driving reports with Power View.

SQL Lunch #68 – Designing Your First Power View Report

Next week I will be speaking on designing your first Power View report for SQL Lunch.  Be sure to join me for the event and as always “No fluff, just stuff”.

Add To Outlook: Add To Calendar

Date and Time: 7/18/2012 11:30:00 AM CST

Topic: #68-Designing Your First Power View Report
Power View has the capability of creating eye-popping visualizations within SharePoint 2010. It provides intuitive ad-hoc reporting that can be used by a variety of business users to make critical decisions. In this session you will learn how to design your own dazzling reports using Power View.


Get every new post delivered to your Inbox.

Join 36 other followers