Articles

What is Power BI? Microsoft just changed the answer to the question

In reading the Power BI blog, it appears Microsoft has just changed what Power BI is. Since Power BI’s introduction last year, Power BI was a series of add-ins to Excel, which I liked to call the four powers, Power Pivot, Power Query, Power View and Power Map, and a web component. For people who weren’t interested in the web component, the most of the features listed in Power BI are available to anyone who has Office 2013 or Office 365. That now appears to have changed. Microsoft now says that Power BI is a Cloud Based service, and doesn’t mention Excel at all.

New Power BI

As I documented here, Microsoft released Power BI Designer as a web application and a stand alone application. It has been freely available since December 18, 2014 to anyone living in the US. This was the first step away from Excel as you no longer needed Excel to create dashboards. In the January 27 announcement, Microsoft has completely broken away from Excel. Now Power BI is new, and the new Power BI is the Designer of December. The old restrictions still apply. Sorry, if you are not in the US, you can’t use Power BI Designer aka the new Power BI. You can get an iPad version of the app in the Apple store and the Surface version in the Microsoft store, but phone support is not yet available for either iPhone or Android, but they are slated for release later this year.

New Product, New Price

It appears the mall is not the only place having sales in January. Microsoft just announced a major price reduction in Power BI too. Here’s the previous pricing model, which I saved from Microsoft’s website, just in case the Power BI webpage changed, which it did.

PowerBILicensing

Here’s the new pricing model ,which doesn’t fit very well on my webpage.  To save you having to click on it, I will cut to the chase. Power BI is now $9.99.   Now that they product is targeted to the masses, the price isn’t an even number, just like everything Not Sold In Stores. It stands to reason that dropping the price will help in the  wider adoption of Power BI. The previous pricing made Power BI much more expensive than Office 365, which was probably a tough sell to many IT managers.

What’s Next with Power BI ?

To be honest, I have no idea what Microsoft is planning next. This announcement marks a big break with the past, which I guess we could call Power BI 1.0. The new direction to a standalone and web product makes Microsoft look more like its competitors, which I am sure was the idea. Personally I thought the break with Excel was quite surprising as I thought the plan was to leverage the knowledge of the current user base, so I didn’t expect it. I wonder if they are going to rebrand the four powers in Excel?  Based on today’s announcement I wouldn’t be surprised, and I will be watching Microsoft closely to see what happens next.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Inquiring Minds Want to Know about Improving Data Quality

Lost in the sauce of all things Power released as part of Excel 2013, Microsoft snuck in an app that is pretty obscure, so I thought I’d shine some light on it. What’s the app? Inquire. Ok, be honest have you heard of this yet? Microsoft bought Inquire which was previously known as SpreadsheetIQ before Microsoft bought Prodiance and their product on June 6, 2011. In order to see and use the Inquire, you will need to add it from the list of Com components shipped but not turned on in Excel. This is the same process that you need to follow to add PowerPivot. Check out my previous post blog and follow the same steps you needed to add PowerPivot to add Inquire, which will add yet another tab to Excel. Why would you want to add Inquire? Well it contains some neat tools to help you analyze what is going on in Excel worksheets.

How accurate is your data?

Since more and more data is being stored in spreadsheets people have been starting to study how accurate the data is. In a recent study at the University of Hawaii they have found that spreadsheets are wrong most of the time. It is not surprising that there are tools, like Inquire, being created to help accuracy improve. One of the ways Inquire helps improve data quality is to expose the internals of Excel so they are easier to review, which will make it easier to see what all is included within in Excel.  These tools make it easier to analyze the contents all in one place.

Workbook Analysis

When you have selected this feature, Inquire will go through your excel spreadsheet and review the contents. The summary contains a lot of useful information. In addition to including things like how many cells create formulas, as well as see how many cells contain errors. The summary also shows Very Hidden Sheets, which is something I didn’t even know you could create via VBA. Data Connections, Validation Criteria, Dependents, Duplicate formulas etc are some of the many items that Inquire includes in the summary. If you accidently put a minus sign in front of a cell and didn’t see notice, Inquire will list the item under Negative Formulas. All of the numeric items you have stored as text are listed too. If you run the detail report, you will see the workbook sheet name and every cell address and formula where these numeric text fields are located, which will be extremely handy when trying to correct this problem. All the information is stored in a report, which naturally is generated in an excel spreadsheet for later review.

Understanding the connections

Inquire’s Workbook Relationship report shows a picture of how workbooks are related together.

Relationship Diagram

The cell relationship does a similar diagram on the individual cell level. If you have two spreadsheets and you want to know what the difference is between the two spreadsheets on a field by field basis, Inquire will show the differences when you select the Compare files button. If you just want to do some cleanup, there is a button for that too. Removing excess formatting can help improve the performance of the spreadsheet by eliminating duplicate and extraneous formatting. Lastly, inquire contains the ability for you to manage your passwords contained throughout the spreadsheet in one place.

With more and more data being stored in excel, either to create PowerBI analysis, Tabular Prototyping or just because excel is your data store of necessity for now, its nice to see Microsoft adding tools to help ensure that you can beat the odds and produce an excel spreadsheet where you can have greater confidence in the data stored within it.

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