What is denormalization and where is it used in BW?

    Requires Free Membership to View

Let me try to explain this with an example:

If you have a table,

TABLE1
0COMPANYCODE 0DIVISION 0CUSTOMER ZRECORDS
============....=========....==========....========
1001............LFA...........115........... 5
1001............POC...........121........... 9
1002............USA...........006........... 2
1002............IN ...........568........... 7

The primary key for this table is 0COMPANYCODE and 0CUSTOMER. For the same company code there are multiple values for 0DIVISION. In a database, if the data is stored in the above form, it creates a performance problem. So, instead of having one single table like the above, its divided into two tables as below.

TABLE2
0COMPANYCODE 0CUSTOMER ZRECORDS
============....============...========
1001................115..........5
1001................121..........9
1002................006..........2
1002................568..........7

TABLE3
0COMPANYCODE 0DIVISION
============...=========
1001.............LFA
1001.............POC
1002.............USA
1002.............IN

Having tables 2 and 3 are advantageous in DB. Having data in TABLE1 is called "DENORMALIZATION" and having the same data in two different tables TABLE2 and TABLE3 is nothing but "NORMALIZATION". But, for BW, if the data is in two different tables, you need a join and it takes more time to read and link the data killing performance. So, the TABLE1 is preferred. If you want to make a report of number of records by Division, you can just create a query very easily with TABLE1 but its not that easy when you have data separated in TABLE2 and TABLE3.

Hope this helps.

This was first published in November 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: