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 Query to HDFS – Remote Name Could Not Be Resolved

I wanted to share a quick resolution I had to a problem with connecting Power Query to data stored in HDFS.

Seems easy enough. Launch Excel and through Power Query select From Other Sources –> From Hadoop Files (HDFS)

image

Next you’re prompted to provide your Hadoop server name then click OK.  I’ll be using the Hortonworks Sandbox environment for this demo.

image

I then see all the files available on HDFS.

image

If I try to explore the data in these files by clicking Binary next to any of the files listed I’m presented with the following error: DataSource.Error: HDFS cannot connect to server ‘sandbox.hortonworks.com’. The remote name could not be resolved: ‘sandbox.hortonworks.com’.

image

The issue here is with the name resolution and is a configuration issue. To fix this you must put your Hadoop cluster details in the HOSTS file on your machine that’s running Power Query. 

To edit the HOSTS file (I recommend you take a backup of it first) you’ll need to launch a text editor as administrator then open the HOSTS file found in the following folder:

C:\Windows\System32\drivers\etc

Provide the IP address and host name of the server as shown below then save.

image

Next time I try to connect to a file on HDFS everything now works properly.

image

Hope this helps!

Creating Power Map Reporting Solutions Recording and Q&A

I hope you were able to attend my free webinar on Creating Power Map Reporting Solutions on April 17 2014.  If you weren’t you can now watch the recording here.

Following each webinar I like to post a quick Q&A session for everything I was not able to answer in the allotted time.

Q: Am I correct in that you need the Pro Plus version of Office 365 and not the Home or University version?

Yes, that’s correct most of the Power BI features in Excel (Power Pivot, Power View, etc..) require the Office Pro Plus edition.

Q: I thought I could do Power Map without Office 365?

Prior to Power Map’s official release it was available to anyone running Excel 2013 (O365 or traditional) but when Power Map was officially release it was released as an Office 365 only feature.  You can however still download the preview and try it on non-O365 versions.

Q: Is there any way to use custom geographic data using shape file? For ex: school boundaries

I get this question a lot.  If your school boundaries fit within traditional geo-political boundaries (zip, county, etc…) you can make this work now but you cannot upload your own shapes yet.  To make this work now you would just categories each of you boundaries in something Power Map can understand.

Bringing Power BI Q&A to your Organization Recording and Q&A

I hope you were able to attend my free webinar on Bringing Power BI Q&A to your Organization on April 15 2014.  If you weren’t you can now watch the recording here.

Traditionally following a webinar I try to post a quick Q&A session for everything I was not able to answer in the allotted time.

Q:  Does Power BI require SharePoint Online or can it work with public facing on premises SharePoint?

Yes, Power BI sites and Power BI Q&A are only available if you’re using SharePoint Online.  Public Facing traditional SharePoint servers do not have the ability to add the Power BI functionality.

Q: If you create a PivotChart or Power View report, can Q&A show that chart when a user searches something relevant to the chart?

No, currently Power BI Q&A can only create Power View reports only the fly.  It cannot reference preexisting reports and display those.  You can however influence the way your data is presented by giving proper relationships, data types and data categories.  Once a Power View report is created on the fly you can change the visualization easily by selecting the the chart type you would prefer.

Q: Can I export the results to Excel and/or PowerPoint?

The current use of Q&A is as a quick ad-hoc analysis tool and does not have an output like this although I think this is an interesting thought. 

Q: Can i upload an Excel file to the Power BI site which is data connected to an SSAS source (locally hosted)?

You can as long as the SSAS source exposed through Power Pivot.  If you want to refresh any locally hosted data sources then you must configure the Data Management Gateway to allow local data sources to interact with the Power BI cloud.

Upcoming Webinar – Creating Power Map Reporting Solutions

image

Come join me for this webinar on 4/17/2014 at 11:00 AM EST

Creating Power Map Reporting Solutions

Excel 2013 with Office 365 brings many new features to the realm of Self-Service BI. There are now new ways to extract, model and present data all within Excel. One of these newest ways to present data is called Power Map. Power Map allows you to create engaging 3D map visualizations to help understand your geographical data better. This session will walk you through the beginning to end process of creating Power Map tours. Register now here.

Upcoming Webinar – Bringing Power BI Q&A to your Organization

image

Come join me for this webinar on 4/15/2014 at 11:00 AM EST

Bringing Power BI Q&A to your Organization

Q&A is an amazing new feature of Power BI that allows you to ask you data question. No longer do you have to write complex SQL queries to get results. Simply type traditional English sentences to return interesting results about your data. This session will walk through how to get a Power Pivot data model ready to use and also the end user experience of Q&A from the Power BI site. Register now here.

Introduction to Power BI Recording and Q&A

I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on March 13, 2014. If you weren’t you can now download the recording and slides.

I usually like to post follow up questions and answer that I wasn’t able to address during the webinar. Here’s several that may help you!

Q: Can you explain quickly how to use Power View on SharePoint 2010?

To use these together you must have SharePoint 2010 SP1 Enterprise and SQL Server 2012 Enterprise or BI Edition. Unfortunately, there’s not a quick way to describe the rest of the configuration so here’s a post that may help.

http://blogs.msdn.com/b/karang/archive/2012/04/13/step-by-step-guide-of-installing-power-view-with-powerpivot-2012.aspx

Q: What devices are supported by mobile BI

The Mobile BI will be available on Windows 8 and Windows RT devices. There is also a native Mobile BI app for iPad under development.

Q: Are MS planning to remove the dependency on Silverlight for Power View (etc.) thus making it possible to render on iPad?

Yes! There are actually some public betas of Power View working on HTML 5 instead of Silverlight. This will be the major enhancement that allows support for the mobile BI app on IPads

Follow

Get every new post delivered to your Inbox.

Join 37 other followers