Data Warehouse Developer

SCD (Slowly changing dimensions), are the dimensions in which the data changes slowly, rather than changing regularly on a time basis.

Three types of SCDs are used in data warehousing, which are defined as:

☛ – SCD1: It is a record that is used to replace the original record even there is only one record existing in the database. The current data will be replaced and the new data will take its place.

☛ – SCD2: It is the new record file that is added to the dimension table. This record exists in the database with the current data and previous data that is stored in the history.

☛ – SCD3: This uses the original data that is modified to the new data. This consists of two records: one record that exist in the database and another record that will replace the old database record with the new information.

A fact table is usually designed at a low level of Granularity. This means that we need to find the lowest level of information that can store in a fact table.

e.g.Employee performance is a very high level of granularity. Employee_performance_daily, employee_perfomance_weekly can be considered lower levels of granularity.

The granularity is the lowest level of information stored in the fact table. The depth of data level is known as granularity. In date dimension, the level could be year, month, quarter, period, week, day of granularity.

The process consists of the following two steps:

☛ Determining the dimensions that are to be included

☛ Determining the location to locate the hierarchy of each dimension of information. The above factors of determination will be resent to the requirements.

☛ An operational data store (“ODS”) is a database designed to integrate data from multiple sources for additional operations on the data. Unlike a master data store, the data is not sent back to operational systems. It may be passed for further operations and to the data warehouse for reporting.

☛ In ODS, data can be scrubbed, resolved for redundancy and checked for compliance with the corresponding business rules. This data store can be used for integrating disparate data from multiple sources so that business operations, analysis and reporting can be carried while business operations occur. This is the place where most of the data used in current operation is housed before it’s transferred to the data warehouse for longer term storage or archiving.

☛ An ODS is designed for relatively simple queries on small amounts of data (such as finding the status of a customer order), rather than the complex queries on large amounts of data typical of the data warehouse.

☛ An ODS is similar to your short term memory where it only stores very recent information. On the contrary, the data warehouse is more like long term memory storing relatively permanent information.

☛ Multidimensional OLAP is faster than Relational OLAP.

☛ MOLAP: Multi-dimensional OLAP

☛ Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats (one example is PowerOLAP’s .olp file). MOLAP products can be compatible with Excel, which can make data interactions easy to learn.

☛ ROLAP: Relational OLAP

☛ ROLAP products access a relational database by using SQL (structured query language), which is the standard language that is used to define and manipulate data in an RDBMS. Subsequent processing may occur in the RDBMS or within a mid-tier server, which accepts requests from clients, translates them into SQL statements, and passes them on to the RDBMS.

☛ Agglomerative Hierarchical clustering method allows the clusters to be read from bottom to top so that the program always reads from the sub-component first then moves to the parent whereas Divisive Hierarchical clustering uses top-bottom approach in which the parent is visited first than the child.

☛ Agglomerative hierarchical method consists of objects in which each object creates its own clusters and these clusters are grouped together to create a large cluster. It defines a process of continuous merging until all the single clusters are merged together into a complete big cluster that will consist of all the objects of child clusters. However, in divisive clustering, the parent cluster is divided into smaller cluster and it keeps on dividing until each cluster has a single object to represent.