Webinar Wednesday on Cloud-Based Dashboards

December 4, 2012

image

Today’s “post” is actually going to be a series of small updates/announcements.

Connection Cloud is one of our new partners at Pivotstream.  They do something amazing:  they make your cloud data sources like SalesForce and Facebook look like normal databases.

Which means PowerPivot can connect to those sources and pull data just as easily as you pull data from a database like SQL or Access:

Read the rest of this entry »


Free Site Signup: Wave One Open

November 7, 2012

 

***UPDATE – FULL:  Wave one filled up fast, no need for 48 hours.  We actually went over 100 during the night and hit about 130.  We’re letting all of those in, but are taking down the signup form now.

Stay tuned for news about Wave Two Smile

image

Excel Pros Don’t Always Dress Like This of Course, But It’s Still a Great Picture

Following up from yesterday’s post, here’s the link for the free site signup:

<Link removed, wave one full>

Bring it on Smile

Minimal Handholding is Intentional

One of our goals here is to see how understandable our whole system is, so we’re trying to keep “human handholding” to a minimum and see where/if people stumble.

Go watch yesterday’s video if you haven’t already.

Then there are support forums available to you once you have your site.  See if you can find them.  I’m intentionally NOT telling you HOW to find them Smile

That said, if you have outright “I can’t go any farther and am completely stumped” problems and can’t get to the forums for some reason, hit me up with a mail.  I’m rob.  At a place called powerpivotpro.  Dot com.


Cloud PowerPivot to Launch Free Offering

November 6, 2012

 

***UPDATE – FULL: Wave one filled up fast, no need for 48 hours. We actually went over 100 during the night and hit about 130. We’re letting all of those in, but are taking down the signup form now.

Stay tuned for news about Wave Two Smile

image

If Ebenezer Scrooge Were Alive Today, He’d Use PowerPivot.  And He’d Love This Post.
(Believe it or not this is an original image I commissioned ten years ago)

Taking my “High Priest” Role VERY Seriously

There are a few themes that I just keep hammering on, month after month.  Most of those revolve around the stunning new future opening up for Excel Pros.  I believe every thing I say about that stuff.  It is REAL.

One of the biggest and most transformational changes is this:  taking your workbooks to the web.  Workbooks were being emailed around back when Roxette topped the worldwide music charts.  PowerPivot workbooks published to a server are a very, VERY different experience, one that inspires MUCH more respect from the person consuming them.

Short Version: Free Forever for Lighter Workloads

This week, Pivotstream is launching something that I’ve been dreaming about for a long time: a way for you (yes YOU, dear reader) to harness the power of PowerPivot server (publishing workbooks as interactive web apps)… for free.

Not a trial. This is more of a Dropbox-style model where lighter usage is completely free, and you only pay if you want more capacity.  I want to remove any barriers I can so that you can experience what I’m talking about (without bankrupting my company of course), and I think we’ve figured out how to do that.  But before I get into details, let me show you something.

Just Add Upload

Thanks to a recent focus group I recruited here on the blog, I learned that many people expect there to be some sort of intensive conversion process – it seems like you would need to put a lot of work into a workbook before it becomes an interactive web application like the ones on our demo site.

So I recorded a video showing that it’s much, MUCH simpler than that.  Just upload Smile

Upload and Share – Short Video Illustrating an Even Shorter Process Smile
(I recommend watching in HD and Fullscreen)

Benefits to Excel Pro

I didn’t have time to cover this in such a short video, so here’s a quick table comparing the old way to the new way, through the eyes of you, the Excel Pro:

Read the rest of this entry »


Announcing Pivotstream Ltd: Expanding to Europe, Led by David Churchward!

November 1, 2012

image

Pivotstream Jumps Over the Pond, Opening Office and Data Center in Europe

Happy to Finally Share the Secret!

Boy, I have been dying to share this, but had to wait until it was official:  David Churchward has joined Pivotstream and will be running our operations in Europe.

At a high level, this means a few things:

  1. We will be opening a data center in Europe – European customers who have desired a Cloud PowerPivot site (or one of our Insight as a Service solutions) have often needed the data to physically remain in the European Union (for legal and privacy reasons).  This will no longer be an obstacle.
  2. We will be doing more work in the finance, accounting, and CRM spaces.  David is a MACHINE when it comes to this stuff, as evidenced by his posts on this blog.  This expands our Insight as a Service capabilities in this space, but also gives us the ability to “seed” the broader PowerPivot world with templates – more on this later.
  3. I’ll be able to bug David a lot more often and not feel guilty about distracting him from his “real” job – part of his job now is to be pestered by me!  (Hmm, I DO suppose that works both ways…)

image

Pivotstream Ltd. – Tentative Logo is Nothing Fancy, All Business

Reinforcement of the Excel Revolution

Regular readers of this blog are familiar with me getting on a soapbox and proclaiming that the future of Excel, and Excel Pros, is exceedingly bright.  PowerPivot changes everything, including our careers.

The fact that I’ve spent the past three years making my living completely off of PowerPivot is testament to that.  It helps the credibility of my message, a lot, that I can say this:  I don’t just talk the story of this revolution, I live it.  I work it.  And it pays the bills.

But now, here’s another public confirmation of it:  It’s a major investment, opening a data center and offices in Europe.  It’s not a step we would take lightly.  You should take this as further confirmation that the world is changing in your favor.  And similarly, David would not have left his Director job at Azzurri to do this otherwise.

Speaking of which…

Read the rest of this entry »


Early Tuesday Post: The “Hero Report”

August 6, 2012

 

image

A Post That Just Refused to Wait for Tuesday

I’m Looking for a Few Good Pivot Pros

If you’re an Excel Pro (which I define simply as “one who creates PivotTables”), and you’ve been using PowerPivot, I want your help for a semi-radical side project I’ve been thinking about.

I want to ask you a few questions, either in email or on the phone.  That’s it – basically I need a focus group off of which I can bounce a few ideas before making those ideas public.

If you’re selected to participate, there WILL be compensation.  That will either be a $50 gift card, some free Pivotstream services, and/or a direct line to me for some PowerPivot questions.

So if you’re interested, please drop me a note at the following address:

***UPDATE – the survey program is closed to new participants at this time, the response was overwhelming!

What’s a Hero Report?

I love this term, but I didn’t invent it.  Credit goes to John, one of my colleagues at Pivotstream.  He talks to a lot of Excel Pros every day, even more than I do, and he tells stories like the following all the time:

Read the rest of this entry »


PowerPivot V2 – To The Cloud!

August 2, 2012

Power View Report From PowerPivot V2 (2012) Workbook Running on Pivotstream Cloud Platform

My First Real Experiment with Power View – Built From a PowerPivot V2 XLSX!
(Running on our New V2 Cloud PowerPivot Platform)

Lots of Work Pays Off

The #1 question I hear every day is “when will Pivotstream offer support for V2 on their cloud platform?”  And in fact, that’s maybe the #1 question that I ask of the team every day, too.

There’s a lot of demand for it, given all the new bells and whistles in the V2 release.  But we’re no longer a fledgling little operation – we can’t just upgrade everyone overnight.  Actually, we could, but that would be irresponsible – we have to make sure none of our customers get burned in the process, and huge software releases like V2 have a tendency to be…  finicky.  If we upgrade everyone and things start breaking, saying “it’s Microsoft’s fault” is not an answer – we have to hold ourselves to a higher bar.

So our V2 cloud platform is a completely separate and parallel investment – new hardware, new domains, new base URL’s, etc.  A lot of time and money, in other words.  So if you’ve been wondering “what the heck are they waiting for,” now you know.

In Private Beta Now, Sign Up for the Public Beta

Read the rest of this entry »


Insight as a Service – Your Next Career?

June 19, 2012

McKinsey Report on How "Ripe" Various Industries are for IaaS (I disagree about some of these)

Is Your Brain More Valuable Than You Know?

A Hot New Industry that Could (Should!) Be Dominated By Excel Pros

I read an interesting article the other day about a new type of Cloud Business Intelligence dubbed “Insight as a Service” (IaaS).

If you’re a BI Pro I recommend you read it carefully. If you’re an Excel Pro I recommend you give it a quick skim just to get the flavor of it.  It’s a good article.  Well-written, thoughtful, and imbued with the author’s experience (and as a venture capitalist, his experience reflects a broad cross-section of industries and firms).

But ironically, I believe that IaaS is MUCH more relevant to the Excel Pro than the BI Pro.  Most Excel pros are already in the IaaS business, but it just isn’t called that.

Read the rest of this entry »


“Drill Across” in PowerPivot – Live Demo

May 24, 2012

Hyperlinks in a Pivot

“I’m telling you there are monkey-fighting hyperlinks in this Monday-to-Friday pivot!”

(Seriously this is how they cleaned up his line for TV, with “monkey-fighting” and “Monday to Friday”)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

 

 

 

 

Retailer Competitive Overlap Application – New and Improved Live Demo

PowerPivot Retailer Competitive Overlap Application With Drill Across

Revamped/Simplified “Retailer Competitive Overlap” Application
(Note that the Row Labels Area of the Pivot Contains Hyperlinks!)

Clicking an Item to Get More Detail

The retailer overlap application is one that I’ve covered before, in my post announcing our live PowerPivot demo site, but I’ve recently spent some time improving it based on customer feedback and requests.

Specifically, our retail customers have asked the following:  “It’s great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?”

 

image

But WHICH Stores?  I Want to See the Addresses!

Hyperlinks in a Pivot!?

Let’s zoom in on the row area of the pivot pictured above:

image

Read the rest of this entry »


Cloud PowerPivot: Free Trials for the Public

April 26, 2012

image

image

Actual Web Browser Screenshots of PowerPivot Sites:
Example of Homepage (top) and Report Page (bottom)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

Want to see (and share) your workbooks on the web?

For a few months now we’ve been running free 30-day free trials where you can:

  1. Upload workbooks to the Pivotstream cloud
  2. Interact with them in the browser
  3. Securely share them with colleagues, even if they don’t have PowerPivot or Excel 2010 installed

Read the rest of this entry »


Auto-Modify Every Pivot in a Workbook

March 6, 2012

 
image

It’s *ALMOST* That Easy Smile

It’s been awhile since I’ve talked about Macros (also known as VBA).  I think it’s overdue.

Macros are nothing short of amazing.  We couldn’t live without them at Pivotstream.

Wait Rob, Macros Don’t Run on the Server!

That’s right, they don’t.  And everything we do at Pivotstream eventually lands on the server.  So why do we use macros?

Simple:  we use macros to more efficiently create and modify our workbooks.  Macros are a “design time” tool for us, not a “run time” tool.

And they have saved us probably decades of work.  I’m not exaggerating.

An Example:  The “Change Every Pivot” Macro

Here’s a pretty simple macro that runs through every PivotTable on every visible worksheet and does whatever you want to each pivot:

Sub ModifyAllPivots()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
   
    For Each Sheet In ActiveWorkbook.Worksheets
        If Sheet.Visible = xlSheetVisible Then
            For Each Pivot In Sheet.PivotTables
                AutoPadPivot Sheet.Name, Pivot.Name, 10
                GrandTotalsBottomOnly Sheet.Name, Pivot.Name

            Next
        End If
    Next

End Sub

I emphasized the “payload” of the macro – for each pivot the macro finds, it “pads” the columns of the pivot to be wide enough, and sets the pivot to display grand totals only on the bottom of the pivot (and never on the right).

Those two lines are macros that I also wrote, and I will include them below.

Auto Pad Pivot Columns Macro

At Pivotstream, we tend to ALWAYS turn off the “auto-fit columns on update” setting on our pivots:

Making Pivots NOT Change Column Widths On Slicer Clicks Etc

Making Pivots NOT Change Column Widths On Slicer Clicks Etc

This yields a much more pleasant “application-like” result – clicking a slicer never results in things jumping around.  I highly recommend it.

But this DOES lead to a problem.  If your numbers suddenly grow by a digit in the future, you can get something like this:

One of the Drawbacks of Turning off Autofit Column Width

One of the Drawbacks of Turning off Autofit Column Width

To account for this, we have a macro that runs through every pivot in the workbook and “pads” the column width by a percentage:

Sub AutoPadPivot(sSheet As String, sPivot As String, iPct As Integer)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
    Dim r As Range
    Dim rCurr As Range
    Dim iCol As Integer
  
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    Set r = oPivot.DataBodyRange.Rows(1)
    For Each rCurr In r.Cells
        iCol = rCurr.Column
       
        oSheet.Columns(iCol).EntireColumn.AutoFit
        oSheet.Columns(iCol).ColumnWidth = oSheet.Columns(iCol).ColumnWidth * (1 + (iPct / 100))
    Next
   
   
End Sub

At the beginning of this post, you saw an example where I called this macro to pad each column by 10 percent.  But I can pad by 5, 15, whatever I want.  And you can easily imagine a version that pads by an absolute amount rather than a percentage.

Grand Totals Bottom Only Macro

This one is more self-explanatory:

Sub GrandTotalsBottomOnly(sSheet As String, sPivot As String)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    oSheet.PivotTables(sPivot).RowGrand = False
End Sub

Want one more?  OK.  You talked me into it.  We have dozens, many of which are quite ambitious.  Simpler macros make for better blog posts though, so…

Create Page Filter For Each Slicer On Pivot Macro

Sub CreatePageFilterForEachSlicerOnPivot(sSheet As String, sPivot As String)
    Dim oSlicer As Slicer
    Dim oSlicerCache As SlicerCache
    Dim sField As String
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Sheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    For Each oSlicer In oPivot.Slicers
        sField = oSlicer.SlicerCache.SourceName
        oPivot.CubeFields(sField).Orientation = xlPageField
    Next
   
End Sub

Why would I want to create a page filter for very slicer on the pivot?  Well, primarily so you can “harvest” the slicer selections in formulas.  But there are other reasons you might do this as well, which I will likely cover in the future.

Learn to Record Macros Folks!

What’s that, you say?  You never need to pad pivots, switch their grand total settings, or add page filters for every slicer?  Not satisfied eh?  Well, you can make your own!

If you have never recorded a macro, seriously, it’s SOOOO easy.  You should try it. 

First you will need to enable the Developer ribbon by going to File|Options|Customize the Ribbon.

Once you’ve done that, here’s how you get started:

Going Into Macro Recording Mode

Going Into Macro Recording Mode
(Note That I Named the Macro Based On What I Am Going to Do Next)

Now I do something to the pivot.  In this case, I switch my Pivot to one of the “Medium Green” Styles:

image

Changing My Pivot to a Different Style
(While the Macro Recorder Watches My Every Move)

Now I can stop recording:

Stop Recording the Macro

Stop Recording the Macro

Inspecting Your Freshly-Recorded Macro

Inspecting Your Freshly-Recorded Macro

Just click the Macros button, select your macro, and then click Edit

And here it is:

Sub ChangeToGreenStyle()

‘ ChangeToGreenStyle Macro


    ActiveSheet.PivotTables(“PivotTable3″).TableStyle2 = “PivotStyleMedium4″
End Sub

There’s really only one line in the macro that DOES anything, so I highlighted it.

Note that it’s “tied” to a pivot named “PivotTable3.”  Not all of your pivots will be named that of course.  And it only works on the ACTIVE sheet.  So it won’t work if you try to loop through using the ModifyAllPivots macro.

So, you can modify it to look like the other macros I showed above (GrandTotalsBottomOnly, etc).  Change the macro to be:

Sub ChangeToGreenStyle (sSheet As String, sPivot As String)

   Dim oPivot As PivotTable
   Dim oSheet As Worksheet

   Set oSheet = ActiveWorkbook.Worksheets(sSheet)
   Set oPivot = oSheet.PivotTables(sPivot)


   oSheet.PivotTables(sPivot).TableStyle2 = “PivotStyleMedium4″

End Sub

The stuff in grey is just copied from the other macros.  The red is a replacement (also copied from the other macros) for the part the recorder set to Active Sheet and PivotTable3.

That macro can now be called from within ModifyEveryPivot just like the others were:


      For Each Pivot In Sheet.PivotTables
         AutoPadPivot Sheet.Name, Pivot.Name, 10
         GrandTotalsBottomOnly Sheet.Name, Pivot.Name

         ChangeToGreenStyle Sheet.Name, Pivot.Name

      Next

Off you go Smile


My Experiences with Hosted PowerPivot, Part One

February 21, 2012

Guest post by David Churchward

Pivotstream Dashboard Application

One of Azzurri Communications Ltd’s PowerPivot
Applications Running in the Browser

Six Months Ago:  The “Lightbulb” Moment

Almost exactly six months ago, after being a long time reader of this blog, I emailed Rob and asked him a question regarding something that I just couldn’t get my head around in DAX – Banding!  He kindly responded, and his answer solved my problem, so I asked him if I could return the favour somehow.  He asked if I’d mind writing it up as a guest post, which I did.

Now, double-digit guest posts later, I’m amazed at how far I’ve come in short order.  Something definitely “clicked” for me that day, and my grasp of PowerPivot’s capabilities expanded rapidly.  It felt like that moment that I imagine Pianists reach where they can suddenly play by ear, because whilst I could conquer most things in DAX, it didn’t seem to quite “flow” – until that day!

