Columnstore Indexes

This is the new feature added in SQL Server 2012 to overcome query performance and index data compression issues. The data stored in the columnstore index is pretty much compressed and very optimized. In SQL Srever 2012, if you have a columnstore index, you cannot perform the DML operations on the table i.e. you cannot insert / update or delete any record. The purpose of this index is for Data warehousing. You have to devise the logic in order to populate the data, for example table partitioning. But this issue is resolved in SQL Server 2014. But there, you will have to create a CLUSTERED COLUMNSTORE INDEX.

You can add this index simply by right clicking the Index folder under the table on which you want to add the column store index;

On clicking the option, a screen will open asking to add the column(s) on which you want to create a column store index (FirstName, LastName).

Leave rest of the properties to default. Click OK button to add the index.

Now, question is how the data will be stored? We have two types of data storge in sql server, RowStore and Column Store. Assume you have a tabel as

RNO       NAME          FNAME
1              Atif                Sheikh
2              Asif                Ahmed
3             Imran              Khan

In RowStore index, the data will be saved as;

Atif,Sheikh
Asif,Ahmed
Imran,Khan

The above data will be saved as a row in the 8k page.

And this will go on until the 8k page is complete.

In case of Columnstore index, the data will be stored as a segment of its respective column. Each segment have only one column. This goes for all the columns in the index. A Column can span on multiple segments and a segment may have multiple data pages. As for above example the data will be stored as;

Col1               Col2

Atif                  Sheikh
Asif                 Ahmed
Imran              Khan

These columns are just like pointers in the 8k page of the index. One page can have multiple columns

Now if you will query with NAME or FNAME in your WHERE condition, the data search and retrieval will be very fast as each condition will have to check one single column. You can check this with Statistics and Execution plans on you tables.

Steps to develop a Cube in SSAS...

Before we start with the cube development, there are a few things that should be discussed. As we know that the cubes are developed on OLTP databases that are de-normalized. You should be having a complete understanding of your existing OLTP database and the reporting requirements for which you are planning for SSAS cube. This is done to get maximum performance from them. Also, It is easier for the cubes to make aggregates against the data. So;

1.   Check and list down the tables for your Diemnsions in your OLTP production database.
2.   Check and list down the tables for Facts in your production database.
3.   In addition to all the dimensions, you will be needing a Date dimension and Time dimension.
4.   Create a separete OLTP database for the above listed Dimensions and Facts. You can also call this database as your Staging database.
5.   Name Dimension tables with "DIM" at the end or Statr so that you can easily identify them.
6.   Name Facts table with "Fact" at the end for easy identification.
7.   De-normalize your Dimension tables in a way that you can create buit-in hirarchies. 8.   You Fact tables should contain factual information(Quantities, Amounts, etc) along with the foreign keys with the tables that you have created as Dimensions in your New OLTP database.

9.   Define Primary keys and foriegn keys as required.
10. Write down the scripts to initialize your Staging Database with the data in your production database. I would recommend that you take backup of your production database and restore at the same serber where you have creted your Staging database. Work on that copy of the production database and DO NOT work directly on your production database on production server.
11.  Verify the data ported by scripts.
 
The purpose of the Staging database are multiple. While porting your normalized data to de-normalized database, you might findout some missing or incomplete data. This issue especilly rises for dates, times and names. You can clean your data in here using data cleansing procedures according to your requirements.
 
Follow the above mentioned steps to create and populate the staging database and prepare your self for creating cube on this database.