How to load Power Query directly into Power Pivot

I have to admit, I like Power Query more and more every day. Even though on one hand sometimes I feel that the tool will only appeal to a very specific type of power users, on the other hand, I can’t help but feel that the tool is so good that I wonder if it has a potential to dramatically redefine where we typically think power users world ends and the world of IT begins.

There was, however, one little thing that left some bad taste in my mouth when I tried to apply the tool in a real life enterprise scenario. Power Query and Power Pivot are the only two Power BI features that are actually made available in Excel 2010 (i.e., they don’t require Excel 2013). I have been using them in Excel 2013 for a while and one of my favorite features was Power Query’s ability to source files out of a folder, concatenate them together and then load them into a Power Pivot bypassing 1M row limit in Excel.

The unfortunate thing when I started going through the same exercise with a customer was that the client only had a 32-bit version of Excel 2010 (and frankly, I have to say that probably 90% of enterprise customers still fall under this category). To my disappointment the Load to Data Model option that I was accustomed to in Excel 2013 was not available in Excel 2010.

The only work around that I found was to limit the data set to about half of the files, load data to worksheet and then use Linked Table feature of Power Pivot to add it to my model. Obviously less than elegant solution.

Yesterday, I was shown a much better way.

In one of my previous posts I demonstrated how to use a web page as a Power Query data source.

As I went through my Power Query design, I would always either have Load to worksheet or Load to Data Model checked. However, apparently, it is totally fine to have both of them unchecked, in which case (well, I guess rather in any case), the Power Query is saved as a Workbook connection and can be accessed by clicking on Data->Connections

Now, we can either use it to create a regular pivot table by clicking on Existing Connections in the Data section of the Ribbon and then picking the right connection from the list

And then selecting Pivot Table or Pivot Chart

Or, one can go to Power Pivot, use Existing Connections option as well and then use that Power Query connection as source.

This is a much more elegant solution as it does not require the intermediate load of all the data into an Excel table. Had I known that trick before, I would not have to delete half the files from the folder and my guess is that the load time would have been cut in half as well.

Another interesting observation, if this Power Query connection is used for a Pivot table, then right clicking on the pivot table and selecting Refresh, executes the Power Query on the data source and refreshes the data in the table.

Microsoft self-service BI on premise vs cloud

It appears that Satya Nadella has added an additional layer of context to the already ambitious Microsoft strategy of transforming itself into a “devices and services” company by declaring that “Going forward, it’s a mobile-first, cloud-first world”.

I believe that it is safe to assume that in the near future, the roadmap of every Microsoft product will be updated to reflect an alignment with this strategy. I am particularly interested in how this strategy will affect the Microsoft BI stack. Jen Underwood wrote a great post about “cloud first, cloud only” almost six months ago surfacing some of benefits as well as some issues arising from this strategy. I am still struggling a little bit with figuring out what the overall MS BI landscape will look like as it relates to the self-service use case vs. the enterprise deployment; therefore, I decided to put some thoughts on paper in an attempt to frame up a way of tying it together.

Here is a simple (but by no means complete) table to compare several key features between the cloud and on premise offerings (please be advised, that technically, the Power BI is still not GA, therefore, the information below may be out of date by the time you are reading it)

Feature Cloud (Self Service) On Premise (Self Services) On Premise (Enterprise)
In memory Yes Yes Yes
Data size 250MB Configurable up to SharePoint limit (2gb) Unlimited (or limited by RAM)
Data Refresh Yes, using Gateway Yes, Power Pivot Gallery Yes
Direct access to on premise data No Yes Yes
Refresh from Power Query Yes No No
Render Power Map in browser No No No
HTML5 support Yes (* not all there yet) No No
Render Power View Excel Sheets Yes Yes (SP 2013), No (SP 2010) Yes (SP 2013), No (SP 2010)
Create Power View reports No Yes Yes
Support for Power View in Power Point No Yes Yes
Natural Language query support Yes No No
Synonyms Yes No No
Windows Authentication (corporate domain) Yes (DirSync) Yes Yes

