BI Reporting

Business Intelligence Reports

Data is collected from various sources and cleansed or transformed into a form required by data warehouse or data mart decision support systems. BI tools provide a means of taking advantage of these data contained within a data warehouse or data mart by providing answer to critical queries related to the business.

The power of business intelligence actually relies on the knowledge acquired through the analysis of various reports taken using business intelligence reporting tools. Business Intelligence Reporting Tools or OLAP Tools provide different views of data by pivotting or rotating the data across several dimensions. BI tools are capable of displaying data in several formats like, tables, pivots, charts, query results or reports. 

Zero Footprint Technology

Earlier, with client/server architecture, business intelligence reporting tools were installed on each user's desktop in order to connect to the database and to take reports. Nowadays, with the revolution of web advancement, Zero Footprint technology allows users to connect to the database and see the reports through web browsers which doesn't require them to install any software to do so. With the help of this Zero Footprint technology, the reports that were only availabale to top executives, now can be seen by any employee making them to participate and contribute towards the decision making.

source: learnbi.com

ETL Process & Data Warehouse

ETL, an acronym for 'Extraction, Transformation and Loading' is a collection of processes associated with extracting the source data, transforming that data and finally loading that data into a data warehouse. Before loading the required into data warehouse, it should be transformed in order to meet the needs of the data warehouse. This transformation involves several processes like data cleansing, data profiling, data type conversion, validating for referential integrity, performing aggregation if needed, denormalization and normalization.

Data Warehouse

Data warehouse is a centralized repository where all the information for analysis is kept in an organization. This is the data collected from variant sources for the purpose of analytical processing and reporting. This data is non-volatile and a data warehouse is built on a dimensional data model. From this data warehouse, data can be extracted for reporting needs with the help of query tools or many data marts can be built based on subject area requirements.

Data Mart

Data Mart is subject oriented, basically a sub-set of data warehouse, built for the purpose of analyzing a particular line of business or department. It holds the data specific to a particular subject area like sales, purchase etc. Data marts can be of derived from a data warehouse or built for the sole purpose of BI directly from the source and like data warehouse, data marts are also constructed from dimensional data models.

source: learnbi.com

Star Schema

In a Dimensional Data Model, a fact table is the centralized table which is connected to multiple dimensions related to that fact. This type of approach is known as the Star Schema design based on which data warehouse and data marts are built. Since BI takes advantage of data displayed in the form of mutli-dimensional cubes, this star schema approach helps analyze and produce complex reports very easy by slicing and dicing the dimensions of interest.

From the sample diagram shown below, the required Fact 'Loan Amount' can be calculated across various dimensions like state, branch, time, product, loan officer and investor dimensions.

Sample Star Schema Diagram

source: learnbi.com

Dimensional Modeling

Dimensional Model comprises a fact table and many dimension tables and is used for calculating summarized data. Since Business Intelligence reports are used in measuring the facts(aggregates) across multiple dimensions, dimensional data modeling is the prefered modeling technique in a BI environment. A Fact table contains various measures or facts like sales amount, loan amount etc., whereas a Dimension table describes the particular entity like time, state etc., based on which the required facts are measured.

source: learnbi.com

Data Modeling

Data Modeling is about representing the real world set of data structures or entities and their relationships in the form of Data Models, required for a database. Simply put, data model is a visual representaion of the database. 

Data Modeling consists of various types and phases like conceptual data modeling, logical data modeling, physical data modeling, enterprise data modeling, relational data modeling and dimensional data modeling.

Conceptual Data Modeling

Conceptual Data Modeling visualizes the overall structure of the database and provides high-level information about the subject areas or data structures of an organization and it does not contain much detailed level of information about attributes.

Logical Data Modeling

Logical Data Modeling is an extension to Conceptual Data Modeling and its includes almost all of the entities, attributes and their relationship. A logical Data Model will not contain any attribute specific information like type, length etc., instead it defines and conveys business information and rules.

Physical Data Modeling

Physical Data Model includes data structures like tables, columns, properties and the relationship between them.

Enterprise Data Modeling

Enterprise Data Modeling is known as global business model as it consolidates the information across the enterprise.

Relational Data Modeling

Relational Data Model revolves around Entity-Relationship Modeling where entities(tables) are normalized to avoid possible redundancy and this type is the prefered technique in OLTP.

source: learnbi.com