1

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

archavin
  • 313
  • 2
  • 5
  • 12
  • You have two columns named head_col1. Please fix this before we try to give an answer. – Walter Mitty Jun 12 '13 at 12:10
  • Before starting to build a datawarehouse, try to figure out what kinds of reporting you would need to do on it. – Olaf Jun 13 '13 at 13:25
  • i have problem of composite primary key in dimension table, this is my problem :s – archavin Jun 13 '13 at 20:35
  • For starters, you should change your column names in the warehouse to *business* names that make sense. So what does head_col1 represent? Put those in your question and it may help us answer. – N West Jun 13 '13 at 23:06
  • As someone told me, i should make a surrogate key and i should load data into dimension and after that load data in fact table. what do you think ? – archavin Jun 14 '13 at 00:57

1 Answers1

1

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.

Abdul Ghaffar
  • 151
  • 3
  • 14