SSAS+PDW: Data selector for cubes
We wrote already about the way to feed the same cube structure with different data selections .
The described solution uses the APP_NAME() function which is missing in PDW v1.
The value of “Application Name” is accessible in the DMV “sys.dm_pdw_exec_sessions” (column “app_name”).
Thus the final solution for the PDW looks like following:
- 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 (select app_name from sys.dm_pdw_exec_sessions where session_id=SESSION_ID()) !=’REDUCED_CUBE’ OR c.CurrencyAlternateKey=’USD’
P.S. The statement works now ONLY with PDW, but not with “normal” SQL Server (due to sys.dm_pdw_exec_sessions). To support both just put the SQL essence in a view.