SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Data Warehousing Designing the data warehouse

tonya avatar
tonya
24 diapositives · 389 Views · Sep 09, 2014

Join Instructor Peter Rawsthorne for an extensive review of data warehouse design principles. This class covers essential topics such as TSQL, project management, and database design, comparing OLTP and DSS while discussing normalization, classic SDLC, star and snowflake schemas. Learn how to effectively build a corporate data model, transform it, and create relationship artifacts. The course examines key considerations for technology selection, data volume, and transformation scheduling, providing practical exercises to reinforce your understanding of data warehousing.

#data warehousing designing the data warehouse #data warehousing designing the data warehouse ppt #powerpoint #presentation
Data Warehousing Designing the data warehouse - slide 1
01 / 24Data Warehousing Designing the data warehouse
1 / 17

Télécharger la présentation

Data Warehousing Designing the data warehouse
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Data WarehousingDesigning the data warehouse Instructor: Peter Rawsthorne

  2. Class 2 review • Go to class2.ppt • Completion of install • TSQL • Managing the Project • Database design of the data warehouse

  3. OLTP vs. DSS • OLTP • Normalization • Entity Relationships • Classic SDLC • DSS • Star Schema • Snowflake Schema • Very Iterative

  4. Prerequisites • The OLTP data model • Technology selection • Sizing the data warehouse • bytes per row • number of rows per table (fact & dimensions) • time intervals • Collecting informational requirements

  5. Technology selection • the volume of data to be accommodated, • the speed with which data is needed, • the history of the organization, • which level of data is being built, • how many users there will be, • what kind of analysis is to be performed, • the cost of technology, etc.

  6. Building the data model • The corporate data model • Other sources • The mid-level data • Transforming the data model • The first iteration • The system of record • Transforming the data • Scheduling the transformation

  7. The corporate data model • the major subjects of the enterprise, • the relationships between the subjects, • the creation of an ERD (entity relationship diagram), • for each major subject area: • the keys(s) of the subject, • the attributes of the subject, • the subtypes of the subject, • the connectors of one subject area to the next, • the grouping of attributes.

  8. The corporate data model

  9. Other sources • functional decomposition, • data/process matrices, • data flow diagrams, • state transition diagrams, • HIPO charts, • pseudocode, et al.

  10. mid-level model

  11. Each subject has a mid-level

  12. Transforming the data model • the removal of purely operational data, • the addition of an element of time to the key structure of the data warehouse if one is not already present, • the addition of appropriate derived data, • the transformation of data relationships into data artifacts, • accommodating the different levels of granularity found in the data warehouse, • merging like data from different tables together,

  13. Removing operational data

  14. Adding the element of time

  15. Adding derived data

  16. Creating relationship artifacts • An artifact of a relationship is merely that part of a relationship that is obvious and tangible at the moment the snapshot of data is made for the data warehouse.

  17. MOC See P02_DWS.PPT power point presentation

  18. Exercise • 30 minutes • Break into groups of three • Use on of the existing SQL7 databases and create a star schema logical design using this database • Be prepared to present your design.

  19. The first iteration • HOW MUCH DATA TO LOAD? • THE FIRST ITERATION SHOULD CONTAIN DATA THAT IS LARGE ENOUGH TO BE MEANINGFUL AND SMALL ENOUGH TO BE QUICKLY DOABLE. • FISHING IN THE RIGHT POND • Classic functional areas; • finance, • accounting, • sales, • marketing • SELECTING A SUBJECT AREA • Marketing campaign success • Inventory turn-over

  20. System of Record • The system of record is the data that is the: • most complete, • most accurate, • most timely, • has the best structural conformance to the data model, and • is nearest to the point of operational entry.

  21. The identification of the system of record is the most complex activity in the building of the first iteration of data warehouse development. There are MANY circumstances that must be accounted for in the specification of the system of record: multiple sources of data, no sources of data under certain cases, reformatting of data, summarization of data, conversion of data, recalculation of data, alteration of key structure, restructuring of data attributes, conditional merging of data, and so forth. System of Record

  22. Transforming the data

  23. Scheduling the transformations

  24. Scheduling the transformations • The frequency of execution of transformation depends on many things, such as: • how much data will be transformed, • whether a delta file is being used, • the complexity of the transformation, • the business need to see the updated data warehouse, • the availability of the operational data, • how frequently system checkpoints are being done, • the amount of summarization and aggregation that needs to be done, and so forth.