1 / 15

Building Dimensional Databases Models

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

anneke
Download Presentation

Building Dimensional Databases Models

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Building Dimensional Databases Models Data Warehousing Technologies Dr. Ilieva I. Ageenko

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. Multidimensional Model Support drill-down and slice and dice Multidimensional data cubes OLAP tools Data Warehouse Operational Systems METADATA

  9. Concepts • Dimensions • Attributes • Attribute elements • Metrics • Consolidations • Qualities

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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 ...

  15. 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

More Related