Tuesday, February 21, 2012

PowerPivot bug?

I ran into an interested thing this afternoon when working in the PowerPivot window.
When the PowerPivot window is maximized I see the following:

So it seems all the tables (which are there) are visible.

But when I restore the window, so it is not longer maximized, the blue triangle reappears and the rest of the tables are accessible again.

I believe it is a bug, but has anybody noticed this before?
I'll try and check this with MS later when I get the chance.

Friday, February 10, 2012

Hub'n'Spoke in PowerPivot - The Core and Thin Workbooks

I'm still working on the Management Dashboard I blogged earlier on when I was Implementing Parent / Child Hierarchy and dealing with the issue with AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE).

I am now dealing with some performance issues, specially when opening and refreshing the Excel file. Another point occured when setting the auto refresh option in SharePoint. The refreshing itself is pretty fast, but when you open the file afterwards, it takes ages to load.. This has to do with the first (PP) and second (Excel) refresh of the file as Rob (PowerPivotPro) explains in his post.
When searching for solutions I came across some posts of Rob, the Core and Thin workbooks posts (1 and 2). The implementation of this hub'n'spoke model for PowerPivot is quite straight-forward with the help of Rob's posts.

In short, you have to get a copy of your workbook, change the connection from embedded to the location of the core workbook on Sharepoint. Then you can rename your thin workbook to .zip and copy an empty item1.data in xl\customData. Rename back to xlsx and your thin workbook is created.

Another performance issue I have is when opening an auto-refresh file in Sharepoint, it takes ages for the file to load, due to the second refresh where Excel has to refresh all the formulas and pivot tables. Everytime the file is opened this refresh takes place. With the Core-Thin concept this is not totally solved, because the thin workbook still has to be refreshed..
Still, I can now auto-refresh the core workbook in Sharepoint (this sets the autorefresh on open mark in the file). But the core never gets opened by the user, the thin only consults the data inside the core, so that's not a problem.
The only issue I now have is keeping the thin workbook up to date. Rob (or PivotStream) seems to have a solution for this problem, so I think patiently waiting is the only option.. :)

Tuesday, February 7, 2012

Backup failed for Server..

When trying to make a backup of a SQL-database I received the following error:
A nonrecoverable I/O error occured on file "" ... Reason: 15105

The error itself is not very descriptive and it didn't lead me in the right direction: 
finally I noticed the disk space on the (C-)drive was the problem for the backup to fail.

Learn DAX Basics in 30 minutes!

Now is the time to start with DAX: a few days ago a tutorial has been released to help you learn DAX basics quickly.
The QuickStart: Learn DAX Basics in 30 Minutes is available as part of the MS Technet Wiki, so we can all contribute to it also!
This QuickStart is for users new to PowerPivot or tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems.