3
Vote

SSAS: Inline named sets in MDX Script

description

suggestion from http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/edc5df49-64f3-449d-ab5a-ec9565d50499/

Might I suggest an automated search-and-replace type of extension to BIDSHelper? Something where you decorate a calculated set with a certain keyword/comment, and BIDSHelper (upon deployment?) searches for (later) calculations that use that set?

For example:

CREATE DYNAMIC SET CURRENTCUBE.[Days in Fiscal Year Set]
AS
/ BIDSHelper Code Replace Source of [Days in Fiscal Year Set] Start /
Descendants(Ancestor([Dates].[Fiscal Calendar].CurrentMember, [Dates].[Fiscal Calendar].[Year]), [Dates].[Fiscal Calendar].[Day])
/ BIDSHelper Code Replace Source of [Days in Fiscal Year Set] End /
, DISPLAY_FOLDER = 'Intermediate Calculations';
CREATE MEMBER CURRENTCUBE.[Measures].[Days in Fiscal Year]
AS
/ BIDSHelper Code Replace Usage of [Days in Fiscal Year Set] Start /Descendants(Ancestor([Dates].[Fiscal Calendar].CurrentMember, [Dates].[Fiscal Calendar].[Year]), [Dates].[Fiscal Calendar].[Day])/ BIDSHelper Code Replace Usage of [Days in Fiscal Year Set] End /.Count,
VISIBLE = 1 , DISPLAY_FOLDER = 'Intermediate Calculations' ;
Or something less verbose? :)

comments

dgosbell wrote Apr 7, 2013 at 7:31 AM

we would probably want to "inline" the sets on a build event, but leave comment "markers" so that we could update the sets if the original definition was ever changed. If we did this then the original project would still be usable by someone without BIDSHelper installed.