SQL Server 2005 has a new version of Analysis Services with a lot of new features, so much that a whole (thick) book would be necessary to describe everything. Nevertheless, many improvements are “marginal”, in the sense that bring us better productivity (= less development time) or better performance. Aside administrative and development features, there are not so (very) much end user features so important for him to convince to jump into the new release (even without changing every client…): Analysis Services 2000 is a great product and if pushed to the limits can resolve a lot of business problems, you only have to know the product very well and have to be ready to do some stunt.

Anyway, Analysis Services 2005 (AS2005 for shorter) has some feature that is revolutionary. One of those is the many-to-many dimension.

This is the business scenario: you have a fact table that describe a fact measure (the account balance at a certain date) for an entity (for a bank account) which can be joined to many members of another dimension (the many owners of a bank account). Who knows the multidimensional model already see the trouble, beacuse it’s not easy to describe the non-aggregability of measures joined to dimensions with a many-to-many relationship (in this case, each bank account can have one or more owners and each owner can have one or more accounts).

With AS2005 the trouble, simply, disappears. The “trick” is to use an intermediate fact table that, in the relational model, indeed defines the relationship many-to-many. In the following figure fact tables are yellow and dimension tables are blue. Note that DimCustomerAccount is considered a fact table.

When you define relationships between dimensions and measure groups (a measure group is similar to a real cube of AS2000, while a AS2005 cube is more like a virtual cube of AS2000), you specify that the Customer dimension is joined to Balance with the dimension DimCustomerAccount (it’s the selected item in the following figure).

The relationship is further described by the following dialog box that you obtain with a click on the button contained in the selected item of the previous image. This dialog box is available for every combination between dimensions and measure groups and it defines the type of relationship.

Here is the result. In test tables I created 5 customers (Luca, Marco, Paolo, Roberto e Silvano) and 9 accounts (numbered from 1 to 9). Each account is joined to one or more customers and the balance for each account is always 100.

As you can see, for each customer you can identify accounts he owns and for each account you can see the balance repeated for each owner…. but the total for each account is always 100 (Grand Total row) and the balance for all accounts is 900 (100 * 9). Try to do that with any other multidimensional model (and I’m not talking only about AS2000) and see what happens… The following image synthetize the non-aggregability of some measure in respect of some dimensions.

You can obtain the same result with AS2000 but only with some stunt and some tradeoff in terms of processing time or query performance. Who, like me, already experienced similar issues, can’t wait to upgrade to SQL 2005 in a production environment just only for this feature…