1. Onlinebuff
2. MSBI Tutorials
3. MSBI Interview Questions and Answers Part 2

MSBI Interview Questions and Answers Part 2

• By Gurunatha Dogi in MSBI
• Jun 14th, 2016
• 12830
• 0

Hey guys welcome to MSBI tutorials, This is Part 2 article of MSBI interview questions and answers for experienced and as well as for freshers. Just click here to view part 1 article.

If you want to view more tutorials and resouces on MSBI or Microsoft technologies kindly check this website http://www.learnmsbitutorials.net/.

During debugging can we make changes to SSIS package?

During debugging making changes to package is not possible as it is already running in debug mode where you can see values as well status of running component where it shows it runs or fails. With green symbol on component it means it is executing successfully and with red mark it represent component is failed. It will also show how many rows being executed etc. So while debugging you can see its only its execution and changes cannot be done to it.

How to stop debugging in SSIS program?

When package is in debugging mode after it is clicked on Start button for execution it can be stopped using stop button available on the top menu ribbon. Thereafter it stops debugging of package and apply new changes or it can be modified.

How to avoid data conversion component in SSIS?

In order to avoid data conversion component in SSIS then that datatype conversion has to be done when CSV or txt file is loaded & read at Flat File Source component. If proper datatype is selected at the start itself everything will be proceeded properly thereafter and even there won’t be need of data conversion component. Now below is the demonstration to make changes of datatype at start on connection manager do a double click to open its Editor.

Once Editor is open click on "Advanced” tab option available at the left side and click on each column to see it’s existing datatype and can be set to proper required datatype as per column field.

Here set CustomerAmount -> DataType as currency[DT_CY] (string to currency)

CountryName->DataType as Unicode string[DT_WSTR] (non-unicode to

unicode)

StatesName->DataType as Unicode string[DT_WSTR]

ProductName->DataType as Unicode string[DT_WSTR]

SalesPersonName->DataType as Unicode string[DT_WSTR]

Once these DataTypes are selected on the input source then using of Data Conversion can be avoided.

What is the gain of avoiding data conversion component?

Conversion is required because there is lots of mismatch of datatypes due to which data conversion component is required. So the data read from input source CSV or text file like string, non-unicode and goes to database which has money and unicodedatatypes here data conversion component will play vital role and do matching of datatype due to this data conversion component overall performance will slow down.

But what if conversion is done at the input source component then very first data conversion can be avoided. Just open the connection manager which reads CSV or text file here set datatypes and by doing this overall process will became faster.

What the difference is between compiled and debug mode in SSIS?

When "Start” button on Visual Studio is clicked it is actually executing package in debug or designer mode where it do checking or shows variables and working of each component along how program flow is being done.

But when ctrl+F5 is pressed on Visual Studio it will run program in compiled or release mode. It is running as if like program is deployed running it in production or executable environment and not through IDE unlikely as debug mode.

In debug mode it will run within the Visual Studio IDE itself

Whereas in compile mode i.e. after pressing ctrl+F5 it will open a separate command prompt screen and run in that DOS window as shown in the image below.

How to generate script of database and load it under project in Visual Studio?

With the help of scripting complete database structure can be scripted and it will help to load easily while make work faster as it is the main benefit served by scripting. With script deleting, recreating table, adding new fields i.e. modification becomes easy due to which whole re-working of database can be avoided. It will also keep database in proper sync with code.

So here will show how to generate in database, following are the practical steps demonstrated to create script in database.

Go to SQL Server Management, do right click on "CustomerDataWareHouse” take mouse over Task which will expand Task properties and on it click on "Generate Scripts”.

Once clicked on Generate Scripts it will open screen to generate scripts, on the very first page click on Next.

Please Note: As it is an Introduction screen it has option to omit it i.e. at start it won’t be shown again if tick mark is done on "Do not show this page again”.

Now Next Screen is to "Choose Object” where select the database object to script.

Do select first radio button "Script entire database and all database objects”

And then click on Next for following screen to come.

Here in this screen set Script option by selecting it’s Output Type to "Save scripts to a specific location”

And save script file to desired location or keep it as it is to default location in my documents.

Now to move to next, click on Click "Next” available option at the bottom.

Next screen is the summary of complete selection done in the wizard of creating script. Just go through it once so that checking can be done for all selected options.

Once found everything is proper just click on "Next” to save script created on the set default location selected.

For every action if you find the result is success then click on Finish button available at the bottom right.

If found any failure in result then go back to the screen where failure is found and do necessary correction to rectify it.

As the script is now been created successfully on the location set it wizard screen (in this case we have kept default location only) Next go and check whether script is properly generated on the location. Script file with name "script.sql” has been created under "My Document”.

Now copy "script.sql” from My Document location available on local drive and paste it in Visual Studio by doing right click on the project as shown in the image below to use it.

Once the file is copied it can be seen in Solution Explorer under Miscellaneous with its created name script.sql.

How can we sort records in SSIS?

Merge	Merge Join
Using Merge component achieving of merger can be done of two or more different file record.	Using Merge Join also merger of different files are done with a proper Lookup between the files.
For Merge there should be same field column present in both files to have merger.	To work with Merge Join there is no such criteria only there should be lookup present to do merge
It will just merge two same or heterogeneous files with exact fields present in it.	It will join two files same or different files with atleast one exact field for lookup.

What is the difference between Merge Join and Lookup?

Merge	Lookup
With Merge Join through Lookup it merges two sorted dataflow i.e. two sources or file can be combined using this.	With Lookup snother column is joined in the table(dataflow) by looking up values in the table.
Here it not only Lookup but also does work of merging dataflow(table).	It is popularly used with ID to lookup in the table using this more table info can be found.
It serves purpose by looking and merging data.	It will only make a lookup and does not do merging.
It combines two tables using FULL, LEFT or INNER Join.	Lookup uses equi-join between values in input and reference.

What is the need of SSAS?

The prime purpose of SSAS or need of SSAS is only to do processing on the data received from SSIS. So when data comes from SSIS then processing it by doing heavy calculation of sum, count or applying complicated formulas to do analysis and forecasting, all these activities are carried out by SSAS. Calculation are done at very faster rate using SSAS. As per the requirement or based on it different type of analysis can be performed over massaged data coming from SSIS. SSAS does some pre-calculation and save it in SSAS database and works very smartly to give analysis or helps to forecast it.

How does SSAS improve performance in Analysis calculation?

SSAS is a software mainly performs analysis and all it does with help of pre-calculation within SSAS to give fast output and has a very improved performance. It will run pre-calculation and stores it into its SSAS database. For every dynamic calculation where data is huge while again and again calculation are heavy under the circumstances it carries out pre-calculation due to which it boost performance. Following image represented process is carried out by SSAS to serve best performance in analysis calculation: -

What is the stored in SSAS database?

SSAS database stores pre-calculation where query is done to get fast analysis output. It saves pre-calculated database which runs at the background.

Name the two ways by which you can create SSAS project?

There are two ways of creating projects in SSAS:

1) Multidimensional(MDX) project.

2) Tabular project.

The two ways of creating SSAS project option are available under Add New Project when we click on Analysis Services.

What is the use of DataSource and Dataview?

With DataSource adding of server name with database can be done on which SSAS need to be implemented. So defining of database can be done in DataSource from where SSAS will get data to do analysis. Following is the image snapshot where Data Sources are found under Solution Explorer below to new SSAS project created.

DataView sources is the next which we see under Solution Explorer it helps to choose table from database that can selected as per user on which analysis can be runned.

It provides facility to add select and unselect tables to do analysis with SSAS project. Once all required tables are selected then on Solution Explorer below to SSAS project Data Sources Views can be found.

What is the named query in datasource?

A SQL query written to do work in customized way on Data Source View(dsv) to support certain requirement and get desired output is called as named query. It is used when using direct table do not help to achieve requirement. In scenario where we want primary keys to be present and make entries of duplicate record by marking it new or old. While using with database structure i.e. tables which is not able to achieve this so this can be fulfilled with the use of named query.

Can data view structure be different from physical database structure?

It is the Data view which helps to decouple physical database structure from SSAS database structure. Depending on the relationship created in physical table it automatically and very smartly it creates SSAS structure. Yes data view structure can be different from physical database structure because data view structure is created on SSAS database which is internal especially designed for analysis purpose whereas our physical database structure is saved on actual database server. Here in data view structure table can have relationship different table whereas there can be no relation between tables on actual database.

On data view structure tables and relations are created logically for analysis purpose on SSAS database. It can be created or deleted irrespective of having changes on tables present in physical database structure.

What is the need of CUBE and how is the structure of CUBE internally?

CUBE is the important part of SSAS. It is the whole purpose for making SSAS exist. All analysis, forecasting calculation done is saved within CUBE.

It’s built structure can be visualized as cube form where all calculation, analysis and forecasting like numbers is saved at centre which is termed as Fact or Measures. And sides or wall of the cube there are dimensions like strings are present.

