Data Model Relationships

I am trying to create a data source view that includes a Fact table and
several related dimension tables. One of my dimension tables is quite small
and uses a tinyint PK. For some reason when I bring this table into the
model it thinks the PK is a System.Int32 datatype and won't allow a
relationship to the fact table FK which it sees as a System.Byte datatype.
In the database both fields are tinyint datatypes and there is a
relationship between the two.
My question is: Do I have to increase the field to a smallint in order to
create a relationship in the model or is there a workaround. This is my
first attempt at creating a data model for the Report Builder and any tips
would be appreciated. Thanks.I've noticed that if a key is an IDENTITY field then, regardless, it is seen
as an Int32. The behavior seems consistent in reporting services and in
analysis services. What we've decided to do is to make all primary and
foreign keys standard Ints (Int32) instead of tiny and small ints - even when
the smaller values were appropriate. The other thing you could do is to make
the table a named query and cast the key as a smaller int so the relationship
works.
"Elmer Miller" wrote:
> I am trying to create a data source view that includes a Fact table and
> several related dimension tables. One of my dimension tables is quite small
> and uses a tinyint PK. For some reason when I bring this table into the
> model it thinks the PK is a System.Int32 datatype and won't allow a
> relationship to the fact table FK which it sees as a System.Byte datatype.
> In the database both fields are tinyint datatypes and there is a
> relationship between the two.
> My question is: Do I have to increase the field to a smallint in order to
> create a relationship in the model or is there a workaround. This is my
> first attempt at creating a data model for the Report Builder and any tips
> would be appreciated. Thanks.
>
>|||sounds correct to me.
I had an identity on a TinyInt and I think that it just blatantly
friggin crashed SSAS or something ridiculous
-Aaron
Aaron wrote:
> I've noticed that if a key is an IDENTITY field then, regardless, it is seen
> as an Int32. The behavior seems consistent in reporting services and in
> analysis services. What we've decided to do is to make all primary and
> foreign keys standard Ints (Int32) instead of tiny and small ints - even when
> the smaller values were appropriate. The other thing you could do is to make
> the table a named query and cast the key as a smaller int so the relationship
> works.
> "Elmer Miller" wrote:
> > I am trying to create a data source view that includes a Fact table and
> > several related dimension tables. One of my dimension tables is quite small
> > and uses a tinyint PK. For some reason when I bring this table into the
> > model it thinks the PK is a System.Int32 datatype and won't allow a
> > relationship to the fact table FK which it sees as a System.Byte datatype.
> > In the database both fields are tinyint datatypes and there is a
> > relationship between the two.
> > My question is: Do I have to increase the field to a smallint in order to
> > create a relationship in the model or is there a workaround. This is my
> > first attempt at creating a data model for the Report Builder and any tips
> > would be appreciated. Thanks.
> >
> >
> >