Error Processingn Cube with time dimension

The strange thing is that- i create two cubes with same data source using the same fact table and the same dimension in both cases.

However with the first one i dont define the dimension as a time dimension and the cube processes fine.

With the second cube i do define the dimension as a time dimension and then the cube processing errors. The error say attribute key cannnot be found? But it was found in the first cube?

Anyone know what might be causing this?

Thanks,

Can you try and use copy/paste functionality in BI Dev studio to make a copy of your existing cube and try to process it then?

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

|||

Thanks for the reply unfortunatly i still get he same error when i try this.

Some more information-

The error i get is that attribute key with value 0 cannot be found. i get as many of these errors as there are nulls in the fact tables date dimension key column. There arent any 0 values.

So it might be that for some reaon the nulls are converted to zeros when a date dimension is added and this throws the referential integrity errors? Please help..

Thanks,

|||

I think you guessed it right.

You need to make sure your fact table doesnt have null's in the date dimension key column.

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

|||

But surely my date dimension field can contain nulls. It is a nullable field in the OLTP system.?

i noticed this http://mgarner.wordpress.com/tag/mdx/ see the 'null = 0' post at the bottom.

|||

This is not a good practice to have a nullable key.

Although Analysis Services allows you to map null keys to Unknown member in the dimension, it is not a good idea.

To solve this problem try define a named calculation in your DSV to map null keys to some pre-defined value on the fly.

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

|||

Thanks for you reply Edward

But.

Analysis services is not converting nulls to unknown if the column is defined as a time dimension. It is converting them to zeros and raisng referential integrity errors.

Is it the case that Analysis services cubes cannot contain nullable time dimensions?

This doesnt sound right to me as date columns often signify an event and therefore a null indicates that the event hasnt occured.

Not being able to define nullable time dimensions if true is a serious flaw in analysis services 2005.

What value could i set the calculated field to in the case of nulls. Surely unknown is the right value for a null date? I certainly wouldnt want to put in someone arbitary date and presumaably it would have to be a vlaue of datetime data type?

[Edit]

Edward below is some test code which builds a test fact table and test time dim. If you define the dim as a time dim then the processing will error if you insert the null in the time dim. Note if you dont define it as a time dim it processess fine with or without the null.

Please have a look at this and let me know.

Thanks alot for all your help.

if object_id('DateDim') is not null

drop table DateDim

go

if object_id('datefact') is not null

drop table datefact

go

create table DateDim

(ID int identity, date smalldatetime)

go

insert DateDim

select getdate()

union all

select getdate()+1

union all

select getdate() +2

union all

select getdate() +3

union all

select getdate() +4

union all

select getdate() +5

create table datefact

(id int identity, datekey int , amount int)

go

insert datefact

select 1, 50

union all

select 1, 50

union all

select 3, 50

union all

select 4, 50

union all

select 5, 50

union all

select 5, 50

/*--Uncomment this so a null is insert into the time dimension reprocess the cube and it will error

union all

select null, 50

*/

sql