Thursday, June 8, 2017

I woke up in Software Hell

This morning, I woke up in software hell.  Condemned to burn for eternity with no way out!

Or was I?  I'm a Christian ... Lutheran to be specific.  So I believe that as long as we have faith there is always a way to heaven.  (Dear God, please stop me from writing blog posts when I am inebriated!  The failure to register my new domain was probably heaven sent!  Now, no one will ever see this post!)

I lead a Business Intelligence team of 10 - 7 full time, and 3 contractors. Well, OK, 1 of the 7 doesn't actually report to me but most of the time the architect listens to me respectfully.

I am putting myself out on a limb here sharing all this information.  I am being very vulnerable here, so please don't laugh!

Up until a year ago, I was a Business Intelligence Developer.  But my boss was overwhelmed by the number of direct reports she had.  And I was keen to try to make a difference for the team that I helped build back up from a count of 2 (post death march), so I made the leap into management.  Of course, I refused the title of "Manager" because who wants that on their title when it makes you seem non-technical, right?

What I quickly discovered is that for every problem that I tried to resolve, there were 10 more underlying problems.  And I love to dig into things, so of course pretty soon the total number of problems became "billions" (Austin Powers reference folks ... milllions just seemed like too dated .. I can still see Scotty's disapproving face).  I used to think my former bosses at this company were ineffectual.  But of course, I now label myself as that too ... OMGoodness the stuff runs deep here.

So, do I throw in the towel?  Heck no!  I am a glutton for punishment.  I've been at this company for nearly FIVE years.  That is a total record folks.  My norm is 3 years before the itch hits ... or layoffs happen.  Shocked my grandparents so much they all are dead now (natural causes, but they were flabbergasted).

I can tell you that there are widespread problems in the way we do software development at my company.  We've got some awesome people.  Really I love them all.  But I am starting to believe that the reason so many Business Intelligence and Data Warehouse projects fail is because no one has figured out how to manage the projects.  I can tell you that the technologists on the team are so overwhelmed with keeping up on all the technological advancements that software process is the last on their list.  Process sucks, right?  And I can tell you that the pool of candidates for what I do is so small that you aren't that picky about the software dev processes they've used because you could never hire anyone and the work is unending!

So tonight, after stewing for a few days on the latest call to the carpet my boss gave me, I stopped thinking about the technologies and I started researching the methodologies.

Some background: I came to my company with a litany of varying SDLC methods.

  • 1st company - 1999 B2B dotcom doing VB6/Classic ASP with 20 yo boss who lived 800 miles away.  Methodology?  We didn't have anyone but developers on our team.  Write code, test it yourself, see if boss likes it.
  • 2nd company - 2001 VB6/COM Watherfall RUP.  Company learning RUP as we went trying to get 6 Sigma certified.  Turns out that was just to make us look good to sell.  Wasted a lot of time building interaction diagrams, but I retained ERDs and I still find Use Case diagram principles helpful.
  • 3rd company - 2002 VB6/Crystal Reports/C# One report/app at a time, custom dev for customers.  Just do it.  Honestly, I think I was best at this.
  • 4th company - 2004 ... well, I went back to training, then tech support where we started building our own ticketing system and I was doing XP on my own, and just as I went back to dev who were doing Scrum ans where I learned TDD.  Bam sold.  Fast forward...
  • 5th company - 2008. tsql / SSIS / Ant / Cold Fusion.  We met every two weeks to share.  Architect was also business stakeholder and called the shots.  People just got stuff done. If one of us saw something, we had the freedom to call it out.  No walls.  I automated everything I could get my hands on!
  • 6th company - 2012. Sql BI stack. "Agile" paceline for 2 years.  Dramatic shift to Kanban/Lean where BI team was accidentally excluded from training.  Welcome to software hell.
So, yeah.  I had no idea what was correct.  I just cared about getting stuff done.  But as I look back, I realize that the less process we had, the better off we were. The less rules we had, the more empowered I was.  The smaller the teams and the less ego on the team, the better we worked together!

I was feeling frustrated tonight, so I broke down and googled "Agile" and went to good old Wikipedia.  Guess what I learned?  While EIS at my company professes to follow Agile ... OMGoodness we are falling sooooo short.  I scored us at 5 of 42 on this test: How Agile Are You? (42 point test).  Some folks are going to argue we are Kanban not Scrum.  But I will argue that we should be.  But find me a Kanban test and I'll take it! Maybe this one How Agile Are You: Let's Measure It.  59 of 300 .. slightly better .. from 12% to 20%

The good news is that taking these assessments is valuable.  It was obvious to me what we are doing wrong.  Next step - let the team figure it out by giving them these assessment tools.

Maybe, just maybe I'll wake up tomorrow and the level of hell will be less deep! 

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

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!

Friday, August 23, 2013

Providing new user functionality directly in cubes

What if someday the SVP of Sales wants to set up sales quotas and be able to change them?  Do we institute that functionality directly into CRM?  Currently the BI sample dashboards for Sales have hardcoded values for determining what the percentage improvement over the previous year should be for all Account Directors.  If they want to change it, they can inside of PerformancePoint, but again, it applies to all ADs.  If they want the ability to set it per Region or per AD, we’d have to provide that ability.  And as BI developers, we may not have access to changing things in CRM… so what do we do?

In comes WriteBack option in SSAS to the rescue.  A few quick tweaks to the SSAS cube and voila … the SVP can set the quotas in Excel with a few clicks!  Check out this video to see how it works:  Excel 2010 Writeback to Analysis Services

Thursday, July 18, 2013

Released: Kerberos Configuration Manager for SQL Server

Haven’t tried this yet, but hey… what an idea!

I believe that the “EffectiveUserName” option in SharePoint mentioned in this blog is only available in SharePoint 2013+

Definitely not in PerformancePoint on SharePoint 2010 UI

Just hit this presentation which may be more helpful than my notes (pretty pictures):