Gaining Perspective on Perspectives

One of the questions that came up in the Ascend class held in (rainy) Issaquah in January:  What happens if you have the same perspective name across multiple cubes in the same database? It seems logical that it should not be possible to create multiple perspectives with the same name inside the same cube, but would that also be true if the perspectives were in different cubes in the same database? We didn’t take the time to actually try this out in class, but I surmised that it wasn’t possible. Afterwards, I tested this out once in the BI Development Studio (BIDS). I also tested by running the Deployment Utility against the .asdatabase file after I modified the file manually to add two perspectives with the same name in different cubes. Whether using BIDS or the utility, I was politely informed that the perspective name that I tried to reuse was already in use and stopped from continuing.

This behavior seems reasonable and shouldn’t surprise anyone. But perhaps a more interesting question that stems from the original question: why would you want multiple cubes in the same database? In AS2000, it was not uncommon to have multiple cubes in the same database because there was a one-to-one relationship between a fact table and a cube. Now in AS2005, you can have one cube with multiple fact tables (using measure groups) and now the one-to-one relationship is between a fact table and a measure group. The vision is now to have a “one cube fits all” solution – something I would never have believed I would find myself saying, having reeducated a lot of clients about the perils of having too much in a single cube.  To be honest, I haven’t pondered all of the ramifications of this new design approach, but as of yet can’t come up with an argument against having a single cube. I’m looking forward to following the certain debate to come regarding the pros and cons of One Cube.

So with everything in one cube, it can sure get confusing for an end user to navigate that cube. Enter perspectives to save the day. To my front-end tools of choice, the default perspective of the cube (which includes everything) appears side-by-side with any additional perspectives that were added to the cube. The idea of perspectives is to filter a cube to show only dimensions with measure groups that make sense together – whether oriented around subject matter or end user roles. It’s important to note that there is no security on perspectives - cube and dimension level security will be honored, but you can’t limit certain users/groups to certain perspectives. BOL explicitly states that perspectives are not intended to be part of the security architecture. By pointing this out, I provoked a stimulating conversation on the subject of security in the class. The bottom line is that the analogy between virtual cubes and perspectives falls apart when it comes to security. Be sure to consider the impact of this constraint when you plan the migration of your AS2000 cubes.

-- Stacia