Hello,

I have a problem with a time dimension in AS consisting of a year and a week
level that shows dubplicate weeks!

I have created a time dimension with the levels year, quarter, month and day
from a datetime field in a database table. That worked fine and the values
are correct.

Then I created another time dimension with the levels year and week from the
same datetime field and then I received a processing error that the dimension
has duplicate values! So I changed the "Allow duplicate names" value in the
properties of the dimension to true and created the dimension.

But the problem is not solved. I now have a dimension with correct year
values, but the weeks are very strange.

They are shown in the order of 1, 2, 3, 4, 4, 6 and so on. Note that week
number 4 occurs twice, but number 5 does not exist!

Does anyone know what the problem is here? I don't think there is anything
wrong with the data since my other time dimension that is created from the
same data works fine!

FYI:
I am using Analysis Services 2000 and the key and name members are created
automatically accordingly:
Key: DatePart(week,"dbo"."table"."field")
Name: 'Vecka ' + convert(CHAR, DateName(week, "dbo"."table"."field"))
where Vecka is the Swedish word for week, since I am located in Sweden.

Any suggestions are appreciated!

Hi Tara,

Does the list of week numbers produced at the week level accurately refect
the dates that are in the date column of the table? It may be worth
considering using an integer representation of a date as the lowest level
key, for example ansi date format integers 20051020 and convert to a char and
then to datetime.

Alternatively, run some simple queries against the date column within your
dimension table and use DatePart function to verify returned values are as
you expect.

Regards, Graeme.

"Tara" wrote:

Hi Scott,

Sorry for the late reply but unfortunatelly a problem seldome comes alone!

I have done some tests of how AS collects the data for the dimension, as I
stated earlier. AS collects the week time dimension the following way when
processing the cube:
Dimension 'DimWeek' Execute : SELECT DISTINCT
DatePart(year,"dbo"."Table"."dDate"),
DatePart(year,"dbo"."vwRapportFlyttKort"."Datum"),
DatePart(week,"dbo"."Table"."dDate"), 'Vecka ' + convert(CHAR, DateName(week,
"dbo"."Table"."dDate")) FROM "dbo"."Table"

When testing the same expression directly on the table through Query
Analyzer, with the following expression:
SELECT DISTINCT DatePart(year,dDate),
DatePart(year,dDate),
DatePart(week,dDate),
'Vecka ' + convert(CHAR, DateName(week, dDate))
FROM Table
for different time periods I get the correct result! I.e. I don't get the
duplicate week values!

Scott, I don't really understand what you mean by using an integer
representation of a date. Why wouldn't the expression allready used give the
desiered result? And why does it seem that AS collects the data in another
way?

I really don't know how to solve this problem and would really appreciate
some advice.

Thanks in advance,
Tara



"Graeme Scott [MSFT]" wrote:

There is a difference in the two statements you posted below, the one
coming from AS refers to "vwRapportFlyttKort".

Could this be the source of your issue?

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <21E883B9-A005-4BDC-9E7E-DF63958126A0 (AT) microsoft (DOT) com>,
Tara (AT) discussions (DOT) microsoft.com says...

No, I'm sorry! It was just my mistake when writing about hte problem here.
The name of my table is originally "vwRapportFlyttKort", but to make it
easier I tried to write "Table".

So, just ignore the "vwRapportFlyttKort", that is not the problem. The code
should be like this:

The dimnsion selected by AS is as follows:
Dimension 'DimWeek' Execute : SELECT DISTINCT
DatePart(year,"dbo"."Table"."dDate"),
DatePart(year,"dbo"."Table"."dDate"),
DatePart(week,"dbo"."Table"."dDate"), 'Vecka ' + convert(CHAR, DateName(week,
"dbo"."Table"."dDate")) FROM "dbo"."Table"

Testing the same expression directly on the table through Query
Analyzer looks like this:
SELECT DISTINCT DatePart(year,dDate),
DatePart(year,dDate),
DatePart(week,dDate),
'Vecka ' + convert(CHAR, DateName(week, dDate))
FROM Table

Hope you can help me with this problem now. I really have no ideas what can
be the problem.

Thanks,
Tara

"Darren Gosbell" wrote:

I solved the problem! It was like usual not so difficult when you knew what
to do!

AS 2000 tried to create the week dimension the following way:
Dimension 'DimWeek' Execute : SELECT DISTINCT
DatePart(year,"dbo"."Table"."dDate"),
DatePart(year,"dbo"."Table"."dDate"),
DatePart(week,"dbo"."Table"."dDate"), 'Vecka ' + convert(CHAR,
DateName(week, "dbo"."Table"."dDate")) FROM "dbo"."Table"

What I did was to change the last DateName to DatePart and also change back
the "Allow duplicate names" to false again. I had to set it to true before,
otherwise it wouldn't process the cube.

So the above expression looks now like this:
Dimension 'DimWeek' Execute : SELECT DISTINCT
DatePart(year,"dbo"."Table"."dDate"),
DatePart(year,"dbo"."Table"."dDate"),
DatePart(week,"dbo"."Table"."dDate"), 'Vecka ' + convert(CHAR,
DatePart(week, "dbo"."Table"."dDate")) FROM "dbo"."Table"

I don't know if this DateName conversion of week is just an unpleasant
feature of AS 2000 or if this is a localization problem. In any case, the
problem was solved in this case by changing DateName to DatePart.


"Tara" wrote: