We have a DW/BI solution that use the now() function to determine the default week to set the time dimension to. This has been in place for over a year and has worked fine. For some reason it is not working due to a wacky time stamp being returned.
Example code ....
-- The First Calculated member is the value of NOW()
WITH MEMBER [Measures].[Full Date] as 'now()'
-- The Second Calculated Member is the Day part of the first calculated member.
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
-- The Third Calculated Member is the Month part of the first calculated member.
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
-- The Fourth Calculated Member is the Year part of the first calculated member.
Member [Measures].[What Year] as 'YEAR([Full Date])'
SELECT
{[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS
FROM [Cube]
Returned Datetime.....
Full Date What Day What Month What Year
10/15/4376 6:17:06 PM 138 207 46161
1. I verified the server has the correct system date and time.
2. I can run this same code against another analysis server and the correct time is returned.
Any ideas?
If I run this MDX in the sample app with a solve_order=1 it works fine. When setting the default member of a dimension there is no place to indicate a solve_order....we have restarted SQL Server/Olap services and rebooted the server but still not working.
Any Ideas?
|||Maybe there is any other calculation interfering with your measures.
For example Time Inteligence with YTD or some Utility dimension you have with changed default members ...
To be sure you should do your default member without measures - only use StrToMember and the Now Function...
At the end of the cubes mdx script - for example
Code Snippet
alter cube currentcube update dimension [Time].[Year], default_member=StrToMember("[Time].[Year].&[" + cStr(Year(Now())) + "]");
Hannes
|||I don't think so. In the example I posted, the MDX is using the now() function. When I run this now() function on one server the correct time is returned on the other server I am getting an incorrect time. Thanks for your help.