Supporting analytical applications in SQL Server differs quite a lot from supporting transactional applications. The typical schema for reporting queries is the star schema. In a star schema, there is one central table called a fact table and multiple surrounding tables called dimensions. The fact table is always on the many side of every relationship with every dimension. A database that supports analytical queries and uses the star schema design is called Data Warehouse (DW). Dealing with data warehousing design in detail is beyond the scope of this book. Nevertheless, there is a lot of literature available. For a quick start, you can read the data warehouse concepts MSDN blog at https://blogs.msdn.microsoft.com/syedab/2010/06/01/data-warehouse-concepts/. The WideWorldImportersDW demo database implements multiple star schemas. The following screenshot shows a subset of tables from this database that supports analytical queries for sales:

Typical analytical queries require huge amounts of data, for example, sales data for two years, which is then aggregated. Therefore, index seeks are quite rare. Most of the time, you need to optimize the number of IO reads using different techniques from those you would use in a transactional environment, where you have mostly selective queries that benefit a lot from index seeks. Columnstore indexes are the latest and probably the most important optimization for analytical queries in SQL Server. However, before going into columnar storage, you should be familiar with other techniques and possibilities for optimizing data warehousing scenarios. Everything you will learn about in this and the upcoming section will help you understand the need for, and the implementation of, columnstore indexes. You will learn about the following:

• Join types
• Bitmap filtered hash joins
• B-tree indexes for analytical queries
• Filtered nonclustered indexes
• Table partitioning
• Indexed views
• Row compression
• Page compression
• Appropriate query techniques