MSBI Interview Questions and Answers

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 a 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 the 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 the 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 an 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 procedures.

Q11) What is the difference between the Merge and Union All transformation in MSBI.

Answer: Merge only gives the output in a 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 the 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 a data warehouse?

Answer: Collection of dimensions 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?

Answer: No

Q18) Explain the slowly arriving dimension scenario?

When the data for dimensions arrives later than that of FACTs.

Q19) Can you parameterize SSRS reports?

Answer: Yes

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 the 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 the Time Intelligence feature  which enables easy use of the YTD, QTD , MTD functionalities.

Q32) Is it possible to use a date other than the current date as reference for getting Year to Date, Quarter To Date aggregations in a data warehouse?

Answer: Yes

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 data source 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 the 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 an OLEDB source?

Answer: It is useful when all the source columns need not 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 the 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.

Q51) What Are The Different Kinds Of SSRS Reports?

• Reports can be managed into operational and cautious reports.
• The pack depends on the wellspring of information and level of assessment connected by a specific report.
• Operational reports depend on OLTP sources and are static reports and Analytical reports depend on OLAP sources and everything thought about attract drill-down and drill-through for evaluation.
• The explanation behind the truth, SSRS reports can be directed into parameterized, related, gathering, set away, and so forth…

Q52) Clarify can SSRS reports Cache results?

• Sparing reports draw in clients to access and view reports in a general sense snappier.
• SSRS licenses Cache gives a record of the overseeing server.

Q53) Clarify by what method may you store your referencing in an SSRS report or a Database server?

• Regulating SQL questions truly in the substance approach in the information ought to be kept up a key superior to standard ways.
• Then again perhaps, it ought to be coordinated in a set away framework in the database server.
• The bit of room is that the SQL would be in an amassed strategy in an SP and gives a large portion of the upsides of SP showed up contrastingly in relationship with utilizing a with no managing arrangements from the report.

Q54) What are the Reporting Services parts?

• Revealing affiliations domains help progress.
• These controlling parts mix a few instruments that are utilized to make, control and view reports.
• Report Designer is balanced making the reports.
• Report Server is utilized for executing and scattering reports.
• Report Manager contemplates dealing with the report server.

Q55) What sorts of records can report be continued running under?

The Report Server Service can be continued running under a trademark help account, a Windows account on your close to PC, or in your structure space.

Q56) What are SSRS reports used for?

SQL Server Reporting Services (SSRS) is a server-based report passing on programming structure made by Microsoft and used as a response for affiliations who need to produce custom reports from a get-together of data sources, for instance, SQL databases and other outside sources, that empowers supervisor to share reports.

Q57) What is a related report in SSRS?

• A related report is a report server thing that gives a path to a present report. Keenly, it would seem that a program clear course that you use to run a program or open a record.
• A related report is gotten from a present report and holds the center’s report definition.

Q58) What is SSRS and why it is used?

• SQL Server Reporting Services (SSRS) is a server-based report making programming structure from Microsoft.
• The SSRS affiliation gives an interface into Microsoft Visual Studio with the objective that designers, in like manner as SQL administrators, can interface with SQL databases and use SSRS instruments to manage SQL reports from different points of view.

Q59) What is Snapshot Report in SSRS?

• A report see is a report that contains plan information and requesting results that were recouped at a specific point in time.
• Not the slightest bit like on-demand reports, which get cutting edge question results when you select the report, report sneak apexes are overseen on a timetable and sometime later saved to a report server.

Q60) What is falling parameters in SSRS?

• Falling parameters give a framework for supervising a ton of report data.
• You can delineate a lot of related parameters with the objective that the snappy diagram of characteristics for one parameter depends on the value picked in another parameter. For example, the central parameter is free and may show a snappy outline of thing classes.

Q61) What is the drill-down report in SSRS?

• Drill Down Reports in SSRS.
• The Drill Down Reports in SSRS means drawing in Users to Show or Hide the Column Data by giving in spite of and fewer pictures on a book box (essentially, Providing Interactivity to the Users).

Q62) What is SSRS organizing?

SSRS is a concentrated and extensible uncovering stage, which joins an organized system of managing parts, adjusted interfaces, and instruments.

Q63) What are SSIS SSAS and SSRS in SQL?

The SQL Server Analysis Services, or SSAS, is a multidimensional evaluation contraption that features Online Analytical Processing, astonishing data mining limits, and dynamically important estimations to business information inside a social database.

Q64) What is the explore report in SSRS?

• A click through report is a report that introductions related data from a report model when you click the characteristic data contained inside your model-based report.
• These reports are passed on by the report server subject to the information contained inside the report model.

Q65) What is Report Subscription in SSRS?

• A Reporting Services intrigue is a structure that passes on a report at a specific time or in light of an event, and in a record position that you show.
• Enrollments can be used to design and robotize the vehicle of a report and with a specific procedure of report parameter regards.

Q66) What is a data driven selection SSRS?

• A data driven selection gives a way to deal with oversee use of dynamic intrigue data that is recouped from an external data source at run time.
• A data driven speculation can what’s more use static substance and default regards that you show when the enrollment is portrayed.

Q67) What is the partition between the table and the structure?

• In the table and system plans, cells are overseen into lines and domains.
• The key partition among tables and structures is that tables can join basically line parties, while systems have line get-togethers and fragment get-togethers.

Q68) Do you make your reports using the wizard or physically?

• Also similarly as with most Microsoft gadgets, SSRS consolidates a wizard that energizes you make reports.
• The wizard is exceptional for people who are new to the thing, yet it’s awful in case you can’t go past the mechanical assemblies and make a report without any planning.
• Before you go for your gatherings, guarantee you can make a report without any planning without any wizards or mechanical assemblies to make them for you.
• Most associations need someone who can change reports, and wizards leave space for by no customizations.

Q69) What are request parameters?

• Request parameters are the bits of a SQL question that empower you to channel results. Parameters are contained in the SQL’s “the spot” explanation.
• These parameters tell the SQL server which records you have to invigorate, select, or delete.
• By the day’s end, notwithstanding the where stipulation parameters, you would impact all records in your tables.

Q70) What is a sub-report?

• Sub-reports are installed in the standard report.
• Much equivalent to the essential report, you similarly pass parameters and request to it.
• Consider a sub-report as an enlargement to your essential report, anyway it contains another enlightening record.
• For instance, you could make a report of customers and thereafter use a sub-report to show a summary of solicitations for each customer.

Q71) What are the RDL reports?

• RDL records take after the “language” for SSRS servers.
• Regardless, RDL records are made in XML, so the “language” of SSRS servers is XML.
• PDF records contain the request and configuration bunch for your reports.

Q72) What is the enlightening record?

• Enlightening lists are the sections that contain your records.
• You can have an unmistakable instructive file, an enlightening list with one record, or an educational list with considerable number records.
• You can see an instructive record like an altered table.
• The enlightening gathering has the fragments and lines like a table, yet you choose the areas and number of sections using your SQL enunciations.

Q73) What is the data source?

• A data source is the database and tables where your instructive gathering begins from. You ought to describe the data source when you make your database affiliation.
• Every datum set needs a source from which to pull the data. Your specifying server can be SQL Server 2000, 2005, 2008 and 2012.

Q74) What servers can be used with SSRS?

• While most associations use SQL Server with SSRS, you can similarly join other database servers with your SSRS reports.
• SSRS is flawless with Oracle, ODBC and OLEDB affiliations, Hyperion, Teradata and level XML records that contain data.

Q75) What is mixed mode database security?

• Exactly when you present SQL Server, you have the choice to allow SQL Server to arrange with Windows or anticipate that customers should have an alternate SQL Server customer name and mystery word.
• While Windows coordination is beneficial, it’s not seen as the most secure of the two security decisions.
• It’s more brilliant to require an alternate SQL Server customer name and mystery key when marking into the database server.
• Your reports will require their customer name and mystery expression to run reports from SSRS.

Q76) Could SSRS report hold results?

• Putting away reports makes it much snappier for customers to access and view data.
• If you have normal data that doesn’t change regularly, it’s optimal to store results.
• SSRS allows you to save gives a record of your noteworthy server.
• This suggests it reduces the pile on the SQL Server and your customers can quickly get to data without scrutinizing the server a couple of times.

Q77) What designs can SSRS charge or render to?

• SSRS is impeccable with a couple of report gatherings.
• Right when the customer runs the report, the default design prints to the web program.
• You can in like manner extra or passage to HTML, Excel, CSV, Image, PDF and XML plans.
• The SSRS uncovering programming has gets at the most elevated purpose of each report that the customer can use to convey these records.

Q78) How might you send a report to a noteworthy server?

• The SSRS uncovering programming fuses instruments to pass on authentically to the SQL Server itemizing server.
• After you’ve planned your item with the most ideal SQL Server, customer name and mystery key, you just need to right-tap the report you have to pass on and click “Send.”
• Make sure you test the report after you send it to ensure there are no bugs and that the data returned is correct.

Q79) How might you adjust a report?

• Open SQL Server Management Studio and the reporting adventure.
• Develop the center point that contains your reports.
• Right-click the report and snap “Modify.” You can in like manner save your noteworthy record from this view.

Q80) How may you approach working up an SSRS report?

• The general development approach for an SSRS report is to start by making a data source.
• Considering the data source make one or differing datasets as required for parameters and the body of the report.
• Wire required controls from the instrument stash which would go about as a compartment for the fields in the dataset. Structure the controls added to the report body.
• Check and grasp the report finally pass on the report.