storyboard data warehouse planning guest
TRANSCRIPT
-
8/2/2019 Storyboard Data Warehouse Planning Guest
1/15
Executive Summary
In order to deliver business value with a DW, establish a manageable technical footprintearly and understanding how to build the program around business needs.
DW is not an infrastructure project (e.g. storage), its an exercise in effective datamanagement and business intelligence development.
Starting a DW project off on the right foot depends on organizational readiness. Ensure theorganization has correct people, processes and technologies to support the DW plan.
Effective planning means establishing justifiable business goals and a realisticimplementation roadmap that enables early business value. InfoTechs DW survey revealedthat organizations that demonstrate business value early, show sustained growth in the usageof their DW.
Business value is determined by articulating the business functional and non-functional
requirements throughout the DW lifecycle.
Requirements must be translated into a blueprint that includes architecture design,application development, data integration, data quality, and data model and interfacedesign.
Info-Tech Research Group 1
-
8/2/2019 Storyboard Data Warehouse Planning Guest
2/15
Data warehouse programs improve decision-making
by enabling BI processes & applications
Info-Tech Research Group 2
To learn more about BI, read Business Intelligence: Core Concepts and Technologies.
Info-Tech Research Article:
Over 40% oforganizationswith BI donthave a data
warehouse.
A datawarehouse is part
of the BIarchitecture butis notnecessary
for BI.
http://www.infotech.com/research/business-intelligence-core-concepts-and-technologies?nav_id=2639http://www.infotech.com/research/business-intelligence-core-concepts-and-technologies?nav_id=2639 -
8/2/2019 Storyboard Data Warehouse Planning Guest
3/15
Start building by understanding the bricks & mortar of a
data warehouse
Info-Tech Research Group 3
Repository Types Definition Use CaseUpdate
FrequencyData
Complexity
Metadata
Contains reference informationabout the primary data. It
describes what the data means
for end users and captures data
lineage.
Part of every data warehouse
environment. Common metadata iscritical to ensure data consistency.
Frequent
near realtime or
longer.
High
Staging Area
Temporary storage area
(separate server) dedicated toperforming transformations and
joins, etc. prior to loading datainto the EDW.
Optional. Typically used when batchloading for various source systems
happens at different times andrequires a holding area for
transformations and joins prior to
loading for the DW.
Hourly or
longer.
Low
Operational Data
Store (ODS)
Provides a snapshot of enterprise
wide operational/transactionaldata for a given moment in time.
Optional. Used for high availability
and high speed sharing of transactioninformation for operational and tactical
decision making. An ODS can also be usedas a staging area.
Frequent
near realtime.
Low
Data Mart
Subsets of data specific to a
functional area or department,
geographical region or timeperiod.
Departmental or line of business
focused data for tactical and strategicdecision making. Can be used on itsown or in conjunction with other
repository types.
Daily or
longer. Medium
EDW
Enterprise Data Warehouse a
central repository of integratedenterprise wide information at
varying levels of detail.
Provides a single view of the
enterprise across all functional areas
and lines of business for tactical andstrategic decision making. Can be used
on its own or in conjunction with
other types.
Daily or
longer.
High
-
8/2/2019 Storyboard Data Warehouse Planning Guest
4/15
The DW components that are required will vary depending on the
kind of decisions that need to be supported
esign choices must address the kinds of decisions involved.
Info-Tech Research Group 4This is a rough approximation and does not reflect all use cases.
Operational Data
Store (ODS) Data Mart EDW
Decision Type
Frequencyof Decisions
Data LatencyNeeds
QueryFrequency
QueryComplexity
AnalyticalComplexity
Scope ofData Involved
Operational Tactical Strategic
High frequency on daily
basis
Low frequency on daily
basis
Weekly monthly
decisions
Near real-time Hourly to Daily Historical
High
High
Medium
Medium
Low
Low
HighMedium to HighLow
Subject Specific
No HistorySubject Specific, cross-
functional and historical
Enterprise wide and
historical
-
8/2/2019 Storyboard Data Warehouse Planning Guest
5/15
Set yourself up for success make sure the enterprise is
ready for DW
Info-Tech Research Group 5
Organizations thathave a need for data warehousing may still facechallenges when attempting to run a DW project. Understanding your
readiness will set you up for success.
Are your people ready? Strong Sr. management and
lower level business/ IT
support? End-user support? IT team has requisite skills?
Do you have strong processes? Data governance is present? Specific business benefits
identified?
Adequate budget/ timeline?
Is the technology ready? Data integration tools and
procedures are in place? Appropriate software/
hardware in place (e.g.
DBMS, etc.)?
Readiness is a function of three factors: Enterprises want to be sureto
-
8/2/2019 Storyboard Data Warehouse Planning Guest
6/15
Assess your readiness to better prepare for
your initial DWproject
se Info-Techs Data Warehouse Readiness Assessment tool to help
determine your preparedness for undertaking a data warehouse project.
Info-Tech Research Group 6
Not all readiness factors are make-or-break. Understand which factors are critical to
success and if/how shortfalls and obstacles can be overcome.
Info-Tech Insight:
People Assessment Recommendations
1. Sponsorship: Do you ha ve an executive sponsor formally supporting
the project?
a. Yes, we have strong sponsors representing both the business and IT thatare equal stakeholders in the project.
b. We have executive sponsorship for either the business or IT, but not both.
c. The business is currently unaware that this project is being considered, this
is an IT led initiative.
a No recommendation. You are on track.
2. End User Support: What degree of support do you have from target
end users of the DW?
a. We have business representatives on the project planning team.
b. We have spoken to the end users and they have offered verbal support of
the program.
c. We have not spoken to end users.
a No recommendation. You are on track.
3. End User Expectations: What ex pectations do end users currently
have about the DW program?
a. End users recognize that the data warehouse will be a work in progress and
that it will not address all of their needs as soon as it is deployed.
b. End users recognize that there will be some initial problems, but they have
unrealistic expectations about how the system will affect their work.
c. End users believe that the s ystem will perform perfectly as soon as it is
deployed and will radically change (positively or negatively) the way they do
their job.
c
Radically incorrect expectations are usually a function of overselling the value
of a DW (positive expectations) or a lack of confidence in IT delivery/projects
(negative expectations). Reset positive expectations by communicating a
realistic delivery sc hedule for business access to the DW and functionality. To
reset negative expectations, achieve quick wins by targeting areas where you
can deliver measurable value early in the process. Advertise successes by
launching an internal DW marketing program.
Data Warehouse Readiness Assessment Tool
This questionnaire is designed to provide a high level assessment of your organizations readines s to embark on a data warehousing project. The questions target key
readiness crit eria that are predictors of an organizatio ns succes s with data warehousing. Use the assessment to disc over and address gaps in your organizations
readiness.
Select one of the three options for each of the questions below. Once completed, the readiness t ool will give you a recommendation and a measure of your organizations
readiness for each category below.
This tool will help you: Determine the
readiness of your
staff.
Assess businessprocess readiness.
Assess technological
capabilities. Determine overall
organizational
readiness. Identify next steps.
http://www.infotech.com/research/data-warehouse-readiness-assessmenthttp://www.infotech.com/research/data-warehouse-readiness-assessment -
8/2/2019 Storyboard Data Warehouse Planning Guest
7/15
Define the business plan - align with enterprise goals and
develop a realistic execution strategy and timeline
The output of the planning
process will be a business planthat identifies:
Problem to be solved.
Alignment with corporate strategy.
Opportunities.
Risks.Project recommendations.
Technology investments.
Roles and responsibilities.
Alternatives.
Implementation roadmap.
Approval sought.
A business plan is a living
document that will evolve over
time as requirements change.
Info-Tech Research Group 7
Use InfoTechs
Data Warehouse Business Plan template to jump start theplanning process:
http://www.infotech.com/research/data-warehouse-business-planhttp://www.infotech.com/research/data-warehouse-business-plan -
8/2/2019 Storyboard Data Warehouse Planning Guest
8/15
Define the scope of the DW: it must have a manageable scope
that builds concrete business value
Info-Tech Research Group 8
here are many ways a DW can contribute to the
business: Providing an enterprise view of business events
and entities (e.g. customers & products).
By enabling advanced analytics that improve
decision making.
improving overall network performance byreducing the strain on operational databases and
speeding up querying response times.
he scope of the project should center around theenterprise information at the level of granularity
required by users to support their business processes.This dictates what goes into the DW and how it is
structured.
he business and IT must work in tandem to gain a
comprehensive understanding of the types of reportsand analysis that end users need. Once the businessrequirements are known, IT requirements and design
points can be distilled.
Business Value
-
8/2/2019 Storyboard Data Warehouse Planning Guest
9/15
Enumerate the specific roles the project will require,
and staff those roles appropriately
As with other systems projects, you will need a projectsponsor, project manager, business analyst, databaseadministrator, developer, and testing and quality
assurance coordinator.
You will need a DW analyst who can architect and deliveryour DW one with experience in this specialized field. Incertain cases, this role is split even further into a dataarchitect, who designs the architecture, and a technicalleader who delivers it.
You will also need someone with experience in datamodeling and design, preferably someone who iscomfortable with one or more modeling tools.
You will need a source data analyst (usually separate fromthe business analyst) who has deep expertise in theorganizations source systems and processes (this usually aspot best filled by an internal rather than externalcandidate).
A DW middleware specialist should be familiar with the
best ways of moving and transforming your data, and withETL and data quality tools.
Finally, you should have someone on the project that isconversant with the data access tools you will be rollingout (meaning that they can install and support them aswell as develop queries and reports.
See the Appendixfor sample job descriptions used forhiring for these roles.
Info-Tech Research Group 9
Role Team Function
Business Analyst
Identifies and defines DW purpose andtarget user groups. Ensures that the DW fulfills the
enterprise's strategic objectives.
Data Architect Defines data collection, transformation,distribution, and loading.
Defines the data models that are the
foundation of the DW.
InformationSystems Services
Tests DW tools. Assesses the need for expansion of the
DW.
End-User Support Allows user reporting and access. Trains end users and provides support.
Leadership andManagement
Sponsors the DW and makes it a prioritywithin the organization.
Develops project plans. Ensures that the DW remains aligned
with business needs.
-
8/2/2019 Storyboard Data Warehouse Planning Guest
10/15
DWrequirements gathering is unique plan to take an iterative
approach to collecting them
Info-Tech Research Group10
Ask users what they need from DW. This is challenging given user ignorance
of their own needs, and alone is notenough to clarify requirements.
Gather requirements iteratively throughout thedesign process that said, keep your eyes outfor scope creep.
For a more detailed guide to requirements gathering, see the Solution Set,
Overcome the Barriers to Good Requirements Management.
Info-Tech Research
DW requirements gathering isunique:
Users typically dont know theirwants and needs as DW is verynew to most users.
DW design requires much
broader, and more difficult todefine business concepts(compare Business ProcessReengineering [BPR]).
This makes requirementsgathering more important andbuilds a strong argument for using
a prototype approach to design.
Employ a project manager or BA that possesses atleast a fundamental understanding of the relevantbusiness and practices, and can ask the rightquestions to clarify unknown terms and concepts.
Ask the following questions: What functions are you trying to
perform in the business?
What type of data do you require to dothese functions? Where do you get this data today? Do you relate or integrate this data with
any other data?
http://www.infotech.com/research/ss/overcome-the-barriers-to-good-requirements-managementhttp://www.infotech.com/research/ss/overcome-the-barriers-to-good-requirements-management -
8/2/2019 Storyboard Data Warehouse Planning Guest
11/15
Data mart focused architecture approaches deliver value early,
but can be expensive over time
Info-Tech Research Group 11
Pros
Targets specificbusiness unit needs. Easy to construct. Quick time toresults.
Cons
Difficult to obtainenterprise view. Redundant datacosts. High long term ETLcosts.High long termmaintenance andsupport costs.
Independent data marts focus on delivering subject orienteddata to key user groups (e.g. sales and marketing) for
organizations that do not need an enterprise wide view.
If you are just starting out, consider beginning with singlescalable independent mart and grow it into an EDW.
Info-Tech Insight:
-
8/2/2019 Storyboard Data Warehouse Planning Guest
12/15
Appliance vendors offer superior speed, DBMS are the most cost
effective, DWsoftware (virtual appliances) strikes a balance
Info-Tech Research Group 12
Sample DatabaseManagement Systems
(DBMS)
Sample DW Appliance
Vendors
Sample DW Software
Vendors
DW Appliances: consists of bothhardware (e.g. servers) and customsoftware that are optimized (e.g.
massively parallel processing) for DW
specific tasks (e.g. query).
Virtual Appliances: are softwarebased solutions for DW that canbe deployed on a variety of
hardware configurations for
commodity hardware.
Database Management Systems(DBMS): is commodity softwarefor database management (e.g.
Oracle, Microsoft SQL, etc.) that
are used for DW, but are
typically not optimized for DW
performance.
http://www.netezza.com/http://www.teradata.com/ -
8/2/2019 Storyboard Data Warehouse Planning Guest
13/15
Data volume & transformation complexity should guide your
choice of transform/load procedures
Info-Tech Research Group 13Data Volume
#of
SourceSystems
5 TB1 TB
10
5
ETL
ELT
ETL
ELTDirect
Load
>10 source systems and >1TB of
transactional data Use ETL.
5 TB oftransactional data Use ELT.
Evaluate the transformationrequirements before deciding on aloading approach.
UseETL if the transformation rulesrequired for preparing the data arecomplex and cannot be performed using
database stored procedures.
ETL is also ideal for populating data martsthat exist on the same physicalinfrastructure as the DW, as there will beno need to move data off the DW platform
and, therefore, no network impact willoccur.
Leverage ELT when loading small datasets with relatively simple transformationlogic.
-
8/2/2019 Storyboard Data Warehouse Planning Guest
14/15
Appendix I
Business Intelligence Introduction:
Business intelligence: Core Concepts and Technologies
Data Warehousing NotesData Warehousing: ETL vs. ELTEarly TCO Analysis Cuts Data Warehousing CostsInformation Roadmap Optimizes Enterprise Data UseInformation Architecture RoadmapData Warehousing: Staffing Dictates Success
Job Descriptions:Data Warehouse ArchitectEnterprise Data ArchitectDatabase DeveloperDatabase AdministratorDatabase AnalystBusiness Requirements AnalystProject Manager
Business Intelligence SpecialistData/Data Mining SpecialistApplication Integration SpecialistUser Interface AnalystIT Security ManagerEDI SpecialistFor more see InfoTechs Job Descriptions Section
Info-Tech Research Group 14
Requirements Notes: Requirements Gathering: What Every IT
Manager Needs to Know Requirements Models: The What, When, and Why
Custom vs. COTS: Tailor Requirements Gathering for a
Elicitation Techniques Bring Requirements to the Surf
Choose the Right Elicitation Techniques to Improve Re
The Politics of Collecting Business Requirements
Business Requirements Template Functional Specifications Template Use Case Template Requirements Tracking Template Quality of Service Requirements Template
http://www.infotech.com/research/business-intelligence-core-concepts-and-technologies?nav_id=2639http://www.infotech.com/research/early-tco-analysis-cuts-data-warehousing-costs?nav_id=2639http://www.infotech.com/research/information-roadmap-optimizes-enterprise-data-use?nav_id=2639http://www.infotech.com/research/information-architecture-roadmap?nav_id=2639http://www.infotech.com/research/data-warehousing-staffing-dictates-success?nav_id=2639http://www.infotech.com/research/data-warehouse-architecthttp://www.infotech.com/research/enterprise-data-architecthttp://www.infotech.com/research/database-developerhttp://www.infotech.com/research/database-administratorhttp://www.infotech.com/research/database-analysthttp://www.infotech.com/research/business-requirements-analysthttp://www.infotech.com/research/project-managerhttp://www.infotech.com/research/business-intelligence-specialist?nav_id=2639http://www.infotech.com/research/datadata-mining-specialisthttp://www.infotech.com/research/application-integration-specialisthttp://www.infotech.com/research/user-interface-analysthttp://www.infotech.com/research/it-security-managerhttp://www.infotech.com/research/edi-specialisthttp://www.infotech.com/research/tools-and-resources/job-descriptionhttp://www.infotech.com/research/requirements-models-the-what-when-and-whyhttp://www.infotech.com/research/custom-vs-cots-tailor-requirements-gathering-for-a-tighter-fithttp://www.infotech.com/research/elicitation-techniques-bring-requirements-to-the-surfacehttp://www.infotech.com/research/choose-the-right-elicitation-techniques-to-improve-requirementshttp://www.infotech.com/research/the-politics-of-collecting-business-requirementshttp://www.infotech.com/research/business-requirements-templatehttp://www.infotech.com/research/functional-specifications-templatehttp://var/www/apps/conversion/current/tmp/scratch9186/C:/Documents%20and%20Settings/gdoherty/Local%20Settings/Temporary%20Internet%20Files/Content.MSO/Requirements%20Templateshttp://www.infotech.com/research/requirements-trackinghttp://www.infotech.com/research/requirements-trackinghttp://www.infotech.com/research/quality-of-service-requirements-templatehttp://www.infotech.com/research/quality-of-service-requirements-templatehttp://www.infotech.com/research/requirements-trackinghttp://var/www/apps/conversion/current/tmp/scratch9186/C:/Documents%20and%20Settings/gdoherty/Local%20Settings/Temporary%20Internet%20Files/Content.MSO/Requirements%20Templateshttp://www.infotech.com/research/functional-specifications-templatehttp://www.infotech.com/research/business-requirements-templatehttp://www.infotech.com/research/the-politics-of-collecting-business-requirementshttp://www.infotech.com/research/choose-the-right-elicitation-techniques-to-improve-requirementshttp://www.infotech.com/research/elicitation-techniques-bring-requirements-to-the-surfacehttp://www.infotech.com/research/custom-vs-cots-tailor-requirements-gathering-for-a-tighter-fithttp://www.infotech.com/research/requirements-models-the-what-when-and-whyhttp://www.infotech.com/research/tools-and-resources/job-descriptionhttp://www.infotech.com/research/edi-specialisthttp://www.infotech.com/research/it-security-managerhttp://www.infotech.com/research/user-interface-analysthttp://www.infotech.com/research/application-integration-specialisthttp://www.infotech.com/research/datadata-mining-specialisthttp://www.infotech.com/research/business-intelligence-specialist?nav_id=2639http://www.infotech.com/research/project-managerhttp://www.infotech.com/research/business-requirements-analysthttp://www.infotech.com/research/database-analysthttp://www.infotech.com/research/database-administratorhttp://www.infotech.com/research/database-developerhttp://www.infotech.com/research/enterprise-data-architecthttp://www.infotech.com/research/data-warehouse-architecthttp://www.infotech.com/research/data-warehousing-staffing-dictates-success?nav_id=2639http://www.infotech.com/research/information-architecture-roadmap?nav_id=2639http://www.infotech.com/research/information-roadmap-optimizes-enterprise-data-use?nav_id=2639http://www.infotech.com/research/early-tco-analysis-cuts-data-warehousing-costs?nav_id=2639http://www.infotech.com/research/business-intelligence-core-concepts-and-technologies?nav_id=2639 -
8/2/2019 Storyboard Data Warehouse Planning Guest
15/15
Appendix II Survey Demographics
Info-Tech Research Group 15
Industry