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.

Office 2013 Preview – Enabling PowerPivot in Excel

With the Office 2013 announcements today I was excited to download and get my hands on the tools.  Because this is still a customer preview we’re bound to find things here and there that aren’t functioning as expected when experimenting with the Office tools.

The things I had on my list to test first were Power View and PowerPivot from Excel (Yes, Power View is now available in Excel).  In this post I’ll focus on helping you get started with PowerPivot.

With Excel 2013 you no longer have to download PowerPivot as a separate add-in but you may find when exploring the PowerPivot tab that all the available buttons in the Office Ribbon are greyed out.  The problem is that while the add-in maybe installed it is not enabled.  To fix this click File->Options as shown below.

image

Go to the Add-Ins page.

image

Change the Manage property to COM Add-ins then select Go

image

Check Microsoft Office PowerPivot for Excel 2013 then click OK.  You may also notice PowerPivot for Excel as an option here if you had a previous version of PowerPivot installed.

image

You should now be able to start working with PowerPivot.  Hope this helps getting you started!

Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

  • Ad Hoc Reporting
  • Content Sharing
  • User Flexibility
  • Developer Flexibility
  • Time to Develop

image

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

http://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

Choosing the Right Microsoft Reporting Technology Part 3: PowerPivot

Today I’ll be continuing in a blog series designed to help you decide which presentation tool is best for your reporting needs.  So far I’ve discussed in previous posts:

You learned how these two tools are almost opposites of each other.  Reporting Services is generally thought of as a static reporting tool, while Excel is usually used for ad-hoc reporting.  The products still left to cover are:

  • PowerPivot
  • PerformancePoint
  • Power View

Part three of the series will focus on PowerPivot.

PowerPivot

What it is

PowerPivot is much more powerful than any of the other reporting tools that will be discuss in this series because it does much more than just product reports.  While it does have the ability to create some impressive reports it really cannot create any addition visualization that regular Excel PivotTables can’t already do.

image

The real impressive part about PowerPivot is the modeling part of it.  With PowerPivot you actually design a modeling layer that brings in the different objects that you choose.  I purposely use the word objects generically here because you can import any data source that you can think of into PowerPivot.  Whether you need to bring in a table from SQL Server, a flat file or even something from DB2 it can all be done very easily with PowerPivot.  In fact, even if I needed to bring in all three of those objects into the same PowerPivot document and relate them to each other that is possible too.  Within the tool you have the ability to create logical relationships that may not exist on the source systems.  The view below shows how the new PowerPivot 2012 has improved the way relationships are designed.  Previously designing these relationships was done through a single dialog box instead of the new graphic diagram view shown here.

image

PowerPivot uses a fairly new in-memory technology called xVelocity (formally called Vertipaq) to handle all of the report processing requests.  With xVelocity Excel is able to process hundreds of millions of rows with amazing response times from a desktop machine. The xVelocity engine uses in-memory column-oriented storage and a highly compression data storage to produced the results you see today.

Another key thing to understand about PowerPivot is that the data pulled into PowerPivot is actually stored in the document an import is performed.  That allows it to use the store engine described earlier.  The one problem with this is that the data is static until manual update is kicked off.  When the update is run all of the data in the PowerPivot document is reloaded backed back in file.  Unfortunately, there is not incremental update yet so if you have a significantly large data source it may take a while update.  Later when I discuss how PowerPivot can be consumed I’ll talk about how SharePoint can assist in automating this data refresh process.  SharePoint is really the true way to scale out PowerPivot so other users can utilize the reports you build.

What it isn’t

One worry that many IT staffers feel about PowerPivot is that power users will begin creating these documents and making decisions off of them while10 other users have created simpler but different documents and get different results.  If you see something like this happening in your environment, which is completely possible, you could create a full Analysis Services solution (either tabular or multidimensional) to replace the PowerPivot documents to ensure all these users a consuming a single source for their reports.

It is also important to note that PowerPivot is not a replacement for any ETL that performs data cleansing or applies business rules.  Most data warehouses have a set of ETL processes that perform these tasks and that way all users are looking at the same data set.  PowerPivot does not eliminate the need for this.

Who Uses it

The goal is to have power users the driving force behind the creation of PowerPivot documents.  This would be a person that understands the source database(s), business needs, understands the concept database relationships and understands Excel all fairly well.

While that may be the goal for who should be using PowerPivot that is not what I actually see happening in the field.  I am still seeing most PowerPivot implementations being completely controlled by IT.

Over the last year I have seen this start to change so it does become more user driven but I think the problem that is preventing more users from getting their hands on the tool is education.  A lot of clients that I visit either don’t know what PowerPivot is or if they do they’ve never be taught (even in a simple demo) how it works.  So until this changes we may see a lot of PowerPivot solutions started in IT.

How is it consumed

The two ways PowerPivot can be consumed is the either directly through Excel or through SharePoint.  PowerPivot documents that are used directly through Excel rely completely on the machines resources they are viewed from.  So for example if I create and use a PowerPivot document on my laptop then it use all the resources of my laptop for importing data and processing results.  If I wanted others to see this report using this method I would have to either place the file on a shared drive or email it to those I want to view it.  That sounds terrible!

The best way to scale PowerPivot so that it is usable by a larger number of users is to setup SharePoint integration.  When PowerPivot for SharePoint is installed any reports viewed from a PowerPivot Gallery (SharePoint library for PowerPivot) run using a special Analysis Services instance to do all report processing rather than your laptops resources as previously described.  You may have noticed going through the SQL Server 2008 R2 install that there is an option to install SQL Server PowerPivot for SharePoint.  Using this SharePoint integration not only allows you to using Analysis Services for report processing but also allows you to schedule data refreshes, which is a huge help because normally data refreshes are a manual process without SharePoint.

f0801

Limitations

The major known limitations with PowerPivot are experienced when SharePoint is not part of the solution.  All of these have been detailed previously but as a reminder:

  • Refreshing report data in PowerPivot is a manual process
  • PowerPivot uses the resources of the machine the file is on
  • Sharing a PowerPivot reports can be difficult for large documents (shared drive or email)

PowerPivot for SharePoint of course addresses each of these limitations.  One other limitation that I have not detailed yet is working on 64 bit vs 32 bit PowerPivot.  I highly recommend if you use PowerPivot that you only do it with the 64 bit version.  You will find out very quickly when you begin importing data sources into you document that without 64 bit the process can be slow and painful.  You might even run into some limitations with the amount of data it will import on a 32 bit instance.  With 64 bit PowerPivot the sky is the limits though!

Summary

As we go through this series remember these high level characteristics about PowerPivot:

  • For power users that can understand a little modeling and relationships
  • Can bring in multiple unrelated data source together for reporting
  • SharePoint is the right way to make PowerPivot scalable

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PerformancePoint. To read any of the other parts to this series follow the links below.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers