The term “Unified Dimensional Modeling” comes from the fact that meta-data in SSAS 2005 is much richer than it was in AS 2005; it is actually rich enough to support the complete data warehouse schema in a single cube. The AdventureWorksDW demo project shows this concept: a single cube includes multiple star & snowflake schemas representing a complete DW.
Although this concept seems very attractive at the first moment and was marketed extensively, I think it is a bad practice. When you have a hypercube like this, you have to do two things. You have to focus your end users to their areas of interest; for example, you have to focus sales analysts to sales part of the hypercube. In addition, you have to secure the data; you have to prevent the same sales analysts from seeing human resources data, for example. You can achieve first task simply with Perspectives. A perspective serves like a view in a relational database, although it is a kind of multidimensional view. However, unlike a view in a RDBMS, a perspective is not a securable. You cannot set up permissions on perspectives; you have to grant permissions on underlying physical objects. In my opinion, this is a big flaw in design of SSAS 2005. This makes a hypercube like AdventureWorksDW completely unusable. Do you really want to deal with dimension and even cell level security?
My advice is creating multiple smaller cubes from a single data warehouse. You can still take advantage of multiple measure groups per cube. For example, in your Sales cube, you can combine the actual with the planned data, and in addition, you can add some interesting data from Finance part of the DW. Nevertheless, you should design your cubes to cover a single business area only; one for Sales, one for Finance and one for Warehouse, for example. This way you have a good chance for very simple administration. Probably you would be able to manage permissions on cube level only. As a side effect, you get also easier change management, because you can do it piecewise.
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018