Sunday, September 27, 2015

Introduction to Power BI

Last Saturday, at SQL Saturday #441 in Denver, I delivered my second presentation on Power BI.  My first was as a guest speaker for Pragmatic Works Free Training on the Ts in March as part of the Women In Technology month.  In March, I covered Power Pivot Add-In for Excel, but this Saturday, I delved into the new Power BI Desktop application that was released for general consumption on July 24, 2015.

My presentation slides can be found here. Please note that 85% of my presentation was a demo, which is not visible in the slides.  I may try to repeat it and record the demo so that others can see it later.

Unfortunately, I didn’t have a pen handy, and I had passed my cell phone with EverNote around the room so folks could look at Power BI Mobile, I didn’t get all the questions written down, but let me try to recap the FAQs:

  1. Can you extend the functionality of Power BI? 
    Yes, there is an API so you can program against it and there is currently a contest running for the next best visual.
  2. Can you export from Power BI to Excel?
    While it is easy to import from Excel, exporting out is not currently that easy out of the box.  Someone suggested trying to copy/paste from the UI, but when I tried this, it wasn’t available. HOWEVER, a more recent release is supposed to include this change.
  3. What is the advantage of manipulating the data in Power BI instead of Excel?
    The main advantage is the ability to refresh and have the ETL steps reapplied, whereas Excel would require you to master Excel macros/VBA.
  4. Can you connect Excel and SSAS models?
    Yes.  You can mash them up easily using Power BI Desktop.  I have not personally tried it yet.
  5. How does it compare to the dashboarding tools in Visual Studio?
    I don’t know the answer to this one.  I haven’t seen dashboarding tools in VS.  So, I am hoping that someone who does would give a presentation at the Denver PASS chapter so I can see!
  6. How does PowerBI compare to Tableau?  Since I’ve never used Tableau, only seen a demo, I can’t answer this, but as one of my attendees pointed out, the price is the big difference.  PowerBI.com is FREE or $10/mth/user for the Pro license. 
  7. Can you use the Power Pro license features FREE with your MSDN license?  If you have either the Enterprise or Platforms license (don’t ask me more as I’m not really an MDSN license expert). I checked back through the emails I have from MDSN and lo, and behold, on Aug 12,2015, they sent me a link to activate my Power BI Pro.  So yes!  I had some issues getting it to work personally.  Kept getting errors.  Finally created a new account thru onmicrosoft.com and it created an Office 365 account for “my company” but then I had to login using my email address at my employer’s and it started “setting up” power bi with “less than one minute left” … not sure if it will ever finish.  Hmmmn… so appears this has not been well tested.

If I have forgotten a question or haven’t answered one fully, please let me know!

As an update, my company is getting closer to adopting Power BI.  This week our operations group received a sales call from SAS who wanted to sell them a $9K deal.  Our operations group runs on spreadsheets.  Their BI needs are still immature and they don’t have enough volume or statistical needs in my opinion to require SAS, plus SAS is not nearly as easy to learn as Power BI.  I thought it would be another year before anyone gave Power BI a notice because our entire company’s development group is immersed in improving our sales software, but the operations group immediately started the ball rolling on PowerBI as soon as I sent them an email highlighting the benefits of PowerBI.  Yay!

Saturday, March 21, 2015

Using Power BI to Remove Business Process Inefficiencies

A huge thank you to PragmaticWorks for featuring Women In Technology during the month of March 2015 for their Free Training on the Ts.  It’s been several years since I spoke in public – I gave it up within a year of having kids since traveling became too hard emotionally.  My kiddos are really adorable.  It’s very hard to stay away from them – I miss their cuddles and their kisses and their happiness!

Thank you to those who attended and the great questions.  This was a 100 level talk and covered some basics of Power Pivot and Power View.

My slide deck for the presentation

The full 1 hour presentation

Questions that I remember:

  1. How do I ignore the filter in DAX ?
    A: Use All  - see Marco Russo’s post on “All, AllExcept, and Values in Dax” here.  If you want to learn DAX, I highly recommend “DAX Patterns” by Alberto Ferrari and Marco Russo.  You can find a list of their books here.  Or Rob Collies PowerPivotPro.com
  2. What about Workbook Mode?
    A: I haven’t used Power Query much.  It’s next on my list! Consultants and Trainers tend to keep up with the latest.  I’ve seen it, but in my current position the company needs a lot of traditional BI work done so I haven’t had as much time to play with PowerBI stack as I’d like. And wasn’t part of my talk… but of course, now I want to know what it is!  I googled it and can’t find anything, so I’ll have to dig under the covers a bit more and cover it in a post.  If this was your question, let me know who you were and I’ll try to get back to you.

Wednesday, January 7, 2015

Geospatial Data in SQL – Lat Lon or Lon Lat? That is the question!

Just wanted to share some fun using Geographic data in SQL. It’s been many years since I’ve touched Geographic data (and that was in VB6 not SQL), but it’s pretty cool really.

I have a lat lon coming in from Shazam. 37.454336 -122.184769

I took those coordinates and pushed them into maps.google.com and it shows Palo Alto, CA.

I then compared it to the address of the TheatreSysID that comes from Shazam and it is also in Palo Alto, CA.

Good so far. At least I know where the point is supposed to be!

But when I run this SQL: SELECT geography::STPointFromText('Point (37.454336 -122.184769)', 4326)

I get:

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":  System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException: at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

We say Lat/Lon when we talk about spatial data and that means Latitude is the first number, right?

And it sure looks like it is between -90 and 90, doesn’t it? Well, maybe not …

If I flip the values and run: SELECT geography::STPointFromText('Point (-122.184769 37.454336)', 4326)

It does not error out and gives me a value… but is it the right one? And why a different format than Google… I mean it rules the world right? We can’t function without it!

So, I googled a bit and found this: http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx

Yeah.. Microsoft has to be backwards. They do it Lon Lat. OK. OK. They aren’t backwards. They are following the Open Geospatial Consortium standard which does X Y … where X is the horizontal axis (longitude) and Y is the vertical axis (latitude).

Anyway… fun fact for the day.

Oh, and there are some good looking blogs by Dave Does Data on MSDN .. might be outdated and a bit oversimplified, but worth a read.

I keep meaning to watch one of Jason Horner’s presentations on Geo data … guess I should get on it!