4

I have deployed an SQL Server 2012 on a Windows Server en-us.

Now my dates on Analysis services are in the American format (MM/dd/yyyy). the company is not American, therefore I would like to change it to yyyy-MM-dd or dd-MM-yyyy, how can i do it?

Everything seems to be properly configured, however, SSAS is refusing to output the dates in the desired format :(

On excel:

American dates on Excel

On cube browsing: On cube brosing

On dimension browsing: enter image description here

Dimension seems to be properly configured on SSAS: SSAS dimension configuration

Query on SQL Database engine:

Query on sql engine

SSAS properties SSAS properties

SQL Server Database engine properties Database engine properties

Windows server regional settings Server regional settings

SQL.injection
  • 2,607
  • 5
  • 20
  • 37
  • May be related this [link](http://technet.microsoft.com/en-us/library/gg492121.aspx) – Nagaraj S Mar 20 '14 at 09:38
  • @NagarajS i tried that yesterday. But it generates this error: Error 1 DimensionAttribute [Dim Date].[Sql Date] : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used. 0 0 – SQL.injection Mar 20 '14 at 10:26
  • 1
    Superbly presented question. Many thanks. – DatumPoint Aug 18 '15 at 00:12

2 Answers2

4

A few month ago i was enduring the same problem with the date, i thought the issue was comming from SSAS but i was wrong...

So i totally changed my dimension table with a new table containing all date format, so now i can use any format that i want...

http://www.codeproject.com/KB/database/647950/figure1.png

You can find how to create it in this link:

http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

ASNAOUI Ayoub
  • 462
  • 2
  • 9
  • Having a char column storing the formatted storing the date seems to be a nice work around. – SQL.injection Mar 20 '14 at 10:19
  • BTW you might consider replace the Holiday null values by "NOT_HOLLIDAY" as described in the Data Warehouse Toolkit by Ralph Kimbal – SQL.injection Mar 20 '14 at 10:21
  • Yes indeed...This is the link for creating the table + sp_procedure to populate it http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho... – ASNAOUI Ayoub Mar 20 '14 at 10:40
  • @ASNAOUIAyoub You would not even have to extend your dimension table. You could use a view based on it containing this as a calculated column, or create a named calculation in the DSV. This could save some space and some additional steps in preparing the dimension table. – FrankPl Mar 20 '14 at 17:09
0

I did have similar problem - checked all 3 environment DEV/UAT/PROD - no difference at all - however, DEV and UAT giving the Date in correct format (yyyy-mm-dd)- but PROD was in US format - tried everything, but still PROD showing in US format. Next, I reprocessed and deployed the cube from Visual Studio. Once it is completed i.e deployed to PROD - Date was in correct format - ie in yyyy-mm-dd.

Deepak
  • 1
  • 1