A Presentation on Business Intelligence June 10 th 2003 by Paul Balacky & Richard Fayers

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 90’s) 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? • 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

8. 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 Where is Business Intelligence applied? Operational Efficiency Customer Interaction

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 • There’s 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 Microsoft’s 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. Excel SQL Server Text Informix Access Sybase Oracle Front-End Tools Client Server Client Server Web Web SQL MDX SQL Server 2000 Relational Database Analysis Services 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 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

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