Data Warehouse Tutorial | Data Warehouse Tutorial for Beginners

<ul>
<li><a style="color: #000000;" href="#data-warehouse">What is Data Warehouse? &nbsp;</a></li>
<li><a style="color: #000000;" href="#architecture">Data Warehouse Architecture</a></li>
<li><a style="color: #000000;" href="#features">Data Warehouse Features</a></li>
<li><a style="color: #000000;" href="#types">Types of Data Warehouse</a></li>
<li><a style="color: #000000;" href="#why-data-warehouse">Why Data Warehouse?</a></li>
<li><a style="color: #000000;" href="#why-data-warehouse-is-important">Why is a Data Warehouse important?</a></li>
<li><a style="color: #000000;" href="#components">Data Warehouse Components</a></li>
<li><a style="color: #000000;" href="#data-marts">Data Marts</a></li>
</ul>

Data Warehouse Tutorial

data-warehouse-tutorials

Explain Datawarehousing?
A data warehouse is a federated repository for all the data that an enterprise's various business systems collect. The repository may be physical or logical.
What is BI?
Business intelligence (BI) is a technology-driven process for analyzing data and presenting actionable information to help executives, managers and other corporate end users make informed business decisions.
Explain dimension table ?
A dimension table is a table in a star schema of a data warehouse. A dimension table stores attributes, or dimensions, that describe the objects in a fact table.
Explain is fact table?
A fact table is the central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often denormalized.
What are fundamental stages of Data Warehousing?
Stages of a data warehouse helps to find and understand how the data in the warehouse changes. At an initial stage of data warehousing data of the transactions is merely copied to another server. Here, even if the copied data is processed for reporting, the source data&rsquo;s performance won&rsquo;t be affected. In the next evolving stage, the data in the warehouse is updated regularly using the source data. In Real time Data warehouse stage data in the warehouse is updated for every transaction performed on the source data (E.g. booking a ticket) When the warehouse is at integrated stage, It not only updates data as and when a transaction is performed but also generates transactions which are passed back to the source online data.
What are fundamental stages of Data Warehousing?
Offline Operational Databases: This is the initial stage of data warehousing. In this stage the development of database of an operational system to an off-line server is done by simply copying the databases. Offline Data warehouse: In this stage the data warehouses are updated on a regular time cycle from operational system and the data is persisted in an reporting-oriented data structure. Real time Data Warehouse: Data warehouses are updated based on transaction or event basis in this stage. An operational system performs a transaction every time. Integrated Data Warehouse: The activity or transactions generation which are passed back into the operational system is done in this stage. These transactions or generated transactions are used in the daily activity of the organization.
What is Virtual Data Warehousing?
The aggregate view of complete data inventory is provided by Virtual Warehousing
What is active data warehousing?
&nbsp;The transactional data captured and reposited in the Active Data Warehouse
What Is Operational Data Store (ODS)?
An operational data store (ODS) is a type of database that's often used as an interim logical area for a data warehouse. While in the ODS, data can be scrubbed, resolved for redundancy and checked for compliance with the corresponding business rules. An ODS can be used for integrating disparate data from multiple sources so that business operations, analysis and reporting can be carried out while business operations are occurring. 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.
What is the difference between view materialized view and view?
A view is created by combining data from different tables. Hence, a view does not have data of itself. On the other hand, Materialized view usually used in data warehousing has data. This data helps in decision making, performing calculations etc. The data stored by calculating it before hand using queries. When a view is created, the data is not stored in the database. The data is created when a query is fired on the view. Whereas, data of a materialized view is stored.
What is the difference between view and materialized view?
View: - Tail raid data representation is provided by a view to access data from its table. - It has logical structure can not occupy space. - Changes get affected in corresponding tables. Materialized view: - Pre calculated data persists in materialized view. - It has physical data space occupation. - Changes will not get affected in corresponding tables.
What is VLDB? Very Large Databases
Welcome to the discussion on VLDB. The purpose of this series, these white papers is to present and discuss VLDB regarding it&rsquo;s implementation, application, design and architecture needs. The focus here will be centered on the techniques necessary to setup, maintain, and grow a database of 1 to 80 Terabytes in nature. The target audience is those who have a background in systems architecture, database administration, or systems administration. This series will not discuss the business benefits nor pro&rsquo;s and con&rsquo;s of VLDB from a business perspective. What the series promotes are the following goals of understanding: Impacts of VLDB on Hardware and Software Sizing and scooping of Hardware Parallel Processing, multi-threaded Architecture in relation to VLDB The other areas of discussion will focus on: networks, partitions, clustering files and indexes, database sharing, CPU speed and quantity, RAM speed and quantity, and an introduction to MPP possibilities (such as NUMA-Q). A new series will introduce new structures capable of assisting in housing VLDB data sets: a structure called a Data Collection, and a new data architecture model called the Data Vault&trade; will be presented as a means for massive data storage in a detailed normalized form. Watch for this series in the coming months. Much of the theories and hypothesis presented here are supported by mathematical functions, however mathematical theory is not the focus of this series; therefore the mathematical functions may be referenced in certain places. However the proof of these theorem&rsquo;s or math functions will not be demonstrated.
REAL-TIME DATA WAREHOUSING
Real-time analytics requires your data warehouse to have timely data available, based on a continuous and efficient data acquisition process. Implementing such a process with homegrown and some traditional ETL software can be complex, lengthy, costly and inefficient.
Attunity can enable real-time data warehousing with Attunity Replicate, providing CDC with optimized integration to all major data warehouse platforms. In addition, Attunity Compose provides end-to-end data warehouse automation that can automatically generate ETL code and help data architects more rapidly configure and manage data warehouses.
What are aggregate tables and aggregate fact tables?
MicroStrategy uses optimized SQL to query the relational database directly to answer users&rsquo; questions. Users can ask any question that is supported by the data in their warehouse and then analyze the results until they find a precise answer. The disadvantage to this relational OLAP (ROLAP) methodology is that accessing huge fact tables can be potentially time-consuming. Multidimensional OLAP (MOLAP) is sometimes considered by some to be the answer to this problem. However, MOLAP is not scalable for large projects because of the difficulty of maintaining every possible combination of aggregates as the number of attributes and the amount of data increases. MicroStrategy&rsquo;s solution is the use of aggregate tables to provide quicker access to frequently-accessed data while still retaining the power to answer any user query. Aggregate tables are advantageous because they: &bull; Reduce input/output, CPU, RAM, and swapping requirements &bull; Eliminate the need to perform dynamic calculations &bull; Decrease the number of physical disk reads and the number of records that must be read to satisfy a query &bull; Minimize the amount of data that must be aggregated and sorted at run time &bull; Move time-intensive calculations with complicated logic or significant computations into a batch routine from dynamic SQL executed at report run time
<hr />

Data Warehouse Interview Questions & Answers

data-warehouse-interview-questions-answers

Data Warehouse Interview Questions

interview-questions-on-data-warehouse

<h2>What is a Data Warehouse?</h2>
According to Inmon, famous author for several data warehouse books, "A data warehouse is a subject oriented, integrated, time variant, non volatile collection of data in support of management's decision making process". &nbsp;
<h3>Example</h3>
In order to store data, over the years, many application designers in each branch have made their individual decisions as to how an application and database should be built. So source systems will be different in naming conventions, variable measurements, encoding structures, and physical attributes of data. Consider a bank that has got several branches in several countries, has millions of customers and the lines of business of the enterprise are savings, and loans. The following example explains how the data is integrated from source systems to target systems. &nbsp; &nbsp;
<h3>Example of Source Data</h3>
&nbsp;
<table style="height: 356px;" width="776">
<tbody>
<tr>
<td width="87">System Name</td>
<td width="128">Attribute Name</td>
<td width="175">Column Name</td>
<td width="103">Datatype</td>
<td width="69">Values</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="87">Source System</td>
<td width="128">Customer Application</td>
<td colspan="2" rowspan="2" width="277">CUSTOMER_APPLICATION_DATE NUMERIC(8,0)</td>
<td rowspan="2" width="69">11012005</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td rowspan="2" width="87">1</td>
<td rowspan="2" width="128">Date</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="175">&nbsp;</td>
<td width="103">&nbsp;</td>
<td width="69">&nbsp;</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="87">Source System</td>
<td width="128">Customer Application</td>
<td rowspan="2" width="175">CUST_APPLICATION_DATE</td>
<td rowspan="2" width="103">DATE</td>
<td rowspan="2" width="69">11012005</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td rowspan="2" width="87">2</td>
<td rowspan="2" width="128">Date</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="175">&nbsp;</td>
<td width="103">&nbsp;</td>
<td width="69">&nbsp;</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="87">Source System</td>
<td width="128">Application Date</td>
<td width="175">APPLICATION_DATE</td>
<td width="103">DATE</td>
<td width="69">01NOV2005</td>
<td width="0">&nbsp;</td>
</tr>
<tr>
<td width="87">3</td>
<td width="128">&nbsp;</td>
<td width="175">&nbsp;</td>
<td width="103">&nbsp;</td>
<td width="69">&nbsp;</td>
<td width="0">&nbsp;</td>
</tr>
</tbody>
</table>
&nbsp; In the aforementioned example, attribute name, column name, datatype and values are entirely different from one source system to another. This inconsistency in data can be avoided by integrating the data into a data warehouse with good standards. &nbsp;
<h3>Example of Target Data(Data Warehouse)</h3>
<table>
<tbody>
<tr>
<td width="92">Target System</td>
<td width="152">Attribute Name</td>
<td width="184">Column Name</td>
<td width="64">Datatype</td>
<td width="59">Values</td>
</tr>
<tr>
<td width="92">Record #1</td>
<td width="152">Customer Application Date</td>
<td colspan="2" width="248">CUSTOMER_APPLICATION_DATE DATE</td>
<td width="59">01112005</td>
</tr>
<tr>
<td width="92">Record #2</td>
<td width="152">Customer Application Date</td>
<td colspan="2" width="248">CUSTOMER_APPLICATION_DATE DATE</td>
<td width="59">01112005</td>
</tr>
<tr>
<td width="92">Record #3</td>
<td width="152">Customer Application Date</td>
<td width="184">CUSTOMER_APPLICATION_DATE</td>
<td width="64">DATE</td>
<td width="59">01112005</td>
</tr>
</tbody>
</table>
&nbsp; In the above example of target data, attribute names, column names, and datatypes are consistent throughout the target system. This is how data from various source systems is integrated and accurately stored into the data warehouse

What is a Data Warehouse

Introduction  to Data Warehouse

what-is-a-data-warehouse

<h2>Data Warehouse Architecture Diagram</h2>
<img class="alignnone wp-image-7244" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Architecture1-460x345.jpg" alt="Architecture1" width="528" height="396" /> &nbsp;
&nbsp;
<h2>Data Warehouse &amp; Data Mart</h2>
A data warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.
In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

Data Warehouse Architecture | Data Warehouse Tutorial

Data Warehouse Architecture

data-warehouse-architecture

There are three types of data warehouses Enterprise Data Warehouse ODS(Operational Data Store) Data Mart &nbsp;
Enterprise Data Warehouse - An enterprise data warehouse provides a central database for decision support throughout the enterprise.
&nbsp;
ODS(Operational Data Store) - This has a broad enterprise wide scope, but unlike the real entertprise <a href="https://datauntold.com/data-warehouse-developer-skills/" target="_blank">data warehouse</a>, data is refreshed in near real time and used for routine business activity.
 Data Mart - Datamart is a subset of data warehouse and it supports a particular region, business unit or business function.
Data warehouses and data marts are built on dimensional data modeling where fact tables are connected with dimension tables. This is most useful for users to access data since a database can be visualized as a cube of several dimensions. A data warehouse provides an opportunity for slicing and dicing that cube along each of its dimensions.
<h2>What is a Data Mart</h2>
A data mart is a subset of data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources. &nbsp;
<h3>&nbsp;Data Warehouse and Data Marts</h3>
&nbsp; <img class="alignnone wp-image-7250" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/120.png" alt="1" width="332" height="449" /> &nbsp;
<h3>General Information</h3>
In general, an organization is started to earn money by selling a product or by providing service to the product. An organization may be at one place or may have several branches. &nbsp;

Types of Data Warehouses - Tekslate

Types of Data Warehouses

types-data-warehouses

&nbsp;
<h2 dir="ltr">Star Schema and Snowflake Schema in Data Warehousing</h2>
<h2 dir="ltr">What is Star Schema?</h2>
Star schema gives a very simple structure to store the data in the <a title="Data Warehousing Tutorial" href="data-warehouse-tutorials" target="_blank" rel="noopener">data warehouse</a>. The centre of this start schema one or more fact tables which indexes a series of dimension tables. To understand star schema, it is very important to understand fact tables and dimensions in depth.
The purpose of Star Schema is to collect the information of numerical &ldquo;fact&rdquo; data relating to business and separate it from &ldquo;dimensional&rdquo; or descriptive data. Fact data includes information like weight, price, quantities, and speed that is the data in the numerical format. Dimensional data includes information of untouchable things like model names, colors, employee names, geographical locations along with numerical data.
The fact data is organized in the fact table, and the dimensional data is organized in the dimension table.&nbsp;
<h2 dir="ltr">Types of Fact tables</h2>
Accumulating Snapshot tables: accumulating snapshot tables record the data related to the running tally of the data.
Transaction Fact tables: transaction fact tables record data related to events like individual stock sales.&nbsp;
Snapshot Fact tables: snapshot fact tables record the data which applies to a particular moment in time, like month-end account statements.
<h2 dir="ltr">Types of Dimension Tables</h2>
Employee dimension table: employee dimension tables contain information about salespeople and employees like employee phone number, address, email address, and so on.
Geography dimension table: location and address information.
Range dimension table: range dimension table contains information related to the range of values for price, time, etc.,
Time dimension table: time dimension table contains the information to identify the exact date, year, month, and time.
Customer dimension table: customer dimension table contains information like customer number, name, address, and so on.
&nbsp;
<img src="https://docs.google.com/drawings/u/0/d/sTcDfeWbpfWmxYFgSl2Vedg/image?w=491&amp;h=274&amp;rev=143&amp;ac=1&amp;parent=1oMnyWwrYC-GkZbwyF7SYxnlrOPihJ2KnnRxnsFYHzEM" alt="Star schema" width="491" height="274" aria-roledescription="Drawing" /> &nbsp;&nbsp;&nbsp;&nbsp;
<ul>
<li>
In a star schema, each dimension is represented by only one dimension table.
</li>
<li>
A dimension table contains a set of attributes.
</li>
<li>
A fact table in the centre contains the keys to every dimension table.
</li>
<li>
The fact table also contains attributes like units sold and dollars sold.
</li>
</ul>
<h3 dir="ltr">Star schema characteristics</h3>
<ul>
<li>
The dimension table is connected to the fact table by using the foreign key.
</li>
<li>
Dimension tables are not joined to each other.
</li>
<li>
The schema is supported by BI tools.
</li>
<li>
The dimension tables are not normalized.
</li>
<li>
Star schema is very easy to understand.
</li>
<li>
Star schema gives optimal disk usage.
</li>
</ul>
Do you want to master Snowflake? Then enrol in<a href="snowflake-training" target="_blank" rel="noopener"> "Snowflake Training"</a> This course will help you to master Snowflake
<h3 dir="ltr">Advantages of star schema</h3>
<ul>
<li>
Star schemas simplify the method of pulling business reports live period-over-period reports.
</li>
<li>
Star schema gives data to Online Analytic Processing systems.
</li>
<li>
As the complete data connects through a single fact table, the various dimension tables are considered as one huge table of data, and that makes queries more comfortable to perform.
</li>
<li>
The performance of read-only commands is very high.&nbsp;
</li>
<li>
The speed of the query is very high.
</li>
</ul>
<h2 dir="ltr">What is a Snowflake Schema?</h2>
Now, as you are aware of a star schema, you are ready to understand the snowflake schema. Snowflake schema normalizes the data that is denormalized in the star schema. Snowflake schema solves the write command slow-downs and few other problems that are associated with the star schema.
The snowflake schema is the multidimensional structure. Same as the star schema the fact table connects to the dimension table but the only difference is in the snowflake schema the dimension tables are divided into sub-dimension tables which creates a snowflake pattern.
The snowflake schema normalizes the dimension schema, which is connected to it by getting rid of &ldquo;low cardinality&rdquo; attributes and turning the dimension tables into multiple tables until that dimension table is entirely normalized.&nbsp;
&nbsp;
<img src="https://docs.google.com/drawings/u/0/d/sEBv6OVQVA2ib5Xdb4JwWrw/image?w=602&amp;h=415&amp;rev=214&amp;ac=1&amp;parent=1oMnyWwrYC-GkZbwyF7SYxnlrOPihJ2KnnRxnsFYHzEM" alt="Snowflake schema" width="602" height="415" aria-roledescription="Drawing" />
<ul>
<li>
Hierarchies are divided into different tables.
</li>
<li>
Very complex database design.
</li>
<li>
Normalized data structure.
</li>
<li>
Low-level data redundancy.
</li>
</ul>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Data Warehouse Interview Questions" href="interview-questions-on-data-warehouse" target="_blank" rel="noopener">Data Warehouse Interview Questions</a>
<h3 dir="ltr">Snowflake Schema Characteristics</h3>
<ul>
<li>
The biggest advantage of the snowflake schema is it uses minimal disk space.
</li>
<li>
Because of multiple tables, the query performance will be reduced.
</li>
<li>
Query performance is reduced due to multiple tables.
</li>
<li>
Maintenance efforts are required because of more lookup tables.
</li>
</ul>
<h3>Advantages of Snowflake Schema</h3>
<ul>
<li>
Some OLAP database tools data scientists utilize for data modeling and analysis are mainly designed to work snowflake data schemes.
</li>
<li>
Normalizing the data which would typically get denormalized within a star schema can give an enormous reduction in disk space requirements. This is because you are converting long strings of non-numerical data within numerical keys which are dramatically abrupt taxing from a storage viewpoint.
</li>
</ul>
<h2 dir="ltr">Star Schema vs Snowflake Schema</h2>
Let us see the difference between star schema and snowflake schema:
<div dir="ltr" align="left">
<table><colgroup><col /><col /></colgroup>
<tbody>
<tr>
<td>
Star Schema
</td>
<td>
Snowflake Schema
</td>
</tr>
<tr>
<td>
Star Schema is a top-down approach.
</td>
<td>
A snowflake schema is a bottom-up approach.
</td>
</tr>
<tr>
<td>
Time taken for executing queries is significantly less.
</td>
<td>
Time taken for executing queries is more.
</td>
</tr>
<tr>
<td>
Designing is very simple.
</td>
<td>
Designing is complex.
</td>
</tr>
<tr>
<td>
High data redundancy.
</td>
<td>
Low data redundancy.&nbsp;
</td>
</tr>
<tr>
<td>
Contains fewer foreign keys.
</td>
<td>
Contains more foreign keys.
</td>
</tr>
</tbody>
</table>
</div>
Conclusion:
Star and snowflake schemas are alike at heart: a central fact table enclosed by dimension tables. The difference is within the dimensions themselves. Within a star schema every logical dimension is denormalized inside one table, while within a snowflake, at least some of the dimensions are normalized.
&nbsp;

Star Schema & Snow Flake Schema | Data Warehousing

Types of Schema's in Data Warehouse

types-schemas-data-warehouse-star-snowflake

<h2>Database - RDBMS</h2>
<h2>&nbsp;</h2>
There are a number of relational databases to store data. A relational database contains normalized data stored in tables. Tables contain records and columns. RDBMS makes it easy to work with individual records. Each row contains a unique instance of data for the categories defined by the columns.
RDBMS are used in OLTP applications(e.g. ATM cards) very frequently and sometimes datawarehouse may also use relational databases. Please refer to Relational data modeling for details to know how data from a source system is normalized and stored in RDBMS databases.
<h2>Popular RDBMS Databases Vendors</h2>
<h2>&nbsp;</h2>
RDBMS Name&nbsp;&nbsp;&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" />Company Name Oracle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> Oracle Corporation IBM DB2 UDB&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> IBM Corporation IBM Informix&nbsp;&nbsp;&nbsp;&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> IBM Corporation Microsoft SQL Server<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> Microsoft Sybase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> Sybase Corporation Terradata&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<img class="alignnone size-full wp-image-7108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/1111.png" alt="11" width="37" height="12" /> NCR &nbsp;

Popular Database Vendors - Tekslate

Popular Database Vendors

types-databases-rdbms-data-warehouse

<h2>ETL Concepts</h2>
<h2>&nbsp;</h2>
Extraction, transformation, and loading. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database. The first step in ETL process is mapping the data between source systems and target database(data warehouse or data mart). The second step is cleansing of source data in staging area. The third step is transforming cleansed source data and then loading into the target system. Note that ETT (extraction, transformation, transportation) and ETM (extraction, transformation, move) are sometimes used instead of ETL. &nbsp; Glossary of ETL (Reference:www.Oracle.com) Source System A database, application, file, or other storage facility from which the data in a data warehouse is derived. &nbsp; Mapping The definition of the relationship and data flow between source and target objects. &nbsp; Metadata Data that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata. &nbsp; Staging Area A place where data is processed before entering the warehouse. &nbsp; Cleansing The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process. &nbsp; Transformation The process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources. &nbsp; Transportation The process of moving copied or transformed data from a source to a data warehouse. &nbsp; Target System A database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse. &nbsp;
<h3>Sample ETL Process Flow</h3>
<img class="alignnone wp-image-7263" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/128-460x393.png" alt="1" width="658" height="561" /> &nbsp; &nbsp;

ETL Concepts in Data Warehouse - Tekslate

ETL Concepts in Data Warehouse

etl-concepts-data-warehouse

<h2 style="text-align: justify;">What is Data Mining?</h2>
Data Mining is a set of processes related to analyzing and discovering useful, actionable knowledge buried deep beneath large volumes of data stores or data sets. This knowledge discovery involves finding patterns or behaviors within the data that lead to some profitable business action. Data Mining requires generally large volumes of data including history data as well as current data to explore the knowledge. Once the required amount of data has been accumulated from various sources, it is cleaned, validated and prepared for storing it in the data warehouse or data mart. BI reporting Tools capture the required facts from these data to be used by the knowledge discovery process. Data Mining can be accomplished by utilizing one or more of the traditional knowledge discovery techniques like Market Basket Analysis, Clustering, Memory Based Reasoning, Link Analysis, Neural Networks and so on.
<h2 style="text-align: justify;">Data Mining Life Cycle</h2>
<ul style="text-align: justify;">
<li>
<h3>Find out the Business Problem:</h3>
</li>
</ul>
Consider a company's current year sales dropped by a percentage when compared to the previous year. By using OLAP Tools, the exact sales fact can be determined across several dimensions like region, time etc.
<ul style="text-align: justify;">
<li>
<h3>Knowledge Discovery:</h3>
</li>
</ul>
Given this business problem, various reasons for the decrease in sales have to be analyzed utilizing one or more of the Data Mining Techniques. Causes may include poor quality or service of the product or flaws in marketing schemes or less demand for the product or seasonal changes or regulations enforced by the Government or competitors pressure, and so on. The exact solutions have to be found out in order to resolve this sales drop, which we call it as the Knowledge Discovery here.
<ul style="text-align: justify;">
<li>
<h3>Implement the Knowledge:</h3>
</li>
</ul>
Based on above discovery, proper actions should be taken in order to overcome the business problem.
<ul style="text-align: justify;">
<li>
<h3>Analyze the Results:</h3>
</li>
</ul>
Once it is been implemented, results need to be monitored and measured to find out outcomes of that action.
<h2 style="text-align: justify;">OLAP vs Data Mining</h2>
OLAP helps organizations to find out the measures like sales drop, productivity, service response time, inventory in hand etc. Simply, OLAP tell us 'What has happened' and Data Mining helps to find out 'Why it has happened' at the first place. Data Mining can also be used to predict 'What will happen in the future' with the help of data patterns available within the organization and publicly available data.
For example if a borrower with bad credit and employment history apllies for a mortgage loan, his/her application may be denied by a mortgage lender since he/she may default the loan if approved. The mortgage lender would have come to this decision based upon the historical data previously mined following a similar pattern.

What is Data Mining in Data Warehouse

Data Mining in Data Warehouse

what-is-data-mining-data-warehouse

<h2>Business Intelligence Tool Guide</h2>
&nbsp; Business Intelligence Tools assist organizations to improve their overall performance by helping them to plan, track, monitor, analyze and report the business activities. These tools improve customer relationship management thereby increasing company's profitability significantly. Below are the few guidelines which may be of help while working with BI tools &nbsp;
<ul>
<li>How to install and setup the BI software?</li>
</ul>
<ul>
<li>How to get license and training from BI software vendors?</li>
</ul>
<ul>
<li>How to create users, administrators and assign privileges to users?</li>
</ul>
<ul>
<li>How to connect to the different database servers from BI applications?</li>
</ul>
<ul>
<li>How to understand and work on BI data models or universe?</li>
</ul>
<ul>
<li>How to frame the select statement according to the business requirements?
<ul>
<li>o How to select the tables that have to be used in the report? o How to select the columns that are required for reporting?o How to write the join condition to join (inner join, outer join, equi join) different tables in select statement? o How to write multiple select statements in a single report?o How to write the filters (null, in, equal to, greater than) that are required after the where clause? o How to create dimensions and facts?o How to drill up and drill down?o How to set user prompts for user to enter values? o How to process the query and retrieve the results?</li>
</ul>
</li>
</ul>
&nbsp;
<ul>
<li>How to work on results?
<ul>
<li>
<ul>
<li>How to modify field formats?</li>
</ul>
<ul>
<li>How to sort data?</li>
</ul>
<ul>
<li>How to create computer items like date functions, numeric and string functions?</li>
</ul>
</li>
</ul>
</li>
</ul>
<ul>
<li>How to create pivots?
<ul>
<li>
<ul>
<li>How to add data?</li>
</ul>
<ul>
<li>How to create totals?</li>
</ul>
<ul>
<li>How to group data?</li>
</ul>
</li>
</ul>
</li>
</ul>
<ul>
<li>How to create charts?</li>
</ul>
<ul>
<li>How to create reports?
<ul>
<li>
<ul>
<li>How to work on reporting body?</li>
</ul>
<ul>
<li>How to work on report group headers?</li>
</ul>
<ul>
<li>How to work on report header/footer?</li>
</ul>
<ul>
<li>How to work on page header/footer?</li>
</ul>
<ul>
<li>How to design the report layout?</li>
</ul>
<ul>
<li>How to use page breaks?</li>
</ul>
</li>
</ul>
</li>
</ul>
<ul>
<li>How to schedule, monitor, modify, delete, and refresh a job(report)?</li>
</ul>
<ul>
<li>How to write report design document, /report testing document, test reports and get user acceptance?</li>
</ul>
<ul>
<li>How to distribute reports and results via email, printers, intranet server, and web?</li>
</ul>
<ul>
<li>How to export and import data?</li>
</ul>
<ul>
<li>How to track on scorecards, balancing scorecards, forecasting, key performance indicators and dashboards?</li>
</ul>
&nbsp; &nbsp;
<h2>Business Intelligence &amp; Key Performance Indicators</h2>
&nbsp; Key Performance Indicators, commonly referred to as KPIs, are a list of measurements that are identified as critical factors in achieving the organizational goals or mission. KPIs are often identified in a business to help them drive a business towards its success and are associated with a number of business activities like Customer Relationship Management(CRM), Supply Chain Analytics or any other activity that is happening within the organization. &nbsp;
<h2>Requirements of a good KPI</h2>
&nbsp; There can be a number of factors related with the success of a company; All of these factors cannot be chosen as the indicators; Only those that are mission critical, strictly adhering to the organizational goals and accurately measurable should be selected as the company's KPIs. It is always better to keep the number of KPIs to a minimum to make sure that greater focus can be given to each of these indicators. So the important factors to be considered in selecting a KPI are as follows: &nbsp;
<ul>
<li>Measurable: A KPI should be quantifiable in terms of numbers.</li>
</ul>
<ul>
<li>Reflect the organizational Goals: A KPI should drive a business towards success.</li>
</ul>
<ul>
<li>Actionable: It should help the managers to initiate some business action as a result of all the analysis and measures lead by KPI.</li>
</ul>
&nbsp;
<h3>Examples of KPIs</h3>
&nbsp; A KPI may reflect regional sales by sales person, supply chain statistics by supplier, productivity by units, customer satisfaction, customer growth or it may reflect employee turnover. In either case, it should give a high-level, real time information to the top level managers enabling them to concentrate in the company's success. &nbsp;

Business Intelligence Tools in Data Warehouse - Tekslate

Business Intelligence Tools in Data Warehouse

business-intelligence-tool-guide-data-warehouse

<h2 style="text-align: justify;">What is a Dashboard in Business terms?</h2>
<h2 style="text-align: justify;">&nbsp;</h2>
A Business Intelligence Dashboard visually represents the key organizational performance data in a near real time, user friendly manner that can be understood instantaneously. Technically speaking, a Dashboard is a visual representation that reflects the Key Performance Indicators(KPIs) of interest for managerial review and not only that it enables them to drill-down further. Business Intelligence Dashboard is similar in function to a car dashboard in that it displays and provides access to the powerful analytical systems and key performance metrics in a form enabling business executives to analyze trends and more effectively manage their areas of responsibility.
<h2 style="text-align: justify;">Features of Dashboard</h2>
<h2 style="text-align: justify;">&nbsp;</h2>
A typical web based Business Intelligence Dashboard encompasses the following features
<ul style="text-align: justify;">
<li>Web based Interface: Managers can gain broad visibility into the real-time key measurements of a business with the help of this multi-window, intuitive and interactive interface.</li>
</ul>
<ul style="text-align: justify;">
<li>Role Based View: Executives can clearly track their organization's overall performance against its goals.</li>
</ul>
<ul style="text-align: justify;">
<li>Reports: Configurable, user-level as well as management-level reports.</li>
</ul>
<ul style="text-align: justify;">
<li>Charting and Graphing: Dashboards are better known for their easy one-click charts and graphs that gives instant access to complex solutions.</li>
</ul>
<ul style="text-align: justify;">
<li>Pre-defined Performance Metrics: All the Dashboards are built with the common pre-defined metrics by default which eases the business user in tracking the regular yet important performance metrics.</li>
</ul>
<h2 style="text-align: justify;">Benefits of using Business Intelligence Dashboard</h2>
<h2 style="text-align: justify;">&nbsp;</h2>
Dashboards quickly convert and communicate the complex corporate data into a meaningful display of charts, graphs, gauges and other formats concurrently. A dynamic, intelligence Dashboard will allow the managers to drill-down data to go deeper into the analysis. It eliminates the need to go through several reports, in one shot Dashboard gives a clear picture about how a company is performing in its critical areas.
<h2 style="text-align: justify;">Dashboard Softwares</h2>
Following are few of the famous Business Intelligence Dashboard softwares available in the current market.
<h3 style="text-align: justify;">Cognos ReportNet</h3>
A product from Cognos that helps to build, distribute, and view multi-object dashboards with graphical elements such as gauges, charts, and clickable pictures. Connects dashboards with underlying tabular data, letting people go from a high-level view to a deeper understanding.
Source: www.Cognos.com
<h3 style="text-align: justify;">MicroStrategy</h3>
Microstrategy Dashboards are designed to deliver maximum visual impact in a format optimized for quick absorption, using a combination of tables, graphics, gauges, dials and other graphical indicators, as well as conditional formatting, free-form labels, borders and background colors.
Source: www.microstrategy.com
<h3 style="text-align: justify;">BusinessObjects Dashboard Manager:</h3>
Dashboard Manager lets one choose the level of detail they need - an overview, a specific chart or graph, or the underlying report.
Source: www.businessobjects.com
<h2 style="text-align: justify;">The Compliance Management Dashboard</h2>
A product from Hyperion provides easy-to-use screens, meters, and stoplights that quickly convey critical information. The Compliance Management Dashboard includes a data model with pre-built connectors to data sources, a set of compliance metrics, and a series of easy-to-use screens, meters, and stoplights designed to quickly convey critical information needed by finance executives. Source: www.hyperion.com
Scorecards:
Scorecards are similar to Dashboards in a way that it provides easy-to-understand, summarized, at-a-glance data for the managers and top officials to tell them about their company's present and past performance.
Scorecards thus help to monitor the Key Performance Indicators accurately and to communicate the goals and strategies across the organization in an efficient and elegant manner. In a Business Intelligence environment, Scorecards allows managers to set metrics or targets and monitor them to see their impact on every department.
Balanced Scorecards:
A methodology created by Drs. Robert S. Kaplan and David P. Norton in 1992, is a management concept which helps managers at all levels monitor results in their key areas, including financial performance, customer knowledge, internal business processes and learning. It has been implemented in thousands of corporations, organizations and government agencies worldwide
&nbsp;Business Intelligence Tools Directory:
<ul style="text-align: justify;">
<li>Actuate</li>
</ul>
<ul style="text-align: justify;">
<li>Business Objects</li>
</ul>
<ul style="text-align: justify;">
<li>Cognos</li>
</ul>
<ul style="text-align: justify;">
<li>HummingBird</li>
</ul>
<ul style="text-align: justify;">
<li>Hyperion</li>
</ul>
<ul style="text-align: justify;">
<li>Information Builders</li>
</ul>
<ul style="text-align: justify;">
<li>MicroStrategy</li>
</ul>
<ul style="text-align: justify;">
<li>ProClarity</li>
</ul>
<ul style="text-align: justify;">
<li>Siebel </li>
</ul>

Dashboards in Data Warehouse - Tekslate

Dashboards in Data Warehouse

dashboards-data-warehouse

<h2 style="text-align: justify;">What is OLAP?</h2>
OLAP, an acronym for 'Online Analytical Processing' is a technique by which the data sourced from a data warehouse or data mart is visualized and summarized to provide perspective multidimensional view across multiple dimensions. Generally OLAP refers to OLAP Tools(e.g Cognos, Business Objects etc.,) that help to accomplish these tasks. Since data warehouse is designed using a dimensional data model, data is represented in the form of data cubes enabling us to aggregate facts, slice and dice across several dimensions. OLAP tools provide options to drill-down the data from one hierarchy to another hierarchy.
For example sales amount can be calculated for a particular year or it can be drilled down to its next hierarchies like month, week, day etc. In the same way, data can be rolled up for summarization from product to product group, product group to product sub-class then from product sub-class to product class. Thus with this cube structure, data can be viewed from multiple points providing the data analysts, a greater insight into data.
There are many OLAP hybrids or variants like MOLAP(Multidimensional OLAP), HOLAP(Hybrid OLAP), ROLAP(Relational OLAP), DOLAP(Desktop OLAP or Database OLAP) available in the market and can be used depending on the needs and requirements of an organization
<h2 style="text-align: justify;">OLAP - Examples</h2>
Topmost executives of an organization are really interested in aggregated facts or numbers to take decisions rather than querying several databases (that are normalized) to get the data and do the comparison by themselves. OLAP tools visualize the data in an understandable format, like in the form of Scorecards and Dashboards with Key Performance Indicators enabling managers to monitor and take immediate actions. In todays business life, OLAP plays a vital role by assisting decision makers in the field of banking and finance, hospitals, insurance, manufacturing, pharmaceuticals etc., to measure facts across geography, demography, product, and sales.
OLAP can be performed in data warehouses that undergo frequent updates and that do not. Following are some of the examples to show how OLAP solves complex queries involving facts to be measured across company&rsquo;s best-interested dimensions.
<ul style="text-align: justify;">
<li>Comparison of sales (fact) of a product (dimension) over years (dimension) in the same region (dimension).</li>
</ul>
<ul style="text-align: justify;">
<li>How may members (fact) have opened a savings account (dimension), in USA branch (dimension), over a period (dimension)?</li>
</ul>
<ul style="text-align: justify;">
<li>How many mortgage loans (fact) have been approved in fixed mortgage (dimension) or Adjustable Rate Mortgage (dimension) in New York City (dimension), over a period (dimension)?</li>
</ul>
<ul style="text-align: justify;">
<li>What is the total sales value (fact) of a particular product (dimension) in a particular grocery store (dimension), over a period (dimension)?</li>
</ul>
<ul>
<li style="text-align: justify;">What is the amount spent (fact) for a particular product promotion (dimension) in a particular branch (dimension) or in a particular city (dimension), over a period (dimension)?</li>
</ul>

What is OLAP in Data Warehouse

OLAP in Data Warehouse

what-is-olap-data-warehouse

&nbsp;
<h2 dir="ltr">Big Data Vs Data Warehouse</h2>
Data is the greatest power of any institution these days. The organizations that can study and gain insights from the collected data are the ones who will excel in their business. This is simply because of the fact that your data never lies. All you need to do is to interpret it properly.
Big Data and Data warehouses are two important mechanisms that can supply an organization with much-needed insights into its data. Big data is like a big pool that can accommodate any kind of data (clean or unclean) for processing. However, the data warehouse can only clean and processed data.
This is just the primary difference between these two mechanisms. Here are some more details on Big data v Data Warehouse
<div dir="ltr" align="left">
<table><colgroup><col width="161" /><col width="254" /><col width="206" /></colgroup>
<tbody>
<tr>
<td style="text-align: center;">
BASIS FOR COMPARISON
</td>
<td style="text-align: center;">
Data Warehouse
</td>
<td style="text-align: center;">
Big Data
</td>
</tr>
<tr>
<td>
Meaning
</td>
<td>
Data Warehouse is for the most part an engineering technique and not an innovation. It separates information from assortments <a href="sql-rdbms-concepts" target="_blank" rel="noopener">SQL </a>based information sources (fundamentally social databases) and helps for creating investigative reports. As far as definition, an information storehouse, which utilizes any systematic reports, has been created from one procedure, which is only the warehouse of data.
</td>
<td>
Big Data is basically an innovation, which remains on volume, speed, and assortment of information. Volumes characterize the measure of information originating from various sources, speed alludes to the speed of information handling, and assortments allude to the number of sorts of information (for the most part supports all kinds of information design).
</td>
</tr>
<tr>
<td>
Preferences
</td>
<td>
If an association needs to know some educated choice (like what is happening in their enterprise, one year from now arranging dependent on current year execution information, and so on), they want to pick information warehousing, concerning this sort of report they need solid or trustworthy information from the points of origin.
</td>
<td>
If the association needs to contrast and a ton of large information, which contains significant data and help them to take a superior choice (like how to lead more income, greater gainfulness, more clients, and so on), they clearly apply the Big Data methodology.
</td>
</tr>
<tr>
<td>
Accepted Data Source
</td>
<td>
Accepted at least one homogeneous (all locales utilize a similar DBMS item) or heterogeneous (destinations may run diverse DBMS item) information sources.
</td>
<td>
Accepted any kind of sources, including business transactions, social media, and information from sensor or machine-specific data. It can come from a DBMS product or not.
</td>
</tr>
<tr>
<td>
Accepted type of formats
</td>
<td>
It is accustomed to working with the most part basic information (explicitly social data).
</td>
<td>
It has been tuned to accept a wide range of configurations. Structure information, social information, and unstructured information including content archives, email, video, sound, stock ticker information, and money related exchange.
</td>
</tr>
<tr>
<td>
Subject-Oriented
</td>
<td>
An information distribution center is a subject arranged on the grounds that it really gives data on the particular subject (like an item, clients, providers, deals, income, and so on) not on association continuous activity. It doesn't concentrate on progressing activity, it for the most part centers on the examination or showing information which helps in the last set of decision making.
</td>
<td>
Big Data is additionally subject-situated, the fundamental distinction is a wellspring of information, as large information can acknowledge and process information from all the sources including web-based life, sensor, or machine explicit information. It additionally fundamental to give accurate examination of information explicitly on the subject arranged.
</td>
</tr>
<tr>
<td>
Time-Variant
</td>
<td>
The information gathered in an information stockroom is really distinguished by a specific timespan. As it primarily holds authentic information for a diagnostic report.
</td>
<td>
Big Data has a lot of ways to deal with recognized previously stacked information, a timeframe is one of the methodologies on it. Huge information basically handling level documents, so file with date and time will be the best way to deal with distinguishes stacked information. Be that as it may, it has the alternative to work with spilling information, so it not continually holding chronicled information.
</td>
</tr>
<tr>
<td>
Non-volatile
</td>
<td>
Previous information never deletes when new information added to it. This is one of the significant highlights of an information distribution center. As it very surprising from an operational database, so any progressions on an operational database won't legitimately affect a data warehouse.
</td>
<td>
For Big data, again past information never eradicates when new information added to it. It put away as a document that speaks to a table. Be that as it may, here some of the time if there should be an occurrence of spilling legitimately use Hive or Spark as an activity situation.
</td>
</tr>
<tr>
<td>
Distributed File System
</td>
<td>
Processing of gigantic information in Data Warehousing is truly tedious and here and there it took a whole day to finish the process.
</td>
<td>
This is one of the enormous utility of Big Data. HDFS (Hadoop Distributed File System) for the most part characterized to stack enormous information in circulated frameworks by utilizing map decrease programs.
</td>
</tr>
</tbody>
</table>
</div>
&nbsp;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="data-warehouse-tutorials" target="_blank" rel="noopener">Data Warehouse Tutorial</a>
<h3 dir="ltr">Conclusion</h3>
From the primary reading of the above table on Big data v Data warehouse, one can conclude that big data is a better mechanism to use. The data warehouse has got its own advantages but the big data mechanism proves to be better in most departments.&nbsp;
I hope we were able to clear some air on the topic of Big Data v Data warehouse. If you need any further clarification or explanations, do write back to us.
&nbsp;

data-warehousing

Articles