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