Articles

Using the xVelocity Engine in Excel Power BI

Recently I was at a client talking about Power BI in Excel. They had some questions about where to load data from their source system. After all you can just load data using the Data tab, and create a Pivot table based on that. Why use Power Pivot? The best reason that I could give is because if you load data up into Power Pivot, you are then going to use the XVelocity engine. The xVelocity engine loads data into memory and provides data compression which will increase the amount of data that you can store within Excel.

Demonstrating How the xVelocity Engine Works in Power Pivot

Since about 1890 Missouri has been known as the “Show Me State”. Apparently, no one really knows why, but I’ve heard the expression “I am from Missouri so show me”. I’m assuming that everyone reading this is from Missouri, so to speak, which
missourimeans I need to be able to show you how the xVelocity engine works. To do that, I am using a sample data set I got from UCI’s Machine Learning Archive, which is a great place for getting machine learning samples. The data set I selected for this test is the Online News Popularity set, which can be found here. If you don’t feel like downloading anything, do the same thing with any other large text file. In the Online News Popularity file there are 65 columns and

39,644 rows. The csv file size is 16,518 KB. If I open up that file and save it in the Excel file format and do nothing else, the file size is 18,484 KB. Saving the csv file in Excel adds about 2 MB to the file size. This isn’t surprising, as Excel adds to the text when it is saved, which can be seen if you ever open up an Excel file in Notepad.

The xVelocity File Size Test

So far we have not tested the xVelocity Engine yet. To test the xVelocity engine, open up Excel, click on the Power Pivot tab, select Manage, which will open up Power Pivot. Within Power Pivot, click on the From Other Sources icon, and select Text File, then click on the Next button. Browse to the Online News Popularity.csv location, wait until the data loads, then click on the Finish button. The data will then be loaded into Power Pivot. Save everything and exit Excel. The file size for the Excel file with the data loaded into Power Pivot is 11,386 KB, which is a 39% reduction in space. In playing around with various files, I have noticed that you get the biggest reduction if you have few columns and many rows. Now I know that the xVelocity Engine does more than just shrink files, but showing someone the decreased amount of memory usage just doesn’t have the same impact as file size. This is a simple experiment which shows the improvements made to Power Pivot for Excel, which I hope you find useful as well.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Getting started with PowerBI in Office 365

When speaking at SQL Saturday #292 – Detroit, I received a question which I thought more people might have. How do I get started using Power BI or PowerPivot? I have Office 365 and I can’t get to the PowerPivot Menu and I don’t see Power Query either. What’s wrong?
I am assuming in that case your version of Excel looks like this.

image 1

 

Power BI was officially released on February 10th 2014. You might think that would mean that if you installed Office 365 after February 10th, which I did here, that Power BI would work out of the box. Sorry, that’s just not the case.

Turn on the Power

In order to get PowerPivot, Power Map, Power View or Power Query working you need to turn them on in Excel first. Click on the file tab in Excel and you’ll see this screen

image 2
 

 

 

 

 

 

 

 

 

 

 

Click on the Options menu item and you’ll see the following screen

image 3
Notice there are two sections to this window, Active Application Add-ins and Inactive Application Add-Ins. I’ve highlighted Power Pivot, which is listed under Inactive Application Add-Ins. This is why you don’t see it, it’s not active. Sounds reasonable, just click on the GO button and fix it right? No, wrong.

Type Tricky

The trick to adding add-ins is looking at the type. PowerPivot is type COM Add-in. If you click on the Go button you will see all of the Excel Add-ins, won’t turn on PowerPivot. When is an Excel Add-in not an Excel Add-in? When the Type says so. If you click on Go, this is what you will see

image 4

 

 

 

 

 

 

 

 

 

 

 

PowerPivot is a COM Add-in, so it isn’t on this menu. Cancel out of here and go back to the Add-In screen and change the drop down box to COM Add-in before clicking on the Go Button. This menu is what you need for getting 3 out of the 4 powers.
image 5

 

 

 

 

 

 

 

Go ahead and check the boxes next to PowerPivot, PowerMap and Power View like you see here. Click ok and then you’ll see the PowerPivot tab in Excel.

image 6

You still won’t see the Power Query Tab, because that has to be downloaded separately, which you can do for free here
https://www.microsoft.com/en-us/download/details.aspx?id=39379
Pick the one version you need, either the 64 or 32 bit version and follow the installation instructions, which means getting out of excel first as it will only install when it is not loaded.

image 7

 

 

 

 

 

 

 

 

 

 

 

After you finish the installation, go ahead and open Excel.

Power Signs

The Power Query tab is magically added, as the install takes care of adding the add-in for you.

image 8

 

When Excel looks like this, it’s time to get started with PowerBI!

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Power Query and Power Pivot – two ways to load up Excel with data

