January « 2011 « PowerPivotPro

Live in Europe? PowerPivot Workshop!

January 25, 2011

Remember Marco and Alberto? The guys who wrote the Ferrari of PowerPivot books?

Yeah, the book that I dogeared to death? The “your brain on DAX” book?

Well, they are scheduling a series of two-day workshops in Europe where you can learn from the masters themselves.

How does this compare to the training and consulting I offer? First, I think there are things they know better than I know, and vice versa. Let’s call it a tie on expertise

The first consideration here is that I don’t really do workshops/classes – I just do hands-on work with one company at a time. There are advantages to that approach that I firmly believe in, but in terms of cost, especially for smaller organizations, something like a class or workshop often makes more sense.

And could you find two better instructors than these two? I think not. Super sharp, funny, and just nice to be around.

If you live in Europe and are looking to soak up some serious expertise, particularly on the capabilities of DAX and data modeling, I think you should look into these workshops. They seem to be touring the whole continent – Frankfurt, London, and Amsterdam already scheduled with more on the way. A good opportunity.

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

Got a high end CPU? Please help w/ an experiment!

January 23, 2011

If anyone out there has a really high end CPU and wants to participate in a VERY quick experiment, please drop me a note – rob at pivotstream (dot) com.

Will take you five minutes. And as a bonus, I will explain to you why it is relevant to PowerPivot – advanced glimpse behind the scenes, weeks in advance of the eventual blog post

May even have a gift or two to line up for you as well.

To be clear, a high end processor is one that ranks near the top of these rankings:

http://www.cpubenchmark.net/high_end_cpus.html

I want to conduct this experiment in the next 24-48 hours.

UPDATE: results so far are a bit surprising. One of the top-end procs is one of the worst performers so far. The current champ is a mid-range, affordable proc that was just released in the past two weeks, based on the new “Sandy Bridge” architecture from Intel.

UPDATE #2: More results trickling in, updated the table below (click for larger version). I am going to leave this open for another day or two, so please get in touch if you would like to participate. You will need 64-bit Office 2010 and 64-bit PowerPivot.

UPDATE #3: New results and information as of Tuesday morning, Jan 25.

(Click for larger version)

If you have a CPU not on that list, or one on that list and in a different RAM config (or overclocked), please leave me a comment or drop me an email – rob at pivotstream (dot) com.

Thanks!

2 Comments | Performance | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Campaign Analysis: Seasonally-Adjusted Measures

January 16, 2011

“We sure sold a lot of toilet paper around Halloween this year. That promotion we were running must’ve been REALLY effective!”

-Retail Manager Extraordinaire

Halloween Mischief Has Nothing to Do With It!

A Common Question: What Caused the Change?

Thought I’d take a break from the “Refresh on SharePoint” series and talk about an age-old problem… and how easy it is to address with PowerPivot. There isn’t much complexity behind it, and yet it’s still much more useful and re-useable than anything you can do with Excel alone.

Let’s say you work for the fictional AdventureWorks bicycle company. And for the past few weeks, you’ve been running a special promotion: “Buy a Bike, Get a Free Water Bottle.” And so far, it’s looking pretty good, as evidenced by the highlighted region on this chart:

OK, great. But hey, those free water bottles are expensive and cut into your profits. So if you want to be truly responsible, you need to make sure there aren’t other variables driving this bump instead.

(Oh, and for readers outside the US – are we the only country where teenagers engage in “toilet papering” other teens’ houses on Halloween?)

Getting a Baseline From Prior Years

The most common source of “noise” in sales like this is just normal seasonal fluctuation. Weeks 17 thru 21 of the year – are those normally a hot week for bike sales every year, whether there are promotions running or not?

What I am going to show is just one technique for taking that into account. There are many others, but this should get you thinking.

Step One: Baseline PivotTable

In the chart above, I’m just looking at bike sales in 2004. To get a baseline, I’m going to look at the three prior years:

OK, yes, the numbers are smaller in prior years – these are the dangers of using a sample data set like AdventureWorks. The inspiration for this blog post, however, is a real project I’m working on at Pivotstream… and for client confidentiality reasons I obviously can’t use that data set. So bear with me – the technique here is the important thing, not whether my sample data set is realistic.

Step Two: Create a Weekly Adjustment Factor

At the bottom of that pivot, the grand total represents the average sales per day over the entire 2001-2003 period:

The ratio of a given week’s sales per day vs. that grand total is an excellent indicator of how active that week of the year is on an ongoing basis.

So, let’s calculate it. And since I only intend to calculate these ratios as one-time fixed values, I’m just going to use normal Excel formulas to do it (as opposed to creating a measure for it):

Calculating the Seasonal Adjustment Ratio for Each Week
($F$57 is a Fixed Reference to the Grand Total Cell)

Fill that formula down for all weeks and we get:

Side Note: Building the Right Reference Pivot

Before I move on, I think it’s worth mentioning that it pays to think about the makeup of your reference pivot. What measure should you use? For instance, if the price you charge for your products fluctuates a lot for random reasons (EX: if you sell farm produce, which is subject to worldwide random price changes), then a pure dollar figure measure may not be a clean baseline. So my Qty per Day measure would be better in that situation.

Similarly, if your company acquired another company in June of 2003, and its product line merged into yours in that month, effectively doubling your sales, that will also skew your baseline higher for the second half of the year – my Qty per Day measure would NOT be immune to that problem. So in that case you may prefer to slice the baseline pivot to ONLY include the original company’s product line.

Point is… it takes business-specific expertise to define your cleanest baseline.

Importing Those Factors Into the PowerPivot Model

OK, now that I have those numbers, I want to get them into PowerPivot so I can apply them all over the place. A little Copy/Paste Values gets me this two column table in Excel:

From there, I can copy/paste into the PowerPivot window as a brand new table:

Relating that Table into the Rest of the Model

Next step is to create a relationship. In this particular model, I need to connect this new table to the existing Date table, since that’s the only place where I have a Week Number column in the model:

Now, I never intend to put fields from this new “Seasonal Adjustment” table on any of my pivots… which means that table will never get filtered by anything (slicers, row fields, etc.)

That’s a problem, because ultimately I will need filtering by week in order to make my measures work. So I need to get those values added into the Date table, because Date table fields WILL be on my pivots. A little =RELATED is in order:

Creating a “Seasonal Adjustment Divisor” Measure

Basically, we want a measure that, in the context of a specific week of the year, returns the corresponding Adjustment Factor from date table. Once we have the measure, we can then divide other measures by that measure to create “seasonally adjusted” versions.

Here’s the formula for the “divisor” measure:

Were you expecting something more complex? Yeah, I was too And to be fair, in my real-world example I am working on, it is a bit more complicated, but that has more to do with the data than basic approach.

To verify that it works, let’s compare it side by side with the original seasonal table I built in Excel:

Bingo. Matches perfectly. Of course, given that we’re just looking at single week numbers here, I didn’t have to use AVERAGE() – I could have used MIN(), MAX(), or SUM() and gotten the same results.

But AVERAGE() really does make a big difference at higher levels, like when I add Month to the pivot, here at right:

So the month of April, for instance, typically moves about 81% as much product as the average month. Neat.

And December, unsurprisingly, clocks in at 170% as much volume as the average month (hey, AdventureWorks sample db DID get that one right).

OK, that’s the last prep step. Now we get to the payoff – the creation of seasonally adjusted versions of our core measures.

Seasonally Adjusted Sales Measures

All that work above may seem like an investment, but trust me, it has taken me a lot longer to write up this blog post than it did to actually just build the model. About 10 minutes, tops.

No matter what, though, the next step is as quick and as painless as it gets. Just start dividing existing measures by the divisor measure!

For instance, at the beginning of this post I was using the “Qty per Day” measure. Let’s make the seasonally adjusted version:

(Oh, and if you are wondering how I made the text in the measure dialog bigger, well, it’s as simple as holding down the CTRL key and rolling the mouse wheel. Thanks to Alberto Ferrari for this great tip.)

Time to test it out. Let’s go back to the original chart and add this measure side by side with the original measure:

Original Measure Side by Side with Seasonally Adjusted Version

Hey, when adjusted for seasonal trends, our sales activity was actually even higher… but then again… so were all of the other weeks on the chart as well.

In fact, after adjusting for seasonality, the “bump” from the promo campaign doesn’t seem as large as it did before. Here’s the chart again, this time with just the seasonally adjusted measure:

Seasonally Adjusted Measure by Itself – Where’s the Lift?

So yeah, maybe a small bump. But not much. Is it worth it? Well, my next step would likely be to create a seasonally-adjusted measure of profit, since that will factor in the cost of the free water bottles. Again, this all comes down to the particular of your business, which is why having the tools directly in the hands of the domain experts is so critical.

Use it Everywhere!

Just wanted to emphasize this one more time: now that I have the [Seasonal Divisor] measure, I can quickly create seasonal-adjusted versions of as many measures as I need. One quick division and I’m done.

Also – if you ever want to adjust your definition of seasonality, all you have to do is change the formula for that one divisor measure, or the table that underlies it, and ALL of your seasonal-adjusted measures respond accordingly. You don’t get that kind of “one touch update” in traditional Excel.

Lastly, because you are creating measures rather than calculations that are fixed to the layout of a given sheet, all of your work is easily re-useable in many different contexts – analyze by individual products or by entire categories. By city or by country, no problem. At a daily or monthly level. You get the idea.

Other Techniques to Consider

Two more possibilities just to get the imagination going:

Define the Seasonal Adjustment table in SQL – why not move that whole process, and the underyling logic, into SQL? That way, you’ll get updated seasonality numbers over time, and you can probably get more sophisticated logic into the system. Sure, you’ll need help from your DBA, unless you ARE the DBA. But cooperation with your DBA team is a prime attribute of an optimal PowerPivot system.
Differentiate by other fields – hey, maybe Accessories have a different seasonality pattern than Bikes. And the Southwest region of the country might not show as much dropoff in January as the Northeast does. You can absolutely factor that into your Divisor measure. You could even try to do it 100% dynamically, in the measure, without pre-calcing an adjustment table first.

OK, that’s enough for now. Back to SharePoint refresh shortly.

1 Comment | DAX, Non-Football Examples | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Seasonal Adjusted Promo Campaign Lift Measures | Permalink
Posted by powerpivotpro

Data Refresh Twists/Turns Pt2 – Pivot Refresh

January 12, 2011

“People looked at me differently. They knew I was with somebody.

I didn’t have to wait in line at the bakery on Sunday morning for fresh pivots.”

-Henry “DAX” Hill

That Second Refresh Can Really Bite You

Back before the great distraction known as Donald Farmer hijacked this blog for a few days, I was talking about Scheduled Data Refresh. Specifically, I left off talking about how refresh is really TWO refreshes – PowerPivot and Excel. Here’s the illustration again:

OK, so the PowerPivot refresh service has one primary mission in life, and that is to refresh the PowerPivot model. Which brings us to…

Surprise #1: PowerPivot refresh service does NOT refresh the Pivots!

That’s right, it leaves the pivots in the workbook alone. So by default, those pivots (and/or cube formulas) still contain stale data! I’m 100% serious.

“But wait, Rob, you’re wrong!” you say. “I’ve tested this feature out, and I have NEVER seen stale data in my pivots when I view them in Excel Services!”

That’s right, you DON’T see stale data do you? That PowerPivot refresh service is one resourceful beast, and it’s playing a clever little trick…

Surprise #2: PowerPivot refresh service sets “Refresh on Open”

Have you ever seen this feature in Excel? It’s buried pretty deep:

Refresh on Open Setting in Excel

Yep, click Connections on the ribbon, select a connection (typically named “PowerPivot Data” in our case), then click Properties. On the resulting dialog you will see the checkbox “Refresh data when opening the file.”

By default that checkbox is NOT set. You can try this out to see what I’m talking about: take a PowerPivot workbook, open in Excel client and verify the checkbox is not set. Then upload to SharePoint, schedule a refresh. When complete, download the workbook and look at this setting again. It will be checked now.

Side Topic: How Does PowerPivot Refresh DO THAT???

I’m 99% certain that there is no API on the server for doing this. I don’t think Excel Services “helps” PowerPivot at all. I’m pretty sure PowerPivot modifies the workbook directly, via the file format. The Open XML File Format, to be precise… which happens to be what Office uses – XLSX, DOCX, PPTX – these are all Open XML files.

You can do pretty astounding things with that format, so if you are a developer type, I suggest playing around with it. I can tell you that OUR developers at Pivotstream had a heart attack when they saw that SDK

How This Can Bite You: Refresh Sometimes Takes Awhile

