A Presentation on Business Intelligence June 10th 2003

1
A Presentation on Business Intelligence June 10th
2003 by Paul Balacky Richard Fayers
2
Topics

• Introductions
• Characteristics of a Business Intelligence
  Application
• Demonstration
• Design Issues

3
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
4
Characteristics of BI
5
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
  90s) or multi-dimensional 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

6
The BI Cycle
Source Business Intelligence, Elizabeth Vitt
7
BI Questions

• What happened?
• What were our total sales this month?
• Whats 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
8
Where is Business Intelligence applied?
Operational Efficiency
Customer Interaction

• ERP Reporting
• KPI Tracking
• Product Profitability
• Risk Management
• Balanced Scorecard
• Activity Based Costing
• Global Sourcing
• Logistics

• Sales Analysis
• Sales Forecasting
• Segmentation
• Cross-selling
• CRM Analytics
• Campaign Planning
• Customer Profitability

9
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

10
Business Intelligence Software

• Integration of
• OLAP multi-dimensional technology
• Relational database technology
• Web technology
• Scalability for warehousing
• Flexibility, performance and business views
• Web deployment

11
Major BI\OLAP Vendors

• Oracle 9i OLAP
• SAP BW
• Microsoft SQL Server 2000 Analysis Services
• Hyperion Essbase\IBM
• Microstrategy
• Cognos
• Business Objects

12
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
• Theres no such thing as a green field site
  (OLAP, Query Reporting, RDBMS, ETL, Data
  Mining)
• ERP vendors are offering BI

13
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 Microsofts
  platform were unknown 4 years ago

14
BI in Action
15
How Many Matches?
16
How Many Matches Now?
17
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?
18
Front-End Tools
Client Server
Client Server
Web
Web
MDX
SQL
SQL Server 2000
Analysis Services
Relational Database
DTS
19
Design Considerations
20
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

21
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?

22
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

23
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

24
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

25
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

26
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

27
BI Design Parameters

• Time Dimension
• Alternative time hierarchies calendar,
  financial
• 13 period year weeks to period
• Number of levels

28
BI Design Parameters

• Timeliness of Data
• Real-time
• Next day
• Weekly reviews (possible weekend to process)
• Monthly reviews (month end processing)

29
BI Design Parameters

• Measures
• Methods of aggregation
• Data entering cubes at differing levels required
  for comparisons
• Custom rollups
• Non additive data
• Precision, format

30
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

31
BI Design Parameters

• Write-Back requirements
• Allocations\break back requirements, level of
  data entry
• Audit log
• Validation

32
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

33
BI Design Parameters

• Security
• Cube
• Dimension
• Cell level

34
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 dont 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

35
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 BIs worth)
• In a post boom business climate, BI offers a
  pragmatic way of delivering high return in the
  short term without major upheaval

36
(No Transcript)