Why use OLAP?
- Queries run 1000% faster
- Click to query
- Near real-time information
- Reduce stress on your OLTP system
Two kinds of tables form a data warehouse:
Fact tables
- Also called measures.
- The fact table is the table that holds the facts of the business that need to be analysed, such as sales, profits, amount of storage at hand, etc.
- This type of data is often numeric.
- The values are quite often subject to aggregation (pre-calculating roll-ups of data over hierarchies, which subsequently yield improved query results).
- Building the fact table is an important step towards building your data warehouse.
Dimension Tables
- A dimension is a collection of properties along which we conduct our analysis of the facts.
- Dimensions allow us to view the fact in different contexts.
- Common dimensions used in sales -related data marts include:
- Time
- Geography
- Customer
- Salesperson
- Scenarios such as actual, budgeted, or estimated numbers
OLAP Schemas
- Star Schemas unite dimensions and facts
- De-normalized schemas (deliberately duplicate data for fast query result, so no need to join tables)
Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server 2000 Data Warehousing with Analysis Services
