Need

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 SQL

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.

-----
Related content from the BeyeNETWORK

Why are traditional business intelligence projects giving way to faster, less expensive ones?
Claudia Imhoff answers that question during this web seminar where you'll also learn how to create accessible, technically feasible, low-cost BI solutions.
-----

Recent articles by Christina Rouse, Ph.D.

• Business Intelligence: Identifying Profitable Niches
• Improve Top Line Revenue with the Right Customer Risk Analysis: Rethinking Customer Profiling after the Recession
• Canary Metrics – A New Thought Process
• A Business Intelligence Primer for Higher Education