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

Initializing BitVector32 to True

When studying for my 70-536 exam (.NET Foundation) I came across the following:

Question:
You are developing an application to assist the user in conducting electronic surveys.
The survey consists of 25 true-or-false questions. You need to perform the following tasks:
- Initialize each answer to true.
- Minimize the amount of memory used by each survey.
Which storage option should you choose?
A. BitVector32 answers = new BitVector32(1);
B. BitVector32 answers = new BitVector32(-1);
C. BitArray answers = new BitArray(1);
D. BitArray answers = new BitArray(-1);

Ok, BitVector32 is preferred above BitArray, but I wasn't aware of the fact how to initialize all the values to true, I would have chosen A.
But then only the first answer would be true. So the correct answer is therefore B (BitVector32(-1)).

Manual sorting in PowerPivot

When you have your own row labels and would like to sort them in your own order you can create a set and have them always in that order, also after refreshing the data.

This is the starting point, we have (string) labels of the length of an employment. PowerPivot sorts them in this order, from 0 - 1 - 2 to 5.

What we would like to have is this:

This can be achieved by creating a Set for the Row Labels. You can find the option on the (PivotTable Tools) Options ribbon, under "Fields, Items & Sets".

After selecting "Create Set Based on Row Items..." you can sort the items manually and give the set a relevant name.

After creating the set it also appears in the PowerPivot Field List under Sets:

Solution to Error Code: 0x8004d00e (BizTalk application installation)

It's been a while since I've written a post due to holidays and busy days..
Well, here's a new post about a problem I ran into when installing / configuring a BizTalk application.

I was installing an application in a DTAP environment and came across the following error message when I tried to start the application (in Acceptation):

xlang/s engine event log entry: NullReferenceException exception occurred while the XLANG/s runtime enlisted a service.Error message:Object reference not set to an instance of an object.

And:

Unable to communicate with MessageBox BizTalkMsgBoxDb on SQL Instance xxx. Error Code: 0x8004d00e. Possible reasons include:  1) The MessageBox is unavailable. 2) The network link from this machine to the MessageBox is down. 3) The DTC Configuration on either this local machine or the machine hosting this MessageBox is incorrect.

After trying several blogs and forums, also with Error Code: 0x8004d00e I didn't find anything useful.

Then I saw two different versions of each dll in the GAC (MSIL and x86).

When I checked my DEV, I noticed the platform was set to "Any CPU", which had to be x86, because the A & P environments are running in 32 bit. After making a new MSI and importing it, I didn't have any issue anymore with starting the application. Another lesson learned!

The Future of Middleware and the BizTalk Roadmap

Tony Meleg said some interesting things about BizTalk Server at the Microsoft Worldwide Partner Conference in LA. You can see his whole presentation / video here.

Another blogpost I found interesting was from Richard Seroter, which is called Is BizTalk Server Going Away At Some Point? Yes. Dead? Nope.
His most important conclusion is "Is BizTalk dead?  No.  But, the product named BizTalk Server is likely not going to be available for sale in 5-10 years.  Components that originated in BizTalk (like pipelines, BAM, etc) will be critical parts of the next generation integration stack from Microsoft."

Monitoring BizTalk with BizTalk360

When I was attending the last BizTalk User Group meeting in Almere the 29th of June, it was the first time I heard of this product: BizTalk360. It describes itself as "World's first web based BizTalk production support / monitoring tool". It immediately got my attention.
At the BTUG, Saravana Kumar was representing Kovai Ltd., the company behind BizTalk360. The funny part about this is, just a few hours ago I saw that his Preparation Diary for BizTalk 2006 R2 helped me get that MCTS! Thanks for that :-)

Now on to the product, it's like the BizTalk Admin Console, but then much better.

BizTalk360 Environment dashboard

- Environment dashboard: an overview of all the properties, you could compare this to the Group Hub page.
- Fine grained authorization: users can be given different access rights to the dashboard (read-only, certain applications, various parts of the dashboard).
- Topology diagram: this automatically provides the physical network topology of the BizTalk group. It recognizes BizTalk and SQL-servers and wether it is a web / database / application server.
- Governance / auditing: all the key activities performed by users can be taken care of. You never have to wonder who stopped that Send Port or who restarted the Host Instance.


Next to that, there are also the Advanced Event Viewer, the BizTalk Server dashboard, the fact that it is web-based and lots of other things. I won't go explaining all the features, you should just check it out yourself. Or, if you don't have any development server set up, you could also try their Demo, which is made possible through Windows Azure with the AppFabric service bus.

As we are just in the middle of getting to manage more BizTalk applications, BizTalk360 could have come at the right time and the right place. I am definitely going to have a look into the dev-edition and see what I can get out of it. I think our administrators would love to use it too.

MS SharePoint Server 2010 SP1

I'll start my first real post on this blog with the following: a few weeks ago Microsoft released Service Pack 1 for SharePoint Server 2010. So why is it important for PowerPivot? It drastically improves performance for PowerPivot users!
In SharePoint 2010 Service Pack 1, members of Excel Services were able to cut the number of ECS query requests nearly in half, which means half the load on PowerPivot application servers per user action and therefore almost double the PowerPivot scalability.







You can find more details about the Service Pack here, you can also download it directly here.

Disclaimer added

I think it would be smart to write a disclaimer for my blog, so I took some examples from Danny Foo and put together my own disclaimer.

New blog added

I started this blog today and I'll be posting to it on a regular basis.
The main things I'll be talking about will be BizTalk, Business Intelligence with PowerPivot and related topics.

Please feel free to distribute this blog to colleagues and friends.