Star schema referential integrity error

UDA
oen
Enthusiast

Star schema referential integrity error

Hi,

my scripts:

CREATE TABLE Dim1
( PIND INTEGER NOT NULL PRIMARY KEY
, DATE_DAY DATE FORMAT 'YYYY-MM-DD'
, NUM_YEAR INTEGER
, NUM_MONTH INTEGER
, NUM_DAY INTEGER
)
;

CREATE TABLE Dim2
( PIND INTEGER NOT NULL PRIMARY KEY
, ATTR1 VARCHAR(100)
, ATTR2 VARCHAR(100)
, ATTR3 VARCHAR(100)
)
;

CREATE TABLE Dim3
( PIND INTEGER NOT NULL PRIMARY KEY
, ATTR1 VARCHAR(100)
, ATTR2 VARCHAR(100)
, ATTR3 VARCHAR(100)
, ATTR4 VARCHAR(100)
)
;

CREATE TABLE Facts
( PIND_DIM1 INTEGER NOT NULL REFERENCES Dim1
, PIND_DIM2 INTEGER NOT NULL REFERENCES Dim2
, PIND_DIM3 INTEGER NOT NULL REFERENCES Dim3
, VAL1 INTEGER
, VAL2 INTEGER
, VAL3 INTEGER
, VAL4 INTEGER
)
UNIQUE PRIMARY INDEX ( PIND_DIM1, PIND_DIM2, PIND_DIM3 )
;

And during insert:
INSERT INTO FACTS ( PIND_DIM1, PIND_DIM2, PIND_DIM3, VAL1, VAL2, VAL3, VAL4 )
SELECT DIM1.PIND
, DIM2.PIND
, DIM3.PIND
, DIM1.PIND + DIM2.PIND
, DIM2.PIND + DIM3.PIND
, DIM1.PIND + DIM3.PIND
, DIM1.PIND + DIM2.PIND + DIM3.PIND
FROM DIM1 DIM1
CROSS JOIN DIM2 DIM2
CROSS JOIN DIM3 DIM3
;

I get error:
5312: Aborted due to a non-valid reference index on Samples.Facts.

If I change Facts table definition into:

CREATE TABLE Facts
( PIND_DIM1 INTEGER NOT NULL REFERENCES WITH CHECK OPTION Dim1
, PIND_DIM2 INTEGER NOT NULL REFERENCES WITH CHECK OPTION Dim2
, PIND_DIM3 INTEGER NOT NULL REFERENCES WITH CHECK OPTION Dim3
, VAL1 INTEGER
, VAL2 INTEGER
, VAL3 INTEGER
, VAL4 INTEGER
)
UNIQUE PRIMARY INDEX ( PIND_DIM1, PIND_DIM2, PIND_DIM3 )
;

I can insert rows but if I want to delete them from table Facts I get:
5312: Aborted due to a non-valid reference index on Samples.Facts.

Could anyone help me?
regards,
oen
8 REPLIES
Enthusiast

Re: Star schema referential integrity error

Hi,
When I was playing with this sample, I didn’t encounter errors described above. In the pure version of sample code, there is no insert in dim tables and thus referential integrity (RI) can’t be violated. When you add simple “insert into values“ for them, it still works.
I’d suspect transaction and such stuff, see also Teradata documentation “SQL Reference: Data Definition Statements, p. 576”; let’s space here for some supreme being to teach us more.

I'd ask the more important question: "Why do you intent to design the DW with RI?” I’ve never seen it on Teradata. The relations are usually kept in the information delivery layer, like Microstrategy, Cognos Framework or Business Objects Universe.

-Petr

oen
Enthusiast

Re: Star schema referential integrity error

Above scripts works fine. I`ve found error in my original script. it was spelling mistake.
oen
Enthusiast

Re: Star schema referential integrity error

In my original script I had an error in FACTS table definition:
CREATE TABLE Facts
( PIND_DIM1 INTEGER NOT NULL REFERENCES Dim1
, PIND_DIM2 INTEGER NOT NULL REFERENCES Dim2
, PIND_DIM3 INTEGER NOT NULL REFERENCES Dim <------ wrong table
, VAL1 INTEGER
, VAL2 INTEGER
, VAL3 INTEGER
, VAL4 INTEGER
)
UNIQUE PRIMARY INDEX ( PIND_DIM1, PIND_DIM2, PIND_DIM3 )
;

btw
1. why did teradata allow me to create table without referenced table?
2. why did teradata allow me to insert values into such table? (with WITH CHECK OPTION)

thx oen.
Enthusiast

Re: Star schema referential integrity error

Well, I'm more pragmatic then theoretic. Regarding question # 2 - WITH CHECK OPTION controls RI on transactional level not a row level. Begin and end of transaction is influenced by session, tool and connection setup (Teradata or ANSI session mode; implicit transaction). Error ocures, when transaction is finished.

- Petr

btw: RI decrease performance and increase data volume. Still curious why use it?

oen
Enthusiast

Re: Star schema referential integrity error

thx.
- Petr, referential integrity is needed if I want to create aggregate join indexes on my data mart. without referential integrity I would have to create a lot of join indexes, with referential integrity I can build only one and it will cover a lot of queries.
Enthusiast

Re: Star schema referential integrity error

Thank you for explanation, it's interesting, never heard about that, but used AJI just for couple of time. Does it help Optimizer?
The common limitations of AJI are outer joins and aggtegated functions, which can be used (SUM, COUNT), but it is not probably this case. Usualy AJIs are done on higher level of dimension like support a monthly numbers from daily datamart. I'd be glad, if you can disclose more info, I'm fun of db design and tuning.

best regards

petr
Enthusiast

Re: Star schema referential integrity error

Reasons to implement RI
– Data integrity and consistency
– Increases development productivity – users
don’t have to code SQL statements to enforce
referential constraints
– Optimized performance – Teradata chooses
the most efficient method to enforce the
referential constraints
– User applications may rely on Referential
Integrity for their functionality
oen
Enthusiast

Re: Star schema referential integrity error

Hi,

Petr lets suppose that we have 3 dimensions and 1 facts table (like in the example above).
lets suppose that join index (or aji) is based on 3 dims and facts (all tables inner joined).

1. with or without referential integrity:
if you submit query which joins dim1, dim2, dim3 and facts index can be used
2. with referential integrity:
if you submit query which joins dim1 and facts index can be used because optimizer knows that facts rows reference rows from other dims (so he knows that inner join will not throw away those records)
3. without referential integrity
if you submit query which joins dim1 and facts index cannot be used because optimizer does not know if rows from facts reference rows from other dims and optimizer does not know if it is one-to-many or many-to-one or anything else.

oen