ColumnStore Index - SQL 2012 New features

Summary:

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

Download "Column Store Index" white paper

Note: above content is copied from the White paper discussed in this post

Types of Hierarchies in Data Warehouse:

Hierarchies are logical entities that an end user can use to analyze fact data.  These entities can be made of one or multiple levels.

Hierarchies are classified as three ways:-
1. Balanced
2. Unbalanced
3. Ragged


Have a look at the exhibit given below, hope it will help you to understand about the types of hierarchies in better way


Balanced Hierarchies:-

Each level in a hierarchy has the same number of members above it as any other member at the same level


Un Balanced Hierarchies:-

Each level in a hierarchy may not the same number of members as the member which are at the same level, refer the hierarchy structure shown above.


Ragged Hierarchies:-


It is not like balanced Hierarchy, the logical parent member of at least one member is not in the level immediately above the member. It will skip the immediate parent. 

For Example: In a company, all the divisions will be reporting to the VP thru hierarchical structure, but Finance department will directly reporting to VP without any middle level.

Difference Between Star Schema and Snowflake Schema

Star Schema	Snowflake Schema
De-Normalized Data Structure	Normalized Data Structure
Category wise Single Dimension Table	Dimension table split into many pieces
More data dependency and redundancy	less data dependency and No redundancy
No need to use complicated join	Complicated Join
Query Results Faster	Some delay in Query Processing
No Parent Table	It May contain Parent Table
Simple DB Structure	Complicated DB Structure
View Star Schema Structure	View Snowflake Schema Structure