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.

PowerPivot-32bits-memory-error-allocation-failure-full

PowerPivot-32bits-memory-error-allocation-failure

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

“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:

Excel-32bits-task-manager

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: InstallPowerPivot.com


DAX examples (Excel)

Found this great download on Microsoft.com

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.

Overview
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:

PowerPivot-Map-Example

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

PowerPivot-Map-Image

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

PowerPivot-Map-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!:
linkedin-microsoft-power-bi-group