SQL 2012 : Column Store Index

Column store Index is one of the new feature introduced by Microsoft in sql server 2012 edition.
This is a new kind of indexing which provides significant performance for queries & ssas for aggregations etc.
In a regular indexing , each row is stored on a page where as in the column store index each column is stored per page which makes it much more efficient for queries such a aggregations etc.. which will have specific columns in it. They are highly compressed , so easy to store large volume of data in  memory resulting in very low IO cost.

How ever there are some limitations , few are mentioned below :
1) they are read only, once created  inserts/ updates are not possible, this can be used in warehousing applications where data is loaded periodically & before data load index can be dropped or disabled & then created once the data load is finished.
2) there can be only one column store index per table
3) it cant be clustered index.
4) cant be pk  or fk
5)cant be used with tables that has cdc or file stream

Reading material :

http://social.technet.microsoft.com/wiki/search/searchresults.aspx?q=SQL+Server+Columnstore
http://blog.sqlauthority.com/2011/10/29/sql-server-fundamentals-of-columnstore-index/
http://blog.sqlauthority.com/2011/11/07/sql-server-video-performance-improvement-in-columnstore-index/
http://blog.sqlauthority.com/2011/11/06/sql-server-updating-data-in-a-columnstore-index/
http://blog.sqlauthority.com/2011/10/30/sql-server-how-to-ignore-columnstore-index-usage-in-query/

SQL Merge + SCD ( slowly changing dimensions)

Sql server Merge is a powerful t-sql statement which can be used to write simple code to do insert, update, delete in one statement instead of writing 3 different statements one for update, one for insert & one for delete.  Using Merge is cleaner & much more efficient when it executes.

Syntax looks like this :


merge in to  dbo.destination as d
using dbo.source as s
on (s.pk= d.pk)
when matched
            then update set d.col1= s.col1
when not matched
         then insert....

How ever Merge can do much more than what is seen here, There is an excellent presentation by Kimball group about how Merge can be used in SCD scenario :

http://www.kimballgroup.com/html/08dt/KU107_UsingSQL_MERGESlowlyChangingDimension.pdf

Real Time Data Push to SSAS Cube

Real time data push in to SSAS cube is very much possible.

There are different ways of doing it, How ever you have explore & see which one best fits your needs

The below are some of the options :

1) Push the data directly from the WH in to SSAS cubes using SSIS Package.
ex: Lets say there is a product dimension & Orders Fact table , in the SSIS package which loads the data in to these tables we can extend them to push the data to Product dimension & Orders Fact  of OLAP cube.

2) Proactive Caching based on policy based management. The Policy settings  - SilenceInterval, SilenceOverrideInterval, ForceRebuildInterval & latency play very important role.

But how do we get the data changed notifications from the data source, Here are the options :
 1) use event trace entries
 2)  application can notify through webservice call
3) by polling the data source using sql queries to see if data has changed using a timestamp or any other column.

Umbraco + MVC

After the announcement from Umbraco team that V5 was ripped off, we were left with version 4.7 which is latest version we could have at the point of this post is published.
we were not left with much options . so we went ahead with what we have & utilised MVCBridge package installer which gives a way to create a page in Umbraco & give the controller , action names to the call. with this we were able to integrate our existing MVC webapp in to Umbraco ..

We thought issue was resolved , how ever we boggled down with Ajax Calls, then we  went ahead and modified the source code of MVCBridge to make ajax calls with just Controller , Action as we don't have access to HTMLHelper classes & used the Umbraco /base ajax calls with parameters controller & action which will then make a call to routine call from MVCBridge. Even though we don't have helper class to pass parameters still we can pass parameters to ajax  /base class as they will be available in Context object for MVC call.

Hope this helps ..

MOLAP ROLAP HOLAP

MOLAP, ROLAP, HOLAP are terms you will come across in SSAS .
Keeping it simple what they mean are :

MOLAP is Multidimensional OLAP : what ever aggregations, group by etc you can imagine on the query to generate the counts are pre-aggregated & calculated before hand  in cube , so that the execution is fast & don't have to calculate when a request is made.

ROLAP is Relational OLAP : when ever a query is issued , they will be executed against the relational databases to return the resultset. you can imagine this as a stored procedure call.

HOLAP is Hybrid OLAP : It is a Hybrid of above two, HOLAP is used where aggregations, group by etc are used to return the result set & MOLAP is used for regular queries. Sharepoint deployment of reports built on SSAS cubes & SSRS reports mostly use HOLAP.  MOLAP for main repots & HOLAP for drill down reports.