Ordering of a dimension in sql server datawarehouse

Question

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

Answer 1

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.

Answer 2

Change your DayOfTheMonth column to TINYINT. Any downstream services, such as Excel or SSAS, will then sort your numbers as numbers.

There is one circumstances where I'd consider using a text data type for the day of the month. Most dimension tables, including Dates, will have a couple special records to represent things like "not available," "not applicable," "bad value," or "illegible." For such records, it can be helpful to have this label in all fields. So if I run a sales report showing sales by date shipped, but some sales haven't shipped yet, they'll appear with a ship date of "not applicable" instead of "7" or "29." In this scenario, you'll need to use one of billinkc's suggestions to override the default sort order.