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

Important SQL Server interview questions on data types: - How many bytes does “char” data type consume as compared to “nchar” data type?

“char” data types consumes 1 byte while “nchar” consumes 2 bytes. The reason is because “char” stores only ASCII characters while “nchar” can store UNICODE contents as well.

In case you are new to ASCII and UNICODE. ASCII accommodates 256 characters i.e.english letters ,punctuations , numbers etc.But if we want to store a Chinese character  or some other language characters then  it was difficult to the same with ASCII  , that’s where UNICODE comes in to picture. An ASCII character needs only 1 byte   to represent a character while UNICODE needs to 2 bytes.

One of my SQL Server friends who had gone for a SQL interview was grilled , whether we can update SQL Server views , I have tried to answer this question here with a video


The above question I have extracted from near to heart book
SQL Server interview questions By Shivprasad koirala by BPB publication

Regards,

Click to view more from authors on SQL Server interview questions and answers

You are 35 and not a manager – The Indian IT society trauma?

I am not trying to underestimate the position of a manager.  But I hate when people point fingers at me and say “You are 35 and still not a manager, till what age can you code?”.
I am so sorry to be harsh, but I really hate those people who for various reasons think that programmers are some kind of useless mass on earth. Do not contradict that many do not think they think and its truth.

By grace of god I am personally at a position where I do not care andI love programming, technology and would like to spend rest of life doing the same. 

But with that statement many good developers would have been converted to bad managers overnight.  Frankly I blame the Indian IT society who thinks after a certain age a person is less capable of  programming.
Leaving my so called personal confidence aside thought to look at the other side of the mirror on, why majority developers are forced to become managers.









Social trauma 

For some reasons when someone (especially senior managers) knows you are a developer they start thinking about you as a person who is not a decision maker or probably they  just imagine you as  technical lover  who has  no understanding of how to do business .


Some people also go one step further to conclude that a pure technical person can never be leader or motivate any one.

A person at the age of 30 is considered a failure, if he is involving himself in coding.

A manager allocating resources from a excel sheet is more respected by IT society than a developer or a technical architect slogging to solve problems.

Major people of IT society envy how a manager has his own cabin, how he commands, how he does so called project management, allocates resources, motivates people (some project managers have the illusion that they are bill gates) , implements processes blah blah. This is the first reason I think that a technical person starts thinking that a manager is a better fit than a technical role. He starts dreaming about the comfort zone and luxury enjoyed in a managerial position.

Technical person does not have money or the power

In India the salary difference of a senior technical person and equivalent experience manager is considerable. 

A manager who is working on a excel sheet, PPT and documents are paid more than technical people who actually create the software.

Adding to everything some senior managers have this feeling that a technical person can be made happy by giving challenging technical work and he will not crib for anything. Oh yes, I love technical work but at the end of the day I have to show my face to the family.

I am not saying start paying the senior technical person’s more. But this difference should be minimized so that atleast senior technical people start feeling that yes this role at this is age is also appreciated by the industry.

Technical architect position a joke

Many people can argue that, then choose the path of a technical architect. Oh, gosh, this position in Indian IT industry is abused (sorry for crossing limits) to a extent that there are people who refrain to take this position any more. I sometimes think this position is created to create a illusion that they are growing in their career.

As a technical architect, they expect you do everything. Right from technical solution, to nitty gritty problem solving and providing hands on solution to the developer’s.



Wondering when Indian IT industry will start respecting technical people , give them appropriate respectable position , his words are considered during decision making and he stands shoulder to shoulder with the project manager  in terms of decision making.Sorry for the loud mouth. I do not have the genes to write these kinds of article but I am sure it echoes feelings of lot of technical people out there.Would like to hear from you guys, if your organization has a proper road map for techies and do they really feel comfortable with the road map.

C# and ASP.NET MVC (Model view controller) interview questions and answers:- What are MVC partial views and how can we call the same in MVC pages?

ASP.NET MVC (Model view controller) partial view is a reusable view (like a user control)which can be embedded inside other view. For example let's say all your pages of your site have a standard structure with left menu,header and footer as shown in the image below.

For every page you would like to reuse the left menu, header and footer controls.So you can go and create partial views for each of these items and then you call that partial view in  the  main view.

When you add a view to your project you need to check the “Create partial view” check box.



Once the partial view is created you can then call the partial view in the main view using "Html.RenderPartial" method as shown in the below code snippet'

Below is an important SQL server interview question and answers video:-




Do visit us for c#  and ASP.NET MVC interview question and answers

See more stuffs on C#/ASP.NET interview questions 

Regards,

Click here to see more C# and ASP.NET interview questions and answers

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 .

.NET Interview questions and answers: -What are different access modifiers?

Taken from my book.NET interview questions http://www.flipkart.com/.
There are 5 access modifiers. Access modifiers define scope for members.

·     Private:  Accessible only with in the class.

·     Protected: - Accessiblewith in the class and in derived classes.

·     Friend (internal in C#):- Accessible anywhere within the current project.

·     Protected friend (protected internal in C#):- Accessible with current project and derived classes.

        ·       Public: - Accessible everywhere.

  

One of interesting .NET interview question: -What is the difference between string and string builder ?

Do not forget to see our Most asked .NET interview questions from www.questpond.com

SQL Server SSIS Interview questions: -What is the difference between “For loop container” and “Foreach loop container”?

The “For Loop Container” executes specified number of times like 10 times, 20 times until the specified condition is met.

The “Foreach Loop Container” runs over an iterator. This iterator can be files from a folder, records from ADO, data from a variable etc.

In case you are fresher to SSIS, SSAS and SSRS you can start seeing our step by step videos from http://www.youtube.com/watch?v=I_Ae3suaL-U

Do not forget to see our SQL Server interview question videos from www.questpond.com

Click to get important c# interview questions

Regards,

Get more C# training stuffs from author's blog