In Tabular projects in SQL denali CTP3 it is now possible to have more then 1 relationship between tables using the same attribute on the corresponding lookup table.
The question I want to answer in this blog is “Does a role played dimension in a tabular project offer the same functionalty as it does in a multi dimensional project?”
Role played what?
Example: Each transaction in a fact table has a order date and a ship date. Both attributes refer to the same table (dimension) date (DimDate).
An OLAP project would creates an user interface displaying the two dimensions:
- Order date
- Ship date
Phyisically they both represent the same base table DimDate. The user, however, sees the DimDate tabel in two different contexts; order and ship date.
A Tabular project offer the same kind of functionality, but is different in both technique and in the way the user eventually navigates the model. How this is done and what this is, is explained here by Kasper de Jonge.
An example to illustrate
In our earlier example a user might want to know the sales amount by order and ship date. Using the classic Multi Dimensional approach you would see the order date on the rows and the ship date on the columns (or vice versa). This would result in a classic matrix report.
Using the Tabular approach you would have to create a second measure like “Internet sales amount shipped” (again; really read this first). You would then place the two measures on your report putting the date dimension on the rows resulting in:
I think that in many scenario’s this last approach is preferable. However, I can think of one in which I would prefer a matrix overview. Let’s imagine a magazine company, they have a marketing department which send out different letters, packages etc. The goal is to get people to subscribe to their magazine. Each payment (transcation) they receive is tagged so that the company can track which campaign resulted in each payment. The tagging is done automatically, the thing you want to investigate is if the “tagging” process is working correctly. A matrix report in which the campaigns created are on the rows and the campaigns to which each transaction is tagged are on the columns, would help.
In spite of what you might expect I am not going to say that you should use one project type or the other (Tabular or Multi Dimensional). As always it depends on a lot more variables, like required flexibility, frequency and maintainability. In the scenario that I described a standard report would be perfect since they would frequently use the report but dont require much flexibility (the report would not change very often). However, it is good to know what tabular and multi dimensional projects support best. I recommend becoming a master of both so that you can advice the right solution. More to come on this subject…