So far so good. PowerPivot refresh does NOT refresh the pivots, but it DOES set the refresh on open flag. So when you open it in Excel Services, the first thing Excel Services does, before it shows you any workbook content, is refresh the pivots. So you never see stale data. Ever.

In many cases, that’s the end of the story, everything is happy. But in other cases, it can be painful.

Remember the post I did awhile back on the differences between Update and Refresh? The overall theme of that blog post was basically: Refresh Can Be Very Slow.

Why is that? Short version: Refresh refreshes EVERY pivot in the workbook. AND refresh does more work than a normal pivot query.

So… if you’ve got a lot of pivots in your workbook, or a large data model, or complex measures, or perhaps a combination of those, refresh can take awhile.

Other Considerations

The worst part, of course, is that when a user opens a report in the browser, they have to wait awhile before they see any data. Sometimes that’s just an extra second or so, which is why you may not have noticed.

In other cases though, it can be as much as 1-2 minutes!

That happens for every user. In fact, if you open a browser and hit a report, wait through the refresh, and then close the browser, even if you immediately return to the report in a new browser window… you have to wait out the refresh again.

Imagine what that can do to the CPU(s) on the PowerPivot server(s). Of course, if you planned your hardware around data refresh as the peak load scenario, then you likely have enough CPU, but still… a lot of users doing this at once will only magnify their wait time.

“Give me the good news!”

First of all, I brought this to the attention of my former colleagues at Microsoft last time I was in Seattle. They’re working on solutions that don’t involve “Refresh on Open” but it’s not yet known when or how those solutions will be made available. Kudos to them for their responsiveness on the matter – I will keep you posted.

In the meantime, there are obvious things you can experiment with. Keeping the number of pivots smaller is one. Cutting back on cross-filtering in slicers is another. But fundamentally, if you have a reporting/modeling/analysis scenario that involves heavy lifting, you can’t exactly just remove those needs from your workbook.

At Pivotstream, we found ourselves in precisely that situation. And I am happy to say that we have beaten the problem, but the answer is complex – deserving of its own series of posts in fact. Plus, there are some things here that I am not yet ready to talk about publicly, muhaha… so let’s put that on the back burner for now.

Next up in this series: “thin” and “core” workbooks, aka, “hub and spoke.”

Click Here for Part Three >>

4 Comments | Data Import, SharePoint | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Scheduled Refresh | Permalink
Posted by powerpivotpro

Quick Poll: Do you run PowerPivot for Excel on Win XP?

January 11, 2011

View This Pollcustomer surveys

Leave a Comment » | Uncategorized | Permalink
Posted by powerpivotpro

The Tommy Chong Meme Spreads!

January 9, 2011

Just a quick humorous note. Another tech blogger recently posted about Donald’s big news.

Here’s a quick screenshot from that blog:

What’s funny? Well, that isn’t Donald. It’s Tommy Chong. Who is the villain behind this confusion? Someone horrible, is my guess.

Totally. Awesome.

1 Comment | Uncategorized | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Donald Farmer Qliktech Qlikview | Permalink
Posted by powerpivotpro

Donald Farmer Leaves MS for Qliktech / QlikView

January 6, 2011

“It’s as if millions of Microsoft employees all cried out at once and then suddenly were silent.”

-Obi Wan Kesoftie

“Double Yoo Tee Eff?… Oh yeah, that’s actually pretty cool!”

Huge news today: Donald Farmer, who has literally been the public face and personality of Microsoft Business Intelligence for many years now, is leaving Microsoft and going to what some people think is PowerPivot’s biggest competition: Qliktech, aka Qlikview.

Twitter was all… atwitter. My inbox turned over with messages. My phone rang. I got texts. The sun blacked out. Oh wait, that wasn’t today. But you get the idea.

People were curious what I thought about it. So was I, actually, so I thought I’d get my thoughts on paper. I mean, hey, we don’t often get events like this in the boring world of numbers. This isn’t pro wrestling where the personalities change teams all the time. No, for us this is Excellent Gossip Material! A chance for random thoughts and wild speculation!

Let’s dig in! Quick, before I actually talk to Donald and have a chance for facts to get in the way!

Oh, and fair warning: I’m also going to use this topic as an excuse to explore some topics, primarily the dynamics of working at Microsoft, that I’ve been noodling on for a long time. So this may end up being insanely interesting to five people and boring to everyone else. Hard to say, but it won’t just be about Donald or PowerPivot.

If all you want to know is what impact I think this will have on PowerPivot, scroll to the end.

But first things first: A Public Thank You

In my last couple years at MS, I had the pleasure of working very closely with Donald – sitting in the office next to his, and even reporting to him for awhile.

Donald, on the other hand, had the misfortune of working closely with me during one of the worst periods of my life I was going through a protracted divorce, including a custody battle that eventually displaced me from Redmond and landed me in Cleveland. My grandfather fought and lost a nasty battle with cancer. On top of it all, I was reaching the crescendo of my long-building frustration with being a cog in the Microsoft machine (a topic on which I could write many posts).

Let’s just say I wasn’t at my best. And Donald was just so immensely kind, patient, and supportive through it all. There were plenty of occasions where others would have lost their patience with me. But Donald went out of his way to help me, repeatedly. I flew home to see my grandfather, and Donald gave me one of his airline upgrades. If I had to be out of the office, Donald made sure I didn’t worry about the work impact of it.

And I can’t even begin to describe how helpful he was in the darkest days of “OK, how am I going to make a living in Cleveland???”

Everyone who is familiar with his public persona knows that he is incredibly sharp and witty, and that he seems very personable. Those that know him more closely can tell you that you don’t know the half of it. Thanks Donald.

So with that in mind, I can tell you that my first thought (once I got over the initial shock) was the realization of just how GOOD this could be for Donald. I’ll explain. And yes, I will speculate, because that’s more fun than just rehashing the facts now isn’t it?

Side Note #1 - What to do about PowerPivot Yoda?

I know this is gonna surprise you, but PowerPivot Yoda is actually a fiction. Yeah, that may be too many shocks for one day, but the truth is important at any price.

Yes, PowerPivot Yoda’s likeness is a morphed combination of the Jedi Master and the man himself.

Should we keep him? Do we… gasp… rename him as QlikView Yoda? Come up with a new likeness?

See, these are the important implications. If you have an opinion on what to do about PowerPivot Yoda, please leave a comment or drop me an email.

Why I Think This Will Be Very Good for Donald

Above, I made reference to my own struggle with being a “cog in the machine.” I don’t remember Donald ever complaining about similar thoughts, but if anything, his situation was even more extreme.

In my farewell mail to the PowerPivot team, I likened them to a collection of Olympic athletes. Literally, it’s an international team of all-stars, as if some magnetic force was drawing all of these Will Hunting types out of their homelands, concentrating them on one team in Redmond.

There are huge benefits to that of course. Absolutely magical things can happen with that kind of cast. The everyday personal interaction is nothing short of extraordinary. And for such exceptional individuals, the combined force of such a team naturally provides a sense of safety and job security.

The flipside, however, is that there’s only so much opportunity to go around. Someone like Donald is capable of a lot more than what Microsoft can ask him to do. There are quite literally too many (celebrity) chefs. Continuing the metaphor, you’ve got chefs on staff that could design and prepare entire menus, open and operate their own restaurants… and you have them making the soup.

Sooner or later, it’s time to try a broader scope.

I hope that QlikTech gets this. I’m sure they know that he will be great for PR. They need to let him do more than that. I’m pretty sure that Donald gets this, and wouldn’t be making the move unless a broader role was part of the deal.

How Much is the Former Face of MS BI Worth?

Another interesting aspect, and yes, I’m going to go there. It’s just basic economics really, even though it’s counterintuitive in many ways.

Donald was literally as close as you could get to being “the brand” for MS BI. That’s a pretty phenomenal thing – MS product lines don’t often have such a singular personification. But from Microsoft’s standpoint, it’s hard to put the right value on such a thing. His departure doesn’t… hurt as much as you might think. The folks designing, building, and testing the software are all still there, and MS software tends to either sell itself, or not sell at all (at least, relative to other brands).

So, the fair market value of someone like Donald is not something that gets fully realized at MS. But on the open market it’s another story isn’t it?

Again, I have NOT talked to Donald. I’m just doing the educated guess thing. And hoping the answer is “A LOT”

Side Note #2 – Next Time I Tease Donald…

…will it be construed as a competitive jab rather than just a humorous sign of respect? I sure hope not. I mean… I can’t retire the Tommy Chong comparison. I just can’t.

(For the full original photo gallery, click here.)

What Does this do to PowerPivot vs. Qliktech?

Short version: I think it helps Qliktech a lot. I don’t think it hurts Microsoft all that much.

Does that seem strange? It all comes back to the points above. Donald is likely leaving, at least in part, because he doesn’t have room to influence as much as he wants at MS. He is the face of the product line, but he is not the product line.

Thing is though… he could be the product line, at least in terms of strategic direction, detailed product reviews like BillG used to conduct, as well as the face of the product line.

So he’s just more valuable at Qliktech then he is at Microsoft. Not to mention… Qliktech is hot, fast-growing, etc… but it’s still a very small company relative to MS. A luminary like Donald will provide them a lot of broad exposure and credibility that they would otherwise lack. This is a very smart move for Qliktech.

Five Years From Now, Will it Have Made a Difference?

Personally, I think if you view PowerPivot and Qliktech as being locked in a duel to the death, then the answer is no… there will be no difference five years from now. If this truly is a head to head battle, Microsoft will win, and win convincingly.

I don’t think it should be viewed as a zero sum game like that, but for those who insist, I will tell you why MS would win such a contest. First of all, no disrespect intended to the Qlikview crew, but Microsoft is just a monster. The scale of what they can do – QV doesn’t just have to compete against PowerPivot, but also Reporting Services, Integration Services, SharePoint, Excel, Access Services… the entire stack.

One of those products, by the way, should have been listed in bold: EXCEL. Let’s be 100% clear: I’m not a PowerPivot advocate because I worked for MS, or even because I helped build it. I’m not MS-patriotic in that way at all. I worked on Bing for awhile, for instance, but I went back to Google as soon as I left the Bing team. It will be a long time before I consider a Windows phone – my iPhone and I are good friends.

No, I am a PowerPivot advocate, and my new company is an aggressive adopter of PowerPivot, because it extends the power of Excel into a whole new realm… and I already knew Excel. In that sense, I am just like 100M people worldwide – I am a programmer of the most widely-adopted BI language and platform in the world. When I train and consult with others about PowerPivot, I am teaching them how to extend their Excel skills in brand-new productive directions, but I am not teaching them something that’s completely new.

Full platform stack, critical mass of international Will Huntings, and the broad adoption of Excel – no one can overcome that. If you are in the way of that beast, it’s just a matter of time. I have a pet theory that the Qliktech IPO early this year was no coincidence. PowerPivot, out of the blue, presented a massive threat to their identity as “the” self-service BI tool. If you were one of the big equityholders at Qliktech in that situation, wouldn’t you want to “lock in” your big money? I sure would.

I like to think that now that the IPO is in the rearview mirror, Qliktech is coming out of that initial reaction. The BI market is far from a zero-sum game. Quality, sharp teams can find an endless number of ways to innovate. I’m excited to see if they go in some brand new directions. We may even use them at Pivotstream some day alongside PowerPivot and BISM.

I bet Donald already has some ideas

Good luck to you Donald. I wish you the very best, and hope to still run into you at events.

6 Comments | Observations, Trends, and Wild Speculation | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Donald Farmer Qliktech Qlikview | Permalink
Posted by powerpivotpro

Speaking at SQL Saturday 60 – Cleveland

January 4, 2011

Hey folks, if you are remotely near the so-called “Heartland” region of the US in February, you may consider dropping in on SQL Saturday 60. It’s Saturday, February 5 in Cleveland OH.

I will be presenting a session titled PowerPivot: BI & Massive Data Analysis for Humans – catchy huh?

I love these community events. It’s pretty crazy, when you think about it. Speakers literally fly in from all over the country to present. (Well, except for me, I’m gonna just drive down the road).

And these are some amazing people who travel around to these things. From Boston: Tom LaRock, aka SQLRockstar, aka “I have the best name in the tech business,” is speaking. From Toronto: Karen Lopez, aka DataChick, is speaking.

Those are just two that I know about. The official complete schedule is not yet released.

And it is free. That’s right, no admission. Just $10 for lunch, and I’m told repeatedly that “lunch is not pizza.”

If this is anywhere near as much fun as SharePoint Saturday was (scroll down that page past the pic of Jesse James), we’re in for a treat.

And apparently there are after parties and stuff. (No, I am not making that up). And yes, the parties are optional… but I hear good things about SQL Karaoke… (not making that up either).

