Knowledge Hub

We all have heard about MPP (Massive Parallel Processing). In the current era, consumption and serving layer without MPP capability cannot survive.

MPP is mostly used to read and write (mostly insert) in parallel where more than 1 processor is involved.

In MPP, multiple processers are used to process queries in parallel which might be running in single node or across multiple nodes, in same location or multiple locations.

In a non-MPP mode when a query is executed its different pieces of code are executed one by one in a sequence. But in MPP mode when a query is executed its different pieces of code are executed in parallel on all the processors in one go. This increases the execution of queries 100 times faster than compared to one-by-one execution.

In the current era where data coming from social-media, audios, videos etc., are coming in such a velocity/ pace/ speed that without MPP, it will not be possible to write, process or read data in timely manner.

Canned Data is something which is readily available at one click. A dataset prepared based on business requirements i.e., if required in pre-aggregated format, in one accessible format.

The aim of canned data is to have a dataset that needs no more cleansing, no more wrangling, no more reconciliation etc. Aim is, User to just click it and data should be served.

Imagine Canned food, which are prepared and seals to be used anytime, anywhere, by anyone. Most of the canned food is ready to eat. Same is with Canned data, ready to use. OLAP Cubes are a perfect example of canned data where pre-calculated and pre-aggregated data is stored in a multi-dimensional table, ready to be consumed anytime.

‘Canned data can be refreshed with latest data using same existing data element.’

Canned Reports

Just like Canned Data, Canned Reports has the same approach where static designed reports are built for non-tech savvy stakeholders, who just want to click once and see the expected required output.

Adhoc Reports

Whereas Adhoc reports are for tech savvy users who know how to play with the BI tools and who also have business knowledge of data element shared in the tool. Using Adhoc reporting, users can build their own reports, dashboards by drag and drop exposed tables and columns. Users can slice and dice the reports. Users can drill-in and drill-out.

Tools like

• Oracle Forms, SSRS etc. are considered as Canned Reports generators.
• Tableau, Power BI, Qlik etc. are considered as Adhoc Reports generators.
• OBIEE, Cognos etc. are considered as Semi Canned and Semi Adhoc Reports generators.

Before we understand MDX, we must first reassemble our memories with respect to OLTP and OLAP. We know what these are, both are explained as separate topics in detail.

SQL (Structured Query Language) is for RELATIONAL DATABASES which is primarily used by OLTP systems, but it also used by almost all the datastores i.e., Data Warehouse, Data Lake, Data Vault, Business Vault etc.

MDX (Multidimensional eXpression) was invented by SQL Server engineers in 1997. MDX is the query language for OLAP DATABASES to extract multi-dimensional data from OLAP Cubes. Its syntax is just like XLS formula.

MDX is not a new language, it’s been there for decades but due to the ease of use of SQL language, it’s hardly used now a day. But experts are predicting the revival of MDX soon.

MDX Data Types

• Scalar can be number or string.
• Hierarchy is like dimension e.g., in time dimension, the hierarchy is year, month, week, day etc.
• Level is the level of Hierarchy of dimension like is year, month, or week etc.
• Member is the member of hierarchy like exact date or week or month etc.
• Tuples contain one or more members of a hierarchy.
• Set contains one more tuple.
• Other Data Types are the name of the fields/ columns in a multidimensional cube. It’s just like fields/ columns in a Data Warehouse or Data Mart.

Data Drift concept is used in Data Science practice, and it is one of the most expected behaviors in every data modelling. One must have automated ways to keep tracking and identifying Data Drifts as after Data Drift, there are high chances the model will not produce or predict the expected accuracy which was planned. Wrong prediction means wrong decisions.

We all know what a Drift is. Drift is about shifting or moving from one point to another but not in a standard approach. This term is mostly referred in Data Science practice, to evaluate trained vs actual., performances of Machine Learning (ML) Models.

Data Drift is also known as covariate shift, virtual drift, or virtual concept drift. When we create and run a ML model, just like any traditional query, it inputs a dataset and outputs a dataset. In ` ML modelling, we select certain data elements to build its logic. Data Drift is about change of data within same select data element.

Concept Drift is used in Data Science practice, and it is mostly unexpected behavior in data. One must have automated ways to keep tracking and identifying Concept Drifts as after Concept Drift, the model will totally not produce or predict the output which was planned. Wrong prediction means wrong decisions.

We all know what a Drift is. Drift is about shifting or moving from one point to another but not in a standard approach. This term is referred in Data Science practice to evaluate tested vs actual performance of Machine Learning (ML) Models.

Concept Drift is also known as class drift, real concept drift, or posterior probability shift. As we discussed in Data Drift, the data elements do not change rather data changes. But in Concept Drift, data elements themselves change.

For example, referring to the same example from Data Drift, model is trained on products but at the same time buying behavior of those products is dependent on external factors like weather. Let’s say, those products are Cold Drinks and it’s obvious in the region where it’s been sold, the weather is expected to be hot and based on the ML prediction, a production of 1M Cold Drinks are planned on weekly basis. Now, what if, due to any reason the weather sees a sudden change and for a week it rains, and the temperature drops unexpectedly, THIS IS CALLED CONCEPT DRIFT, then the whole model’s behavior will be impacted. Due to pleasant whether people will only consume 25% of 1M Cold Drinks which means the ML model must quickly change its training data and predict the production behavior.

Scope Creep is a known term among Project Management but is worth discussing it among data folk as scope creep can also impact data architecture and data modelling.

‘Scope Creep are those new requirements which are never formally added in project scope or if added, are without estimating impact of project deadline.’

Scope Creep means adding new requirements without analyzing impact on time and cost. I personally follow the thumb rule of TCS.

‘TCS: Time, Cost and Scope.’

Whenever 1 of the above mentioned 3 pillars of project management is impacted, the remaining 2 WILL also be impacted. Scope Creep is referred as e.g., a new requirement is added to the project scope, but the Project Manager didn’t estimate how much more effort is required and will there be any additional cost involving it. At the end of the day, it may impact Project Go-Live date which no one wants.

Reasons of Scope Creeps

• Deficiency of a Vibrant and Thorough Scope
• Absence of a Stakeholders Concurrence 
• Weak Project Sponsor Decision Management 
• Contradictory Business Users Views
• Less participation of all stakeholders during Project Lifecycle
• Bad Management of Risk and Issues Proactively
• Crossing Prioritizing Requirements
• Lack of Change Management Process
• Bad Efforts Estimation
• Bad Evaluation of new Requirements
• Missing Stakeholders
• Non-identification of Out-of-Scope earlier in Project Lifecycle
• Vague Acceptance Criteria(s)
• Poor Assumptions Management
• Poor Constraints Management
• No Clear Project Deliverables

Data Discrepancies are confused with Data Errors which is not true. Data Errors are searched, identified, and fixed based on Data Discrepancies.

‘Dictionary: An illogical or surprising lack of compatibility or similarity between two or more facts are called discrepancies.’

Data discrepancies can be like mismatch of data from source verses data processed.

Some time, when data analysts run manual calculation, those are not matching with the numbers coming from automated reports which means there are data reconciliation issues. This is Data Discrepancy.

For example, two results do not match based on business logic. Refer to the image, total of 9,093 + 6,104 is not equal to All Users i.e., 14,018. This means, a total of 14,018 came from various sources and 9,093 & 6,104 came from different sources. This is Data Discrepancy.

As mentioned above, once data discrepancies are found, the next step is to start identifying the data errors, code errors, business rule errors etc.

Data Skew Issue is one of the main hidden culprits for query performance 😊. It is normally referred to as Skewed data distribution which is used in Data Science domain.

Have you ever noticed, your query is correct, your joins are correct, your all indexes are enabled, you have high data quality, you have data standardization in place but still your query performance is not as expected? You noticed when you loaded new datasets, queries were flying but as time passed, the performance has gone down. You have added more CPU, more RAM, more nodes but NO performance is not like newly added data. It’s because of the Data Skew Issue.

Data Skew Issue is about when data is not distribution evenly. Uneven distribution of data at cluster level causes this issue. It heavily degrades the performance of the queries especially those with joins.

We all have faced a situation where query run exceptionally good till 99% and then hangs for 3-4 hours right lolz yeah, I can understand your pain 😊. Let me tell you the reason, it’s because of Data Skew Issue.

Coupling is a parameter dependency approach used cross software or cross systems.

This approach is used when there are more than one software or system is in the picture to deliver the result.

Data Coupling is about when data elements are used, to kick-off the next step in a different system. For example, in data pipeline, we are pulling data from a hospital management system e.g., when a baby is born let’s have a data dependency like, the moment the baby is registered in system, pull its data. So, it’s a cross-system data dependency or we called Data Coupling.

The disadvantage of Data Coupling approach is, if something goes wrong in the first system, dependent systems cannot move.

Let me share another old example. Old data pipeline folks will remember this one i.e., when we used to load files from another system, we had to wait till the time whole file movement is completed. The challenge was how to make sure when to start pulling like only once file is completely copied.