Posts Tagged powerpivot

Powerpivot Gallery – Snapshot Creation Error – IE9 culprit!

When uploading a PowerPivot model into an existing SharePoint 2010 Powerpivot Gallery, I received the infamous “an error occured while capturing snapshots” error, where the hourglass changes into a disturbing red cross.

Quite odd seeing as I had used this exact library in the past and successfully created snapshots.

Now, there were errors that were quite prevalent in the CTP builds of SharePoint and related to security and underlying services. So, I used this thinking according to Dave Wickert’s blog, And my server passed all of the checkpoints, i.e. everything was setup correctly. I did check the ULS logs, and only found the generic Timeout Expired error.

So, in a move of desperation, I uninstalled Internet Explorer 9 Beta.


And the snapshots started working again!

Another reason not to experiment with IE9 just yet… g


, ,


PowerPivot DAX Session Notes 2 – Previous Row

Those that are familiar with PowerPivot and DAX, will be aware that DAX provides the ability to reference columns.
However, it does not provide support for Cell based references (such as A1, E23).
This is normally no big issue, until I hit a small design issue this week… I had the requirement to refer to a previous row of data within the PowerPivot model.
And yes, it is possible, using the EARLIER DAX function.
Let me talk through the data quickly… I received some stock futures trading data, where each trade had two rows. One row would be the opening position at a point in time, and when the future actually closed, a second row would come through with the closing position. The profit made would be the difference between the opening position and the closing position.
Something like the following table…
The basic process to solve this problem was to do the following:
1. for every row, determine the Previous Date that applies to this particular Client Code.
2. Once the Date is established, determine the Value at that point in time.
3. Profit is then simply Current Value – Previous Value for CLOSE rows.
1. Using the following DAX statement, I could calculate the [DatePrevious] column

=CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[Date])>Table1[Date])))

Note: I’m still coming to grips with the EARLIER function, seems to work semantically the same as an INNER join… will hopefully post a better explanation when I understand some more…

2. Using similar thinking, I could then join back to [Table1] and return the Value at the point in time established in the previous step.

(I would love any feedback if it’s possible to merge this with the previous step, may be more efficient)

=CALCULATE(SUM(Table1[Value]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[DatePrevious])=Table1[Date])))

3. Finally, Profit is then just the difference between the current value and the previous value for the CLOSE rows.

=if(Table1[Position]="CLOSE", Table1[Value]-Table1[ValuePrevious], BLANK())

Results in the table below.




PowerPivot DAX Session Notes 1 – Dates

Part of my preperation for TechEd Africa this year has required me to really get into PowerPivot and DAX, and I thought I’d share some of the lessons learnt along the way…
So, in standard Analysis Services models, the concept of a Date dimension is quite accepted… and the world of PowerPivot is not much different.
Part of creating this type of Dimension / Lookup table involves adding attributes like ‘Month’, ‘Year’, ‘Day of Week’ to a given date. Given the date expressions in Excel, it is quite straight forward to add these attributes using these standard Excel functions.
One that I didn’t find out the box though was a MonthName(<date>) function.
So, approaches to getting this right are:
1. Using a massive switch / case statement; yuck 🙂
2. Using a lookup table of sorts to map MonthNumber to MonthName; less yuck.
3. Use Excel date formatting features to achieve the same result… (and yes, I only learnt about this this morning, always more to learn)
 – =TEXT([@Date],”mmmm”)
Another common hinderance is the sort order of months.
By default sorting is alphabetical, meaning April suddenly appears before February. (and oddly enough this does confuse end users!)
Again, the more SQL based approaches would involve injecting some kind of number into the month name and sorting accordingly. (But 02-February is also not so user friendly on reports!)
Fortunately, Standard Excel sorting functions understand Months. So, if you sort the months A-Z, Excel will sort correctly.

, ,

Leave a comment

Excel 2010 – Save & Send to SharePoint Issue

So, I have just finished installing (yet another) BI 2010 Virtual Machine, on Windows Server 2008 R2 RTM.
On this one, I was unable to use Excel’s “Save & Send” feature to publish a workbook to Sharepoint.
When I tried to use the “Browse for a Location” dialog, I would type in the Sharepoint URL, and got a message along the lines of “this application can’t open this location”.
Quite odd.
Turns out that you need the Desktop Experience feature turned on Windows Server 2008 R2 to allow this to work.
Thanks Alan Whitehouse!

, , ,

Leave a comment

PowerPivot – Compression Stat

Today I found myself needing to load a .csv file into powerpivot.
This .csv file has around 11M rows in it, 45 columns, fully denormalised, and is around 3,4G.
So, to test and validate Microsoft’s claims that the Vertipaq engine does a decent job at compression, I loaded the .csv file as-is directly into a PowerPivot model.
And yes, Microsoft were right. The same data in PowerPivot compressed to a 140M .xlsx file!!
That’s a compression ratio of 4.1%!!

Leave a comment

SAP + Microsoft BI: 6 Scenarios to set that data free!

The Microsoft BI front end tools provide a fantastic mechanism to create reports, dashboards and free form analytics. So, how do we use these tools when the underlying data is provided by SAP, either R3 or BW?

Well, I’m in a session at the Microsoft BI Conference this year with Scott Cameron, and he’s discussing some scenarios on how to get data into PowerPivot. These techniques are quite generic, and can allow data to be exposed and consumed by a number of front ends, namely…

  1. Excel 2007 / 2010
  2. Reporting Services 2008 / 2008 R2
  3. Data Dumps
  4. Third Party Providers
    1. Theobald Xtract PPV
    2. ERP-Link iNet.BI
    3. Simplement Data Liberator

Interestingly, only 2 of the techniques involve Microsoft-provided drivers and techniques. The others are either custom data exports and imports, or third-party providers. Let’s discuss them a little further.

Scenario 1: SAP BW à Excel 2010

Excel 2007 / 2010 natively supports browsing and reporting on SAP BW infocubes. No need for additional add-ins, or 3rd party software. Only requirement is the install and configuration of the SAP GUI front end.

This SAP GUI front end provides two things:

  1. SAP BW OLE DB Provider
    1. limitation here is that only supports 32 bit Excel.
  2. SAP Login (Description and Client Number)

Once the driver has been installed, and we’ve used SAP GUI to create a SAP Login; we can then use the Get External Data wizard in Excel to connect to SAP BW, in a very similar manner to connecting to Analysis Services cubes. Some differences though:

  1. When connecting to external data, select Other Data Sources
  2. Select the SAP BW OLE DB Provider
    1. Again, if it’s not there, it’s probably a 64 bit machine ;(
  3. Data Source = Description of the SAP Login created in SAP GUI
  4. User Name and Password are the SAP credentials
  5. Under the All tab, add an Extended Property for the Client Number, SCF_CLIENT = <Client Number>

And you’re done!

Check for further details.

I like this approach personally because I am quite a fan of Excel to analyse data. It also allows re-use of the existing BW cubes, and no data needs to be physically moved between systems.

Scenario II: SAP BW à Reporting Services

SQL Server 2008 and SQL Server 2008 R2 allows reports to be written directly against SAP BW data sources.

These reports use the Microsoft .Net Provider 1.1 for SAP NetWeaver Business Intelligence. This driver is installed as a part of the Client Tools install on the SQL Server install media.

To enable these queries on the BW Server, you need to enable the QueryCube for external access. (This is done through the SAP BEX Query Designer)

The connection string is the XML/A connection URL. This URL allows for the passing of parameters (as the dataset itself doesn’t support parameters) Once connected, the report design experience is again similar to reporting off of Analysis Services.

Again, a neat method to create reports, as tools like Report Builder 3.0 do allow more people to create and consume these reports. And the data is not physically moved into a staging environment.

Check for further details.

Scenario III: SAP Open Hub à CSV File

SAP provides an Open Hub license. This allows data to be exported. These data exports are natively written to CSV file, however it is also possible to export directly to SQL Server (which would be preferred!)

Clearly, this is the most manual method to get to the data. The biggest negatives here are

  • the security is lost in translation
  • the Open Hub license is not a trivial purchase from my understanding

Scenario IV: Third Party Providers

  1. Theobald Xtract PPV
    1. Very neat software. Basically creates a data feed that PowerPivot can consume to directly import data. (Without having to use the “traditional” reporting services middle tier.
    2. Check for further details
  2. ERP-Link iNet.BI
    1. Provides a host of tool that use a .Net Data Provider to extract data from SAP.
    2. Interesting one is the Data Source View Creator, as it creates an Analysis Services cube. (which I’m a massive fan of)
    3. Check for further details
  3. Simplement Data Liberator
    1. A replicator to move data from SAP to SQL in real-time
      1. (Most SAP administrators are nervous to do this, as you need to enable replication in SAP)
    2. Neat solution to create a fully-fledged SQL Server data warehouse
    3. Check for further details


, ,

Leave a comment

Thoughts when Installing PowerPivot – Client

So, I’m in a session at MS BI Conference 2010 with Dave Wickert, and he’s doing a great job of working us through things to look out for when installing the PowerPivot client.

This is an Add-In that runs in the context of Microsoft Excel 2010. Any version of Excel that supports add-ins will support the PowerPivot add-in (which is almost any version!)

The download is available from

Make sure you download and install the version that correlates to the version of Excel (not your Operating System). So, basically 32-bit Excel = 32-bit Add-in (regardless of 64 bit OS) And yes, you want to use the 64-bit version if at all possible. It allows you to create and manage larger datasets, any size that fits in RAM will work.

However, please note that only 4G files can be saved to disk, and only 2G files can be uploaded to Sharepoint. Yes, only J

Preconditions for the Add-in to work include:

  1. VSTO installed and running
    1. The Complete install of Office manages this by default
    2. If installing only Excel, be sure to select “Shared Components” as well to include VSTO
  2. The PowerPivot add-in is enabled. (once installed)
    1. Check under File – Options – Addins
    2. Ensure that the add-in is not in the Disabled State
  3. If the add-in is still gone, even if both are ok, it is possible to expose the VSTO exceptions on Excel start up
    1. Create an environment variable, VSTO_SuppressDisplayAlerts, and set to 0
    2. Check for full details

Debugging options for the client add-in can be set through PowerPivot à Settings. It’s possible to log events off to a log file, which can be opened and analysed in SQL Server Profiler. Also possible to create a snapshot of the current memory usage, by clicking “Snapshot”.

The settings window is also the equivalent of HelpàAbout in most apps. The RTM current version of the add-in is 10.50.1600.1


Leave a comment

BI Conf 2010 – PowerPivot Management Dashboard

PowerPivot provides a feature in SharePoint Central Admin, where administrators can see the usage history of published PowerPivot workbooks. This PowerPivot Management Dashboard is essentially a custom web part web page that shows a number of views on the current state of the system, including:

  • Infrastructure – Server Health
    • Query Response Times
    • Average CPU Usage
    • Average Memory Usage
    • Activity
    • Performance
  • Workbook Activity – Chart and List
    • This is the killer time based chart showing animated history of workbook activity
  • Data Refresh – Activity and Failures
  • Miscellaneous Reports
    • Essentially a document library that has been exposed through a web part

Generally, this dashboard is quite fixed, both in terms of layout and content.

Elements that cannot currently be updated:

  1. Overall Layout. The “dashboard itself is not written in something like Performance Point, so the layout and content can’t be changed.
  2. The Silverlight Control. Sigh. There are no plans to produce a separate reusable bubble chart control. Bit of a pity, would be great to embed in other dashboards. Maybe next release J

Elements that we can update:

  1. Any of the Excel based workbooks, including the Workbook activity book
    1. These workbooks are in themselves PowerPivot workbooks, which feeds data from the PowerPivot Shared Services Database. (The Usage schema tables)
  2. The Reports document library (bottom right section of the dashboard.)
  3. This is simply a reference to a document library in All Site Content à PowerPivot Management à Instance ID à 1033


Leave a comment

Business Intelligence – ala Microsoft 2010 – Part I

For those that haven’t heard all the news yet … this year is going to see some of the most exciting product launches from Microsoft; particularly in the Business Intelligence space.

Now, Business Intelligence is a far bigger
concept than simply a sexy front end tool, or a super quick ETL tool. Rather it is that sum of the parts that creates a magic whole, and hopefully helps business people grow their businesses.

To support this, the Microsoft Business Intelligence picture includes three main products, all of which are getting new versions in 2010; namely

  • Office 2010
  • Sharepoint 2010
  • SQL Server 2008 R2

I’ve had the opportunity of playing with all of these products in pre-Beta and now in Beta releases, and have seen them improve with each new release. The key new features in each of these products that have got me the most interested include:

  1. Excel Slicers for Pivot Tables
    1. No more horrible unrelated drop down lists in Excel. Slicers really open up your data in an intuitive and ‘touch-ready’ way.
  2. Reporting Services Map Control
    1. Wonderful method to display geographical information, within the context of Reporting Services. No custom code. No messy data connections. Flexible, and very useful.
  3. Master Data Services
    1. One of the features that gets very little attention in the SQL space; but has great potential to help businesses manage their critical business data. No more messy Excel spread sheets, or phone calls to IT to update that mapping table.
  4. Decomposition Tree
    1. Still one of the most powerful visualisations for performing root cause analysis. Available within PerformancePoint Services. (Would love to see it introduced into Excel as well…)
  5. Powerpivot
    1. No Microsoft Business Intelligence blog would be complete without a reference to this wonderful in-memory tool. A plug-in into Excel 2010 as a fully-fledged modelling tool, as well as a Sharepoint feature allowing for online access and sharing. A must have for every financial manager J

These are just a handful of the vast array of features that are coming from Microsoft this year. I would encourage you to download the public betas and start becoming familiar with these products.

Sharepoint 2010 – download

Office 2010 – download

SQL 2008 R2 – download

, , ,

Leave a comment

SQL PASS: DAX, the language of PowerPivot

PowerPivot, the topic that has been getting all the big attention here at PASS, has introduced ‘yet another’ expression language to define calculations. So, introducing DAX, Data Analysis eXpressions.

The idea behind this language is to provide OLAP type query functionality to the Excel end user. So the basic syntax looks and feels exactly like standard Excel functions. A set of Excel functions have been implemented (80 in total), with additional functions from the Vertipaq engine to allow OLAP functionality.

Ed Note: The additional functions in DAX actually come directly from the VertiPaq engine. Meaning that it is not a wrapper for MDX, it is actually the language used to define and execute the calculation within the engine.

DAX can be used in two places

  • Calculated Columns
    • Similar to creating measures in fact tables, or in the DSV in SSAS. Basically used for line-by-line calculations. These values are calculated and stored at design time, and not recalculated at execution time.
  • Measures
    • Similar to Calculations in SSAS. Calculated at run time, in the context of the current pivot table or query. Best place to define ratios or custom aggregations that don’t make sense in a line-by-line method.

IMHO, the single most important aspect of this language is the underlying relationships defined within the PowerPivot model. As most calculations require some form of navigation between tables, whether it be date calculations or aggregations over groups.

The basic groups of functions that are supported include:

  • Excel Functions
  • Relationships
  • Aggregation
  • Table Functions
  • Time Intelligence

The Time Intelligence ones are by far the most fun ones for me. it is possible to define MDX-type calculations including Year on Year growth, Period to Date and Parallel Period navigation. A bit of a different model though, as the “time dimension” is assumed based on date fields. Only limitation is that it assumes calendar time.

e.g. YOY Growth looks like

Fact([Sales]) – Fact([Sales])(DateAdd(Time[Date], -1, YEAR)

Overall impression is very positive. Full descriptions available at


Leave a comment