A few tips on maintainability

  
Eat This Not That, PowerPivot Edition!

Just like normal Excel, PowerPivot requires little or no premeditation – you can just jump right in and build something quite impressive.

But if you want to be a true PowerPivot Pro, and reap the full long-term manageability and robustness benefits, applying a little bit of discipline to your workbook development can go a long way.  

The list of best practices we’ve been developing at Pivotstream is a little longer than I have time to pull together right now, but I thought I’d share a few quick tips.

#1:  In Measure Formulas, ALWAYS Include Table Names in Col Refs

You may have noticed that PowerPivot sometimes lets you omit table names in your measure formulas.  Resist this temptation.  Always include the table name.

Use THIS:

   =SUM(Table1[MyColumn])

NOT that:

   =SUM([MyColumn])

Yes, I know they both work, and both return the same result.  But some places REQUIRE the table name, like arguments to the CALCULATE function.  So you might as well get into the habit.  Plus, when combined with the next tip, you get another benefit too.

#2:  NEVER Include Table Names in Measure Refs

In another odd bit of flexibility, PowerPivot lets you include the table name in references to other measure names.  My advice here is exactly the opposite of the above.  NEVER include the table names when referencing a measure.

Use THIS:

   =[Measure1] – [Measure2]

NOT that:

   =Table1[Measure1] – Table1[Measure2]

Measure names must be unique across the entire PowerPivot workbook, so the table name is not needed.

More importantly, if you follow tips 1 and 2 religiously, you will always be able to tell the difference between columns and measures in your measure formulas, and trust me, that is a big advantage.

#3:  Don’t cut corners on prototype data sources

Here’s the quick version:

Use THIS:

1. SQL Server
2. Another “real” database like Oracle, DB2, etc.
3. SharePoint Lists, Access Services, etc.
4. Reporting Services reports
5. Other OData Data Feeds

NOT that:

1. Excel Linked Tables
2. Copy/Pasted Sources

These are MAYBE’s:

1. Access MDB’s
2. Text Files

Long version:  Data Sources are like Marriages (High Switching Cost)

Why do I have these biases?  Primarily because changing data sources later is exceedingly painful.

You CANNOT take a PowerPivot table that is sourced from Copy/Paste, for instance, and switch it to use another data source type.  In fact, you can’t change any source type to any other.

So, if you prototype against a text file and later want to point that table to SQL Server…  you must delete the table (which deletes ALL of that table’s measures, calc columns, and relationships), and start over.

If you think that isn’t a big deal, you haven’t lived through it yet 🙂  Nothing is more frustrating than re-doing work you have already done.

So…  if you think there’s a decent chance that long-term, your data will come from a real DB, go ahead and take the time to start that way.  It will pay off over and over.

Note that you CAN point a PowerPivot table from one db server to another, or point it at another db table, or another db view, etc. – and this DOES NOT require you to delete your table.  There is a universe of flexibility once your data is sourced from a db.

Oh, and if you think you’ve got a table that you’ll never need to update, and that you will never want to move to a real db, well, from experience I can tell you that you might be mistaken.  I’ve made that mistake already, many times, and paid for it later.

There are some other surprising benefits of having your data come from a robust refreshable source, but I will cover that another time.