Data Warehouse – Physical(PDM) and logical model (LDM) Why ??

Data Warehousing – Why a physical(PDM) and logical model (LDM).

I have been asking myself this question a few times that’s when I realised it is needed for several reasons.

1) Logical Model
Reason for having it:

  • Shows a non technical person what is in a database with attributes that are normal names ie FirstName and not FNAME saying this do not show types to the user – no need they will see this on the physical model.
  • Shows relationships between information (tables) in a logical manner i.e. a company can have multiple staff members not like One Co_Company to many EMP_Staff Table.
  • Shows sharing between information
  • A building block to implementing the physical structures
  • Assists in the impact analysis if changes are to be made on the database.
  • Pokes the business users mind to think a bit more before signing off the technical solution and later just to simply play the blame game. – Lets not do that.I know it seems like a waste of time and money to do the logical model first.Yes I am technical person first then a “documenter” and it’s “easy “ to do a physical models in minutes with all the new database tools like SQL server. Its click-click and you are done these days not?

Now imagine the user ask for a change on one table, what is the impact? And damn it is hard to explain a simple physical data model to a non-technical person without the logical model. I have tried a few times and they always know what is going on until we implement the solutions then we start all over again.
So avoid this and do the logical model first and answer those question that they needed to be answered on paper first with a logical model. Remember the without the business there will be no IT or work to be done so the business need comes first. The simpler the data format the better the results to the business.

See more:

2) Physical Model

Reason for having it:

  • Not to be obvious – no physical model then no data it’s where you store it all.
  • To optimize the actual data use. I.e. store one table’s data on several hard drives for fast retrieval.
  • To split the actual subject areas into several tables with joins in order to make every table as effective and speedy as possible.
  • All fields are data type specified and optimised for the database platform selected – man you need to take really good care of this or it will bite you later with regards to storage/speed etc.
  • Re-doing it will take time and a lot of money depending on the usage in your business.
  • Surrogate and lookup keys splits data and relationships into tables (Will go into this on a feature blog)

This is my favourite part getting down in dirty in the code to load the physical data model and to create it in order to get it to load as fast as possible and for the business users to get to the data and use it in its simplest format, a format that must be understandable to them – or what’s the use of the database.
I will blog on this “how to load data in star schema etc” a bit later.
Remember the model must be created in such a way that it takes consideration of the database platform – use the best techniques for the platform forget what you know of other platforms they differ or we would have had only one platform.

See more:

{Views and opinions on this Blog does not reflect current/past employers view(s).}


Tagged: , ,

One thought on “Data Warehouse – Physical(PDM) and logical model (LDM) Why ??

  1. Martijn Evers (@DM_Unseen) November 1, 2012 at 2:54 pm Reply

    Christian, see my blog post on Concerning Data Model Madness on classifying kinds of data models:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: