![Page 1: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/1.jpg)
An Agile Approach to Building & An Agile Approach to Building & Managing Data WarehousesManaging Data Warehouses
A Briefing by WhereScape
Mary Edie Meredith, Sr. Technical AnalystMary Edie Meredith, Sr. Technical Analyst- [email protected] [email protected]
![Page 2: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/2.jpg)
2
Why do Data Warehouse Projects struggle ?
Gartner notes that over 50% of data warehouse projects fail or go wildly over budget
1. Inaccurate business requirements - #1 problem IDC
2. Poor development productivity
3. Slow development cycles
4. High cost of resources
5. High TCO
6. Poor documentation – usually the last thing that is considered &
never up to date.
7. Poor data quality
8. HIGH RISK
![Page 3: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/3.jpg)
3
Where did they go wrong? – one real problem is the “Big Bang” project approach
“Incremental Incremental Data Warehouse Development –
The Only Way to Fly” Bill Inmon, Jan 8, 2009, (BeyeNetwork)– “There are many reasons the ‘Big Bang’ approach doesn’t work … “but at the heart is inability of the
development analyst to gather requirements in the manner prescribed by the SDLC”– “End users of analytical systems need to know what the possibilities are before they can articulate the
requirements.”
The goal is NOT to build a Data Warehouse, but rather…– Deliver real valueDeliver real value– Create a solution that is adaptable becauseCreate a solution that is adaptable because responding quickly to change brings competitive advantage
– Create a process Create a process to develop and maintain the solution that is trustworthy and sustainable
![Page 4: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/4.jpg)
4
How would agile proponents approach the problem? From the agile manifesto: //agile
• Early, frequent, and continuous test and delivery of valuablevaluable working working software (every 2 wks-2mos).
• Welcome changing requirementsWelcome changing requirements, even late in development. • Business people, developers work together daily work together daily throughout the project.• Build projects around motivated individualsmotivated individuals. Give them the environment and
support they need, and trust them to get the job done. • The most efficient, effective method of conveying information to and within a
development team is face-to-faceface-to-face conversation. • Continuous attention to technical excellence technical excellence and good design good design enhances
agility. • Simplicity--the art of maximizing the amount of work not donemaximizing the amount of work not done--is essential. • At regular intervals, the team reflects the team reflects on how to become more effective, then
tunes and adjusts its behavior accordingly.
![Page 5: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/5.jpg)
5
What is uncomfortable about this approach?
• The further out in time, the less a project team can say about what will be accomplished.
• An agile approach can break the rules.– Agile implementers sometimes wrongly assume you can break ANY rule.– Shortcuts do not equal Quality Pragmatism
• Classic trade-offs for project managers - Schedule/ Scope/ Resources/ Quality – agile leaves little wiggle room.
• Does not lend itself to outsourcing, distributed teams.• Having a close working relationship with business users does not solve
the difficulty determining requirements.
And ….
![Page 6: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/6.jpg)
6
If I could deliver something meaningful in weeks
DON’T YOU THINK I WOULD HAVE, ALREADY.
![Page 7: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/7.jpg)
7
Agile Approach Versus Traditional Approach
Docs?
![Page 8: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/8.jpg)
8
What really works using agile “The WhereScape Way”
• A Governance structure – Strategy, Architecture, Roadmap, Standards– Goals, sponsors, infrastructure, data governance ….
• New Development Paradigm for delivering data - RED– ETL tools are great for moving data, but RED can do DW part better.– Integrated Development using one metadata driven tool.– Do the data delivery in the database.– Incorporate Business Rules into data delivery process
• Iterative workshops with business users– Use REAL DATA for flushing out requirements (RED enables this)– Track all issues discovered, especially data quality
![Page 9: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/9.jpg)
9
Agile in Operation
• Integrate analysis, design, creation, data delivery, deployment, iteration
• Useful even if you just need to provide the presentation layer
• Feedback from business users on live data part of the development process
Live Data Workshop
Business UserSessions
![Page 10: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/10.jpg)
10
Speeding up the development by leveraging metadata, embedding best practice methods
dim_customer_key
dss_update_time
![Page 11: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/11.jpg)
11
Data Warehouse Scenario – Build a Sales Fact
![Page 12: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/12.jpg)
12
Star schema creation scenario – start with load table
SourceWarehouse
Oracle, SQL/Server, Teradata, DB2Oracle, SQL/Server, Teradata, DB2Native RDBMS, ODBC accessible, FilesNative RDBMS, ODBC accessible, Files
![Page 13: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/13.jpg)
13
RED Browser Mode
Metadata
Results
ActionsDrag and Drop Target Area
BrowsingConnections
Choose connection and filtering
![Page 14: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/14.jpg)
14
For the Teradata shop -
![Page 15: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/15.jpg)
15
Star schema creation scenario – start with load table
SourceWarehouse
Oracle, SQL/Server, Teradata, DB2Oracle, SQL/Server, Teradata, DB2Native RDBMS, ODBC accessible, FilesNative RDBMS, ODBC accessible, Files
![Page 16: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/16.jpg)
16
Drag and Drop Example: load source data
![Page 17: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/17.jpg)
17
Drag and Drop Example: load table properties
![Page 18: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/18.jpg)
18
Drag and Drop Example: load table storage mapping
![Page 19: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/19.jpg)
19
Drag and Drop Example: load table “create and load”
metadata
![Page 20: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/20.jpg)
20
Drag and Drop Example: load table results
create
generated load script execution
![Page 21: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/21.jpg)
21
Drag and Drop Example: load table results
create
generated load script execution
Display Data
![Page 22: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/22.jpg)
22
Stage table creation scenario – the stage table
SourceWarehouse
Foreign dimension Keys, lookups
Source table join
![Page 23: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/23.jpg)
23
Stage table: start with load_order_header (Drag and Drop)
![Page 24: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/24.jpg)
24
Add columns from load_order_line (Drag and Drop)
Load_order_headerColumn metadata
![Page 25: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/25.jpg)
25
Add columns from load_order_line (Drag and Drop)prevents duplicate column names
Load_order_headerColumn metadata
![Page 26: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/26.jpg)
26
Add FK cols to Stage Table – Drag and Drop dim_*
Drag and drop Dimension table keys
![Page 27: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/27.jpg)
27
Column Metadata easily altered
![Page 28: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/28.jpg)
28
Column Transformations – Business Rules, Computed Fields, String Manipulation, Type Conversion, Null handling,…
![Page 29: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/29.jpg)
29
Create the Stage Table (right click object)
![Page 30: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/30.jpg)
30
Create the update procedure (object Properties)
![Page 31: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/31.jpg)
31
…then select Procedure Type
![Page 32: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/32.jpg)
32
… then specify the Join statement
add appropriate clauses
Numerous joins supported
![Page 33: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/33.jpg)
33
…indicate the business key to identify SK in DimensionPrompts if column names match
![Page 34: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/34.jpg)
34
…indicate the join column if names are different
![Page 35: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/35.jpg)
35
Procedure is created, compiled. Execute Procedure.
![Page 36: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/36.jpg)
36
Display Data
![Page 37: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/37.jpg)
37
Fact table creation scenario – Sales Fact table
SourceWarehouse
![Page 38: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/38.jpg)
38
Create the Fact Table from the Stage table
![Page 39: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/39.jpg)
39
Metadata leveraged to create the code
Dimension tables are created with
“zero” row for unknowns
Join metadata
Transformation for quantity column
![Page 40: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/40.jpg)
40
Auto generated stored procedure code …
• Keeps all the data movement in the database• Provides consistent variable naming, coding best practices• Utilizes custom parameters you can embed in metadata• Includes error checking and rollbacks• Preserves the metadata for easy modification• Can augment with custom procedures• Includes features best practices for various object types
o Can handle slowly changing dimensions (all three types)o Procedure provided to populate and update time dimensiono Handles code for surrogate keys, update and life-span dateso Creates Unknown Row for each dimension tableo Accounts for missing dimension key matches in source data
Let’s advance developers can skip the mundane
Allows less experienced developers to be productive
![Page 41: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/41.jpg)
41
Generated Procedures with version compares
![Page 42: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/42.jpg)
42
Next Step – Business User review
Easy vehicles to show this to Business users:
Output table data to Excel
Stress test with SSAS cube
![Page 43: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/43.jpg)
43
Create a SSAS Cube for Business User EvalDrag and Drop Fact to OLAP Cube target
Creates OLAP dimensions
Creates OLAP measure group
![Page 44: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/44.jpg)
44
Create a SSAS Cube for Business User EvalSlice and Dice in Analysis Services
![Page 45: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/45.jpg)
45
Capturing Metadata - Lineage information
![Page 46: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/46.jpg)
46
Leveraging Metadata: Reports
![Page 47: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/47.jpg)
47
Ready to Deploy
![Page 48: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/48.jpg)
48
Scheduler to manage objects and data flow
Run in parallel
![Page 49: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/49.jpg)
49
Scheduler to manage objects and data flow
Run in parallel
![Page 50: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/50.jpg)
50
Diagrammatical View Example: Update Job
![Page 51: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/51.jpg)
51
Application Files to promote to QA and Production
![Page 52: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/52.jpg)
52
Leveraging Metadata: Auto Producing Documentation
![Page 53: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/53.jpg)
53
User Documentation
![Page 54: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/54.jpg)
Where RED fits
![Page 55: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/55.jpg)
55
"WhereScape promised a lot and the product has delivered. We are very happy with the amount of time it is saving us in development, as well as the documentation it is producing and the built-in scheduler. I am very happy with the purchase.“
"We estimate the development lifecycle is 20-25% of what it was previously when we were hand-codingWe estimate the development lifecycle is 20-25% of what it was previously when we were hand-coding."."
Dan Mosher, Director of Enterprise Data Warehousing
![Page 56: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/56.jpg)
“WhereScape RED offers IPC a sophisticated Lifecycle Methodology sophisticated Lifecycle Methodology that guides us through the process of building our data warehouse. RED
creates integrated database objects such as tables, indexes, procedures, etc; produces standard yet customizable T-SQL code and
auto-generated user and technical documentation.”
Maylee Sanchez, Sr. Database Administrator
![Page 57: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/57.jpg)
Some WhereScape Customers
57
![Page 58: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/58.jpg)
58
ConclusionConclusion
• Build Your Data Warehouse Solution
– Way Faster– Way Cheaper– Ready for Change
• Get Full Documentation– For Users– For Techies
• And DO IT THE AGILE WAY
![Page 59: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/59.jpg)
60
Tools and Reports
![Page 60: An Agile Approach to Building & Managing Data Warehouses A Briefing by WhereScape](https://reader036.vdocuments.us/reader036/viewer/2022062301/56813adb550346895da3235f/html5/thumbnails/60.jpg)
61
Additional CUBE Features
• Can add MDX calculations to the cube metadata for calculated members
– Specify font, foreground/background colors, boldness, display format, non-empty behavior, order number, client visibility
• Canned MDX calculations – Month/Year to date, Moving Qtr/Year, same month previous year, previous year to date.
• Can specify Post Create or Post Update XML/A Scripts– Allows features built outside of RED to be added to the Schedule cube processing (e.g. security roles added,
perspectives, translations )
• Cube properties include – Processing modes for Cubes (Regular, Lazy Aggregation) and priority– OLAP dimension processing (together or separately)– Cube visibility to client applications– Default Measure and estimated rows
• Can optionally drop Dimensions, Measure Groups, Cubes, and Cube databases from within RED.
• Can manage KPIs, partitioning, and processing for measure groups