Cleaner SharePoint pages, and an offer to share

August 30, 2010

 
Worlds Most Renowned Designer of Business Intelligence Weaponry (Nonlethal of course)

 
 
“Dr. A. Heller. Weapons designer.
Innovator. Inventor. World changer.”

“…why does he live in a deserted amusement park?”

 

Building an Arsenal of PowerPivot Components and Tools

In the past six months assembling the core PowerPivot-fueled platform at Pivotstream, we’ve been developing some components and tools to supplement the core PowerPivot v1 product, both in terms of what our customers see as well as “behind the scenes” tools for us.

You might expect this from an ex-Microsoftie whose prior job was designing software – I really can’t stay out of that game completely.  (Doubly so given my background on the PowerPivot and Excel teams).  I’m always starting sentences out like “You know, I bet we could…”

One small example of this that I shared last week:  VBA macros that we’ve been using and modifying to make workbook creation/editing go faster.  That’s just the tip of the iceberg though really, so let’s take a look at the first in a series of larger components.

First Example:  Cleaner SharePoint Pages

First, consider what the top of the default SharePoint page looks like:

Standard SharePoint Non Report Page

Default SharePoint Home Page Etc. 

And what the default Excel Services report page looks like:

Standard SharePoint PowerPivot Report Page 
Default Excel Services Report Page

Not bad for out of the box, but there is definitely room for improvement.  Let’s look at those again with some elements highlighted:

Standard SharePoint Non Report Page Highlighted 
Extraneous Elements Highlighted

Things that many BI sites would rather NOT have:

  1. Site Actions Menu – This is fine for a standard SharePoint experience, where users are often pseudo-admins of the site.  But for a BI site, where you want as little noise as possible, and don’t want users to have anything resembling admin control, this is a liability.
  2. SharePoint Ribbon – Don’t get me wrong, I actually am LOVING the SharePoint Ribbon.  Way more than I expected.  But again, on a BI site, the user’s task is to navigate as quickly as possible to actionable information.  And you will be surprised how often someone will call you and say that they can’t find the option they are looking for on the Page ribbon tab, when the button they want is right there in the report.  Again, it’s just noise in the BI scenario.

Now let’s take a look at the Excel Services page again, with the same level of focus:

Standard SharePoint PowerPivot Report Page Highlighted 
Extraneous Elements Highlighted Again

Things that many BI sites would rather NOT have:

  1. Excel File Tab and Toolbar – Just like above, this is a great element for many scenarios.  If you view Excel Services as “Excel in the browser,” which is precisely its mission in most cases, then yes, you need a File menu for Saving, Downloading, Checking Out, etc.  But in a BI site, again, it’s noise, it’s confusing because it’s read only, etc…  plus it distracts from the well crafted perception that this is NOT Excel but a Web Application.  Most people just feel GOOD about web apps, and they feel kinda icky about Excel.  People are more likely to use something that they feel good about.
  2. File Name – Same thing.  The feel-good vibe of the app is disrupted by the presence of an Excel file name.  “Are you saying that’s a file on my desktop?  Where is it?”  Subtle little things like this are easy for techies like us to dismiss, but ignore them at your own peril.
  3. Unsupported Features Bar – This one requires little explanation, it looks horrible.  And it suggests to the user that something is broken, when really, it’s just those slicer parent controls.  In the VBA post I gave you a macro for removing all of those right before you save, but in practice that isn’t a viable solution.

The Simplified/Modified Versions

None of those problems was going to fly for us at Pivotstream, so we developed our own modified set of page templates.

First, a really simple, sample home page as a customer would see it:

Pivotstream HomePage Theme 1

Note also the little copyright notice at the bottom, that’s built into all of our pages now as a result of the template.

These templates are also themeable, so we can change the color scheme, page content, and images from within SharePoint itself with a few clicks:

Pivotstream HomePage Theme 3

(No one has ever accused me of being an artist, so at time I choose intentionally ugly colors – you can do much better I am sure.  Bet you can’t whip up a PowerPivotYoda like I can though).

Improvements to Note:

  1. No SharePoint ribbon to distract
  2. No Site Action menu (ditto)
  3. No Search box (we can turn that back on once we have it hooked up to work properly)
  4. No QuickLaunch menu telling me about all those doclibs and such on this site that I don’t want the users to concern themselves with.
  5. Lots of real estate for adding content and links.

Customized Report Page Templates

Next, here’s a report page we have in production, with a very minimalist style:

PivotStream PowerPivot Online Service 1

Zooming in on the top, we see:

Pivotstream PowerPivot Report Page Zoomed

Improvements to Note:

  1. Still no SharePoint Ribbon or Site Actions menu
  2. No Excel “File” ribbon
  3. No “Unsupported Features” warning
  4. “Download Snapshot” appears as a link in the header, separate from the File ribbon
  5. No Excel Icon on the browser tab – actually it is usually our Pivotstream logo, but IE forgets those icons all the time and puts the little IE logo in instead.  Sigh.  Firefox is much better about that I’m told, but I’m still running IE.
  6. No Excel filename in the title bar
  7. “Breadcrumbs” nav bar in the header, for nav back to the site home

In other words, everything extraneous removed, and only functionality remains.

Fear not, admins still see the suppressed controls!

Of course, when *I* log in to these sites, I often need to make changes, like add a new page, update a setting, etc.

So, I need a lot of the things that are suppressed in the screens above, like the Site Actions menu, and the SharePoint ribbon.

The screenshots above were all taken when I was logged in under my User account.  Here’s what I see when I sign in as my Admin account:

Pivotstream Admins See the Ribbon Etc

Neat huh?  It detects I am an admin, and gives me back all the stuff like the SharePoint ribbon, Site Actions, the notification I have the page checked out, etc.

Under the hood

OK, so what all is going on here, in order to make this work?  Here’s a rough list:

  1. A new SharePoint Master Page, from which all user-facing pages are derived
  2. Admin vs. User detection logic in that Master Page, and subsequent suppression of certain page components when User
  3. A new Layout for the report page, that includes the Excel Services web part with default settings already applied
  4. A new Control (part server-side and part Javascript) that implements Download Snapshot functionality without having to expose the Excel toolbar (whose options are confusing in a pure BI environment).

That’s about all I am qualified to convey – I supervised the creation of all of this, but I did not code it.  (Similarly, I can tell you about how Excel’s calc chain involves a separate linked list per processor, but I couldn’t tell you how to write something similar now could I?).

That Excel Services Toolbar – Isn’t that part (hiding it) simple?

Yes and no, and then no again :)   On one hand, it IS easy to create a new web part page, slap an Excel Services web part on there, and then customize its settings to your liking.

What I did NOT find easy, was repeating that process every time I wanted to publish a new report.

With this new system, I just instantiate the right template, and change one setting to point the template at the right workbook.  Done.

Also, the Download Snapshot feature turns out to be pretty important.  For us, and most locked-down BI deployments of PowerPivot, the snapshot feature is the ONLY way to print something that looks decent.  And it’s the only way to take a fixed view of the entire workbook offline.  So, we didn’t want the File menu, but we needed that feature to stay.

So we re-implemented it :)

What about the Sharing thing you mentioned?

It cost us well over $20,000 in billable time to build all of this, in addition to my time designing and supervising it.  In my opinion, there is no sense in others having to sink the same cost (and their own time) rebuilding the wheel.

If you are interested in getting your hands on any of the components I described here, or if you are interested in whether they can be customized to your specific needs, drop a note to:

     software@pivotstream.com

Unlike the VBA Macros and other small stuff, I can’t give away modules like this that the company paid for.  But I am free to explore the idea of sharing with the community at a fraction of what it cost us, as a means of recouping some of our development budget.

And if I can make this sort of project pay for itself, that just means I can spin up other such projects :)   This is very much an experiment at this point.

Anyway, drop us a note if you’re interested, and we’ll see if we can figure something out.

2 Comments | Pivotstream Tools and Software, PowerPivot as Online Service, SharePoint | Tagged: | Permalink
Posted by powerpivotpro

A few tips on maintainability

August 30, 2010

  
Eat This not That PowerPivot EditionEat This Not That, PowerPivot Edition!

Just like normal Excel, PowerPivot requires little or no premeditation – you can just jump right in and build something quite impressive.

But if you want to be a true PowerPivot Pro, and reap the full long-term manageability and robustness benefits, applying a little bit of discipline to your workbook development can go a long way.  

The list of best practices we’ve been developing at Pivotstream is a little longer than I have time to pull together right now, but I thought I’d share a few quick tips.

#1:  In Measure Formulas, ALWAYS Include Table Names in Col Refs

You may have noticed that PowerPivot sometimes lets you omit table names in your measure formulas.  Resist this temptation.  Always include the table name.

Use THIS:

   =SUM(Table1[MyColumn])

NOT that:

   =SUM([MyColumn])

Yes, I know they both work, and both return the same result.  But some places REQUIRE the table name, like arguments to the CALCULATE function.  So you might as well get into the habit.  Plus, when combined with the next tip, you get another benefit too.

#2:  NEVER Include Table Names in Measure Refs

In another odd bit of flexibility, PowerPivot lets you include the table name in references to other measure names.  My advice here is exactly the opposite of the above.  NEVER include the table names when referencing a measure.

Use THIS:

   =[Measure1] – [Measure2]

NOT that:

   =Table1[Measure1] – Table1[Measure2]

Measure names must be unique across the entire PowerPivot workbook, so the table name is not needed.

More importantly, if you follow tips 1 and 2 religiously, you will always be able to tell the difference between columns and measures in your measure formulas, and trust me, that is a big advantage.

#3:  Don’t cut corners on prototype data sources

Here’s the quick version:

Use THIS:

  1. SQL Server
  2. Another “real” database like Oracle, DB2, etc.
  3. SharePoint Lists, Access Services, etc.
  4. Reporting Services reports
  5. Other OData Data Feeds

NOT that:

  1. Excel Linked Tables
  2. Copy/Pasted Sources

These are MAYBE’s:

  1. Access MDB’s
  2. Text Files

Long version:  Data Sources are like Marriages (High Switching Cost)

Why do I have these biases?  Primarily because changing data sources later is exceedingly painful.

You CANNOT take a PowerPivot table that is sourced from Copy/Paste, for instance, and switch it to use another data source type.  In fact, you can’t change any source type to any other.

So, if you prototype against a text file and later want to point that table to SQL Server…  you must delete the table (which deletes ALL of that table’s measures, calc columns, and relationships), and start over.

If you think that isn’t a big deal, you haven’t lived through it yet :)   Nothing is more frustrating than re-doing work you have already done.

So…  if you think there’s a decent chance that long-term, your data will come from a real DB, go ahead and take the time to start that way.  It will pay off over and over.

Note that you CAN point a PowerPivot table from one db server to another, or point it at another db table, or another db view, etc. – and this DOES NOT require you to delete your table.  There is a universe of flexibility once your data is sourced from a db.

Oh, and if you think you’ve got a table that you’ll never need to update, and that you will never want to move to a real db, well, from experience I can tell you that you might be mistaken.  I’ve made that mistake already, many times, and paid for it later.

There are some other surprising benefits of having your data come from a robust refreshable source, but I will cover that another time.

1 Comment | Data Import, DAX, Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot VBA Macro Mania!

August 23, 2010

 
Macros Are Assembly Line Approach for Spreadsheets 
“I approve of your spreadsheet methodology.”
-Henry Ford

I’ve got some Excel VBA macros that you may find useful in your work on PowerPivot.  Been saving these up for awhile now, and I think there are now enough to warrant a post.

I’ll post them all below with a description of each, but since WordPress seems to prevent copy/paste by default, I will also place a text file here containing all of them, so you can download all at once.

These get the job done in workmanlike fashion.  Feel free to point out how much better I could have made them :)   I know, I could have turned off visual display updates to make them faster, for instance, but if I had to make these things pretty before sharing, I never would have shared :)

So hopefully, you will find them useful, if as nothing more than a starting point.

Without further delay, I give you, the macros…

“Make the same change to all PivotTables in the Workbook” Macro

Useful When:  You have lots of Pivots and they all need the same change made

Be careful of:  Well, as with all macros, you should save your work before running

Sub ModifyAllPivotTables()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In ActiveWorkbook.Worksheets
        For Each Pivot In Sheet.PivotTables

            ‘YOUR CODE HERE 
            ‘EXAMPLE:  Add Field “b” to Rows:
            ‘Pivot.AddDataField Pivot.PivotFields(“b”), “Sum of b”, xlSum
            ‘With Pivot.PivotFields(“b”)
            ‘     .Orientation = xlRowField
            ‘     .Position = 3
            ‘End With

        Next
    Next
End Sub

Remove slicer parent controls (rectangles around slicers in the sheet)

Useful when:  You want to get rid of that “Unsupported Features” warning in Excel Services
Also useful when:  You end up with lots of slicer parent controls stacked on top of each other, like we do…  ‘cuz we copy/paste sheets too much

Be careful of:  This macro nukes all Rectangle art shapes in the workbook :)
Also be careful of:  The PowerPivot addin puts the parent controls back whenever you select a pivot

Sub RemoveAllRectangles()
    Dim oSheet As Worksheet
    Dim oShape As Shape
    For Each oSheet In ActiveWorkbook.Sheets
        For Each oShape In oSheet.Shapes
            If Left(oShape.Name, 9) = “Rectangle” Then
                oShape.Delete
            End If
        Next oShape
    Next oSheet
End Sub

Connect all Slicers on a given sheet to a specific pivot on any sheet

Useful when:  You want to create an All Slicers sheet, that contains nothing but slicers that are intended to affect pivots on other sheets.  I like to use the PowerPivot addin to create the slicers because it sizes them correctly, then getting rid of the pivot, leaving just a slicer sheet.  But then you have to connect all those slicers to the pivot(s) on other sheet(s), eek.  This macro is a huge help.

Be careful of:  What could possibly go wrong with this one?  Heh heh.

Sub ConnectSlicers()

   Dim oSlicer As Slicer
   Dim oSlicerCache As SlicerCache

   For Each oSlicerCache In ActiveWorkbook.SlicerCaches
       For Each oSlicer In oSlicerCache.Slicers
           If oSlicer.Shape.BottomRightCell.Worksheet.Name = “YOUR SHEET NAME HERE” Then
               oSlicer.SlicerCache.PivotTables.AddPivotTable (Sheets(“ANOTHER OR SAME
                       SHEET”).PivotTables(“DESIREDPIVOT”))
           End If
       Next
   Next

End Sub

“Disable slicer cross filtering” macro

Useful when:  you have a lot of pivots in a workbook that all have very similar slicer fields in use, and you suddenly realize that cross-filtering is not required on a bunch of those fields and it is just slowing down performance.

Be careful of:  If you have slicer fields with similar names, this macro’s use of InStr (essentially, a “Contains” operator) may cause you problems.

Sub DisableCrossFilter()

Dim oSlicer As Slicer
Dim oSlicerCache As SlicerCache
Dim sName As String
Dim bClearCrossFilter As Boolean

‘ASSUMES 3 FIELDS YOU WANT TO DISABLE X-FILTER FOR, CHANGE 3 TO OTHER NUMBER AS NEEDED!
Dim aTestString(1 To 3) As String
Dim iStringCount As Integer
iStringCount = 3

Dim i As Integer

‘YOUR FIELD NAMES HERE
aTestString(1) = “Buyer”
aTestString(2) = “Period”
aTestString(3) = “Store”
For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bClearCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bClearCrossFilter = True
            End If
        Next i
        If bClearCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = xlSlicerNoCrossFilter
    Next
Next
End Sub

Enable slicer cross filtering

Useful when:  you over-use the macro above

Just replace the FOR loop body of the macro above with:

For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bSetCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bSetCrossFilter = True
            End If
        Next i
        If bSetCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = 
            xlSlicerCrossFilterShowItemsWithDataAtTop
    Next
Next

Hide all gridlines and headers

Useful when:  you want to make your report sheets look more professional

Sub HideGridAndHeaders()

Dim sSheet As Worksheet

For Each sSheet In ActiveWorkbook.Worksheets
    sSheet.Activate
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False
Next

End Sub

Link to text file with all macros

Here’s the link again in case you don’t want to scroll all the way back up.

Got a macro of your own?

Post it in comments or email it to me.  If I get enough I’ll do a “Part Two – Macros From the Community” post.

11 Comments | Quick Tips & Tricks, VBA / Macros | Tagged: | Permalink
Posted by powerpivotpro

Datamining using PowerPivot and Predixion Insight

August 23, 2010

By Kasper de Jonge, cross post from PowerPivotblog.nl

Since this week the public beta of Predixion Software’s Data mining in the cloud for Excel is available. Those of you who are familiar with the the Microsoft SSAS Data mining Add-ins should be very comfortable with what is inside Predixion Data mining for Excel.  I have done a previous blog post on doing data mining using PowerPivot with the MS data mining add-in where you can see how it currently works .

Predixion Insight for Excel is like a new version of the current SSAS add-in, the Predixion insight team consists of the folks that previously build the Add-in for MS and now started on their own.

The biggest change is that you no longer need an SSAS server installed. All action happens on the Predixion servers in the cloud. Second biggest (for me) is that you can use PowerPivot data as a datasource for you Data mining. Using it in combination with PowerPivot requires nothing more then Excel and a Predixion subscription for data mining. Furthermore the overal UI had been improved to make data mining a more user friendly experience. And it support 64 bits.

From the Predixion site:

Predixion’s intuitive and easy-to-use solution allows users to run predictive analytics in the familiar environments of Microsoft Excel® and PowerPivot. Whether you are an existing SQL Server® Data Mining user, a BI specialist or a newcomer to the arena of Predictive Analytics, Predixion Insight™ will enable you to easily create, manage and run powerful and accurate predictive models without extensive training or specific knowledge of the methodologies currently required to create successful predictive projects.

In this blog post we are going to see what are the key influencer are of the number of items on stock from the Contoso sample database. First we need to install the Predixion Insight for Excel, just run setup and the client will be installed within Excel. Next time you open Excel the client will be there. We have two tabs “Insight analytics”:

and “Insight Now”:

The “Insight analytics” tab is mainly for the advanced data mining,the insight now enables you to get started immediately. Before we can do anything we need to connect to the predixion servers with the account we created on the website:

After logging into the Predixion cloud service we can start data mining. I have loaded information from my datawarehouse in PowerPivot for Excel, i have information about my stock. I have loaded the fact table FactInventory that contains the actual nr of stock, this contains 8 million rows . The fact table is related to a lot of descriptive tables that surround the fact table, called the dimension tables. I have loaded a few of these descriptive tables into PowerPivot as well. What do we know about an item that is on stock:

  • When was it on stock? Year/month/day
  • What Product?
  • What Productcost
  • Aging of a product in inventory
  • The Country of the store it is in.

Of all these properties we want to know what influences the nr of days in stock the most. For this i want to use the “Analyze key influencers”  function. So i click on it.

This gives us a screen where i can select what my source is, Excel or PowerPivot, I select PowerPivot. Now i can select what table i want to analyze, i select the fact table. We could place filters here but i decided to plague the Predixion server all out with my full 8 million rows :) .

Next we can select the column we want to we want to determine the key influencers for:

Of course we don’t need all the columns to be analyzed, we can select the columns we want to include in our analysis:

And this is where we notice something not right. As you can see we can select DateKey, StoreKey, ProductKey. But when we analyze this it would analyze this as a Key value, instead of Year 2009 it would test for the value 1-1-2009 and Store “Amsterdam” it would check as Integer 12. So we need to do something first, we need to prepare our PowerPivot table so that it contains descriptive values.  Luckily for us this is not that hard, just add a column in the PowerPivot field window using the =RELATED function:

Now we can select these columns in the data mining add in:

Now we are good to go, just click Run and the data mining will be started.

The great thing here is that everything happens on the server, i can start multiple operations at the same time. And of course it being in the cloud i can open this up on another machine and immediately access the results.

One thing i noted is that the information is send to the cloud through an encrypted tunnel so no worry your data can be read while sniffing your network.

When i click on Minimize to Task pane you will see a new Predixion pane will show up where you can see all your tasks:

As you can see i ran this demo before so i can use these results to show the result of the mining Predixion did, just click “Results” and the report below appears:

As you can see it is pretty easy to combine the information you have in PowerPivot with the enormous powers of data mining. The new user interface and the availability of the Predixion servers in the cloud really make data mining available for anyone. Just as PowerPivot makes data analytics available for everyone. The Predixion Insight for Excel works with Excel 2007 and Excel 2010 32 AND 64 bit, of course PowerPivot won’t be available with Excel 2007.

Predixion Insight is also working on a on-premise and dedicated off-site cloud solution which leverages SQL Server, SSAS and SharePoint which they call Enterprise Insight.

3 Comments | Uncategorized | Permalink
Posted by Kasper de Jonge

Extranet Analytics Products Using PowerPivot!

August 20, 2010

 Marvin-Martian-Pivotstream 

“I claim this planet milestone in the name of Mars Pivotstream, isn’t that lovely?”

-Me (always looking for Marvin Martian tie-ins)

(Our poor CEO is in agony right now at the sight of Marvin adorning this announcement.  It’s a blog, it’s meant to be fun, but be assured I take my day job very seriously.  Like earth-shattering kaboom seriously).

May 2011 Update: We can now host PowerPivot for SharePoint for You

When I first wrote this post in August 2010, we weren’t in a position to take on general-purpose PowerPivot hosting for the community at large.

But now, we are.  In a big way.  Please see this post for more detail if you are interested in us running your PowerPivot for SharePoint infrastructure for you, via a collaboration between Pivotstream and Rackspace.

Ok, back to the original post…

Rolling Along  (…and Pretty Sure We Are First)

PivotStream PowerPivot Online Service 1

A Production Pivotstream Application
(All Clients’ Identities Redacted)

I’ve been meaning to blog about this for a long time now actually – I planned to make a big splashy post the moment we went live with a PowerPivot-fueled version of one of our existing Retail/Pharma Analytics products.  (Pivotstream has been in business for a couple of years using a variety of technologies, so PowerPivot is a new angle for us, since the time I signed on).

That first “go live” moment passed months ago, actually, and instead of calling it Miller Time and firing up the blog editor, it was then time to start building the next product.  And the next.  Etc.  Good problems to have.

Our clients are making better business decisions already, and are doing so with zero investment in infrastructure or training.  A URL, a browser, and login credentials is all they need to start using business applications like the one above.  We’ve heard customers respond with gems like “Reports are dead, now we have Pivotstream!” which makes me smile for multiple obvious reasons.

I believe we are the first in the world to be doing this (extranet-provided PowerPivot apps), so I finally got around to a post, both to brag about it a little (I’m really proud of the results) and to explain a bit of how it works :)

PowerPivot = powerful enabler in the background

The fact that PowerPivot is a key component of these products is why I am blogging about it here – I figure it is interesting proof of what can be done.

But our customers/clients don’t have much much day to day reason to think about PowerPivot as part of those products, for one key reason:

Customers of our products are NOT building the applications/reports/workbooks in PowerPivot for Excel – WE ARE.  All our customers see is what’s important to them:  a suite of interactive reports in their browser.  Reports that span vast quantities of data, return some very targeted and intelligent metrics, and can be sliced in seconds.

But still follows/validates the core value proposition of PowerPivot

The only real difference between our PowerPivot-fueled app suites vs. a prototypical intranet deployment of PowerPivot is simply that the author and consumer roles are split across companies.

If you ignore that boundary, well, our system becomes very similar to any other deployment.  The end result for the consumers is the same, it’s just that the address bar in the browser points to pivotstream.com versus your internal SharePoint sites.  Will consumers in your organization know it’s PowerPivot fueling the web apps on your intranet?  Some will, and some won’t, depending on their level of curiosity, just like with us.

PowerPivot Hosted Analytics 2
Beta application for another of our clients, about to go into production this month
(One of about 50 reports in that suite)

Why that works:  Pivotstream knows the clients’ business needs

Here’s a crucial difference between Pivotstream and the average BI consulting firm:  except for me, most of our people didn’t come up through the BI or technology ranks.  Each of their many years of experience is rooted in the industries that we serve.  In other words, our PowerPivot modelers/authors used to work in roles that would benefit from the types of applications that we offer today.

Now, that’s not to say these folks are techno-phobes, or afraid of learning new things.  They were willing to invest in PowerPivot, and hungry to learn it because it empowered them to do things that were previously impossible.

Interesting people for sure – they could all be very superstars working in their former roles in retail, pharma, etc., but they’ve opted to essentially broadcast their expertise instead.  They just needed a transmitter powerful enough.  PowerPivot, SharePoint, and Excel Services, with SQL Server behind the scenes, fills that need.

“Self Service BI” becomes “Full Service BI,” at least in our verticals

As I’ve emphasized before, the biggest time savings of using PowerPivot is that it no longer requires the business unit workers to communicate requirements to BI pros, and then iterate semi-endlessly until the right results emerge.  When the toolset, the business problem, and the business rules all live within one person’s head, sophisticated and robust applications can be built to address complex needs at a fraction of the cost of using traditional methods.

And that’s how it works with us, too.  Those savings are then passed on to our clients in the form of very reasonable subscription rates.

But we also provide our clients with everything else:  we run all of the servers, ingest and shape all of the data, build the applications, keep them up to date…  all they need are web browsers and the logins we provide.  Protection from the chart police is also included, heh heh:

PivotStream PowerPivot Online Service 4

Did Someone Say “Pie?”

Benefits of using SharePoint

We choose to deliver those products over the web using SharePoint, because:

  1. It gives our customers the quickest possible lead times – no installation requirements
  2. It gives us a means to keep everything up to date, nightly
  3. It eliminates user data download requirements – a web app is a lot better than downloading a 2 GB workbook every day.
  4. It allows us to protect our core IP – some of our measures and views are truly firsts in the target industries, and the ways in which we implement those, from the backend up through pixels, are something we can lock down via in-browser delivery.

And sure, if you have a proposal about how we might be able to build or host a different kind of app for you, drop us a note at info@pivotstream.com – in fact, we’re already discussing several cooperative opportunities currently.

What’s my role in all of this?

Why, I’m the PowerPivotPro, of course :)   This entails things like:

  1. Training my colleagues on PowerPivot – the basics, best practices, etc. 
  2. Responsible for our server farm – selecting data center and hardware, planning server roles and topologies, spinning up SharePoint and PowerPivot for SharePoint, customizing it to our needs, extranet security, monitoring server health, etc.
  3. Adviser to our SQL Integration Services team – we use SQL Server Integration Services (SSIS) to digest data from our clients and partners (typically flat files over FTP), and then land the data in SQL Server.  From there, it’s imported into PowerPivot models.  I don’t know SQL or SSIS all that well myself, but I do know what sorts of data structures make for the best PowerPivot modeling and performance, so I spend a lot of time working closely with that team.  Longtime readers of this blog will know that sort of cooperation sounds familiar.
  4. High Priest of What’s Possible – I do a lot of application prototyping in PowerPivot based on the needs of our business team.  “Can we build something that does X?”  is a common starting point, and off I go.  I frequently create DAX measures, data structures, and slicer sets that are then used as templates.  But even that is a highly collaborative process, as we tradeoff application requirements against performance, PowerPivot limitations, etc.
  5. Soaking it all in – in the process, I’m learning quite a bit about the retail, pharma, and alcoholic beverage industries and what makes them tick.  As I learn from my colleagues and vice versa, the line between our roles is naturally blurring over time.

In short, I’m basically a fulltime PowerPivot consultant to the rest of Pivotstream :)

And now that things are rolling along nicely, I’m thinking its time to start assisting others with their on-premise deployments.

Consulting – interested in assistance from the ‘Pro and the team?

I know most people reading this are doing so in order to apply PowerPivot on their own, and we are increasingly in a position to share our expertise.  So if you are interested in training/consulting to support your own internal deployments, or just want to know more about our core applications, please click here, or contact info@pivotstream.com

Questions on how we did this?

This post is getting kinda long so I’ll cut it short.  Lots left to talk about, but I’ll start with wherever there is the most curiosity.

Anything you want to know about how we employ PowerPivot for this, please ask in comments.  I can’t give away certain secrets of the core apps, but as always, will try to be as helpful as possible.

Some questions may end up warranting entire posts to answer, which is good too :)

7 Comments | PowerPivot as Online Service | Tagged: | Permalink
Posted by powerpivotpro

Review: Prof. PowerPivot for Excel & SharePoint

August 19, 2010

 
Click to view on Amazon

No more delays!

Awhile back I reviewed Mr. Excel’s PowerPivot book and vowed to post a review of the other major PowerPivot book to date, by Denny, Ron, and Siva (all from the SQL team at Microsoft). 

I actually finished this book awhile back, but things got pretty intense at Pivotstream (in a good way), delaying my writeup much longer than intended.

So, no more delays, time to dive in and tell you what to expect.
 

Focus on The PowerPivot System

In my review of Bill’s book, I said that there was no better primer for Excel pros making the transition to PowerPivot, and that remains true.  Bill took the approach of “this is the greatest thing to happen to Excel in over ten years, so Excel pros, listen up, you’ve got an arsenal of new tools to deploy.”  And I think that is precisely the right approach to take with Excel pros who are getting started. 

But there are MANY other aspects to PowerPivot, and a lot of ground left to cover in depth.

The boys from Redmond have VERY different backgrounds from Bill, and those backgrounds shine through in their book.  You could almost read their book and Bill’s book and not be sure they were discussing the same product… and I think that is a very good thing.

Cutting to the chase, the Red book (I’m gonna call it that for short) covers a lot of things that the Bill book intentionally leaves to others:

  1. How PowerPivot compares to and interacts with traditional BI systems
  2. What it takes to implement an entire PowerPivot SYSTEM in your organization
  3. The technical underpinnings of PowerPivot, both in Excel and SharePoint
  4. A quantity of hyper-detailed tips and tricks that can only come from insiders
  5. Integration with a wide variety of data sources
  6. Planning and deploying a PowerPivot for SharePoint farm
  7. Monitoring and maintaining your SharePoint deployment
  8. Also covers a LOT of the basics of SharePoint, useful for SharePoint neophytes

What the book is NOT

1) It is not the first book an Excel pro should pick up.  It covers too much, and comes from a perspective that might seem a bit foreign.  Start with Bill’s book if this is your background.

2) Like Bill’s book, it is only a light treatment of DAX.  Again, this is wise, because DAX really deserves its own book.  Those seeking a detailed DAX book should look forward to an upcoming title from Marco Russo.

3) It does not cover the real-world gotchas encountered in a full adoption of a PowerPivot system.  After a full year of applying PowerPivot to real-world problems – six months as a product team member in exile (in Cleveland), and the last six months putting the full system into action at Pivotstream, I can say that there are a number of “gotchas” that you have to look out for in PowerPivot v1.  They are not fatal – Pivotstream’s PowerPivot platform is very much thriving – but you only discover them, and learn to avoid/overcome them, via real-world practice. 

Anyway, for a just-released product, it’s not like we can expect ANY book to cover that kind of thing.  There’s probably room for a book like “PowerPivot in the Trenches” but someone else needs to volunteer, as I, um, rarely get out of my trench :)

What the book IS – Consistently readable and info-rich

1) Great Information Density, but High Readability – When I finished the book, I immediately dropped an email to Denny/Ron/Siva and told them how impressed I was with the balance between density and clarity.  As a de facto tech writer myself throughout my career, I can say that I struggle with this, and typically end up jamming in too much at the expense of readability, or a 50-page doc with so much whitespace that 20 pages would have covered it.  Perhaps you have noticed.

It’s hard to put my finger on how the book strikes such a nice balance – perhaps it’s the editing, or the layout style – but I consistently noted how MUCH was conveyed on each page, while at the same time, it felt like a light read.  That’s saying a lot considering the list above.

2) Benefits of Three Product Team Authors – When you include all of the folks from teams like SharePoint, Excel, SSRS and others that contributed to PowerPivot, it’s probably safe to say that the product reflects the efforts of 200+ people for several years.  No one human being could possibly span all of that, and it only gets harder if you weren’t directly involved in that process.

These three guys were working on the book for a long time, while the product was still in development.  They all had different areas of focus, both in terms of natural affinities and in terms of dividing up their assignments.  They then had a lot of time, and access to the people who were building the product, to refine the content.

And then they all rigorously cross-reviewed each others’ chapters – you can’t get away with slips and omissions when two of your peers are on you, and they have the same level of exposure and access that you do. 

The benefits of this authorship approach are evident in the book.  You’ll see what I mean – I always expect some “uneveness” in a tech book of this length because the talents and endurance of a single author are themselves uneven.  Chapter to chapter, the red book’s quality remains consistent.

3) History of PowerPivot.  The book is sprinkled with a number of sidebars titled “Inside PowerPivot” that relate some of the human side of how the PowerPivot project got started, and evolved over time.  While not actionable in a technical sense, other people that I’ve talked to about this book, without exception, the first thing they say is how much they enjoyed these sidebars.  That’s saying a lot given the two points above.

Overall Recommendation

If you fit any of the following descriptions, I rate this book as a must-read:

  1. Anyone leading or contemplating a PowerPivot deployment (the total system, as opposed to just viewing it as Excel 2010++)
  2. Excel pros who have read Bill’s book and want to expand their expertise – I suspect a lot of you will decide, correctly, that a full PowerPivot system makes your talents a lot more valuable and visible, and will find yourselves on point for test deployments
  3. BI or SharePoint pros who are ramping up on PowerPivot (or evaluating PowerPivot’s impact on their work)

4 Comments | PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

Pivot Refresh vs. Update – is there a real difference?

August 16, 2010

 
Choose Your Next Words Carefully

“Choose your next words carefully, PowerPivotPro, for they may be your last…  well, your last before your next mtg anyway.”

 

 

Most pivot operations are “Updates”

Quick primer:  when I worked on the Excel team, we described most pivot manipulations as “Updates.” 

  1. Changing selections in a slicer or report filter
  2. Moving a field from rows to columns, or changing its order, etc.
  3. Adding removing fields to/from the pivot
  4. Drilling up or down (expanding/collapsing) within a field
  5. Toggling subtotals/grandtotals
  6. Etc. – I could be here all day trying to list them all

These are all Updates, even though that word appears (almost) nowhere in the UI.  These are all generally very fast, especially with PowerPivot.

But a Refresh, well, a Refresh can take an hour in the right conditions.

What does Refresh do and when is it a lot slower than Update?

Pivot Options Tab Refresh Right Click Refresh Option in a PowerPivot

Refresh does two things that Update does not.  The first is very straightforward, the second is a bit more mysterious.

Difference #1:  Refresh processes EVERY pivot that shares the connection!

Let’s say you have 10 worksheets in your workbook with 4 pivots on each, all of them created using PowerPivot.

Then you right-click one of those 40 pivots and select Refresh.  What happens?

All 40 pivots refresh, that’s what.  Because all 40 are connected to the PowerPivot data in the workbook, they all share the same connection.  Doesn’t matter what worksheet you are on.

So, if there is any reasonable complexity to your pivots, you are in for a bit of a wait, perhaps about 40x as much as an Update of one pivot.

Wait, so a Refresh is really the same thing as a Refresh All?

Data Ribbon Refresh and Refresh All

Notice on the Data ribbon in Excel, you also find a refresh button, and this one offers two options – refresh the selected pivot, or refresh everything.  The Refresh option does the same as the commands shown in the previous section.

Refresh All is not precisely the same thing, but 99% of the time, it is.  If you added a 41st pivot, and this one was connected directly to, say, an Access db, then that pivot would NOT be on the same connection as the others, and Refresh would affect either 1 pivot or 40, whereas Refresh All would touch all 41.

What’s that second difference you mentioned?

Refresh also includes something that Update does not, something we’ll call “field list refresh.”

Whenever Excel is processing an Update action, it generally assumes that the underlying data structure in the source (PowerPivot’s db, over in the PowerPivot window) has not changed.  So if you added a new field, the field list will NOT pick that up.

That’s actually the reason why you see the three most common PowerPivot error messages – Excel’s “picture” of the PowerPivot db is stale. 

The addin works VERY hard to prevent this ever happening, however.  Notice how when I add a new calc column in the PowerPivot window, the addin gives me a warning on the Excel side?

New Calc Column in PowerPivot Window New Field Not Reflected Yet But Warning

When out of sync like that, the Addin turns Updates into Refreshes!

Whenever the addin detects that it’s in that state, you have a Refresh coming whether you want it or not.  You see, the addin is “listening” for changes to pivots that are connected to PowerPivot, and if one of those gets an Update, and Excel is out of sync with the PowerPivot db, the addin intervenes and calls Refresh to get everyone synchronized.

