What is Business Intelligence?
BI is the act of transforming raw/operational data into useful information for business analysis
How Does it Work?
- BI based on Data Warehouse technology extracts information from a company’s operational systems
- The data is transformed (cleaned and integrated), and loaded into Data Warehouse
- Since this data is credible, it is used for business insights.
Why Data Warehouse?
- Data collected from various sources and stored in various databases (Oracle, SQL server, MySQL…) cannot be directly visualized
- The data first needs to be integrated and then processed before visualization takes place.
What is Data Warehouseing?
- A central location where consolidated data from multiple locations (databases) are stored
- DWH is maintained separately from an organization’s operational database. (DWH is another copy)
- End uses access it whenever any information is needed.
- Data Warehouse is not loaded every time new data is added to databases.
What are The Advantages of a Data Warehouse?
- Strategic questions can be answered by studying trends (from past data).
- Data Warehousing is faster and more accurate
- Data Warehouse is not a product that a company can go and purchase, it needs to be designed and depends entirely on the company’s requirement.
End User
=> Take the data from opeartional systems
=> Integrate the data from multiple sources
=> Standardize the data and remove inconsistencies
=> Store the data in format suitable for easy access
=> Return result to end user
Properties of a Data Warehouse
A Data Warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of managemnet’s decision-making process
- Subject-oriendted: Data is categorized and stored by business subject rather than by application. (data in data warehouse are suitable for business requirements)
- Integrated: Data on a given subject is collected from disparate sources and stored in a single place
- Time-variant: Data is stored as a series of snapshots, each representing a period of time
- Non-volatile: Typically data in the data warehouse is not updated or deleted
Key Terminologies Related to DWH Architechture
OLTP (Online Transaction Processing) vs OLAP (Online Analytical Processing)
| Relational Database (OLTP) | Analytical Data Warehouse (OLAP) |
|---|---|
| Contains current data | Contains historical data |
| Useful in running the business | Useful in analyzing the business |
| Based on Entity Relationship Model | Based on Star, Snowflake and Fact Constellation Schema |
| Provides primitive and highly detailed data | Provides summarized and consolidated data |
| Used for writing data into the database | Used for reading data from data warehouse |
| Database size ranges from 100MB to 1GB | Data warehouse size ranges from 100GB to 1TB |
| Fast, provides high performance | Highly flexible, but not fast |
| Number of records accessed is in tens | Number of records accessed is in millions |
| Example: All bank transactions made by a customer | Example: Bank transactions made by a customer at a particular time |
OLTP Examples:
- A supermarket server which records every single product purchased at that market
- A back server which records every time a transaction is made for a particular account
- A railway reservation server which records the transactions of a passenger
OLAP Examples:
- Bank manager wants to know how many customers are utilizing the ATM of his branch. Based on this he may take a call whether to continue with the ATM or relocate it.
- An insurance company wants to know the number of policies each agent has sold. This will help in better performance management of agents.
ETL
ETL is the process of extracting the data from various sources, transforming this data to meet your requirement and then loading it into a target data warehouse. (Tools: Talend, Informatica…)
Data Mart
- Data mart is a smaller version of the data warehouse which deals with a single subject.
- Data mart are focused on one area, hence, they draw data from a limited number of sources
- Time taken to build data marts is very less compared to the time taken to build a data warehouse
| Data Warehouse | Data Marts |
|---|---|
| Enterprise wide data | Department wide data |
| Multiple subject areas | Single subject data |
| Multiple data sources | Limited data sources |
| Occupies large memory | Occupies limited memory |
| Longer time to implement | Shorter time to implement |
Types of Data Mart
- Dependent Data Mart
- The Data is first extracted from the OLTP systems and them populated in the central DWH
- From the DWH, the data travels to the Data Mart
- Independent Data Mart
- The data is directly received from the source system
- This is suitable for small organizations or small groups within an organization
- Hybrid Data Mart
- The data is fed both from OLTP systems as well as the Data Warehouse
Metadata
- Metadata is defined as data about data
- Matadata in a DWH defines the source data. (Flat file, Relational Database and other objects)
- Matadata is used to define which table is source and target, and which concept is used to build business logic called transformation to the actual output.
DWH Architecture
