What Is Star Schema?

What's the best way to get your data warehouse to shine? The answer is simple: star schemas. A star schema is a data warehousing architecture model where one fact table references multiple dimension tables. When viewed as a diagram, it looks like a star with the fact table in the center and the dimension tables radiating from it. It is the simplest among the data warehousing schemas and is widely used. In the star schema model, one central fact table is surrounded by multiple dimension tables. It can be represented as shown below: The relationships between these tables are shown as follows: Its approach simplifies queries and improves query performance. The fact table contains all the measures of interest, while each dimension table contains aggregated values describing each record's attribute in its corresponding dimension. The star schema is the simplest form of a dimensional model used in business intelligence and data warehousing, wherein data is arranged in measurements and facts. In the star schema, there is a single fact table, which is usually expressed in the third standard form (3NF), and multiple de-normalized dimension tables connected to it, radiating out like the points of a star. The star schema has been optimized for querying large data sets. It is generally used in data marts and warehouses to support OLAP cubes, ad hoc queries, analytic applications and business intelligence. The basic concept behind the star schema is that it enables you to design a single fact table that holds all your baseline information. You can create multiple dimensions to help you understand how each piece of information relates to another within your database. For example, suppose we were working with sales data. In that case, we might have separate dimensions for customer names, product names, or even date ranges depending on what information we wanted to track down once our query was complete! Did you know that a star schema is a fancy way of saying: "table"? Yeah, it's true. A star schema is a fact table with two foreign keys pointing to dimension tables. Those dimension tables are just structures usually composed of multiple hierarchies that categorize data.