BA Participation on Reporting Projects: A Panel Presentation

1. BA Participation on Reporting Projects: A Panel Presentation November 1st, 2007 5:30pm Registration and Networking 6:00pm Panel Presentation 7:00pm Questions and Discussion Hosted by: Consulting Matters and Solutia Consulting Inc. Please do not distribute this information

2. Panel Agenda • Introduce Panel Participants • Steve Elkins • Lori Buschena and Lyle Orr • Tad Salyards • Panel Participant Experiences • Reporting project experiences • Approach to requirements definition • Involvement through lifecycle of project • Best practices • Lessons learned • Questions and Discussion

3. Panel Bio’s • Steve Elkins Steve is an Information Architecture consultant with over twenty years of experience and dozens of Business Intelligence projects under his belt in a wide range of industries including Travel and Hospitality, Financial Services, Education and Health Care. He is currently working at Boundary Medical, a healthcare software company in Eden Prairie. ElkinsEcon@aol.com or 612-578-2103 • Lori BuschenaLori works at Long Term Care Group, Inc. as a Project Manager in the Data Services department. Prior to becoming the PM, Lori was a Business Analyst. Lori has 3 years experience in working for Data Services. • Lyle Orr With more than 12 years of IT experience, Lyle has worked with LTCG in programming, manager and analyst roles for more than 7 years. Prior to working for LTCG, Lyle worked for the State of MN in Health Care Operations on their data warehouse both as a user and trainer. Currently, Lyle leads the Data Services Business Analyst team and supports all areas of the company as a Business Liaison. • Tad Salyards Tad works at Target Technology Services (TTS) in Minneapolis as a Business Analyst for Target India. He has 8 years of total IT experience working as a developer, Business Analyst and Project Manager.

4. Steve Elkins Two Types of Reporting • Operational Reporting • Queries are of the form: “Show me a list of items meeting these criteria”, e.g., “Show me a list of customers whose accounts are more than 60 days past due.” • Analytical Reporting • Queries are of the form: “Show me such-and-such a metric summarized by this, by that, by the other thing”, e.g., “Show me sales revenue summarized by month, by product by store.”

5. Steve Elkins Operational Reporting • Is related to the tactical management of a process (and may appear as a step in a use-case.) • Generally requires “real-time” operational data • Can be run from the production operational database (but are best run from a copy of it). • Can be run from a “normalized” relational database design without an undue performance penalty (to the person running the report) • Often provided as “canned reports” by packaged application vendors

6. Steve Elkins Analytic Reporting • Is related to strategic analysis • Generally requires regular “snapshots” of data for analysis across time • Should never be run from a production operational database • Analytical query response times will be unacceptable • Severe performance degradation of operational application can result • The form of the query requires a “Dimensional” approach to database design • There is no meaningful role for use-cases

7. Steve Elkins Dimensional Modeling Metaphors • 1 The Rubic’s Cube • Depicts Dimensional Structure of Data (the “Bys”) Minnesota Wisconsin Geography Iowa March Time February Cars January Trucks SUVs Product

8. Steve Elkins Dimensional Modeling Metaphors • 2 The Family Tree • Depicts Dimensional Hierarchies (e.g., day -> month -> year)

9. Steve Elkins Implementation Approaches • Multi-dimensional Databases (MOLAP), e.g., Essbase, Cognos PowerPlay, MS Analysis Services • Relational OLAP engines (ROLAP), e.g., MicroStrategy, Mondrian on “Star” or “Snowflake” schema relational databases • Hybrid (MOLAP on ROLAP)

10. Steve Elkins Documenting Dimensional Designs • The “Sunrise Diagram”

11. Steve Elkins Documenting Dimensional Designs • The “Sunrise Diagram” • Depicts Metrics (the “Such-and-Suches”) • Depicts Dimensional Hierarchies (the “Bys”) • Can be used to create logical data model • Can be constructed from • Interviews with end user SMEs • Report specs (e.g., wireframes) • Validate against existing reports to be replaced • Don’t obsess about report formatting – spreadsheet layouts are fine

12. Steve Elkins Business Metadata • Plain English Names, Definitions and Business Rules for all data elements that will be used in reporting • Must be collected from and validated by the business community • Must be collected during requirements gathering and logical design (or it will never be collected) • Can be collected into spreadsheets for upload into data modeling tools and metadata repositories • If you don’t do this, your reporting project will fail – period

13. Steve Elkins Reporting w/o Business Metadata

14. Steve Elkins Reporting with Business Metadata

15. Steve Elkins Common Pitfalls • Postponing the collection of business metadata until “a later phase of the project” • Spending too much time on report formatting • If you get the database design right, the report composition will be easy. • Your BI software may constrain your formatting options, anyway. • You don’t have to cram all of the information onto one report – it’s easy to create several, more readable, reports quickly. • Thinking that you’re going to nail the requirements on the first iteration • Change Management: What happens when your SME realizes that he’s going to be out of a job.

16. Lori Buschena & Lyle Orr The LTCG Way • BA experiences on reporting projects – • Types of projects – Reports, data feeds, implementations, conversions • Size of teams – varies, depending on the project • Number of reports – in the hundreds 2005 – 357 2006 – 425 2007 – 200+ so far • Internal vs. external audience – both Key point - We need to adjust definitions and terms for both. • Type of data - Includes, but not limited to: Underwriting data, Claims data, Demographic data, HIPAA sensitive, Attorney General/DOI

17. Lori Buschena & Lyle Orr The LTCG Way • BA approach to requirements on reporting projects – • Tools to collect reporting requirements The key to collecting reporting requirements is a clear, concise and uniform reporting document. • Are there things that you define for reports that you don't for functional/ system requirements? Many! For system/functional requirements, you are concerned more with “I need the system to do this.” For reports or feeds, we need to document the specific fields, format, layout, etc. • At what point in the process does this happen versus when it should happen? Most of what we gather is in the requirements elicitation phase, where we ask all the pertinent information needed to code the report or feed. Transmission is a typical gap. • What questions are really important to ask? Understanding HOW the data will be used is key. Will this be compared to something else we send? What kind of system are we feeding? What business does that system process. What is the format, fields requested, selection criteria, population, etc. The list is endless. • How are the report look and feel, performance and user access requirements captured? We generally mock up a same report and place it right in the RSS document.

18. Lori Buschena & Lyle Orr The LTCG Way • BA involvement through lifecycle of report delivery • How should the transition to design/development/test flow and how may that differ from other development lifecycles? • The BA starts the cycle, but it doesn’t end when requirements are signed off! • RUP may inflate the BA time. • What reporting solutions have you implemented? • Our company has fed systems of all shapes, sizes, platforms and ages. • Currently working on implementing a data warehouse. • What if you are constrained by the current reporting system functionality? • We may request a system enhancement • Need to derive information • Develop new platforms or environments

19. Lori Buschena & Lyle Orr The LTCG Way • Best practices – • What works best and how have you educated yourself (books, training, websites, etc.)? • Intuition • Knowing what questions to ask • Seeing new questions in the answers • Excellent problem solving skills • How can you do it better next time? • What about the need to educate the users on their data and the benefits of leveraging data that’s collected? • Data education is a slow process, with so much data available. • Key is having a BA who can truly analyze.

20. Lori Buschena & Lyle Orr The LTCG Way • Lessons learned – • What challenges have you overcome on these types of projects and how have/would you approach things different? • A BA needs to understand the purpose of the report/feed in order to ask the right questions. • Also important to understand what data is available and what isn’t. • Also important to understand the data itself.

21. Tad Salyards Student Retention and Matriculation • Internal project at online university with goal of streamlining inconsistencies in how student matriculation and retention were measured and reported Core project team consisted of 6 SMEs within the Academic Records and Finance departments plus 2 programmers for data mart development Requirements phase lasted 2+ months and involved 40+ hours of meetings to flesh out core business definitions

22. Tad Salyards Student Retention and Matriculation • Challenges • Core business definitions were inferred and had never been written down, agreed to, or managed • Resulted in operational inconsistencies, data corruption and multiple sources of truth • Traditional academic measurements did not meet needs of Finance • Academics measured educational outcomes in cohorts (groups of students) • Finance measurements did not meet regulatory needs of academics • Finance measured income streams in dollars

23. Tad Salyards SAS Clearance Pricing • Project involved the development and testing of a dozen SAS libraries for use by Target’s Clearance Pricing analysts • Core team consisted of 2 developers and 4 SMEs • Disk use exceeded 10+ terabytes • Data was used to make operational decisions that resulted in revenue improvements measured in millions of dollars

24. Tad Salyards SAS Clearance Pricing • Challenges • Inherited project mid-stream (when it was already one year late and running out of money) • Data was complex and required a large amount of domain knowledge • Storage requirements kept growing (SAN team hated this project)

25. Tad Salyards Requirements Approach • Requirements approach should be dictated almost entirely by the nature of your SMEs (one size does not fit all) Student Retention and Matriculation • Non-technical SMEs • Focused on legalistic definitions that were clear and precise • Challenged assumptions and forced clients to verbalize the “obvious” • Used pictures and diagrams to illustrate complex concepts • Developers were empowered to create a solution that would meet the needs of the business

26. Tad Salyards Requirements Approach SAS Clearance Pricing • Highly technical SMEs • Requirements were highly technical and included data source table and column names, calculation details and hierarchies • Requirements prioritization and cost benefit analysis was very important • Requirements had to be fluid and flexible • “How can we get the best bang for our buck?” • Finite time, money and disk space were primary drivers of project priorities • Developers were tightly constrained

27. Tad Salyards Role of BA in Report Delivery • Facilitate the responsible use of company resources and ensure that reports will have impact • Cut through IT bureaucracy to ensure speedy delivery • Steer clients towards a value-added reporting culture and fight the tendency to create reports that merely provide “interesting” information • “What decisions will you make with this report? What actions will it drive?” • There is no definitive beginning or end in the BA role as in traditional software development projects

28. Tad Salyards Best Practices • ITERATE! ITERATE! ITERATE! • Waterfall methodologies work well if you’re managing a team of robots (or IT professionals); mere mortals need tangible deliverables to spur on new ideas • Speed is of the utmost importance…change is imminent • Reporting nirvana has simplicity at its core, not complex “dashboards” for F16 pilots • Don’t dismiss the client’s ideas; suggest different ways to accomplish their business goals and show them mock-ups

29. Tad Salyards Lessons Learned • Different tools bring a confusing array of terms and technologies to the table, but the fundamentals of good BA work do not change • Don’t get too hung up on the details • Scope is almost always greater than your budget or timeframe • Build strong relationships with your client and promote good decisions with transparent budgets, risks and issues • Learn to let go. The client probably knows best.

30. QUESTIONS?For TCBAC information or feedback please contact TCBAC@solutiaconsulting.comor refer to our website atwww.TCBAC.orgFuture TCBAC Meetings:2008 - TBD