What You Should Know About Business Intelligence – This Isn’t the Same Profession When You Started

1. What You Should Know About Business Intelligence – This Isn’t the Same Profession When You Started Lewis F. McLain, Jr. CityBase.Net, Inc. Finance Roundtable NCTCOG March 9, 2012 http://technet.microsoft.com/en-us/edge/the-history-of-business-intelligence.aspx www.citybase.net/downloads/gfoatroundtable.ppt

2. Disclaimers & Credits • Slides that have the Collin College and KnowledgeFlight logos are the property of Tim Smith, a Collin College Instructor and Co-Founder of KnowledgeFlight Consulting. They are being used with permission. • Knowledge Management and Business Intelligence are terms that incorporate a huge array of topics, concepts and practices. This presentation is intended to just introduce a small portion of the subjects. • Hey, if you are a small shop and your accounting system is QuickBooks and your BI tool is constrained to Excel (bless your heart!), you can still benefit from this presentation.

3. How to Make A Lot of Money • Charge people for using the word “Sustainability” if they can’t be specific about what they mean. • Charge people who use the words “Smart Growth” if they can explain it without using the word “Smart.” • Charge people who use the words “Business Intelligence” and can’t explain the process.

4. The BI Train is Leaving the StationAre You on IT?

5. An Assessment of the Accounting Profession • Record. (A+) • Classify. (A-) • Summarize. (A on required, D on elective) • Analyze. (D)

6. New York Times Best Seller List

7. What’s Happening Here? • Legacy OLTP Systems are Loaded with Years of Unanalyzed Data. • Incredibly Powerful User-Oriented Analytical Packages are Rushing to Us at Reasonable Prices. • Business Oriented Council Members are Using BI Tools in Their Jobs. Many are being provided by iPads by the City. • You are at Risk for Others Telling You about Your Business. • College Students are Graduating with New Talents, Skills, Interests, Expectations. • Some IT Departments and Some Fiscal Departments are Learning about Collaboration and Data Knowledge Management. • Every Leading Software Package of the Future has or will have BI Features. • You May Think You Can’t Afford the Time, Effort & Money for BI, but You May Not be Able to Afford to Ignore IT.

9. A Big Part of the Challenge – the Lingo • BI – Business Intelligence. • OLTP – Online Transaction Processing. • OLAP – Online Application (Analytical) Processing. • SQL – Structured Query Language. • SQL Server – Software Product to Store & Retrieve Data. • DW – Data Warehouse. • ETL – Extract, Transform and Load. • Flat File – Text, Comma Separated Values (CSV). • OLE DB – Object Linking & Embedding Database (SQL, Access). • ODBC Open Database Connectivity. • DBMS – Database Management Systems.

10. A Big Part of the Challenge – the Lingo • SSMS – SQL Server Management Services. • SSIS – SQL Server Integration Services. • SSAS – SQL Server Analytical Services. • SSRS – SQL Server Reporting Services. • SME – Subject Matter Expert. • ODS – Operational Data Store. • Schema – Data/Table Structure & Relationships. • Metadata – Data About (Descriptive, Structural & Administrative) Data. • BICC – Business Intelligence Competency Center. • Grain – The Level of Detail (Granularity).

11. How Many Data Sources Do You Have? • Core Accounting System. • Utility Billing System. • Municipal Court System. • Project Accounting Systems. • Human Resource Systems. • Recreation Program Systems. • Library Systems. • Police Dispatch Systems. • Dozens More. • But What About Those (hundreds, thousands?) Side Excel Spreadsheets and Access Databases that Only One Person Understands?

14. Business Analyst • Information Ombudsperson. • Is a communicator. Especially in written forms. Presentations. • Willing to learn how departments work. • Knows what managers want, especially the city manager. • Knows what the council wants. • Knows the core system tables and relationships. • Knows how to extract from those systems. • Knows how to put together management reports.

15. Envision the End Result (Dashboard) andWork It In Reverse • This data is wrong. • This data is too old to be meaningful. • This data needs to be consolidated. • This data needs to be broken down. • This data needs better labeling. • This data is too brief. • This data is too summarized. • This data is too detailed. • This data is not mine. • This data is incomplete. • This data should not be shown outside this department. • This data is pretty, but I still don’t know what it is telling me. • Who thought of this stupid BI idea?

16. Multi-Dimensional Cube

17. Municipal Court Revenue Dimensions • Statutory Changes. • Police Department Leadership. • Police Officer Staffing and Efforts. • Weather Conditions. • Day of the Week. • Time of Day. • Geographical Considerations. • Municipal Court Staffing. • Municipal Court Judge Decisions. • Warrant Officer Staffing and Effectiveness. • Fine Rate Structure. • = Revenue • “The trend is your friend … until it bends at the end.”

18. Hold The (BI) Train!

19. ETL Components • Extract from data sources. • Look for missing data. • Look for duplicates. • Error corrections that did not get to history files. • Deal with abbreviations, periods, commas, spaces. • Standard spelling of names. • Standard IDs for employees, vendors, etc. • Pivot or unpivot data. • Index, sort. • Deal with historical changes in systems. • Deal with historical reorganizations. • Load into data warehouse.

20. BICC • What is a Business Intelligence Competency Center • Is a cross-functional entity for organizations that embrace BI as a strategic need and require a greater return on their investments and continued success of their BI plans. • According to Gartner, the BICC consists of 4 main pillars: • User Training (communicating the technology through varying mediums) • Data Stewards (data ownership, data cleansing) • Meta Data (identifying the source and impact of changes to data) • Advanced Analysis (why it happened, what might happen, take BI to the next level)

21. The success of a BI initiative (and/or project) needs to be measured and compared against set objectives. • BI projects require repeatable processes, common terms, planning tools, templates, and best practices. • Measurements should be established to track and monitor the value of the BICC and its performance reported back to the stakeholders of the BI strategy. • The BICC needs to be a cost center generating value with its associates being part of project teams. • The stakeholders should set a utilization number for all members of the BICC team.

22. Most organizations think of BI in terms of constructing a data warehouse and deliver reporting. • But we know we need to support: • Traditional Ad-Hoc queries • Traditional operational reports • Online Analytical Programming (OLAP) • Scorecards/Dashboards • Data Mining • Forecasting and Planning • Statistical Analysis • Predictive Analytics • Business Rules Based Alerts • Knowledge Management

23. As a formal entity within the organization, the BICC is mandated to provide value in the following areas within the 4 main pillars: • Provide BI/DW expertise (all pillars). • Generate new BI/DW projects (advanced analysis). • Assure that all projects include a BI/DW component (meta data, advanced analysis). • Market the BI/DW role within the company (user training). • Be the advocates of BI/DW (user training, data stewards). • Ensure on-going analysis of existing solutions within the enterprise (all pillars).

24. The diagram below depicts the ideal roles and responsibilities associated with the BICC function (Source: SAS Institute).

25. Skills and Knowledge • Project Management • Business Analysis (requirements, metric definition, measurement) • IT Delivery (report and scorecard design, ETL mapping) • Business Knowledge • BI and DW Technology & Architecture • Data Modeling (Schemas), Metadata • Bus Diagram • Analytical Experience • Education & Training • BI/DW mentorship & promotion

27. Roles, Resources, and Knowledge • Project Management. • Business Analysis (requirements, metric definition, measurement). • IT Delivery (report and scorecard design, ETL mapping). • Business Knowledge. • BI and DW Technology & Architecture. • Data Modeling (Star Schemas), Metadata. • Analytical Experience. • Education & Training. • BI/DW mentorship & promotion.

28. Delivery also includes post-project measurement. • The BICC must also be responsible for the adoption program and a put in place procedures to capture varying measurements related to project acceptance. • These can be automatic through auditing features and reports whereas other techniques may involve user canvassing. • Measurements must be captured and reported to the stakeholders.

29. A useful delivery method is a BICC scorecard capturing the following items: • Projects Success Factors (on time, on budget, level of engagement – hours and resources, PMO success factors). • Report Usage (usage, refreshes, speed of execution) • Data Usage (dormancy). • Application Usage (log-on counts). • Training program statistics (how many people trained) • Data Volumes (counts, daily loads). • Internal Seminars. • Knowledge Management Contributions (Wiki’s, Webcasts).

31. Project Charter • As with writing a project charter, the following areas must be defined for the BICC ; • Executive Summary – overview. • Mission – purpose of the BICC. • Vision - future expected state due to the existence of the BICC. • Stakeholders – reporting structure. • Mandate – governance of BI/DW projects, stewardship, metadata capture. • Deliverables – specific needs from projects to the BICC. • Approvals – sign-off authority on the deliverables, projects. • Appendices (if necessary) – additional information pertaining to the BICC (various artifacts).

32. Training • A full training program should include; • A calendar of conferences and seminars. • A calendar of classes for technologies including SQL Server, Integration Services, Data Quality, Metadata tools, Data Modeling tools, Performance Point Server Monitor and Analytics. • A matrix of which users should attend which technology • List of books, magazines, and web sites that deliver BI/DW knowledge. • Library of white papers, books, webinars, webcasts. • List of local user groups, brown bag sessions, invited speakers.

34. Artifacts • The initial set of BICC artifacts must be created to include; • Checklist to identify whether a SOO contains a BI/DW component • Project template identifying tasks and general durations for those projects requiring an ETL and/or reporting need • Template and Wireframes for reports and sites • Metadata capture template • Data Stewardship template and a list of data owners • Requirements gathering template • Data Modeling guidelines including naming standards, techniques for star schema creation, existing conformed dimensions list, tools for modeling, etc • Reports & ETL testing scripts and guidelines • Various ancillary documents such as ETL data maps, glossaries, security needs, scorecard needs, etc

35. Data Governance and Compliance • The formal orchestration of people, processes and technology to enable an organization to leverage data as an enterprise asset. • Focused on managing the quality, consistency, usability, security, and availability of information. • Closely linked to the notions of data ownership and stewardship. • Data Owners – individuals that often belong to a business rather than technology division and are in a position to obtain, create and have significant control over the data • Data Stewards – individuals who ensure that adequate, agreed-upon quality metrics are maintained continuously and that there are data quality improvement programs within the organization.

36. Data Governance and Compliance • Regulations for compliance and intelligent data reporting • Sarbanes-Oxley Act, HIPAA, Patriot Act, Basel II, FDA CFR 11, Gramm-Leach-Biley Act. • Increased consequences for non-compliance . • Risk Management is a necessity. • Secure data. • Detecting problems in a tight timeframe with ability to respond quickly. • Timely delivery of information. • Garbage in = Garbage out is still true. • Expanding global markets. • Data is mobile. • Data has an external market value. • Demand for Single Version of Truth.

37. Data Governance and Compliance • Streamlined Processes. • Transparency and Visibility. • Integrated Systems. • Secure Data. • Lower Cost of Ownership. • Efficient and Effective Use of Resources.

38. Data Governance Principles Integrity - Data Governance participants will practice integrity with their dealings with each other; they will be truthful and forthcoming when discussing drivers, constraints, options, and impacts for data-related decisions. Transparency - Data Governance and Stewardship processes will exhibit transparency; it should be clear to all participants and auditors how and when data-related decisions and controls were introduced into the processes. Audit-ability - Data-related decisions, processes, and controls subject to Data Governance will be auditable; they will be accompanied by documentation to support compliance-based and operational auditing requirements. Accountability - Data Governance will define accountabilities for cross-functional data-related decisions, processes, and controls. Stewardship - Data Governance will define accountabilities for stewardship activities that are the responsibilities of individual contributors, as well as accountabilities for groups of Data Stewards Checks-and-Balances - Data Governance will define accountabilities in a manner that introduces checks-and-balances between business and technology teams as well as between those who create/collect information, those who manage it, those who use it, and those who introduce standards and compliance requirements. Standardization - Data Governance will introduce and support standardization of enterprise data. Change Management - Data Governance will support proactive and reactive Change Management activities for reference data values and the structure/use of master data and metadata *Per the Data Governance Institute

39. Data Governance Strategies • Design and refine “future state” business processes with both IT and business leadership collaborating on it • Determine value of data and calculate probability for risk associated • What is it worth; Where is it; How is it used; Where and when to integrate it and federate it • Risk will determine how much should be spent on controls and protection • Define a data governance process • Initial data load • Data refinement • Standardization • Aggregation • Elimination of duplicate records • Creation of linking and matching keys • Design, select and implement a data management and data delivery technology suite • Defining your Data Architecture • Role of SOA and integration • Measuring and selecting data cleansing, auditing, etc. tools

