The tl/dr version is this:
Columnstore indexes use the Vertipaq compression engine (that’s the shiny compression engine in PowerPivot) to further compact indexes to make querying them between 10-100 times faster.
The most significant limitation is that tables become read-only when they have a columnstore index (no Inserts / Updates / Deletes etc) – though James notes you can work around this by using Partitions if you are dealing with tables that are just additive. Otherwise indexes will need to be dropped and recreated as data changes.
So – a powerful new indexing feature which, with careful management – can have a serious positive impact on the performance of your Data Warehouse.