MS SQL Server OLAP - Date format problem in OLAP

Author

Date format problem in OLAP

Terence

2005-04-21, 3:24 am

Hi,

We have a dimension (DateTime) in Analysis Service based on dates.
When the dimension is displayed in OLAP, the date appears as long date
format.
mm/dd/yy hh:mm:ss.mm (ie. 01/04/2001 00:00:00.00).
We have tried to get rid of the hh:mm:ss.00 without succeed.

Beneath are some infomation:

In SQL server:
The fact table - dso.time_by_day.the_date 's Data type = datetime
Records from the column the_date = in the correct format, d/mm/yyyy
E.g. 1/01/2004

In Analysis Service:
The dimension (TimeDate)'s Member Key Column =
"dbo"."time_by_day"."the_date"
Member Name Column =
"dbo"."time_by_day"."the_date"

What makes it interesting is that we have 4 machines.
1 = Windows 2003
2 = Windows XP
1 = Windows 2000 Server
Only the Windows 2000 server displays short date format in the OLAP
for some reason. SQL Server/Analaysis Service/Coding = all identical
in the above machines. All machines contain the latest SQL + Analaysis
Service Patch.

Further more, William Epp (previous poster) actually had the same
problem.
I've tried what [MS] Hai-Wei Xu (from Microsoft) suggested, still
couldn't get it working somehow...

Beneath is the question+answer from previous poster.

Any suggestion which can help us to get rid of the long date format
will be deeply appreciated. Thanks in advance.

Best Regards
Terence.

====================
====================
====================
===================
All messages from thread
Message 1 in thread
From: William Epp (res08unw@verizon.net)
Subject: Date dimension and SQL Server DATETIME

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-01-04 14:18:08 PST

I have a dimension based on dates (I can't use the built in Time
dimension type because it's not a calendar year). The underlying
column data type is DATETIME in the dimension and fact tables (no
times in the data). When I process the Date dimension and browse it
the dates are formatted as mm/dd/yyyy but when I process the cube it
changes to mm/dd/yy hh:mm:ss.mm (ie. 01/04/2001 00:00:00.00).

I've tried changing the dimension data type to DBDate,
deleting/rebuilding the dimension to no avail. This first appeared
when we updated SQL Server sp2 but went away (how I'm not sure). Now
I've added 2002 and it's come back. Do I have to create a view on the
table and format the time away? Seems like a zero time should be
ignored...

Windows 2000, Analysis Server 2000 sp2, SQL Server 2000 sp2.
Message 2 in thread
From: [MS] Hai-Wei Xu (haiwxu@onlinemicros
oft.com)
Subject: RE: Date dimension and SQL Server DATETIME

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-01-08 01:22:14 PST

Hi ,

In the "day" level of your time dimension, please change the "Member
Name
Property" from ""dbo"."time_by_day"" to
" convert(varchar(15),
"dbo"."time_by_day"."the_date",101)". And then,
full
process the dimension and the cube.

To control how the dimension member is displayed, setting "Member Name
Property" is suggested.

This posting is provided ¡°AS IS¡± with no warranties, and confers no
rights

Regards,
Haiwei Xu
Microsoft Support Engineer

Deepak Puri

2005-04-21, 3:24 am

The suggested solution should work - what error/problem do you see when
you try it? Also, remove the outer quotes:
[color=darkred]
convert(varchar, "dbo"."time_by_day"."the_date", 101)[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Sponsored Links