Better image sizes

October 29, 2009

I apologize for the poor size of the images in my posts thus far.  I’m still figuring my way around this blogging thing.

Changed the theme so that images could be larger inline, and figured out how to upload larger images so that when you click to get a bigger image, it truly is bigger.

Football: loading the data

October 29, 2009

No more messing around, let’s dive in. 

Rather than boiling the ocean up front, let’s set a modest goal for getting started:  I want to end up with a report that shows passing yards by player.  (PowerPivot lends itself quite well to this approach – no need to build an all-encompassing model up-front like with traditional BI tools.  This is more like…  Excel.)

First, I launch Excel 2010 (with the PowerPivot addin installed), and using the PowerPivot ribbon tab, I open the PowerPivot window:

01 PowerPivot Blank Wkbk

(I’m not yet set up to do videos, but will be soon.  For now, let’s make do with pics).

Then, since my football tables live in a SQL db, I use the “From Database” button, type in my server and db name, and end up here:

02 PowerPivot Select Tables

As I showed yesterday, there are 40+ tables in there.  Some of which I probably won’t ever need.  And I certainly don’t need them all in order to deliver my first report.

But it’s just so much easier to grab all of the tables now.  That way I can browse them all, look at the data, and decide which tables I need for the report in a hands-on manner.

So I just click the little “select all” checkbox at the top-left.  Great feature…  that I argued against for awhile.  I was wrong.  I use it all the time.  Amir and Olivier, allow me to apologize publicly :)

Hit Finish, and I wind up with this:

03 PowerPivot Import Done

A few things to note:

  1. I’ve maximized the PowerPivot window.  Don’t worry, I can switch back and forth to Excel as much as I want, without closing this window.
  2. There is one “sheet” per tab per table from the database.  Gives Excel users a comfortable way to browse the data.
  3. I am actually seeing the data, just like in Excel.  No abstract schema browsers here.  We get real data, all of it, to work with.
  4. I have expanded the sheet navigation “overflow” popup.  Given that PowerPivot will be working with a lot more “sheets,” on average, than Excel, it needed a revamped navigation concept.  (We stole this from OneNote – thanks folks!)

All good.  But there’s a problem here, too, that you may have noticed.

I have dirty players!

No, I’m not talking about Hines Ward.  (Leave the guy alone, ok, he just “plays hard.”)  I mean that my players table contains some bad data – players for which Stats Inc. did not record player names, and instead recorded numbers.

What do I do about this?  The PowerPivot window itself is read-only – I can add formulas, which I’ll show later, but I cannot edit or delete rows. 

Why Read-Only?

The reason for this is the remarkable compression employed by PowerPivot – some db’s shrink to 5% original size.  And that same compression is used, in-memory, to deliver faster query results at report and analysis time. 

Jamming individual edits into that compressed structure, while maintaining lightning-fast query speed – well, let’s not go there.  Outstanding compression and query perf.  It’s a good tradeoff, especially given the other methods available to me.

OK, so how *can* I clean the Players table?

I have three options, which I will cover in the next post.

Next Football Post

PowerPivot’s impact on BI pros?

October 29, 2009

A few of us are having a really interesting discussion on Twitter right now that I thought could use a different forum.

“PowerPivot – bad for BI developers and consultants because it removes some need for their services, or good because it opens more doors?”

Here are some of the posts, I mean, tweets:

  • Vidasmatelis_normal VidasM: Preparing key points to talk at #sqlpass FoB lunch.Topic #PowerPivot impact on DW projects.More/less work for us?I welcome any suggestions.
  • Rd_headshot_cropped2_normal andrewbrust: @VidasM I think #PowerPivot will give rise to projects that would otherwise never have happened.
  • Rd_headshot_cropped2_normal andrewbrust: @VidasM And if a published #PowerPivot solution becomes popular, then it’s a candiadte for a more conventional implementation
  •  powerpivotpro: @VidasM I think more work for SSAS dev. In my exp, iterating on reqs =90% of project.Now u’ll do many short "upsize" conversions from PPwkbk
  •  powerpivotpro: @andrewbrust Agree with all your pts. #powerpivot good 4 BI pro. If BI were less crucial, 1 could squeeze other. But crucial feeds crucial:)
  • 110483183_4_yu59_normal Kjonge: @VidasM my idea is analysts creating adhoc and reports for single users with #powerpivot but enterprise reports run on SSAS and SSRS
  • Vidasmatelis_normal VidasM: @powerpivotpro , @andrewbrust – But – I can count projects where #PowerPivot would have been "Good enough". So I can quantify my loss.
  • Vidasmatelis_normal VidasM: @powerpivotpro , @andrewbrust – But I just have to guess how much I’ll gain (maybe). That is why I worry. And again-I just love #PowerPivot
  • Vidasmatelis_normal VidasM: I also see risk – companies building "Data dumps" instead of proper data warehouse, as it is faster and #PowerPivot can be used for reports
  • 110483183_4_yu59_normal Kjonge: @VidasM i still see some #powerpivot issues like security that will prevent it from being used as real enterprise wide reporting
  • Would love this conversation to continue, both the four of us above, as well as others.  Please post your thoughts as comments!

    SharePoint Conf 2009: Observations #2 and #3

    October 29, 2009

    Continuing my series of 5 Things the ‘Pro Wants You to Know (About the Show)

    #2: If it isn’t SharePoint, it isn’t Microsoft BI

    Another one of those things that’s been creeping up on us with no formal announcement (until the conference, and even then only in a few places).  Every MS team that builds BI tools has, over the past couple of years, decided to embrace SharePoint.  Excel Services was first, then Reporting Services offered it as a mode, PerformancePoint jumped in, then PowerPivot… 

    I might have some of the timeline mixed up, but the point is that this was a series of organic, independent decisions.  There wasn’t some edict coming down from on high.  One by one, each team decided it made sense to embrace SharePoint.

    And because of this bottom-up nature, there was no one central team/person/marketer to trumpet the shift.  We’re starting to say it, but in my opinion, we need to be a bit louder about it.

    So, let me say it loud and clear:  SharePoint is now the Microsoft BI platform.  I seriously doubt you will see much, if anything, come out of Redmond that’s BI-focused and not integrated with SharePoint.

    If you’re a BI pro, start learning about SharePoint.  If you’re a SharePoint pro, start learning about BI.

    #3:  A lot of SharePoint admins already “get” BI

    Donald Farmer had a conflict, so I offered to take his Tuesday morning 9-10:30 shift at the PowerPivot booth.  10:30 rolled around, and I was having so much fun that I stayed the rest of the day.  At one point I had to ask someone to bring me lunch, which I ate standing up.

    Knowing that this was a SharePoint conference and not a SQL/BI conference, when people first showed up at the booth, I made a point of asking about their background, their exposure to BI, etc.

    And while I only met a couple of real BI pros during the day, I was very pleasantly surprised at the overall level of BI awareness.  A few high-level impressions:

    1. Reporting – everyone knows about the need for published, interactive data, and is already doing at least one project on SharePoint.
    2. The notion of “central” BI vs. “business unit” BI – I’d say about half the folks dropping by already were at least peripherally aware of this breakdown and tension, and the other half were quick to understand it.
    3. Cubes – again, about half were familiar with what cubes are, and the purpose they serve, and the other half picked it up awfully fast.
    4. General BI Awareness – near-100% awareness of what BI means and what the products offerings are.  A little confusion about “when do I use this vs. that” but it wouldn’t be a Microsoft conference if I didn’t field that question multiple times an hour :)

    It’s a recurring theme:  customers are ahead of where I expected them to be.

    Football data source tables

    October 29, 2009

    OK, last post for the night.  Turns out there were over 40 tables of data that went into the MSN football cube.  These are the tables I will be importing into PowerPivot.

    It was pretty difficult finding a way to list them all out in one place, with their column names, so everyone can see.  I eventually settled on a db diagram from SQL Server Mgmt Studio.

    Filter/Grouping Tables

    First, here are the tables that contribute to all of those Filter/Group fields from my last post:

    powerpivot football dim tables

    Note how they all start with “Dim” in their names?  For the non-cube folks out there, that is because Filter/Grouping fields are often called “Dimensions” in cubespeak.  PowerPivot does not use that lingo, which is a good thing, but I’m just telling you these things in case you encounter them elsewhere.

    Normally, if you are presented with this number of tables and you have to do something with them in Excel, you block off your calendar for the rest of the week.  It will be much, much faster with PowerPivot, as we will see.

    Numerical/Play-by-Play Tables

    And here are the tables that actually contain the data of what happened on individual plays.  There will be more rows in these tables than in the tables above.

    powerpivot football fact tables

    Just as the tables above are prefixed with “Dim,” these are prefixed with “Fact,” because that’s what they are called when you are building a traditional cube.

    So, all I have to do is turn these tables into the fields from my previous post, and then into the reports from my first football post.  Simple!

    But it wouldn’t be as much fun if I had picked a simpler example.  Remember, I intentionally chose an extremely realistic and complex project, because we are really gonna kick the tires :)

    Next Football Post:  Loading the Data >>