BI GuideBook(上)

 This book is from justifying the project, gatheringrequirements, developing the architectural framework, designing the proper approach for BI data models, integrating the data, generating advanced analytics, dealing with “shadow systems,” understanding and dealing with organizational relationships, managing the full project life cycle, and finally creating centers of excellence—this book covers the entire gambit of creating a sustainable BI environment.

This book will help the BI beginner to learn the need ,its methodology and the myriad designs and implementation pathways of BI。

一.The Business Demand for Data, Information, and Analytics

This chapter explains how the deluge of data and its accompanying need for analysis makes BI critical for the success of today’s enterprise

1.The data and information deluge

1)Data:People need data integration to unify and massage the data, data warehousing to store and stage it, and BI to present it to decision-makers in an understandable way.

Enterprises need this information to understand their operations, customers, competitors, suppliers,partners, employees, and stockholders. They need to learn about what is happening in the business,analyze their operations, react to internal and external pressures, and make decisions that will help them manage costs, grow revenues, and increase sales and profits.

2)Data deluge:when there is more data than an enterprise can handle. They collect massive amounts of data every day internally and externally as they interact with customers, partners,and suppliers. They research and track information on their competitors and the marketplace. They put tracking codes on their websites so they can learn exactly how many visitors they get and where they came from.

3)Data volume,variety,and velocity : Not only that enterprises accumulate data in ever-increasing volumes, the variety and velocity of data is also increasing. They can cause an enterprise’s ability to gather data to explode,

2.The analytics deluge:

1)All of this information has had a tremendous impact on businesses’ ability to make informed decisions.Businesses cannot underestimate the importance of their analytics initiatives.Enterprise depend on data to validate their intuitions.data becomes a strategic guide that helps executives see patterns they might not otherwise notice.

2)Analytics strategy:

Designing a data architecture that enables reporting, analytics, predictive modeling, and self service BI:

• Architecting a BI portfolio

• Architecting solutions with data discovery, data visualization, and in-memory BI

• Enabling operational and analytical BI

• Designing and implementing analytical sandboxes and hubs

• Creating data and analytical governance programs

• Creating shared BI metadata environments.

3.Data versus actionable information

1)Data&information:

`Data is raw, random, and unorganized.like a collection of ingredients.

`Information is data that has been organized, structured, and processed. Information is what you use to gain knowledge.

`In the DW/BI world, the data has been moved into the ETL (extract, transform, and load) system and is transformed into information.

2)The role of bi in creating actionable information

BI turns data into “actionable” information—information that is useful to the business and helps it gain knowledge,whose significant business value has been recognized by many enterprises.

4.Data capture versus information analysis

Captured data is input into operational systems,which means converting or translating it to a digital form.Structured for processing and managing business transactions and interactions.Information analysis isused for reporting, querying, and it remains a key ingredient to an enterprise-wide solution providing clean, consistent, conformed, comprehensive, and currentinformation analytics

5.The five Cs of data

clean,consistent,conformed,current,comprehensive

6.Common terminology

Data integration—combining data from different sources and bringing it together to ultimately provide a unified view.

Data warehousing—the process of storing and staging information, separate from an enterprise’s

day-to-day transaction processing operations, and optimizing it for access and analysis in an enterprise.

BI—to present data to business people so they can use it to gain knowledge.

二.Justifying BI

It helps the BI team make both the business and technical case to determine the need, identify the benefits, and, most importantly, set expectations.

1.Building the business case

1)The importance of justification:The BI team needs to estimate scope, costs, schedule, and a return on investment (ROI). Identifying risks and an organization’s readiness is critical to determining how realistic expectations are.

2)How to define the business case

• Review the organization’s business initiatives

• Enlist a BI sponsor

• Connect with BI stakeholders

• Identify business processes affected by BI

• Document business benefits

The BI effort needs to be based on supporting the needs of business initiatives, business drivers, and

business processes. The focus is on solving business problems

3)Business Initiatives

Most organizations perform business planning for strategic initiatives with a minimum time horizon of the next couple of fiscal years. These business initiatives have been prioritized, approved, funded, and scheduled.

4) How to solicit and engage business sponsors

Use your list of business needs (above) to  identify the business people you will approach as potential business sponsors.Beyond the initial funding, a business sponsor needs to secure the commitment of resources from the business stakeholders and users (below) to ensure BI success.Key characteristics of a business sponsor are being politically astute, enthusiastic, and realistic.

5)How to enlist bi stakeholder

They include the business people who will be either direct users of the BI solution or whose work will be affected by the BI solution, as well as those working on data governance.Getting the business stakeholders involved while building the business case enables more detailed business input and may help identify gaps and risks

2.Building the technical case

A BI project will introduce new technologies and products into an enterprise across a variety of BI, data integration, database, and infrastructure categories.

1)how to build the technical case

selecting the products and technologies, or at least narrowing down a short list, but as we will discuss in the next two subsections, you still need to convince business people and technologists that they ought to use these tools.

2)how to convince business people

the short-term benefit is that business people are inexpensive and may be producing some value. If the business choses the alternative of expanding these reporting capabilities, it will likely be cheaper and faster than a BI solution, at least initially. Likewise, it is almost guaranteed that business people have been using spreadsheets to do reporting and, if the reporting complexity warranted it, they also built data shadows systems

3)how to convince the technologists

First, you need to get the technologists involved in the technology and product selection process. In

this phase, you will need to:

• Determine the technologies required

• Create a short list of product candidates

List the technologies that will be needed to fulfill business requirements and classify them as must-haves versus nice-to-haves. Your business requirements are very high level at this point, but should be enough to develop these lists.

• Gather and prioritize requirements

• Establish success and value criteria

• Select short list of product candidates

• Conduct product reviews with hands-on proofs of concept, if possible

• Score and rank products

• Review results and select product(s)

• Haggle with product vendors over pricing

3. Assessing readiness

1)The areas assessment need to cover:• Data• Expertise and experience• Analytical commitment• Organizational and cultural change• Financial commitment

2)you need to determine:• Data• Expertise and experience• Analytical commitment• Organizational and cultural change• Financial commitment

4.Creating a high-level BI road map

1)The attributes of the bi road map:•Key business initiatives supported and business processes involved• High-level business deliverables• Data source feeds (at the application level, not tables or files)• Technology introduced• Business groups involved

2)The reason for creating a bi road map.

BI should be built incrementally and iteratively. The overall BI program becomes the framework of the journey, with individual BI projects building out specific portions of that road map, relying on tactical decisions that are appropriate for that particular project at that particular time.

5.Developing scope, preliminary plan, and budget the guidelines of developing the preliminary scope:

1)Keep it simple.

2)Be conservative.

3)Expect the unexpected.

6.Obtaining program and project approval

1)The BI justification needs to include:• Overview• Business problem or opportunities• Situational analysis• Solution alternatives• Project description—plan and budget• Cost–benefit analysis• Initial investments• TCO• Risks and issues• Business sponsors and stakeholders

2)you need to sign-off on the following:to ensure realistic commitments:• Follow-on detailed project requirements and priorities• Scope change• User acceptance testing• System testing• Deployment

7.Justification pitfalls

The reasons of pitfalls may include:`Overzealous business sponsors• The CIO is the sole sponsor• Intangible benefits• Confusion between BI technology and business value

三.Defining Requirements—Business, Data, and Quality

This chapter discusses the process of creating the foundation of a successful BI solution by documenting what you are planning to build. These requirements are used to design, develop, and deploy BI systems.

1. The Purpose of defining requrements

1)The purpose:Defining requirements creates the foundation of a successful business intelligence (BI) solution by documenting what you are planning to build. The development team then uses these requirements to design, develop, and deploy BI systems.

2)The mistakes in defining requirements

• Are not detailed enough to clarify what is needed and set expectations

• Restrict their focus to just business requirements

• Are not refined and changed as the project changes

• Do not involve business power users or BI designers and developers

• Re-create the existing system with its warts and inefficiencies

2.Goals

1)The primary goal:

·Define the set of requirements that will be used to design, build, and implement BI solutions within an agreed-upon project timeline and budget.

·From a business perspective, it gets agreement on the business needs and their priorities, while from a technology perspective, it establishes what needs to be built in the three pillars of BI: data model, data integration, and analytical processes.

2）The secondary goal:

·for the BI team to establish the working relationships with its sponsors, stakeholders, and others that support the BI project.

·This phase is the bridge between defining the project scope and designing the data model, data integration, and BI processes.

3. Deliverables

1)The primary deliverable: is a set of requirements that business sponsors, stakeholders, and IT management have agreed upon. A supporting deliverable will be a revised project plan including budget and resource commitments based on these requirements.

2)The documentation should include:

·Project description• Project functionality.• Project assumptions• Authors and contributors• List of inputs for requirements• Requirement priorities:• Issues and concerns• Change management• Sign-offs

3）The subjects should include:

• Business requirements• BI functional requirements• Data requirements• Regulatory and compliance requirements• Technical requirements

4.Roles

1)bi team participants

2)business participants

3)other IT participants

5.Workflow

The BI team needs to gather the business, functional and data requirements from business and IT in sufficient detail.

The process starts with defining business requirements, followed by defining data, functional, regulatory/compliance, and technical requirements.

1)business requirements:It's associated with particular business processes and business groups.

2)Data requirements:means drilling into the data sources to identify detailed data content, such as columns within tables or fields within files, and the transformations that are needed for consistency, conformance, or to calculate the business metrics.

3)functional requirements:It may include BI use cases,Analytical process workflow and user interaction,Analytical styles needed

4)Regulatory/compliance requirements

5)Prioritizing requirements

6.Interviewing business people

1)Prepare for the interviews:

·Background information on the enterprise and interviewees

· Creating a list of subject areas and, ideally, specific questions to be covered in the interview

·Inviting participants and providing the information from above

2) Conduct the interviews

3)Follow up after the interviews：Try to review your notes (and recording, if you have it) as soon as possible after the interview.

7.Documentation techniques

The process of collecting requirements involves a stepwise refinement, starting with business requirements and then delving into more details on data and functional requirements, followed by a review of the current state of reporting. You should document each of these processes during the requirements workflow, gathering feedback from applicable stakeholders and refining the document throughout the process.

四.Architecture Introduction

This chapter helps everyone understand the importance of a well architected foundation. The architecture sets your directions and goals. It is a set of guiding principles,

1.BI architecture framework

1)The need for architectural blueprints:. An architectural  framework, i.e., a set of architectural blueprints, is needed as each new BI project is undertaken to enable these projects to complement each other and create a cohesive, cost-effective BI solution. The framework needs to be designed to accommodate expansion and renovation based on evolving requirements, capabilities, and skills.

2)4 architectural layers:Information architecture,Data architecture,Technical architecture,Product architecture

2.Information architecture

The information architecture defines the business context necessary for successful BI solutions to be built on a sustaining basis.

1)The information architecture defines the “what, who, where, and why” for BI or analytical applications:

• What business processes or functions are going to be supported, what types of analytics will be

needed, and what types of decisions are affected

• Who (employees, customers, prospects, suppliers, or other stakeholders) will have access

• Where the data is now, where it will be integrated, and where it will be consumed in analytical

applications

• Why the BI solution(s) will be built—what the business and technical requirements are

3. Data architecture

1)The data architecture defines the data along with the schemas, integration, transformations, storage, and

workflow required to enable the analytical requirements of the information architecture.

2)The scope of the data architecture :starts where data is created in the source systems by information providers and ends where the business person (or information consumer) performs data analysis.

4.Technical architecture

1)The technical architecture defines the technologies that are used to implement and support a BI solution that fulfills the information and data architecture requirements.

2）The scope of  technical architecture:the entire BI life cycle of design, development, testing, deployment, maintenance, performance tuning, and user support.

3）4 major functional layers of the BI technical architecture:

·Business intelligence (and analytical applications)

·Data warehouse and BI data stores

·Data integration

·Data sources

5.Product architecture

The product architecture defines the products, their configurations, and how they are interconnected to implement the technology requirements of the BI framework.

6.Metadata

1）The classic definition：It is “data about data.” Metadata is the description of the data as it created, transformed, stored, accessed, and consumed in the enterprise. It  is one of the key ingredients to enabling a data-driven enterprise.

2)Business people need to know what the data represents—where it came from, how it was transformed, and what it means—if  they hope to perform meaningful business analytics.

IT people need to know what happened to the data from the point of capture through its consumption by the business in their reports and analysis if they are to provide consistent, comprehensive, conformed, clean, and current data for business analytics.

3）Technical & business.

Technical metadata is the description of data as it is processed by software tools.Business metadata, in contrast, is the description of information from the business perspective (e.g.,the business context of the inventory turns, weekly sales, or budget variance reports).

4)What to do about it:·manage the scope.·address the cultural and political issues.·do not count on technology to be a silver bullet for metadata management.

7.Planning for security and privacy

1)Security and privacy are not just a BI concern; this obviously needs to be elevated to an enterprisewide policy and program. Typically, it is in the CIO’s domain, but regardless of who manages it, the BI initiative needs its security and privacy policies to follow enterprise standards and become active participants to get BI data and applications included into this domain.

2)How to implement the plan:

·the BI team needs to determine what data needs to be secured and what does not.

·the BI team needs to examine what type and level of security could be implemented at each BI layer

·they need to examine the cost/benefit trade-off of what could be done and determine what makes the most sense from a business value perspective.

·implement the security and privacy in the BI architecture

·communicate those policies to all BI stakeholders and enlist their vigilance in enforcing those policies in what they do.

8.Avoiding the accidental architecture

Whatever your solution architecture, you need to plan on building it iteratively and replacing or integrating your silos incrementally. Your solution architecture needs to be built using a program approach; it will evolve as your business and technology evolve.

五.Information architecture

This chapter introduces the framework that defines the business context—“what, who, where, and why”—necessary for building successful BI solutions.

1.Information architecture purpose

1）The purose:The information architecture enables the business to perform analytics on these diverse processes, whether they are selling a product, monitoring a patient’s vital stats, posting a student’s grades, or tracking the performance of financial accounts.

2)The key area when designing it:

·Data integration framework

·Operational BI and its role versus analytical BI

• Master data management (MDM)

2.Data integration framework (DIF)

1）The DIF is a combination of architecture, processes, standards, people, and tools used to transform enterprise data into information for tactical operations reporting and strategic analysis.

·People tend to equate data integration with an extract, transform, and load (ETL) tool.

2）The objective:gather data that is scattered inside and outside an enterprise and transform it into information that the business uses to operate and plan for the future.

3)DIF is composed of:

Data preparation;Data franchising;Business intelligence and analytics;Data management;Metadata management

3.Data preparation

1)Data preparation is the core set of processes for data integration that gather data from diverse source systems, transform it according to business and technical rules, and stage it for later steps in its life cycle when it becomes information used by business consumers.

2)Steps:Gather and extract data from source systems.Reformat data.Consolidate and validate data.Transform data.Data cleansing.Store data

4.Data franchising

1)Data preparation gathered and transformed data from source systems into a DW. Data franchising takes the data from the DW and transforms it into the information consumed in business analysis by using BI tools.

2)The need of data franchising:

• Business people can understand the data.

• BI tools can more effectively present the data.

• Improves business and IT productivity.

• Enables pervasive self-service BI.

3)Data Franchise Steps

• Gather, filter, and subset data.

• Restructure or denormalize data.

• Perform business transformations and metrics calculations.

• Aggregate or summarize data.

• Store data. T

5.BI and analytics

1)2 roles of BI are ti provide:

`• Back-end processes that select, retrieve, and transform the data stored in the information architecture.

• Front-end processes visible to BI application users to interact with, analyze, and present results in graphic or tabular form.

6. Data management

Data management encompasses the policies, procedures, and standards used to design and manage the other information architecture processes: data franchising, data preparation, BI, and metadata management.

7.Metadata management

1) Metadata is generated by many of the tools used in the BI environment, and also created by design, development and deployment activities such as gathering business requirements.

2)The categories of metadata:Data definitions;ETL source-to-target mapping;BI applications

8.Operational BI versus analytical BI

1)Operational reporting is essential for the business people involved in running the business on a dayto-day basis. Business transactions and events are captured, monitored, and reported on by the operational applications.

2)two types of application vendors where this scenario get serious consideration:

·two types of application vendors where this scenario get serious consideration:

· Application vendor supporting cross-function business process.

9.Master data management (MDM)

1）MDM is the set of processes used to create and maintain a consistent view, also referred to as a master list, of key enterprise reference data. This data includes such entities as customers, prospects, suppliers, employees, products, services, assets, and accounts.

2）The steps with respect to MDM:

• Identify what reference data needs MDM.

• Determine where in the information lifecycle inconsistent data creates business risks.

• Assess what type of information processing is needed to create consistent reference data.

六.Information architecture

This chapter explains that data architecture is a blueprint that helps align your company’s data with its business strategies.

1.Data architecture purpose

1)The data architecture defines the data along with the schemas, integration, transformations, storage,

and workflow required to enable the analytical requirements of the information architecture.

2)The reasons:

• Helps you gain a better understanding of the data

• Provides guidelines for managing data from initial capture in source systems to information

consumption by business people

• Provides a structure upon which to develop and implement data governance

• Helps with enforcement of security and privacy

• Supports your business intelligence (BI) and data warehousing (DW)activities, particularly Big Data

2. Various architectural choices

The major competing data architectures are:

• EDW

• Independent data marts

• Enterprise data bus architecture—Ralph Kimball [1]

• Hub-and-spoke, corporate information factory (CIF)—Bill Inmon [2]

• Analytical data architecture (ADA)

3.Analytical data architecture (ADA)

1)3 layers of data that enable BI:

· Systems of record (SOR)

—source systems where data is created, updated and deleted

·Systems of integration (SOI)

—data structures used to integrate data to enable the 5C’s of

information: consistent, clean, comprehensive, conformed and current.

·Systems of analytics (SOA)

—data structures used by BI application to enable analytics

4.Data integration (DI) workflows

1）The data architecture enables the transformation of data into consistent, conformed, comprehensive,clean, and current information for business analysis and decision-making. 

2）DI processes include:

• Gather data

• Integrate and transform data using business rules and technical conversions

• Store it in various data stores

• Make it available to business users with BI tools

5. Operational data store (ODS)

1)4 characteristics of ods

• Integrates data from multiple source systems

• Enables consolidated reporting

• Offers pre-built reporting or integration solution for enterprise applications

• Uses a data schema similar to source systems

七.Technology& product architecture

This chapter gets into the nitty gritty of the technology and product architectures and what you should know when you are evaluating them.

1.Evolution of technology and products

1) Over the last decade, BI has been one of the top, if not the top information technology (IT) initiative, according to various industry analyst surveys of chief information officers (CIOs). Regardless of economic conditions, BI has continued to grow faster than the overall IT spending, as enterprises across industries view BI as a strategic requirement to operate and grow.

2)4 technology layers that form the BI technology architecture

· Business intelligence and analytics

• Information access and data integration

• Data warehousing

• Data source

2.BI and analytics

1)An enterprise’s business community will have a very diverse set of analytical needs and work styles.The type of analysis they perform will vary based on the depth, subject, volume, and structure of the data used, as well as the business processes: examining salespeople’s performance, providing customer support, predicting customer behavior, etc.

2)BI options include :data discovery, data visualization, in-memory analytics, predictive modeling, BI appliances, and Big Data analytics.

3. Information access and data integration

1)BI target:business processes and applications are increasingly important BI targets, particularly as BI expands into internal operations and interacts with external enterprise stakeholders. BI may be embedded into business processes or used to communicate with business applications

2）Integration services:

The integration may take place at the beginning of, or during the business analysis process.It will satisfy the needs  for a variety of integration services of data sources spanning a wide variety of locations, protocols, data types, security,and currency。

3）The types of data integration services include:

Enterprise application integration (EAI) i

Enterprise message services (EMS)

Enterprise information integration (EII)

ETL and extract, load & transform (ELT):

4.Relational databases and alternatives

1)today’s enterprise is gathering more unstructured data from sources such as e-mails, social media, patient records,and legal documents

2)database technology is used for both data capture and analytics, analytical data is stored separately from the data capture (depicted as data sources). Real-time queries and operational BI will take place on the data capture database while analytical BI accesses the analytical data that has been integrated and transformed from the data capture databases

3)Rational alternatives:Online Analytical Processing，This technology was developed to provide query and analysis of data in a multidimensional schema.

5.Big Data

This phenomena of big data: Enterprises have been experiencing an ever-increasing rise in data volumes, data variety (source and formats), and data velocity (the need for real-time updates.)

6. BI appliances

BI appliances are a combination of products designed to improve performance and increase scalability for BI and analytical applications. There are also comparable appliances built to do the same for data warehousing, Big Data, and transaction processing.

7.Product and technology evaluations

1)BI has had a long history of sustained and disruptive innovations that continue to improve an enterprise’s ability to meet the challenge of increasing data volumes, velocity, and varieties, while at the same time returning more value to its stakeholders.

2)Product evaluation tasks include:

Gather and prioritize requirements:

Establish success and value criteria:

Select shortlist of product candidates.

Conduct product reviews.

Score and rank products.

Review results and select product(s).

Hassle with product vendors over pricing.

八.Foundational data modeling

This chapterdescribes the different levels of models: conceptual,logical and physical; the workflow, and where they are used. It explains entity relational (ER) modeling in depth,and covers normalization, the formal data modeling approach for validating a model.

1.Introduction to data modeling

1)The purpose of data modeling:A well-designed data model is the cornerstone to building business intelligence (BI) and data warehousing (DW) applications that provide significant business value. Effective data modeling results in transforming data into an enterprise information asset that is consistent, comprehensive, and current.

2)data model&data modeling

The data model’s primary job is to be the data design specification for an IT application.

Data modeling:It is a structured approach to identify and analyze those data components of the infor

mation system specifications.

2.Three levels of data models: conceptual, logical and physical

1)The conceptual data model is a structured business view of the data required to support business pro

cesses, record business events, and track related performance measures.

2)The logical data model is the one used most in designing BI applications. It builds upon the require

ments provided by the business group.

3)The physical data models detail how that data will be implemented.

3.Modeling workflow

1)You use this process to:gather business requirements;create the various data models needed;support the application development.

2)the modeling workflow:Gather the business requirements——>Develop the conceptual data model——>create the logical data model——>design physical data model

4.Where data modeling is used

1)two primary areas in which relational databases are used:

Transactional processing and operational systems;BI applications

5.Entity-relationship (ER) modeling overview (cardinality, relationships, keys, referential integrity)

ER modeling is a logical design modeling technique.ERP systems nearly always uses ER models. These systems often have thousands of tables, and some even have tens of thousands.

6.Normalization

The ER model is used for transactional systems primarily because it minimizes data redundancy and ensures data integrity. The approach to reduce redundancy is called normalization, which is a formal data modeling approach to examining and validating a model and putting it into a particular normalized form.

7.Third normal form (3NF)

To eliminate the columns not dependent on the key