Relational Database Support for Data Warehouses is the third course in the Data Warehousing for Business Intelligence specialization. In this course, you'll use analytical elements of SQL for answering business intelligence questions. You'll learn features of relational database management systems for managing summary data commonly used in business intelligence reporting. Because of the importance and difficulty of managing implementations of data warehouses, we'll also delve into storage architectures, scalable parallel processing, data governance, and big data impacts.
Storage Architectures video lecture
Loading...
Из курса от партнера University of Colorado System
Relational Database Support for Data Warehouses
408 оценки
University of Colorado System
408 оценки
Курс 3 из 5 — Specialization Data Warehousing for Business Intelligence
Из урока
Physical Design and Governance
Module 5 finishes the course with a return to conceptual material about physical design technologies and data governance practices. You will learn about storage architectures, scalable parallel processing, big data issues, and data governance. After this module, you will have background about conceptual issues important for data warehouse administrators.
Познакомьтесь с преподавателями
Michael ManninoAssociate Professor
Business School, University of Colorado Denver
Welcome to Lesson 1 of Module 5 on Physical Design and
Governance of Data Warehouses.
Let's have some fun learning about bitmaps and data warehouse appliances.
I'm going to start with an important conceptual question
that I want you to think about throughout this lesson.
What are the differences between data warehouse appliances and
data warehouse cloud solutions?
Module five finishes the course with a return to conceptual material.
About physical design technologies and data governance practices.
Lesson one covers basic storage architectures and
important indexing technique in packaging of data warehouse technology solutions.
The coverage in this lesson is conceptual, so
your learning should readily apply across enterpriseDB messes and data warehouses.
You have three learning objectives in this lesson.
You should compare and contrast basic physical architectures for
implementing data warehouses.
You should explain components of a bitmap join index, an important storage structure
for efficient processing of data warehouse queries.
Finally, you should explain features of data warehouse appliances.
Important packaging of hardware and software components by by DMS vendors.
Several storage technologies have been developed to provide multi-dimensional
data capabilities.
The storage technologies support online analytic processing, or OLAP for short.
A generic name, applied to business intelligence capabilities, for data cubes.
Multidimensional OLAP, or MOLAP, the original storage architecture for
data warehouses, directly manipulates stored data cubes.
MOLAP systems provide high performance, because of optimization for
queries, using precomputed data cubes.
Even with techniques to deal with sparsity
Molap engines can be overwhelmed by the size of data cubes.
This data explosion problem limits the size of data in cubes that Molap engines
can manipulate.
In a relational Olap, or Rolap approach data cubes are dynamically
constructed from fact and dimension tables as well as from materialized views.
Typically only a subset of a data cube must be constructed
as specified in a user's query.
ROLLUP provides a number of well-developed storage and
optimization technologies of parallel query execution and partitioning as well
as data warehouse specific technologies for materialized view processing.
Because of tradeoffs between MOLAP and ROLAP, hybrid OLAP, or
HOLAP, has been developed to combine ROLAP and MOLAP.
HOLAP allows a data warehouse to be divided between relational storage of fact
and dimension tables in multi-dimensional storage of summary data cubes.
A whole app system can combine data from the rollups managed data and
the MOLAP managed data.
Despite the conceptual appeal of HOLAP, complexity limits its usage with
a DBS vendor required to provide two storage engines, and
processing to combine storage engines.
Indexes support efficient query response with overhead for database modifications.
A bitmap index supports fast query response for
columns with relatively few values, such as province name and tax filing status.
Bitmap indexes require considerable overhead to update.
So Bitmap Indexes are much better for data warehouses than operational databases.
A Bitmap contains a string of bits that is zero one values with one bit for
each row or table.
In this example,
the first bit position containing one indicates that the first row contains USA.
The second bit position containing the 1 indicates the second row contains USA,
and the third bit position containing a 0 value
indicates the third row does not contain USA.
The DB Mesh storage engine provides an efficient way
to convert a position in a bitmap into a physical address of a row.
Let's look at sample Bitmap indexes to clarify your understanding.
These tables depict a Bitmap column index for a sample SSCustomer table.
The to the Bitmap is 12 positions
because there are 12 rows in a sample SSCustomer table.
For the first index row you should verify that the rows one, two, five,
eight and 12 of the SSCustomer table have USA for the cust country value.
If a query contains a condition on cust country equal USA the storage
engine can use the bitmap index to provide efficient access to the SSCustomer table.
For data warehouse queries, bitmap join indexes can be especially useful for
star queries involving a fact table and related dimensional tables.
A join bitmap index can be defined for a join column such as cusnum or
a non-join column such as cuscountry.
This tables depict a bitmap join index for the cust country column in
the SSCustomer table to related rows in a sample SS sales table.
The length of the bitmap is 24 bits
because there are 24 rows in the sample SSSales table.
A one value in a bit map.
Indicates that a parent row containing the column value
joins with a child table in a specified bit position.
For example, a 1 in the first bit position of the USA row of the join index,
means that an SSCustomer row with a USA value
joins with the first row of the SSSales table.
Enterprise DBMSs support fast query response using bitmap join indexes.
Bitmap indexes on multiple columns can be combined with intersection operation
before accessing fact tables.
Data warehouse appliances provide pre-packaged solutions.
For data warehouses containing various storage technologies and
optimization methods.
A data warehouse appliance is a combination of hardware and
software components for rapid deployment and transparent operation.
The components typically include a DBMS,
server, hardware, storage devices in an operating system.
Early appliances emphasize proprietary components.
But the trend now is now for and open source operating systems.
Data warehouse appliances promise to increase performance,
reduce maintenance cost and improve scalability.
Most appliances provide parallel data processing and
other performance enhancements.
To improve both query and refresh processing.
Performance improvements are partially due to dedicated components and tuning for
data warehouse processing without compromise for other types of processing.
Vendors have designed data warehouse appliances for rapid deployment and
transparent operation organizations who reported lower staffing cost
due to less performance tuning and monitoring.
Vendors had designed data warehouse appliances for
scalability with incremental addition of components as loads increase.
Data warehouse appliances have some potential disadvantages.
Using a data warehouse appliance often means
redeploying a data warehouse to a new environment.
Migration costs are potential problem, although data warehouse appliance vendors
provide tools to reduce data migration difficulties.
Query migration may also be required depending on the level of proprietary SQL
used in the current environment.
Fixed cost, that is the purchase and
lease of data warehouse appliances can be considerable.
Although these costs must be weighed against cost reductions for
improved performance and lower staffing cost.
Data warehouse appliance vendors emphasize lower cost of ownership,
balancing fixed cost against lower operating cost.
The market for data warehouse appliances provides both complete solutions
with both hardware and software components.
In partial solutions, sometimes known as software appliances,
with software components only.
Complete solutions are mostly offered by DBMS vendors,
sometimes in cooperation with particular hardware vendors.
For example, Microsoft provides solutions for both HP and
Dell servers packaged with SQL server.
Partial solutions are special purpose to analytical engines for
data warehouse processing, especially data cube operations.
Some of the partial solutions use different database models.
Such as column and
grid representation, along with an emphasis on main memory processing.
Partial solutions an be combined with commodity hardware
to provide complete solutions for data warehouse processing.
Lesson one began module five with conceptual material
about fiscal design technologies for data warehouses.
You learned about three storage architectures, MOLAP, ROLAP, and
HOLAP, bitmap indexes supporting queries combining fact and dimension tables,
and data warehouse appliances providing prepackaged solutions of hardware and
software.
This conceptual material applies across enterpriseDB messes.
In answer to the opening question, enterprise vendors package data
warehouse components into appliances to simplify deployment by organizations.
An organization must provide infrastructure and
staff to host data warehouse appliances, or outsource these service.
In contrast cloud solutions for data warehouses involve external hosting
providing economies of scale crossed organizations.
Generally the same product features are available in data warehouse appliances and
cloud services.
The choice between a data warehouse appliance and cloud solution.
Involves different cost structures and levels of control.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
