PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence

Data of Different Grains: A Followup

January 19, 2012

Sales Measure Returns the Same Value for Everything: We Solved the Calendar/Periods
Problem But Now We Have a Products/Category Problem

Quick Recap: We Created a Separate “Periods” Table

In last week’s post, I explained how PowerPivot can very easily help you solve the “Budget vs. Actuals” problem or any other problem where you have data sets of different granularities that you want to compare in a single report.

The crux of the problem in my example was that my Budget table only budgeted down to the Month level whereas my Sales table went down to the day level.

To solve that, I created a separate table that only contained months (no sales or budget data, just months), and assigned a “PeriodID” to each month. (I could have just as easily called it MonthId).

The Newly-Created Periods Table

I then linked that PeriodID column to the PeriodID column that already existed in my Budget table:

And then created a new PeriodID column in my Sales table using a formula:

Resulting in a three-table setup that looks like this:

Which then allows me to use fields from that Periods table on slicers (or on rows or columns) while using measures from both Sales and Budget in the same pivot:

And that, my friends, is a very useful thing indeed.

Could I have just added the PeriodID column to Sales and Stopped?

This is an important question for us to stop and consider. Once I had a PeriodID column in both Sales and Budget, could I have skipped the creation of the Periods table and just moved on?

No. I could not. And there are two reasons.

Reason #1: I can’t create a relationship between Sales and Budget.

Each PeriodID Appears More than Once in Each Table, So You Can’t Relate
Sales Directly to Budget or Vice Versa

That’s just the way relationships work. The matching column needs to be unique (no value appears more than once) in at least one of the two tables. No need to really worry about why, but if you think about how VLOOKUP works when you set the last argument to FALSE, it’s pretty similar.

Reason #2: Without a relationship, PeriodID from one table doesn’t work with measures from the other.

Look what happens to Budget measures if I use PeriodID from the Sales table:

PeriodID from Sales Table Results in Broken Budget Measures
(and a relationship warning)

Yep, all busted. And if I use PeriodID from the Budget table, I’ll get good Budget numbers but the Sales numbers will be hosed instead.

THAT is why the third, new, separate Periods table is required. When I use fields from THAT table on my pivot, I can use measures from both Sales and Budget at the same time and nothing is broken.

So we use the Periods table as a master filter of sorts, one that can drive filters down into both Sales and Budget.

“Master Filter” Table is a MUCH better description than “Bridge”

I debated using a “bridge” metaphor to describe the role of the Periods table but I think that’s misleading – a bridge helps you travel from point A to point B: A –> Bridge –> B. That would make you think that we’re “starting” on the Sales table and “traversing” the Periods table to get to the Budget table.

But that’s not how it works. Neither Sales nor Budget is “in charge” here – neither is the starting point. There are two separate paths here: Periods –> Sales, and Periods –> Budget. The diagram drives that home:

So you should think of the Periods table as being “in charge” here. Which makes sense, since you have to use it on the pivot, and can’t use PeriodID from either Sales or Budget.

Extending that Rule to Fields Other than PeriodID

This is the crux of what I left out of my previous post, and it’s an important rule to drive home:

When you have separate data tables that cannot be combined into a single table (as is the case with Sales and Budget), you CANNOT use row/column/slicer fields from one table in a pivot with measures from the other table. You MUST have a separate “master filter” table, and use fields from THAT table in your pivot.

So does that apply to fields other than PeriodID? You betcha. Guess what happens if you use a Product Subcategory or similar field from the Budget table in a pivot that contains a Sales measure (or vice versa)? Yeah, same problem as trying to use PeriodID from Sales as we did above:

What’s the solution? A separate master filter table for Products!

And that may be as simple as a single column:

Wash, Rinse, Repeat

Once you have measures from two different data tables, every row/column/slicer field you use requires a separate master filter table.

It may sound tedious but it’s actually quite quick. To easily create such a table you can even just use a pivot:

Put the ProductSubCat field from either Sales or Budget on rows.
Copy/paste that column of unique values into PowerPivot as a new table.
Create the relationships to Sales and Budget.
Use fields from the newly created master filter table in your pivot. Done.

(Better to get someone to add a new view or table to a database for you if that’s available, but copy/paste works well otherwise).

Also Useful for Single Table Situations!

One last point: even when you only have a single table of data, like Sales, it’s often still quite useful to create separate master filter tables.

Why is that?

Because when you create master filter tables, it gives you the opportunity to remove lots of columns from your “big” table.

Imagine a sales table that, in addition to columns like Quantity and Amount, also contains columns like CustomerID, CustomerGender, CustomerAge, CustomerAddress1, CustomerAddress2, etc.

If you leave CustomerID in the sales table but “move” all of the other customer columns into a separate Customer table, and then link the two tables by Customer ID, you can reduce the number of columns in your Sales table by a significant number.

And that can sometimes shrink your files dramatically, AND speed them up. See this post for an example.

Leave a Comment » | Custom Calendars, DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Guest Post: 15 Spreadsheet Formatting Tips

January 17, 2012

IF YOU ARE ARRIVING HERE FROM THE EXCEL BLOG: Welcome! In addition to reading Dan’s excellent post below, I highly recommend you look into PowerPivot if you haven’t already. Microsoft has never given you a bigger gift than PowerPivot and you just need to take it. A quick glance at the top five benefits it provides will whet your appetite and then this collection of links can get you started.

–

Last week’s post about the importance of formatting spreadsheets published to SharePoint drew a lot of interest, including from some of my old colleagues on the Excel team back in Redmond.

Dan Battagin is one of those guys who seems to have a special deal worked out with Time. As in, Time has agreed NOT to apply to him. While often doing the work of two or three people at MS, Dan was typically completely remodeling his house, staying super-fit, and as a hobby, programming a number of robust applications in his “free time” at home.

