CODEWITHSUNDEEP
database-designdata-warehouseolapoltp
archavin
archavin

Reputation: 323

Transforming Relational Database (OLTP) to Data Warehousing Model

This my first project around BI, i will create a Data Warehouse, based on existing relational database. I have a data base with 6 tables with many relations(One-To-Many)

I would like to give you an idea about the existing schema of the relational database:

-------------
HeadOperation
-------------
head_col1
head_col2
head_col3
col4
col5
col6
....


-------------
Item
-------------
head_col1
head_col2
head_col3
colItem1ID
colItem2
colItem3
valueitem

....

Every HeadOperation have at least one Item, we can say also the Item is the detail of the HeadOperation table.

head_col1, head_col1, head_col3 : is the primary key of HeadOperation and foreign key of Item table

To create a fact table and as new in BI modelization, i dont see how can i make a fact table, 1st there is multiple primary key(more than one primary key) and the Item table have the same key+its primary key colItemID.

another thing come in my mind is to merge/fusion those tables, but the data warehouse will be huge.

there is suggestion to resolve this problem of modelization ?

thanks

Upvotes: 1

Views: 960

Answers (1)

Abdul Ghaffar
Abdul Ghaffar

Reputation: 149

Definitely someone told you the right thing. Surrogate keys are just unique integer values most of the time auto-increment values. Then you should populate your dimension tables. Once your dimension tables are populated you should load data into you Fact table. After that optionally you can create Aggregate Fact tables if your Fact table size is very large.

Upvotes: 1

Related Questions