Month Ordering by Calendar Sequence when Slicing Measures by CalendarMonth

Month Ordering by Calendar Sequence when Slicing Measures by CalendarMonth

  • Comments 3
  • Likes

From Atchut Barli:

I have seen few customers who tried the blog  http://blogs.technet.com/b/servicemanager/archive/2012/12/07/incidents-or-service-requests-sliced-by-months-quarters.aspx   to prepare reports using  CalendarMonth in DateDim as Slicer find that the Months are  ordered alphabetically rather then by calendar Month order. Actually by definition, CaelendarMonths attribute in Date dimension in Service Manager Analysis Database has  orderby Property set as Name which is causing this..

Here is a snapshot with Months in Alphabetical order

The month order can be fixed by  following the below steps:

1) Updating Monthnumber attribute of the Date dimension to be sorted  as Integer rather then String- by default even MonthNumber is being ordered by Name which is Char type..

      if key is integer, then ordering will be 1, 2, 3..., 11, 12. If Key is string, then ordering will be 1, 11, 12, 2, 3 ...

so update the Orderby attribute to its key

2) add a relationship between Monthnumber and CalendarMonth

3) Update CalendarMonth attribute to be ordered using Monthnumber attribute

The above steps can be performed using Business Intelligence studio or I have attached a power shell script  to do the same

Here are the steps to achieve it using attached Powershell Script

1)  Please make sure that cube processing jobs are not running or will not be scheduled in next 15mins.

2) Take a backup of Analysis Services Database just to be safe if there is an error during transaction.

3) Execute the attached powershell script on the SSAS server

4) The powershell script updates the schema and Database object, so the password need to be retyped for Impersonation using the following steps.

      a) connect to the SSAS server from Management studio

       b) select each Datasource properties and select ImpersonateAccount

      c)Enter the password for  Impersonation Information Account

   d)repeat the above steps for all three datamart sources in a Multimart topology or  Dwdatamart Datasource if its a single mart topology

5)process the cubes from SM Console or using DW cmdlets

Attachment: UpdateCalendarMonthOrder.ps1
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Thanks very helpful

  • Can you also give a powershell script do undo the changes ?

  • I am getting an error :

    True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisService...
    Connecting to the database DWASDatabase
    Creating Attribute relationship between Month number and Calendar month
    Creating Attribute relationship between Month number and Calendar month
    Exception calling "Add" with "1" argument(s): "Another 'AttributeRelationship'
    object has the 'MonthNumber' key."
    At C:\software\incident_slider_MP\UpdateCalendarMonthOrder.ps1:36 char:39
    + $cm.AttributeRelationships.Add <>
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    updating the SSAS Database- takes few mins ...