Workarounds for “Canceled Due to Memory Pressure”

April 24, 2012

 
Ah, the dreaded dialog:

The Operation Has Been Cancelled Due to Memory Pressure

The Operation Has Been Cancelled Due to Memory Pressure

You can run into this error via a number of different routes, but one way or another, your computer ran out of memory while PowerPivot was trying to do something.

I’ll list some potential fixes here:

Read the rest of this entry »


PowerPivot V2 is Released!

March 8, 2012

 
Well the wait is over.  It is now released, for real.  No longer a beta or RC.  The real deal.

Download it here.

Other important links:

  1. My first glimpse at new features here.
  2. Important warnings about compatibility with V1.

Downgrading From PowerPivot v2 to v1

February 6, 2012

OK, let’s say you already installed v2, whether inadvertently or…  advertently.

And now you need to downgrade to v1, so that your workbooks function on your server, or can be opened by other people running v1 on their desktops.

So… how to do that?

The Obvious Thing Works for Most People

Simple:  just uninstall v2 (from Windows Control Panel) and then install v1.

But if you see this error when you try to build a new workbook, or open an old one, well, it’s not working out:

An operation that uses the database driver could not be completed - seen after downgrading PowerPivot v2 to v1.

What to do if you get that error?

1) Open Start Menu

2) Find “Command Prompt” – it is under Accessories

3) Right Click on Command Prompt, choose Run as Administrator

4) Once in the cmd window (looks like the old DOS window), type the following:

regsvr32 “C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll”

You may need to correct for your actual folder location – that’s the location on my machine.  The quotes ARE required.


Microsoft’s PowerPivot Download Links Updated

February 6, 2012

 
My former colleagues at Microsoft have responded to the issue I raised last week and have updated the PowerPivot.com download page to include links to both the “beta” of v2 as well as the latest, trusted version of v1.

Still not perfect though, as the v2 link remains first Sad smile

And since not everyone knows that “2012 RC0” means “test version that won’t work with your servers, or anyone using v1” and that “2008 R2” means “version 1, the one you need for production environments,” well, this could still use some serious clarification. 

So let me make it 100% clear which link is good for what purpose:

image

First Link is a Test Version of V2, Second Link is Released Version of V1

See Also

Downgrading from V2 to V1


Warning: PowerPivot.com download is v2 beta!

February 3, 2012

 
image

“Danger, Will Robinson!”

I’m seeing this pop up a lot now, both in our hosting business and on forums.

If you have gone to PowerPivot.com lately and downloaded PowerPivot, you downloaded a beta (a release candidate) of PowerPivot v2, and probably didn’t know it.

That’s not a big deal except for:

  1. The release candidate likely isn’t as stable as the most up-to-date release of PowerPivot  v1
  2. Workbooks created in v2 CANNOT be opened by v1, and that includes v1 server!
  3. There is no way to “downgrade” a v2 workbook to v1, so if you want to use a v2 workbook on v1, you have to start over

So be careful – v2 is awesome but using it unknowingly may have side effects you didn’t expect, at least until v2 is official released and the rest of your organization upgrades.

I’ve notified MS of this problem but so far they haven’t fixed it.  Both v1 AND v2 should be available for download, with v1 the default, and both clearly marked.

If you want to download v1, here is the link:

V1 PowerPivot for Excel – Download Here

See Also

Downgrading from V2 to V1

Microsoft Fixes the Download Page…  Sorta


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

image

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 ButterDo 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!

image

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

image

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.


Mini-Post 3/4: DAX.xml Update for Notepad++ from Colin Banfield

November 3, 2011

(Rob’s note:  Apologies to Colin, he put this up here in draft form weeks ago and even though I promised to flip it to live two weeks back, I forgot.  So Colin…  a thousand pardons.  This is awesome!)

From Colin:

For those of you that use DAX.xml with Notepad++, there is an update available at http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx. There is also an update of the DAX functions file. Although I created the new files shortly after downloading the Denali version of PowerPivot, I held off making the files available because, at the time,  I didn’t know whether any additional functions would be added in the final release. I have it on good authority that no additional functionality will be added on top of CTP3, so it’s unlikely that the files will change between now and PowerPivot V2 RTM. Note that the list of functions contains those that are not currently documented, including the very important KEEPFILTERS function, which you can read about in this superb post from Alberto Ferrari. Perhaps this function should have been called KEEPUSERSIGNORANT (okay, just kidding Smile).

If you missed the Notepad++ post, and might be interested in using this excellent tool to author your DAX measures, you can read the original post here.


Creating Accurate Percentile Measures in DAX – Part II

October 1, 2011

Intro from Rob:  today, Colin finishes in Percentile Measures topic from last week.

Note that 1) this technique requires the new release of PowerPivot that is currently still in beta (the Denali release)…

…and 2) I would rate this one a solid 3 or 4 on the spicy function scale.  Not one of the beginner techniques, in other words Smile

Guest Post by Colin Banfield

October 01, 2011

In Part I, I discussed my approach to creating percentile measures in DAX. In this part, we will add an extra dimension to our dataset and create a box & whisker PivotChart. The expanded dataset is shown in figure 1, with the source table on the left, and the resulting PowerPivot PivotTable on the right.

clip_image001

Figure 1 – Expanded dataset with Set column

In the expanded dataset, I have added a column called Set. The goal is to create percentile measures that are constrained to each set. Following the 4-step approach outlined in Part I, we start with the rank calculation. Recall that our rank formula in Part I was:

=RANKX(ALL(Data),[Sum of Value],,1)

This formula works when we are working with the entire table. However, with the addition of the Set column, we need to calculate values for the sets A, B, & C in a new filter context (i.e. one that excludes the ID column). We need RANKX to rank the values for each set independently. We employ the CALCULATETABLE function to achieve the desired result:

=RANKX(

                  CALCULATETABLE(
                                                       Data,
                                                      ALL(Data[ID])
                                                    ),
                 [Sum of Value],,1
               )

To show the ranking within each set more easily, I’ve sorted the source table by set and value, both in ascending order. Next, I created a calculated column that uses a lookup formula to get the rank measure from the PivotTable.

clip_image002

Figure 2 – Rank measure

With the inclusion of the Set column in the PivotTable, we must also modify other measures used in Part I.

Rank for 25th percentile ([25PctRank_INC])

From (Part I):

=CALCULATE(
                           (COUNTROWS(Data)-1)*25/100+1,
                           ALL(Data)
                         )

To:

=CALCULATE(
                           (COUNTROWS(Data)-1)*25/100+1,
                           ALL(Data[ID])
                         )

PercentileDown

From (Part I):

=MAXX(
                FILTER(
                             ALL(Data),
                             [Rank] = ROUNDDOWN([25PctRank_INC],0)
                           ),
              [Sum of Value]
            )

To:

=MAXX(
                FILTER(
                               ALL(Data[ID]),
                               [Rank] = ROUNDDOWN([25PctRank_INC],0)
                             ),
               [Sum of Value]
            )

PercentileUp

From (Part I):

=MAXX(
                  TOPN(
                               ROUNDUP([25PctRank_INC],0),
                              ALL(Data),
                             [Sum of Value],
                            1
                         ),
               [Sum of Value]
            )

To:

=MAXX(
                TOPN(
                             ROUNDUP([25PctRank_INC],0),
                             CALCULATETABLE(
                                                                   Data,
                                                                   ALL(Data[ID])
                                                                ),
                            [Sum of Value],
                            1
                          ),
               [Sum of Value]
            )

Or:

=CALCULATE(
                            MAXX(
                                          TOPN(
                                                       ROUNDUP([25PctRank_INC],0),
                                                       Data,
                                                       [Sum of Value],
                                                      1
                                                   ),
                                       [Sum of Value]
                                     ),
                         ALL(Data[ID])
                      )

The preceding alternative formulas are provided to illustrate how the same scalar result can be obtained using either CALCULATE or CALCULATETABLE. Similarly, we can use CALCULATETABLE instead of CALCULATE in the rank for the 25th percentile formula. I point this out because using CALCULATETABLE as an alternative to CALCULATE in applicable situations is rarely, if ever discussed.

25th, 50th, and 75th Percentiles

Recall the interpolation formula from Part I:

=[25thPercentileDown]+([25thPercentileUp]-[25thPercentileDown])*([25PctRank_INC]-ROUNDDOWN([25PctRank_INC],0))

After completing the measures for the 50th and 75th percentiles, our table is as shown in figure 3.

clip_image003

Figure 3 – Percentile measures, with comparisons to values returned by Excel’s PERCENTTILE.INC function

Creating a Box & Whisker PivotChart from Percentile Measures

It is difficult, if not impossible to analyze data directly in a table – whether or not the data is summarized. The purpose of a chart is (or should be) to provide us with insights into the data that we cannot derive by staring at the numbers. One such chart that takes advantage of percentile measures is the box & whisker plot (hereafter called a box plot). This chart is commonly used in areas such as statistical process control (SPC) and Six Sigma, but they can be quite useful for general business analysis. This chart is not native to Excel, so it’s not surprising that its use is not explored more often. The box plot visually shows the distribution of values in a dataset. The most common plots show the minimum value, maximum value, the range between the 25th and 75th percentile (known as the interquartile range), the median value and the mean (average value). The “box” portion is the interquartile range, and the two “whisker” parts are the range between the min and the 25th percentile, and the range between the 75th percentile and the max. The box plot is most effective when comparing the distribution of values across several data sets. A few examples of box plots for general business use include:

· Comparing the distribution of call hold times of agents over a period

· Comparing the distribution of price fluctuations across several products, or product parts

· Comparing the distribution of shipping weights for different service levels (e.g. normal, express, overnight). This data could provide useful intelligence for a shipping company

· Comparing the distribution of actual salaries across different salary scales. An excellent example is provided in this article here. The article explains how to interpret the plot and also illustrates a very important concept for making charts more meaningful i.e. adding additional context to a chart by providing reference information.

Basically, any related entities in your business that could benefit from analyzing a distribution of their values could benefit from a box plot.

The first step in creating the box plot PivotChart is to arrange the data appropriately. The actual construction of the plot is beyond the scope of this post, but the technique is explained in this tutorial by Excel charting guru, Jon Peltier. If you’re new to creating box plots in Excel, you should read the tutorial before continuing. Jon’s excellent charting tutorials have been around for a long time, and recently he has been updating them. In addition to his tutorials, Jon also has a few chart utilities for sale (and no, I don’t earn a commission for this plug).

The box plot PivotChart using our dataset is shown in figure 4

clip_image004

Figure 4 – Box plot PivotChart

Notes

1. I created a perspective called BoxPlot Data to contain only the measures required for the box plot

2. The measures are numbered to indicate the order that they must be added to the PivotTable

3. The box plot measure formulas are as follows:

[1-25th Percentile] = [25thPercentile_INC]

[2-50th Percentile] = [50thPercentile_INC]-[25thPercentile_INC]

[3-75th Percentile] = [75thPercentile_INC]-[50thPercentile_INC]

[4-Minimum] = [25thPercentile_INC]- MINX(ALL(Data[ID]),[Sum of Value])

[5-Maximum] = MAXX(ALL(Data[ID]),[Sum of Value])- [75thPercentile_INC]

[6-Mean] = AVERAGEX(ALL(Data[ID]),[Sum of Value])

4. I’ve created a chart template that can be used to create box plots. When creating a new plot, the dimension and measures should be added to the PivotTable before selecting the chart template from the Insert Chart dialog box.

5. After you create the chart from the template, the whiskers will not appear. This occurs because the whiskers are created using error bars, and external ranges are used to specify the data for the error bars. Unlike the rest of the chart, the template doesn’t know which ranges to use for error bars. You must add these manually, using the technique detailed in Jon’s tutorial.

6. The white median lines you see in figure 4 were created using a bit of formatting trickery. I used a white border for the bars. The outside borders don’t show because they are set against a white background.

Downloads

You can download a copy of the workbook & chart template from the following location:

http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx


Distinct Count in PowerPivot v2 – MUCH Faster

July 28, 2011

A quick update, as tonight I managed to sit down and experiment a bit with the V2 Beta for the first time in many days.

(OK, actually, I am standing up…  because I have a fantastic new treadmill desk and have walked nearly 8 miles today while working, but that’s a topic for another post).

I was working with one of our HostedPowerPivot clients today who was observing slower-than-expected slicer click performance in one of their reports.  And after some sleuthing, I found that they had a “distinct count” measure in their model, something like this:

[Measure] = COUNTROWS(DISTINCT(Table[Column]))

Now, that’s a pretty useful measure in many situations, like “does product X sell every day?”  Simply taking a count of the date column doesn’t cut it, because if it sells twice on one day and not at all the next day, the count is still 2, and you want it to be 1.

Trouble is, in PowerPivot v1, that operation can be slow.  I won’t bore you with all the details, because frankly, I don’t know all of them, heh heh.  But I do know that PowerPivot v1 does end up creating all kinds of temporary new tables in memory behind the scenes when it is evaluating the measure.

Anyway, I’d heard a long time ago that PowerPivot V2 was going to make distinct counts a lot faster, so I thought I’d try it out.

The Test Case

First I needed to create a test case that was challenging.  I don’t want to compare “fast query vs. fast query” because that often gets skewed by fixed overhead.  Much better to test a “worst case scenario.”

So, I took a 15 million row table, and added a calculated column to it.  Calculated columns in PowerPivot are much less compressed than imported columns, and are therefore more challenging for the measure engine to scan through, looking for duplicates etc. which is required for a distinct count.

And then, to make matters worse, I just used the RAND() function so that there are many unique values:

image

Cutting to the Chase:  The Results

A distinct count measure in PowerPivot v1 took 35 seconds to complete in my test pivot.  This was a big part of why our client was seeing slow perf today.

That same measure, in that same pivot, took less than a second in the V2 beta.

Wow.  This is gonna be nice.

One Last Note:  Two Ways to Write the Formula

PowerPivot v1 did not have a dedicated function for distinct count, you had to do the countrows of distinct thing like what I showed above.

But in PowerPivot v2, there IS a DISTINCTCOUNT() function.  So you can write your distinct count measures in two ways:

[Measure] = COUNTROWS(DISTINCT(Table[Column]))

[Measure] = DISTINCTCOUNT(Table[Column])

That new function is nice, makes for a more readable formula.  BUT…  the performance is still the same.  Either way I wrote it, the measure was sub-second fast.  Very nice.


Guest Post: TOPN in PowerPivot V2

July 20, 2011

Well folks I haven’t had much time to play with the V2 Beta (aka CTP3) yet.  A lot going on over the past week:

  1. Authoring a brand new suite of models and reports for our Duane Reade DR-Direct solution
  2. A nasty crashing bug in PowerPivot for SharePoint that we’ve now learned to catch and repair
  3. I submitted a PowerPivot article to CIMA Insight magazine (for August)
  4. Running refresh tests with PowerPivot servers in Chicago pulling data from SQL in Dallas (over Internet VPN – more on this later) for the HostedPowerPivot offering

So in the meantime, David Hager has a short article to share on the new TOPN function.  I haven’t had a chance to try it yet, and I’m a little surprised that it doesn’t have special handling for ties (as he’s discovered).

David Hager on TOPN

Using the New PowerPivot Version 2 TOPN Function: Up, Down and Between

One of the new DAX functions included in the Denali PowerPivot CTP3 release is the TOPN function. This function returns a table which can be aggregated for the top n values. Prior to the release of this function, workarounds were available to achieve the same results, mainly through DAX ranking formulas (and Denali PowerPivot now has native functions for ranking too!). So, a typical DAX formula which uses the TOPN function is:

=SUMX(TOPN(N,SalesTable,SalesTable[SalesTotal]),SalesTable[SalesTotal])

where SalesTable is the table and SalesTotal is the column in that table containing the top n results to be analyzed.

Well, that was easy enough. However, there is a systemic problem here and it’s mainly data-related. In order to understand why the TOPN function don’t work the way you might expect, read the following that comes from the Denali PowerPivot CTP3 help documentation on TOPN.

“If there is a tie, in order_by values, at the Nth row of the table, then all tied rows are returned. Then, when there are ties at the Nth row the function might return more than n rows.”

In English, that means if there are duplicate values in your data, the TOPN function will return the WRONG answer if the Nth value is adjacent to duplicates. In order to get a meaningful result then, the data must be converted so that each data point has a unique value. The question is, how do you do that and retain the integrity of the data? An Excel trick can be used here as a workaround. This method requires that each value be incremented by a very small but unique value. The best implementation of this comes in the form of a calculated column with the following formula

SalesTable[UniqueIncremValue] = 
IF(RANDBETWEEN(-1,1)=-1,-RAND()*2,RAND())*0.000001

The goal of this formula is to create a column of unique numbers add up basically to zero. There is a balance that is required in this formula between affecting data and insuring that all values in the dataset are unique. The RAND function returns a 15-place decimal, which is the limit in Excel. However, if all of the decimal places were used, the value of the number could be as high as 1 or as low as -2. By multiplying the random numbers by a very small number, the numbers will not interfere with the individual values if they themselves are not small numbers. However, if the multiplier is made too small, not all of the random numbers may be unique depending on the size of the dataset. So, if you are going to use this workaround, you need to test [UniqueIncremValue] to see if all of the values are unique. The best way to do this is to compare its distinct count with the distinct count of a primary key column in the table. If they are equal, TOPN will work for any value of N.

Now, the original formula will work fine if the new calculated column is used in place of SalesTable[SalesTotal].

SalesTable[SalesTotalMod]=
SalesTable[SalesTotal]+ SalesTable[UniqueIncremValue]

It should also be noted that the TOPN function can used in a DAX like the one shown below to afford a BOTTONM result. Note that this formula takes advantage of the new DISTICTCOUNT function.

