There is no way to show hierarchy level in one dimension of one cube and to hide that hierarchy level in the same dimension (role-played dimension) of the same or other cube. Solution is to add additional hierarchy without levels to hide to this dimension.
There is dimension 'Till' in database with hierarchy 'Tills' with structure of Till->Site->Group. And there is the cube 'Sales' that contains that dimension. Granularity level is 'Till'. Everything is good.
But there is one also cube 'StockOnHand' that contains that dimension under 'Site' name. Granularity level is 'Site', because goods are not stored in Tills. Leaf 'Till' in 'Tills' hierarchy are useless, so it would be good from user experiences point to hide this leaf.
- Edit 'Till' dimension of database: add new hierarchy 'Sites' with structure 'Site->Group' (there is no 'Till' level).
- Edit 'Till' dimension of 'Sales' cube: make 'Sites' hierarchy unvisible (set 'visisble' property to false)
- Edit 'Site' dimension (role-played one of 'Till' database dimension) of 'StockOnHand' cube: make 'Tills' hierarchy unvisible.
Result: you hid Hierarchy levels under Granularity level in Role-Played dimension.
MS SQL Server Database Developer with 7+ years experience
Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.
Feel free to contact me for rates and details.