Knowledge Hub

Many folks are getting confused between Concurrent Processing or MPP (Massive Parallel Processing).

Please note there is a major difference between both where one is dependent on the developers and the other on hardware.

Concurrent Processing means when technology can run concurrent queries which most of the time dependent on cores handling the queries.

In simple words, when more than one job runs in parallel and execute some code sets at the same time.

On the other hand, MPP utilizes hardware level concurrency where one job/ code set is divided in multiple pieces and runs in parallel mode.

In simple words, a single job utilizes multiple nodes to execute the same code set making it much faster as compared to job utilize single node at a time.

Having Concurrent and MPP capability gives a huge benefit to the solution when it comes to meeting SLA(s). In Big Data era, having these capabilities is a MUST-TO-HAVE.

Information Modelling is not a new term, but nerd data folks hardly get in touch with it as it’s one level above of creating any special solution.

Let’s decode it…..

We also know when we start a project or solution, we begin with Conceptual Model then Logical Model then Physical Model etc. Unlike these models where we focus especially on either entities or relationships or process or constraints, Information Modelling represents entities, relationships, and processes within a system or organization.

Information modelling is a crucial aspect of designing data solutions that goes beyond merely structuring databases. It involves creating a comprehensive representation of how information is organized, flows, and is utilized within an organization or system. Information models provide a framework for understanding the relationships and dependencies among different elements of information, facilitating effective communication and management.

These are one of the most confusing topics. We all mix-up data quality fixes and standardization of data for better processing with transforming data for better processing and serving.

Let’s decode it…..

We all know, in Data Warehouse or when we pull data from an OLTP system, we first store it in a LANDING ZONE somewhere which normally is the true copy of source systems. Then we move it to STAGING ZONE before we transform it into a Unified Data Warehouse (Bill Inmon way) or in a Dimensional Model (Ralph Kimble way).

Tables in LANDING and STANGING ZONES, both keep the true DATA MODEL from source systems but not the CONDITION of DATA.

WHAT DOES ABOVE STATEMENT MEANS, not the CONDITION of DATA?

Data Standardization

Does standardizing the condition of data not equal to transformation? NOPS, ITS NOT. Data Standardization can have unlimited scenarios, few are shared below e.g.,

• Standardization of Dates:
  • Converting different dates formats from different source systems e.g., DD/MM/YY, MM/DD/YY, YY/MM/DD, YY/DD/MM etc., into one format e.g., DD/MM/YYYY.
  • Convert empty date rows into e.g., 01/01/9999.
• Convert special characters to NA.
• Convert empty fields to NA.
• Profile the fields, if data type is string or varchar but data is only number than convert it into digit or number as running queries on digit or numbers are way faster.
• If the field contains codes only with only character, then make it CHAR and if it only contains numbers then make it DIGIT.
• If the full name of a person is in one field, convert it in First, Middle and Last Name.
• Fix the spelling mistakes.
• Fix the up and lower cases e.g., if a country is Australia and in data its coming as Aus, Aust, australia, Aussie, aus etc., than convert it in one format e.g., Australia.

Semantics are different for everyone. In an organization, each department has its own semantics, to view data. Same data can have different meanings for one department as compared to others.

Salary for HR department is to know who is getting what and when they hire new candidate, they can refer to what others are getting based on role, JD, skill set etc.. But Salary for Finance department can be, to calculate annual expense for the company.

Semantic Layer in Decision Support Systems are based on business requirements, in their language, using their own key words, their codes etc.

Interoperability on the other hand is about exchanging something across systems so others don’t need to regenerate it again and again.

Semantic Interoperability is about exchanging semantic information across systems or across departments or across organizations. But this comes with a challenge whether the receiving party can understand the keywords, symbols, terminologies etc., from the sending party.

Approaches play a vital role in the failure of many projects in the IT World. We all have heard about POC, Protype, Pilot, MVP, SIT, UAT, and Production implementations, but I am sure, most of us might have not given a thorough thought of aftereffects, if one is interchanged with other.

Let’s try to understand everything in the upcoming topics but to give a broader perspective all above approaches have their own implementation strategies. Most of the projects fail when e.g., started with a POC and once the POC is successful, move the same in Production. One must not do that. This normally happens when business push implementers to achieve tight timelines.

Please note, small level of implementation which in our example, POC might be successful with limited infrastructure or software, but 99% chances are when it goes in Production, it’s bound to fail. Same is true for Prototype, Pilot and MVP environments. One must plan infrastructure and software based on, if plans to be in Production.

In MVP stage where actual Tools and Technologies, and SIT, UAT, and PRODUCTION environments are implemented on a small scale.

Once MVP is successful, the next stage is to gather complete business requirements, a complete designed is made, teams are built, project plan is made, end-to-end SOW is signed, development is conducted, testing is done i.e., UT, SIT and UAT, and solution goes live.

On contrast with POC, Prototype and Pilot/ MVP, in this stage a complete Infrastructure, Software and Data is prepared and used.

UT stands for Unit testing and is done by developer during development phase for every piece of code developed. The better the UT is, the better the product will be.

SIT stands for Systems Integration Testing. Now a days, SIT is disappearing, SIT is the first step to save cost which is NOT a good idea. I am sure in a decade, newbies in IT World might not even know what actually SIT is. Once complete solution is ready, it’s moved to another environment which do have all the possible components of the actual solution but in term of size, it is small e.g., if based on data there is 10 servers and 100 licenses are required, SIT can be run with 1 server and with 1 license. In SIT, we make sure code can run end-to-end, user access is properly set, security protocols are in-place, jobs dependencies are in-place etc.

Accessibility and Visibility are often used interchangeably, especially in simple terms. However, in the world of data solutions, it's crucial to distinguish between them.

Let’s decide it…..

Accessibility pertains to your ability to connect to and utilize data for decision-making purposes.

On the other hand, Visibility involves having a comprehensive understanding of what's happening behind the scenes with your data.

For instance, if you request your Data Warehousing or Data Lakehouse datastore owners to extract data from source systems and transform it into the desired format for decision-making, two key concepts come into play:

Accessibility: This is achieved when your database administrator or datastore owner grants you privileges within the database, allowing you to read, write, or update your data.

Visibility: This extends beyond mere access. It encompasses a deep understanding of various aspects of your data, such as what was sourced, the business rules used for transformations, the presence of business, technical, or operational metadata, the formula for golden records, data quality assurance methods, data loading approaches (e.g., CDC, batch, real-time), SLAs, and lineage information, among other things. All of these elements collectively provide you with comprehensive Visibility into your data.

ETLT is a way of talking about how we handle information. Think of it like this: first, we take out the data we need. Then, we change it to make sure it's clean and ready to use. After that, we put it where we want it to be. Lastly, we might make some more changes to the data to make it even better for understanding. ETLT is like a helpful addition to the usual process (ETL) that makes our data even more useful and organized.

ETLT stands for "Extract, Transform, Load, and Transform" in the context of data integration and processing. Please don’t worry about where this ETLT has come from. ETLT just includes another stage in ETL world to support semantic layers.

Let’s understand each:

• Extract: Involves extracting data from various sources, which could be databases, applications, logs, or other systems.
• Transform (First T): Refers to the process of cleaning, structuring, and enriching the extracted data so that it is suitable for analysis. Transformation may include data cleansing, normalization, aggregation, and other operations.
• Load: Involves loading the transformed data into a target database or data warehouse where it can be used for reporting, analysis, or other purposes.
• Transform (Second T): This additional transformation step might be included in some interpretations of ETLT. It represents further processing or transformation that occurs after the data has been loaded into the target system. This step acknowledges that data transformation can be an ongoing and iterative process.

In the world of data, synchronization and propagation are like superheroes that make sure our information stays accurate and consistent, even when it's spread out in different places.

Let's understand these ideas:

Synchronization:

• Synchronization is about keeping data the same and up to date in many different spots or systems.
• The goal is to ensure that if we change something in one place, like updating the inventory in an online store, those changes quickly show up everywhere else, such as on the website and in the physical store's system. This way, everyone using the data gets the latest and correct info.
• For example, imagine you run an online store, and you add new items to your inventory on your computer. Synchronization is like magic that makes sure those new items appear on your website and in your store's computer, so customers and the store both know what's available.

Propagation:

• Propagation is like the spreading of news, but for changes in data. It's about making sure updates in one part of a system travel to other connected parts.
• When something changes, like depositing money at an ATM in a bank, propagation helps that change reach all the places that need to know, such as your online bank account and the main bank database. It's like making sure the news of the deposit goes to all the right places.
• For example, if you put money into an ATM, propagation ensures that the information about your deposit reaches all the important places, like your online bank app and the bank's main computer. It's like making sure everyone in the banking system knows about the deposit.

In data modelling, particularly in the context of relational databases, the terms "supertype" and "subtype" are used to describe the relationships between entities.

Let's explore these concepts:

Supertype: A supertype is a generalization that represents a higher-level entity. It encompasses common characteristics shared by multiple lower-level entities.

• It contains attributes common to multiple related entities.
• It represents the generalization of several entities.
• For example, consider a supertype "Vehicle" that includes common attributes like "Manufacturer," "Model," and "Year." Cars, motorcycles, and trucks could be subtypes of this supertype, inheriting these common attributes.
• Subtype: A subtype is a specialization of a supertype, representing a more specific entity with additional attributes or characteristics.
• It inherits attributes from the supertype and may have additional attributes.
• It represents a more specific category within the generalization.
• For example, using the "Vehicle" example, "Car" is a subtype that inherits attributes from the "Vehicle" supertype but may have additional attributes specific to cars, such as "Number of Doors" or "Body Type."