A Presentation on Business Intelligence June 10 2003

A Presentation on
Business Intelligence
June 10th 2003
by
Paul Balacky &
Richard Fayers
Topics

Introductions

Characteristics of a Business Intelligence Application

Demonstration

Design Issues
Introductions – Thorogood Associates Ltd
 Established 1987 as Independent Business Intelligence
Specialists
 45 people
 We are located in London, High Wycombe, Manchester
and Princeton USA
 Microsoft Gold Certified Partner for Business Intelligence
 15 years experience in the application of Business
Intelligence/OLAP technologies
 We partner with key players in the market
www.thorogood.com
Characteristics of BI
Business Intelligence
 The term Business Intelligence (BI) is relatively new but
the it is synonymous with a range of applications that have
been around for years;
– Decision support systems
– Executive Information Systems
– On-line Analytical Processing (E.F Codd early 90’s) or multidimensional modelling
 It is the conversion of data into information in such a way
that the business is able to analyse the information to gain
insight and take action
The BI Cycle
Business
Intelligence
MEASUREMENT
Source: Business Intelligence, Elizabeth Vitt
ACTION
ANALYSIS
INSIGHT
BI Questions

What happened?
– What were our total sales this month?

What’s happening?
–

Are our sales going up or down, trend analysis
Why?
– Why have sales gone down?

What will happen?
– Forecasting & What If Analysis

What do I want to happen?
– Planning & Targets
Source: Bill Baker, Microsoft
Where is Business Intelligence applied?
Operational Efficiency
Customer Interaction
 ERP Reporting
 Sales Analysis
 KPI Tracking
 Sales Forecasting
 Product Profitability
 Segmentation
 Risk Management
 Cross-selling
 Balanced Scorecard
 CRM Analytics
 Activity Based Costing
 Campaign Planning
 Global Sourcing
 Customer Profitability
 Logistics
OLTP v OLAP
 OLTP systems model processes
 OLAP focuses on output and user reporting and analysis
requirements
– Data warehouses support business decisions by
collecting, consolidating, and organizing data for
reporting and analysis with tools such as online
analytical processing (OLAP) and data mining.
(Microsoft)
 OLAP still requires a very formal approach
Business Intelligence Software
 Integration of
– OLAP multi-dimensional technology
– Relational database technology
– Web technology
 Scalability for warehousing
 Flexibility, performance and business views
 Web deployment
Major BI\OLAP Vendors
 Oracle 9i OLAP
 SAP BW
 Microsoft SQL Server 2000 & Analysis Services
 Hyperion Essbase\IBM
 Microstrategy
 Cognos
 Business Objects
State of BI at the present time
 Robust, scaleable, web deployable BI technologies are
available
 Problems are likely to lie in data complexity, process and
people
 Successful implementation demands very close working
between the business and the system providers
 Choosing products is as hard as ever
– There’s no such thing as a green field site (OLAP, Query &
Reporting, RDBMS, ETL, Data Mining)
– ERP vendors are offering BI
The BI market has been turned upside
down in the last 4 years
 Microsoft has entered the market with dramatic impact
 Oracle has lost momentum
 The products best able to work with Microsoft’s platform
were unknown 4 years ago
BI in Action
How Many Matches?
How Many Matches Now?
Concept of a Cube or
Pivot Table
Product – Chocolate
Date – May 2003
Region – South East
Measure – Sales
Date
Region
Product
How much Chocolate did we sell in the South East in May 2003?
Front-End Tools
Client
Server
Client
Server
Web
SQL
Web
MDX
SQL Server 2000
Relational
Database
Analysis
Services
DTS
Text
Excel
Informix
Access
Sybase
SQL
Server
Oracle
Design Considerations
Things to get right at design stage
 Scope of project
– Better to phase project than big bang
 Business unit buy-in
– Ownership within the BU and clear goals
 User Focus
– Management of user expectations becomes very important
Things to get right at design stage
 Source data
– Do we have access?
– Often data in disparate sources and not always accessible
– Is it at the same level
– Budget data may be formulated at a higher summary level than
actual data is sourced at
– Process
– How and when does the data get into the Warehouse?
– What level of data cleansing & transformation will be required
– Who is responsible?
Things to get right at design stage
 Source data
– Are we able to match outputs to inputs
– Merging and matching of data sources
– Requirement for company wide data standards and definitions
– Are there common keys?
– Hierarchy movements over time
– the need to restate or retain historic view?
– Timeliness of data
– Data volumes
– Handling of missing values and relationships
Things to get right at design stage
 Can you deliver the user/business requirements with the
tools/skills available
– Some things that look easy are sometimes not
– Dimension changes
– Things that do not seem important to the developer are
important to the business user
– Format
– Performance
– Some things will be slow because they are slow
– Manage expectations
– Product limitations
Things to get right at design stage
 Reporting vs Analysis
– They may seem the same but they are not
– Different tools
– Different approach
– Different audience
BI Design Parameters
 Cubes
– Number of cubes – possibly defined by business functions or
security
– Number of dimensions per cube, shared or private
– Partitions relating to data volumes and update speeds (cube
processing times)
– Virtual cubes – cross functional analysis
– Data storage options
BI Design Parameters
 Dimensions
– Types of hierarchies - multiple, ragged, parent\child,
balanced\unbalanced
– Size, number of members
– Member properties and how these could be used (attributes)
– Number of levels, children within each level
– Hierarchy changes over time
– Reporting views, scenarios
BI Design Parameters
 Time Dimension
– Alternative time hierarchies – calendar, financial
– 13 period year – weeks to period
– Number of levels
BI Design Parameters
 Timeliness of Data
– Real-time
– Next day
– Weekly reviews (possible weekend to process)
– Monthly reviews (month end processing)
BI Design Parameters
 Measures
– Methods of aggregation
– Data entering cubes at differing levels required for
comparisons
– Custom rollups
– Non additive data
– Precision, format
BI Design Parameters
 Calculated Measures
– Time series calculations
– SQL vs OLAP calculations (pre cube build vs post cube
build)
– Calculated cells
– Nature of equations required to derive the calculated
measures
– Currency exchange rates
– Distributed processing opportunities (server calcs vs client
side calcs)
– Application of MDX
BI Design Parameters
 Write-Back requirements
– Allocations\break back requirements, level of data entry
– Audit log
– Validation
BI Design Parameters
 Output requirements
–
–
–
–
–
–
–
–
–
–
User report definitions – format, layout, precision
Types of adhoc analysis
Actions
Requirements for printed output
Quantitative vs Qualitative data output
Browser\Office delivery
OLAP database drill-through to SQL Server
Number of users
Report maintainability
Security
BI Design Parameters
 Security
– Cube
– Dimension
– Cell level
To consider when building BI applications..
 Users can fail to realise how much info they requested –
leads to poor perceived performance
 Complexity due to a large number of dimensions – users
don’t understand the model/numbers
 Hard to test because they are conceptually complex
 Performance vs storage – consider
MOLAP/HOLAP/ROLAP, on-the fly versus pre-aggregated
data
There is a strong case for a BI
strategy
 BI can drive significant value
 It is an agile technology
– crosses functional boundaries
– crosses organisational boundaries
– Implementation can involve many stakeholders
 Tactical BI applications may deliver significant value (and
prove BI’s worth) …
 In a post boom business climate, BI offers a pragmatic
way of delivering high return in the short term without
major upheaval