Sort Dimension Members in DESCending order

Sometimes we need to display Date dimension members in descending order. For example, Client ask to show Calendar Year in DESC order.

But Analysis Services doesn't provide a way to sort dimension members in Descending order. SSAS provides only Ascending order.

However, we can achieve this sort functionality by using a simple trick. I will take an example to display Calendar Year member in DESC order. Below are the steps to achieve our goal:

Step1: Go to Data Source View (DSV) and right click on Date dimension in DSV diagram. Click on New Named Calculation... to create a new member.

Step2: Now enter CalendarYearSort in Column name and 0-CalendarYear in Expression textbox. This additional column will be used to sort calendar year in descending order. Click OK to proceed. Here I considered that CalendarYear is existing column in the dimension.

Step3: Go to Dimension Structure of Date dimension and drag and drop new member "CalendarYearSort" in attributes list.

Step4: Click on Attributes Relationship tab. Right click on Calendar Year member and select New Attribute Relationship... It will open Create Attribute Relationship wizard.

 

Step5: In Create Attribute Relationship, select Related Attribute as Calendar Year Sort and Relationship type as Rigid. Click OK to proceed.

Step6: Again go back to Dimension Structure tab and click on Calendar Year attribute. Go to properties and set following things as shown below:

OrderBy: AttributeKey and OrderByAttribute: CalendarYearSort

Step7: Now Calendar Year is set in decending order. Just save and process the cube.

Note: Before processing the cube, you can set AttributeHierarchyVisible = FALSE because we should not show this attribute in cube and AttributeHierarchyEnabled = False to reduce cube space and increase the performance.

Date Dimension

Below is the script to create Date Dimension.

USE [DatabaseName]
GO

IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO


CREATE TABLE [dbo].[Date](
   [DateSK] [int] NOT NULL,
   [FullDate] [datetime] NOT NULL,
   [DateName] [char](11) NOT NULL,
   [DayOfWeek] [tinyint] NOT NULL,
   [DayNameOfWeek] [char](10) NOT NULL,
   [DayOfMonth] [tinyint] NOT NULL,
   [DayOfYear] [smallint] NOT NULL,
   [WeekdayWeekend] [char](7) NOT NULL,
   [WeekOfYear] [tinyint] NOT NULL,
   [MonthName] [char](10) NOT NULL,
   [MonthOfYear] [tinyint] NOT NULL,
   [CalendarQuarter] [tinyint] NOT NULL,
   [CalendarYear] [smallint] NOT NULL,
   [CalendarYearMonth] [char](7) NOT NULL,
   [CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO Date
SELECT
   CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK
   ,@StartDate AS [Date]
   ,CONVERT(varchar(20),@StartDate,106) AS DateName
   ,DATEPART(DW,@StartDate)   [DayOfWeek]
   ,DATENAME(DW,@StartDate) [DayNameOfWeek]
   ,DATENAME(DD,@StartDate)  [DayOfMonth]
   ,DATENAME(DY,@StartDate)  [DayOfYear]
   ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'
             ELSE 'WeekDay' END     [WeekdayWeekend]
   ,DATEPART(WW,@StartDate) [WeekOfYear]
   ,DATENAME(MM ,@StartDate) [MonthName]
   ,DATEPART(MM ,@StartDate)   [MonthOfYear]
   ,DATEPART(QQ,@StartDate)     [CalendarQuarter]
   ,DATEPART(YY ,@StartDate)    [CalendarYear]
   ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2)  [CalendarYearMonth]
   ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

   SET @StartDate = @StartDate +1
END
GO


Date Dimension is ready to use as soon as you execute this script in required database.
You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD).
Year-->Quarter-->Month-->Week-->Date