If you’ve have messed with pivot tables in the past, you know that adding data to an excel spreadsheet is nothing new.  You now can add data three ways, because you can now add data with Power Pivot and Power Query.  The question is which tool should you use when?  Well if you have Office 2013 and didn’t sign up for the Power BI preview, you are limited to using Pivot Tables.  Since I signed up for the Power BI preview I was left with another question.  Which tool is the right one to use for loading data?  Well it’s the same answer you always get when working with computers.  It depends. 

Power Query was designed to readily provide access to data, any data.  Microsoft on their site promotes the fact that you can include, or as they like to say “Mash” data from Wikipedia or other sources together with your business data.  I can see where this feature would be very useful.   I work with a concrete company who has a lot of KPIs that they have for daily production.  Well, when it rains they don’t meet the performance metrics because no one wants to pour concrete in the rain as it affects the way it cures and the concrete can end up bumpy instead of smooth.  If they correlated the weather together with the KPIs they could definitively determine the effect of rain on their business. Power Query also provides a way of merging or appending data together, which makes sense since you may be selecting data from a variety of sources

If you are pulling together internal data so that you can create a Power View report on it, Power Pivot would be the best way to proceed.  Yes, you can do this in Power Query, but Power Query doesn’t provide the same level of filtering that Power Pivot does.  Power Pivot also provides the dangerous feature of allowing users to update data in the database by modifying the data in Power BI a spreadsheets, a feature which most of the time I believe will be turned off. 

With Power Pivot, not only do you load up the data, but you also build the relationships between the data.  It works the same way if you user Power Query for half the tables in your database and Power Pivot for the other half.  I felt compelled to check that out when I was playing around to see if it would make a difference.  Of course Power Pivot can do a lot more than just relationship management of data.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Do I really have the Power?

Is just it me or does anyone else keep on hearing that stupid Power song by the one hit wonder Snap whenever they are hear about Power BI?  Now if you cannot recall this catchy tune this should send it rattling around into your skull http://www.youtube.com/watch?v=_BRv9wGf5pk.  It’s the new Rick Roll.

Is it possible that adding the word Power is supposed to be a subliminal message to people who’s lives are spinning out of control and have been sapped of their personal essence?  But then again I could be wrong.  You add data by Power Query, then manipulate it in Power Pivot, then make a Power View and add a Power Map.  And naturally this task should never be attempted at home and only by Professional Power Users.  I feel that by using it I have increased my power exponentially by a factor of 365 to complete this task.  On the other hand, I could be totally wrong.  Power BI is available as a preview edition, which means for now anyone can load it up as it has not been officially released.  Still though the more time I spend with it the more I generally like it. 

Although you could argue that it might have made more sense for Microsoft to build a reporting app than bloating up Excel, it’s perfectly understandable why they did it.  Based upon what I have seen, there is a real reticence on the part of the IT staff to install anything else on a computer.  Given the things end users wish to install on their computers, one could hardly blame the IT staff.  The idea of self-service BI is for business users to use it, and Excel has been the staple of ad-hoc data storage and analysis for decades.  Since Power BI is a series of task specific bolt-on accessories to the ubiquitous Microsoft spreadsheet, using it isn’t the most intuitive thing in the world either.  It is sort of awkward to click here and there to get the tool to work, but once you figure that out, in the end it turns out to be a cool tool.  It has changed quite a bit since it was first introduced.  Microsoft’s blog from July includes a walk through of Yelp data which will no longer works as written because the expand feature has been removed. 

Power BI really makes Microsoft competitive in an area where they had nothing but a gaping hole which their competitors, namely Tableau, have driven their truck through.  In less than two years they have come up with an application which provides self-service BI to business users, uses in-memory analytics, can be viewed securely on the web, and can be viewed on tablets and phones.  Since Microsoft is slowly but surely getting rid of Silverlight to follow everyone else to HTML5, you can view your Power BI reports on whatever phone and tablet you happen to own. Ok, the caveat is that the application that was created for the Microsoft phone is better, but you are not shut out if you have an iPhone or Samsung tablet either. 

From a marketing perspective, Microsoft has provided perfectly sensible reason that you might want to actually get Office365.  Personally Power BI was the reason that I decided to move to Office 365.  Let’s face it, for most people Office 365 was just another way to load up office.  The whole cloud thing was not really much of a selling feature for end users who didn’t want to use a skydrive when they could easily use a hard drive ensuring your data isn’t spread all over the known universe.  Hosting SharePoint in the cloud provides a feature people might actually use.  There is an argument to be made as to whether or not it is any less annoying to administer SharePoint via a cloud rather than locally, but it is one less server IT needs to monitor.  The price isn’t terrible.  Here’s the info which Microsoft has released on pricing http://www.microsoft.com/en-us/powerBI/pricing.aspx#fbid=5EP_a34ZHLQ

I am giving a talk at SQL Saturday in Albuquerque on Power BI where I will delve the technical features of Power BI in greater detail.

Yours Always,

Ginger Grant

Data aficionado et SQL Raconteur