SSAS: Data selector for cubes

Recently we had a request from one of our customers to provide a reduced version of the cube. The idea behind was to let a certain department to work with the dedicated cube which holds only the small portion of data. This brings not only performance for queries, but also reduces the processing time and makes it easier to move or backup the cube.

We have reformulated this request as following requirements:

the reduced version must have exactly same metadata and same source database, but…

…must have a reduced set of members in some dimensions

…must load facts only for the remaining dimension members

The first requirement also implies only one version of objects in the Data Source View.

We found the following elegant solution: SQL queries return a full or a reduced datasets for dimensions and facts based on the value of APP_NAME() which we provide in the connection property “Application Name” for data source: