What is ETL in Sql ?

What is ETL: ETL stands for Extract, Transform, Load. Extract means to read data from source, Transform means to apply business logic on data & in last load means to add or load data on other server.

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse. ETL involves the following tasks:

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse. ETL involves the following tasks:

  • extracting the data from source systems (SAP, ERP, other operational systems), data from different source systems is converted into one consolidated data warehouse format which is ready for transformation processing.
  • transforming the data may involve the following tasks:
    applying business rules (so-called derivations, e.g., calculating new measures and dimensions),
    cleaning (e.g., mapping NULL to 0 or “Male” to “M” and “Female” to “F” etc.),
    filtering (e.g., selecting only certain columns to load),
    splitting a column into multiple columns and vice versa,
    joining together data from multiple sources (e.g., lookup, merge),
    transposing rows and columns,
    applying any kind of simple or complex data validation (e.g., if the first 3 columns in a row are empty then reject the row from processing).
  • loading the data into a data warehouse or data repository other reporting applications.

Why we need ETL ?: we need it because whenever you need to move some logic or data from one server to another server then you need to write ETL. for example: you have written a query i.e. using linked server or more than one database so it will take time to run so it’s better option to write ETL for particular data tables.

Advertisements
Posted in oracle, sql | Leave a comment

How do I find duplicate values in a table in Oracle?

To find duplicate value we need the sql group by & having clause as below.

SELECT V.FIELD_NAME1, COUNT(*) FROM TABLE_NAME V GROUP BY V.FIELD_NAME1 HAVING COUNT(*)>1

How query works?

  1. First it will group the same value by non-clustered index i.e. virtual indexing by oracle in-built.
  2. After grouping of same value, it will calculate the count of rows which is repeatedly.
  3. Query performance will be slower when you use Having clause because it will filter all data from actual output.
Posted in oracle, sql | Tagged | Leave a comment

What is clustered & non-clustered index in oracle?

Clustered index & Non-clustered index

  • A clustered index the table records are physically stored on the basic of that index, A table can have only one clustered index because of physically stored to the table.
  • A Non-clustered index that is one kind of logic or reference to the actual table & data will be stored to the outside of the table, a table can have more than one clustered index.
  • In Theory, each data object within an Oracle database is a totally separate entity.
    In practice, certain tables are frequently used together, especially when using a normalized database design.
    A cluster is a way of organizing data to leverage the related nature of data stored in different locations.

Advantage of cluseter

  • Since related data is stored together, the related data can be accessed with fewer data block reads. With a cluster, Oracle reads the cluster key, which directly points to the disk area that contains the data for that value of the key. The cluster delivers two advantages:
  • First of all, a query can retrieve all the related data with usually no more than 2 logical reads–one to get the cluster key and another to retrieve a data block that contains only the relevant data. For instance, if an employee table is clustered on the department number, a query for employees in a department would first retrieve the relevant cluster key for the department and then the employee rows for that department. If an order header table and an order detail table were clustered together, one read would retrieve the order cluster key and one more read would retrieve both the order header and detail rows for that order.
  • The second advantage is that the value for the cluster key is only stored once, and this reduces the amount of space required for storage.

When to use cluster?

  • The ideal places to use a cluster are when you have a group of tables that are frequently queried together, or when you have a single table that is frequently accessed by an index value. For example, you might cluster an order header table and an order detail table together. If you frequently accessed employees by department, you may want to cluster an employee table by department.

when not to use cluster

  • In some situations, a cluster is definitely not appropriate. You should not use a cluster in the following situations:
    If the value for a cluster key is updated frequently.
  • If the data for the values of the cluster key takes up more than one or two Oracle data blocks.
  • If you frequently require full table scans on the clustered data.
Posted in oracle, sql | Tagged , | Leave a comment

What is Agile methodology? Examples, when to use it, advantages and disadvantages

Agile software development methodology is an process for developing software (like other software development methodologies – Waterfall model, V-Model, Iterative model etc.) However, Agile methodology differs significantly from other methodologies. In English, Agile means ‘ability to move quickly and easily’ and responding swiftly to change – this is a key aspect of Agile software development as well.

Brief overview of Agile Methodology

  • In traditional software development methodologies like Waterfall model, a project can take several months or years to complete and the customer may not get to see the end product until the completion of the project.
  • At a high level, non-Agile projects allocate extensive periods of time for Requirements gathering, design, development, testing and User Acceptance Testing, before finally deploying the project.
  • In contrast to this, Agile projects have Sprints or iterations which are shorter in duration (Sprints/iterations can vary from 2 weeks to 2 months) during which pre-determined features are developed and delivered.
  • Agile projects can have one or more iterations and deliver the complete product at the end of the final iteration.

Example of Agile software development

Example: Google is working on project to come up with a competing product for MS Word, that provides all the features provided by MS Word and any other features requested by the marketing team. The final product needs to be ready in 10 months of time. Let us see how this project is executed in traditional and Agile methodologies.

In traditional Waterfall model –

  • At a high level, the project teams would spend 15% of their time on gathering requirements and analysis (1.5 months)
  • 20% of their time on design (2 months)
  • 40% on coding (4 months) and unit testing
  • 20% on System and Integration testing (2 months).
  • At the end of this cycle, the project may also have 2 weeks of User Acceptance testing by marketing teams.
  • In this approach, the customer does not get to see the end product until the end of the project, when it becomes too late to make significant changes.

The image below shows how these activities align with the project schedule in traditional software development.

Agile versus traditional software development methodologies

With Agile development methodology –

  • In the Agile methodology, each project is broken up into several ‘Iterations’.
  • All Iterations should be of the same time duration (between 2 to 8 weeks).
  • At the end of each iteration, a working product should be delivered.
  • In simple terms, in the Agile approach the project will be broken up into 10 releases (assuming each iteration is set to last 4 weeks).
  • Rather than spending 1.5 months on requirements gathering, in Agile software development, the team will decide the basic core features that are required in the product and decide which of these features can be developed in the first iteration.
  • Any remaining features that cannot be delivered in the first iteration will be taken up in the next iteration or subsequent iterations, based on priority.
  • At the end of the first iterations, the team will deliver a working software with the features that were finalized for that iteration.
  • There will be 10 iterations and at the end of each iteration the customer is delivered a working software that is incrementally enhanced and updated with the features that were shortlisted for that iteration.

The iteration cycle of an Agile project is shown in the image below.

Agile Methodology development-what is agile

gile software Development Methodology

This approach allows the customer to interact and work with functioning software at the end of each iteration and provide feedback on it. This approach allows teams to take up changes more easily and make course corrections if needed. In the Agile approach, software is developed and released incrementally in the iterations. An example of how software may evolve through iterations is shown in the image below.

Agile Iterative Development

Agile methodology gives more importance to collaboration within the team, collaboration with the customer, responding to change and delivering working software.

Agile development has become common place in IT industry. In a recent survey over 52% of respondents said that their company practiced Agile development in one form or another. Irrespective of your role in the organization, it has become essential to understand how Agile development works and how it differs from other forms of software development.

In traditional approach each job function does its job and hands over to the next job function. The previous job functions have to signoff before it is handed over the next job function authenticating that the job is full and complete in all aspects. For example, Requirement gathering is completed and handed over to design phase and it is subsequently handed over to development and later to testing and rework. Each job function is a phase by itself.

In Agile way of working, each feature is completed in terms of design, development, code, testing and rework, before the feature is called done. There are no separate phases and all the work is done in single phase only.

Difference between Agile model and Non-Agile models

Parameters

Agile Model

Non-Agile Models

Approach of this methodology This methodology is very flexible and adjustable and can adapt to the project needs. This methodology is not as flexible as Agile model and it’s tough to accommodate changes in the project.
Measurement of Success The success of the project in Agile model is measured by the Business value delivered. In this methodology the success of the project is measured by the Conformation to plan.
Size of the Project The Project size is usually small in Agile model. However larger projects can also be handled using the Scaled Agile Framework (SAFe). The project size is Large in non- Agile models.
Style of Management The style of management in Agile model is not centralized. It is distributed among the team members. The management style in the non-Agile models is dictatorial. Only one person is the decision maker and rest of the people follows him.
Ability to adapt to change In Agile model the changes are accepted and adapted as per the project needs. But in non-Agile models the changes are not accepted easily in the later stages of the development.
Documentation required Less documentation is required in Agile. More documentation is required in non-Agile models.
Importance of In Agile model more emphasis is given to the people that means it’s People- Oriented. In non-Agile models the more importance is given to the process hence it’s Process- Oreinted.
Cycles or iterations Agile methodology has many cycles or iterations which is also known as Sprints. But, in Non-Agile methodology the cycles are limited.
Planning in Advance There is minimal upfront planning in Agile methodology. In Non-Agile models the planning should be complete before the development starts.
Revenue In Agile method the return on investment is early in the project cycle. In non-Agile methods the return on investment is at the end of the project.
Size of the team The size of the team in Agile methodology is usually small and creative. But in Non-Agile models the team size is large.

When to use Agile and Non-Agile models

