Fundamental -- <Four> Using Star Schema
Understanding the basic usage pattern of the star schema allows the dimensional designer to make intelligent choices.
Querying Facts
Most queries against a star schema follow a consistent pattern. One or more facts are requested, along with the dimensional attributes that provide the desired context. The facts will be summarized in accordance with the dimensions present in the query. Dimension values are also used to limit the scope of the query, serving as the basis for filters or constraints on the data to be fetched and aggregated.
A properly configured relational database is well equipped to respond to such a query, which is issued using Structured Query Language (SQL).
The SELECT clause of the query indicates the dimensions that should appear in the query results (category and product), the fact that is requested (order dollars), and the manner in which it will be aggregated (through the SQL Sum() operation).
The FROM clause specifies the star schema tables that are involved in the query.
The WHERE clause serves two purposes. First, it filters the query results based on the values of specific dimension columns (month and year). It also specifies the join relationships between tables in the query.
The GROUP BY clause specifies the context to which the fact will be aggregated by the relational database.
The ORDER BY clause uses dimensions to specify how the results will be sorted.
For readers new to dimensional design, there are two key insights to take away. First, the star schema can be used in this manner with any combination of facts and dimensions. This permits the star to answer questions that may not have been posed during the design process. Second, note that the ability to report facts is primarily limited by the level of detail at which they are stored. While it is possible to aggregate the detailed fact table rows in accordance with any set of dimensions, it is not possible to produce a lower level of detail. If a fact table stores daily totals, for example, it cannot be used to look at an individual order.
