Hardware/Capacity Planning: From the Trenches

November 30, 2010

So How Many Servers Do I Need in My PowerPivot SharePoint Farm?

As people increasingly move up from just dabbling with the addin, and decide to start leveraging the publish/schedule/share/secure benefits of the PowerPivot for SharePoint infrastructure, I am getting this question more frequently.

How many servers?  How much RAM?  And less often but just as important…  how many CPU’s?

Time for an old joke:  “Ever hear the one about the statistician who drowned in a river that was, on average, only 3 inches deep?”

That’s the whole joke.  And as time goes on, I only find it funnier.  And funnier.  But first, some basics.

The Three Primary Server Roles

For the vast majority of PowerPivot deployments, you will mostly need to concern yourself with three server types:  Excel Services, PowerPivot Engine, and Data Source DB Servers:

PowerPivot Server Roles Summary For RAM and CPU Planning 
(Click for Larger Version)

A few notes on the diagram up front:

  1. I recommend viewing the larger version – there is information on the diagram (in the notes) that I will not repeat below.
  2. Don’t read too much into the 3/4/2 ratio of Excel Services/PowerPivot/SQL – I included different numbers of servers in each tier intentionally, to illustrate that you can scale each tier out at independently.  But, for instance, I do NOT expect you will need 3 Excel Services boxes for every PowerPivot box (you will likely need less).
  3. Although separated above, server roles CAN be combined onto single servers – for instance, many folks can get by with an “all in one” server where everything above lives on a single box.  Also, even in a multi-server farm, one of the MS-recommended configs is to combine Excel Services and PowerPivot onto a single box, and then deploy as many of those combined boxes as needed.
  4. There are some elements missing from the diagram – for instance, there is a Web Front End (WFE) role, and a SharePoint Configuration Database role, but in my experience, PowerPivot does not put unique strain on those elements (except for config db disk space).  Your mileage may vary of course.

Early Planning Efforts

Back in the Spring, I set down to the very serious task of “how much hardware will we need on our SharePoint servers in order to handle user loads?”  And naturally, I made a very serious spreadsheet to model it out:

PowerPivot SharePoint Hardware Planning Spreadsheet v1

Wow, what a spreadsheet.  I mean, I even used the =POISSON.DIST() & =BINOM.DIST() functions – clearly, such spreadsheet horsepower indicates accurate results!

Turns out, that spreadsheet was nothing more than an amusing theoretical exercise with little bearing on the real world.

There were multiple problems with that spreadsheet:

  1. It relied on outright guesses as to how often/how intensely consumers would utilize the published reports.
  2. I was not yet aware of the CPU-gobbling power of slicers, a power that makes real-world queries many times more CPU-intensive.
  3. Most importantly, it assumed peak usage would occur in the morning, when consumers are most in need of fresh information.

Solution for 1) and 2)

These are in some sense the simplest to address.  Quite simply, put up a pilot solution and observe the usage characteristics.  You will learn a lot about usage patterns as well as what that does to CPU and RAM.

But even better:  you may choose to simply ignore these factors for now and focus on problem 3 instead.  Here is why:

Peak Load is Probably Experienced During Scheduled Refresh

If you’ve looked at the larger version of the diagram above, you have seen a hint as to what I’m about to say here: 

At Pivotstream, our nightly refresh process puts far more strain on our servers than our users do, and is what we now plan our hardware around.

I will explain further, so you can evaluate whether your situation will be similar.  We receive new data on a nightly basis (typically late at night).  That data gets ingested into SQL server, and then our PowerPivot refresh process begins.

Once PowerPivot refresh begins, we have about 6 hours, tops, to get all models and reports refreshed, so that when business opens in the morning, everyone has access to fresh insights.  6 hours sounds like a lot…  until you discover that it isn’t.

A lot happens during scheduled refresh!

Keep in mind that the PowerPivot model (the embedded database living inside your PowerPivot workbooks) must first be refreshed – let’s call this phase “Model Refresh.”  This can put a lot of strain on your SQL servers just in terms of supplying the data, since PowerPivot v1 pulls a fresh copy of all tables (no incremental refresh).

As the data flows into the PowerPivot servers, a LOT of CPU power goes into compressing that data into the storage format.  During the refresh process, RAM usage steadily climbs as well.

As model refresh nears completion, RAM usage spikes upward quite a bit, sometimes by as much as 50-100% the size of the resulting workbook.  CPU usage also spikes during this phase.

Once the model is done refreshing, PowerPivot for SharePoint then triggers a refresh (via Excel Services) of the Pivots and Cube Formulas in the workbook, so that when thumbnail screenshots are “snapshotted” for the report gallery, those reflect the latest data (let’s call this phase “Pivot Refresh”). 

Yes, that process initiates via Excel Services, but that refresh immediately results in a meaty set of queries sent back to the PowerPivot servers for processing.  So, CPU usage spikes again.  And the model in question is held in RAM while that happens, so that RAM can’t be recycled into the pool to be used for model refresh.

Remember, this all happens for a single workbook.  And if you hang a bunch of report-only workbooks off of a single “hub” model workbook (as we do all the time), the amount of time it takes to finish Pivot Refresh can actually exceed the time for Model Refresh.

If you have to cram all of this into a narrow nightly window, chances are that you will need more hardware to pull it off than you ever will need for normal daily usage!

Rough Guidelines for Your Own Situation

This post is running a bit long, so I will try to be succinct in closing:

  1. If a significant percentage of your PowerPivot models and reports will be refreshed nightly, refresh is likely going to be a larger peak strain than normal interactive usage
  2. Even if you only refresh weekly (or less often), if the execution window between “data is ready” and “reports must be ready” is basically still a single night, then refresh is still likely going to drive your peak hardware need.
  3. If you think “Peak Load = Refresh Process” is likely the case for your organization, I suggest ignoring interactive usage projections during your hardware planning process, and instead developing some prototype workbooks (with rich data sets and mutliple report sheets with 100% realistic slicer sets, measure complexity, etc.), and then putting those workbooks through the refresh process to get a baseline.

9 Comments | Performance, PowerPivot as Online Service, SharePoint | Tagged: | Permalink
Posted by powerpivotpro

Thanksgiving thought: A BI Pro Reflects on Investing

November 25, 2010

With the Thanksgiving holiday here in the US, I thought I’d briefly depart from the norm and offer up some “food for thought” that’s been rattling around my head for awhile.

Given my analytical/numerical nature, the topic is hopefully one you will still find interesting and relevant…  and oh yeah, it’s about money, which we all like :)

The Magic of Modern “Investment”

In a nutshell, here is how investing works for most people today: 

  1. You take some money
  2. Set it aside in a special account
  3. The money grows for retirement, without much further effort

Years from now when you retire, an original $10k might have become $100k, or more.  The first few times I was introduced to this concept, I wondered, in my normal questioning way, “where does the $90k come from?”  But concrete answers weren’t available, so pretty soon I just started funneling money into investment accounts like everyone else.

OK, really, where DOES that extra money come from?

Questions like this are the sort of thing that I can never really put down, though, so for the past 15 years, I’ve been noodling on it, at least occasionally.  I’m much closer to an answer today, and the answer is kinda…  strange.

There are really only a handful of places that magical $90k can come from:

  1. Technological advancements that increase the total productivity of society – if we become 10 times as efficient/productive, your money can grow 10x.
  2. Smart investment decisions – buying dot-com stocks in 1997.  Selling them in early 1999.
  3. Bubble-style shifts – basically, all houses in the US rose in value for ten years, and now we are well on the road back to the levels of ten years ago.  But just like the dot-com boom, you can make a lot of money if you time it right.
  4. Competitive shifts – for instance, the United States was the only industrial power to emerge from World War Two with its manufacturing capacity intact.  So investments in US companies panned out pretty well as the US became the world’s source of goods.
  5. Other people’s work – sounds strange but it’s true.  If, for example, population booms during your investment timeline, total productivity rises just as it does when technology advances.

Strange Observation #1:  Who Invited Mr. Zero Sum Game?

Notice something about numbers 2-5 above?  They are all competitive.  They all have winners and losers.  If you sold your dot-com stock in early 1999, or your house in 2007, some poor sap had to buy it from you and suffer the subsequent decline in price.  If one country advances on the world scene, that only happens relative to (and at the expense of) others.  And if you gain money as the result of other people’s productivity, it stands to reason that you are gaining benefits that otherwise could have gone to them.

Only broad technological advancement raises all boats.  Everything else is zero sum.

Strange Observation #2:  Aren’t We All Following the Same Formula?

Anyone who’s ever worked with an investment adviser (or studied the management of their own money) knows The Formula: 

  1. Take the desired lifestyle in retirement in terms of “money available to spend each year”
  2. Work backwards from that to a goal (dollar amount you need to amass)
  3. Given the number of years left to retirement, determine the needed annual rate of return
  4. Use that required rate of return to allocate stocks vs. bonds etc.
  5. Diversify across asset classes to offset hiccups in any one asset class

