By- Gaurav Malhotra Business Intelligence - PowerPoint Presentation

Slide1

By- Gaurav Malhotra

Business Intelligence

Slide2

AgendaWhat BI ?Past of BIWhy BI ?

How BI ?OLAPOLTPDashboards & ScorecardsData Mining

Slide3

What is BI (Business Intelligence) ?

Organized, Analyzed DataB.I. (Processes, Tools & Technologies)Critical to Understand from every perspectiveBetter Decisions leads to ProfitsAnalyzing and

Re-arranging the data according to the relationships between the data items by knowing what data to collect and manage and in what context.Raw, Collected Data

Slide4

History of BI1989 & 1990s –

Mr. Howard Dresner (later a Gartner Group analyst) proposed "business intelligence" as an umbrella term to describe "concepts and methods to improve business decision making by using fact-based support systems“.1990s – Business Intelligence was widespread.1958 – IBM researcher Hans Peter Luhn used the term business intelligence.1960 – Birth of DSS (Decision Support Systems)Mid-1980s – Maturity in development of DSS. DSS assisted

decision making & planning.Late-1980s – DSS gave birth to Data Warehouses, EIS (Executive Information System), OLAP, & Business Intelligence

Slide5

Importance of BI

Raw DataCritical Questions:Net Profit Last Year ?Sales This Year ?How to increase Sales this year?Business Intelligence System

Raw Data

Critical Questions:

Net Profit Last Year ?Sales This Year ?How to increase Sales this year?

Customer Preferences,

N

ature

of

customers,

S

upply chains,

G

eographical influences, &

pricings

Exploring Data

Slide6

BI & ROI (Return on Investment)

B.I. Implementation Cost, Maintenance CostBusiness Intelligence System

Business Intelligence System

Proper Business Action, Proper Implementation

Slide7

BI Environment & Business Flow

Business ModelsData ModelsData SourcesETL

ToolsOLAP Analysis

Reporting ToolsBusiness Intelligence System

Target Data Warehouse

Data Marts

Skilled Business People

Slide8

Common Business Intelligence Environment

Business ModelingData ModelingRelational Databases

File SourcesOther Sources

Data SourcesData Marts

Data WarehouseOLAP Cubes

Extraction, Transformation & Loading

Slide9

1. Business ModelingWhat is the business all about ?, W

hat specific business problem it is intend to solve ?, &How the information flows from source to destination ?Business Modeling

Business ModelDiagrams

ArrowGraphics

Text LabelsBusiness Process Modeling:

Process Flow Modeling: Data Flow Modeling:

Business Modeling

Processes & their Relationships

Data Flow Diagram

Slide10

Sample of BPM

Slide11

Sample of PFM

Slide12

Sample DFD/DFM

Slide13

2. Data Modeling:Data Structure/Entities & their Relationships

Converting into Visual FormData ModelsConceptual Data Modeling:

Logical Data Modeling: Physical Data Modeling:

Enterprise Data Modeling: Relational Data Modeling:

Data Models

Visualizes overall database StructureEntities, attributes & their relationship

T

ables, columns, properties & relationship b/w them

E-R Model

Consolidates

the information across the

Enterprise

.

Slide14

CDM Example

Slide15

LDM Example

Slide16

P

DM Example

Slide17

EDM Example

Slide18

RDM Example

Slide19

3. Dimensional Modeling:Dimensional Model

fact table Dimension table

Various measures or facts like sales amount, loan amount etc.

Describes the particular entity like time, state etc.

Slide20

4. Star & Snowflake Schema:

Slide21

5. ETL Process & Data Warehouse:ETL Process:

Data Warehouse:Data Marts:Extracting the source data

Transforming that dataloading that data into a data warehouse

Cleansing, Profiling, data type conversion, validating for referential integrity, performing aggregation if needed, de normalization and normalization.Centralized repository where all the information for analysis is kept in an organization

.Subject oriented, basically a sub-set of data warehouse, built for the purpose of analyzing a particular line of business or department.

Slide22

6. Business Intelligence Reports:B.I. Tools

Cleansing & Transformation of DataB.I. Reports

Straight Tables & Pivot Tables

ChartsZero Footprint Technology

Slide23

What is OLAP?

Data Marts

Data WarehouseData Sources

ERPCRM

SCMOracle DB Server

MS Sql ServerOLAP Cube

List Reports

Formatted Reports

Dashboards

Slide24

OLAP Example

Slide25

What is OLTP ?Data Sources

ERPCRMSCM

Oracle DB ServerMS SQL Server

Data Marts

Data Warehouse

List Reports

Formatted Reports

Dashboards

OLTP Application

Slide26

OLTP System (Operational System)

OLAP System (Data Warehouse)Source of DataOperational data; Original source of the data.Consolidation data; From Various OLTP Databases.Purpose of dataControl & Run fundamental business tasks.Help with planning, problem solving, & decision support

What the DataOngoing business processesMulti-dimensional views of Various business activitiesInserts & UpdatesShort & fast inserts and updates initiated by end users

Periodic long-running batch jobs refresh the dataQueriesRelatively standardized and simple queries Returning relatively few recordsOften complex queries involving aggregationsProcessing SpeedTypically very fast

Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexesSpace RequirementsRelatively small if historical data is archivedLarger due to the existence of aggregation structures and history data; requires more indexes than OLTPDatabase Design

Highly normalized with many tablesTypically de-normalized with fewer tables; use of star and/or snowflake schemasBackup & RecoveryBackup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liabilityInstead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

Slide27

Business Intelligence Tools Directory:

Slide28

Business Intelligence Tool Guide:How to install and setup the BI software?

How to get license and training from BI software vendors?How to create users, administrators and assign privileges to users?How to connect to the different database servers from BI applications?How to understand and work on BI data models or universe?How to frame the select statement according to the business requirements? How to select the tables that have to be used in the report?How to select the columns that are required for reporting?How to write the join condition to join (inner join, outer join) different tables in select statement?How to write multiple select statements in a single report?How to write the filters (null, in, equal to, greater than) that are required after the where clause

?

Slide29

How to create dimensions and facts?How to drill up and drill down?How to set user prompts for user to enter values?

How to process the query and retrieve the results?How to work on results? How to modify field formats?How to sort data?How to create computer items like date functions, numeric and string functions?How to create pivots? How to add data?How to create totals?How to group data?How to create charts?How to create reports?

How to work on reporting body?How to work on report group headers?

Slide30

How to work on report header/footer?How to work on page header/footer?How to design the report layout?

How to use page breaks?How to schedule, monitor, modify, delete, and refresh a job(report)?How to write report design document, /report testing document, test reports and get user acceptance?How to distribute reports and results via email, printers, intranet server, and web?How to export and import data?How to track on scorecards, balancing scorecards, forecasting, key performance indicators and dashboards?

Slide31

Business Intelligence & Key Performance Indicators:List of measurements that are identified as critical factors in achieving the organizational goals or mission.

Requirements of a good KPI:Measurable: Should be quantifiable in terms of numbers.Reflect the organizational Goals: Should drive a business towards success.Actionable: Should help the managers to initiate some business action as a result of all the analysis and measures lead by KPI.

Slide32

What is a Dashboard in Business terms?Visual Representation of KPIs of interest

Slide33

Benefits of using Business Intelligence Dashboard: Quick

Conversion of Complex Corporate Data into a Meaningful Display of Charts, Graphs, Gauges and other formats concurrently.Allow the Managers to Drill-Down data to go Deeper into the Analysis

Clear Picture about how a company is performing in its Critical Areas.

Slide34

Scorecards:Mostly Scorecards are a Subset of Dashboard

ScorecardsDashboardCRM scorecards presents a quick picture of which strategy you need to concentrate to improve customer satisfaction but lacks any detail as to why are you struggling in bringing up maximum resolutions.CRM dashboards use lots of measures that give you data about how your team is operating, but provide little insight into progress towards your goal of reaching maximum resolutions.Its Managing, but not monitoring/measuring.

Its measuring/monitoring, but not managing.

Slide35

Scorecard

Dashboard

Slide36

What is Data Mining?

Data MartsData WarehouseCleansing Data

Profiling DataData type Conversion

Validating for Referential Integrity

Performing Aggregation if neededDe normalization and Normalization

Slide37

Data Mining Life Cycle:

Slide38

OLAP vs. Data Mining:

vs

.What has happened ?Why it has happened ?

Slide39

Thank you

Slide40

Any questions?

Slide41

Bite of QlikView