Month Ordering by Calendar Sequence when Slicing Measures by CalendarMonth

Published Feb 15 2019 05:49 PM 184 Views
First published on TECHNET on Mar 11, 2013

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-slic... 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


UpdateCalendarMonthOrder.ps1

Version history
Last update:
‎Mar 11 2019 09:49 AM
Updated by: