December 16, 2010 at 10:53 am
Has anyone seen this behavior before or know why this is happening?
I entered a Data Source View in BIDS for DimTime and it changed my WEEK(FullDate) line to { fn WEEK(FullDate) } when I went back to look at the code.
Here's the original query:
SELECT TimeKey, FullDate, DAY(FullDate) AS CalendarDayKey, DATEPART(dw, FullDate) AS CalendarDayofWeekKey,
WEEK(FullDate) AS CalendarWeekKey, MONTH(FullDate) AS CalendarMonthKey, CalendarQuarterKey,
CalendarYearKey
FROM dbo.DimTime;
And here's what BIDS / SSAS did to it after I'd saved the package and came back to it the next day.
SELECT TimeKey, FullDate, DAY(FullDate) AS CalendarDayKey, DATEPART(dw, FullDate) AS CalendarDayofWeekKey,
{ fn WEEK(FullDate) } AS CalendarWeekKey, MONTH(FullDate) AS CalendarMonthKey,
CalendarQuarterKey, CalendarYearKey
FROM dbo.DimTime;
It still seems to function correctly. I can run the code in SSMS and get the expected result. I'm just wondering why SSAS did this to one function, but not the others. Any ideas?
December 17, 2010 at 3:49 am
utter guess....
do you have WEEK as a field name in dbo.DimTime?
and so SSAS is being verbose to distinguish between the two?
December 17, 2010 at 4:19 am
No. I don't use keywords or functions for column & table names.
December 17, 2010 at 4:50 am
Brandie Tarvin (12/17/2010)
No. I don't use keywords or functions for column & table names.
always worth checking the easy stuff 🙂
funny how WEEK isn't pink in the sql...
i can't run the first one in SSMS, it says
'week' is not a recognized built-in function name
seems week isn't a t-sql function...
December 17, 2010 at 6:05 am
Best guess would be that someone has added a WEEK function to your sql DB and SSAS is simply identifying it as a user function versus the built in functions you're referencing. And no, WEEK isn't a TSQL datetime function (see the list, you'd need to use datepart).
Steve.
December 17, 2010 at 7:27 am
DOH. I wrote the original query wrong in this thread. I did use Datepart. Honest.
Still odd how it transformed it, though.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply