Data Warehousing & Business Intelligence

1. Data Warehousing &Business Intelligence 30 March 2007 Brett Hanes

2. Agenda • Overview • Data Warehousing • Definition & Purpose • Varieties • General Architecture • Modeling • More Information • Business Intelligence • Definition & Purpose • Common Components • How It All Works Together • Why Is It Useful? • How To Convince The Business • Summary

3. Data Warehousing (DW) • Definition • A subject-oriented, integrated & non-volatile database updated on a typically rhythmic cycle from an enterprise’s various transaction databases. • Purpose • Accumulate data from disparate data sources for querying purposes • Separate reporting and analysis operations from transaction systems to maximize the performance of both Commonly very large repositories that house historical data

4. Common Data Warehouse Components • Staging Area • A preparatory repository where transaction data can be transformed for use in the data warehouse • Data Mart • Traditional dimensionally modeled set of dimension and fact tables • Per Kimball, a data warehouse is the union of a set of data marts • Operational Data Store (ODS) • Modeled to support near real-time reporting needs • Contains traits of both relational and dimensional modeling techniques

5. Data Warehouse Modeling • Data warehouses typically use a denormalized method called dimensional modeling made up of the following components: • Dimension • An entity defined in its entirety with a single primary key • Examples: Customer, Product, Sales Force, Calendar • Fact • Details (often numerical) regarding a set of dimensions • Example: Order Details

6. 2 - Data transfer from transaction system to data warehouse via Extract-Transform-Load (ETL) Tool (i.e. Informatica) 3 - Data Output via Business Intelligence Tool (i.e. Cognos, Business Objects, Hyperion) 1 - Data Input via Applications to transaction databases Separation of Transactions and Reporting Improves Performance and Enhances Capabilities Data Flow from Transaction to Warehouse Complex Structure Necessary for Accurate Transactions Simplified Structure Necessary for Fast, Powerful Reports

7. Word is they don’t really get along Learning More About Data Warehousing • Pre-eminent Data Warehousing Minds • Bill Inmon -> Normalization • Building the Data Warehouse • Corporate Information Factory • Ralph Kimball -> Dimensional • The Data Warehouse Lifecycle Toolkit • The Data Warehouse Toolkit

8. Business Intelligence Software • Definition • A set of tools that allow users to access enterprise data via reports, Online Analytical Processing (OLAP) cubes, graphs/charts, ad-hoc queries and dashboards • Purpose • Allow users to view the data from all levels of the enterprise • Provide users with information necessary to make timely, well-informed business decisions The tools must be easy for the end user to understand and manipulate

9. Some Components In The BI Toolkit • Reports (Example) • Commonly needed data can be structured in a set of canned reports made available to large numbers of users • Flexibility can be given to users through ad-hoc querying and filters • Cubes (Example) • Multi-dimensional, allowing the user the view the data from multiple angles • Interactive, giving the user the ability to change what is viewable on the fly

10. Some Components In The BI Toolkit • Charts & Graphs (Example) • Graphical Representation of data • Commonly used in presentations and statistical analysis • Dashboards (Example) • Actively updating graphical displays that provides business users with updates on key metrics • Some dashboards provide drill through capability, allowing users to start with summary data and dive in to the details

11. Disparate Data Sources Extract Transform Load Single Reporting Repository Real-time Dashboards Data Input OLTP AIMSPC Static and Ad-hoc Reporting OLTP TIMS DW RECBASS OLTP Graphical Data Analysis ATRRS Other Possible Data Sources RATSS RFMSS How It All Works Together

12. ODS The data transmitted from the engine in flight can alert a service team of an engine component in need of repair so they can meet the plane at the gate. DW Engineers can analyze the data to find ways of designing engine components with longer life spans. Why Is It Useful?

13. In Other Words….You Save $$$$$$$$$!!!!! Learn to speak the language of the business users…Understand what is important to them Convincing The Business • Consider the Previous Example • Having a team waiting at the gate to service an engine reduces flight delays • Engineering analysis helps create higher quality engines that: • Requires less servicing • Stay on wing longer

14. Keys To Success • Sponsorship • High level endorsement is essential to ensuring you have the authority to drive the effort • Funding • You have to spend money to save/make money • Time • This can be a years long effort to implement • Maintenance is ever-present • Central Governance • Without strict governance over components of your enterprise data warehouse, you risk stove piping

15. Summary • Data is Key • Whether coming in or going out, data is the foundation of all business applications and should be structured to properly meet the need • Solutions are Complex • There are many components to a good BI strategy…and they all have to work • Diligence Required • Data will change • Technology will change • Be assured…user requirements will change

16. Questions?

17. Appendix

18. Report Example

19. Cube Example Dimensions and facts can be dragged and dropped on the to display to view the data in different ways

20. Chart/Graph Example

21. Dashboard Example