diamension relationship

Hi

I have a diamension called 'mechanism of injury' and measure injurycount situated in the injury measure group.

I defined a relation between mechanism of them using the regular relationship connecting using the mechanismofinjuryid key in both tables (mechanismofinjury and injury). I tried to deploy the cube and it gave me the following error.

Errors in the OLAP storage engine: The attribute key cannot be found: ....

I changed the error handling of cube by right clicking on the cube and going to processes and then customising the error handling to ignore the error. Now i could deploy the cube but the injurycount measure is not showing up correct results.

Before defining the relationship between the diamension i was getting injurycount=2541 which was correct.

After defining relationship between the diamensions i am getting injurycount=2526

When i carefully looked at the injury table i found out that in injury table there are 15 (2541 - 2526) records with mechanismofinjuryid=NULL. It seems like the analysis services do not take these records into account while calculating the measures.

I don't understand how do i handle the nulls without changing the error handling options.

Any help would be greatly appreciated.

-padu.

One way to resolve this problem is to create a missing member in the dimension like MissingDimMember.

When you load the fact table you use this missing member for fact table records with missing dimension members or dimension members that are not found in the dimension table.

It is also possible to configure this solution directly in SSAS2005.

Have a look in BOL: Defining the Unknown Member and Null Processing Properties

HTH

Thomas Ivarsson

Diagrams SQL Server 2005

Hello,
I am working with the 2005er version.
I am missing the diagrams where I can show the tables and there relation
ships (th old daVinci tools).
Can somebody tell me where they are gone?
Thanks
Michael
--
Michael Zdarsky
www.zac-it.com
ZAC-IT GmbH
09190-9271010You should be discussing this in the beta groups for SQL Server 2005. But
since SQL 2005 B2 isn't covered under beta I can say that they diagrams are
not in the version of the beta you are working with. New features, such as
this one, may or may not be added to the final release.
--
Brian
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010|||http://www.aspfaq.com/sql2005/show.asp?id=6
Please use the SQL Server 2005 newsgroups to discuss SQL Server 2005.
That's what the groups were created for.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010

Diagram relation other DB

Hi,
Is it possible in sql server 2005 to make a relation (in diagrams) with
a table of an other database in the same sql server.
Thx
No- FK relationships can't span databases. The nearest you could get is to
separate the tables into schemas in one database and then define the
relationships there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison schreef:

> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?
|||Paul Ibison schreef:

> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?
|||No - a schema is just a "container". To all intents and purposes the number
of tables is limited only by the disk space. Each tables belongs to a schema
which itself belongs to a database eg the object PaulsDB.Paul.MyTable
describes a table called "MyTable" in the schema "Paul" in the database
"PaulsDB". So, you could migrate the tables from one database to the other
and change the schema to reflect the fact that it is a different set of
info. Each schema could be placed on a different filegroup also. However all
this is more of an Oracle way of doing things which is perhaps not ideal for
your circumstance. It might be that if the two databases are really strongly
related, you should just have one large database. The most obvious downside
for this is the backup size might prohibit it, and restoring a separate
database obviously won't be possible. So, if the backup policy of a single
database isn't acceptable, I'd implement the relationship constraint using a
trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Diagram relation other DB

Hi,
Is it possible in sql server 2005 to make a relation (in diagrams) with
a table of an other database in the same sql server.
ThxNo- FK relationships can't span databases. The nearest you could get is to
separate the tables into schemas in one database and then define the
relationships there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Paul Ibison schreef:

> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?|||Paul Ibison schreef:

> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?|||No - a schema is just a "container". To all intents and purposes the number
of tables is limited only by the disk space. Each tables belongs to a schema
which itself belongs to a database eg the object PaulsDB.Paul.MyTable
describes a table called "MyTable" in the schema "Paul" in the database
"PaulsDB". So, you could migrate the tables from one database to the other
and change the schema to reflect the fact that it is a different set of
info. Each schema could be placed on a different filegroup also. However all
this is more of an Oracle way of doing things which is perhaps not ideal for
your circumstance. It might be that if the two databases are really strongly
related, you should just have one large database. The most obvious downside
for this is the backup size might prohibit it, and restoring a separate
database obviously won't be possible. So, if the backup policy of a single
database isn't acceptable, I'd implement the relationship constraint using a
trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Diagram relation other DB

