87.Classifying data for successful modeling

Qualitative Data

Qualitative data are also called categorical data as they represent distinct categories rather than numbers.In case of dimensional modeling, they are often termed as “dimension“.Mathematical operations such as addition or subtraction do not make any sense on that data.

Example of qualitative data are, eye color, zip code, phone number etc.

Qualitative data can be further classified into below classes:

NOMINAL :

Nominal data represents data where order of the data does not represent any meaningful information.

Consider your passport number.There is no information as such if your passport number is greater or lesser than some one else’s passport number. Consider Eye color of people, does not matter in which order we represent the eye colors, order does not matter.

ID, ZIP code, Phone number, eye color etc. are example of nominal class of qualitative data.

ORDINAL :

Order of the data is important for ordinal data.

Consider height of people – tall, medium, short.Although they are qualitative but the order of the attributes does matter, in the sense that they represent some comparative information.Similarly, letter grades, scale of 1-10 etc. are examples of Ordinal data.

In the field of dimensional modeling, this kind of data are sometimes referred as non-additive facts.

Quantitative data

Quantitative data are also called numeric data as they represent numbers.In case of dimensional data modeling approach, these data are termed as “Measure“.

Example of quantitative data is, height of a person, amount of goods sold, revenue etc.

Quantitative attributes can be further classified as below.

INTERVAL :

Interval classification is used where there is no true zero point in the data and division operation does not make sense.

Bank balance, temperature in Celsius scale, GRE score etc. are the examples of interval class data.Dividing one GRE score with another GRE score will not make any sense.In dimensional modeling this is synonymous to semi-additive facts.

RATIO :

Ratio class is applied on the data that has a true “zero” and where division does make sense.Consider revenue, length of time etc. These measures are generally additive.

Below table illustrates different actions that are possible to implement on various data types

Data Reconciliation

88.Data Reconciliation

• Many of the data warehouses are built on n-tier architecture with multiple data extraction and data insertion jobs between two consecutive tiers.
• As it happens, the nature of the data changes as it passes from one tier to the next tier.
• Data reconciliation is the method of reconciling or tie-up the data between any two consecutive tiers (layers).

.Why Reconciliation is required?

• In the process of extracting data from one source and then transforming the data and loading it to the next layer, the whole nature of the data can change considerably.
• It might also happen that some information is lost while transforming the data.
• A reconciliation process helps to identify such loss of information.

One of the major reasons of information loss is loading failures or errors during loading. Such errors can occur due to several reasons e.g.

• Inconsistent or non coherent data from source
• Non-integrating data among different sources
• Unclean / non-profiled data
• Un-handled exceptions
• Constraint violations
• Logical issues / Inherent flaws in program
• Technical failures like loss of connectivity, loss over network, space issue etc.

Failure due to any such issue can result into potential information loss leading to unreliable data quality for business process decision making.

Further more, if such issues are not rectified at the earliest, this becomes even more costly to “patch” later. Therefore this is highly suggested that a proper data reconciliation process must be in place in any data Extraction-Transformation-Load (ETL) process.

.Methods of Data Reconciliation

Master Data Reconciliation

Master data reconciliation is the method of reconciling only the master data between source and target.

• Master data are generally unchanging or slowly changing in nature and no aggregation operation is done on the dataset.
• That is – the granularity of the data remains same in both source and target.
• That is why master data reconciliation is often relatively easy and quicker to implement.

In one business process, “customer”, “products”, “employee” etc. are some good example of master data.

Ensuring the total number of customer in the source systems match exactly with the total number of customers in the target system is an example of customer master data reconciliation.

Some of the common examples of master data reconciliation can be the following measures,

1. Total count of rows, example
   • Total Customer in source and target
   • Total number of Products in source and target etc.
2. Total count of rows based on a condition, example
   • Total number of active customers
   • Total number of inactive customers etc.

Transactional Data Reconciliation

• Sales quantity, revenue, tax amount, service usage etc. are examples of transactional data.
• Transactional data make the very base of BI reports so any mismatch in transactional data can cause direct impact on the reliability of the report and the whole BI system in general.
• That is why reconciliation mechanism must be in-place in order to detect such a discrepancy before hand (meaning, before the data reach to the final business users)
• Transactional data reconciliation is always done in terms of total sum.
• This prevents any mismatch otherwise caused due to varying granularity of qualifying dimensions.
• Also this total sum can be done on either full data or only on incremental data set.

Some examples measures used for transactional data reconciliation can be

1. Sum of total revenue calculated from source and target
2. Sum of total product sold calculated from source and target etc.

Automated Data Reconciliation

• For large warehouse systems, it is often convenient to automate the data reconciliation process by making this an integral part of data loading.
• This can be done by maintaining separate loading metadata tables and populating those tables with reconciliation queries.
• The existing reporting architecture of the warehouse can be then used to generate and publish reconciliation reports at the end of the loading.
• Such automated reconciliation will keep all the stake holders informed about the trustworthiness of the reports.

.Scope of Data Reconciliation

• Data reconciliation is often confused with the process of data quality testing.
• Even worse, sometimes data reconciliation process is used to investigate and pin point the data issues.
• While data reconciliation may be a part of data quality assurance, these two things are not necessarily same.
• Scope of data reconciliation should be limited to identify, if at all, there is any issue in the data or not.
• The scope should not be extended to automate the process of data investigation and pin pointing the issues.
• A successful reconciliation process should only indicate whether or not the data is correct. It will not indicate why the data is not correct. Reconciliation process answers “what” part of the question, not “why” part of the question.

 89.Volumetric mis-judgement  ?

A very minutely done volumetric estimate in the starting phase of the project would go weary later. This happens due to several reasons e.g. slight change in the standard business metrics may create huge impact on the volumetric estimates.

For example, suppose a company has 1 million customers who are expected to grow at a rate of 7% per annum. While calculating the volume and size of your data warehouse you have used this measure in several places. Now if the customer base actually increase by 10% instead of 7%, that would mean 30000 more customers. In a fact table of granularity customer, product, day – this would mean 30000 X 10 X 365 more records ( assuming on average one customer use 10 products ). If one record takes 1kb, then the fact table would now require – ( 30000 X 365 X 10 X 1kb ) / ( 1024 X 1024 ) = 100+ GB more disk space from only one table.

Data Retention

90.What is data retention and Purging?

There are certain requirement to purge / archive / delete the data in data warehouse after a certain period of time, often termed as retention period of the data warehouse. Once a the retention period is reached, data from the data warehouse are purged or deleted or archived into separate place usually comprising of low cost storage medium (e.g. tape drive).

.Why data purging is required?

In a idealistic scenario, we assume data warehouse to store data for good. However there are some reasons why this might not be a good idea in a real scenario:

• There are cost overhead associated with the amount of data that we store. This includes the cost of storage medium, infrastructure and human resources necessary to manage the data
• There is direct impact of data volume to the performance of a data warehouse. More data means more time consuming sorting and searching operations
• End users of the data warehouse in the business side may not be interested in the very old fact and figures.
• Data might lose its importance and relevance with the changing business landscape. Retaining such impertinent data may not be required

.Variable purging requirement

• The concept of data purging may not be applicable for all types of data in the warehouse.
• Typically one may only want to purge the transactional data and may not want to purge any master data.
• Similarly the retention period of the data warehouse may not be same for all types of data.
• One may set a variable retention period such that all detail level transactional data will be purged after 2 years and all aggregated transaction data will be purged after 7 years.

.Rolling window for data purging

• Process of purging is typically implemented as a rolling window wherein whatever data falls under the purging window are only purged.
• So suppose if the retention period is set as 2 years and purging process is executed bi-monthly then after every 15 days, whenever the purging process runs it deletes all the records older than 2 years as of that day.

.Purging Master / Reference data

Purging of master data is not recommended unless:

• There is specific reason why the master data will never be required in the future
• History is maintained for the change in the master data (e.g. in the form of Slowly Changing Dimension etc.)

If history of the changes in the master data are maintained in the data warehouse, it might be necessary at some point to purge the records corresponding to old histories of the master data. When this is done, one must also keep in mind to clear all the corresponding transactional records pertaining to the master data. To understand the situation, consider this: I have a customer C1 for whom I have two records in my master data table with surrogate keys as K1 and K2. K1 corresponds to the customer C1’s attributes as of year 1996 and K2 corresponds to the customer C1’s attribute as of year 2000.

92.What is Data Integration (DI)?

Data Integration is the process of combining heterogeneous data sources in to a single queriable schema so as to get an unified view of these data.

.Why Data Integration is required?

• Often large companies and enterprises maintain separate departmental databases to store the data pertaining to the specific department.
• Although such separations of the data provide them better manageability and security, performing any cross departmental analysis on these datasets becomes impossible.

For example, if marketing department and sales department maintain two secluded databases, then it might not be possible to analyze the effect of a certain advertising campaign by the marketing department on sales of a product. Similarly, if HR department and production department maintain their individual databases, it might not be possible to analyze the correlation between yearly incentives and employee’s productivity.

Data integration provides a mechanism to integrate these data from different departments into a single queriable schema.

Below is a list of examples where data integration is required. The list, however, is not comprehensive

• Cross functional analysis – as discussed in the above example
• Finding correlation – Statistical intelligence/scientific application
• Sharing information – legal or regulatory requirements e.g. sharing customers’ credit information among banks
• Maintaining single point of truth – Higher management topping over several departments may need to see a single picture of the business
• Merger of Business – after merger two companies want to aggregate their individual data assets

.How data integration can be done? – different approaches to data integration

There are mainly 2 major approaches for data integration – commonly known as

• “tight coupling approach”
• “loose coupling approach”.

Tight Coupling: Data Warehousing

• In case of tight coupling approach – which is often implemented through data warehousing, data is pulled over from disparate sources into a single physical location through the process of ETL – Extraction, Transformation and Loading.
• The single physical location provides an uniform interface for querying the data.
• ETL layer helps to map the data from the sources so as to provide a semantically uniform data warehouse.
• This approach is called tight coupling since in this approach the data is tightly coupled with the physical repository at the time of query.

Loose Coupling: Virtual Mediated Schema

• In contrast to tight coupling approach, a virtual mediated schema provides a interface that takes the query input from the user, transform the query in the way source database can understand and then sends the query directly to the source databases to obtain the result.
• In this approach, the data does not really remain in the schema and only remain in the actual source databases.
• However, mediated schema contains several “adapters” or “wrappers” that can connect back to the source systems in order to bring the data to the front end.
• This approach is often implemented through middleware architecture (EAI).

To understand “Virtual Mediated Schema” approach, consider this example. Let’s say one database stores weather data of a country for past 50 years. Another database contains crop production data of the country for each year. A user might want to query – “In which year crop production is lowest inspite of more than average rainfall?”. The question can be subdivided into 3 different questions as follows:

1. What is the average rainfall across all the years?
2. Which are the years where recorded rainfall was higher than the average?
3. Out of all the years obtained from the above query, which year has least crop production?

The first 2 queries can be straightway sent to the weather database and the last query can be sent to the crop production database to get the answers.

In a mediated schema all the data of weather and crop productions will not be available in the schema itself, but the schema will have necessary “adapters” to send the queries back to appropriate databases and combine the result sets.

93.Why do we need Staging Area during ETL Load

We have a simple data warehouse that takes data from a few RDBMS source systems and load the data in dimension and fact tables of the warehouse.

 why you can’t avoid a staging area:

• Source systems are only available for extraction during a specific time slot which is generally lesser than your overall data loading time.
• It’s a good idea to extract and keep things at your end before you lose the connection to the source systems.
• You want to extract data based on some conditions which require you to join two or more different systems together. E.g. you want to only extract those customers who also exist in some other system. You will not be able to perform a SQL query joining two tables from two physically different databases.
• Various source systems have different allotted timing for data extraction.
• Data warehouse’s data loading frequency does not match with the refresh frequencies of the source systems.
• Extracted data from the same set of source systems are going to be used in multiple places (data warehouse loading, ODS loading, third-party applications etc.)
• ETL process involves complex data transformations that require extra space to temporarily stage the data
• There is specific data reconciliation / debugging requirement which warrants the use of staging area for pre, during or post load data validations

1.  impact of having a stage area? Yes there are a few.
   • Staging area increases latency – that is the time required for a change in the source system to take effect in the data warehouse. In lot of real time / near real time applications, staging area is rather avoided.
   • Data in the staging area occupies extra space.

DATA Load

94.Methods of Incremental Loading in Data Warehouse

• Incremental loading a.k.a Delta loading is an widely used method to load data in data warehouses from the respective source systems.
• This technique is employed to perform faster load in less time utilizing less system resources.

What is Incremental Loading and why is it required

•  we extract data from one or more source systems and keep storing them in the data warehouse for future analysis.
• The source systems are generally OLTP systems which store everyday transactional data.

Now when it comes to loading these transactional data to data warehouse, we have 2 ways to accomplish this, Full Load or Incremental Load.

INCREMENTAL LOAD METHOD FOR LOADING DATA WAREHOUSE

We can make use of “entry date” field in the customer table and “sales date” field in the sales table to keep track of this. After each loading we will “store” the date until which the loading has been performed in some data warehouse table and next day we only extract those records that has a date greater than our stored date. Let’s create a new table to store this date. We will call this table as “Batch”

Batch

Batch_ID  Loaded_Until  Status
1         22-Mar-2012   Success
2         23-Mar-2012   Success

Once we have done this, all we have to do to perform incremental or delta loading is to rite our data extraction SQL queries in this format

Next day, the query “SELECT max(b.loaded_until)” will return me 22-Mar-2012. So in effect, above queries will reduce to this:

Why status field is created in batch table?

This is because it might so happen that 23rd load has failed. So when we start loading again on 24th, we must take into consideration both 23rd data and 24th data.

Batch_ID  Loaded_Until  Status
1         22-Mar-2012   Success
2         23-Mar-2012   Fail
3         24-Mar-2012   Success

In the above case, 23rd batch load was a failure. That is why next day we have selected all the data after 22-Mar (including 23rd and 24th Mar).