SQL BI

The many-to-many revolution: paper finally released!

After a month of editing, I finally released my paper titled "The many-to-many revolution".

I copied in this post the introduction of the 84-page paper that is published (for free!) on a dedicated dimensional modeling page of SQLBI.EU web site.

I would like to receive feedbacks about the paper. You can use mail, comments or the support forum on SQLBI.EU.

The many-to-many revolution - Introduction

Analysis Services 2005 (SSAS 2005) introduced the capability to handle many-to-many relationships between dimensions. At a first glance, you may tend to underscore the importance of this feature: after all, Analysis Services 2000 and many other OLAP engines do not offer many-to-many relationships. Yet, its lack did not limit their adoption and, apparently, only a few businesses really require it. However, as this paper shows, the UDM (Unified Dimensional Model) can leverage many-to-many relationships helping you to present data from different perspectives that are not feasible with a traditional star schema. This opens a brand new world of opportunities that transcends the limits of traditional OLAP.

We will explore many different uses of many-to-many relationships that give us more choices to model effectively business needs, including:

• Classical many-to-many
• Cascading many-to-many
• Survey
• Distinct Count
• Multiple Groups
• Cross-Time
• Transition Matrix
• Multiple Hierarchies

Although you do not have to do so, I recommend you to read the models in the order presented above, because often each one builds upon the previous models.

Each model has a brief introduction, followed by a business scenario that may benefit of its use and an explanation of its implementation. Each model uses only the minimal set of dimensions that are necessary to explain the concept behind it and a small dataset that demonstrates the underlying behavior.

Only the Distinct Count scenario contains a section discussing the impact on performance. Since the considerations presented there may be applied to other many-to-many relationship uses, I recommend you read this scenario if you are interested in performance evaluations.

An important warning has to be made if you are going to use VisualTotals MDX function (directly or through an OLAP browser): visual totals apply only to one level at a time with many-to-many dimensions. In the Links section, you will find a link to a document written by Richard Tkachuk that explains this limitation.

Download the complete paper and demo here.

Refresh of a linked dimension in SSAS

I started to evaluate the use of linked dimension across different database on the same server.

I encountered the first issue after a few minutes: when you link a dimension, VS editor create a dimension file that copies the structure of the dimension, without details on binding to data source (which are useless in this case). If I add an attribute to the original dimension, I am unable to use the linked dimension: the worst part is that I need to remove and to recreate the linked dimension on the project, losing all references to the dimension in all measure groups. At this point I need to recreate dimensions in cubes and dimension relationships for all measure groups.

It would really useful a "refresh" function that do automatically what I do to workaround the problem (I use the Customer dimension as an example):

1. Preparation. Create the dimension Customer on database A (solution A)
2. Deploy and process database A
3. Create the linked dimension Customer on database B (that points to A.Customer) (solution B)
4. Create a cube on B that use Customer
5. Deploy and process database B
6. Create a copy of solution B and name it database C (and solution C) - we will use it as a tool without deploying it to SSAS
7. Change the original dimension. Add an attribute (for example Country) to the dimension Customer on database A (solution A)
8. Deploy and process database A
9. Make the change on linked dimension. Delete dimension Customer from database C
10. Recreate linked dimension Customer on solution C that points to database A
11. Open Customer.dim on solution C with the View Code function, Select All and Copy to Clipboard
12. Open Customer.dim on solution B with the View Code function, Select All and Paste from Clipboard
13. (as an alternative to 11 and 12, you can copy the Customer.dim file from solution C to solution B)
14. Deploy and process database B

This workaround works if you only changed attributes that are not directly referred in the cube file: it happens when you use an attribute as a reference to another dimension or to a measure group. Most of the time dimension modifications does not touch those attributes and this workaround allows you to avoid the tedious and error-prone work of recreating dimension references to measure groups in the cube.

I would like to get feedback from people that already user linked dimension in a working (and changing!) environment.

How to handle schema in Visio database modeling

Alberto Ferrari wrote a nice post with a workaround to generate tables in different SQL Server 2005 schemas starting from a single Visio document. The trick includes a SQL script to do the magic.

I hope that next Visio version would be able to do that in a simpler way.