MSBI Interview Question’s:- Part 1

We have come up with few real-time questions asked to one of the fresher candidate during MSBI(SSIS, SSAS and SSRS) interview questions if you are one of those who is seeking for the same then below questions and answers will surely help you.

1. Is SQL Server required for MSBI Development?

  • Yes, SQL Server is required for MSBI Development.

2. What components did you use in SSIS package development?

  • We did use many components is SSIS like Conditional Split, Merge, Merge Join, Sort, SCD, CDC, Data Conversion, Execute Task component, Script Task, etc.

3. What is the difference between Merge & Union All?

  • They are both transformations of SSIS. The first difference between them is that Merge can only accept two datasets as input while Union All can accept multiple datasets as input.
  • The second main difference is that Merge requires both datasets to be sorted while Union All does not require sorting of the datasets.

4. What is an MDX query and give its sample query?

  • MDX Query is used to retrieve information stored in an OLAP Cube. It gives us the Multidimensional view of the data. SQL Query can handle only 2 dimensions in a query for processing tabular data whereas MDX Query can process more dimensions in a Query.
  • Example of MDX Query, “Select From [OLAPCubeName];”,

“SelectDimension.Member on Column From [Cube Name];”

5. What do you mean by SCD and explain its types?

  • SCD means Slowly Changing Dimensions. As the name says it is used where our dimensions are not changing that frequently. These dimensions can be our master tables like Country master, Currency master. We know that the countries or currencies do not change frequently so therefore these dimensions are termed as Slowly Changing Dimensions. Now SCD is of three types Type 0, Type 1 & Type 2.
  • Type 0 is known as Fixed Attribute. It means that whenever any changes are detected, it will consider those changes as errors.
  • Type 1 is known as Changing Attribute. It means that whenever any changes are done it will overwrite the new value on the old value.
  • Type 2 is known as Historical Attribute. It means that whenever any changes are done, it will show us both the old value and the new value.

6. How to generate a Cube is SSAS?

  • Cubes are multidimensional database which stores data to do analysis process. A Cube contains a Fact or measure in its center and Dimensions all around it means dimensions act as sides of the cube.
  • So when we create a SSAS project according the project structure first we have to provide the source from where our data or Facts & Dimensions will come. As soon as we do that, in our Data Source Views we see that our SSAS Database diagram is created. Here if we want we can change the relationship also. Remember that our SSAS database and our SQL Database are not linked, it means that any changes we do in our SSAS database it will not affect our SQL Database. For example, we change our relations and our Database diagram of SSAS Database, so it will not change the relations or the database diagram of our SQL Database.
  • After that there is a folder of Cube where on right click comes an option to create cube. Once we select it there will be a wizard opened where we have to configure the cube and follow the wizard step by step and our cube is generated. Inside the wizard it will ask us which Fact and which dimensions we want in our Cube.
  • In this way the Cube will be generated.

7. What are the different project deployment methods is SSIS?

  • There are basically two methods of project deployment in SSIS, they are Project deployment and Package deployment.
  • In project deployment the project is deployed in one go and this technique is preferred way of deploying SSIS project. It was introduced from year 2012. By this method we can deploy our project in SQL Server, MSDB & File System.
  • In package deployment individual package is deployed. This method is used when the whole project is deployed and after that there are some changes to be done on a package or new packages are to be added at that time we use this package deployment method. This method was active till 2008 and with this method we can deploy our project in our MSDB & File System and not in our SQL Server.

8. What do we mean by Primary Key & Foreign Key in SQL Table?

  • Primary & Foreign Key concepts are very important in creating a SQL Table. Primary key means it is a key given to that column which will be unique and non-repeating. Usually Primary key is given to the numbers or Id’s in a table.
  • Foreign Key means it is a key which is related to the primary key. There can be no foreign key without primary key. A table can have many foreign keys but it can have only one primary key.

9. What are the different joins in SQL?

  • There are different types of Joins which are there in SQL when we want to join the records of two or more tables.
  • These joins are Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join.

10. What is the query syntax of all the different joins in SQL?

  • Inner Join:

Select * From Employeetable inner join Departmenttable

ON Employeetable = Departmenttable;

  • Left Outer Join:

Select * From Employeetable left join Departmenttable

ON Employeetable = Departmenttable;

  • Right Outer Join:

Select * From Employeetable right join Departmenttable

ON Employeetable = Departmenttable;

  • Full Outer Join:

Select * From Employeetable full outer join Departmenttable

ON Employeetable = Departmenttable;

Above given questions and answers will serve as last minute revision on MSBI topic or will help as manual to prepare for interview. If you want more learning on MSBI topic will
practical full hands-on MSBI training in Mumbai so if you are from Mumbai city then visit us it will surely help to shape your MSBI career.

If you are not from Mumbai then learn MSBI through self-study learning MSBI project videos series especially for distance learners which has turn-out be good substitute of offline learning. Below is first video which starts with SSIS from that project series: –


About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- Learn AngularJS Step by Step:- Learn Design Pattern in 8 hours:- Learn C# and .NET in 60 days:- Learn MSBI in 32 hours:- Learn SharePoint Step by Step in 8 hours:-
This entry was posted in MSBI, MSBI Interview Question, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s