Simple!  But…  the zero sum nature of most growth sources (above) means that when we follow The Formula, we are betting that either 1) technology will raise all boats and The Formula’s role is just to smooth out rough spots  or 2) my investment adviser can beat up your investment adviser.

The Formula promises some amazing things, too, like lifetime returns of 2000% or more.  For me to get 2000% return based on skillful application of The Formula alone, well, my adviser is not only going to have to beat yours, but many other people’s advisers, too.  So it’s not one winner for every loser.  It’s actually many losers for every winner.  Do you feel lucky?

So…  CAN technology deliver? 

Who knows, really?  It’s certainly possible.  But is it likely?  Hmmm…  who can predict?  I don’t have an answer.  I just know that my investment adviser never brought this up.  His whole plan hinges essentially on a 10x or greater tech-fueled advancement in total productivity.  He’s a Formula guy, through and through, not a tech futurist.  I think he’s in the wrong business.

Better business intelligence tools are one component of technological growth.  We better get busy :)  

But if we all expect 10x or more return, we should be honest with ourselves – we are gambling in a big way.  Nothing wrong with that of course, but it isn’t the safe green path they draw out for us on those Fidelity commercials.  We are expecting something for nothing, and that sort of thing very often does not work out.

What DOES Work Out:  Providing Real Benefit to Others

I don’t think the notion of investing is fundamentally flawed, I just think we’ve drifted away from what really works.

Quite simply, if you start something that benefits others, you will be rewarded with real growth on your investment.  Venture Capitalists, for instance, fund companies from the beginning. 

I believe our friends at Predixion, for instance, are funded by VC.  If Predixion successfully brings high-powered, easy to use, and affordable predictive analytics tools to the masses, well, society will have benefited.  And the money that society trades to Predixion in exchange for that benefit will be both voluntarily and well-spent.

The venture capitalists will have helped society.  They will benefit accordingly because they will now own part of a valuable company.  That makes fundamental sense in a way that mutual fund investing does not.  Note also how they take a tangible risk when they lay out their money – not every venture works out, and when they don’t, the original venture funds often are completely lost.

Venture capitalists accept that risk.  No one in the VC community kids themselves into thinking there is a Formula, or a green line to follow.  One positive benefit of that risk is that they are incented to be incredibly careful about what enterprises they fund.  For every company like Predixion that gets funding, there are many that do not.

So venture capitalists also end up serving a “filter” role – they sift through ideas, strategies, and, frankly, the people with those ideas and strategies to find the ones that are most likely to make money…  and that tends to correlate strongly with societal benefit.

Summing Up:  How Does This Impact Us?

Recapping:

  1. ALL investment involves a lot of risk, more than what we typically acknowledge
  2. Most investment gains come from competitive, zero-sum mechanisms
  3. The Formula is no guarantee (I can say more on this but am out of time)
  4. If you want to invest more soundly, accept the risk, manage it yourself, and start/expand something that benefits society

You’re probably asking, “how the heck do I do #4?”  It’s true, not everyone can be a venture capitalist in the typical sense.  I know I certainly don’t have the money to start something like Predixion.

But…  you CAN be a venture capitalist, of sorts, on a local scale.

Here’s one of my own pet theories to illustrate what I mean:  lots of people are out of work these days, and their spending habits are changing as a result.  Brand new clothes, for instance, are a needless luxury, and I personally think that thrift stores are now going to be a growth business.  (There’s plenty of cheap retail space available these days too). 

If you took the risk and started a successful thrift store, you will have benefited a lot of people:  the people who sold their old clothes got money to buy other necessities, the people who bought them got cheap clothes (saving them money to buy other necessities), and you probably employed a couple of people along the way.  And hey, maybe you yourself didn’t start the store, maybe you funded someone else who had an excellent plan for the thrift store THEY wanted to start, but couldn’t afford to.

Interesting huh?  That whole “think globally, act locally” hippy slogan might actually intersect with one of the best ways to become wealthy in the coming years.  I really like that sort of positive irony.

Back to PowerPivot in the Next Post

Don’t worry, I will not make this off-topic thing a habit.  If a subset of you find these sorts of things interesting, I may start a second blog and occasionally post there.

But “the PowerPivot remains strong in this one,” I assure you.  I think I may next post about hardware planning and PowerPivot server roles.

Happy Thanksgiving, everyone.

Leave a Comment » | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

Time to Rename Traditional BI?

November 22, 2010

 
Intelligence Didn't Mean Intelligence Before PowerPivot

“You keep using that word, ‘Intelligence.’  I do not think it means what you think it means.”

-Average BI Consumer

Wrapping up an old thread

Awhile back, I wrote parts 1 and 2 of “PowerPivot Consulting Observations” (part one here and part two here) in which I hinted at some potentially controversial conclusions…  but then I trailed off into other topics.  I have not forgotten, however, and it’s time to close the loop.  Let’s dig in shall we?

Revoke the License to the Word “Intelligence?”

Basically, my conclusion is this:  whoever coined the term “Business Intelligence” is a genius, because the term sparks the imagination.  It gets people excited about high-tech, hyper-sensitive equipment in the hands of inquisitive, focused, and nimble knowledge workers.

I’ve written about this many times before, of course, but the theme never gets old for me because it seems so absolutely central:  BI, as experienced over the past 10+ years, simply does NOT deliver on that vision.

Here are some adjectives that apply to most folks’ experience with traditional BI:  Slow.  Disciplined.  Iterative.  Consolidated.  Managed.  Controlled.  Foundational.  Structured.

“Intelligent” never makes the list.  Ever. 

And so, traditional BI, I think it is time to stop calling you “Intelligence.”  I’m absolutely serious that “intelligence” is a misleading label, and sets expectations impossibly high relative to what traditional methods and toolsets can deliver.

What SHOULD we call it though?  Data Stewardship?

I don’t really have any hope of changing the industry lingo – Traditional BI will still keep its “I” no matter what a few of us say, but as a thought experiment, I certainly think it is a valid exercise.  Because remember, I DO very much value the work that goes into traditional BI projects.  I just think the word “intelligence” is misplaced, and that completely miscasts PowerPivot, and undersells what it can deliver.

So, as a means of provoking discussion, I’ll throw out “Data Stewardship” as a potential replacement for the term “Traditional BI.”  Or is “Information Discipline” better?  Remember, the goal is not to demean traditional BI, but to distinguish it from the experience of things like PowerPivot (and, I suspect, the experience we will get with BISM in the next release of SQL).

I mean, once you’ve seen PowerPivot do its thing, you realize the two worlds couldn’t be more radically different.  With PowerPivot, business unit workers who normally glaze over at BI sit forward in their chairs.  They are engaged.  They are excited.  They want to dig deeper, they want to LEARN.

And, they want to engage with the folks who manage all of the critical business data.  They want to partner with them.

That is, of course, how it should be.  I just think that the nomenclature we currently use, putting both disciplines under the same umbrella of “BI,” does no one any favors.

Alternate Approach:  Recast PowerPivot as “NOT BI?”

Another idea:  since “BI” has meant information discipline for so long, and is therefore an entrenched term, maybe we should just describe PowerPivot differently?

Officially, Microsoft refers to PowerPivot as “Managed Self-Service BI,” which woefully understates its value.  Taking the adjectives from above, it often comes across as “You mean I can be slow, iterative, and deliver underwhelming business value ALL ON MY OWN?  WAHOO!’”

Back when Ariel Netz was Group Program Manager of the Analysis Services team (the PowerPivot team), he proposed calling PowerPivot “BI 2.0” – I liked that then, and I still like it now.

Any other ideas?  I’m just pretty sure that “Self-Service BI” doesn’t cut it.

Summing Up:  True Discipline vs. True Intelligence

Whatever we ultimately end up calling these two skillsets/investments, I think the core difference comes down to this:

Traditional BI is primarily the art of minimizing mistakes, and eliminating the paralysis that stems from confusion.  Whatever numbers are produced, you can be sure they are “the truth” according to all corporate policies and systems.

PowerPivot BI is the art of developing a much clearer business picture, and in near-real time.  It is the art of generating a much better, more informative truth. 

It isn’t going to deliver as much discipline as traditional BI of course, but still a LOT more than traditional Excel does.  Plus with BISM, PowerPivot models will be directly importable into the “heavy duty” toolset, and can therefore be seamlessly “taken over” and absorbed into the 100% managed, aka traditional, space. 

Perhaps we can think of PowerPivot BI as a “scout” for traditional BI, where scouting is an everyday, constantly evolving exercise, and traditional BI is more of a slow-churning background process that is always digesting the most core and relevant findings from the scouts.

5 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

Learning DAX Measures – the “Spicy Function” Scale

November 16, 2010

 
DAX Function Spicy Complexity Rating Scale

“I will start with the calculate curry.  And a green tea.”

-Wise first time DAX customer

Is There a Preferred Way To Learn DAX Measures?

I’m glad you asked :)   Actually, I do get asked this quite often – in email, on Twitter, and in person at events like last week’s PASS Summit in Seattle.  I promised the folks at my Birds of a Feather lunch table that I would blog about the approach that I take in my consulting/training engagements.

I then, of course, promptly forgot.  Good thing Phil reminded me today in Twitter, because it gave me an opportunity to put together that menu mockup above.  Which clearly is where my time is best spent while watching Monday Night Football.

So, here it is.  The official PowerPivotPro technique, refined by decades spent in the trenches of DAX.  But first, some background.

Sometimes, You Actually CAN Have Too Much of a Good Thing

If you read my post on the official Excel blog, you may remember me saying something to the effect that DAX measures are a feature so rich, it’s as if Excel had a whole team working on them for a decade, then delivered it all at once in a single release.

That’s obviously a good thing, but it also comes with a downside:  it’s a lot to digest all at once.  Most products give you the simple stuff in v1, then add some more power in v2, continue in v3, etc. – and the user base gets to grow their knowledge slowly over time.

Not so in PowerPivot v1.  We’re basically looking at a version 5 product when it comes to DAX measures and all of the functions supported.  So it’s a bit daunting – of all these many functions, which ones would have been released in v1, had we not skipped straight to v5? 

Because, hey, those are the ones we want to learn first.  But, they are hiding in there next to functions from v2 – v5, and the function names provide little clue as to complexity.

The Spicy Scale to the Rescue!

On Thursday night we had a very entertaining dinner with members of the PowerPivot/SSAS team.  At my table, we discussed the idea of a spiciness scale for DAX functions, to help people approach them in the right order.  It would be just like the spicy scale you see on menus at Chinese and Thai restaurants.

(I was suggesting the idea as something that might be useful in the product itself – I now have the luxury of just throwing out ideas at random, and my former colleagues decide whether it actually makes sense in the real world…  in a sea of ideas coming from all directions.  Their job is a LOT harder.)

Table 2
                      

Some amazing people in those pictures, including, but not limited to, people who get mentioned here all the time – Kasper (now on the PowerPivot team), Marco and Alberto, Marius of DAX Superhero fame…  and that is leaving out some of my all-time favorite people.

OK, let’s dive in.

Spice Level One:  Fundamentals, COUNTROWS, and DISTINCT

Before you get rolling, it is very important to understand how measures actually work.  And while you’re at it, it helps to have a couple of new (but simple) functions to try out.

This explanation works best in video form, so I’ll direct you to my YouTube video on the topic.

Spice Level Two:  CALCULATE

Now that you understand the basics of pivot cell context and how those filters apply to measures, it’s time to start overriding and modifying that filter context.

CALCULATE is the foundation for a whole world of magic, and it’s a lot like SUMIF, which is excellent news for Excel pros.

You can learn more about CALCULATE here, here, and here.

Also note that you don’t have to restrict yourself to the = operator here.  You can use <, >, <=, >=, and <> as well.

Spice Level Three:  ALL

Once you get comfortable setting fields to single values, the next expansion of your powers is removing filters altogether.

ALL is what you need.

Spice Level Four and Beyond:  “It Depends”

As I’ve mentioned before, the best way to learn is with your own data, rather than textbook cases.  So I would let that be your guide.

Many people will probably want to experiment with time intelligence functions next, employing them to change date filter context in much the same way that ALL does…  except with more specific, time-shifting results.  I really should do a new series of posts just on time intelligence functions.

Or alternatively, do you want a measure that iterates over values that are NOT shown in your pivot?  Or do you want a measure to be a MAX at the lowest level of the pivot but then a SUM at higher levels?  If so, I recommend you try SUMX next.

Or maybe you want to revisit CALCULATE, and instead of setting fields to particular values, or even comparing then to particular values using <, >, <=, >=, or <>, let’s say you want to compare those fields to *measure* values?  Well, you are ready for FILTER.  Sort of :)

I will probably return to this subject in the future, time allowing, but for now, this is the gist of it.  If you master levels 1-3, you have a tremendous amount of new power at your fingertips, and you are ready for new things, but it’s hard to cover any of those new things in a short paragraph or even a single link.  I usually spend several hours on the level four stuff with my consulting/training clients, as a point of reference (and that is with the comfort of the clients’ own data and business problems).

1 Comment | DAX | Tagged: | Permalink
Posted by powerpivotpro

Five Observations From SQL PASS

November 12, 2010

 
Wow, what a trip.  I barely slept.  Let’s get right down to it:  summary of my thoughts on the whole thing.

1) The Future of MS BI is (Basically) PowerPivot

As was widely anticipated, MS announced that the VertiPaq engine will no longer just be part of PowerPivot – it will be added to the “core” SQL Server Analysis Services (SSAS) product in the next release, codenamed Denali.

The VertiPaq engine, aka “the engine of the devil” in AmirNetzSpeak, is the technology under the hood in PowerPivot that makes the crazy compression and query speed possible.  The omission of VertiPaq from PowerPivot’s “big brother,” SSAS, in SQL Server 2008 R2 was due only to insufficient time to do both.

So… the MS team going back and rectifying the omission is hardly a surprise.  What IS a surprise, however, and a wonderful surprise at that, is that the core of the PowerPivot design and modeling experience is ALSO moving into core SSAS.

It may not be immediately obvious what that means to us, the budding PowerPivot pros of the world, so I will try to line it out clearly:

  1. Going forward, the DAX formula language will be supported (and encouraged) in the core SSAS product.  You will not have to learn a new formula language in order to take advantage of the heavier-weight tools, in other words.
  2. Similarly, the table-oriented modeling approach of PowerPivot will also be moving into SSAS core.  In PowerPivot, you see tables of data that look like Excel, and you relate them to each other, add calc columns, etc.  In SSAS vNext, you can still do exactly the same things.  They now are calling this table-oriented model “BISM” but I have already forgotten what it stands for :)
  3. On net, this means that all of the most important PowerPivot concepts will now be useable in core SSAS.  So you will be able to “graduate” from the Excel addin experience to the core SSAS modeling environment, if that appeals to you.  The big barriers that prevented people like me from mastering core SSAS in the past (namely the MDX formula language) will no longer stop us :)
  4. PowerPivot as it exists today will continue to exist, and WILL be improved as well.  If you want to stay in Excel, you absolutely can, and you WILL get new features (just not as many as you will get in core SSAS).
  5. Everything, or almost everything, the SSAS team does going forward will be available to us, the PowerPivot/Excel pros.  Examples:
    1. Larger data volumes – PowerPivot caps out at 2 GB filesize thanks to SharePoint’s file size limit.  Core SSAS with Vertipaq, DAX, and BISM will have a MUCH larger data capacity.
    2. Dimension Security – today, if you want employees in each department to only be able to see data pertaining to their department, you have to create separate PowerPivot workbooks for each department, which is a maintenance hassle.  In core SSAS, that is not the case – you can secure things in one place, no need to make copies.
    3. More – there will be more of course, but for now those two jump out at me as the most important that will ONLY be available in core SSAS.

2) Vanishing Separation Between Excel and BI

Because of these developments, at some point in the future – maybe 5 years, maybe 10 – I predict the line between “Excel Pro” and “BI Pro” will have all but vanished.  The range of skill levels will be as broad as ever – there will still be Excel pros who use VLOOKUP but never use DAX – but the sharp divide we see today will be gone.

Which is, of course, a very rational thing to have happen.  Long overdue in fact.

Another way to say this:  the top-end career possibilities of Excel pros are about to be expanded massively.  Here’s how I visualize it in my head:

Excel 2 BI Evolution

Hey Excel Pros – No More Career Walls 

3) “Project Crescent” Finally Gives Us Modern Visuals!

If you are an MS BI pro, you’ve long had the following visualization choices in the Microsoft platform:

Excel is easy and approachable, but limited to basically the same chart types that existed in 1985.  Excel charts DID get a facelift in 2007, and I’d argue that conditional formatting has improved so dramatically in 2007 (data bars, icons, gradients) and 2010 (sparklines) that it warrants mention as a visualization type of its own.  But…  no map visualizations.  No gauges.  No time-lapse animations.  Nothing like Pivot Viewer “card views.”  Nothing remotely “web 2.0” or otherwise modern.

Reporting Services and PerformancePoint offer things like maps and gauges, but still, no animations, no card views.  Oh, and hooking those up to PowerPivot isn’t exactly hard, but I wouldn’t say it is trivial either.

Brace yourself for Crescent, and the enthusiasm of the one and only Amir Netz:

Super modern – card views, time lapse animations, interactive exploration…  it’s like we stepped into a time machine and landed in, um, 2010.  Visualization has long been SUCH a weakness of the MS platform that I’d kinda grown used to it, and now suddenly it feels like a TRIUMPH rather than “they’re just doing what they should be” – delaying this long was a clever marketing ploy :)

Oh, and it connects directly to PowerPivot models.  Super simple, just give it a URL.  I’m hooked.  This will never replace Excel for us as a reporting and visualization tool, but I’m sure we will use it a LOT as a supplement.

4) The SSAS and SSRS Teams Actually ARE Cooperating!

The SQL team at MS has long had three teams in the BI space:  SSAS, SSRS (Reporting Services), and SSIS (Integration Services).  Both AS and RS got along great with IS, but in all honesty, AS and RS have behaved more like rivals for as long as I can remember.

Well, a number of the personalities behind the scenes that were responsible for that competitive vibe have departed.  And the results are resoundingly positive – the two teams are now cooperating.  Fully.

A few examples:

  1. Crescent Itself – is a product of the Reporting Services team, but it demos like a front end that was designed 100% with PowerPivot in mind.  Which it was.
  2. Blobs! – Since Crescent is so big on images, PowerPivot vNext is being expanded to support columns of type “blob,” which is a weird way of saying “your PowerPivot models can now contain images.”  Got a Products table?  Now you will be able to embed a picture of each product as a new column.  Just one example.
  3. Shared Data Model (BISM) – this is less interesting to Excel pros than BI pros, but to be brief:  AS and RS have long promoted competing data models (cube vs. semantic layer over flat tables).  There has never been a super good reason for that.  Moving forward, both teams are adopting BISM, which PowerPivot pros can translate to mean “the PowerPivot way.”

5) Other Goodies On the Way

Just a random grab bag of other fun things I saw coming our way:

  1. New DAX Functions – for instance, a *simple* RANK function…  and a fast one at that.
  2. KPI Design Tools – think of this as “conditional formatting defined in the model” that applies by default to every pivot you create – less repetition.
  3. Number Formats – similarly, you will be able to set number formatting in the model and have it automatically carry through to all pivots.
  4. Hmmm…  I think that may be all that I am allowed to talk about.  (I saw a number of other things but under NDA)

All in all, I can’t wait.  I hear it’s about a year before this stuff hits the market, minimum.  But the betas will be fun :)

6 Comments | Observations, Trends, and Wild Speculation, PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

OK… PowerPivot Green After All

November 7, 2010

 
Well, OK, I took the plunge.  Decided not to do the whole head – this way I can more easily get it dyed back to dark brown after the conference.  Anyway, this should solve the whole recognition problem :)

PowerPivotPro at SQL PASS Summit

SQL Pros…  I will see you Tuesday in Seattle.  (I don’t get there until late Monday night.)

Reminder:  I am @powerpivotpro on Twitter.  Good way to link up.

(And if you are NOT attending, I’ll likely use Twitter to share exciting news as it happens, and the word is that there is a LOT of big news coming from the product team at the conference, so it may be worth following just for those updates).

Leave a Comment » | Hello World / Administrivia | Tagged: | Permalink
Posted by powerpivotpro

Psst… Sports League/Team… yeah, you…

November 5, 2010

If you work for a sports organization, drop us a note at info@pivotstream.com – I have something interesting to suggest.

Leave a Comment » | Hello World / Administrivia | Tagged: | Permalink
Posted by powerpivotpro

Going to PASS? Track me down!

November 5, 2010

 

“Um…  you know…  on second thought… he’s probably not that entertaining in person anyway…”

Next week, I will attend my first-ever PASS Summit in Seattle.  (Well, first ever as an attendee – I did visit PASS a couple times as an MS employee, but really just for private meetings).  From monitoring the attendee excitement on Twitter, it appears that this is the highlight of the year for SQL pros worldwide.

And it seems at least as SOCIAL as it is professional.  Witness, on Twitter, the following streams:  #passawesomeness, #sqlkaraoke, and, I am not making this up…  #sqlkilt.

The SQL community is a bit foreign to me.  My background, of course, is not SQL – I come more from the “front end” side of the equation (but I’m happy to say that I *can* explain the difference between Inner and Outer joins.  See?  I do know *some* things.)

But hey, there are a million technologies.  No one can know them all.  But the SQL community goes a bit beyond – there is something *different* about these folks.  They are just SO community focused.  At TechEd, I accosted Thomas LaRock, aka SQLRockstar, and asked him point-blank what’s up with the SQL Community.  He blogged his answer here (the comments at the end are also worth a look).  So I’m pretty jazzed to attend.