Hi,
Is it possible in sql server 2005 to make a relation (in diagrams) with
a table of an other database in the same sql server.
ThxNo- FK relationships can't span databases. The nearest you could get is to
separate the tables into schemas in one database and then define the
relationships there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Paul Ibison schreef:
> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?|||Paul Ibison schreef:
> No- FK relationships can't span databases. The nearest you could get is to
> separate the tables into schemas in one database and then define the
> relationships there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Schemas = diagrams?
So put ereything in one database.
How many tables can you create in 1 database?|||No - a schema is just a "container". To all intents and purposes the number
of tables is limited only by the disk space. Each tables belongs to a schema
which itself belongs to a database eg the object PaulsDB.Paul.MyTable
describes a table called "MyTable" in the schema "Paul" in the database
"PaulsDB". So, you could migrate the tables from one database to the other
and change the schema to reflect the fact that it is a different set of
info. Each schema could be placed on a different filegroup also. However all
this is more of an Oracle way of doing things which is perhaps not ideal for
your circumstance. It might be that if the two databases are really strongly
related, you should just have one large database. The most obvious downside
for this is the backup size might prohibit it, and restoring a separate
database obviously won't be possible. So, if the backup policy of a single
database isn't acceptable, I'd implement the relationship constraint using a
trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Diagram Editor

Hello,

i just moved over from MS Access to MSSQL.

when trying to save a relation in the Diagram editor
between 2 tables i get this error:

'abbrev' table saved successfully
'companies' table
- Unable to create relationship 'FK_companies_abbrev'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_companies_abbrev'. The conflict occurred in database 'intranet', table 'abbrev', column 'id'.

relation is between companies.id <-> abbrev.company

both are 4 byte INTs.

it works fine with the other 4 tables but not for this last one.

any ideas/clues about what could be wrong?
and is it usefull to use that diagram or should i redo all the relations just in the query editor everytime it's needed?

thanks in advance.

SorexAre they also both "identity" columns?

Make sure that you have set the following properties for these columns in their respective tables...

Identity: YES
Identity Seed: 1
Identity Increment: 1

Also, I would set both of these columns as PRIMARY KEYS in their respective tables, so that they can be relate to each other as FKs.|||>Are they also both "identity" columns?

no, the link is between companies.id & abbrev.company (not abbrev.id)

>Make sure that you have set the following properties for these
> columns in their respective tables...
>Identity: YES
>Identity Seed: 1
>Identity Increment: 1

done this for both ID's of the tables.

i've put a screenshot online to look at what it actually is

should be on http://users.skynet.be/sky81117/sql.gif|||what's the use of the diagram anyway?

when i reopen the things i did yesterday i see the relations but they dissapear when the table content appears.

so it's a waste of time to use this feature or not?|||Originally posted by sorex
what's the use of the diagram anyway?

when i reopen the things i did yesterday i see the relations but they dissapear when the table content appears.

so it's a waste of time to use this feature or not?

The major purpose of the Diagram Editor is providing a visual tool to identify referential integrity within your database. It is the exact same tool as "Relationships" in Access. Yes, you can identify FK constraints by using the "Manage Index" tool for each table, however, I find the Diagram Editor to be quite useful and easy to establish FK constraints and show referential integrity between tables within my database systems.

The major problem with what you have done so far is within your database schema design. When I said that you should have primary key IDs in your tables, I meant that you needed to specify a current column within the tables and make that the PK, not necessary adding another field. Also, your columns are not normalized across the two tables.

Why do you have duplicate Company and contact information columns within each table?

What is the purpose of the abbrev and company tables? How are they supposed to relate to one another?