Data and Info. Management Chapter 7 continued (Marist College Eitel Lauria)
Operational queries typically process much smaller or larger amounts of data than analytical queries?
smaller
Data mart
A data store based on the same principles as a data warehouse, but with a more limited scope
Metadata: The Yellow Pages
A directory with data about the institutions
Top-Down Approach advantages
A truly corporate effort, an enterprise view of data Inherently architected - not a union of disparate data marts Single, central storage of data about the content Centralized rules and control May see quick results if implemented with iterations
One DW architecture always exists, but there are two broad types, which are what? And you should always choose the what approach as it is the best way to achieve your goals of a what DW?
Ad hoc or Planned planned well functioning, extendable, robust and maintainable
Data Transformation: Perform 8individual tasks:
Clean Standardization Combine Purging and separating out Sorting and merging Assignment of surrogate keys
Bill Inmon is a co-creator of the what?
Corporate Information Factory (hub-and-spoke architecture)
Dependent data mart: Does or does not have its own source systems?
Does not
3 parts of Refresh cycles
Extract the changes to the source data Transform the data revisions And feed the incremental data revisions on an ongoing basis
How to change database owner?
In a file in System Stored Procedures folder Exec sp_changedbowner 'sa'
Tools for data extraction
Purchasing outside tools - Developing in-house programs
How many subjects does a data mart have? Does it have more data sources than a data warehouse?
Single No
Independent data mart
Stand-alone data mart, created in the same fashion as the data warehouse
ETL infrastructure
The infrastructure that facilitates the retrieval of data from operational databases into the data warehouses
Data Loading: Two distinct groups of tasks
The initial loading of the data into the data warehouse Refresh cycles
Information Delivery Component: Who are the users?
The novices, the casual users, the business analysts, and the power users
Is the data in a data warehouse stable?
Yes
Information Delivery Component: Are there different methods of information delivery? If so, what are they?
Yes Ad hoc reports, complex queries, multidimensional analysis, statistical analysis, EIS feed, data-mining applications
Data Warehouse (The Data Storage Component): Is there a separate repository? If so, why?
Yes To keep large volume of historical data for analysis To keep the data in structures suitable for analysis
Data Transformation Results:
a collection of integrated data that is cleaned, standardized, and summarized
Extract the source data into what or what or what?
a group of flat files, or a data-staging relational database, or a combination of both
operational databases are referred to as what oriented?
application-oriented.
analytical databases can contain both detailed and summarized data or just one of the two? That data is physically or digitally stored?
both detailed and summarized data physically
Operational data typically represents the current or past state of affairs in the real world, while analytical data can represent both the current situation and snapshots from the past or just one of the two?
current both the current situation and snapshots from the past
Dependent data mart: The data comes from the what?
data warehouse
Operational data typically reflects detailed or not detailed data?
detailed
Data makeup differences are what?
differences in the characteristics of the data that comprises the operational and analytical information.
Functional differences
differences in the rationale for the use of operational and analytical data
Centralized Data Warehouse: Seeks to overcome the what? Highly or not highly variable with many or not many individual approaches?
limitations of previous architectures Highly Many
The metadata is the source of information for the what?
management module
Information Delivery Component: Information delivery what? Examples?
mechanism Online, internet, intranet, e-mail, mobile
Management and Control: Monitor the what into the what and from there into the what?
movements of data staging area data warehouse storage
In contrast to the constituency of operational data, analytical data is used by a narrower or larger set of users for decision-making purposes?
narrower
Eliminating redundancy is more critical or not as critical in analytical databases as it is in operational databases?
not as critical
Data Warehouse (The Data Storage Component): A typical data warehouse periodically retrieves what from the what?
selected analytically useful data operational data sources
Analytical databases are referred to as what oriented?
subject-oriented
Data Warehouse (The Data Storage Component): The data warehouse is sometimes referred to as the what? To indicate the fact that it is a destination for the data from the what?
target system source system
Technical differences
the differences in the way that operational and analytical data is handled and accessed by the DBMS and applications
analytical information
the information collected and used to support decisions involving tasks that require data analysis.
Data in operational systems is regularly updated by who?
the users
ETL includes the following task: Loading the what into the what?
transformed and quality assured data into the target data warehouse
The different types of DW architecture: The basic types are what?
Independent Data Marts Dependent Data Marts / Hub and Spoke Bus Architecture Centralized Data Warehouse (no dependent data marts) Federated With big data, Data Lakes
Independent Data Marts: What were developing solutions into what? Is there a program / enterprise perspective?
Individual projects functional silos No
The database in a data warehouse must be open to what like what?
Must be open to different tools RDBMSs or MDDBs
Focus of a data mart: Is often narrower or wider than organization-wide?
Narrower
Independent Data Marts: Are there conformed dimensions?
No
DW Architecture: Before deciding to build a data warehouse, you need to ask:
Top-down or bottom-up approach? Enterprise-wide or department? Which first - data warehouse or data mart? Build pilot or go with a full-fledged implementation? Dependent or independent data marts?
4 Types of Metadata
Operational Metadata Extraction and Transformation Metadata End-user Metadata
Bill Inmon
The "Father of Data Warehousing" a known expert, speaker, and author on data warehousing
Management and Control: Coordinate the what and what? Control the what and what into the data warehouse storage? Moderate the what to the users?
services and activities data transformation and the data transfer information delivery
Data Staging: Provide a place and an area with a what to what for what and what?
set of functions clean, change, combine, convert, duplicate, and prepare source data for storage and use in the data warehouse
Operational systems have a shorter or longer time horizon of data than analytical systems?
shorter
Independent data mart has its own what and what?
source systems and ETL infrastructure
Is a data mart bigger than a data warehouse? Is its implementation time not as long as a data warehouse's?
No Yes
Top-Down Approach Disadvantages
Takes longer to build even with an iterative method High exposure/risk to failure Needs high level of cross-functional skills High outlay without proof of concept
DW front-end (BI) applications (information delivery component): Used to provide what to the data warehouse for users who are engaging in what use?
access indirect use
ETL includes the following tasks: Extracting what from the operational data sources? Doing what to such data so that it what?
analytically useful data Transforming conforms to the structure of the subject-oriented target data warehouse model (while ensuring the quality of the transformed data)
Data Extraction: Deal with one or numerous data sources?
numerous
Management and Control: Sit where compared to all the other components?
on top of all the other components
The database in a data warehouse must be what?
open
Firstly, a DW architecture basically what?
outlines how the DW components fit together
