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:
Here is the example of the query (DimOrganization in AdventureWorks):
- SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership,
- o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
- FROM dbo.DimOrganization AS o INNER JOIN
- dbo.DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
- WHERE APP_NAME()!='REDUCED_CUBE' OR c.CurrencyAlternateKey='USD'
The query returns records only with “USD” for the reduced cube.
This solution can be used not only to reduce production cubes, but also for development to speed up the processing.
Hope it helps!