One Cube, Different Dimensions, Different Data – with MDX
The previous business scenario (different sums by different dimensions based on UserName function) can be implemented with MDX only. Just to remind the business requirements: users login to cube, they all have access to different departments (can one or many) and their data, users must be assigned only to one Master department, they can see overall VisualTotal when data sliced by any dimension but Merit, they can see only subtotal of VisualTotal (excluding Master department) when data sliced by Merit dimension in any variation. To support MDX-only solution, relational schema needs to be modified. DimLogins table now consists of 3 columns: LoginKey, LoginID, AccessToMerit. LoginKey is a primary key; LoginID holds user Login ID and AccessToMeritData holds one of the 3 values:"None", "Full" or "Partial". "None" – no Access to Merit data completely means when data is sliced by Merit general message "no access" shown. "Full" means full access to the Merit data including Master department, Partial - partial access to data (excluding Master department sub-total from overall VisualTotal) when sliced by Merit and full data when sliced by any dimension but Merit. In the cube, a hidden dimension named Logins is built which consists of two attributes: [Login] - key attribute and [Access To Merit Data] attribute. Intersection of the current User login and [Login].