What are measures?

SSAS heart is CUBE and it understands in terms of Fact or Measures and Dimension. At centre Measures or Fact are stored of CUBE. Fact or Measures plays a very vital role as numbers are stored here and all calculations, analysis and forecasting are carried out.

What is the use of Cube structure tab and the browser tab?

Cube structure tab has all information related to cube where it help us to show Fact or Measures on the left top side with inner details of CUBE which will have number or figure amount and pre-calculation. And down below on left side it has dimensions

Browser tab: It help us to show how the CUBE is created with measures and dimension. Just select the fact or measures and drop on the browser tab then select dimension drag and drop that too as per requirement on the tab as required to see the cube created. As dimension fields are selected cubes will be created accordingly.

Why do we have latency in SSAS data?

Data comes from physical database which is also called as OLTP RDBMS gets processed by CUBE and then it is saved on SSAS database or OLAP SSAS DB so during the whole process there is latency present. Latency means present but not visible during the process when data getting added in RDBMS does not get reflected instantly on SSAS OLAP DB for a while it goes out of sync until process button is clicked on Cube Structure to get data updated in SSAS database and make it visible. Concluding it that data present on SSAS database in not realtime updated there is a latency present.

How do we query SSAS database?

SSAS database uses MDX(Multi-dimension query) as writing or query language. While writing in SSAS use of MDX query is done for SSAS CUBE storage.

To see the MDX query written in SSAS, go to CUBE and in that click Browser -> then click on Design Mode from here MDX query can be seen.

With this it makes SSAS very much decoupled. Any reporting tool which understand or able to read MDX query can get displayed its result on its UI.

What does the time series wizard do?

In SSAS with time series wizard which creates permutation and combination for example range of years selected and can create CUBE and display as reports according to days, week, months, quarter and year wise. Under Dimension wizard it will create time period for days, week, months, quarter and year as shown in the image below :-

Explain the importance of Dimension Usage tab in SSAS?

The prime importance of Dimension Usage tab in SSAS is that it connects Fact or Measures and the Dimensions. It tells how the Fact or Measures are connected to Dimensions. Here in the image below it shows Fact or Measures "Fact Customer" is connected to Dimension "Dim Sales Person" through "Sales Person Id".

To set up relationship if it is not defined then click on to Fact field button which will open up screen to define relationship between Fact and Dimension as shown in the image below. Select relationship type to "Regular".

What does reconnect do in the SSAS browser tab?

It is the error to do "reconnect" which we get on browser tab if the display of the CUBE is kept open and when the processing of CUBE is done on server under CUBE Structure tab. So error to do "reconnect” can be seen at the bottom of browser tab.

Now if wish to see data on the browser tab again so there is a need to reconnect it again or click on the symbol as shown on the top.

It will once again do the reconnection and do reloading of the browser with fresh view and updated data to be displayed on to the browser.

What is the importance of hierarchical dimensions?

Under Dimension Structure hierarchy can be seen where under Year Quarter -> Month -> Date. This hierarchy is automatically created when used time series wizard

To see hierarchy in detailed form click on "Attribute Relationships" tab which shows relationship between Date -> Month -> Quarter -> Year

Hierarchy in dimensions provide aggregation where it total up each date, month, quarter and year wise to give up the total. If clicked on Year it will show up total year wise. If drag and dropped month then it will take up totaling from month and similarly it will show for date and so on.

Hierarchy makes aggregation possible and because of that only total of Date make sum of Month then Month will total upto Quarter and last Quarter will total upto Year. Due to hierarchy only various total of date, month, quarter and year wise gets displayed on browser tab.

How to use excel to see SSAS cube data?

Excel can be used as viewing tool to see SSAS cube data. And following are the simple steps by using which can be achieved.

• Go to excel click on Data.
• Click on "Get External Data”.
• Then "From Other Sources”.
• Click "From Analysis Services”.

Next is to connect to Database Server give here Server name as shown in the image below. Also set Log on credentials use Windows Authentication. Then click on "Next".

Now select the CUBE name as SSASCustomer and then click on Next to move to following screen.

In this screen just do check of Data Connection selected and click on "Finish" to save to the Data Connection.

Now select type of Data to be viewed on excel workbook, here select "PivotTable Report" and then click OK.

Here is the report which is seen on excel after setting the PivotTable Field List this report is same as it is seen on browser tab.

Hey guys if we missed any question kindly post it in the comment section below. If you have any query or doubts regarding any question or answer kindly let us know via comments