Leave a Comment » | Hello World / Administrivia | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence SQL Saturday | Permalink
Posted by powerpivotpro

PowerPivot Scheduled Refresh – Twists & Turns, Pt 1

January 3, 2011

“Rico, we’ll need special tactical equipment. We’re going to face extreme peril. The private probably won’t survive.”

OK, with a new year it’s time to get serious on the blog. (Yeah, nothing says “serious” like cartoon penguins, but really, I *do* mean it). This is gonna kick off a series of inter-related posts that I’ve had in my head for a long time now. So buckle up, Rico.

Let’s say you’ve built a bunch of nifty PowerPivot workbooks. You’ve published them to SharePoint. And naturally, now you want to set things up so those workbooks automatically refresh, with the latest data, every night (or perhaps once a week).

As a recap, the simplest way to get started is to go to the PowerPivot Gallery view and click the highlighted button:

PowerPivot Gallery Schedule Refresh Button

Which leads to this page:

Refresh ASAP vs. Refresh Once

Most of the options in that scheduling page are quite straightforward. The only one that’s ever tripped me up is “Once.” “Once” does NOT mean “Now.” Instead, “Once” typically means refresh will occur tonight – either after business hours or after your specified earliest time.

If you want a refresh to occur now, you either need to specify a time (from today) that has already passed, OR don’t use “Once” at all.

Instead, you can schedule a refresh to occur Daily/Monthly/Weekly, and then check the “Also refresh as soon as possible” checkbox.

Personally, I think the lack of a “One time, right now” option is an oversight. As a workaround, I often found myself scheduling something to run once every 12 months just so I can check the “ASAP” checkbox.

Another Way to Turn “Once” into ASAP

At Pivotstream, we have found another way to schedule ASAP refreshes: we’ve changed the centralized definition of “business hours” to be so narrow that there are essentially no business hours at all. (Right now we have biz hours set to be 7-7:15 AM – I haven’t tried setting Start Time = End Time, but a 15 minute window is narrow enough for our purposes).

The downside of essentially disabling Business Hours, of course, is that it impacts everyone. That may not be a good option for your organization, as a refresh scheduled for “after business hours” will end up running sooner than expected. It works well for us at Pivotstream however.

For details on how to modify Business Hours, see Vidas’s item in the FAQ: http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=51

Behind the Scenes – the Basics

When a scheduled refresh does occur, a lot more happens than meets the eye. This rough diagram illustrates the steps behind the scenes:

(Click for Larger Version)

Notes on the diagram

I won’t repeat the descriptions on the diagram above, so spend a couple of minutes going over it ok?

With that done, I do want to call out a few points:

The timer service only checks about once per minute – so if you schedule an ASAP refresh but nothing happens immediately, just be patient, it will start shortly.
You can set different schedules per connection – I just discovered this recently, thanks to the watchful eyes of a training/consulting client of mine. George and company, you rock
The whole process will fail if the original file is checked out, or otherwise open for editing, during Step 6. So it’s extra important that you not leave your published PowerPivot workbooks checked out. I recommend editing locally on your desktop for this reason (and also because SharePoint is not terribly speedy for open/save of large files… and you should be saving often.)

Refresh is Actually TWO Refreshes

OK, one last piece of background in this post. When PowerPivot runs a refresh, it is refreshing the PowerPivot model – which corresponds to the contents of the PowerPivot window in Excel client.

In order for report consumers to see the latest and greatest data when they first navigate to it, however, the pivots in the Excel sheets themselves ALSO need to be refreshed. So there are TWO refreshes that need to happen – we’ll call the first one “PowerPivot Refresh” and the second one “Excel Refresh.”

PowerPivot Refresh and Excel Refresh

OK, that’s enough for part one. Next time I’ll explain how this two-stage refresh can complicate things in some situations.

Click Here for Part Two >>

In the meantime, if you are interested in further reading on scheduled refresh, you can check out the following articles:

http://msdn.microsoft.com/en-us/library/ff976569.aspx (Mariano’s excellent whitepaper)

http://powerpivotgeek.com/2010/09/08/a-peek-inside-getting-the-most-from-data-refresh/

http://blogs.msdn.com/b/powerpivot/archive/2009/11/20/powerpivot-data-refresh.aspx

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

M	T	W	T	F	S	S
« Dec				Feb »
					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	30
31

PowerPivotPro