40. Data Governance Strategies • Enable audit-ability and accountability for all data under management that is in scope for data governance strategy • Provides verifiable records of the data access activities • Serves as an invaluable tool to help achieve compliance with current and emerging regulations • Creation and empowerment of roles…assign data owners and data stewards • Don’t underestimate efforts needed for data quality • Data preparation, validation, extraction, transformation and loading…could take as much as 75% of the data warehouse development effort • Over 50% of these activities could be spent on cleansing and standardizing the data • Need complete and clear semantic definitions of what the data is supposed to represent, in what form and with what kind of timeliness requirements…typically stored in a metadata repository • Data quality tools and technologies • Assess level of maturity for data governance categories and then build plan and vision for moving to higher levels.

41. Data Governance Strategies

42. Data Governance Goals • Enable better decision-making • Reduce operational friction • Protect the needs of data stakeholders • Train management and staff to adopt common approaches to data issues • Build standard, repeatable processes • Reduce costs and increase effectiveness through coordination of efforts • Ensure transparency of processes

43. Data Methodologies Master Data Management (MDM) The authoritative, reliable foundation for data used across many applications and constituencies with the goal to provide a single view of the truth no matter where it lies. Customer Data Integration (CDI) Processes and technologies for recognizing a customer and its relationships at any touch-point while aggregating, managing and harmonizing accurate, up-to- date knowledge about that customer to deliver it ‘just in time’ in an actionable form to touch-points. Master Data Integration (MDI) Process for harmonizing core business information across heterogeneous sources, augmenting the system of record with rich content by cleansing, standardizing and matching information to provide high data quality in support of a master data management initiative

44. Steering Committee • The Steering Committee must assume and/or delegate the following responsibilities: • Formation and Continuance • Develop, maintain and follow a formal group charter and bylaws • Identify Data and Application Integration Opportunities • Review whatever existing BI components are to ensure that they continue to be optimal. This will include the monitoring and analysis of activity so that bottlenecks are eliminated or reduced and opportunities for enhancement can be identified and acted upon • Listen to the “voice of the user” by receiving and processing change requests from those affected by implemented BI solutions • Receive and process change requests from external users, such as partners, customers, etc.

45. Steering Committee • Define Business Intelligence Opportunities • Understand opportunity-specific dependencies, timing, alternatives and risks • Define organizational/operational costs and impacts • Combine opportunities where appropriate • Detail the impact of external dependencies • Define solution alternatives and estimate implementation costs for each • Ensure that each solution alternative complies with BICC principles and designs • Create cost/benefit scenario(s) • Prioritize Opportunities • Alignment with strategies • Business priority • Technical priority • Economic benefit • Risks and alternatives • Decide which opportunities are justified to be executed as projects

46. Steering Committee (Roles)

47. Steering Committee • Data Steward (DS) responsibilities include: • Definition and sourcing of the data elements to which they are assigned • Establishing data management quality criteria and routines • Mitigating quality out-of-bound conditions • Population and maintenance of their respective metadata • Quality assurance checklist includes: • Attribute data types are appropriate to their contents; e.g. numeric attributes are stored in numeric, not character, fields • Attribute values are all within valid ranges • Capitalization, abbreviation, etc, of text attributes (e.g. place names) are consistent throughout the dataset • Temporary attributes used only in construction of the dataset have been removed • Features all have 'reasonable' locations (e.g. all soil samples occur on land) • All feature or cell/pixel attributes contain values (or documentation explains why some values are null, zero or blank)

48. Metadata Management • Metadata, or “data about data,” allows organizations to create a map of their data terrain. • Organizations can leverage this map on an enterprise or a project level to understand, manage, and integrate data more effectively. • Metadata describes how information assets and processes are derived, the fundamental relationships between them, and how they are used. • It is the linkage between technical and business metadata, and tracks all the dependencies within the enterprise, holding the definition of where and how data is used, what happens when it is moved, and what it becomes in the source environment. • It has been estimated that 66% of data warehousing efforts fail and the leading cause is improper metadata management. No wonder Ralph Kimball, considered one of the fathers of data warehousing writes “Metadata isn’t important – It’s everything!”

49. Examples of Metadata • Technical Metadata • User report and query access patterns, frequency and execution time • The system of record feeding the database(s) • Mappings and transformation from the system of record to the database(s) • The data model, both physical and logical • Database table names, keys and indexes • Database table structures and table attribution • Job dependencies • Program names and descriptions • Security

50. Examples of Metadata • Business Metadata • Data structures as known to the business user (friendly names and descriptions). • Table names and their business definitions. • Attribute names and their business definitions. • Field mappings, transformations and summarizations. • Database refresh dates. • Tagging Names used for search. • Taxonomy.