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.
> >
> >
> >

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.