Ordering of a dimension in sql server datawarehouse
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.