150 likes | 292 Views
Building Dimensional Databases Models. Data Warehousing Technologies Dr. Ilieva I. Ageenko. Model for Target Database. The target database for the central DW is almost always relational, due to it’s potentially large size
E N D
Building Dimensional Databases Models Data Warehousing Technologies Dr. Ilieva I. Ageenko
Model for Target Database • The target database for the central DW is almost always relational, due to it’s potentially large size • Target database for data marts depending on the size of the database can be either: • Relational databases • Multidimensional databases • Hybrid databases
Relational Databases • RDBMSs are generally used for central DW and for large data marts. • They have no db size contraints - Scalability up to multi-terabytes • Detailed data and aggregated data can be stored in the same database • Support open systems standards such as: SQL, ODBC, OLE DB • Portable among many platforms • Supported by many third-party vendors
Multidimensional databases • MDB is a data structure with three or more independent dimensions • Do not use relational tables • Store data in the form of multidimensional array • Store aggregated data • Recommended for relative small data marts (less then 50 GB) • Requires proprietary database solution
Multidimensional databases • High performance, sophisticated multidimensional calculations • OLAP optimization. Not constrained by SQL limitations • Efficiency of data storage (less space than RDBM) • Fast browsing through members of dimensions
Hybrid databases • Hybrid databases incorporate both relational and multidimensional technologies • Combination controlled by OLAP server • RDBMS used for detailed data stored • MDB used for fast read/write OLAP analysis and calculations • OLAP server routes queries to either RDBMS or MDB
Multidimensional Model Each point in the cube contains measurements for a particular combination of: Product, Market and Time Time M a r k e t DIMENSIONS Product
Multidimensional Model Support drill-down and slice and dice Multidimensional data cubes OLAP tools Data Warehouse Operational Systems METADATA
Concepts • Dimensions • Attributes • Attribute elements • Metrics • Consolidations • Qualities
Concepts • Dimensions • General categories of data • Attributes • Categories within a dimension • Attribute elements • The unique entries that are stored for each attribute in the DW • Metrics • User defined analytical calculations of data
Structure of Dimensional model • Fact Tables store numerical measurements • Dimension Tables store the textual descriptions of the dimensions Product Dimension Sales Fact product_key description brand category Time Dimension time_key product-key store_key dollars_sold units-sold dollars_cost time_key day_of_week month quarter year holiday_flag Store Dimension Numerical store_key store_name address floor_plan_type Descriptions
Structure of Dimensional model • Asymmetric Star Join Schema with a central Fact table and other dimensional tables Product Dimension Sales Fact product_key description brand category Time Dimension time_key product-key store_key dollars_sold units-sold dollars_cost time_key day_of_week month quarter year holiday_flag Store Dimension store_key store_name address floor_plan_type
Design Process • #1- Understand the business process • #2- Select the grain of the fact table in each business process • The grain of the fact table is important because it determines the dimensionality of the DW. • #3- Select the dimensions that will apply to each fact table record • #4- Select the measured facts that will populate each fact table record
The Grocery Store Schema (Ralph Kimball) Sales Fact Product Dimension Time Dimension *Dimensions* time_key product-key store_key promotion_key *Facts* dollars_sales units_sales dollar_cost customer_count product_key product attributes ../ time_key time attributes ... Store Dimension Promotion store_key store attribues ... promotion_key promo attributes ...
Normalization • The fact table in a dimensional schema is naturally highly normalized • Efforts to normalize any of the tables solely in order to save disk space are a waste of time • The dimension tables must not be normalized but should remain as flat tables • Normalized dimension tables destroy the ability to browse