<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Muhammad Salah ElOkda on Medium]]></title>
        <description><![CDATA[Stories by Muhammad Salah ElOkda on Medium]]></description>
        <link>https://medium.com/@Muhammad7Salah?source=rss-3b80c732b669------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*pSNlVlNPBeg5qYa_5fKvNQ.jpeg</url>
            <title>Stories by Muhammad Salah ElOkda on Medium</title>
            <link>https://medium.com/@Muhammad7Salah?source=rss-3b80c732b669------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 13:58:20 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@Muhammad7Salah/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Data Modeling Part 3: An Introduction to Dimensional Modelling (Kimball’s Technique)]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@Muhammad7Salah/data-modeling-part-3-an-introduction-to-dimensional-modelling-kimballs-technique-924ee423b226?source=rss-3b80c732b669------2"><img src="https://cdn-images-1.medium.com/max/1024/1*hAqfaS4H8srpWI6lKykfHg.jpeg" width="1024"></a></p><p class="medium-feed-snippet">In Part 2, we introduced the Enterprise Data Warehouse (EDW) data model, a more sophisticated approach that organizes your data warehouse&#x2026;</p><p class="medium-feed-link"><a href="https://medium.com/@Muhammad7Salah/data-modeling-part-3-an-introduction-to-dimensional-modelling-kimballs-technique-924ee423b226?source=rss-3b80c732b669------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@Muhammad7Salah/data-modeling-part-3-an-introduction-to-dimensional-modelling-kimballs-technique-924ee423b226?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/924ee423b226</guid>
            <category><![CDATA[dimensional-modeling]]></category>
            <category><![CDATA[ralph-kimball]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Sat, 12 Oct 2024 16:32:02 GMT</pubDate>
            <atom:updated>2024-10-12T16:53:42.810Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Data Modeling Part 2: An Introduction to Enterprise DWH(Inmon’s Technique)]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@Muhammad7Salah/data-modeling-part-2-an-introduction-to-enterprise-dwh-inmons-technique-e44ffd8bfdce?source=rss-3b80c732b669------2"><img src="https://cdn-images-1.medium.com/max/1792/1*4WNpz7gZh3DJUFMVTV3W5A.jpeg" width="1792"></a></p><p class="medium-feed-snippet">In Part 1, we introduced the One Big Table (OBT) data model, a simple yet effective method where all relevant data for a specific business&#x2026;</p><p class="medium-feed-link"><a href="https://medium.com/@Muhammad7Salah/data-modeling-part-2-an-introduction-to-enterprise-dwh-inmons-technique-e44ffd8bfdce?source=rss-3b80c732b669------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@Muhammad7Salah/data-modeling-part-2-an-introduction-to-enterprise-dwh-inmons-technique-e44ffd8bfdce?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/e44ffd8bfdce</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Sun, 06 Oct 2024 11:28:27 GMT</pubDate>
            <atom:updated>2024-10-06T11:28:27.238Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Data Modeling Part 1: An Introduction to the One Big Table (OBT) Technique]]></title>
            <link>https://medium.com/@Muhammad7Salah/data-modeling-part-1-an-introduction-to-the-one-big-table-obt-technique-fb37393f7d54?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/fb37393f7d54</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data-engineer]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Sat, 28 Sep 2024 12:32:29 GMT</pubDate>
            <atom:updated>2024-09-28T12:39:14.602Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dLFXfQqyriSIk6Wk9GRbvg.jpeg" /></figure><h3>Data modeling</h3><p>Data modeling is a cornerstone of data engineering, providing the framework to structure and organize data meaningfully. In data engineering, professionals are responsible for more than just moving data. The role encompasses a range of tasks, including extracting data from diverse sources, cleansing and transforming it, and loading it into systems like Data Lakes and Data Warehouses.</p><p>However, the true value of data lies in how well it can be used. This is where data modeling becomes critical. By creating an organized, well-defined structure for data, engineers ensure that it’s stored efficiently and optimized for analysis. A well-designed data model makes it easier for Data Analysts and Data Scientists to query and analyze the data, uncover trends, and extract actionable insights.</p><p>Moreover, as businesses continue to generate massive amounts of data from multiple systems and platforms — ranging from web applications and IoT devices to cloud services — data modeling helps streamline how that information is integrated and understood. Without proper data models, the data can be chaotic, redundant, and inefficient, making it difficult to extract value.</p><p>In summary, data modeling isn’t just about organizing data; it’s about creating a foundation for scalable, efficient, and insightful data use that drives business growth and innovation.</p><h3>Importance of Data Modelling</h3><p>When dealing with a small-scale operation, data management is often straightforward. Teams can easily run a few queries or write simple code to extract the necessary information and provide business insights. In these cases, the volume of data and its complexity are manageable, and even ad-hoc solutions may suffice.</p><p>However, as the company grows, so does its data — both in volume and complexity. Suddenly, the data is no longer simple or isolated; it becomes an intricate web of information generated from various sources like customer transactions, website activity, IoT devices, and third-party platforms. This exponential growth introduces new challenges. Queries that once provided quick answers to business questions start taking longer, or worse, they may no longer work effectively at all. The simple data structures and processes in place are no longer scalable, and finding relevant insights becomes a daunting task.</p><p>At this stage, the need for robust data modeling becomes critical. Without it, trying to manage or query such vast amounts of complex data can result in inefficiencies, inaccurate results, and missed opportunities. Proper data models ensure that as the business scales, the underlying data architecture remains organized, optimized, and adaptable to evolving business needs.</p><p>As data complexity grows, so does the need for more sophisticated approaches. This is where advanced data modeling comes into play. By designing new and more efficient data models, data engineers can streamline the process of answering business questions directly, without relying on convoluted queries or retrieving data from multiple locations.</p><p>A well-constructed data model consolidates information in a way that minimizes redundancy and allows for faster, more efficient access to relevant data. Instead of running complex, resource-intensive queries that pull data from various sources and require heavy processing, advanced data models provide a unified view that simplifies data retrieval. This not only improves performance but also enhances the accuracy and speed with which insights are delivered to the business.</p><p>For example, a data model designed with business intelligence in mind may aggregate relevant data into a single, accessible format. This eliminates the need to continuously transform and process raw data, allowing analysts and decision-makers to focus on deriving insights, not battling the complexity of the data itself. The result is a significant boost in efficiency and a data architecture that is scalable and adaptable to evolving business needs.</p><p>This article marks the beginning of my data modeling series, where I will explore various techniques that data engineers can use to optimize data management and analysis. In each article, I’ll dive into a specific method, discussing its benefits, applications, and best practices.</p><p>To kick things off, let’s start with one of the simplest and most straightforward approaches: the One Big Table (OBT) model. While it may not be the most advanced technique, OBT offers an easy-to-understand starting point for structuring data. In the next section, we’ll explore how OBT works and when it’s best used, setting the foundation for more complex modeling techniques to come.</p><p><strong>What is OBT (One Big Table)?</strong></p><p>The One Big Table (OBT) is exactly what its name suggests — a single, comprehensive table that consolidates all relevant information on a specific subject, such as sales or transactions. In the OBT model, all data is stored in a single, flat structure, eliminating the need to join multiple tables when querying the data.</p><h3>Advantages of the OBT Model</h3><ol><li><strong>Simplicity in Design and Implementation</strong><br>One of the biggest benefits of OBT is its simplicity. Since all the data is contained in one table, it’s easy to design and implement. There’s no need to worry about creating complex relationships or dependencies between multiple tables, making it an ideal starting point for smaller-scale data solutions.</li><li><strong>Faster Queries</strong><br>Because there are no joins involved, querying an OBT is typically faster than in more complex models. Since all the data resides in one place, retrieval is direct and doesn’t require complex joins or aggregations.</li><li><strong>Simplified ETL Processes</strong><br>With all data in one location, the Extract, Transform, Load (ETL) processes become much simpler. You don’t need to move and transform data between multiple tables or deal with complicated data integration workflows, which can speed up processing times.</li></ol><h3>Disadvantages of the OBT Model</h3><ol><li><strong>Data Redundancy</strong><br>One significant downside to OBT is data redundancy. Because all information is stored in a single table, data points are often duplicated. For example, if a customer makes multiple purchases, all of their details may be stored repeatedly for each transaction.</li><li><strong>Increased Storage Requirements</strong><br>Data redundancy leads to another challenge: increased storage consumption. The more records and duplicates you store, the larger your dataset becomes. Over time, this can make OBT a costly model in terms of storage, particularly for larger datasets.</li><li><strong>Data Integrity Issues</strong><br>Maintaining data integrity can be difficult in the OBT model. If data points are duplicated across multiple records, ensuring that these values remain consistent throughout the table becomes more complex. Any updates to a particular data point must be applied to all redundant copies, which increases the risk of inconsistencies.</li><li><strong>Limited Flexibility</strong><br>The OBT model is rigid and not well-suited for evolving data needs. If the data or business requirements change, modifying the structure can be time-consuming and difficult. Unlike more modular or normalized models, OBT lacks the flexibility to easily adapt to complex data relationships or new business questions.</li></ol><p><strong>Example of One Big Table (OBT) vs. Normalized Model</strong></p><p>Let’s first consider a normalized model in 3rd Normal Form (3NF) for an e-commerce system, commonly used in an OLTP (Online Transaction Processing) source system. In this structure, data is split across multiple tables to minimize redundancy and ensure data integrity.</p><h3>Normalized Model in 3NF</h3><p><strong>Clients Table</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/717/1*vdnFh-sIdj0Gxu7QefeHZQ.png" /></figure><p><strong>Products Table</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*TfJhao-8PMcrWWzot5tfPA.png" /></figure><p><strong>Orders Table</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*VRfla4CRvuVnn9hmWvDjfw.png" /></figure><p>In this normalized model, data is separated into distinct tables, and relationships between clients, products, and orders are defined using foreign keys (Buyer_ID and Product_ID), ensuring a clean structure with minimal duplication.</p><h3>Implementing the OBT Model</h3><p>Now, let’s simplify this structure by building a One Big Table (OBT) model for data analysis. The OBT model flattens the relationships by combining all relevant data into a single table. Here’s how it would look:</p><p><strong>One Big Table (OBT) Example</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mN8WIIqS6exCGzCB6LkvjA.png" /></figure><p>As you can see, the OBT model consolidates data from the clients, products, and orders tables into a single, large table. There are no foreign keys or joins-everything is stored in one place, making it simple to query.</p><p><strong>Populating the One Big Table (OBT)</strong></p><p>Now that we’ve discussed the benefits and drawbacks of the OBT model, let’s look at how to build it.</p><p>Below is an example SQL query that populates the OBT table by pulling together data from the normalized structure into a single flat table:</p><pre>INSERT INTO OBT_ORDERS<br>SELECT <br>    o.id AS Order_id, <br>    c.id AS Buyer_id, <br>    c.name AS Buyer_name, <br>    c.Email, <br>    c.phone,<br>    p.id AS Product_id, <br>    p.name AS Product_name, <br>    p.brand, <br>    p.category, <br>    p.price,<br>    o.order_date, <br>    o.order_time<br>FROM orders o<br>INNER JOIN clients c ON o.buyer_id = c.id<br>INNER JOIN products p ON o.product_id = p.id;</pre><p>This query joins the <strong>orders</strong>, <strong>clients</strong>, and <strong>products</strong> tables into a unified dataset, forming the OBT structure. It may not be the most efficient way to build each data model, but remember that the primary goal here is to quickly deliver business value. In scenarios like startups or when building a quick business model, the OBT technique provides a fast and straightforward solution that minimizes complexity.</p><p>Be sure to check out the next parts of this data modeling series to explore more advanced techniques that can help you scale and refine your data architecture for growing businesses. We’ll dive deeper into models that reduce redundancy, ensure data integrity, and provide more flexibility for long-term use.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fb37393f7d54" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Slowly Changing Dimension in Data Warehouse]]></title>
            <link>https://medium.com/@Muhammad7Salah/slowly-changing-dimension-in-data-warehouse-7ca21e0e8c9?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/7ca21e0e8c9</guid>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-warehousing]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[slowly-changing-dimension]]></category>
            <category><![CDATA[data-integration]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Sat, 20 Apr 2019 08:39:18 GMT</pubDate>
            <atom:updated>2022-01-03T00:51:54.739Z</atom:updated>
            <content:encoded><![CDATA[<h3>Slowly Changing Dimension in Data Warehousing</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/867/1*I6Ry40Qiudyzg7J8MQAaYQ.png" /><figcaption>Image Source: Microsoft</figcaption></figure><p>SCD (Slowly Changing Dimension) is a very important concept in ETL and Data Warehouse in general.</p><p>“Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse”<br>-<a href="https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/slowlychangingdimensions.htm"><strong>Oracle</strong></a></p><p>This methodology is used to track changes in the dimensions that change slowly over time.</p><p>Note: you can also read the article and more from my blog from this <a href="https://datawarehousetoday.blogspot.com/">link</a></p><p>Let’s have an example:</p><p>If a customer in a bank has an account and the balance is changing every business day and this is the normal case that does not need a SCD technique.<br>Then this customer decided to upgrade his account to a premium one.<br>Our assumptions here:<br>1. The bank wants to track the balance and other changes starting from the opening of the basic account and does not want to open a new account but just an upgrade to premium. <br>2. Upgrading the account to a premium one force the bank to change the account number to meet the criteria of the premium accounts(let’s say the premium account must start with number 2 and the basic account must start with 1).<br>2. While upgrading, the balance does not change.</p><p>So the SCD case here will be on the ACCOUNT_NUMBER field. So let’s discuss the SCD on this example.</p><p>There are main 3 types to handle this change and it depends on the case for which type to fit your data warehouse and your problem.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/428/1*28vLOBQhiZflvgiqOsIe-Q.png" /><figcaption>the original account of our customer</figcaption></figure><p>Our change is from account_number 1111111111111111 to 2222222222222222</p><p><strong>Type 1: Overwrite</strong></p><p>This type overwrites the whole record with a completely new record. hence, we cannot track the historical changes and this type does not fit the case I mentioned.</p><p>We can get benefit from this type when we are changing a record in the whole system and we don’t need the old one such as a bank product (like credit card) and we need to change its name on the whole level of the system.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*Jksrln4dk4JcR_QgRtG2BA.png" /><figcaption>SCD Type 1</figcaption></figure><p><strong>Type 2: Adding rows</strong></p><p>Here we can track changes historically by adding or using a dimension that is unique.</p><p>You need to separate every change and be able to track these changes in this order.</p><p>In our example, I would add the account type (assuming there is no more than 1 current account per customer) and the version column to track the history of changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*4HzGYc8R76LVys8okgFKDQ.png" /><figcaption>SCD Type 2 with version number</figcaption></figure><p>Another popular way and more common is using from_date and to_date dimensions instead of version.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/721/1*z_koINrsZqoE29wEWJrrUA.png" /><figcaption>SCD Type 2 with dates</figcaption></figure><p>Note that the to_date in the new record is supposed to be infinity till the dimension needs another change.</p><p>Another important note is that in other changes like maybe martial status or address of a customer will need to add surrogate key (system key) as we don’t have a unique natural key here( neither the customer_id nor the account_number is changed). So, the surrogate key can be useful to distinguish between the 2 records or whatever how many it was changed.</p><p>The versions method of type 2 mentioned above can have instead a composite key between any natural key combined with the version column.</p><p>The dates method of this type also can have a combination between the natural keys and the dates (but it’s not a good practice to include the dates into a composite key), so maybe a new surrogate key be useful here.</p><p><strong>Type 3: Adding Columns</strong></p><p>This type can only track the last few changes based on how many columns will you add.</p><p>Here we add a column to store the old value and another date column to know when the change has been executed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/712/1*LxBoNY5krMEqD0Vi1SwAgQ.png" /><figcaption>SCD Type 3</figcaption></figure><p><strong>Type 4: Adding historical table</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/316/1*WHCsatwSpDY8fXm9kWEkpQ.png" /><figcaption>Main Customers table that has the latest data</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*hZZ1WAP8V9qNxG3m3HIpUQ.png" /><figcaption>The customer historical table that has the changes</figcaption></figure><p>This type creates another historical table that has all the changes.But, the main table has the current and the latest update of the data.</p><p>Here we created two tables that have the same structure but the historical one has the update date and it gonna expand more and more with every change that happens.</p><p>The SCD is essential for any ETL/DW developer or architect.You just need to distinguish which type to use in your problem that will help you a well designed historical data warehouse.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7ca21e0e8c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introduction to RPA (Robotic Process Automation)]]></title>
            <link>https://medium.com/@Muhammad7Salah/introduction-to-rpa-robotic-process-automation-ab5022e0f06c?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab5022e0f06c</guid>
            <category><![CDATA[robotics-automation]]></category>
            <category><![CDATA[rpa-tools]]></category>
            <category><![CDATA[intelligence-automation]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Sat, 26 Jan 2019 16:26:33 GMT</pubDate>
            <atom:updated>2019-02-09T09:29:32.146Z</atom:updated>
            <content:encoded><![CDATA[<h3>What is RPA (Robotic Process Automation)?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/0*InggGCZHrlH0JCjO.jpg" /></figure><blockquote>Everyday we hear about the revolution of AI and computers that are gonna replace our jobs and how many jobs will disappear in our days.</blockquote><p>Artificial Intelligence, Machine Learning, Deep Learning are going to replace our jobs in the next days but it does not yet.<br>It started since the 18th century when the industrial revolution began that Europe and US began to transit from hand production methods to machines.<br>That meant that machines replaced many jobs at this time and everyday till now the world is losing a job that can be replaced by a machine.</p><p>Nowadays there are technologies that have the ability to replace the simple jobs that do not need creativity or thinking.</p><p>RPA is one of them and I am gonna talk about it in simple words.</p><blockquote><strong>What is RPA in the first place?</strong></blockquote><p>RPA is a software program (software robot) that can do basic tasks that is done for a business repeatedly and takes time and cost by human.</p><p>RPA automates portions of the work such as Data Entry, Data Manipulation, Data Extraction, Email Automation, filling forms and much more of repeated work.</p><blockquote><strong>What can RPA add to business ?</strong></blockquote><p><strong>Reduce Cost:</strong></p><p>RPA can reduce cost by replacing human resources to do their repeated work.</p><p><strong>Increase Productivity:</strong></p><p>RPA can produce in small amount of time what can one employee do in months or maybe more.</p><p><strong>No Human Errors:</strong></p><p>If your RPA system is well defined, then you can get higher accuracy than what you get from a human work as computers do not get bored from the repeated work.</p><p><strong>Monitoring and Visualizing your virtual workforce:</strong></p><p>You can monitor the process of RPA and visualize the productivity of your system.</p><blockquote><strong>When can RPA be appropriate for your work ?</strong></blockquote><ol><li><strong>High volume of employees</strong><br>You may have too much repeated work that needs high volume of employees to do the same work.</li><li><strong>Fixed input/output<br></strong>Inputs and outputs have to be fixed for each time the work is done.</li><li><strong>No human intervention required<br></strong>As long as the work does not need a human intervention, RPA can fit here.</li><li><strong>Pre-defined decision tree (steps)<br></strong>Having a pre-defined steps of how the work is done by the employee and how to decide what to do in all cases.</li></ol><blockquote><strong>The question now is how to apply RPA in to automate your work ?</strong></blockquote><p>There are many RPA tools that can integrate with other software applications such as office, mail …etc.</p><ol><li>Blue Prism</li><li>UI Path</li><li>Automation anywhere</li><li>PEGA</li></ol><p>In the end be careful and think if your job is gonna be replaced in the next year, decade or more because this is just a beginning of an explosion.</p><figure><img alt="Image result for robots vs human" src="https://cdn-images-1.medium.com/proxy/1*4tALuYm6-y0fCRdlzPHyXg.jpeg" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab5022e0f06c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Joining and Merging sources in Informatica PowerCenter]]></title>
            <link>https://medium.com/@Muhammad7Salah/joining-and-merging-sources-in-informatica-powercenter-b8c135cd7eb5?source=rss-3b80c732b669------2</link>
            <guid isPermaLink="false">https://medium.com/p/b8c135cd7eb5</guid>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[powercenter]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[informatica]]></category>
            <dc:creator><![CDATA[Muhammad Salah ElOkda]]></dc:creator>
            <pubDate>Wed, 16 May 2018 14:32:30 GMT</pubDate>
            <atom:updated>2018-05-18T08:48:48.817Z</atom:updated>
            <content:encoded><![CDATA[<p>Hello ETL Developers,<br>Today I am going to explain different ways to join or merge data sources using Informatica PowerCenter.</p><blockquote><strong>First way is an obvious way that is using Joiner Transformation:</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/853/1*e8UJGmnVXkFoEciywIAZ_w.png" /><figcaption>Employees table and Departments table are joined using Join transformation</figcaption></figure><p>Joiner Transformation combines 2 sources have a common column or a Primary key-Foreign key relationship.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/908/1*igMAgwrVPnN_wxq8NsyZ4g.png" /><figcaption>Condition tab in Joiner Transformation</figcaption></figure><p>Here we have assigned the 2 common columns that the joiner is going to join based on the same data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*_BpQCkQ8Iz1NftPKyUcT3A.png" /><figcaption>Properties Tab in Joiner Transformation</figcaption></figure><p>There are 4 types of Joining techniques:</p><ol><li><strong>Normal Join -&gt; Inner Join in SQL</strong></li><li><strong>Master Outer Join -&gt; Left Outer Join in SQL</strong></li><li><strong>Detail Outer Join -&gt; Right Outer Join in SQL</strong></li><li><strong>Full Outer Join</strong></li></ol><p>Note that in Informatica we use the terms Master and Detail instead of Left and Right Tables and you can assign the ports as a Master or Detail so that you can deal with the Informatica terms.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pxaHAYAa7jyeqdFm3WTQDg.png" /><figcaption>Ports tab in Joiner Transformation</figcaption></figure><p>As seen above, the last 4 ports are checked as M(aster) and the rest that are not checked as a Master are Detailed.</p><p>Disadvantages in this method is that you can’t join more than 2 sources only but you can join any type of sources that is supported in Informatica.</p><blockquote><strong>Second way is to use Source Qualifier Transformation:</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*zgvm-JwMixUtTyZdGhv2Vg.png" /><figcaption>Employees table and Departments table are joined using Source Qualifier Transformation</figcaption></figure><p>Source Qualifier transformation can also be used to join sources.</p><p>The main purpose of this transformation is to convert the native datatypes of sources into Informatica datatypes that the integration service can deal with it till it reaches the targets.</p><p>Source Qualifier transformation is one of the important transformations that can perform many tasks for your mapping but here we are going to focus on 2 features that it supports.</p><h3><strong>User Defined Join</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FcAfRNVpo37dMdZ8Fh9iLA.png" /><figcaption>User Defined Join attribute in Properties of Source Qualifier Transformation</figcaption></figure><p>We enter the condition of joining in the User Defined Join Attribute as shown in the above image where the department id in the 2 sources is the common column.</p><h3><strong>SQL Query</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aNjkNPxssl1UPX1t-NrPeA.png" /><figcaption>SQL Query attribute in Properties of Source Qualifier Transformation</figcaption></figure><p>SQL query as shown above enables you to write SQL query to get what you want from the source.<br>So, the Source Qualifier will get the joined data from the two tables.<br>But notice that you have to select the same number of ports as the number of outputs in the Source Qualifier.</p><p>The above query performs joining the two sources based on the condition in the WHERE clause.</p><p>The problem here is that the sources must be from the same Database but unlike Joiner Transformation you can join more than 2 sources.</p><blockquote>Third way to join tables is using Union transformation:</blockquote><p>This methods is very different from the previous two methods.</p><p>Union transformation is used to merge tables with the same structure.<br>This means that the merging is performed as inserting new rows and discarding duplicates.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kEa7vJL_CHQm69ch1aWwUg.png" /><figcaption>Groups tab in Union Transformation</figcaption></figure><p>First create the number of groups as the number of source you are going to combine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/857/1*jSwi-TILUfAdgpuMJBmuAA.png" /><figcaption>Oracle Table and Structured Flat File are merging together using Union Transformation</figcaption></figure><p>As seen above the two sources have the same structure and they can be from different Databases or any other type of sources.</p><blockquote>Thanks for completing the article and I hope it helps you in merging and joining sources.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b8c135cd7eb5" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>