Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
This enterprise logical data model, also known as the enterprise information
architecture, is not constructed all at once, nor is it a prerequisite for BI projects to
have a completed one. Instead, the enterprise logical data model evolves over time
and may never be completed. It does not need to be completed because the
objective of this process is not to produce a finished model but to discover and
resolve data discrepancies among different views and implementations of the same
data.
These data discrepancies exist en masse among stovepipe operational systems and
are the root causes of an organization's inability to provide integrated and consistent
cross-organizational information to its business people. The discovery of these
discrepancies should be embraced and celebrated by the BI project team, and
especially by the business people, because poor-quality data is finally being
addressed and resolved. Gaining control over the existing data chaos is, after all, one
major function of any BI decision-support initiative.
If organizations would follow business analysis best
practices by developing logical data models for all their
operational applications and merging them (over time)
into an enterprise logical data model, the BI decision-
support development effort could be significantly
reduced. This would enable BI project teams to increase
the speed of delivering reliable decision-support
information to the business people. In other words, the
BI project teams could deliver the "quick hits" that
everyone wants—and deliver them with quality.
Logical Data Modeling Participants
Logical data modeling sessions are typically facilitated and led by a data
administrator who has a solid business background. If the data administrator does
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
181/631
not have a good understanding of the business, a subject matter expert must assist
him or her in this task.
The business representative and the subject matter expert assigned to the BI project
are active participants during the modeling sessions. If the data is being extracted
from several different operational systems, multiple data owners may have to
participate on the BI project because each operational system may be under the
governance of a different owner. Data owners are those business individuals who
have authority to establish business rules and set business policies for those pieces
of data originated by their departments. When data discrepancies are discovered, it
is the data owners' responsibility to sort out the various business views and to
approve the legitimate usage of their data. This data reconciliation process is and
should be a business function, not an information technology (IT) function, although
the data administrators, who usually work for IT, facilitate the discovery process.
Systems analysts, developers, and database administrators should also be available
to participate in some of the modeling sessions on an as-needed basis. These IT
technicians maintain the organization's applications and data structures, and they
often know more than anyone else about the data—how and where it is stored, how
it is processed, and ultimately how it is used by the business people. In addition,
these technicians often have in-depth knowledge of the accuracy of the data, how it
relates to other data, the history of its use, and how the content and meaning of the
data have changed over time. It is important to obtain a commitment to the BI
project from these IT resources since they are often busy "fighting fires" and working
on enhancements to the operational systems.
Standardized Business Meta Data
A logical data model, representing a single cross-organizational business view of the
data, is composed of an E-R diagram and supporting business meta data. Business
meta data includes information about business data objects, their data elements,
and the relationships among them. Business meta data as well as technical meta
data, which is added during the design and construction stages, ensure data
consistency and enhance the understanding and interpretation of the data in the BI
decision-support environment. A common subset of business meta data components
as they apply to data (as opposed to processes) appears in Figure 5.4.
A data name, an official label developed from a formal data-naming taxonomy,
should be composed of a prime word, a class word, and qualifiers. Each data
name uniquely identifies one piece of data within the logical data model. No
synonyms and no homonyms should exist.
A data definition is a one- or two-sentence description of a data object or a
data element, similar to a definition in a language dictionary. If a data object
has many subtypes, each subtype should have its own unique data definition. A
data definition explains the meaning of the data object or data element. It does
not include who created the object, when it was last updated, what system
originates it, what values it contains, and so on. That information is stored in
other meta data components (e.g., data ownership, data content).
A data relationship is a business association among data occurrences in a
business activity. Every data relationship is based on business rules and
business policies for the associated data occurrences under each business
activity.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
182/631
A data identifier uniquely identifies an occurrence of a data object. A data
identifier should be known to the business people. It should also be "minimal,"
which means it should be as short as possible (composed of just enough data
elements to make it unique). In addition, a data identifier should be
nonintelligent, with no embedded logic. For example, account numbers
0765587654 and 0765563927, where 0765 is an embedded branch number,
would be poor data identifiers.
A logical data identifier is not the same thing as a
primary key in a database. Although a data identifier
can be used as a primary key, it is often replaced by
a surrogate ("made-up") key during database design.
Data type describes the structure of a data element, categorizing the type of
values (character, number, decimal, date) allowed to be stored in it.
Data length specifies the size of a data element for its particular data type. For
example, a decimal data element can be an amount field with two digits after
the decimal point or a rate field with three digits after the decimal point.
Data content (domain) identifies the actual allowable values for a data
element specific to its data type and data length. A domain may be expressed
as a range of values, a list of allowable values, a generic business rule, or a
dependency rule between two or more data elements.
A data rule is a constraint on a data object or a data element. A data
constraint can also apply to a data relationship. A data constraint can be in the
form of a business rule or a dependency rule between data objects or data
elements, for example, "The ceiling interest rate must be higher than the floor
interest rate."
Data policy governs the content and behavior of a data object or a data
element. It is usually expressed as an organizational policy or a government
regulation. For example, "Patients on Medicare must be at least 65 years old."
Data ownership identifies the persons who have the authority to establish and
approve the business meta data for the data objects and data elements under
their control.
Figure 5.4. Data-Specific Business Meta Data Components
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
183/631
Although logical data models are extremely stable, some of these business meta data
components (such as data content, data rules, data policy, data ownership)
occasionally change for legitimate reasons. It is important to track these changes in
the meta data repository.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
184/631
[ Team LiB ]
Bottom-Up Source Data Analysis
Data analysis cannot stop after top-down logical data modeling because the source
data often does not follow the business rules and policies captured during the
modeling sessions. If bottom-up source data analysis were not performed, the data
problems and business rule violations would not be discovered until the ETL process
was being implemented. Some data quality problems would not be discovered at all
until after implementation, and then only if somebody complained about them. As
Figure 5.5 shows, source data mapping must adhere not only to the usual technical
data conversion rules but also to the business data domain rules and to the business
data integrity rules.
Figure 5.5. Source Data-Mapping Rules
Technical Data Conversion Rules
Any time data is mapped from one system to another, whether for traditional
systems conversion or for source-to-target mapping in BI applications, the following
technical rules must be observed.
The data types of the source data elements must match the data types of the
target data elements.
1.
The data lengths must be adequate to allow the source data elements to be
moved, expanded, or truncated into the target data elements.
2.
The logic of the programs manipulating the source data elements must be
compatible with and applicable to the content of the source data elements.
Otherwise the results will be unpredictable.
3.
Business Data Domain Rules
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
185/631
A much larger effort of source data analysis revolves around business data domain
rules. These rules are more important to the business people than the technical data
conversion rules. A source data element can meet all three technical data conversion
rules but still have incorrect values. Business data domain rules are rules about the
semantics (meaning and interpretation) of data content. They are used to identify
and correct data violations like those listed in Table 5.1.
Business Data Integrity Rules
Similar to business data domain rules, business data integrity rules are much more
important to improving information quality than are the technical data conversion
rules. The business data integrity rules govern the semantic content among
dependent or related data elements, as well as constraints imposed by business rules
and business policy. Table 5.2 lists examples of violations to business data integrity
rules.
Table 5.1. Data Domain Violations
1.
Missing data values (a big issue on BI projects)
2.
Default values; for example, "0", "999", "FF", blank
3.
Intelligent "dummy" values, which are specific default (or dummy) values that
actually have a meaning; for example, using a value of "888-88-8888" for the
social security number to indicate that the person is a nonresident alien
4.
Logic embedded in a data value, such as an implied business rule; for example,
using lower-valued ZIP codes (postal codes) to indicate a state on the east
coast, such as 07456 in New Jersey, and higher-valued ZIP codes to indicate a
state on the west coast, such as 91024 in California
5.
Cryptic and overused data content; for example, using the values "A, B, C, D" of
a data element to define type of customer, while the values "E, F, G, H" of the
same data element define type of promotion, and the values "I, J, K, L" define
type of location
6.
Multipurpose data elements, that is, programmatically and purposely redefined
data content; for example, the redefines clause in COBOL statements
7.
Multiple data elements embedded in, concatenated across, or wrapped around
free-form text fields; for example, Address lines 1 through 5 containing name
and address data elements:
Address line 1: Brokovicz, Meyers, and Co
Address line 2: hen, Attorneys at Law
Address line 3: 200 E. Washington Bouleva
Address line 4: rd,
Address line 5: Huntsville OR 97589
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
186/631
Table 5.2. Data Integrity Violations
1.
Contradicting data content between two or more data elements; for example,
"Boston, CA" (instead of MA)
2.
Business rule violation; for example, for the same person, "Date of Birth =
05/02/1985" and "Date of Death = 11/09/1971"
3.
Reused primary key (same key value used for multiple object instances); for
example, two employees with the same employee number (when one employee
left the company, his or her employee number was reassigned to a new
employee)
4.
No unique primary key (multiple key values for the same object instance); for
example, one customer with multiple customer numbers
5.
Objects without their dependent parent object; for example, job assignment
points to employee 3321, but the employee database contains no employee
3321
6.
A real-world relationship between two data objects, or between two occurrences
of the same data object, that cannot be built because it is not tracked by the
operational systems; for example, a customer refinances a mortgage loan but
the operational system does not track the relationship between the old paid-off
loan and the new refinanced loan
Every critical and important data element must be examined for these defects, and a
decision must be made whether and how to correct them. The information
consumers (business people who will be using those data elements to make business
decisions) and data owners should make that decision after discussing the impact of
the cleansing effort with the business sponsor, the project manager, and the core
team.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
187/631
[ Team LiB ]
Data Cleansing
One of the goals stated most frequently for BI applications is to deliver clean,
integrated, and reconciled data to the business community. Unless all three sets of
data-mapping rules are addressed, this goal cannot be achieved. Many organizations
will find a much higher percentage of dirty data in their source systems than they
expected, and their challenge will be to decide how much of it to cleanse.
Data Quality Responsibility
Data archeology (the process of finding bad data), data cleansing (the process of
correcting bad data), and data quality enforcement (the process of preventing data
defects at the source) are all business responsibilities—not IT responsibilities. That
means that business people (information consumers as well as data owners) must be
involved with the data analysis activities and be familiar with the source data-
mapping rules.
Since data owners originate the data and establish business rules and policies over
the data, they are directly responsible to the downstream information consumers
(knowledge workers, business analysts, business managers) who need to use that
data. If downstream information consumers base their business decisions on poor-
quality data and suffer financial losses because of it, the data owners must be held
accountable. In the past, this accountability has been absent from stovepipe
systems. Data quality accountability is neither temporary nor BI-specific, and the
business people must make the commitment to accept these responsibilities
permanently. This is part of the required culture change, discussion of which is
outside the scope of this book.
The challenge for IT and for the business sponsor on a BI project is to enforce the
inescapable tasks of data archeology and data cleansing to meet the quality goals of
the BI decision-support environment.
Step 5, Data Analysis, may be time intensive since many
battles may rage among the business people as to the
valid meaning and domain of data.
Although data-cleansing tools can assist in the data archeology process, developing
data-cleansing specifications is mainly a manual process. IT managers, business
managers, and data owners who have never been through a data quality assessment
and data-cleansing initiative often underestimate the time and effort required of their
staff by a factor of four or more.
Source Data Selection Process
Poor-quality data is such an overwhelming problem that most organizations will not
be able to correct all the discrepancies. When selecting the data for the BI
1.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
188/631
application, consider the five general steps shown in Figure 5.6.
Identify the required data.
Identify the data of interest and the significance of this data. Data cleansing is a
collaborative effort between business analysts who are familiar with the
semantics of the data and data quality analysts who know the program-specific
meanings of the data (e.g., the use and meaning of a "flag" value or redefined
record layouts).
1.
Analyze the data content.
Analyze the data for content, meaning, and importance. Many organizations
have accumulated massive amounts of data in files and databases. This data
constitutes a prospective gold mine of valuable business knowledge and is
potentially a good source for data mining. However, the quality of the data
content must be assessed first, since mining dirty data is of little value.
2.
Select the data for BI.
Determine which data to include in the BI application. Select only the data that
will meet core business requirements. Even with automated tools, the cost of
assuring data quality for an all-inclusive BI decision-support environment
becomes prohibitive for most organizations. Some questions to consider when
selecting data appear below.
- Is this data clean enough for decision-support usage?
- If not, can this data be cleansed, at least partially? Do we know how?
- Is the dirty data the reason for building this BI application? Is cleansing
this data therefore mandatory?
- How much effort will it take to figure out how to cleanse the data?
- How much will the data-cleansing effort cost?
- What is the benefit of cleansing the data as opposed to moving it into
the BI application at the current level of dirtiness?
- What are the data quality expectations from the information consumers
and from business management in general?
3.
Prepare the data-cleansing specifications.
The IT staff, working with the business representative, will get to know the
business rules needed to write the data-cleansing specifications. In essence,
this is a source data reengineering process.
4.
Select the tools.
Select the ETL and cleansing tools. Determine whether it is appropriate and
cost-effective to acquire an ETL tool, a cleansing tool, or both. Examine the
suitability and effectiveness of those tools. Some data-cleansing specifications
can be very complicated. Be sure the tools are capable of handling them.
5.
Figure 5.6. Source Data Selection Process
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
189/631
Automated tools do not eliminate the manual labor of
source data analysis; they only reduce it.
Key Points of Data Selection
When identifying and selecting the operational data to be used to populate the BI
target databases, some key points should be considered. Applying the source data
selection criteria shown in Figure 5.7 minimizes the need for and effort of data
cleansing.
Data integrity: How internally consistent is the data? This is the most
important criterion.
- The greater the proportion of manually entered data (data keyed in with
few or no data controls, edits, and validations), the lower the integrity.
- Programming errors also contaminate great masses of data—and do so
automatically.
- The lower the integrity, the greater the cleansing requirement.
Data precision: How precise is the data? This is the next important criterion.
- How is the data represented internally?
- For numeric data, what is the scale and precision of the data?
- For date data, how is it formatted?
Data accuracy: How correct is the data?
- Are there edit checks in the data entry program?
- Are dependent values cross-checked? For example, does the data entry
program forbid an expiration date to precede an effective date?
- Is there an operational process in place for correcting data?
- Are calculated values stored? What, if any, mechanisms are in place to
keep these values accurate?
Data reliability: How old is the data?
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
190/631