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: –

Posted in MSBI, MSBI Interview Question, Uncategorized | Tagged , , , , , | Leave a comment

How to perform Script Task and send email using Script Task?

Script Tasks are used to send an email or do any web related tasks where customization is required. In Script Task for doing the customization we have to write C# code. It would be helpful for the MSBI training candidate if they have earlier worked with Visual Studio as writing C# code would require this tool to perform this lab easily.

So first of all we have to create a new package and inside that package we have to drag and drop the Script Task component from SSIS Toolbox.

1

After doing that we have to just double click on the Script Task component to configure it.

2

Now to write the C# code we have to click on option Edit Script and then a new Visual Studio window will open where we will write our C# code.

3

Now we will write the C# code.

4

This is how our Visual Studio window will look like where we have to write the code.

5

publicvoid Main()
		{
// TODO: Add your code here

// Step 1 :  Configure SMTP..
SmtpClient client = newSmtpClient();
client.Host = "smtp.gmail.com";
client.Port = 587;
client.EnableSsl = true;

//Step 2 : Provide Credentials..
string Username = "xyz123@gmail.com";
string Password = "xyz123";

NetworkCredential credential = newNetworkCredential(Username,Password);
client.Credentials = credential;

//Step 3: Configure from address..
MailAddressFromAddress = newMailAddress(Username, "John Doe");

//Step 4 : Create message to be sent..
MailMessage message = newMailMessage();
message.From = FromAddress;
message.To.Add("abc@gmail.com");
message.Subject = "SSIS Script Task";
message.IsBodyHtml = true;
message.Body = "Hello World from MSBI";

//Step 5: Send Email..
client.Send(message);

			Dts.TaskResult = (int)ScriptResults.Success;
		}

This is the C# code written step by step. No worries if candidate of MSBI traininghas never worked with C#,faculty helps to learn, understand and execute this lab.

After writing the code, we will close the window and click OK on our Script Task configuration dialog box.

6

After this done save the Script Task component and then execute the package.

In this way we can send email using Script Task component.

Also get to see following video on MSBI project series which start from the basic to start SSIS : –

Posted in MSBI, Uncategorized | Tagged , , , , , , | Leave a comment

3 things to avoid during Job Interviews By Questpond.

First and foremost avoid the below three things:-

Avoid saying negative about your previous organization because that would send wrong message to the interviewer. Do not create a pessimistic image before the interviewer.On the contrary if you start with positive things like :- “The current company has no issues and I am extremely happy with the current environment but it’s more of a personal problem etc etc…” that would create a very strong and positive image of yours.

Avoid complaining about your current work profile like “It’s a maintenance project, it’s in 1.1 version and I am not learning anything new etc.”. It’s very much possible that the interviewer has a maintenance job. So in such kind of situation’s I have seen the interviewer rejecting people irrespective even though if they have performed well in technical round.

Avoid saying you are jumping only because of salary. Some of the interviewer’s do not like people who just look at monetary benefits. When you are asked about salary expectation these things could be better discussed during that conversation. Do not make it explicit during the interview. Bet me nobody likes greedy professionals:).

Below are some of the good reasons for which the interviewer does not cross question back:-

  • For better prospects and growth.
  • Office is far away I spend lot of time travelling and would like minimize my travelling time.
  • They are telling me to relocate but in my current situation it’s not viable.
  • I am developer but due to project requirement I am in a testing role and so want to switch.
  • Project is getting closed and the company does not have projects.
  • Company is not paying salary on time.
Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on (SSIS, SSAS and SSRS)MSBI

What are partitions in SSAS?

SSAS let us create cube. Cube is a multi-dimensional database. Data will be stored inside cube as dimensions and Fact Tables.
By default in order to store Fact tables (measure group tables) partitions will be created inside cube.
Be default for one fact table one partition will be created. Partition is simply a physical storage unit inside cube.

Note: End user is nothing to do with partition. Her/she never say, I want data from this or that partition.
For him/her it is always going to be single partition.

Can we span single fact table across multiple partition?

Yes, we can. We can create more than one partition for a single fact table.

Is there anything we have to keep in mind while creating multiple partitions for single fact table?

Yes, we have to make sure that each partition contain unique records.

Advantages of having partition:

  • Each partition can be stored inside a separate physical drive bringing parallel data access into picture.
  • Each partition can have its own aggregation logic.
  • Different storage setting can be defined for each partition. Example – one partition supports MOLAP whereas one supports ROLAP
  • Each partition can be processed independently
  • By keeping historical data and a new data of a particular method in separate partitions processing speed can be improved.

Does partitions store only method group data?

No, in case of aggregating logic is defined, it also stores aggregated data.

Also refer our article on 8 important SQL Server (SSIS, SSAS and SSRS)MSBI Interview questions

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See practical video on explaining SSIS, SSAS and SSRS (part 1) with sample demo: –

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on ASP.NET MVC (Model View Controller)

Why MVC (Model View Controller) is loosely coupled?

In order to answer this question, first let’s understand what’s the difference between loosely coupling and tightly coupling is.

Look at the following pic.

p1

When we talk about human body, body parts are tightly coupled to each other. We cannot simply replace hand with some other hand or a leg with some other leg. This is called tightly coupling.
When we talk about computer most of the parts are loosely coupled. Take the RAM out put a new one, same with display, keypad etc. This is called loosely coupling.

In Web Forms User interaction logic is handled inside CodeBehind where as in Asp.net MVC it’s handled inside Controller. Now if we compare Web form Codebehind with MVC Controller biggest difference is, CodeBehind is tightly coupled with view where as controller wont tightly connected to any view.
Single controller can work with more than one view and similarly one view may contain references for more than one controller in form or anchor tag.

p2

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See the following MVC (Model View Controller) video on viewdata, viewbag, tempdata & session:-

 

Click and see here for more step by step training in ASP.NET MVC

 

 

 

 

Posted in Uncategorized | Tagged , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on ADO.NET

What is the use of Command objects?

Command object helps to execute SQL statements. Following are the methods provided by command object:-

  • ExecuteNonQuery: – Executes insert, update and delete SQL commands. Returns an Integer indicating the number of rows affected by the query.
  • ExecuteReader: – Executes select SQL statements which can either be in your .NET code or in a stored procedure. Returns a “Datareader” object.
  • ExecuteScalar: – Executes SQL command and returns only a single value like count,sum , first record etc.

How can we fine-tune the command object when we are expecting a single row?

Again, CommandBehaviour enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.

What are Dataset objects?

Dataset is an in memory object with data tables, rows and columns. You can visualize it as in-memory RDBMS.Dataset has the following features:-

  • The in memory RDBMS works in a disconnected manner. In other words even if the connection is closed the dataset is still there in memory.
  • You can do modification in the in-memory database object and send the final changes to the database.

Below is a simple code snippet which shows how to access a column value. You can see how the full dataset object hierarchy is accessed to get the column value.

objDataset.Tables[0].Rows[0]["CustCode"]

dataset

How can we force the connection object to close after my data reader is closed?

Command method Execute reader takes a parameter called as Command Behavior wherein we can specify saying close connection automatically after the Data reader is close.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.Close
Connection)

See the following video on ADO.NET connection pooling: –

 

Click and see here for more training on ADO.NET

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on .NET

What is a Class and structure’s?

Structures are value types and classes are reference types..

When to use Structures and When to use classes?

You will use structures when: –

Point 1:- If you want to represent a custom value type. This custom value type is derived from primitive data types (int, double). Some of the example’s of custom types are co-ordinates (which have X, Y), complex numbers (which have real and imaginary components). You can also term these things as value objects or technical objects. Technical objects do not represent real world objects like customer, supplier, invoice etc.

Point 2:- If you want to have low memory foot print. For instance let’s say you want to plot a graph. To plot a graph you need to have 100’s of objects created so that you can represent co-ordinates. Now if you create a class and generate those 100’s of objects you end up putting lot of load on your garbage collector. If you create a “struct” , it’s a value type. So they get created and destroyed immediately. Thus putting less load on memory.
For all other scenarios use a class.

datatype

 

What are similarities between Class and structure?

Following are the similarities between classes and structures:-

  • Both can have constructors, methods, properties, fields, constants, enumerations, events, and event handlers.
  • Structures and classes can implement interface.
  • Both of them can have constructors with and without parameter.
  • Both can have delegates and events.

What is the difference between Class and structure’s?

Following are the key differences between them:-

  • Structures are value types and classes are reference types. So structures use stack and classes use heap.
  • Structures members cannot be declared as protected, but class members can be. You cannot do inheritance in structures.
  • Structures do not require constructors while classes require.
  • Objects created from classes are terminated using Garbage collector. Structures are not destroyed using GC.

Click and see here for more basics training in .NET


 

Posted in Uncategorized | Tagged , , , , , , , | Leave a comment