Articles for the Month of July 2014

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
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

MCSA – Part Deux, Exam 70-462

If you haven’t seen Charlie Sheen’s cinematic triumph Hot Shot’s Part Deux, you’re missing out, which is why I made sure to include the link for it. If you have seen the movie you will wonder how it missed out on an Oscar nomination, or maybe better understand why it is on cable non-stop in reruns. It was also the first thing that came to mind when I thought about Part Deux. So having taken the first exam 70-461, without the book, I felt compelled to go out and get the Official Microsoft Press study book for 70-462 . This was the first book made available for any of the SQL Server MCSA Exams. Yea! That will make studying for the test so much easier, right? No. Wrong. Dead wrong.

So about the book.

If all you do is follow it, make sure you check out this web site as you will be doing a Part Deux yourself. This exam covers the administration part of the exam. This is the exam that DBAs should be more familiar with, although I doubt in your environment that they have set up every form of replication and high availability option in SQL Server 2012, so I bet you will have to study too. I set up a server and thumped around the exercises. It wasn’t enough. The book does offer a coupon in the back for a discount on the exam cost as well as practice exams. After I failed the practice exams, I thought hmm this isn’t working. I went back to what I did on the last exam, after all I passed that one. Here is everything you need to know to pass the 70-462 exam. What I did was made a word document out of all the material it said you needed to know by copying things off of MSDN and books online. I made two documents, after all this is part two, and it got sorta long and I didn’t want to blow up Word.

After making sure that I could really do the stuff listed that I didn’t have a chance to mess with when I played DBA, I drove my rolling purse over to the testing center and took the exam. I brought my running watch which has a handy stopwatch which is also fun to set when standing in TSA lines in the airport to see how much of your time is really being wasted. You should try that sometime as generally you will be surprised that it seems like you are spending a lot more time in line than you actually are. The test was like that, as it seemed like it was taking forever but I actually had more time than I needed. I had 30 minutes to spare so I could go get ice cream before I had to be at my next appointment.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur