“It is the stars, the stars above us, that govern our conditions.”
Business users need to query the data warehouse and to create reports with little or no intervention from IT. Adopting a reporting and online analytical processing (OLAP) tool that engages a
full semantic layer with cubing capabilities will improve the business user experience and lessen the reliance on IT. OLAP
tools, while capable of connecting to a third normal form (3NF) database,
prefer a star schema-designed database. The goal of any semantic layer should be to produce user-defined queries with only inner JOINs in the SQL statement.
Star Schema Data Models
Star schema data warehouse models are designed for analytics. Specifically, a star schema:
- Is very easy to understand, even for non-technical business managers.
- Provides better performance and shorter query times.
- Is extensible and will handle future changes more easily than the source transactional system.
Why It Works
In the star schema design, the fact table contains only keys and additive facts, or integers and some decimal numbers. The keys are also numbers. Databases store numbers very efficiently. A star
schema uses two kinds of database tables: a dimension table and a fact table. Each type of table plays a unique role in the overall design allowing an efficient inner JOIN only.
Dimensions can be thought of as categories. Dimensions usually have hierarchies within them. For example, a Campus dimension could have University (all campuses) at the top of the hierarchy.
University can then be broken down into many Campuses and Colleges and can then be further broken down into many Departments and Majors.
A fact table contains the measurements or metrics or facts of the business processes, in this case student registrations. A measurement of this business process would be student FTE, revenue and
headcount. The tuition revenue is also captured in the fact table.
Star schemas are extremely user-friendly and allow users to generate SQL statements that are efficient and can be processed easily by a database. Using a star schema and a business intelligence
(BI) tool, users will generate queries like the samples provided here. This kind of query technique will allow users to run queries across different units of the business. Users may change columns
needed in the report and may apply different filters in the WHERE clause noted here. From an IT perspective, users can generate a standard and yet optimized query with great flexibility and without
much IT intervention.
Here are two examples of the expected SQL statement. The first is a query against one star and the second of a cross-star join.
SOURCE: Why Stars Work
Recent articles by Christina Rouse, Ph.D.