Dynamic Named Sets in SQL Server 2008 Analysis Services (SSAS 2008)

Introduction:

SSAS is having a nice & useful feature of defining the calculations (Calculated Members & Named Sets). These calculations can then be used in MDX or by any other client tools etc. These calculations come very handy to allow the user to use them to analyse their data with required parameters, attributes, & custom calculations. Using Named Sets, we can predefine the member(s) which are based upon certain conditions and then user can directly use them to see the measure/data against those members.

Let’s say we want to offer the user with a set of last 5 days/dates which contains transactional data, even though there may be lot of other later dates but with no fact data.

So for this we can create a regular Named Set on Calculation tab, something like:

 CREATE SET ActualSalePeriods AS

Tail

(      NonEmpty([Date Order].[Calendar].[Month].[Date].Members,

                        Measures.[Sales Amount]),

5);  

 Every calculated member/named set created here is session scoped & created with keyword ‘Create’ behind the scene.

 These Named Sets are evaluated and created every time the cube is processed so at the time of next process, if cube gets data with new dates, it will refresh the result set of this create Named Set as well accordingly. So we can say, they are static is nature and contents will remain intact until cube is reprocessed.

Dynamic Named Set:

These regular Named Sets are Static in Nature (only possible option till 2005 version) to improve the query performance by not automatically refreshing/querying it against cube every time. And in fact, works well in the situations like getting latest 5 dates as mentioned above since as in when new dates get added with data in the cube, it will be reprocessed & above set would also be updated with latest members then.

However, this may result a serious issue with respect to the actual requirement which (let’s say) is to get the latest Top 10 employees with highest sales on monthly basis. Created Named Set (static), evaluated at the time of processing, will give the correct result set with those employees. But now in case user wants to slice/dice the result set based upon some other dimension say region, product etc, again user will be offered with the same result set generated earlier at the time of processing, i.e. wrong data information.

 To cope up with such issues/requirements, SSAS 2008 has come up with one more option for creating the Named Set. It’s named as ‘Dynamic’. So developer can either select Static (default) to have the regular Named Set or can select Dynamic to give the Named Set a dynamic behaviour. And by selecting Dynamic option, SSAS will add a keyword ‘Dynamic’ before SET keyword to make it dynamic named set.

Dynamic sets are not calculated once. They are calculated before each & every query, and very important, in the context of that’s query WHERE clause and sub-selects. 

Thus the above requirement of getting Top 10 sales employees will work absolutely fine with Dynamic Named Set.

 Imp: Dynamic named set not only resolves the above mentioned issue but also adds capability to MDX which was not able to generate the desired results with regular named set. This is in regards with the issue while using the multi-select dimensions in the sub-select rather than where clause (which is case with excel wherein it always converts where to sub-select). So using Dynamic sets, multi-select statements are evaluated properly even being used in sub-select statements.

About these ads
This entry was posted in SQL Server 2008, SSAS 2008. Bookmark the permalink.

6 Responses to Dynamic Named Sets in SQL Server 2008 Analysis Services (SSAS 2008)

  1. Anand Vanam says:

    I have gone through your Article about Dynamic Column Mapping in SSIS.

    I will get 10 Flat files every day. I have 10 database tables whose schema is according to those 10 flat files.

    I am loading Data from Flat File source to SQL server Table (OLEdb Destination. I created a SSIS package with one data flow task, which dynamic flat file and OLEdb Destination.

    I want to change the name of the Flatfile Name and Table Name for every run through configuration File. When I change the Names of FlatFile and Database Table (of different schema ). I got an Error stating VS_NEEDSNEWMETADATA.

    So can you please provide me any suggestions or any dtsx package file to overcome this issue.
    Can you please send the dtsx package file and config file to my mail id.

  2. Ed says:

    Hi Munish. Can you create a named set using dates whereby you want to fix the start date? For example, I have a set as:

    NonEmpty ( [Transaction Date].[Date].[Date].Members,
    [Measures].[NameOfMeasure]
    );

    But what if I wanted to pull in all dates as of a specific date? In my case I only want dates starting with Feb. 1, 2011 and all dates thereafter.

    Any suggestions. Like your blog. Thanks.

  3. Per says:

    Hi!

    I modified your expression to this:
    Tail( NonEmpty([Dim Date].[YearQuarterMonthWeekDay].[Date Key].Members),1)

    It works, i get the right day but I can only use it to 1 measure, when i drag in another measure all data disaperse. Why?

  4. Pingback: Link Resource # 42 : Feb 01 – Feb 24 « Dactylonomy of Web Resource

  5. Pingback: Link Resource # 43 : Mar 01 – Mar 24 « Dactylonomy of Web Resource

  6. Pingback: Link Resource # 44 : Mar 24 – Mar 31 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s