Delete Cascade on cyclic relationship

If I have 3 tables with cyclic relationship like this. Table A has a fk
referencing Table B, table C has a fk referencing table B and table A has a
fk referencing table C.
|--<--fk--|
| |
v |
A--fk-->B--fk-->C
Question is how do I handle "Delete Cascade". How do I write triggers to
handle this?
Thanks,
Tom DOn Sun, 23 Jan 2005 22:35:52 -0800, tom d wrote:

>If I have 3 tables with cyclic relationship like this. Table A has a fk
>referencing Table B, table C has a fk referencing table B and table A has a
>fk referencing table C.
> |--<--fk--|
> | |
> v |
> A--fk-->B--fk-->C
>
>Question is how do I handle "Delete Cascade". How do I write triggers to
>handle this?
>Thanks,
>Tom D
Hi Tom,
I just replieed to your question about the self-referencing table. The
trick for this cyclic relationship is essentially the same. If you are
sure that the nest level won't pass 32, just make a trigger for each of
the tables that will delete from the referencing table all rows
referencing a deleted row, make sure you enable nested and recursive
triggers and don't forget to start each trigger with
IF @.@.ROWCOUNT=0
RETURN
If you think the 32 level nesting limit might cause problems, use the
alternative approach outlined in my other message. This time, you'll have
to use three temp tables and run through a three-step cycle to add rows to
each of these temp tables in turn.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Defining Hirarchies Accross Dimensions

I have a cube whose dimensions include Product and Product Group. The Product Group has a Many-To-Many relationship with the cube through a bridge table to the Product dimension. Whenever I want to see the sales for product by product group on the cube browser I associate these two dimensions manually (dragging and dropping to create a hierarchy). Is it possible to define this hierarchy in the Product or Product Group dimension? Thanks in advance

+ Product Group

++++ Product

No, it is not possible to create a hierarchy spawns several dimensions.

But on the other side, try to see is there way you might be able to model your Product dimension to include ProductGroup attribute in it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks,

I know you should include these things in the product dimension, but the thing is that according to the business rules, these product groups could be anything because the users define these dynamically: regional areas, brands, etc. Moreover, a product could belong to many product groups and viceversa. This is why I had to create the group as a separate dimension.

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have data i

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have da

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?

Define more than one relationship per table?

Why is it not possible to define more than one relationship per table?

i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?

thank you::Why is it not possible to define more than one relationship per table?

This is possible. You are in error here. read the error message.

::i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2
::separate relationships.

THIS is not possible. You can only have one cascade.

::why can't i do this

Wrong location for this question. Ask the developers.

::and what are my alternatives?

Use a trigger.|||thank you for your response,

in my attempts to use a trigger, i can't seem to get around the error of a subquery returning more than one result. how can you implement a cascading delete with this restriction and/or how do your work around it?

thank you|||Triggers.

And get used to set based operation.

::i can't seem to get around the error of a subquery returning more than one result

What subquery?|||'subquery' refers to the DELETE query in my trigger.

more specifically, i have a DELETE trigger in a primary table. in this DELETE trigger, i have a DELETE query that deletes records in a foreign 'many' table. it is this DELETE query that is the 'subquery' in the error message as listed below;

error message
===============
Server: Msg 512, Level 16, State 1, Procedure triggerMyPrimaryTableDelete, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
===============

trigger code
===============
CREATE TRIGGER triggerMyPrimaryTableDelete
ON dbo.MyPrimaryTable FOR DELETE
AS
BEGIN
DELETE MyForeignManyTable
FROM MyForeignManyTable , deleted
WHERE MyForeignManyTable.ID = deleted.ID
END
===============

thank you