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.

image

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:

image

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.

Conclusion

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.

Power BI for Office 365 Released

Today Microsoft has officially announced the release of Power BI for Office 365.  You can read the Power BI teams post on the release here:

http://blogs.msdn.com/b/powerbi/archive/2014/02/10/announcing-the-general-availability-of-power-bi-for-office-365.aspx

Power BI has some pretty amazing features that allow users take control of their data.  I’ve also written several posts in the past about the Power BI features you can browse here:

Power Pivot

Power Query

Power View

Power Map

Power Q&A

Sign up for Power BI now here!

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 Voting

You’ve watched my video (hopefully) now vote for it!  In a previous post I wrote about my Power BI contest entry (Read more about it here).  I really enjoyed making this video and hopefully you enjoyed watching it.  Please take a few moments and vote for my entry here.

http://j.mp/1cFcLGj

 

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.

Power Pivot Synonyms

A little gem was included with the latest Office 365 update.  After downloading and installing this update you will now find that a feature called Synonyms is available inside Power Pivot.

image

Synonyms are a piece of the puzzle that make the Power BI feature Q&A work.  If you haven’t seen Q&A watch these two videos and sign up for the Power BI preview yourself.

http://blogs.msdn.com/b/pedram/archive/2013/07/16/the-natural-language-capability-of-power-bi.aspx

http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2013/10/02/from-data-to-insight-amp-impact-preview-of-the-q-amp-a-feature-of-power-bi.aspx

I would expect some official guidance on how Synonyms work soon but it’s pretty clear you create a common separated list of words that would be considered synonyms for each column/table to make the nature query language searches possible on the Power BI site.

So in my example I gave the column called gender a synonym as shown using a common separated list.  This would allow the user to type in either word in Q&A to still get results.

Again, expect more guidance on this soon.  For now these are my assumptions on how these would work.  Right now when you click the “Click here for more information” button it sends you to the www.PowerBI.com main information page.

**Updated 12/18/2013**

The documentation has been updated for synonyms and now directs users here.

http://office.microsoft.com/en-us/excel-help/add-synonyms-to-a-power-pivot-excel-data-model-HA104143188.aspx?redir=0

Dealing with Power Pivot to Tabular Upgrade Errors

Upgrading from Power Pivot workbooks to SSAS Tabular models is usually a pretty seamless process.  This is part of the beauty of the relationship between Tabular and Power Pivot.  This allows an Analyst to get started on a project using Power Pivot and when new requirements like row level security are needed IT can upgrade the workbook to Tabular and add the security feature. 

While this generally is a smooth process I often see the same couple error messages pop up for folks that are doing this.  So here’s a couple of the most common problems and how to deal with them. 

Error:  The file chosen does not contain PowerPivot content embedded in it. Choose a valid PowerPivot for Excel file.

image

Translation:  The version of Excel that you are trying to upgrade is not compatible with the version of SQL Server you are running.  This often happens when you are trying to upgrade an Excel 2013 workbook to SQL Server 2012.  You can narrow this down to the problem because if you attempt to upgrade and Excel 2010 workbook it works without any issue.

Solution:  This problem has been solved with SQL Server 2012 SP1 and Cumulative Update 4.  Install these and you shouldn’t see the issue any more.

=================================================================

Error: The PowerPivot workbook could not be imported. The service account for the workspace database server does not have permission to read from the PowerPivot workbook.

image

Translation: The Service Account that is running the SSAS Tabular does not have rights to the folder that the Power Pivot workbook is stored in.  You can find out which account this is by launching the SQL Server Configuration Manager and find the instance name.

Solution:  Grant the Service Account the permissions it needs or move the Power Pivot workbook to a folder that the Service Account does have access to.

If you know the Service Account does indeed have access but still is not able to import with this error then again you likely need to upgrade to SQL Server 2012 SP1.

Summary

As you can see both of these problems can be solved by upgrading to the latest Service Pack.  Remember SSAS Tabular was a new product to SQL Server 2012 and as you may expect with a new product if will go through some growing pains.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers