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.

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

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.

AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE)

I'm still working on the Management Information Dashboard I previously talked about when I was Implementing Parent / Child Hierarchy. (Note: There are some Dutch words and expressions in the examples)

Now I ran into this issue when trying to calculate the number of clients of a particular type of cost center (Activiteitencentrum, Ambulant, etc.).

My implemented hierarchy is in the Organisatie table, I'm interested in the organization type Woonlocatie and I want to calculate the number of clients per Region / Cost center. I have the Region and Type (both of table Organisatie) in the row label, where Year is in my column label.

I first tried this with Measure1:

AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))

But Measure1 does not calculate the totals per region correctly, it takes all the clients into account, not only where type="Woonlocatie". Then I tried Measure2:

SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))

which does take into account the organization type in the hierarchy. I'm not all clear about why this is working correctly with Measure2 and not with 1. If anyone does know why or has something to share about this, I'd like to know.

Measure1: AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))

Measure2: SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))

[Edit]

A few months later I participated in the 2-day SSAS Tabular workshop from Marco Russo and Alberto Ferrari. After the workshop I had a better understanding why measure 2 is working correctly, please see my follow-up post.

How we see each other

Recently I came across this funny picture about how we all see each other in different kind of ways.

I can't seem to find the one who posted it, but the credits are his / hers.

Implementing Parent / Child Hierarchy in PowerPivot

We are currently implementing a Management Information Dashboard with Critical Success Factors and I ran into the problem of parent / child relationships in PowerPivot.
Since we are not working with Denali with the customer, I had to make a workaround to import the organisation structure into PowerPivot.

Special thanks to Alberto Ferrari and Marco Russo who made this possible on SQL Blog.com!

Of course not every project is the same, so I ran into a few issues while walking through the steps of Ferrari & Russo.
The first issue I had was that my ParentID and NodeID were both a VarChar (don't ask me, I don't know why..), so I had to convert / cast them as an INT.
Second, this organisation structure has the first two layers which are always the same and have no other nodes on the same level. So my structure actually starts at level 3. I edited the last select statement to leave out the first NodeID's and also changed the ComputeParentNodeIdAtLevels to not select Level1 and Level2. This worked for me.

Several measures with parent / child relations