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
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: 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:
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
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.
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.
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!: