Business Intelligence (BI)

What is Business Intelligence (BI)?

•  Business Intelligence is a generalized term applied to a broad category of applications and technologies for gathering, storing, analyzing and providing access to data to help enterprise users make better business decisions
• Business Intelligence applications include the activities of decision support systems, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining
• An alternative way of describing BI is: the technology required to turn raw data into information to support decision-making within corporations and business processes

BusinessIntelligence Architecture

Business intelligence has become a critical element of information technology. It’s an old term with general or even ambiguous meaning. It has been used synonymously with decision support, analysis, and data warehousing, but today business intelligence has a more specific definition and a better understood application. Taken literally, business intelligence is just that—intelligence or understanding of your business. You get that understanding by analyzing your business operations.

This business intelligence process can deliver significant, bottom-line results. Implementing its technologies and applying its process can help make your business more effective and more efficient, increasing revenue, decreasing costs, and improving your relationships with customers and suppliers.

   Why BI?

• BI technologies help bring decision-makers the data in a form they can quickly digest and apply to their decision making.
• BI turns data into information for managers and executives and in general, people making decisions in a company.
• Companies want to use technology tactically to make their operations more effective and more efficient - Business intelligence can be the catalyst for that efficiency and effectiveness.

By definition, the moment any given business is operating, it begins generating data. Some obvious examples are sales, bookkeeping, production data, warehouse information, transportation and logistics, personnel, etc.In addition there also exists large volumes of data which are important to the business but not directly generated by business operations. Examples are market data, competitive data, tenders and proposal, legal information, raw material prices, etc.

As such, none of the above described information can be used in its raw form by corporate management to make decisions although the information is critical in helping make those business decisions.Therein lies the necessity for Business Intelligence. BI technologies help bring decision-makers the data in a form they can quickly digest and apply to their decision making. BI turns data into information for managers and executives and in general, people making decisions in a company.

     Benefits:

 The benefits of a well-planned BI implementation are going to be closely tied to the business objectives driving the project.

1. Identify trends and anomalies in business operations more quickly, allowing for more accurate and timelier decisions.
2. Deliver actionable insight and information to the right place with less effort .
3. Identify and operate based on a single version of the truth, allowing all analysis to be completed on a core foundation with confidence.

 

Facebook Commentbox

What is ETL (Extract, Transform, Load)

ETL refers to the methods involved in accessing and   manipulating source   data and loading it into target database. During the ETL process, more   often, data is extracted from an OLTP database, transformed to   match the data warehouse schema, and loaded into the data warehouse   database.

(Extract, Transform, Load) –

ETL Process

 EXTRACTION :

• Data Capturing: The ETL extraction element is responsible for extracting data from the source system. During extraction, data may be removed from the source system or a copy made and the original data retained in the source system.

Data Capturing

• Data Transmission: Legacy systems may require too much effort to implement such offload processes, so legacy data is often copied into the data warehouse, leaving the original data in place. Extracted data is loaded into the data warehouse staging area (a relational database usually separate from the data warehouse database), for manipulation by the remaining ETL processes. 

Data Transmission

• Cleansing Process: 

1. Data extraction is generally performed within the source system itself.
2. Data extraction processes can be implemented using Transact-SQL stored procedures, Data Transformation Services (DTS) tasks, or custom applications developed in programming or scripting languages. 

Transformation:

         The ETL transformation element is responsible for data validation, data accuracy, data type conversion, and business rule application. An ETL system that uses inline transformations during extraction is less robust and flexible than one that confines transformations to the reformatting element. Transformations performed in the OLTP system impose a performance burden on the OLTP database.

• Data Validation: Check that all rows in the fact table match rows in dimension tables to enforce data integrity.

• DataAccuracy : Ensure that fields contain appropriate values, such as only "off" or "on" in a status field.

• Data Type Conversion : Ensure that all values for a specified field are stored the same way in the data warehouse regardless of how they were stored in the source system. For example, if one source system stores "off" or "on" in its status field and another source system stores "0" or "1" in its status field, then a data type conversion transformation converts the content of one or both of the fields to a specified common value such as "off" or "on".

Transformation

• Business Rule Application: Ensure that the rules of the business are enforced on the data stored in the warehouse. For example, check that all customer records contain values for both FirstName and LastName fields. 

Loading :

• The ETL loading element is responsible for loading transformed data into the data warehouse database.
• Data warehouses are usually updated periodically rather than continuously, and large numbers of records are often loaded to multiple tables in a single data load.
• The data warehouse is often taken offline during update operations so that data can be loaded faster and SQL Server 2000 Analysis Services can update OLAP cubes to incorporate the new data. BULK INSERT, bcp, and the Bulk Copy API are the best tools for data loading operations.
• The design of the loading element should focus on efficiency and performance to minimize the data warehouse offline time.

Facebook Commentbox

    

Data Warehouse Design Approaches

There are two major types of approaches to building or designing the Data Warehouse.

1.  The Top-Down Approach
2.  The Bottom-Up Approach

s  The Top Down Approach:

•       The data flow in the top down OLAP environment begins with data extraction from the operational data sources. This data is loaded into the staging area and validated and consolidated for ensuring a level of accuracy and then transferred to the Operational Data Store (ODS). 

•      Detailed data is regularly extracted from the ODS and temporarily hosted in the staging area for aggregation, summarization and then extracted and loaded into the Data warehouse. 

•      Once the Data warehouse aggregation and summarization processes are complete, the data mart refresh cycles will extract the data from the Data warehouse into the staging area and perform a new set of transformations on them. This will help organize the data in particular structures required by data marts. Then the data marts can be loaded with the data and the OLAP environment becomes available to the users

DWH TOP DOWN DESIGN APPROACH

•     The data marts are treated as sub sets of the data warehouse. Each data mart is built for an individual department and is optimized for analysis needs of the particular department for which it is created.

 The Bottom-Up Approach:

          

•      Ralph Kimball designed the data warehouse with the data marts connected  to it with a bus structure.

•      The bus structure contained all the common elements that are used by data marts such as conformed dimensions, measures etc defined for the enterprise as a whole. 

•        This architecture makes the data warehouse more of a virtual reality than a physical reality

•       All data marts could be located in one server or could be located on different servers across the enterprise while the data warehouse would be a virtual entity being nothing more than a sum total of all the data marts

•      In this context even the cubes constructed by using OLAP tools could be considered as data marts.

Kimball Approach

•              The bottom-up approach reverses the positions of the Data warehouse  and the Data marts. Data marts are directly loaded with the data from the   operational systems through the staging area.

•                   The data flow in the bottom up approach starts with extraction of data  from operational databases into the staging area where it is processed  and consolidated and then loaded into the ODS.

•         The data in the ODS is appended to or replaced by the fresh data being loaded. After the ODS is refreshed the current data is once again extracted into the staging area and processed to fit into the Data mart  structure. The data from the Data Mart, then is extracted to the staging area aggregated, summarized and so on and loaded into the Data Warehouse and made available to the end user for analysis.

• 
  

Facebook Commentbox