New visualizations in Excel 2016

Scott Ruble demoed a few new visualizations in Excel 2016 on the Microsoft Ignite conference:

Forecast Worksheet

Box & Whisker



Pareto (80/20-rule)



Office 2016 Preview
Want to play around with the Office 2016 preview right now? You’re able to find more info about it here.

GeoFlow: 3D Mapping Analytics Tool for Excel

Today is yet another wonderfull day, Niels Naglé shared a blogpost about Excel 2013’s new add-in called “GeoFlow” (Codename)

The full blogpost on SQL Server Blog can be read here.

Today we’re pleased to add another exciting business analytics tool to help customers gain valuable insight from their data. Project codename “GeoFlow” for Excel, available in preview now, is a 3D visualization and storytelling tool that will help customers map, explore and interact with both geographic and chronological data, enabling discoveries in data that might be difficult to identify in traditional 2D tables and charts. With GeoFlow, customers can plot up to a million rows of data in 3D on Bing Maps, see how that data changes over time and share their findings through beautiful screenshots and cinematic, guided video tours. The simplicity and beauty of GeoFlow is something you have to see to understand – check out the video demo and screenshots below. You can also download and try it out firsthand today.






For more information on GeoFlow, check out the Excel team’s blog and visit the BI website.

Download the GeoFlow Preview Add-In for Excel 2013 here.

Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:

Interactive PowerPivot Dashboard (custom map visualization)

I’ve found this great example of an interactive PowerPivot worksheet by Robert Mundigl about the beer prices at Munich’s Oktoberfest.


How it works?
Actually it’s about the same technic as Kay Unkroth used earlier in one of my previous posts.
Well…here’s how Robert did it!:


Have a look at Robert Mundigl‘s blogpost and his tutorial: Click here.

The example is also downloadable via this URL.

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

Slowly Changing Dimensions (Type 6) the basics

Preserving historical data in a Data Warehouse (DWH – Business Intelligence)
Slowly Changing Dimensions (Type 6)

For business users or data analysts that directly access data marts in Excel or directly from the database/cube, it can be hard to understand how Slowly Changing Dimensions (SCDs) work.
To make things easier, I wrote this small ‘whitepaper’ about the In’s and Out’s of Slowly Changing Dimensions.

Let me know if I’ve missed anything 🙂

Preserving historical data in a Data Warehouse

PowerPivot Memory error: Allocation failure

Did you receive this error message from PowerPivot?

Memory error: Allocation failure : Not enough storage is available to process this command. .
The operation has been cancelled.



Well…you’re probably using PowerPivot’s 32-bits version, more info:

“The PowerPivot add-in runs as an extension of Excel and the PowerPivot Vertipaq in-memory engine loads within the same process space. As 32-bit Excel is limited to a 2GB virtual address space. Once you start adding up all of the uses of those virtual addresses, you will find that the largest PowerPivot workbook that you can create on a 32-bit machine is something like 500-700MB. Excel, all of the add-ins, the in-memory database itself and all of the rest just takes up lots and lots of space.” by Dave Wickert

Open Task Manager and locate Excel:


In my case, it’s not causing any problems since I’m not near the 500 to 700 MB of memory usage.

What shall I do?
– Close all other PowerPivot workbooks which are active (if there are any)
– Reduce the imported datasets/tables by applying filters (in the Table Import Wizard)
– Close Excel (and all PowerPivot workbooks) to clear the memory and re-open that particular PowerPivot workbook
– Install the 64-bits version of PowerPivot (also requires a 64-bits version of Microsoft Excel), more info:

DAX examples (Excel)

Found this great download on

DAX in the BI Tabular Model Whitepaper and Samples

This whitepaper and sample workbook introduce Data Analysis Expressions (DAX), a formula expression language used to define calculations in PowerPivot for Excel® workbooks and Analysis Services tabular model projects authored in SQL Server Data Tools.

This DAX in the BI Tabular Model file contains two files:
– Contoso Sample DAX Formulas.xlsx PowerPivot for Excel workbook
– DAX in the BI Tabular Model.docx whitepaper

The PowerPivot workbook illustrates the data analysis expressions (DAX) discussed in the included whitepaper.

The Contoso DAX Formula Samples file contains one file:
Contoso Sample DAX Formulas.xlsx PowerPivot for Excel workbook

NOTE: To fully interact with this sample, you must have Microsoft Excel 2010 and the Microsoft PowerPivot for Excel add-in installed or SQL Server 2012 Analysis Services instance running in Tabular mode and Visual Studio 2010 with SQL Server Data Tools installed.

Want to download it? Click here.

PowerPivot Bing Maps look-a-like

I recently found this blogpost on how to create a Bing Maps look-a-like in PowerPivot and it’s actually quite easy.
Here’s how it looks like in PowerPivot:


It’s an transparent image that lies on top of the cells in Excel:


And here’s how it looks like once it’s deployed to SharePoint:


Want to know how he did this?
Well have a look at the blogpost, click here!

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

Microsoft’s Mobile BI platform – Project Helix?

At the SharePoint Conference last SQL PASS, there was apparently a demo on Microsoft’s long awaited Mobile BI solution/platform (based on SharePoint 2013?). It supposed to be developed under the name ‘Project Helix‘. Just Blindbaek tweeted about it during the SharePoint Conference and thanks to Chris Webb…the rest of the community knows 🙂
But! Is it a hoax? A commercial app made by the company Mobile Helix Link??

Excel: AutoFit Column Width format functionality

I’m sure you guys have seen this before when copy/pasting datasets into Excel (probably for the business?):

Well there is great format functionality in Excel 2010 (propably also in 2007) which I didn’t know:

Ok, first select the entire Excel-sheet by hitting CTRL-A.
After that….you’ve probably guessed it already…hit the AutoFix!

The result after:

That really saved me a lot of time 🙂

Excel Spatial Visualizer Add-In

My colleague Karlo Bartels made a very nice Excel 2010 Add-In:

To easily facilitate the process of displaying spatial objects in Excel, I created an Excel add-in that allows you to select a range of cells containing coordinates and show them on a map. To do this, the add-in accesses the Bing Maps SOAP Services. This is a set of programmable SOAP services that allow you to match addresses to a map (and much more). You will need a Bing Maps Application ID to access these services.

Check out his blog/article here (source and examples included!).