Morals of the story

1) Don’t Refresh when an Update will do.  I know, you went and changed a formula and you want to see how that impacts your pivots.  It’s tempting to click Refresh.  In small workbooks that won’t matter, but often, you are MUCH better off changing a slicer value or something like that.

2) Add calc columns in bulk before modifying any pivots.  If you’ve got 5 calc columns to add in the PowerPivot window, consider doing them all at once before adding any of them to pivots, so you only pay the refresh once.

3) Try to finish building all measures before building lots of report sheets.  The addin rightly assumes that every measure you write, is likely something you are about to add to the pivot, so it adds it to the pivot for you.  And since the measure is new, Excel doesn’t know about it, so that’s right, Refresh.  No big deal if all you’ve got is one pivot, but I have workbooks with forty.  So plan ahead.  I know, this is easier said than done.  That’s why there’s a fourth trick.

4) Add measures in bulk using the Defer Layout Update trick.  See this earlier post for a quick rundown.

One more moral

This whole Refresh vs. Update thing rears its head one other place, and with some pretty nasty implications.  It happens on SharePoint, and is the topic of an upcoming post.

2 Comments | Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro

Two common PowerPivot error messages

August 14, 2010

Been meaning to post this for awhile now.

Here are two error messages I see all the time now, about once per day:

UPDATED:  I’ve found a third message that belongs in this same family, with same root cause and same fix, now included below.

PowerPivot Formula is Invalid Error

PowerPivot Element Not Found Error

PowerPivot The command was canceled error

I typically get the first when I’m adding a measure, the second when I am trying to put a field on a slicer, and the third when I am adding a field from a table I just added to the model.

In my experience, these are all caused by exactly the same thing.

The first error message is completely misleading.  The third is uber-vague.  The second is very much on target. 

How to fix this

Don’t worry, your workbook is fine.  All you really have to do is get Excel, the addin, and the PowerPivot db up to date with respect to each other, because something somewhere has gotten out of sync.

There are four fixes you can try, and I recommend you try them in order since each is more time consuming than the previous.  After each step, retry adding the measure (or field) that failed to see if it’s fixed.

  1. Make the pivot update somehow – click a slicer, add a different field, filter it, etc.
  2. Right click a cell in the pivot and choose Refresh
  3. NEW:  Add a calculated column in the PowerPivot window and then delete it (this worked for Maurice Prather where everything else, including tricks 4 and 5, failed)
  4. Save and close the workbook, close Excel completely, reopen workbook
  5. Close Excel, go and clean out the Vertipaq files from your temp folder, reopen workbook

For details on how to find the Vertipaq temp folder, see this entry in the FAQ:

http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=128

2 Comments | Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro

Combine two data sources (in our case ssas) using PowerPivot

August 11, 2010

By Kasper de Jonge, crosspost from PowerPivotblog.nl

As part of our PowerPivot for the regular Information worker today a blog post on how to use PowerPivot to combine information from two sources into a single information product.

In our case the information is stored inside two analysis services cubes that the BI department had made accessible to us.

We want to analyze the order amount per product, year, month and country we have in one cube with the actual order count we have in another cube.

We start by importing the information from both cubes into PowerPivot, make sure both tables contain the same columns you want to analyze against.

We use the “From database”, “From analysis services” to select the analysis service and cube we want to import from. After that we can select the data to import:

Same for the second cube:

The data from both cubes are loaded into two seperate tables. We want to combine the two tables. To do that we can create a relationship between the tables, for more information read a previous blogpost on relationships. First thing we need to do is make sure that all the columns are identical. As you can see in the screenshots above the month and year columns from both tables do not contain the same values.

We start by creating a new calculated column in which we use DAX to get only the last 4 numbers and create a new year column

The next one is a little harder, the monthscolumn from one cube contain only the names of the months instead of the numbers. To translate the monthname to a monthnumber i created a translation table in excel and loaded it into PowerPivot:

next i created a relationship between the table and the translation table:

Now we are able to use the translated values in our table to create a new column using DAX, using the function RELATED we can get the value from the translation table in each row:

To make sure we can create a relationship we need a unique key in each table to connect a row from one table to a row from the other table. Again for more information check out my previous blogpost on relationships.

A row is uniquely identified by the values of all the columns from a row. SSAS will make sure a row is returned once for each measure because it automatically aggregates rows. So all we need to do now is combine the values from all the columns into a new column and connect the two tables by creating a relationship.

First we create a new column we call Key in both tables using the &-sign or use the Concat function:

Then we create a relationship between the two:

Now we have prepared the the data we can use it into the PowerPivot pivottable, you can see we can use the measure from both cubes in one table:

What is important to notice is that I use columns from the table I indicated as lookup table as filter / slicer / row label. When i would have used the other table we only would have had measure from the orderamount table.

To make it a little easier to use i would recommend to hide the columns from the orderamount table in our pivottable. Go to the table, click design, click hide and unhide.

Here you can hide columns from the pivottable, in our case we only want to keep the measure value in the pivottable:

This will give us only the cleaned up colums in our PowerPivot Field list:

As you can see combining data from two cubes is not that hard, important is identifying  a key column between the two tables. You need to keep this in mind when importing data from cubes to combine them.

Leave a Comment » | Uncategorized | Permalink
Posted by Kasper de Jonge