data access management in OLAP cube for organization with multiple compaines

Question

I am building a OLAP Cube in SSAS for an organization which has many different companies under its umbrella.

i have built a principal cube which consists of all the measure groups and dimensions, which has the data of all the companies in this organization.

Now this cube is ok for the top level management, but i need to limit the access of users from each of the companies, only to the data of its own company.

Is there a way to do that in the principal cube, without duplicating it to many sub cubes, each consists of only the relevant company?

Thank you in advance, Tal

Answer 1

You can use role based security in combination cell-based security. below link can be useful:

https://learn.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-cell-data-analysis-services

Answer 2

We solved the problem through the automatic modification of MDX queries, restricting data through nested cubes (subcube) for each organization. Not only data, but also cube metadata were limited. The mechanism of roles to the customer is not suitable, as organizations and users are constantly added.The Ranet UI Pivot table was used in the Saas solution. The library allows you to parse and modify MDX query, as well as filter the metadata of the cube.