Up until that point I had viewed PowerPivot as a “private” tool – something that was useful for me in my work, a supplement to other tools and methods.  But starting six months ago, I started to understand that PowerPivot could, and SHOULD, be used to improve or replace most of our existing Business Intelligence and Analytics tools.

Step One:  Azzurri Deploys its First “On Premise” SharePoint Server

At Azzurri, I am fortunate to enjoy two critical flavours of support:

  1. Our executive team is open-minded to progressive ways of doing things (so long as there is a solid value proposition).
  2. My tech team is a crack outfit who will bring me the moon if I ask for it, but tend to make reasonable alternative suggestions such as building data warehouses.

In other words, Azzurri is the perfect sort of place to deploy PowerPivot for SharePoint, bordering on a textbook example.  There aren’t many companies of Azzurri’s size where I could explain the benefits of a PowerPivot server, win people over, and have a server deployed two weeks later.  But that is precisely what happened Smile

Step Two:  Start Emailing Rob Again Smile

We didn’t just deploy the server, we immediately began USING it for serious work.  And that led to questions – questions about performance.  Questions about hardware.  Questions about customisation.  Questions about refresh.  Questions about “core and thin” workbooks.

Rob and I had a friendly correspondence going at that point, so I started sending those questions over.  I even looped him into email chains with our tech team, and we talked through a number of issues and optimisations.

Step Three:  Try Out Hosted PowerPivot in Parallel

Everything I do in Systems Development, especially with my Finance background, is about Cost V Benefit, ROI, IRR and payback.  With this in mind, I started wondering whether it made sense for us to develop PowerPivot for SharePoint expertise of our own.

We had originally decided to go with our own SharePoint deployment because we had the required licences and a particularly clever team who I had every faith could deliver.  This seemed obvious as SharePoint was already in operation at Azzurri.  My initial view was that it must be relatively straight forward to bring all of the BI tools into the equation.

Two weeks into the process, however, I was already seeing that things might not be as straight forward as I had first hoped.  Performance was the first major barrier that I hit and I couldn’t be entirely sure what kind of investment in hardware might be required to alleviate this.  Out of nowhere, PowerPivot gallery started playing games which turned out to be an IE9 issue and then I was introduced to Kerberos which, it turns out, isn’t a breakfast cereal that I was yet to encounter!

I knew about the Pivotstream Hosted Solution of course, and I still wasn’t ready to commit to hosting, but I decided that running a trial in parallel made a lot of sense, especially since I was particularly aware that my tech team needed to be doing other things.

I’m very glad that we decided on a trial, because step four was to switch over full-time.

Goodbye “Do it Yourself”, Hello SaaS

The journey I’ve been on as a customer of Pivotstream has validated for me that the SaaS model together with the capability of PowerPivot makes for a more compelling business solution for reporting and BI than any alternative that I can find.

I’ve been particularly conscious of making sure that my tech team spend their time where they can really drive business value – building Data Warehouses, ETL and efficient business processes.  It was clear that time spent developing SharePoint Server was time not spent adding value elsewhere.  There’s no doubt in my mind that they would have delivered, but I knew that they could deliver more value elsewhere to more than offset any cost of hosting.

Summary of Our Experience

Once I had taken the decision to try out Pivotstream’s hosted solution, it became clear that “elapsed time” taken was no longer going to be a constraint to the project.  On that same day, Azzurri had it’s own Pivotstream site in full working order with admin and consumer accounts setup for the trial.  It was now down to me to start making this a fully functional dashboard.

Naturally, I had workbooks at the ready and I loaded a few up immediately.  I started sniffing some of the additional features that I could now start playing with.  Before I knew it, I was canvassing Pivotstream for direction on Query String URL filtering (an awesome attribute to drill across to other dashboards).  A handy guidance document found it’s way into my inbox and I was away.

I was supplied with a program to split core and thin workbooks, another gem that just saves time and aggravation.  I served up a query with web part layouts and, next day, I get a new page layout deployed straight to our site.

Immediately, the focus of what I was delivering was about end user usability as opposed to finding ways around potential (and in some cases very evident) performance issues.  Performance was immediately apparent on the Pivotstream solution, as could be immediately seen by some of my more “chunky” analysis that didn’t even make it flinch.  My in-house SharePoint Server could take upwards of a minute to open these workbooks whereas the hosted solution barely registers seconds.

Within a matter of days, I realised that the limits of this solution only existed to the extent of the limits of my imagination in creating dashboards.  All of a sudden, my focus was turned on making sure that full value was derived and, to that end, I started spreading the word within Azzurri.  Some initial training took place and I immediately recognised that these clever individuals that I was working with had even more insight bursting to get out and the fact that we were playing in Excel meant that they could immediately relate to what they were being shown.  I had hit that fantastic point in the project where momentum starts taking over and this is probably less than two weeks after starting the trial.

Speed (both of implementation and application), elimination of complexity and additional value adding applications delivered in a scalable data-centre model with an OPEX cost model sums it up for me.  Now, it’s just about making the dashboards deliver the real benefit to the business – insight!

As I’ve been writing this, Rob’s reminded me of a comment I made back towards when this whole thing started:

“My key driver is laziness so I’m always looking for quicker and better ways to do things. In doing so, I find myself working non-stop so I may have my driver wrong or I’m failing miserably!”

The reality is that Hosted PowerPivot does do it quicker and better.  I’m working non-stop because the results speak for themselves and I fundamentally “get it”.  The reality is that my driver was wrong!


More Live PowerPivot Web Apps!

February 9, 2012

Excel Pro Turned Web Developer with a SINGLE CLICK!  MUHAHAHAHA!

“IT’S ALIVE!!!!”
(Just your average Excel Pro after converting
his first PowerPivot Workbook into a Web App)

I’ve got another article about to go live on CIMA Insight, but I’m gonna jump the gun a bit and post basically the entire thing here ahead of time.

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

At Pivotstream we recently went live with our first full-time demo site for Hosted PowerPivot.  We’re going to be adding to it over time, but it’s got enough on it already that I think it’s worth looking at – it shows that “spreadsheets have become live web applications” thing that has to be seen, live, to really sink in.

Customizable Homepage

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Pretty self-explanatory – customizable content plus a menu of applications.  Link below.

(OK, one note:  I say “customizable” but customization is only allowed for Authors/Owners of the site.  What you are seeing here in the Consumer experience, and Consumers cannot customize this home page).

App #1: Based on Microsoft’s “AdventureWorks” Sample Data Set

This is the workbook from the Budget vs. Actuals Part One and Part Two posts. Here’s a picture of that same workbook after it’s been saved to a SharePoint web server and then accessed in my browser:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Notes on this application:

  1. This is just an XLSX file, created with PowerPivot and then saved to the server
  2. But it’s rendering in my browser (Firefox in this case), and the XLSX is NOT being downloaded to my computer
  3. I do NOT have to have Excel installed on my computer in order for this browser application to function
  4. This picture is of the menu page of the app, which is just a worksheet with the gridlines and headers turned off
  5. The graphics are two image files inserted into the sheet via Insert.Picture on the ribbon
  6. The four hyperlinks are merely links to other worksheets within the workbook

If I click the “Sales vs. Budget” hyperlink, I am taken to the report I built in last month’s article:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Click the slicer – it works Smile

 

Application #2: Retailer Overlap Competitive Analysis

This application is based on two real-world data sets. One is a list of the addresses of almost every retail food/drug store in the United States. The other is a detailed list of demographic information about every ZIP code (postal code) in the United States.

Blend them together in PowerPivot and you get an application like this:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Note that I have selected the two warmest temperature ranges, circled in orange. If I click the link at the top of the sheet I then see the following analysis:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Notes:

  1. I have the application set as if “I” were Walgreens, and evaluating my competitors’ geographic overlap with my Walgreens retail locations
  2. The first column indicates to me that CVS competes with me (Walgreens) for 41.5% of the potential customers that I try to reach
  3. In other words, 41.5% of the people who live near a Walgreens also live near a CVS
  4. The second column reports that Walgreens competes with me for 47.3% of my customers in Warm locations
  5. So the first column ignores the slicer selections I made on the menu page, and the second column respects them!
  6. The third column represents the delta between columns 1 and 2. Interesting, for instance, that Rite Aid does NOT compete with me at all really in warm locations
  7. Try it out, slice away – there are many ways to slice and analyze this data set

Application #3: CRM Analyzer

This one is also based off of a sample data set, but it is one pulled from a popular CRM package and therefore represents real-world value.

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Note that there are multiple reports in this application as well, plus a menu page, but I’m just showing one here for space reasons.

Application #4: UFO Sightings

Many of you have seen this one already, on Mr. Excel’s Hosted PowerPivot site, but we put it on this demo site as well.

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple