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
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 DWASDatabaseCreating Attribute relationship between Month number and Calendar monthCreating Attribute relationship between Month number and Calendar monthException 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 : DotNetMethodExceptionupdating the SSAS Database- takes few mins ...
If you have UAC enabled Then
Run PowerShell as an Admin
Else you'll get the following:
Exception calling "Update" with "1" argument(s): "The ImpersonationInfo for datasource 'DWDataMart' contains an ImpersonationMode that can only be used by a server administrator.
At C:\temp\UpdateCalendarMonthOrder.ps1:41 char:9
+ CategoryInfo : NotSpecified: (:) , MethodInvocationException
+ FullyQualifiedErrorId : OperationException
SSAS Database Update complete ...
Update the Impersonation Info as per steps from blog