=SUMX(SalesTable,[SalesTotalMod])
-SUMX(
     TOPN(
         DISTINCTCOUNT(SalesTable[SalesTotalMod])- N,
         SalesTable,
         SalesTable[SalesTotalMod]
         ),
     SalesTable[SalesTotalMod]
    
)

I think you can see that the TOPN function is a valuable new item for your DAX toolbox. Have fun!


PowerPivot V2 CTP3!

July 12, 2011

image

“Tell ya what Mom…  just lock me in here tonight.  I will be fine.”

A lot of you are already on the scent, but just in case…

WAHOOOO!

CTP3 = “Public Beta”

What the heck is a CTP, you ask?  Well it’s a “Community Technology Preview.”  You know…  what we used to call a Beta.  Calling it a CTP is a much more grown-up approach.  But it’s silly IMO.  A Beta release of PowerPivot V2 is EXCITING!  “Beta” was, and always will be, a more exciting word.  Do you think the next release of Halo will have a Beta or a Community Entertainment Preview?

But I digress.  These are the important things you need to know:

  1. The public beta of PowerPivot V2 (aka “Denali”) is available!
  2. It contains MANY exciting features (preview below)
  3. Everyone can download it, but I don’t recommend putting it on your “production” computer
  4. Workbooks created in V2 cannot be edited by PowerPivot V1 (V2 “upgrades” V1 workbooks when you open them, but it’s a one-way trip)
  5. This version is buggy enough that you won’t want to use it for everyday work (technically you aren’t really allowed to either), but it does tantalize in a BIG way and already can help you out with V1 (see below)
  6. The final version won’t be available for several months, exact date unknown
  7. Download it :  http://www.microsoft.com/download/en/details.aspx?id=28150 (UPDATED link to point to the newer RC0, not CTP3.)

Take the Pepsi Challenge

I’m gonna show you two pictures and you tell me which one you prefer.  Ready?

OK – would you rather “eyeball” a PowerPivot model, trying to see which tables relate to which, using this:

PowerPivot Manage Relationships Dialog

imageContinuing the candy theme, that feature is kinda like candy corn – dull and not very inviting.  What’s that you say?  You prefer something more lively?

I agree.

So… maybe, just maybe…  you would prefer something like this little number below…

 

PowerPivot V2 "Denali" - Diagram View

Diagram View – Click for Bigger Version

 

image That’s more like it.  Let’s call this feature a Reese’s.

I debated, say, Sour Skittles.  Or Spree.  Or Fun Dip.

Yeah, Fun Dip.  There’s some nostalgia.  And the color scheme in this picture fits, too.

 

Make Your Own Posters for Free!

This feature is:  Fan.  Freaking.  Tastic.  In fact, this is useful ALREADY.  Why not make a copy of an existing model, load it in V2, and then use V2 to generate this picture?  Print it out, put it on your wall, reference it as you work on the model in V1!

In fact I just sent this to a HostedPowerPivot client today to help them visualize what we’ve cooperatively built in a recent engagement:

clip_image001

Some PowerPivot Models are More In-Depth Than Others (Again Click for Larger)

In the “looking a gift horse in the mouth” department, there are some warts with this one for sure.  It took me 15 minutes to get the tables to all fit on a single page.  The fonts are small.  And I wish there was a way to toggle between “show details for each table” and “just show a big fat table name that I can read, let me expand for details.”  Because that would fit on a screen much better.

Oh well.  I still love it, even as I immediately crave the next round of must-have improvements.  Such is life.

Another Killer Feature:  Sort-By Column!

See if you can spot the moment of awesome in this picture:

PowerPivot Sorted Labels in a Slicer!

Yeah…  that’s right.  The days of the week in the slicer are sorted CORRECTLY.  There was NO way to control sort order of labels/values in a slicer in V1 without prepending a number and doing something like “1 – Sunday” as your labels.  Blech.

In V2, no problem, here’s the feature you use:

image

That one doesn’t require much explanation, I think.

More to come…

There are a few other goodies that I want to share but I have a bunch of real work to do today for Pivotstream – we are developing a new suite of reports and models for our retail suite after an excellent few days of strategizing and planning.  I’m excited to share that as well – I’ve reached the point where I need about three of me to do everything I want to do. That’s a good sign of course, but boy is it tantalizing to leave so much on the table every day.

Stay tuned.  And in the meantime, if you have an extra computer, don’t wait on me.  Go download it!  In fact…

Send me your observations!

If you are messing around with the Beta (yes, BETA damnit), send me your observations.  Things you like.  Things you don’t.  Things that surprised you.  Whatever.

I am Rob.  At a place called PowerPivotPro.  Dot com.  If you get what I’m sayin.