In case you’re searching for MSBI Interview Questions and answers for Experienced or Freshers, you are at the correct place. GangBoard offers Advanced MSBI Interview Questions and answers that assist you in splitting your MSBI interview and procure dream vocation as MSBI Developer.
Q1) What kind of Privileges do you need on database to be able to use BULK Insert transformation ?
Answer: The account running the package should have write access to the tempdb of the target database .
Q2) How would you debug an SSIS package ?
Answer: Using breakpoints .
Q3) What is the use of Expression task ?
Answer: To set User variables’ values during run time without having to use Script task .
Q4) Which are blocking transformations in SSIS ?
Answer: Sort , Merge , Merge Join .
Q5) What can you use to get the Cube measure values
Answer: DAX, MDX .
Q6) Purpose of Partitions in SSAS ?
Answer: Processing only the data that is changing rather than the legacy data as well , hence mitigating Cube process time .
Q7) What can be used to host SSRS Report server ?
Answer: Both native installation and Sharepoint mode installations can be used , in case of latter the SQL server installation should be in accordance with SharePoint .
Q8) What is SCD-2 ?
Answer: Slowly changing transformation , type 2
Q9) When do use SCD -2 , give example
Answer: Employee table where the employee location , name etc changes but employee ID does not and we need to keep the entire history of changes to any attributes of the employee entity .
Q10) How can you implement SCD ?
Answer: It can be implemented by both SSIS and stored procedure .
Q11) What is the difference between the Merge and Union All transformation in MSBI .
Answer: Merge only gives the output in sorted manner while UNION ALL does not .
Q12) Which different versions of any of the tools in MSBI stack have you worked , any illustrations on difference in functionalities ?
Answer: 2008 R2 , 2012 , 2016
Expression Task was introduced in SSIS 2012
SSAS Tabular was introduced starting 2012 .
SQL 2016 contained new date functions like EndOfMonth etc .
Q13) How to dynamically process Cubes ?
Answer: Using XMLA
Q14) What is data warehouse ?
Answer: Collection of dimensons and facts
Q15) What are Facts ?
These contain the aggregations built on top of transactional data .
Q16) What are dimensions ?
Answer: These represent entities about which factual data or aggregations are collected .
Q17) can you use ORDER BY in a view ?
Q18) Explain the slowly arriving dimension scenario ?
When the data for dimensions arrives later than that of FACTs .
Q19) Can you parameterize SSRS reports ?
Q20) What are cascading parameters ?
Answer: It is used when the drop down list of one parameter impacts the values in the drop down list of other , like Country and Region .
Q21) How can you prepare drill down SSRS reports ?
Answer: By using Row Groups .
Q22) What are subreports ?
Answer: These are reports that are designed to be opened from main reports by specifying Action which can be to ‘Go to URL’ or ‘Go to Report’ etc.
Q23) What are subscriptions in SSRS?
Answer: It is used to process the report and send it to a target audience .
Q24) What is data driven subscription ?
Answer: It is where the attributes required to create a subscription are drawn from database fields .
Q25) What are the various components of SSRS ?
Answer: Report server , Report Manager , Report Builder , Report Configuration manager .
Q26) How would you debug a script task in SSIS ?
Answer: Using breakpoints .
Q27) Purpose of Checkpoints in SSIS ?
Answer: It is used to start the package execution from last successful run
Q28) What property in SSIS needs to be set to prevent the ForEachLoop container from failing due to failure of a contained task ?
Answer: PropagateError .
Q29) What are the DAX functions you have used ?
Answer: SUM , CALCULATE , SWITH , RELATED , LOOKUP , YEAR, MTD .
Q30) What is YTD ?
Answer: Year to date meaning it get the records which lie from the Start of the year uptill the date field value in the row
Q31) What is Time Intelligence in SSAS ?
Answer: SSAS project can be configured to use Time Intelligence feature which enables easy use of the YTD , QTD , MTD functionalities .
Q32) Is it possible to use a date other than current date as reference for getting Year to Date , Quarter To Date aggregations in a datawarehouse ?
Q33) What are the modes of SSAS ?
Answer: Multidimensional , Tabular .
Q34) What actions are required for publishing an SSRS report to SharePoint ?
Answer: You need to remap the datasource and dataset and create them beforehand .
Q35) How to run a piece of XMLA code in a batch ?
Answer: Using <BATCH> </BATCH> .
Q36) Name some transformation sin SSIS ?
Answer: Bulk Insert , Lookup , Merge , Merge Join , Fuzzy Lookup .
Q37) Which SSIS task can be used to send DBmail ?
Answer: NotifyOperator .
Q38) If same task can be achieved using SQL and SSIS , which would you prefer and why ?
Answer: SQL for better performance , Sorting is a classic example .
Q39) What are package configurations ?
Answer: These are used to facilitate mo e dynamic and smooth package deployments across PROD ,DEV and test environments .
Q40) How to call a child package from Parent package ?
Answer: Using parent child package configurations.
Q41) What are layers involved in creating a datawarehouse ?
Answer: Landing , Staging and Modelling where Landing contains data received from sources , Staging layer contains the transformations made to fit the data for aggregations , Modelling is where aggregations are stored .
Q42) How to create aggregations in SSAS tabular ?
Answer: Using DAX .
Q43) How to dynamically create SQL command in SSIS ?
Answer: Using expressions and variables.
Q44) What is the benefit of using SQL command in a OLEDB source ?
Answer: It is useful when all the source columns need not to be pulled .
Q45) What is the default mode of SSAS ?
Answer: Multidimensional .
Q46) State differences between the Multidimaensional and Tabular
Answer: Tabular doesnot have DataMining algorithms
The dataprocessing upper limit of tabular is less than multidimensional .
Q47) Any other tools that use DAX ?
Answer: PowerBI .
Q48) How can you configure the Report server URL ?
Answer: Using Report Configuration Manager .
Q49) What is a shared file subscription ?
Answer: It is where the report file is dropped in a shared location by the Reporting services .
Q50) How to debug an expression at runtime in SSIS ?
Answer: By using breakpoints and assigning the expression to a variable .