Sql-server – Ordering of a dimension in sql server datawarehouse

data-warehousedimensional-modelingsql-server-2012ssas

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as
1, 10, 11, 12, … 19, 2, 20, 21

Design.

DimDate (3 columns)

  • DateID KEY
  • Date DATETIME {Values 1/1/2000, 1/2/2000, ect}
  • DAY VARCHAR(2) {Values 1,2,3,4,5,6,7,ect}

FactAppointment (one column)

  • DateID {Values key to dim}

I set the dims sort column to key or date and neither works.

When excel, or any other bi tool is connected and the date dim is a column, a simple count is the value the dates are not sorted.

Picture: Column headers are not ordered properly.
1, 10,11,12,…,19,2,20

enter image description here

Best Answer

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore, they will be sorted according to character sorting (1, 10, 11) and not numeric sorting (1,2,3).

You didn't specify whether this was multi-dimensional or tabular model but both will support a sort order property. In cases where I need to present character data, like month name, but need it sorted in month order, I will have columns like MonthName and MonthSortOrder and not present the SortOrder suffixed fields to the consuming tools.

In a multi-dimensional model, another option is to edit the Data Source View and replace the source table with a named query that includes a cast to integer for the "Day" column.

Related Question