Incompatible Data Types question

Hi,

I've got a dimension table with an Int32 primary key. I have a few fact tables that have an Int16 field, which is acting as a foreign key to the dimension table. I can't actually change the data types of these tables in the SQL database, but I'd still like to create a cube that uses those 32 bit dimension tables with the 16 bit field entries. Unfortunately, the cube designer won't let me specify a relationshop between the two tables because the fields are different data-types.
What I did is create Calculated Member fields which cast the 16 bit value to a 32 bit value, and then create the relationship. I was wondering if anyone else had a different solution?

Thanks,
KobiWe had to create temporary tables and cast the fields into matching data types.

Including view primary keys in relationships

I have two separate databases. One db as several tables that have foreign
key fields whose parents reside in the second db. I would like to be able
to define relationships between these entities.
I was thinking I could create a view of the parent table in the child's
database, but I don't see any way of including a view table as a
relationship partner. Nor do I see how to reference a table in a separate
database as a relationship partner.
Any advice would be greatly appreciated.
TIA.Peter J. Hunter wrote:
> I have two separate databases. One db as several tables that have foreign
> key fields whose parents reside in the second db. I would like to be able
> to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
> TIA.
You cannot create foreign keys across databases.
Keep the referencing tables and the parent tables in the same database,
then create views or synonyms that point to them from the other
database.
It's also possible to use triggers as a "constraint" to enforce the
foreign key.
Or you could replicate the data in both databases by some means.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Peter J. Hunter" <pjhunter@.wintersnet.net> wrote in message
news:%23R3zIUbiGHA.3408@.TK2MSFTNGP05.phx.gbl...
>I have two separate databases. One db as several tables that have foreign
>key fields whose parents reside in the second db. I would like to be able
>to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
If you are inclined to define relationships across databases, then you
probably shouldn't be using multiple databases. Perhaps you can use
multiple schemas inside a single database.
Davdi

include declared referential integrity

When i specify 'include declared referential integrity ' for an article
definition, does that mean that the primary key and foreign key definition
on that article are transferred to the subscriber ?
If the table Im replicating is a foreign key table and i specify that option
but i do not have the primary key table on the publisher, will the initial
distribution of the snapshot fail while trying to create this table cos it
cannot reference the primary key table that does not exist ?
Also when i add multiple primary key and foreign key tables to a publication
and specify the 'include declared referential integrity ' option, is
replication smart enough to create these tables on the subscriber in the
right order.. ?
Right now i already have multiple publications one for tables, views,indexed
views bcos replication is not smart enough to know what to create first..
Using trans replication
Thanks
Hassan,
choosing this option will create the PK-FK relationships only when the
related articles are in the same publication and actually have the PK and
the FK explicitly declared on the publisher. The order of creation on the
subscriber takes this into account, although in the case of merge
replication in particluar, we tend to use the Not For Replication attribute
anyway.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)