Hi Guys,

I have been to L& T last week ..Please find the questions
mentioned below
1)Slowly changing dimension types
2)DML statement
3)DDL statement
4)write a syntax to create database
5) differences between count(*) and count(column)
6) differences between union and union all
7) what is decode
8) what is NVL
9) Arrange the values in ascending order 10,5,20,15,null
10)Primary and surrogate key
11)what is group by clause does?
12)what is difference between truncate and delete
13)what is difference between OLAP and OLTP ?
14)Architecture of Datawarehouse
15)how do you test the data validation from source as (flat
file and xml) to staging tables.
16)what is shedule and time bound

the mentioned above question asked in two rounds.all the
best

Answers were Sorted based on User's Feedback



Hi Guys, I have been to L& T last week ..Please find the questions mentioned below 1)Slowl..

Answer / kashish

ans1:-Slowly Changing Dimensions:

The "Slowly Changing Dimension" problem is a common one
particular to data warehousing. In a nutshell, this applies
to cases where the attribute for a record varies over time.
We give an example below:
Christina is a customer with ABC Inc. She first lived in
Chicago, Illinois. So, the original entry in the customer
lookup table has the following record:
Customer Key Name State
1001 Christina Illinois
At a later date, she moved to Los Angeles, California on
January, 2003. How should ABC Inc. now modify its customer
table to reflect this change? This is the "Slowly Changing
Dimension" problem.
There are in general three ways to solve this type of
problem, and they are categorized as follows:
Type 1: The new record replaces the original record. No
trace of the old record exists.
Type 2: A new record is added into the customer dimension
table. Therefore, the customer is treated essentially as
two people.
Type 3: The original record is modified to reflect the
change.
We next take a look at each of the scenarios and how the
data model and the data looks like for each of them.
Finally, we compare and contrast among the three
alternatives.
Type 1 Slowly Changing Dimension:
In Type 1 Slowly Changing Dimension, the new information
simply overwrites the original information. In other words,
no history is kept.
In our example, recall we originally have the following
table:
Customer Key Name State
1001 Christina Illinois
After Christina moved from Illinois to California, the new
information replaces the new record, and we have the
following table:
Customer Key Name State
1001 Christina California
Advantages:
- This is the easiest way to handle the Slowly Changing
Dimension problem, since there is no need to keep track of
the old information.
Disadvantages:
- All history is lost. By applying this methodology, it is
not possible to trace back in history. For example, in this
case, the company would not be able to know that Christina
lived in Illinois before.
Usage:
About 50% of the time.
When to use Type 1:
Type 1 slowly changing dimension should be used when it is
not necessary for the data warehouse to keep track of
historical changes.

Type 2 Slowly Changing Dimension:
In Type 2 Slowly Changing Dimension, a new record is added
to the table to represent the new information. Therefore,
both the original and the new record will be present. The
newe record gets its own primary key.
In our example, recall we originally have the following
table:
Customer Key Name State
1001 Christina Illinois
After Christina moved from Illinois to California, we add
the new information as a new row into the table:
Customer Key Name State
1001 Christina Illinois
1005 Christina California
Advantages:
- This allows us to accurately keep all historical
information.
Disadvantages:
- This will cause the size of the table to grow fast. In
cases where the number of rows for the table is very high
to start with, storage and performance can become a
concern.
- This necessarily complicates the ETL process.
Usage:
About 50% of the time.
When to use Type 2:
Type 2 slowly changing dimension should be used when it is
necessary for the data warehouse to track historical
changes.
Type 3 Slowly Changing Dimension :
In Type 3 Slowly Changing Dimension, there will be two
columns to indicate the particular attribute of interest,
one indicating the original value, and one indicating the
current value. There will also be a column that indicates
when the current value becomes active.
In our example, recall we originally have the following
table:
Customer Key Name State
1001 Christina Illinois
To accomodate Type 3 Slowly Changing Dimension, we will now
have the following columns:
• Customer Key
• Name
• Original State
• Current State
• Effective Date
After Christina moved from Illinois to California, the
original information gets updated, and we have the
following table (assuming the effective date of change is
January 15, 2003):
Customer Key Name Original State Current State
Effective Date
1001 Christina Illinois California 15-
JAN-2003
Advantages:
- This does not increase the size of the table, since new
information is updated.
- This allows us to keep some part of history.
Disadvantages:
- Type 3 will not be able to keep all history where an
attribute is changed more than once. For example, if
Christina later moves to Texas on December 15, 2003, the
California information will be lost.
Usage:
Type 3 is rarely used in actual practice.
When to use Type 3:
Type III slowly changing dimension should only be used when
it is necessary for the data warehouse to track historical
changes, and when such changes will only occur for a finite
number of time.
Surrogate key :
A surrogate key is frequently a sequential number but
doesn't have to be. Having the key independent of all other
columns insulates the database relationships from changes
in data values or database design and guarantees
uniqueness.
Some database designers use surrogate keys religiously
regardless of the suitability of other candidate keys.
However, if a good key already exists, the addition of a
surrogate key will merely slow down access, particularly if
it is indexed.
The concept of surrogate key is important in data
warehouse ,surrogate means ‘deputy’ or substitute’.
surrogate key is a small integer(say 4 bytes)that can
uniquely identify the record in the dimension table.however
it has no meaning data warehouse experts suggest that
production key used in the databases should not be used in
the dimension tables as primary keys instead in there place
the surrogate key have to be used which are generated
automatically.

Conceptual, Logical, And Physical Data Models:
There are three levels of data modeling. They are
conceptual, logical, and physical. This section will
explain the difference among the three, the order with
which each one is created, and how to go from one level to
the other.
Conceptual Data Model
Features of conceptual data model include:
• Includes the important entities and the
relationships among them.
• No attribute is specified.
• No primary key is specified.
At this level, the data modeler attempts to identify the
highest-level relationships among the different entities.
Logical Data Model
Features of logical data model include:
• Includes all entities and relationships among them.
• All attributes for each entity are specified.
• The primary key for each entity specified.
• Foreign keys (keys identifying the relationship
between different entities) are specified.
• Normalization occurs at this level.
At this level, the data modeler attempts to describe the
data in as much detail as possible, without regard to how
they will be physically implemented in the database.
In data warehousing, it is common for the conceptual data
model and the logical data model to be combined into a
single step (deliverable).
The steps for designing the logical data model are as
follows:
1. Identify all entities.
2. Specify primary keys for all entities.
3. Find the relationships between different entities.
4. Find all attributes for each entity.
5. Resolve many-to-many relationships.
6. Normalization.
Physical Data Model
Features of physical data model include:
• Specification all tables and columns.
• Foreign keys are used to identify relationships
between tables.
• Denormalization may occur based on user
requirements.
• Physical considerations may cause the physical data
model to be quite different from the logical data model.
At this level, the data modeler will specify how the
logical data model will be realized in the database schema.
The steps for physical data model design are as follows:
1. Convert entities into tables.
2. Convert relationships into foreign keys.
3. Convert attributes into columns.
4. Modify the physical data model based on physical
constraints / requirements.

Is This Answer Correct ?    10 Yes 1 No

Hi Guys, I have been to L& T last week ..Please find the questions mentioned below 1)Slowl..

Answer / vignesh

Count(*) will count all records including nulls and count
(column_name) will give count of non-null values only.

Is This Answer Correct ?    4 Yes 0 No

Post New Answer

More Data Warehouse General Interview Questions

what are the similarities between ROLAP and MOLAP

2 Answers   Siemens,


Explain what are the various file formats involved in reportnet?

0 Answers  


What is bi?

0 Answers  


what is the difference between DSS and ODS ?

2 Answers   IBM, Satyam,


How many different schemas or dw models can be used in siebel analytics. I know only star and snow flake and any other model that can be used?

0 Answers  






If I have table T1 and table T2 T1 has 250 cols and 10,000 rows T2 has 2 billion rows and 10 cols Which one would be Fact table and which one would be Dimension table and why ?

3 Answers   Extech, Principal Finance,


What is Bulk Insert?

3 Answers  


What are the key features of chameleon that separates it from other algorithms?

0 Answers  


What is the 4 + 1 view model as it relates to system modeling?

0 Answers  


Hi,Please can u tell me.. What is the difference between a Hyperion Essbase CUBE and a Star Schema? Thanks in advance, Vijay.

1 Answers   iFlex, Wipro,


What is meta data and system catalog?

0 Answers  


What is is a star schema?

0 Answers  


Categories