So when Dan sent me a list of tips he’d been compiling on spreadsheet formatting and related topics, naturally I pressed him to expand that into a blog post. He said “well I’m not sure how long it will take me, no promises,” then sent me the following opus about 36 hours later. Showoff.

Yes, it is possible to create nice looking spreadsheets

Guest post by Dan Battagin

As a member of the Excel team I’ve been known to wear clothing that says “recalc or die,” (yes, I’m a geek) and one thing that makes me smile is a great looking spreadsheet. Luckily, I smile other times too, because basic spreadsheets don’t generally look that great – like everything, Excel gives you the tools, but leaves it up to you to use them.

Today, I’ve pulled together 15 tips that can help you step up your spreadsheet design So let’s jump right in and create some sweet looking spreadsheets!

Here’s a pretty typical spreadsheet. It shows data about a Windows Phone 7 application, to help understand how much it’s getting downloaded, and what its rankings are in the phone marketplace.

I see a lot of spreadsheets like this – it works, and has all the data you need. But boy is it ugly. In fact, I’d call it ugly three ways:

1. Layout – nothing is really organized. It’s like someone threw up data in the sheet.

2. Style – it has the default Excel style – another way of saying it doesn’t have any.

3. Structure – it uses the grid, but Excel’s got a lot more tools than that folks!

So, let’s see how we can turn this into a great looking spreadsheet by addressing these three areas. In the end, here’s what I’d like to see instead – these screens show the same data, but using the tips below to make this spreadsheet something that I wouldn’t mind looking at each time it was open (and not just because of the kittens).

Tip 1: Don’t use row 1 or column A

Just because the grid looks like a table doesn’t mean you need to use all the cells. Using row 1 and column A almost always puts your data too close to the row and column headers (A, B, C, … and 1, 2, 3, …) and things look squished. Do yourself a favor and use the first row and column for spacing. Personally, I size them to 10px and then start in B2.

Before	After

Note from Rob: I sometimes go one step further and make the first row and column a single pixel, THEN make the second row/column about 10 pixels as Dan does here. That way I can park selection in that first cell and have it be barely noticeable:

That “dot” in the upper left is cell A1

The other thing I often do is select a cell that is completely hidden behind an image.

Tip 2: Resize rows and columns

You can make your spreadsheet look a lot better if you space things out a bit, and leave room to read the info it contains. Make cells that contain data wide enough to read most of the content. Leave empty rows or columns between data as a visual separator. For finer grained control, leave multiple rows or columns, and size them to just a few pixels, so you can adjust later.

Before	After

Tip 3: Merged cells are evil, instead use Center Across Selection

This one surprises a lot of people, but it’s true. When you merge a cell in Excel, you’re really limiting what you can do in the future because Excel still “thinks” in rows and columns, and merged cells muck that up. For instance, you can’t insert a cell if any of the cells in that column are merged across multiple columns; you can’t create a table if there are merged cells in the range; and you can’t sort or filter a range with merged cells either. There is a workaround however and it looks exactly like merged cells once it’s applied – it’s called “Center Across Selection” and it’s available on the Cell Format dialog. To use it follow the steps below, or check out this video from Mr. Excel.

1. Select the cells you were going to merge

2. Open the Format Cells dialog (Home Ribbon, Font group, little button in bottom right)

3. On the Alignment tab, set the Horizontal alignment to “Center Across Selection” and click OK.

4. Type the value you want centered into the left-most cell of the selection from step 1.

Seen this?	Use this

Note from Rob: I admit, I still use a lot of merged cells to center titles and such. I know about merge across but old habits die hard. That said, I think the unspoken tip here is to center your titles in the first place. Consider these examples:

Uncentered Title

Centered Title

Tip 4: Align everything, and keep related data close

All too often, data ends up wherever you clicked and then pasted it into the spreadsheet. Charts end up “near” the data. Shapes and images are placed semi-randomly. Spend some time to align everything and you’ll get a much cleaner look. If you have two tables of data, make sure they start in the same row. If you have a chart, set it to Snap to Grid, and then make it the exact width of the columns you want it to span. If you have multiple charts, line them up. The human eye gets distracted when objects aren’t aligned because our brain tries to align them internally…so help your brain.

Before	After

Tip 5: Keep it to 3-5 colors – white, gray, and 1-3 accents

When it comes to color in your spreadsheet, less is more. For the background, use white. Studies have shown that dark text on white is much easier to read (and looks better to most people). For most text, and for anything that’s black by default in a chart (labels, axis, gridlines, etc.) use a shade of gray – it’s less jarring than black, and it “feels” modern. And when it comes to colors, don’t go overboard – choose an accent and stick with it throughout the entire workbook. I tend to like a shade of green but any accent will do (and less saturated ones tend to look better). If you’re building charts, you may need a second or third color too to differentiate the series – that’s fine. Note that Excel doesn’t actually help you here – we added a lot more colors in Excel 2007 (from 64 to infinite) and that tends to make people want to use them (all). More colors are good, but only because you can pick the few that you like.

Before	After

Tip 6: Don’t use more than 2 fonts

By default, everything in Excel is one font (and that font depends on your version – it might be Arial, Calibri, or even Segoe UI), but the font picker is so prominent in all versions that I see a lot of workbooks with a dozen or more fonts. Like colors, it turns out that less is more here. Choose a couple of fonts and stick with them – use font size (as opposed to different fonts or bolding) to differentiate parts of the spreadsheet. A clean, modern looking combination is Segoe UI for “normal” cells, and Segoe UI Light for any headers. And remember – make most of your text gray (you can do this easily – right click on the Normal style in the ribbon and change its color).

Before	After

Note from Rob: Whoa! Multiple fonts! Never occurred to me. Hmmm….

Tip 7: When you’re done, turn off gridlines and headers, and chart borders

Nothing can do more for your spreadsheet than turning off the gridlines and row/column headers. If you structure your data right (use tables – see Take advantage of tables) then you have all the borders you need and the gridlines just make things look busy. And the row/column headers might remind you that you’re in Excel, but when you’re viewing (as opposed to authoring) a workbook, you just don’t need them. While you’re at it, turn off any borders on charts – it looks a lot better when the graph is just “on the background” of the sheet.

Before	After

Tip 8: Don’t forget the images

With the prominence of the grid in Excel, it’s easy to end up with a spreadsheet that’s just text and numbers – boring. Spice it up a bit with your company logo – or better yet, the logo of the subject of the spreadsheet. Don’t go overboard here, but a few representative images will go a long way to making the end result nice to look at. Oh, and if you can, try to get ones with white backgrounds, so you don’t end up with a bunch of intrusive rectangular images.

Before	After

Tip 9: Ignore the fancy formatting options

This is a follow-on from Tip 8: Don’t forget the images, but applies to other elements of the spreadsheet as well (charts, shapes, WordArt, etc.) Nearly all the time you think that a drop shadow or reflection or glow or rotation or gradient or perspective or bevel would look good, it usually won’t. What it might do is distract from the clean simplicity of a modern spreadsheet. So, rather than look for “cool formatting to add,” remember that “simple formatting is cool formatting.”

Before	After

Tip 10: Take advantage of tables

Tables were introduced in Excel 2003, but didn’t really become useful until Excel 2007. Now, if you have tabular data, it should be in a table. This gives you quick access to sorting and filtering, makes the data easy to reference into, and – even if you don’t use them for any of the data capabilities – gives you a bunch of nice formatting options. When you choose a format, choose one that uses your accent color, and try to grab one of the Light or Medium styles. Remember – less is more.

Before	After

Tip 11: Flatten your data

This tip is a bit more advanced, but the idea is that any data you enter in the spreadsheet should be completely un-aggregated. This means that each row should stand completely on its own. For instance if you know that on 3/11/2012, you had 12 downloads from the United States, and 82 from the United Kingdom, you should enter that as two rows with columns named “date,” “country” and “downloads” rather than one row with “date,” “United States,” and “United Kingdom.” The reason for this is that Excel is GREAT at aggregating data for you (with less mistakes)…which we’ll get to in Tip 12: Let Excel do the math and Tip 13: Don’t be scared of PivotTables.

Before	After

Tip 12: Let Excel do the math

Spreadsheets aren’t just a big table that you can enter values into – they all support formulas, which can do almost anything you want to do, faster than you can do it yourself. Need to add 100 values? Excel can do that for you. Need to find the average of some value for each month? Excel can do that for you.

Before	After

Note from Rob: Or let PowerPivot do the math of course

Tip 13: Don’t be scared of PivotTables

PivotTables in Excel are a way to group and summarize your data. If you’ve entered your data as suggested in Tip 11: Flatten your data, you probably have several categories of data in your table. By putting this into a PivotTable, Excel will automatically sort the data into those categories and show you a grouped view. Plus, just like Tables, PivotTables give you some really nice formatting options that make it easy to see where each group starts and stops, as well as totals for each group way faster than you could do the totals by hand. Handy, fast, and clean.

Before	After

Note from Rob: most people reading my blog love pivots of course, but as I mentioned Friday, pivots are probably the most under-used feature of Excel. It was (and is) one of the ongoing missions on the Excel team to help a broader audience adopt pivots.

Tip 14: Use charts, but do not use 3D charts

You should always use charts – a quick visual representation of data is always easier to understand and looks better than a big table of numbers – but when you do, make sure they are 2D (“flat” charts). Not only do 2D charts look cleaner and more modern (see Tip 9: Ignore the fancy formatting options), but you can actually read the data in them. Because of the way 3D charts work, it’s nearly impossible to see the size of bars or columns, and lines look like they are floating in space. Do yourself and the people who are looking at your spreadsheets a favor and just don’t use 3D charts.

Before	After

Note from Rob: 3d charts don’t render as 3d in the browser either. See this post.

Tip 15: Name everything (esp. sheets and tables)

OK, this last tip doesn’t actually make your spreadsheet more visually pleasing from a “pretty” sense, but the more things you name, the easier it is to use the spreadsheet. It’s a lot easier to know what’s on the “Download Dashboard” sheet than it is to know what’s on “Sheet 2.” Likewise, if you name cells, tables, and other objects, you can refer to them by name in formulas, which makes it a whole lot easier to see what’s going on in the formula – =SUM(DataTable[Downloads]) is a lot easier to understand than =SUM(C17:C753).

Before	After

In Summary

So, those are my tips. Each tip by itself is pretty small – but once you embrace the lot, my guess is that you’ll be happy with the result. Plus, I’m sure there are more, and even some that I don’t know yet. And, if you’ve got a favorite (that I did or didn’t mention), let me know – post a response!

Oh, and if you’d like a copy of the sample spreadsheets I used, feel free to download them here.

17 Comments | Usability and Appearance | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Friday Bonus: 2012 IS the Year

January 13, 2012

Today’s Scene (Just add a Foot of Snow)

An obsessive and intrusive habit

Quick anecdote. For a couple of years now I have been accosting people in public places when I see them using Excel. On an airplane? In an airport? In a doctor’s office waiting room? You aren’t safe in any of those places.

If you’re using Excel, some weirdo is gonna walk up to you and ask you two questions:

Do you use PivotTables?
(If Yes) Do you use PowerPivot?

The first answer tends to be “Yes” about a third of the time. That’s disproportionately high relative to the broader population, in which only about 5-10% of people use pivots. There’s a selection bias in play here of course, because someone who uses Excel enough that it’s on their screen when I walk by is more likely than average to be an Excel pro, and therefore more likely than average to use pivots.

But for two years the second answer has always been “no.” I then explain that they should be, they express shame, they promise to check it out. Sometimes they even get a demo.

Until Today!

Recall that just a few days ago, I predicted this would be the year where we start running into PowerPivot via random personal connections and conversations.

This morning, at 10:15 eastern time, at the Starbucks on Cedar and Fairmount in Cleveland Heights OH, I noticed a well-dressed man sitting with his laptop, engrossed in Excel 2007 or 2010 (couldn’t tell, just saw the Ribbon).

Being a “work from home day,” I took stock of my own ensemble: Unshaven? Check. T-shirt and tennis shoes under a coat covered in dog fur? Check. Beanie-style snowboarding hat with a pom-pom ball on top? Check.

Perfect. I moved in for the kill.

I’m positive he thought I was going to ask him for money or something, and the smile that came over his face when he heard the pivot question was one of sheer joy and relief Smile

OK, so “yes” he uses pivots. Now for the second question, the one that engenders shame when the person has to say “no” to a question that so clearly, I think they should say “yes” to…

ME: “Do you use PowerPivot?”
HIM: “No, I don’t, but my team does. We use a lot of SharePoint.”

Quite the rollercoaster in that sentence. In sequence, my brain flashes the following reactions:

“He said no. Yep, just like every other random sample to date.”
“Whoa, his team does??? Wait, he doesn’t know what PowerPivot is does he? He’s bluffing!”
“Hold it hold it! Anyone who follows up a PowerPivot question with a SharePoint reference actually DOES know it. And quite well!”

Fantastic. I let him get back to work rather than chatting him up. Took some serious willpower.

1 Comment | Observations, Trends, and Wild Speculation | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Sales/Budget: Integrating Data of Different “Grains”

January 12, 2012

Latest Article for CIMA Insight

Some of you know that I’ve been writing a series of articles for the Chartered Institute of Management Accounts, aka CIMA. Up until this point those articles have either focused on the organizational impact, the “why” of PowerPivot. Things like how it contrasts with traditional BI and traditional spreadsheets. How it saves costs and delivers more.

And then I started to introduce basic topics that have already been covered at length here on this blog, just simple how-to-get-started stuff.

But this month is the first time I wrote something for CIMA that I have not really covered here.

The Problem: 60k Sales Rows vs. 2k Budget Rows

Sales Table with 60 Thousand Rows

Budget Table with Two Thousand Rows

The Desired Result: A Single Unified Report

The Solution

Here’s where I have simplified it a bit for the introductory audience. I likely have a Calendar table in a well-designed model, and that doesn’t appear in the diagram. I also likely have to solve for more than just Time granularity – the Budget is also likely less granular at the Product level as well.

But to introduce this powerful concept, I think a simple example is best so I kept it clean.

Click here to read the full (short) article and see the details of how I create the linkage between the two tables.

In the Browser, Aesthetics Yield a Greater Return

January 10, 2012

A Spreadsheet in Excel Services Is No Longer a Document,
It’s an Application

I thought today was going to be a “handful of mini posts” kind of day but then this post blossomed into a bit more than I thought it would, which is a good thing.

Normal Spreadsheets are Usually Ugly and That’s OK

It’s true: most Excel pros are not artists. I certainly am not. We’re number folks first and foremost, and our jobs haven’t historically placed top-level emphasis on aesthetics either. So we don’t spend much time on it, typically.

Besides, Excel itself isn’t pretty. Back in the 80’s or 90’s, even if you made a spreadsheet look fantastic, well, it was still loaded in Excel. So you got all those lovely “battleship grey” toolbars, the title bar, etc.:

Spreadsheets of Yore Were Also Made Ugly
Just by the Excel Frame Itself

I want to be clear: This is NOT a post that is going to encourage you to run out and start putting lipstick on all of your spreadsheets.

Instead, I am going to make the case for why the game changes significantly (for the better too) when you switch to using a server (or a cloud hosting service like ours) to share your work.

The “Excel Frame” Has an Enormous, Underappreciated Impact

Hey, you might say that the old-style Excel screenshot above is an unfair example, since Excel 2007 and 2010 replaced menus and toolbars with the Ribbon. But in a crucial way the Ribbon is NO different really – the point is that either way (ribbon or menu/toolbars) the Excel frame is NOT YOURS. It belongs to Excel. And no matter how much work goes into the document itself, the user of that document still thinks you made them a document.

Let that sink in for a minute. Because the user of your spreadsheet thinks they are “using Excel” and not “using an application built by my favorite Excel pro,” you are receiving a hidden benefit AND a hidden penalty:

There are many things you will never be blamed for as long as the consumer thinks of Excel as the application. Hey, the overall experience just feels kinda clunky. “No big deal, that’s just how Excel docs always are.”
But you also don’t get nearly enough credit, psychological impact, or perceived importance that your work deserves. Seriously, your work drives your organizations. If everyone thought of you as a programmer (which you are, even if you don’t write macros), you’d be viewed differently. But people who produce documents are often viewed as “Excel Monkeys.” Honestly I think Excel pros are, for the most part, underpaid relative to their true importance.

When you switch from mailing spreadsheets around to publishing spreadsheets on SharePoint, well, both of those go out the window. Well, if you do it right, anyway.

SharePoint Brings Its Own Frames! Ack!

OK, so you switch over to using SharePoint as your publishing mechanism. Does that get rid of the Excel frame? Well not really. It just gives you a new Excel frame in your browser:

See all of those highlighted elements? Just another Excel frame, ported to the browser. Complete with File tab, toolbar, the .XLSX extension blazoned across the top, and even a warning bar.

Not to be left out, SharePoint adds some of its own at times:

So, you gotta get rid of those. And that means customizing SharePoint. If you’re a SharePoint pro that’s mostly not too difficult, but even then it likely will take you some time to get it tuned just right (we’ve been making tweaks now for two years). And if you’re not a SharePoint pro, well, you are going to need one.

(For more info on the details of these SharePoint elements and what we’ve done to modify/suppress them, see this post and this post).

Going Frameless Turns “Document” Into “Application”

In our Hosted PowerPivot offering, we’ve got all of that suppressed, and the only “frame” you see is just the browser and a typical web header. An example:

The Consumers of This Application Neither Know NOR Care that it Was Built in Excel

The idea for this post struck me yesterday as I was putting together this sample workbook (based on Microsoft’s AdventureWorks data set) that we are going to start including in all of our HostedPowerPivot sites as a tutorial:

Sample Workbook v1 For Hosted PowerPivot

Or try this humorous example (based on real data) that examines UFO sightings – click image to view the application on Mr. Excel’s HostedPowerPivot site.

Click Image to View the Live APPLICATION

Completing the Illusion: A Few Simple Steps

Once you’ve gone frameless, there are a few simple things you can do to complete the transformation from document to application. Neglect these and your “frameless” would-be application screams “spreadsheet” to the audience. Follow them, and even if you’re not terribly artistic, your work will be perceived very differently:

Turn off gridlines and headers. It’s not hard. Two checkboxes on the View tab of the ribbon, but do that for EVERY sheet the consumer sees. Every single one. Crucial.
Hide or delete all sheets you don’t want them to consume. Don’t leave extra blank tabs in there named “Sheet3” OK?
Insert some images. So important! Your company logo. Your client’s logo. Something. And make sure you use the Insert ribbon to do this! Simple copy/pasting an image into a worksheet, in my experience, seems to result in that image NOT showing up in the browser.
Line up slicers, charts, and tables. Takes just a minute or two to make sure the top of your slicers are even with the top of your chart, etc.
Don’t neglect number formatting. If something is a currency, format it as a currency.
Use conditional formatting. No need to overdo it, but conditional formatting turns a boring black and white grid (a pivot) into an inviting surface that is actually fun to look at. Plus, trends, patterns, and outliers jump off the page much more readily. I’m especially fond of data bars, color scales, icon sets, and when I have the time, sparklines.
Create a Menu sheet (Table of Contents), and use hyperlinks for navigation. Yes, the sheet tabs are visible. But why force people to use them? There are a million reasons why sheet tabs are disproportionately old fashioned and cognitively difficult. When it comes to navigating around a web application, nothing comes close to a hyperlink.

“I’ll Take ‘Hyperlinks Between Sheets’ for the Win”

Let’s focus on that last one. Did you know that you can hyperlink between sheets in a workbook? I worked on Excel for years and never really realized this. Our CEO at Pivotstream pointed that out to me, and it works on the server too.

Hyperlinking Between Sheets in Excel

This lets you create menu sheet like the AdventureWorks sheet above, as well as this one at the beginning of the post:

This Is Actually a PowerPivot Menu Sheet (aka Table of Contents)

Cool huh? Those chart thumbnails are IMAGES. The hyperlinks above them take you to the full-page interactive sheets that host each of those chart views.

Formatting Macros

I have a number of macros that help me do some of this stuff, and in an upcoming post I will share some of them, once I have time to organize them a bit.

In the meantime, here’s a real simple one whose intent should be obvious:

Sub HideGridAndHeadersOnAllSheets()

    Dim oSheet As Worksheet

    For Each oSheet In ActiveWorkbook.Worksheets
        If oSheet.Visible = xlSheetVisible Then
            oSheet.Activate
            ActiveWindow.DisplayGridlines = False
            ActiveWindow.DisplayHeadings = False
        End If
    Next

End Sub

How many of you use macros, by the way? I’m really curious. Drop me a comment if you would and just say yes/no. I will also put up a survey at some point if I get industrious.

Conclusion

I know that not everyone who has embraced PowerPivot for Excel has started using PowerPivot for SharePoint yet. That’s changing, but it takes time.

For those of you who are starting that transition, I’m very excited for you. I know it sounds weird. But you cannot appreciate how much more impactful your work “feels” as a web application until you see it in action. And this post is aimed at helping you reap that benefit.

For those of you who are yet to start down that path, file this one in the back of your mind for later.

3 Comments | Observations, Trends, and Wild Speculation, PowerPivot as Online Service, SharePoint, Usability and Appearance | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Welcome to 2012: the Year of PowerPivot Everywhere

January 3, 2012

Coming Soon to an… Everywhere Near You Smile

(OK so I said this post was coming on January 2nd, but really I meant the 3rd since today is Tuesday. Apologies for being so late in the day though).

Well it’s been a refreshing holiday season. I actually did a lot less work than I planned. That’s mostly a good thing, but I committed to a bunch of new things too, so I better have fully-charged batteries rolling into the new year.

So, why do I think 2012 will be the year of PowerPivot? Well, the first reason is that… 2013 isn’t here yet Smile . Because I am quite certain that every year will be a bigger year for PowerPivot than the previous. For a long time.

But 2012 will be the first year where we all start running into PowerPivot in places where we weren’t looking for it. I certainly see plenty of PowerPivot adoption in places that we all might consider “unlikely,” but I find out about those cases because those people reach out to Pivotstream for assistance. It’s not like I’m stumbling on it.

But I think that changes in 2012. I think we will start running into PowerPivot through random personal connections, because its adoption has just reached that point.

I’m increasingly meeting “High Priests” coming downstream while I am paddling upstream. Lately I keep finding myself on so-called “sales” calls with people who have been using PowerPivot for awhile and glimpsing its potential, and THEY start telling ME why they think PowerPivot is a game-changer. Not only that, but their reasons, their “talking points” if you will, are as crisp as anything I have ever captured on the blog. That certainly gets my attention.

Reason #1: My Uncle-in-Law Savvies the PowerPivot?

A funny thing happened over the holidays. My wife and I were visiting her family in Chicago, and the usual “so what are you up to these days” type of holiday party chatter ensued. Her uncle owns his own video editing business, and when he heard the word “PowerPivot,” he said:

“Hey I edited a video on PowerPivot last year!”

I am not accustomed to this sort of thing yet. People who aren’t spreadsheet or SharePoint or BI pros really have no reason to know about PowerPivot at this point. So my response was naturally something like “are you SURE it’s PowerPivot? Was it computer related?” Turns out, it was an Intel video from TechEd 2010, which I even attended:

Not the best video in terms of content, but the editing is SUPERB

OK, yeah, it’s from a year and a half ago, so it’s not exactly a sign of a recent tipping point.

But it’s still my first-ever completely random, “normal, non-number-crunching-person has a connection to PowerPivot” moment. I expect to have many more of these in 2012.

Reason #2: Blog Stats on the Rise, with an Exponential Flavor

PowerPivotPro.com enjoys a decent page ranking in the search engines, as evidenced by a quick google of the term “PowerPivot.” As such, I tend to regard traffic here as a decent indicator of overall PowerPivot adoption and awareness.

I’ve guarded these stats closely for a long time now (mostly out of insecurity, and not knowing what counts as “good” traffic for a blog). But the trend I have been watching is interesting, and I want to provide some detail.

The blog had its first full month in November 2009, about 6 months before PowerPivot v1 was released. Here is a graph of total page views per month going back to the beginning:

PowerPivotPro Monthly Blog Views Since Inception

Notice how the little orange trendline doesn’t actually keep pace with the recent traffic numbers? That’s because we’ve seen an inflection point this Fall. A sharp rise.

Curious as to what this would look like just over the last eighteen months, I filtered it down:

PowerPivotPro Monthly Blog Views Since PowerPivot V1 Release Date

That’s probably a better indication of trend, since those 18 months roughly correspond to the time since PowerPivot v1 was released.

Now if we extend the trendline out another six months into the future, we see that we’re trending toward 35,000 a month by June, about 3x what it was the same time in 2010.

Same as Previous Chart But Projected Six Months Into the Future

Lastly, it’s instructive to look at the data in “year over year” fashion:

PowerPivotPro Monthly Blog Views Since Inception, Year over Year

See how the gain from is bigger from 2010-2011 than it was from 2009-2010? That’s an example of exponential growth: the audience is growing at a rate that is proportional to the existing size of said audience.

One of the hallmarks of exponential growth is that it tends to seem slow at first, because the size of the population is also small at that point. Later, once it has a good foothold, it surprises you with startling increases in a short period. This is literally the way that viruses and bacteria multiply, and when something spreads “virally,” it follows this sort of a curve.

For examples of this, see my reference to the “magic eyedropper” story near the end of a previous post, and here’s an article that explains how plants/gardens follow the same sort of growth curve.

Typical Example of Linear vs. Exponential Growth:
Note How the Exponential Curve Seemingly Goes Nowhere for Awhile,
Then Starts to Pick Up, Then Goes Literally “Viral”

Reason #2a: A Past Observation by Bill Baker

I was at a TDWI conference in San Francisco about seven or eight years ago where I attended a panel discussion. Bill Baker was one of the three panelists. Bill was asked if he thought a particular technology (I think it was web services like SOAP) was going to be a big deal or a one-hit wonder.

I really liked his answer, which was to point out that we often tend to OVER-estimate a new technology’s impact when we look ONE year into the future, but we tend to UNDER-estimate its impact FIVE years in the future. In other words, “hot” technologies tend to “disappoint” at the one year mark, but by the five year mark, they often have quietly popped up everywhere.

Today, it struck me that Bill’s answer is really just the anecdotal way of describing exponential growth: something that spreads by word of mouth, by example, by experimentation, and by proving itself the hard way, rather than being adopted on command. I’m pretty sure Microsoft wishes it could command this one, but it won’t have to.

OK, on Thursday we have a killer new post by the esteemed Mr. Churchward. Stay tuned Smile

Leave a Comment » | Observations, Trends, and Wild Speculation | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Why PowerPivot is Better Fed from a Database, Pt2

November 24, 2011

Think of a Database as a Bodybuilding Supplement
for Your PowerPivot Regimen

In part one I covered the following three benefits of pulling data into PowerPivot from a database, as opposed to using other sources such as Excel itself:

Data-shaping – much easier in a database than in basically any other tool, this is the first benefit you will see.
Auto-refresh – by removing manual data shaping from the system, you can then rely 100% on PowerPivot’s server-side automatic refresh to keep your reports up to date. The first time in “Excel history” that Excel pros aren’t slaves to their own spreadsheets.
More quality, less errors – the combination of PowerPivot’s “portable formulas” and performing your data shaping in a database greatly reduces the opportunity to make mistakes.

OK, time for benefits 4-7.

Benefit #4: Complex Calc Columns

Let’s say you want to stamp your customers into quintiles – the top 20% in terms of dollars spent, the bottom 20%, etc.

And you don’t want that to be dynamically re-calculated every time you slice or filter a pivot – you just want it statically calculated according to total overall sales, so that you can then use the quintile as a slicer or row/column axis in your pivot. You want to be able to offer a pivot report like this:

To do that, you need a calculated column in your Customers table:

But that’s a reasonably tricky calculated column, as it has to be calculated per-customer, against their matching rows in the Sales table, and then ranked against other all other customers. If you do that in Excel, you’ve introduced another manual step which foils auto-refresh and in turn introduces more chances for error, etc.

And doing it in PowerPivot is awkward, at least in v1. I’m not saying you can’t do it in PowerPivot, because you can, and that DOES auto-refresh. But it’s just so much easier in a database, if you have someone around who knows databases, and there are more complex examples than this one which get even harder in PowerPivot.

Benefit #5: Centralized, Re-Useable Logic

This one is both its own distinct benefit as well as a multiplier for other benefits (such as data-shaping and complex calcs). Anything you do in Excel is manual. Anything you do in PowerPivot is “tied” to the workbook in which you did it. But logic that you put in a database can be re-used across many different workbooks.

Which, of course, is very nice. No need to re-write it every time. No chance that it accidentally diverges from other models (a source of error). And if you need to modify that logic in the future, you only have to do it in one place.

At this point it’s worth pointing out that a PowerPivot BI environment DOES share some characteristics with a traditional BI environment. These db-centric benefits are a hallmark of traditional BI, and these are the things you want to preserve from the traditional approach.

Benefit #6: “Magic” Sliding Windows and Parameters

This is one of my favorites. Oftentimes, an organization operates according to a calendar, or even multiple calendars, that are not quite the same as the calendar hanging on your wall. And that leads you to a place where your measures like “growth versus prior year” can get pretty complex.

I won’t go into great detail here because it would take awhile, but imagine a slicer driven from a table that is purely calculated in the database before input:

To get a sense of some similar techniques, which revolve around using “unconnected” slicer tables as inputs, see this post and this post.

Benefit #7: Compression

It’s a fact: imported columns compress much better in PowerPivot than calculated columns. So if you have a choice between doing a calc column in your db (and then importing it) or importing a table and then adding that calc column via a DAX calc column in PowerPivot, DO IT IN THE DATABASE!

Better compression means smaller files, less memory consumption, and often faster response times to slicer clicks. (Note that the bigger the table, the bigger the difference you will see).

Again, deserving of its own post in the future.

Leave a Comment » | Data Import | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Why PowerPivot is Better Fed From a Database, Pt 1

November 22, 2011

An Excel Pro’s Two Year Journey in Database Land

In a post last week I mentioned that when you use a real database as a source for PowerPivot, a number of unexpected doors open up. As a longtime Excel pro who has spent the past two years working closely with a database team, I can tell you that it’s been an eye opening experience.

So I have been advocating to Excel/PowerPivot pros for quite some time that they cozy up to their database teams (if they have them). Of course, this works both ways, and I have also been advocating to Database/BI pros that they embrace and cooperate with Excel/PowerPivot pros.

Awareness, Flexibility, and Sometimes… Outright Resistance

It’s an interesting ambassadorship. On one hand, Excel pros are often accustomed to doing everything on their own, with no external help whatsoever, and so it’s mostly a matter of opening their eyes to benefits they’ve never imagined.

But the other camp often doesn’t expect to learn anything new and relevant about the role of Excel, that unruly little brother of “real” BI. In conversation, that camp splits into two subgroups rather quickly – one that is willing to consider new ideas and possibilities, and another that regards Excel as the devil. Interactions with that second group can get ugly. I also tend to fear for them a bit.

Today, I’m going to focus on the Excel crowd and explain why a db pro can make a huge difference in their lives. While I have danced around the topic for two years on the blog, this will be my attempt to provide a definitive list of the benefits.

And you die-hard Excel haters out there, don’t worry, I’m coming back around to you soon Winking smile

Benefit #1: Data Shaping is Easier in Databases. MUCH Easier.

Hey, Excel is just a collection of individual cells when you come right down to it. Cells go into formulas, and cells come out. But turning a wide and short table into a tall skinny table (or vice versa) for instance can consume the better part of an Excel pro’s day in some cases. That same operation might take a db pro 5 minutes or less. And even better, the next time might take them no time at all…

Benefit #2: Auto-Refresh!

Once the db pro has a script in place (or query or view or sproc or whatever it is that they deem best), it can now be run automatically in response to your refresh request from PowerPivot.

If you are running PowerPivot on your desktop, hey, now it’s just one click (refresh) to pull in the latest, properly-shaped data. But even better, if you have access to a PowerPivot-enabled SharePoint server, you can put your workbook up there and schedule it to refresh itself!

Even if the manual shaping that you do today only takes you a few minutes each time, it’s still worth it to outsource it to a db. The SharePoint option means you don’t have to do anything each day to merely update the reports, and the difference between “small” and “nothing” is huge. Like, “you can go on vacation without repercussions” huge. Or “you don’t have to come in early each day” huge.

Benefit #3: Quality

Did you know there are entire conferences devoted to the topic of spreadsheet errors? Spreadsheet errors are a fact of life in traditional spreadsheets, but they are primarily due to specific problems that a good PowerPivot “ecosystem” eliminates. One is the lack of convenient named reference – “what did D$14 refer to again?” crosses the inner monologues of Excel pros worldwide millions of times a day. PowerPivot fixes that – everything is referenced by table/column/measure name.

But raw repetition is the real killer. If you perform the same spreadsheet task every day for a year, pure statistics tells us you will make mistakes. And if the task is tedious, you will make even more.

The lack of “portable formulas” in traditional spreadsheets is an underappreciated source of repetition. Even a single iteration of a reporting task explodes into repetitious subtasks and provides lots of opportunity for error. (PowerPivot fixes that one too).

So we are left with raw repetition – performing the same task every day – as our primary source of error. Once you have PowerPivot, data shaping is the biggest source of repetition. Outsourcing that shaping logic into a database, then, doesn’t just save time. It prevents mistakes.

Why? Because the db logic is written only once, and it doesn’t change no matter how many times you run it. Even if there were mistakes made in the db logic, you will catch them sooner or later (usually immediately), and once you fix them, they stay fixed.

Come back Thursday for part two, with items four through seven.

Leave a Comment » | Data Import, Observations, Trends, and Wild Speculation | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Six Observations from the 2011 PASS Keynote

November 10, 2011

The PASS Summit tends to be one of Microsoft’s favorite venues for unveiling big news in the BI space. As you may recall, the 2010 Summit revealed some amazing things for the PowerPivot world.

Yes, I know that the 2011 Summit was weeks ago, and I’m overdue on my observations. And no, I did not attend in person this year. But the keynote tends to be the vehicle for the big news, and it was available via streaming. So I watched it later the same day.

It’s a couple hours long, vast stretches of it are dry wooden rhetoric, you can’t really fast forward it, and I don’t recommend watching the whole thing even though the highlights were worth it. I’ll share those here to the best of my ability.

Point 1: Denali Release Date “First Half of 2012”

OK, this means we will get the final production version of PowerPivot v2, the new Tabular BISM, and Crescent in first half of 2012. I was kinda expecting them to say first quarter of 2012, so I was a little surprised. I guess this means there is still time to get real feedback submitted Smile

Points 2-4: Cloud and Big Data

A very distinctly “cutting edge” feel to this year’s keynote. And honestly, there appears to be substance to it, not merely hype. I would say that the SQL team is one of the most nimble orgs at Microsoft, and one of the most responsive to changing customer needs.

2) “The cloud world is a hybrid of your data center and the cloud”
-SQL VP Ted Kummert

This was a very deliberate and prominent statement. It’s very interesting (and encouraging) that they said this – a sharp contrast to the MS reputation of “our offerings are the only things in the universe and are only designed to work with themselves.” (BTW – that reputation, while deserved, derives from the academic mindset of MS employees rather than from arrogance, but at times that’s a fine line).

The meaning here is that we will be able to opt in “a la carte” rather than being forced to convert completely to Azure in order to make use of service X. I like that a lot, because I expect some services to mature faster than others.

OK, maybe that’s not a big deal. That’s just good business strategy and perhaps obvious. But there’s a big difference between them stating this as a prominent theme (as they did) versus mentioning it as a detail, or merely bringing it up in Q&A (which is often the case). They were NOT saying this last year. So I call this a very positive development.

3) “Reporting Services Will Be Available in Azure Sometime Next Year”
(…and then nothing was said about Analysis Services)

I forget who said this – it was either Ted or Amir, or maybe both.

The real information for me here was what was NOT said. They said nothing about Analysis Services (SSAS), and the omission simply cannot be an oversight. It was too obvious, the void in the next sentence was tangible.

That means they either already know that it will be 2013, or they are trying for 2012 but aren’t sure enough yet to promise it. Either way, we can safely assume we won’t see SSAS Azure until late 2012 at the earliest.

Since PowerPivot is built on Analysis Services, that also means we won’t see any PowerPivot in the cloud until late 2012 at the earliest. Furthermore, Office 365 won’t support PowerPivot until late 2012, or probably 2013. That’s not a fact, but it’s a very safe guess.

4) Hadoop Support in PowerPivot!

PowerPivot and Hadoop: Sounds Like Chocolate and Peanut Butter Do you use Hadoop? I don’t either, at least not yet, but a number of our clients at Pivotstream do. So my ears definitely perked up when they said that we will soon have an ODBC driver that connects directly to Hadoop sources. And as a bonus, our boy Denny Lee got some stage time giving the demo.

Seems like a natural fit – PowerPivot’s ability to crunch large volumes of data coming together with the world’s most popular system for collecting massive amounts of web data. And again, a departure from the MS norm. I would typically expect MS to hastily invent a Hadoop competitor and rush it to market, then take five years to make it a credible competitor. Maybe that’s still a long term goal, but to embrace something with open source and Google roots like this so prominently is again a very novel and mature move that we should salute.

I’m actually getting a more in-depth demo and update today, so I hope to report back with more detail soon.

5) Introducing Data Explorer!

How often do we get something 100% brand new? Data Explorer allows you to take basically any collection of data sources – like an Excel file on my desktop, a sales data set in SQL Azure, and a demographics data source on DataMarket – and mash them together into a single table.

Even better, it then allows me to publish the resulting data set, in Azure, so that others can consume it.

I have a LOT of questions about this new offering, but very little time to explore it. I have asked a member of the Data Explorer product team if I can interview them on the blog. If that doesn’t work out, maybe one of you out there would like to investigate it and submit a review to the blog.

Point 6: Crescent is now named Power View

Just like PowerPivot was known as Gemini until late 2009, we knew Crescent would eventually get a real name. And that real name is Power View. Yes, the space is official.

Point 6a: Live interactivity in PowerPoint (yes, the slides app) is going to be included in the Denali release after seeming like it was going to get cut. Pretty cool.

Point 6b: Purely my opinion, but Power View seems aimed at putting a more glamorous face on traditional BI scenarios – it’s a very “field list oriented” tool which in my experience means that only “data people” will take to it initially.

But I also DO believe that as Excel pros get more and more comfortable with publishing PowerPivot models to SharePoint, they will start opportunistically exploring what Power View can do for them, since Power View can be connected directly to a PowerPivot model and used as an alternative front end (or complement to) Excel Services.

Leave a Comment » | Observations, Trends, and Wild Speculation, PowerPivot Links, PowerPivot V2 | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Mini Post 4/4: Refreshing the FAQ, Recruiting New Contributors!

November 3, 2011

The Great PowerPivot FAQ still gets great traffic every day, but I recently realized that no one had added any new FAQ items for a year.

Dave Boylan asked me a question the other day, and that triggered it: I have been neglecting it, and it could use an infusion of new content and contributors.

Submitting items to the FAQ is quite easy, quick, and straight to the point. A great place to gain some experience with the community without committing to writing full blog posts.

Even the FAQ Page here on the blog is piling up unanswered questions in comments. Answering one or two of those and adding it to the FAQ is a great place to start.

And you always get public credit for everything you submit. You also get to add yourself to the Contributors/Moderators list. (I think there are some people in that list who never submitted anything, and I’m going to trim them out at some point, muhaha).

Dave Boylan and David Churchward both recently signed up. Dave B then submitted this entry on the EARLIER function. (And another on market basket analysis).

If you are interested in becoming a FAQ contributor, drop me a note: info@pivotstream.com

1 Comment | PowerPivot Links | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

February 2012
M	T	W	T	F	S	S
« Jan
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29