Top 20 SQL Server 2012 features

Just started writing a 4 part article on 20 top features in SQL Server 2012 , below is the link of  part 1

http://www.codeproject.com/Articles/526621/Top-20-exciting-features-of-SQL-Server-2012-Part-1

What is BI Semantic model (BISM) in SQL Server 2012?

Some days back I was installing SQL Server 2012 enterprise service pack 1. During installation when I was running through the setup, it gave me two options (multi-dimensional and tabular) of how I want to install SQL Server analysis service. Below is the image captured while doing installation

At the first glance these options are clearly meant to specify how we want the model design for our analysis service.

Now the first option i.e. “MultiDimensional” was pretty clear as I have been using them right from SQL Server 2005 till today i.e. (Star schema or Snow flake).

After some googling and hunting I came to know about the second option. Let me throw some light on the same and then we will conclude what is BISM.

Now overall we have two kinds of database systems , one is OLTP system where the database design thought process is in terms of tables and normalization rules ( 1st normal form , second normal form and third normal form  database design ) are followed.

The second kinds of systems are OLAP system’s where we mostly design is in terms of fact tables and dimension tables. Cube which is a multi-dimensional view of data is created properly if you design your database as OLAP system.

So in simple words we need to create a DB with OLAP design to ensure that proper cubes structure is created.

Now some times or I will say many times it’s really not feasible to create different structures and then create cubes from them. It would be great if SSAS gives us some options where we can do analysis straight from normalized simple tables.

For instance take simple end users who use “power pivot”. It’s very difficult for them to make understand OLAP models like dimension and fact tables. But yes they do understand tables with rows and columns.  If you see Microsoft excel the format is in terms of tables which have rows and columns and these end users are comfortable with a tabular structure.

 Below is a simple image of how simple end user visualize data in excel i.e. tabular – rows and columns.

That’s where exactly the second option i.e. the “Tabular” mode comes in to picture.

So if we put in simple words BISM (Business intelligence semantic model) is a model which tries to serve simple users / programmers who are comfortable with tabular structure and also maintains professional OLAP models for corporate.

So BISM is a unifying name for both Multi-dimension and tabular models.  So if you are personal BI person who loves ADHOC analysis, you can use power pivot or SSAS tabular IDE to do analysis. And if you are person who is working on a corporate project then Multi-dimension model is more scalable and worth looking in to.

Just a last quick note this is also a favorite SQL Server interview question which is making rounds now a days when SQL Server 2012 topic is discussed.

With all due respect to my publisher I have taken the above answer from my book SQL Server interview questions and answers.

You can also see my blog which has some important for SQL Server interview questions on Colaesce. 

You can also see my video on Can views be updated (SQL Server interview questions) ?

CodeProject

SQL Server Interview Question and answers: - How does index affect insert, updates and deletes?

First let me give a short answer so that you can please the interviewer and get your job. If you are inserting heavy data on table which have clustered indexes that will lead to page split. Due to heavy page splits the performance can degrade. So a proper balance of indexes should be maintained for tables which are heavy in transactions and have clustered indexes created.

Now let’s go for a big explanation of what exactly is a page split.

Indexes are organized in B-Tree structure divided in to root nodes, intermediate nodes and leaf nodes. The leaf node of the B-tree actually contains data. The leaf index node is of 8 KB size i.e. 8192 bytes. So if data exceed over 8 KB size it has to create new 8 KB pages to fit in data. This creation of new page for accommodating new data is termed as page split.

Let me explain you page split in more depth. Let’s consider you have a simple table with two fields “Id” and MyData” with data type as “int” and “char(2000)” respectively as shown in the below figure. “Id” column is clustered indexed.

That means each row is of size 2008 bytes (2000 bytes for “MyData” and 8 bytes for “Id”).

So if we have four records the total size will be 8032 bytes (2008 * 4) that leaves 160 bytes free.Do looat the above image for visual representation.

So if one more new record is added there is no place left to accommodate the new record and the index page is forced to go for an index page split.

So the more we have page splits, the more the performance will be hit as the processor has to make extrefforts to make those page split’s, allocate space etc.

Here’s a awesome video on one more tough SQL Server interview question: -What are CTE (Common table expression) ? , you watch the same from by clicking on

Taken from the best selling SQL Server interview question book, you can see more about the book by clicking on  SQLServer interview questions book Dotnet interview questions and answers book  

See more stuffs on  SQL server interview questions

Regards,

Click here to view more SQL server interview questions and answers .