CODEWITHSUNDEEP
mysqlsqldatabasepostgresqldatabase-design
AlexWithYou
AlexWithYou

Reputation: 429

Insert value to Star schema fact table

I'm stuck when I was designing star schema. Here is my problem

I have several dimension table already designed. with customer table (customer_id, name, address) (200 rows) inventory table (film, category,inv_id) (400 rows) store table (store_id, store) (4 rows) and sale table (sale_date,sale_id) (16500 rows)

I'm trying to insert value into fact table (newly created, empty) payment (FK customer_id,FK inv_id,FK store_id,FK sale_id, payment_amount) I have 15650 payment records. How could I insert these values into fact table?

When I use

insert into payment_amount
select amount
from original

It runs an error. NOT null violation for foreign keys

What should I do to include these values into fact table? I know I have conceptual error here, hope you can give me a good clarification

Upvotes: 0

Views: 1126

Answers (1)

Pankaj Singh
Pankaj Singh

Reputation: 46

The error being thrown may be because these values do not exist in the parent dimensional table - customer table,inventory table, store table , sale table or else once you are trying to load this values in the fact table, the rest of fields with not null constraint are not being inserted. Your design misses the basic relationship between table criteria. For e.g, how is customer related with sale or how is sale related with store

For e.g, You can have some relationships like - store table -> customer table,sale and inventory table. These relations can be one to many, one-one or many to one kind. So that you can identify a unique sale to a customer from a particular store for particular inventory

You need to design the data flow in such a way that : 1. first a Staging table which will may be non-persistent and will be landing area for all the ingestion from the different sources 2. Have some intermediate table which will contain transformation of data from the staging tables 3. Create the dimension table from the staging and intermediate tables 4. Create the Fact table from dimension and intermediate tables

As a best practice - load the records in the below flow so that there is no conflict of keys - Staging -> Intermediate -> Dimension -> Facts

In ideal scenario - You need not mentions the Keys explicitly, instead have a cleaning job on top of it.

Upvotes: 0

Related Questions