Excel and tableau doesn't detect date dimension as datetime column

0
votes

I have a DIM Date dimension in my cube that has a date column whose type in SQL datamart is "date" and the data type in cube for this arribute is set to "date". But when I query the cube from excel and tableau, this arribute shows up as string instead of date and so I don't get the natural hierarchy of year ->month -> date in the client tools. Both excel and tableau format this column correctly if I connect directly to datamart instead of a cube. Is there any trick or tip to make these client tools format the date column as datetime instead of string ? I don't want to manually create these hierarchies in the cubes because there are 60+ date columns in my cube across all dimensions Thanks

1
excelssastableau-apiolap-cube
You can use Data Extract API to extract that data, and tell exactly what data type is each field – Inox

1 Answers

1
votes

In Tableau, you can change the field type to date and then save the data source as a reusable tds file. The settings should then be retained

From http://www.theinformationlab.co.uk/2013/12/02/tableau-file-types-and-extensions/ :

Tableau Datasource (.tds) When you connect to your data for the fist time, you may have a little bit of data ‘modelling’ to do – setting the right data types, changing default aggregations, setting default colours, creating some custom calculated fields etc etc. You are giving Tableau information about the data you will be using – you are setting up its ‘metadata’. When you want to connect to this data again, you don’t want to really go through all this data modelling a second time so instead you can save your metadata as a .tds file (again, it is saved in XML format) and connect to your data though this file instead. You could also distribute this file so that your colleagues have access to the nice formatting and custom fields you have worked to set up. Tableau is clever enough to pick up new columns/fields in the data source if they appear and column ordering does not matter but if column names change or disappear completely, you will need to reconfigure. To create a .tds file, from Tableau Desktop, right click on your data source connection and select Add to Saved Data Sources. Alternatively you can publish the .tds to Tableau Server by right clicking and selecting Publish to Server instead