Anaylsis Server 2005 and wrong results in dimensions/cube

My cubes are somehow incorrect and I can't find out why:

I created a very simple table, because of wrong results in my project cubes and inserted 102 rows:

-

USE EA_DWH
GO

CREATE TABLE [dbo].[TEST_DWH]
(
[DWSTOREDATE_INT] INT NOT NULL,
[USER_NAME] VARCHAR(20) NULL
)
GO

INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021217','USER_03')
INSERT INTO TEST_DWH VALUES('20021218','USER_04')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
GO

-

The first value of this table represents the date value as integer and the second value is the user name.

Now when building a cube (I) you will find out that the values differ to a simple group by on the sql database (II):

I count all user and the dimension is build on DWSTOREDATE_INT (logical key) as key attribute and USER_Name as regular attribute.

(I):

-

TEST DWH Count TEST DWH USER NAME Ergebnis 20021216 USER_99 22 20021216 Ergebnis 22 20021217 USER_03 1 20021217 Ergebnis 1 20021218 USER_04 1 20021218 Ergebnis 1 20021219 USER_01 62 20021219 Ergebnis 62 20021220 USER_01 16 20021220 Ergebnis 16 Gesamtergebnis 102

-

(II):

-

SQL query (SQL Server):

SELECT DWSTOREDATE_INT, USER_NAME, COUNT(*) AS Count_StoreDate_User
FROM TEST_DWH
GROUP BY DWSTOREDATE_INT, USER_NAME
ORDER BY DWSTOREDATE_INT

RESULT:

DWSTOREDATE_INT USER_NAME Count_StoreDate_User
-- --
20021216 USER_99 22
20021217 USER_03 1
20021218 USER_04 1
20021219 USER_01 62
20021220 USER_01 8
20021220 USER_02 5
20021220 USER_03 3

(7 row(s) affected)

-

I tried this example on three different computers and can't find out why this happens. On Analysis Server 2000 it works correctly.

My computer:

W2K3 (SP1), SQL Server 2005 EE (SP1 CTP)

I had the same results without SP1 CTP and installed it because I thought this issue may be corrected.

Please help me solve this problem.

Best regards

You are correct. This is one of the cases where AS2005 is different from AS2000.
In your example you have a case of dimension that is based on the non-unique key attribute.
In AS 2000 Analysis Server automatically makes a dimension key unique by concatenating all levels to the key level ( lowest level in the dimension). In AS 2005 this is no longer the case. Although in simple cases (like you have here) you might be little confused at first, the real gain comes in AS2005 being able to implement better performing and scalable dimensions.
To fix the problem in your dimension, you need to define your composite key for your dimension key attribute. Add USER_NAME column to the KeyColumn of your dimension key attribute.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.