NULL Date Value cause problem in Dimension?

NULL Date Value cause problem in Dimension?

Post by Ray » Sun, 21 Jan 2001 00:51:40



I found that I am missing some data measures from current records, it turns
out these records have <NULL> value on one of the columns. This column is
one of the dimension of my cube (SQLServer7). When the cube try to find
distinct values from this column to build the dimension, it can a [0] year
[] month [] day member, I think this is the way it interpret my <NULL>
records, unforturately it don't know how to relation this back when it build
the aggregations.

Any work around for this? What value is Time.[0].[].[]? My only thought is
set "01/01/1900" to all <NULL>.

Thank in advance

 
 
 

NULL Date Value cause problem in Dimension?

Post by thomas ivarsso » Sun, 21 Jan 2001 04:38:54


Well Ray. I think you need to carefully describe the problem here. Like all
relational database
structures you need a relation between the dimension table and the fact
table. It is always a good
idea to avoid nulls in fact- and dimension tables. Analysis server supports
something called ragged
hierarchies, if you have a problem with nulls in the dimension tables. There
is a whitepaper available
on MSDN about this. In Olap service you will have to use another solution.
Nulls in the facttable (foreign keys or measures) is not a good idea.

Thomas Ivarsson
DW consultant
Sweden


Quote:> I found that I am missing some data measures from current records, it
turns
> out these records have <NULL> value on one of the columns. This column is
> one of the dimension of my cube (SQLServer7). When the cube try to find
> distinct values from this column to build the dimension, it can a [0] year
> [] month [] day member, I think this is the way it interpret my <NULL>
> records, unforturately it don't know how to relation this back when it
build
> the aggregations.

> Any work around for this? What value is Time.[0].[].[]? My only thought is
> set "01/01/1900" to all <NULL>.

> Thank in advance


 
 
 

NULL Date Value cause problem in Dimension?

Post by Ray » Sun, 21 Jan 2001 05:29:47


Thx Thomas,

I think my problem is cause by the relationship between my fact table and my
reference table (just a distinct list of all DATEs).

I am working around it by directly using the DATEs on the fact table, it
seems to work now. But thanks anyway..

BTW, I cannot find the white paper you mentioned, can you post the link, or
key work to search..


> Well Ray. I think you need to carefully describe the problem here. Like
all
> relational database
> structures you need a relation between the dimension table and the fact
> table. It is always a good
> idea to avoid nulls in fact- and dimension tables. Analysis server
supports
> something called ragged
> hierarchies, if you have a problem with nulls in the dimension tables.
There
> is a whitepaper available
> on MSDN about this. In Olap service you will have to use another solution.
> Nulls in the facttable (foreign keys or measures) is not a good idea.

> Thomas Ivarsson
> DW consultant
> Sweden



> > I found that I am missing some data measures from current records, it
> turns
> > out these records have <NULL> value on one of the columns. This column
is
> > one of the dimension of my cube (SQLServer7). When the cube try to find
> > distinct values from this column to build the dimension, it can a [0]
year
> > [] month [] day member, I think this is the way it interpret my <NULL>
> > records, unforturately it don't know how to relation this back when it
> build
> > the aggregations.

> > Any work around for this? What value is Time.[0].[].[]? My only thought
is
> > set "01/01/1900" to all <NULL>.

> > Thank in advance

 
 
 

NULL Date Value cause problem in Dimension?

Post by thomas ivarsso » Sun, 21 Jan 2001 19:12:54


It is "Analysis Services:Choosing dimension types in sql server 2000
analysis services" I found it
under the msdn-sql server.
Also. There are several good labs available for Analysis Server if you have
a look
at www.microsoft.com/sql . I think its on a download site there. These  labs
have documentation as well.

Regards
Thomas Ivarsson
DW consultant Sweden


> Thx Thomas,

> I think my problem is cause by the relationship between my fact table and
my
> reference table (just a distinct list of all DATEs).

> I am working around it by directly using the DATEs on the fact table, it
> seems to work now. But thanks anyway..

> BTW, I cannot find the white paper you mentioned, can you post the link,
or
> key work to search..



> > Well Ray. I think you need to carefully describe the problem here. Like
> all
> > relational database
> > structures you need a relation between the dimension table and the fact
> > table. It is always a good
> > idea to avoid nulls in fact- and dimension tables. Analysis server
> supports
> > something called ragged
> > hierarchies, if you have a problem with nulls in the dimension tables.
> There
> > is a whitepaper available
> > on MSDN about this. In Olap service you will have to use another
solution.
> > Nulls in the facttable (foreign keys or measures) is not a good idea.

> > Thomas Ivarsson
> > DW consultant
> > Sweden



> > > I found that I am missing some data measures from current records, it
> > turns
> > > out these records have <NULL> value on one of the columns. This column
> is
> > > one of the dimension of my cube (SQLServer7). When the cube try to
find
> > > distinct values from this column to build the dimension, it can a [0]
> year
> > > [] month [] day member, I think this is the way it interpret my <NULL>
> > > records, unforturately it don't know how to relation this back when it
> > build
> > > the aggregations.

> > > Any work around for this? What value is Time.[0].[].[]? My only
thought
> is
> > > set "01/01/1900" to all <NULL>.

> > > Thank in advance

 
 
 

NULL Date Value cause problem in Dimension?

Post by Ray » Tue, 23 Jan 2001 23:52:37


Thank you


> It is "Analysis Services:Choosing dimension types in sql server 2000
> analysis services" I found it
> under the msdn-sql server.
> Also. There are several good labs available for Analysis Server if you
have
> a look
> at www.microsoft.com/sql . I think its on a download site there. These
labs
> have documentation as well.

> Regards
> Thomas Ivarsson
> DW consultant Sweden



> > Thx Thomas,

> > I think my problem is cause by the relationship between my fact table
and
> my
> > reference table (just a distinct list of all DATEs).

> > I am working around it by directly using the DATEs on the fact table, it
> > seems to work now. But thanks anyway..

> > BTW, I cannot find the white paper you mentioned, can you post the link,
> or
> > key work to search..



> > > Well Ray. I think you need to carefully describe the problem here.
Like
> > all
> > > relational database
> > > structures you need a relation between the dimension table and the
fact
> > > table. It is always a good
> > > idea to avoid nulls in fact- and dimension tables. Analysis server
> > supports
> > > something called ragged
> > > hierarchies, if you have a problem with nulls in the dimension tables.
> > There
> > > is a whitepaper available
> > > on MSDN about this. In Olap service you will have to use another
> solution.
> > > Nulls in the facttable (foreign keys or measures) is not a good idea.

> > > Thomas Ivarsson
> > > DW consultant
> > > Sweden



> > > > I found that I am missing some data measures from current records,
it
> > > turns
> > > > out these records have <NULL> value on one of the columns. This
column
> > is
> > > > one of the dimension of my cube (SQLServer7). When the cube try to
> find
> > > > distinct values from this column to build the dimension, it can a
[0]
> > year
> > > > [] month [] day member, I think this is the way it interpret my
<NULL>
> > > > records, unforturately it don't know how to relation this back when
it
> > > build
> > > > the aggregations.

> > > > Any work around for this? What value is Time.[0].[].[]? My only
> thought
> > is
> > > > set "01/01/1900" to all <NULL>.

> > > > Thank in advance