The biggest issue for me right now in terms of parity between the cloud and on premise offerings is the lack of ability to drive Power View dashboards through Power Point from the workbooks posted in the cloud. Cloud first strategy should (at least in theory) allow Microsoft to innovate at a much higher rate, however, the apparent disparity between the two flavors of the stack confuses the customer by making him/her think that now there are two forked versions of the stack that will be maintained independently from one another which may not look in Microsoft’s favor, particularly in light of the apparent “simplification” strategy manifested by some of the Microsoft’s competitors.

Running Windows 8.1 on MacBook Pro 15 with Retina display

No, there is no mistakes here, my new official working machine is now a MacBook Pro 15 with Retina display running Microsoft Windows 8.1. I am very surprised myself with myself, but at this point, I totally understand why Microsoft decided to do what it did with its own line of hardware devices because offerings from companies like Dell, Lenovo, HP with respect to a high performance /demo machine are, quite simply, pathetic right now. The closest laptop from a standard Windows ecosystem perspective was a Dell XPS 15 Touch; however, I could not afford to wait five weeks for it to arrive (supply chain issues perhaps?), so, instead, I stopped by an Apple store and, after five minutes there, walked out with a significantly more powerful alternative from Apple albeit at an almost $1k premium.

Coming from one of the biggest Apple haters out there, this is saying something.

The first thing I did after I unpacked this admittedly very impressive piece of hardware was to attempt to install Windows 8.1 on it.  I have to admit that prior to this purchase I have NEVER ever used Mac before. I had no clue how to do anything. However, I had done about a day worth of research about installing Windows on Mac before making the purchase and I knew that a lot of people ran into many issues trying to install Windows 8.1 on it; regardless, I decided that my better than average technical acumen will help me overcome whatever Apple contrived adversity I may encounter during this noble undertaking.

Generically speaking I was right, although I think that dumb luck had a lot more with the successful outcome of the endeavor than any supposed technical prowess that I might claim to have. Having tried and repeatedly failed at installing Windows 8.1 using Bootcamp utility that comes bundled in with every Mac following the official Bootcamp guide, I was lucky to find a kind of a cryptic post on the Apple care site that eventually shun enough light for me to get the install working.

This is a high level description of the problem and also a description of how to solve it.

  1. For whatever reason (definitely a bug on Apple side with Bootcamp utility), when Bootcamp creates a new partition for Windows install, that partition is created incorrectly and when one tries to boot into the windows install one gets an error message informing him/her that there is an issue with the partition and that installation cannot continue.
  2. The way to solve this problem is the following – right after the Bootcamp wizard is done creating the partition it will attempt to restart the laptop and continue with windows installation (as described in Step 1 above). This process will fail because the partition is not created right, it is essentially corrupted. Therefore, one should hold the “OPTION” key while the laptop is rebooting which will instead of automatically booting into the Windows set-up, will provide a user with a choice of boot options.
  3. At this point, one should boot back in Mac OS, go to Utilities (I believe it’s GO->Utilities from the main menu bar, again I have no clue about anything when it comes to Macs) and then go to the Disk Utility instead of Bootcamp. The Disk Utility will allow you to see the newly created Windows partition that will probably be named BOOTCAMP. Since this partition is corrupted, it’s not enough to Erase it. You have to click on the disk, not individual portion, and then Delete this BOOTCAMP partition which will leave some unallocated space on the disk. After that, go ahead and create a new partition, name it whatever you want it to be named and then make sure to format it as ExFAT. After that, restart the laptop again, hold the OPTION key and make sure that you boot into the USB Windows option. Bootcamp wizard will have created this USB option in the very first steps before requiring the initial restart of the computer.
  4. Once the Windows install gets to the partition screen, select the newly created partition and click on Format button which will format it for NTFS, after that’s done, the installation should proceed without hiccups.

Several things to note for those who are considering this Mac as their Windows machine:

  1. The display is gorgeous, but there are some scaling issues. Things like Internet Explorer work beautifully, but SQL Management Studio does not scale well for the native resolution unless you are going with 100% scale option which renders most text too small to read (the default font scale is 200% and as I said before it mostly works… with exception of the cases when it doesn’t)… some Silverlight modules look funky.
  2. Battery life is nowhere close to the advertised 8 hour limit. I think realistically, if you load your typical BI tools on it, you can expect around maybe 3.5 hours of battery life, provided the screen is dimmed significantly
  3. The keyboard is strange, I know, those who are regular Mac users are probably laughing right now, but a lot of keys that I am used to on my Windows machine are missing. Has not been a big issue for me yet (this entire post is typed on it) but nonetheless, it is definitely something to consider.
  4. The hard drive is crazy fast (mine has 1TB). Loading large CSV files into SQL Server or Power Pivot is now a breeze.
  5. The mouse pad is solid but does not support Windows gestures
  6. Again, I am in no way an expert in Mac OS, but it does totally feel dated compared to Windows 8.1; I genuinely enjoyed several features that I thought were neat, I appreciated the fact that I was able to navigate the OS well enough with no prior experience in it at all, but at the same time, the OS does not feel as crisp as Windows 8.1 at all. I will be first to admit that this is a very subjective personal opinion, but I will take Windows 8.1 over Mac OS any day of the week.

Getting back in the game…

I have not been posting much on the site in the last few months. This lack of activity was attributed to predominantly two things: A) My summer got really busy with my primary revenue generating activities and B) There was really not whole lot going on in the MS BI space that I felt I would have much to add to.

Well, as busy as I still am, the amount of new developments in MS BI is about to kick up a few notches in the next couple of months so I figured I had better make time and get back in the game of updating this site on a regular basis.

It seems to me that the biggest vacuum of news today is around the Power BI. The sign up page has not seen many changes in a couple of months now and I think everyone is getting a little anxious to finally get to play with the new features and see if our prayers and questions are being answered.

What are some of the questions one might ask?

Well, here is as good a list as any:

  1. What exactly is Power BI – a product? A SKU? A brand? A licensing model? A new software distribution model?
  2. Is Power BI a cloud only offering and what would an on premise solution look like? And When?
  3. Seems like there are new visualization elements that are becoming available. Does it mean that we’ll see things like:
    1. Drillthroughs
    2. Waterfall charts, butterfly charts, etc –we need more options!
    3. Formatting options for color and style
  4. Will the Power View experience be more touch friendly? (drilling into by double clicking and drilling up can be a challenge for somebody with fat fingers)
  5. And ultimately the questions that has caused me the most grief from my customers and prospects thus far is when will it run on iPad?

I am looking forward to having these questions answered in the next several weeks.

Stay tuned…..

A quick rant on Power BI, #confused….

There are now scores of blogs and web articles available that do a great job of introducing Power BI (a good example can be found here) so there is little value in re-hashing it in this post. I do, however, want to reflect and pontificate a bit on this announcement.

  • The most important piece of news for me is Mobile BI. Mobile BI has been a sore topic in the Microsoft BI world for a while now and I think this is the first time we have heard anything semi-official from Microsoft confirming that PowerView will be delivered using HTML5. I am not sure how I feel about the “Cloud First and Cloud Only” strategy yet. Hopefully, Microsoft will release a public roadmap for Mobile BI functionality soon. If nothing else, we now have some evidence that this functionality is coming (albeit in Office365 flavor first) which, if nothing else, gives me a little more strength to wait a little longer
  • The Power Query and Power Map are great additions to the MS BI features, with Power Query product being dramatically more significant one of the two. Both of these products have now been around for a while, although under different names. Given the fact that both are delivered as Excel add-ons (which means they run on a desktop), I am a little confused with the fact that they are being marketed with the Power BI which is a cloud Office365 offering
  • Natural Language Query Language – this will be a great Demo feature, however, I am a little skeptical with how practical it will be
  • Data Stewardship – sounds like a neat feature but I think we need some clarity with respect to where this fits in on the MDS roadmap for SQL Server
  • I am also encouraged by the news that the PowerPivot workbooks deployed in Power BI environment will be able to refresh from the on premise data sources. I can only then assume that Azure IaaS connectivity will also be supported.

There is something in this announcement that I find a little bit confusing. I would normally expect new functionality to be added to the enterprise feature set first and to the Self Service use case later. I am afraid that a lot of IT shops will find it a little discouraging that BI features they provide via the enterprise toolset are inferior to what the end users will get for a self-service use case in the cloud.

PowerView can now connect to multidimensional models

The cumulative update 4 for the SQL Server 2012 Service Pack 1 has now been released. This update enables PowerView to connect to Multidimensional models and also to have global filters across multiple views (pages) in a PowerView report.

Please follow this link for more details

http://blogs.msdn.com/b/sqlrsteamblog/archive/2013/05/31/power-view-for-multidimensional-models-released.aspx

Modeling financial scenarios tutorial available from Microsoft

Kasper de Jonge has recently posted a PowerPivot model, Access database with some financial data and a white paper that provides what I think is the absolutely best way to jump start a BI initiative focused on implementing financial metrics and KPIs. Please find the overview and the download link here.

This is an absolute must read for those who are trying to understand how to implement budget and prior year variance analysis, last known value aggregation scenarios (used for headcount analysis) and many other extremely useful calculations. The dataset is also included with the typical dimensionality for a finance application – Account, Time and Profit Center.

Kerberos Configuration Manager for SQL Server

Anybody who has had to worry about implementing security in a SharePoint environment has at some point run into challenges with impersonating end users and passing their credentials all the way to the analysis services cube for authorization. The dreaded two hop challenges necessitated having to implement Kerberos authentication which has caused a lot of grey hair to many a SharePoint administrators. Although the latest release of SharePoint brings multiple enhancements that alleviate much of the pain, sometimes, Kerberos will still be necessary and now Microsoft is making it easier to have it configured in SQL Server environments. The Kerberos Configuration Manager for SQL Server is now available for download here. According to the website, KerberosConfigMgr can perform the following functions:

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.

Please follow this link for additional information about Kerberos and SharePoint 2013.

Unpivot data using Microsoft Data Explorer

I have been really impressed with Microsoft Data Explorer the minute I had a chance to play with it as I was able to turn formatted Excel reports into legitimate data sources available for analysis. Fairly recently, an update to the tool delivered an uber critical feature that makes a huge impact on the world of self-service BI. I am talking about the Unpivot.

Let’s look at this table for example (shamelessly borrowed from this Wikipedia page)

Here we see historical and predicted population for several regions of the world. This table serves great purpose at conveying the information, however, if I wanted to do any sort of analysis across all of the time periods, I would have a problem doing it as the data is stored across eight columns instead of one, like this

region year population
Africa

1750

106

Africa

1800

107

Etc…

The new Unpivot feature of the Microsoft Data Explorer allows us to do just that, collapse multiple columns of data into one. (Another common example would be a financial statement where you have financial metrics going down in rows and the amounts going across the columns, one number for each month)

Let’s go through a few steps to get a feel for how it works.

First, let’s fire up Data Explorer and click on the   button on the ribbon. That will bring up find page window where we can paste the URL of our table (http://schools-wikipedia.org/wp/w/World_population.htm)

Click OK and the new data explorer query window comes up that looks like this:

Click on the first table on the Navigator pane and you should get something that looks like this

As you can see, we have our regions in rows and our data in a column for each year analyzed.

Now, let’s unpivot this data, in order to do that, highlight all the year columns and right click on a header of any highlighted column and select Unpivot menu option

The result should like this:

Now you can right click the Attribute column and rename it to say Year and you will have yourself a perfectly consumable data set

You can click Done and then add the data to your PowerPivot model or a pivot table. I think that for anybody who gets data as a pivoted report, this feature will save countless hours trying to massage the data into something a little bit more consumable.

GeoFlow is going public

The public preview of GeoFlow is now available here

http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx

I would definitely grab the “Data Explorer” plug in while I were there…(also available here http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx)

There are plenty of reviews and good tutorials of both products available elsewhere, but in the coming few days I will try to write about where I think these technologies fit in with respect to Self Service BI