Excel Import Data from Analysis Services - Date comes across as text

Question

I'm trying to use an (exercise) SSAS tabular model in Excel. The model contains a column DATE, in the Proporties window its Data Type is Date and the Date Format is General. When I import the data into Excel the date column seems to come across as text.

To import, I open Excel, go to Data -> Get External Data -> From Other Sources -> From Analysis Services

After the data gets imported, the DATE column contains text representations of the date. The "Group" section of the PivotTable Options tab is greyed out, and the sort only does A-Z. How do I get the data type to come across?

Answer 1

Analysis Services delivers attribute names always as strings. You could either build your own MDX query for the extract, instead of relying on the default import wizard query. In your custom query, you convert the name to measure of Date data type. This would look similar to this:

WITH MEMBER Measures.MyDate AS
     CDate([DateDim].[DateAttrib].CurrentMember.Name)
SELECT { Measures.MyDate } ON COLUMNS,
       [DateDim].[DateAttrib].[DateAttrib].Members ON ROWS
  FROM [MyCubeName]

Or, within the tabular model, build a calculated column in date format, i. e. create a calculated column with the expression

DateValue([origDateColWhichIsText])

and make the original column invisible.

In all cases, converting from date strings to dates may be locale setting sensitive.