Videos uploaded by user “Tech Coach”
Materialized views in oracle - Part 1
In this video I have explained what are materialized views? What are there basic difference with views, and how they leverage query rewrite to optimize the performance with almost no change in existing code
Views: 15092 Tech Coach
Oracle SQL Interview Questions : Delete duplicate records
This is the 1st video of the video series Oracle Interview questions. The video Demonstrates how to delete duplicate records in Oracle. I will cover all the major common questions asked in SQL and PL/SQL interviews as a part of this series. Please do contribute questions that you have come across in any of your interviews
Views: 4394 Tech Coach
Dynamic Lookup Transformation in Informatica
This Video explains the limitations of Static lookup and then explains how dynamic lookup transformation provides that solution with a practical Scenario. The Initial PPT explains the generic concept followed by Implementation in powercenter
Views: 17048 Tech Coach
Analytical Functions in oracle explained with real examples
This video explains analytical functions and how they are implemented in real projects. Analytical functions are somewhat similar to aggregate functions,but they offer much more. Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function. I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions. You can find the sample problems along with dataset in the below link. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Views: 30833 Tech Coach
SCD: Slowly changing dimensions explained with real examples
The video explains what are slowly changing dimensions, Their relevance in data warehousing and which SCD type should be used in what kind of data scenario. The video Explains below with real project examples. SCD 0 - Passive method (No change in data) SCD 1 – Overwriting the old value (Latest Record Only) SCD 2 – Creating a new additional record (Maintains History) SCD 3 – Creating an additional Column (Rarely Used) SCD 4 – Using history table SCD 6 - The hybrid approach
Views: 35434 Tech Coach
Indexing in Oracle :B-Tree,Bitmap Indexing
This Video is the 1st tutorial in the video series Indexing in Oracle , The video series explains in detail, What are indexes?It's types, what index should be used in which scenario and other important thing in basic terminology. Note :You may want to watch the video with a higher playback speed(1.25 if it suits you more)
Views: 24939 Tech Coach
DWH Interview Question :Number of  fact and Dimension table in project
This is a common question asked in a DWH and BI interview, What are the number of fact and dimension tables you had in your project. While it seems that the answer to the question is numeric (4 fact and 10 dimensions . The interviewer is looking for specific details. This video explains how exactly you should answer such questions, It explains the entire answer with the help of a retail organisation.
Views: 8540 Tech Coach
Datawarehousing Concepts Basics (Fact and Dimension Table)
Fact, Dimension, Star Schema may sound little tricky specially to people who have never worked on a Datawarehouse, This video explains them in a simple manner with a real world example
Views: 104322 Tech Coach
Partitioning in Oracle Explained with Real project Examples : Introduction
This Video series will explain partitioning and its use cases referencing real project examples from different domain. It will explain what when and why of partitioning in a simple but elaborate manner. This is the 1st video which explains why partitioning is required and what are the advantages we gain from it. The following videos will explain when should we do it and how. Part 1 : Partitioning in Oracle : https://www.youtube.com/watch?v=m3q4lrE671Y&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=1 Part 2 : Partitioning in Oracle - Performance Basics :https://www.youtube.com/watch?v=OQ8LXbxLI5g&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=2 Part 3 : List Partitioning in Oracle : https://www.youtube.com/watch?v=w6kB-90pFa4&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=3 Part 4 : Range partitioning in Oracle : https://www.youtube.com/watch?v=VBkpI4Ki49Q&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=4 Part 5 : Hash Partitioning in Oracle : https://www.youtube.com/watch?v=2YxOF_sJMLI&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=5 Part 6 : Composite Partitioning in Oracle :https://www.youtube.com/watch?v=q_e9IkNtSWc&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=6 Part 7 : Reference Partitioning in Oracle :https://www.youtube.com/watch?v=p8gNwr6Rtdo&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=7 Part 8 : Local Vs Global Partitioned Index in Oracle 11g : https://www.youtube.com/watch?v=t77fyUt8XPw&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=8 Part 9 : Partitioning enhancement in 12 c :https://www.youtube.com/watch?v=o_WjapdXiQg&list=PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA&index=9
Views: 36849 Tech Coach
Informatica Interview Scenario :Listagg Function in Informatica
A source table has values Col1 Col2 a l a m a n b p b q x y The target table should have values Col1 Col2 a l,m,n b p,q x y The Video Demonstrates 2 approaches to achieve the same using listagg function in SQ query and by using expression and aggregation in case of flat file. Please post in comments section in case you want help with any scenario i will try to cover it in my next video
Views: 6970 Tech Coach
Informatica Scenario Rank Transformation :Selecting the top 10 salaried employees of a company.
The video demonstrates the scenario of selecting 10 top salaried employees in an organization it also depicts the limitation of rank transformation in selecting 5th-10th highest record and conveys how easily the same can be achieved using analytical functions in source qualifier query
Views: 9686 Tech Coach
Local Vs Global Partitioned Index in Oracle 11g
The Video Explains the difference between Local Partitioned Indexes(Prefixed vs Non Prefixed Indexes). and Global Partitioned Index along with the challenges in maintaining Global partitioned Indexes when the underlying tables partitioned is dropped/truncated/Merged/Moved. Local Partitioned Index Shares the same boundaries as the table and are in the same number as table partitions they are widely used in DSS and DWH systems. While Global Partitioned Index are predominantly used in OLTP systems
Views: 9991 Tech Coach
Reverse Key Index :Types of Btree Index in Oracle
Please subscribe to my new channel. https://www.youtube.com/c/AnIndianAbroadd The Videos explains how Reverse Btree Index works and in what condition they shall be used. Reverse Btree index are used to solve index block contention. You can't perform range scans in reverse btree Index.
Views: 3336 Tech Coach
Transaction control transformation in Informatica explained with scenarios
The video explains Transaction control transformation in Informatica with real project scenario. The Dept heads of all departments wants to see all the Policies sold by the employees working in their respective department on a monthly basis. Using transaction control transformation the process will be automated and the files will be created department wise, So that only the Authorised individuals can see their department files
Views: 2527 Tech Coach
What are Junk dimensions?
A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators. The have low cardinality and usually don't come under SCD. If you require any clarifications for this video, Please drop me a comment and I will try to answer asap.
Views: 4753 Tech Coach
Informatica Scenario Converting Rows into Columns:Best Two approaches explained
The Video Demonstrates a scenario where the Source contains the scores of three students in three subjects in below format. ID Name Subject Score 100 Vivek Science 50 100 Vivek Maths 50 100 Vivek English 70 200 Amit Maths 80 300 Ankit Maths 40 200 Amit Science 70 300 Ankit Science 80 200 Amit English 60 300 Ankit English 60 It explains how we can display the scores of students in cross tabular format using pivot in Source qualifier query or using expression and aggregator in case if source is flat file
Views: 27767 Tech Coach
Informatica Scenario Segregating Unique and Duplicate Rows:Best 2 approaches
The Video demonstrates how the unique and duplicate records can be segregated from source and loaded to 2 different targets.It shows 2 approaches which covers for both table and flat file as soure
Views: 19333 Tech Coach
This Video explains the difference between ROWID and ROWNUM using real project examples. ROWID provides the unique physical address where the row is being stored. ROWNUM indicates the order in which the data was returned from the select query.
Views: 4986 Tech Coach
Informatica Scenario Convert single row to multiple rows:Normalizer Transformation/(Unpivot)
The Video Demonstrates the following scenario using various approaches including normalizer and unpivot and explains why we should choose our approach wisely while implementing . SOURCE COL1 COL2 COL3 A B C P Q R TARGET COL A B C P Q R
Views: 14840 Tech Coach
Star and Snowflake schema explained with real scenarios
Star and Snowflake schema are basic and vital concept of dataware housing. This video explains what are star and snowflake schema. Their differences and which should be used when in a very simplified manner with real examples. Please like this video and subscribe to my channel for more such content
Views: 49638 Tech Coach
Composite Partitioning in Oracle
This video is the 6th Video in the series partitioning in oracle. It explains the different composite partitioning approaches and in what scenario they should be used with real project use case explanations.
Views: 3982 Tech Coach
Fact Tables and Types of fact tables
This Video Explains what are fact tables in Data warehousing ? What is their relevance in Data warehousing ? What are the types of Fact tables present ? What kind of Fact tables you should choose when designing a data warehouse? In case if you have any queries please drop me a comment and I will be happy to help
Views: 19020 Tech Coach
ODS database (Operation data Store ), Its properties and purpose explained with examples
Most of the developers can't differentiate between ODS,Data warehouse, Data mart,OLTP systems and Data lakes. This video explains what exactly is an ODS, how is it different from the other systems. What are its properties that make it unique and if you have an ODS or a warehouse in your organisation
Views: 7902 Tech Coach
Oracle Interview Questions: Dual table in Oracle
This video is the 2nd video in the video series Oracle Interview questions.It explains dual table in oracle along with the advantages that it offers. Below tutorial explains how you can generate test data in oracle using oracle table and hierarchical queries. https://www.youtube.com/watch?v=Lf8m9lXNPnc&t=25s
Views: 3780 Tech Coach
Reference partitioning in Oracle 11g
The video explains Reference partitioning and its advantages in Oracle with real project example. It builds on the limitations of reference partitioning in oracle 11g (Interval partitioning) and scenarios where you should implement reference partitioning
Views: 2796 Tech Coach
Indexes in Oracle :Index Scan Methods :Part 2
The Video Explains when should you create indexes. The difference between Simple and composite Index, Relevance of order in composite indexes and Index Scan Methods in detail. 1.Index Unique scan 2.Index Range Scan 3. Index Skip Scan 4. Fast full Index Scan 5. Full Index Scan If you have any questions just drop in a comment
Views: 10842 Tech Coach
Using Oracle Metadata to your Advantage Explained with real scenarios.
In this video I have explained who you can use the Oracle metadata tables to your advantage with real project scenarios and situations. The video is a little descriptive it gives you much more than just the syntax and the tables. All database offer a bunch of tables using which you can make smart inferences about your data,process and health of the database. I have explained the below tables in this video and how to use them smartly 1.All_Source 2.all_objects 3.All_tables 4.all_tab_cons 5.all_constraints I will be sharing more such places where we can use these metadata queries so don't forget to subscribe to my channel. If you can questions/clarifications just drop a comment and I will be more than happy to solve your queries.
Views: 5991 Tech Coach
Oracle views explained with real project examples
This video demonstrates what are oracle views and why and when you should use them. It explains abstraction and security that the views offer with different real project examples. The DML operations you can perform on views and the restrictions. Syntax : CREATE VIEW view_name AS SELECT columns FROM tables [WHERE conditions]; Please do watch the video and leave your inputs/questions in the comments section, If you like the video give a thumbs up and share it with your friends who can benefit from this.
Views: 8911 Tech Coach
Execute Immediate in PL/SQL Explained with example.
Execute Immediate is mainly used to run native dynamic sql, This video takes a lay men approach to easily but precisely explain the concept using real project examples.
Views: 1845 Tech Coach
List partitioning in Oracle : Part 3
This video explains list partitioning in detail with 2 real project examples. I recommend watching these 2 videos on partitioning prior to watching the current one. 1. https://www.youtube.com/watch?v=m3q4lrE671Y&t=25s 2. https://www.youtube.com/watch?v=OQ8LXbxLI5g&t=1312s
Views: 8022 Tech Coach
Materialized View In Oracle - Part 3
This video explains the different modes using which you can refresh your materialize views and the different scenarios in which you should opt for these strategies.
Views: 5157 Tech Coach
Materialized Views in Oracle - Part 2
The Video explains the different refresh options available for materialized views. It also specifies what refresh strategy should be used in which scenario.
Views: 7135 Tech Coach
Hash Partitioning in Oracle
The Video explains Hash partitioning in oracle and how it focuses on equal data distribution. It explains how hash partitioning uses partition joins to improve performance in VLDB by MPP.
Views: 8640 Tech Coach
Range Partitioning in Oracle
In this video I have explained what is range partitioning along with it's 2 real project use cases. I have also explained interval Partitioning as an extension of Range partitioning If you have not watched my Initial Videos on partitioning I will recommended watching them before watching this video Apologies for the 10 second video glitch between 6 and 7 minutes :(
Views: 6884 Tech Coach
DWH Interview Question : Granularity in Datawarehousing
The video explains an important interview question what is granularity in DWH. The granularity of a table is the finest level of detail it contains, while creating a fact table it is of vital importance to understand which dimensions will form that fact table or in other terms what is the lowest level of detail that can be fetched from the fact table. The video explains the same with real project examples Please do help me in building this new channel https://www.youtube.com/c/AnIndianAbroadd
Views: 2384 Tech Coach
Debugger in Informatica powercenter Explained
The video explains Usage of Informatica Debugger with dynamic lookup example. It provides step by step explanation of how to run powercenter Debugger and how it functions
Views: 873 Tech Coach
ETL(DWH) Testing Basics
This is the 1st video of the video series ETL testing where I will cover the practical aspect of DWH testing which will include both the ETL and reporting aspects.
Views: 3144 Tech Coach
Splitting and Spawning in B- Tree Index : How Blocks are stored
The video explains how Btree index is stored in blocks and when data is inserted into the underlying table how additional blocks are added to the index which leads to index Spawning and block spliting
Views: 2808 Tech Coach
Partitioning in Oracle - Performance Basics
This is the 2nd video from " Partitioning in Oracle " series, It explains how oracle stores and manages data. What is single Block IO and Multi Block IO ? Why full table scan is better than index access in few cases. The video is very elaborate, I have tried my level best to keep it as simple as possible
Views: 14339 Tech Coach
ETL Testing Tutorial  3 : Scheduling and Job run
This tutorial explains how the ETL jobs are scheduled and run.The tool used to explain the scheduling process is Autosys and Informatica powercenter is used to demonstrate the job run. It also explains PMCMD command and its associated syntax in detail and how even if Autosys, Application server and Informatica server are configured on different machines , Still how we can run the informatica workflow directly from autosys box.
Views: 5131 Tech Coach
Regular Expression Oracle - 1st Video : Practice Excercise
Please watch and subscribe to our new channel "An Indian Abroad" https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg This video is the 1st video of the video Series Regular expression in Oracle. Here we will discuss in details "Metacharacters " and how to perform complex searches easily using regular expressions. The series will cover 1.REGEXP_LIKE 2.REGEXP_SUBSTR 3.REGEXP_INSTR 4.REGEXP_REPLACE 5.REGEXP_COUNT with main focus 1,2 and 4. In this video tutorial I have added 12 problems and I will be posting the solutions in the next video tutorials.
Views: 509 Tech Coach
ETL testing tutorial 2 : Basic Testing Strategies/Categories Explained
The video explains the basic testing strategies and categories for the ETL projects. Which Includes 1.Metadata testing 2.Data testing 3.Performance Testing
Views: 3479 Tech Coach
Working with Flat files - Flat File Validation
Flat files are very commonly use to transfer data between internal and external systems, The video explains common things that you need to consider while validating and working with flat files in detail
Views: 4525 Tech Coach
The video explains BITMAP and BITMAP JOIN INDEX IN ORACLE and when you should create them on a column. BITMAP INDEXES should be dealt with carefully as they can lead to serious performance issues if the table is updated by multiple processes in parallel. Indexing Basics :https://www.youtube.com/watch?v=0X9bbtwTnuE&t=1095s Star and snowflake Schema :https://www.youtube.com/watch?v=Qq4yhhAk9fc&t=17s
Views: 7412 Tech Coach
Partitioning Enhancements in oracle 12c
Oracle has made 4 significant enhancements in 12c. 1.Mixing Interval and Reference partitioning 2.DDL operations on multiple partitions 3.Global Index maintenance 4.Cascade Operations from parent to child, Truncate and drop This video explains the enhancements in detail with practical examples
Views: 2540 Tech Coach
Joins in Oracle (SQL) Explained in detail with practical examples
The video demonstrates what are the different types of joins and who they work in SQL. The tutorial is a little elaborate to make sure that you understand different kind of joins and where you should use which kind of join. The 5 Kind of joins explained are. 1. Inner join (Join or simple join) :- Rows that are common to both tables are returned as the output. 2. Left Outer join (Left join):- Returns all rows from the left table along with matching rows from the right table 3.Right Outer join (Right join):- Returns all rows from the right table along with matching rows from the left table 4.Full outer join (Full join) :- Returns all the records when there is a match in either left or right table. 5. Cartesian Join : (Join with no where condition)also known as a Cartesian product, is a join of every row of one table to every row of another table.
Views: 11556 Tech Coach
Test Data Generation in SQL with in 1 minute
This tutorial explains how can you generate test data for tables with in 1 minute using only sql queries. It explains the 3 components used in data generation. 1.Dual table 2.DBMS_RANDOM 3.Connect by level and how you can combine them easily to generate huge amount of data with in seconds. The video touches on how to handle constraints but doesn't go in depth. If all of you find it useful and give me a thumbs like. I will prepare a detailed video explaining everything in simple manner with real projects examples. If you require any clarifications for the video content.Drop a comment and I will try to resolve asap. Please don't forget to like the video and do subscribe to my channel
Views: 3819 Tech Coach
Engineer’s Unemployed
A small Video on How more than 70% engineers will struggle for job this year and how to be a part of the remaining 30%. www.internshipsfromhome.com will you get what you deserve.
Views: 1092 Tech Coach
Informatica Scenario Load Alternate Records to Targets:Best Approaches
The Video Demonstrates 2 different appraoches how we can Load Alternate Records to Targets using Informatica The approaches are 1.Using SQl Override 2.In case of a flat file The Key Learning's from the video apart from the scenario are 1.Analytical Function Row_number() 2.Case in Oracle 3.Filter Transformation 4. Router Transformation The Video in its second half takes a next step and explains how we can load alternate records in different targets using the 2 apporaches
Views: 7981 Tech Coach
Informatica Scenario Retaining Values from previous rows
In the below scenario we have the source table as displayed where the first row contains the details of the manager accompanied by the details of employee reporting to him. We need to put the data in the target so that each employee detail maps with its corresponding manager in other column SOURCE seq EMP_ID ROLE 1 A Manager 2 B Employee 3 C Employee 4 D Employee 5 E Employee 6 F Manager 7 G Employee 8 H Employee 9 I Employee 10 J Employee 11 K Manager 12 L Employee 13 M Employee 14 N Employee Target EMPID_MANAGER EMP_ID_EMPLOYEE A B A C A D A E F G F H F I F J K L K M K N
Views: 5077 Tech Coach