M87systems Corporation Logo

M87 Galaxy

M87 Galaxy
 
Services
 

Star Pattern is a database design methodology where the information to be modeled is divided into Facts and Dimensions.

Dimensions provide ways of grouping information for analysis. Groups are defined in common business terms such as Time, Products, Stores, Customers etc.

Facts are related to the dimensions by a key. By selecting a key value for each of the dimensions a specific row, or series of rows is available from the fact table. Facts are details which are summarized in some manner, and grouped according to the dimensions.

Dimensions often have levels to reflect a hierarchy within in the dimension. Hierarchies represent some method of grouping information for management purposes.

Consider a department store which may have Categories like Clothing, House Wares, and Sporting Goods. A Category of clothing may have Sub Categories of Men's Wear, Ladies Wear, and Children's Wear. Within a Sub Category of Men's Wear it may be further broken down into Items like jeans, slacks, and dress shirts. Items may then be further broken down to SKU's or individual sellable items, like Levi Jeans, blue, straight leg, 32 X 28.

The different levels of Category, Sub Category, Item, and SKU allow analysis of products by grouping them in ever increasing levels of detail.

This kind of Product hierarchy can be depicted as a such:

ProdKey

Level (Total)

           
ProdKey

Level (Category)

Category          
ProdKey

Level (Sub Category)

Category Sub Category        
ProdKey

Level (Item)

Category Sub Category Item      
ProdKey

Level (SKU)

Category Sub Category Item SKU Colour Size

The Level attribute is used to define what level in the hierarchy the row reflects. At the bottom of the hierarchy where the most detail is available (SKU), all columns have meaning.

As we move to the Item level, SKU, Colour and Size have no meaning because they are attributes of SKU.

At the Sub Category Level Item no longer has meaning, at Category level, the Sub Category has no meaning, and finally Total represents all products.

The different level of rows in the Product dimension would point to different rows in the Fact table, which provide details for the indicated level. Using this approach, information is pre-aggregated to avoid having to read all information from the detail level to arrive at a calculation for higher level management of Products.

A large department chain may have over 1,000,000 SKU's that they sell, but if you ignore Colour and Size that number may reduce to 10,000 items, divided into 500 Sub Categories, and perhaps 50 Categories. When analysis at the total level is required, its is significantly faster to read 1 row, than to read 1,000,000 rows to arrive at the answer.

Advantages of Star Pattern Design

  • Dimensions are business elements users are familiar with. A retailer thinks in terms of sales of Products, at a given Time, from a Particular Store.
  • The relationships from the dimension tables to the Fact Tables is easy to identify as the dimension keys are present on the Fact table and are a simple join from one table to another.
  • Some Business Intelligence tools operate by showing the user a picture of the database tables to make their selections from. Star patterns are a simple organization for users to deal with as all related dimensional information resides in the same table.
  • Many of the major database vendors have Star Pattern query recognition, so the execution plan to access the data is optimized for the design and results in better performance.
  • Star Pattern design has been in use for Data Warehousing for a number of years, and has proven its value for standard business reporting, which accounts for at least 80% of business reporting.