Project Attributes Agile Model Non-Agile Model
Requirement of the Project Requirements in Agile model can change as per the customer requirement. Sometimes requirements are not very clear. In Non-Agile models the requirements are very clear before entering into the development phases. Any change in the requirement is not easily accepted during the development phases.
Size of the Project The Project size is small in Agile model hence small team is required. But in Non-Agile models the Project size is usually big hence big team is required.
Design of the Project In Agile model the architecture is made as per the current requirements but is designed to be flexible. In Non-Agile models the architecture is made as per the current requirements as well as for future requirements.
Planning and Control of the Project In Agile model the planning of the project is Internalized and has qualitative control. But in Non-Agile models the plans are documented properly and have quantitative control.
Type of Customers Agile methodology is followed by the collaborated, dedicated collated and knowledgeable customers. In Non-Agile models the customers are of Contract provisions.
Developers required In Agile model the developers should be knowledgeable, analytically strong, collated and collaborative. In Non-Agile models the developers should be more Plan Oriented.
Refactoring In Agile model refactoring is not costly. But in Non-Agile models the refactoring is very costly.
Risks involved Usually in Agile models the chances of occurrence of unknown risks are more which can have major impact in the project. In Non-Agile models the risks are understood clearly and the impact of the risk in the project is very less.

Advantages of Agile Methodology

  • In Agile methodology the delivery of software is unremitting.
  • The customers are satisfied because after every Sprint working feature of the software is delivered to them.
  • Customers can have a look of the working feature which fulfilled their expectations.
  • If the customers has any feedback or any change in the feature then it can be accommodated in the current release of the product.
  • In Agile methodology the daily interactions are required between the business people and the developers.
  • In this methodology attention is paid to the good design of the product.
  • Changes in the requirements are accepted even in the later stages of the development.

Disadvantages of the Agile Methodology

  • In Agile methodology the documentation is less.
  • Sometimes in Agile methodology the requirement is not very clear hence it’s difficult to predict the expected result.
  • In few of the projects at the starting of the software development life cycle it’s difficult to estimate the actual effort required.
  • The projects following the Agile methodology may have to face some unknown risks which can affect the development of the project.
Posted in PHP | Tagged , | Leave a comment

How to get table size in MB oracle ?

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_type=’TABLE’ and lower(segment_name)=’your_table_name’;

Posted in oracle, sql | Tagged | Leave a comment

SQL Optimization Techniques

  • Optimize access structures:
    • Database design and normalization.
    • Tables: heap or index-organized tables, and table or indexed clusters.
    • Indexes.
    • Constraints.
    • Materialized views.
    • Partitioning schemes.
    • Statistics, including a comprehensive refresh strategy.
  • Rewrite SQL statements:
    • Exclude projections that are not required.
    • Minimize the amount of work done more than once.
    • Factor subqueries that are used multiple times in the same statement.
    • Use EXISTS instead of IN because the former stops processing once it has found a match.
    • Use CASE and/or DECODE to avoid having to scan the same rows over and over again, especially for aggregation functions that act on different subsets of the same data.
    • Use analytic functions to do multiple or moving/rolling aggregations with a single pass through the data.
    • Avoid scalar subqueries in the SELECT-list.
    • Use joins instead of subqueries, as it gives the optimizer more room to play around in.
    • Say what you mean and pick the right join: if you only need an inner join don’t write an outer join.
    • Add logically superfluous predicates that may still aid in the search for an optimal execution plan, particularly for outer joins.
    • Avoid implicit conversions of data types, especially in the WHERE clause.
    • Write WHERE clause predicates with a close eye on the indexes available, including the leading edge of a composite index.
    • Avoid, whenever possible, comparison operators such as <>, NOT IN, NOT EXISTS, and LIKEwithout a leading '%' for indexed columns in predicates.
    • Do not apply functions on indexed columns in the WHERE clause when there is no corresponding function-based index.
    • Don’t abuse HAVING to filter rows before aggregating.
    • Avoid unnecessary sorts, including when UNION ALL rather than UNION is applicable.
    • Avoid DISTINCT unless you have to use it.
    • Use PL/SQL, especially packages with stored procedures (and bind variables) and shared cursors to provide a clean interface through which all data requests are handled.
    • Add hints once you have determined that it is right and necessary to do so.
Posted in MysQl | Tagged , , , | Leave a comment

How to identify user idle state by minutes using javascript / JQuery?

Descrption:

i used with 20 minute of interval if user is in idle state from last 20 minute then it will call the timerIncrement() and then will check for 20 minutes if yes or not.

Why i need this?

i need to do this because of when i am creating an app using angular-4 then  i need to logout any user if he/she is in idle state from last 20 minutes.

$(document).ready(function () {
    var idleTime = 0;
    //Increment the idle time counter every minute.
    var idleInterval = setInterval(timerIncrement, 60000); // 1 minute

    //Zero the idle timer on mouse movement.
    $(this).mousemove(function (e) {
        idleTime = 0;
    });
    $(this).keypress(function (e) {
        idleTime = 0;
    });


function timerIncrement() {
    idleTime++;
    if (idleTime > 20) { // 20 minutes
         localStorage.removeItem('currentUser');

    }
}
});
Posted in angular-4, Javascript, jQuery | Tagged , , , , | Leave a comment