Data Modelling Interview Questions for Data Warehouse Data Modeller Role

What do you mean by Data Modelling?

Data modelling is an exercise to put the layout of the structured data in a meaningful way so that respective business support systems can read or write back their data. It gives birds eye view of how the business application stores the data and explains about how the data flows behind the scene.

What is entity, what is relationship and what is attribute?

Any place, class, object or person can be classified as entity. An entity can end up being multiple physical tables in the database. For example, Address can be an entity in conceptual data model and it can end up as Country table, State table and City table in the physical model.

What are different types of Data Modelling? What are the steps to build the data model? 

There are 3 main types of data model, 1. Conceptual 2. Logical 3. Physical. Conceptual model talks about overall data and its relation in the system. It also give us the information on how each entity is related to other entity and what is its relation with other entity.

Where is Data Modelling used?

Wherever the data is involved, there has to be data modelling. Sometimes if the application is small then modeler directly prepares the physical data model. If the system is large than it is advisable to have all the 3 (Conceptual, Logical and Physical) data models. If the system is transactional (for example, Ecommerce website) then Relational data modelling techniques are used. If the system is Data Warehouse, then the dimensional data modelling techniques are used.

How to create simple Informatica mapping.

Hi,

This blog will show how to create simple Informatica mapping. If you are new to Informatica, this below blow will help you to create Informatica mapping.


Step 1 : Import Source and Target definition.


After you connect to your Informatica repository folder of Informatica Power Center Designer open Source Analyzer from Tools menu.
If your source database is relational database then go to menu 'Source' and click on the 'Import from Database...'

[Figure 1]

As shown in the above Figure 1, provide ODBC data source name, username and password and click on connect. That will give the list of the tables available in the 'Select tables' area. Double click on the table which you want as a source. That will bring the table definition in the 'Source Analyzer' area.

In order to import target table definition, go to 'Target Designer'. Now click on the menu 'Target' and then click on 'Import form Database...'. As shown in the above Figure 1, provide ODBC data source name, username and password and click on connect. That will give the list of the tables available in the 'Select tables' area. Double click on the table which you want as a target. That will bring the table definition in the 'Target Designer' area.

[Figure 2]

[Figure 3]

Imported table will look like above shown Figure 2 and Figure 3.

In order to create sample mapping, Click on the menu 'Tools' and click on 'Mapping designer'. Once you are in the 'Mapping Designer', click on the menu 'Mappings' and then click on 'Create'. that will provide you and option to give mapping name as shown below in Figure 4. Give mapping name and click  'OK'. That will create the mapping. It is always good coding practice to give mapping name on the target table name and which starts with 'm_<target_table_name>'.

[Figure 4]

Once the mapping is created, we need to drag and drop the source and target table from the Navigator. If you have accidentally closed the Navigator then go to menu 'View'  and click on 'Navigator'. Drag the table into 'Mapping Designer'. Make sure that you have 'Mapping Designer' opened while dragging and dropping the table. As shown in the Figure 5 below, once you drag and drop the source definition it will automatically bring that table's 'Source Qualifier' with the name SQ_<source_table_name>. Now for example if you have 2 different source table then both the table will bring their own 'Source Qualifier'. And in mapping we can not have more than one 'Source  Qualifier' so we will have to delete one of the 'Source Qualifier' and connect ports of second table to the first tables 'Source  Qualifier'. This is all together a different topic so I'll cover this in some other blog.

[Figure 5]

Next step is to import target table in the mapping. In Navigator go to the Informatica folder and go to 'Targets'. Drag and drop the target table in to mapping. And connect the respective port from Source qualifier. Please refer Figure 6 below.

[Figure 6]

Save the mapping that will automatically validate the mapping. Make sure that mapping is valid. And Congratulations you have created the simple Informatica mapping.

Please comment if you find this blog useful.