{"id":1138,"date":"2012-06-26T15:23:13","date_gmt":"2012-06-26T19:23:13","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=1138"},"modified":"2017-11-06T15:07:07","modified_gmt":"2017-11-06T20:07:07","slug":"support-for-star-schema","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/data-transformation2\/support-for-star-schema\/","title":{"rendered":"Support for Star Schema"},"content":{"rendered":"

\"\"<\/a>Star schema<\/a> is the simplest and most common database modelling structure used in traditional\u00a0data warehouse\u00a0paradigms<\/a>. The schema resembles a constellation of stars — generally several bright stars (facts) surrounded by dimmer ones (dimensions) where one or more fact tables reference different dimension tables.<\/p>\n

Star schemas are designed to improve ease-of-understanding, and retrieval performance, by minimizing the number of tables to join when materializing a transaction.<\/p>\n

 <\/p>\n

Star Schema Characteristics <\/b><\/p>\n

    \n
  1. The schema consists of a Fact table (in this case, Sale_Fact<\/i><\/b>) whose foreign keys are inherited from the Dimension tables<\/li>\n
  2. Each foreign key values is a concatenation of the corresponding foreign key value in the Dimension tables, and uniquely identifies individual records in the Fact table<\/li>\n
  3. Many different Dimension tables may reference the same Fact, and do not usually reference any other tables.<\/li>\n<\/ol>\n

    Star Schema Benefits<\/i><\/b><\/p>\n\n\n\n\n\t\n\t\n\t\n\t\n\t
    Simple Structure<\/td>The schema is easy to understand.<\/td>\n<\/tr>\n
    Query Performance<\/td>Queries run faster against a Star Schema (than an OLTP) because it has fewer tables and a clear join path. This design feature gives consistent query results.<\/td>\n<\/tr>\n
    Load Performance<\/td>This schema reduces the time required to load large batches of data into the database. The impact of load operation is reduced by defining facts and dimensions, and separating them into different tables.<\/td>\n<\/tr>\n
    Built-in Referential Integrity<\/td>A Star Schema is forced to maintain the referential integrity of the data by using primary andforeign keys. Primary keys in the dimension table become foreign keys in the Fact table to link each record across dimension and Fact tables.<\/td>\n<\/tr>\n
    Efficient Navigation through Data<\/td>A navigation through data is efficient in Star Schema because dimensions are joined through Fact tables. To construct an efficient query, you can browse a single-dimension table in order to select attribute values.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n

    To understand why the star schema is so popular, consider an example database from a store chain where sales-related data in a central fact table is linked by a primary key “Id” column in data, store, and product dimension tables:<\/p>\n

    \"Star<\/a><\/p>\n

    The Fact_Sales table has a three-column (compound) primary key (Date_Id, Store_Id, Product_Id) and a non-primary Units_Sold column attribute that can be used in calculations. This structure allows queries on the Fact_Sales table (joined to the dimensional table) to retrieve specific information, such as the number of TVs sold by brand and country in a given year.<\/p>\n

    So what does this have to do with IRI Software?<\/p>\n