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.

No 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 through some light on the same and then we will conclude what is BISM.

Now over all 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 ( 1 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 in terms of fact tables and dimension tables. Cube which is a multi-dimensinal view of data is created properly if you design your database as OLAP system.

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

Now some times I will say many times it's really not feasible to create different structure 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 tamles. But yes they do understand tables with rows and columns. If you see microsoft excel the format is in terms of table which have rows and columns and these end users are comfortable with a tabular structure.

Below is a simple image of how simple end users visulize 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 user / 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 alast quick note this is also a favorite SQL Server interview question which is making round now a days when SQL Server 2012 topic is discussed.

With all due respect to my publisher Ihave 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)?



 

   

NET and Sql Server interview questions - By which query do we merge two different columns into single column?

Answer:
This is one of the most typical question asked by the interviewers.

Let’s us assume that we have the following two table of Product and Vendor with their respective fields like below.

ProductId ProductName Cost //Product table

VendorID VendorName ProductId //Vendor table

Now let’s begin with how you can design one to one relationship between these two tables.

One to One relationship:-
Below diagram show one to one relationship between Product and Vendor table.




In the above Product table “ProductId” is defined as a primary key and in the Vendor table “ProductId” is defined as foreign key with reference to the “ProductId” of the Product table.

In this way you can achieve one to one relationship.

One to Many relationship:-
Now we want to define one to many relationship on Vendor and Product table.
In this many records in one table correspond to the one record in another table.

Example: Every one vendor can have multiple products. So there exist one-to-many relationships between Vendor and Product table.

In order to define one to many relationship you have to add one column name as VendorId in Product table which is foreign key reference to VendorId of Vendor table.

Below diagram show one to many relationship on Product and Vendor table.



Many to Many relationship:-

In this, one record in one table corresponds to many rows in another table and also vice-versa.
For instance: In a company, one vendor can sale many products and also one product can be sold by many Vendors.
Given below diagram is a sample of many-to-many relationship. For defining many to many we have to add one more table VendorProduct which is linked to the primary key of Product and Vendor tables.




In the following video, view samples of various questions asked in C#, .NET and Sql Server interview.

Please click here to see more .NET/SQL Server interview questions

Regards,

Visit Authors blog for more .NET and ASP.NET interview questions

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

SQL Server interview questions: - How to back-up SQL Server Database and Restore Database?

There are basically two ways (methods) in order to back-up SQL Server Database
and Restore database.

The following are the two methods to do the same.

1. By simple Detach and Attach Methods.

2. By creating a .bak file of the Database.

Let’s see a simple example on both the above methods for creating back-up and
restoring database.

Method1: -By simple Detach and Attach.

Let us assume that we have a Customer Database on which we have to perform the
Back-up and Restore action.

In order to perform simple Detach and Attach method just right click on the
database name > go to Tasks > Select Detach. A new window will pop-up from the
check box of drop box just select the drop box and clicks ok.





As soon as you click on ok you find that now the Customer database is detach
from the Databases.



Now, just browse to the location of “Customer.mdf” database and copy the both
“Customer.mdf” and “Customer_log.ldf” and save both the file to your specified
location.



Now, let’s see how we can restore the database back to the Databases.

In order to restore “Customer” database right click on Databases > Select Attach
a new will pop-up like below diagram from that click on Add then again a new
window will open of locate database file from that select “Customer.mdf” and
click ok again you will be revert back to the Attach database window from that
click ok.









Now, you will see that “Customer” database is again attached to the Databases.



Method2: -By creating a .bak file of the Database.

In order to create a .bak file right click on “Customer” database > go to Tasks
> Select back up.


As soon as you select back up a new window like below diagram.


Now click on ok, a message will be displayed like below diagram.



Now, just browse to the destination, you will see a backup file (.bakformat) file is created.





Now, let’s see how to restore back the database.


As soon as you click on Restore Database a new window will appear like below
diagram from that select from device and click [----] then again a new window
will appear of specify back-up from that click on add.





As soon as you click on add then again a new window will appear like below
diagram of Locate back-up file from that select the Database on which you have
created the back-up and then click ok.



Now just click on ok.



Similarly, when you click on ok you will revert back to the restore database
window from that select the database name and check the restore box as marked
and later click on ok.



If everything goes right you will see the message as completed like below
diagram.


See the following SQL Server interview questions video on difference between unique key and primary key



By a click you can avail more tutorial on SQL Server interview questions

Regards,

Visit for more author’s blog on SQL Server interview questions

.NET and SQL Server interview questions - How to find out second highest salary from the table?

Answer:

Let's us assume that we have the following table of Employee.





Now we want to find out second highest salary from Employee table, as you see that the second highest salary is 15000 and we want to display the same.


Query:-

SELECT Emp_Name,Emp_Salary
FROM Employee e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.Emp_Salary))FROM Employee e2 WHERE e2.Emp_Salary >= e1.Emp_Salary)

The above employee table contains two same records as second highest salary so the result set will look like below output table.

Output:-




If the table contains two or more same record of salary which is the second highest salary then the query will give you all the record of second highest salary as you see in the above output table.


Please click here to see more .NET and SQL Server interview questions

Regards,

Visit Authors blog for more .NET and SQL Server interview questions

Working with DataTableReader class in C#

See 500 videos on c# and Dotnet interview questions