What is ETL (Extract, Transform, Load)

ETL refers to the methods involved in accessing and   manipulating source   data and loading it into target database. During the ETL process, more   often, data is extracted from an OLTP database, transformed to   match the data warehouse schema, and loaded into the data warehouse   database.

(Extract, Transform, Load) –

ETL Process

 EXTRACTION :

• Data Capturing: The ETL extraction element is responsible for extracting data from the source system. During extraction, data may be removed from the source system or a copy made and the original data retained in the source system.

Data Capturing

• Data Transmission: Legacy systems may require too much effort to implement such offload processes, so legacy data is often copied into the data warehouse, leaving the original data in place. Extracted data is loaded into the data warehouse staging area (a relational database usually separate from the data warehouse database), for manipulation by the remaining ETL processes. 

Data Transmission

• Cleansing Process: 

1. Data extraction is generally performed within the source system itself.
2. Data extraction processes can be implemented using Transact-SQL stored procedures, Data Transformation Services (DTS) tasks, or custom applications developed in programming or scripting languages. 

Transformation:

         The ETL transformation element is responsible for data validation, data accuracy, data type conversion, and business rule application. An ETL system that uses inline transformations during extraction is less robust and flexible than one that confines transformations to the reformatting element. Transformations performed in the OLTP system impose a performance burden on the OLTP database.

• Data Validation: Check that all rows in the fact table match rows in dimension tables to enforce data integrity.

• DataAccuracy : Ensure that fields contain appropriate values, such as only "off" or "on" in a status field.

• Data Type Conversion : Ensure that all values for a specified field are stored the same way in the data warehouse regardless of how they were stored in the source system. For example, if one source system stores "off" or "on" in its status field and another source system stores "0" or "1" in its status field, then a data type conversion transformation converts the content of one or both of the fields to a specified common value such as "off" or "on".

Transformation

• Business Rule Application: Ensure that the rules of the business are enforced on the data stored in the warehouse. For example, check that all customer records contain values for both FirstName and LastName fields. 

Loading :

• The ETL loading element is responsible for loading transformed data into the data warehouse database.
• Data warehouses are usually updated periodically rather than continuously, and large numbers of records are often loaded to multiple tables in a single data load.
• The data warehouse is often taken offline during update operations so that data can be loaded faster and SQL Server 2000 Analysis Services can update OLAP cubes to incorporate the new data. BULK INSERT, bcp, and the Bulk Copy API are the best tools for data loading operations.
• The design of the loading element should focus on efficiency and performance to minimize the data warehouse offline time.

Facebook Commentbox

    

Logical Design In Datawearhouse

Logical Data Model (LDM) -

• A logical design is conceptual and abstract. The process of logical design involves arranging data into a series of logical relationships called entities and attributes.
• Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.
• An entity represents a chunk of information. In relational databases, an entity often maps to a table. An attribute is a component of an entity and helps define the uniqueness of the entity. In relational databases, an attribute maps to a column.

Logical design  in Data wearhouse

Important Terms:

Entity

•      Entity: Are the principal data object about which information is to be collected. A class of persons, places, objects, events, or concepts about which we need to capture and store data.

1.      Persons:  agency, contractor, customer, department, division, employee,       instructor, student, supplier.
2.       Places:  sales region, building, room, branch office, campus. 
3.       Objects:  book, machine, part, product, raw material, software license,  software package, tool, vehicle model, vehicle.  
4.       Events:  application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip.
5.       Concepts:  account, block of time, bond, course, fund, qualification, stock.
6. 
   

•      Relationship:A natural business association that exists between one or more entities. The relationship may represent an event that links the entities or merely a logical affinity that exists between the entities

Relationship

         An example of a relationship would be:

1. Employees are assigned to projects.
2. Student enrolling in a curriculum.
3. Projects have subtasks.
4. Departments manage one or more projects 

• 
  
•      Cardinality:The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-to-many. The minimum and maximum number of occurrences of one entity that may be related to a single occurrence of the other entity. Because all relationships are bidirectional, cardinality must be defined in both directions for every relationship.

Cardinality

•     Cardinality Notations:

       

    

Facebook Commentbox

Physical design in datawearhouse

Physical design is the creation of the database with SQL statements. During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database structure.

A complete physical data model will include all the database artifacts required to create relationships between tables or achieve performance goals, such as indexes, constraint definitions, linking tables, partitioned tables or clusters.

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables.

Features of a 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.
• Physical data model will be different for different RDBMS. For example, data type for a column may be different between MySQL and SQL Server.

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.

Physical Design Structures:

• Table spaces : A tablespace consists of one or more data files, which are physical structures within the operating system you are using. A data file is associated with only one tablespace. From a design perspective, table spaces are containers for physical design structures.

• Tables and Partitioned Tables : Tables are the basic unit of data storage. They are the container for the expected amount of raw data in your data warehouse. Using partitioned tables instead of non-partitioned ones addresses the key problem of supporting very large data volumes by allowing you to decompose them into smaller and more manageable pieces.

• Views : A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Views do not require any space in the database.

• Integrity Constraints : Integrity constraints are used to enforce business rules associated with your database and to prevent having invalid information in the tables. Integrity constraints in data warehousing differ from constraints in OLTP environments. In OLTP environments, they primarily prevent the insertion of invalid data into a record, which is not a big problem in data warehousing environments because accuracy has already been guaranteed.

• Indexes : Indexes are optional structures associated with tables or clusters. In addition to the classical B-tree indexes, bitmap indexes are very common in data warehousing environments.  

                             

Facebook Commentbox

SQL interview Qustions and Answers for freshers (List 2)

1) What is Index?

• A pointer to data having physical representation is called as Index.
• Record can be located quickly and efficiently by creating Indices on existing tables.
• Each index in a table has some valid name and we can have more than one index in different columns of a table.
• We can speed up queries by setting up index in a column of a table.
• In a table , each row is examined by sql server to fulfill our query is known as table scan and it only happen when there is no index available to help the query.
• On large tables, the table scan has huge impact on performance.
• Clustered and Non clustered indexes are the most widely used indexes in a database.

2) What is Trigger?

• A Trigger is a process of firing an action when some event like Insert, Update or Delete occurs.
• A trigger can’t be called or even executed rather they are automatically become active by the DBMS whenever some modification in associated table occur.
• Triggers are event driven and can attached to particular table in a database.
• Triggers are implicitly executed and stored procedures are also executed by triggers.
• Referential integrity is maintained by the trigger and they are managed and stored by DBMS.
• Triggers can be nested also, in which Insert, Update or Delete logic can be fired from the trigger itself.

3) What is a NOLOCK?

• NOLOCK is used to improve concurrency on a busy system.
• On data read, no lock can be taken on SELECT statement.
• When some other process is updating the data on the same time you are reading it is known as dirty read.
• Read (Shared) locks are taken by SELECT Statements.
• Simultaneous access of multiple SELECT statements is allowed in Shared lock but modification process is not allowed.
• The result to your system is blocking.
• Update will start on completion of all the reads.

4) What is the STUFF function and how does it differ from the REPLACE function?

• Using STUFF function we can overwrite the specified characters of a string.
• The syntax of STUFF function is
• STUFF (stringToChange, startIndex, length, new_characters )
• where stringToChange is the string which will have the characters those we want to overwrite, startIndex is the starting position, length is the number of characters in the string that are to be overwrited, and new_characters are the new characters to write into the string.
• While REPLACE function is used to replace specified character at all its existing occurances.
• The syntax of REPLACE function is REPLACE (string_to_change, string_to_Replace, new_tring).
• Every occurance of string_to_change will be replaced by new_string.

5) What are Self Join and Cross Join?

• When we want to join a table to itself then SELF JOIN is used.
• We can give one or more aliases to eliminate the confusion.
• A self join can be used as any type, if both the tables are same.
• The simple example where we can use SELF JOIN is if in a company have a hierarchal reporting structure and an employee reports to another.
• A cross join give the number of rows in the first table multiplied by the number of rows in second table.
• The simple example where we can use CROSS JOIJ is if in an organization wants to combine every Employee with family table to see each Employee with each family member.

6) What are the advantages of using Stored Procedures?

• Procedure can reduce network traffic and latency, and can enhance application performance.
• Procedure execution plans can be reused, staying cached in the management tool's memory, reducing its overhead.
• Procedures provide the benefit of code reuse.
• The logic can be encapsulated using procedures and can help to change procedure's code without interacting to application.
• Procedures give more security to our data.

7) What is RANK function?

• RANK function can be used to give a rank to each row returned from a SELECT statment.
  For using this function first specify the function name, followed by the empty parentheses.
• Then mention the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause identifies the column on which you are going to apply the RANK function.
• For Example
  SELECT RANK() OVER(ORDER BY BirthDate DESC) AS [RowNumber], FirstName, BirthDate FROM EmpDetails
• In the result you will see that the eldest employee got the first rank and the youngest employee got the last rank. Here the rows with equal age will get same ranks.
• The rank depends on the row's position in the result set, but not on the sequential number of the row.

8) What are cursors and when they are useful?

• When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.
• When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.
• There are two of cursors

1. Implicit Cursor :   SQL Server automatically manages cursors for all data manipulation statements.These cursors are called implicit cursors.   
2. Explicit Cursor:
 When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.They are managed by OPEN, FETCH and CLOSE.-%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are          used in both types of cursors.

9) What is Similarity and Difference between Truncate and Delete in SQL?Similarity

Both Truncate and Delete command will delete data from given          table and they will not delete the table structure from the     database. 

Difference

• TRUNCATE is a DDL (data definition language) command whereas           DELETE is a DML (data manipulation language) command.
• We can’t execute a trigger with TRUNCATE whereas with DELETE command, a trigger can be executed.
• TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion, whereas
• TRUNCATE will not store data in rollback space and will directly delete it.
• You can’t get the deleted data back when you use TRUNCATE.
• We can use any condition in WHERE clause using DELETE but it is notpossible with TRUNCATE.If table is referenced by any foreign key constraints then TRUNCATE cannot work.

10) What are COMMIT and ROLLBACK in SQL?

• COMMIT statement is used to end the current transaction and once the COMMIT statement is exceucted the transaction will be permanent and undone.
• Syntax: COMMIT;
• Example:
  BEGIN
  UPDATE EmpDetails SET EmpName = ‘Arpit’ where Dept = ‘Developer’
  COMMIT;
  END;
• ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction.
• Syntax: ROLLBACK [TO] Savepoint_name;
• Example
  BEGIN
  Statement1;
  SAVEPOINT mysavepoint;
  BEGIN
  Statement2;
  EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK TO mysavepoint;
  Statement5;
  END;
  END;

11) What is a WITH(NOLOCK)?

• WITH(NOLOCK) is used to unlock the data which is locked by the transaction that is not yet committed. This command is used before SELECT statement.
• When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction.
• Syntax: WITH(NOLOCK)
• Example:
• SELECT * FROM EmpDetails WITH(NOLOCK)
• WITH(NOLCOK) is similar as READ UNCOMMITTED

12) What is difference between Co-related sub query and nested sub query?

• Correlated subquery executes single time for every row which is selected by the outer query.
• It has a reference to a value from the row selected by the outer query.
• Nested subquery executes only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
  For example,
• Correlated Subquery:
  select e.EmpFirstName, e.Salary, e.DeptId from Employee e where e.Salary = (select max(Salary) from Employee ee where ee.DeptId = e.DeptId)
• Nested Subquery:
  select EmpFirstName, Salary, DeptId from Employee where (DeptId, Salary) in (select DeptId, max(Salary) from Employee group by DeptId)

13) Differentiate UNION, MINUS, UNION ALL and INTERSECT?

• INTERSECT - It will give all the distinct rows from both select queries.
• MINUS - It will give distinct rows returned by the first query but not by the second query.
• UNION - It will give all distinct rows selected by either first query or second query.
• UNION ALL - It will give all rows returned by either query with all dublicates records

14) What is a join? Explain the different types of joins?

• Using Join in a query, we can retrieve referenced columns or rows from multiple tables.
• Following are different types of Joins:
  1. JOIN: Return details from tables if there is at least one matching row in both tables
  2. LEFT JOIN: It will return all rows from the left table, even if there are no matching row in the right table
  3. RIGHT JOIN: It will return all rows from the right table, even if there is no matching row in the left table.
  4. FULL JOIN: It will return rows when there is a match in either of tables.

15) What is DDL, DML and DCL?

SQL commands can be divided in three large subgroups.

DDL: The SQL commands which deals with database schemas and information of how the data will be generated in database are classified as Data Definition Language.

• For example CREATE TABLE or ALTER TABLE belongs to DDL.
• DML: The SQL commands which deals with data manipulation are classified as Data Manipulation Language.
• For example SELECT, INSERT, etc.
• DCL: The SQL commands which deal with rights and permission over the database are classified as DCL.
• For example GRANT, REVOKE

Facebook Commentbox

Data Warehouse Design Approaches

There are two major types of approaches to building or designing the Data Warehouse.

1.  The Top-Down Approach
2.  The Bottom-Up Approach

s  The Top Down Approach:

•       The data flow in the top down OLAP environment begins with data extraction from the operational data sources. This data is loaded into the staging area and validated and consolidated for ensuring a level of accuracy and then transferred to the Operational Data Store (ODS). 

•      Detailed data is regularly extracted from the ODS and temporarily hosted in the staging area for aggregation, summarization and then extracted and loaded into the Data warehouse. 

•      Once the Data warehouse aggregation and summarization processes are complete, the data mart refresh cycles will extract the data from the Data warehouse into the staging area and perform a new set of transformations on them. This will help organize the data in particular structures required by data marts. Then the data marts can be loaded with the data and the OLAP environment becomes available to the users

DWH TOP DOWN DESIGN APPROACH

•     The data marts are treated as sub sets of the data warehouse. Each data mart is built for an individual department and is optimized for analysis needs of the particular department for which it is created.

 The Bottom-Up Approach:

          

•      Ralph Kimball designed the data warehouse with the data marts connected  to it with a bus structure.

•      The bus structure contained all the common elements that are used by data marts such as conformed dimensions, measures etc defined for the enterprise as a whole. 

•        This architecture makes the data warehouse more of a virtual reality than a physical reality

•       All data marts could be located in one server or could be located on different servers across the enterprise while the data warehouse would be a virtual entity being nothing more than a sum total of all the data marts

•      In this context even the cubes constructed by using OLAP tools could be considered as data marts.

Kimball Approach

•              The bottom-up approach reverses the positions of the Data warehouse  and the Data marts. Data marts are directly loaded with the data from the   operational systems through the staging area.

•                   The data flow in the bottom up approach starts with extraction of data  from operational databases into the staging area where it is processed  and consolidated and then loaded into the ODS.

•         The data in the ODS is appended to or replaced by the fresh data being loaded. After the ODS is refreshed the current data is once again extracted into the staging area and processed to fit into the Data mart  structure. The data from the Data Mart, then is extracted to the staging area aggregated, summarized and so on and loaded into the Data Warehouse and made available to the end user for analysis.

• 
  

Facebook Commentbox

Surrogate key in Datawearhouse with example

What is Surrogate Key?

• Surrogate Keys are integers that are assigned sequentially in the dimension table which can be used as PK.
• Surrogate key is a unique identification key, it is like an artificial or alternative key to production key, because the production key may be alphanumeric or composite key but the surrogate key is always single numeric key. 
• Assume the production key is an alphanumeric field if you create an index for this fields it will occupy more space, so it is not advisable to join/index, because generally all theDatawearhouse fact table are having historical data. 
• These fact Table are linked with so many dimension table. if it's a numerical fields the performance is high
• Surrogate key is the primary key for the Dimensional table.
• It’s a substitution for the natural primary key.
• It is just a unique identifier or number for each row that can be used for the primary key to the table. 
• The only requirement for a surrogate primary key is that it is unique for each row in the table.
• Data warehouses typically use a surrogate, (also known as artificial or identity key)  key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.
• It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Example:

          Let us consider an scenarios where you have designed a very good Data Warehouse , its catering all your reporting need and its in production. After two years organization decides to reuse there business keys of products

e.g.

         There was product called Baby Powder after two years organization decides to stop selling at its cost is high and sell is low and instead Baby Powder they launched a Talcum Powder for Men and want to give same key to the product say 336.

         Now while designing a Data Warehouse you have used Business Keys as Primary Key in dimension table with this new change you will have to update the dimension table to replace Baby Powder with talcum Powder and organization does want to remove all data of Baby Powder, what will you do now?

        So to avoid such situation its always better to use Surrogate Keys as Primary Key in dimension table along with Business Key.

e.g.

SK_Prouduct	Product_ID	Product Name	Cost	Acive
1	336	Baby Powder	444	N
2	345	Cream	34	Y
3	336	talkem Powder	44	Y

Now with above table you can use same code for another Product, mark the product as active and inactive and maintain all the product in Data Warehouse

Advantages of Surrogate Key:

• Surrogate Key allow to cater all your data need that arises from the Business or operational changes and recycling.

• Surrogate Keys allow the data warehouse to integrate data from all sources if they lack natural Business keys.

• Surrogate keys are very helpful for ETL transformations.

Facebook Commentbox