Where I Will Be

Assuming that you are the least bit interested in attending a conference about SQL and then talking to someone whose core expertise is not SQL, here is where I am going to be (click for larger version).

PowerPivotPro at SQL PASS

Basically, the green items are the ones I have chosen to attend, even though many in blue were VERY tough to pass up.  The two circled in orange are the two places I am guaranteed to be just sitting/standing around talking to people, but feel free to ambush me elsewhere.  I am quite friendly and would love to meet you :)

Spotting me in the crowd

I’m excited to meet people.  Everything is more fun that way, even just saying hi.  So I have been debating dying my hair PowerPivot green.  But I am told (by @samanthahhh ) that will destroy my hair and I will look like a freak for months – not good when I visit clients.

I’ve been told multiple times now that I look nothing like my profile picture.  Well, that pic is 3 years and 30 pounds ago, before the PowerPivot Diet.  In fact, one longtime online contact, the first time we met, exclaimed, “whoa, I thought you were going to a lot shorter and skinnier!”  Hmmm…  not sure what to think of that.

So here’s a decent picture of what I look like – me and Kasper at TechEd this year (me on the right – the taller and less skinny one).

I may even wind up wearing goofy clothes that id me, like these.  No promises though, depending on weather and mood :)

Hope to see you there!

2 Comments | Hello World / Administrivia | Tagged: | Permalink
Posted by powerpivotpro

Add a “Last Refreshed Date” Readout

November 1, 2010

 
The King of Pivots

“Because there is nothing worse than a stale report.”

OK, so you’ve built some killer models and reports.  You’ve published them to SharePoint.  You’ve scheduled automatic refresh to run, say, once per week.

You have this PowerPivot thing All.  Dialed.  In.

But are the report consumers satisfied?  Hell no, they aren’t satisfied!  If there’s one thing you can be certain of, your report consumers will NEVER be satisfied.

And hey, that’s kinda cool.  It’s a good thing.  You weren’t given all this new power just so you can sit on your laurels, now were you?  Nope.  True PowerPivot pros aren’t even sure they can FIND their laurels, much less sit on them.  You will always be improving – both your skillset AND the reports you produce.

Today’s improvement:  automatically informing the report consumers of how “fresh” the data is.  It’s actually pretty simple.

Step One:  Add a [LastRefreshed] Measure

Hopefully, somewhere in the PowerPivot window, you have a column whose most recent date is always the date on which the data was last refreshed.

For instance, in a retail system you might be able to use your Sales table for this, as long as there are no “holes” in your data in terms of dates (meaning you always have at least one transaction per day).  Or perhaps the Calendar table you pull from the database is always current (and does not contain future dates).  Or as a third option, perhaps you can get your db admin to add a single-cell table just for this purpose.

In this example, I’m going to use the TransactionDate column from my Sales table:

PowerPivot Column Basis for Last Refreshed Measure

Did you know that measures can return dates?  They sure can, and it’s killer useful.  So let’s create a measure:

    [LastRefreshed] = LASTDATE(Sales[TransactionDate])

LASTDATE() is kinda like MAX(), but for dates.  So it will always return the most recent transaction date:

PowerPivot Last Refreshed Date In a Pivot

Neat huh?  A date returned as a measure, in a pivot.

Step Two:  Use that measure in a Cube Formula!

I think the most flexible, least intrusive way to display this measure in your report is to create a single cube formula for it, and then stuff that formula into a single cell.  That way you have complete control over appearance.

Two things to note:

1) Given that your report will often have columns that are oddly sized to make everything look good, I’ve found it much better to use a single cell formula that includes the label, rather than splitting it across two cells.

2) Cube formulas, when they return a date measure, format it as an integer rather than a date.  So you need to reformat it as a date IN THE FORMULA.  (You could just use format cells, but since we are putting the label AND the date in a single cell, you can’t just format the cell as a date.)

Long story short:  here is your cube formula:

    ="Last Refreshed: " & TEXT(CUBEVALUE("PowerPivotData",
     "[Measures].[LastRefreshed]"),"mm/dd/yyyy")

Plop that in the desired cell, and you are all set:

PowerPivot Report Home Page with Last Refreshed Date

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Pretty slick, if I may say so myself.

That cell will refresh every time the workbook is refreshed.  But it should not re-evaluate during an update, like when someone operates a slicer.

For more on refresh vs. update and how it can make a HUGE difference in performance, click here.

Leave a Comment » | Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro