Msbi Interview Questions And Answers For Experienced Pdf
1. What are the Reporting Service Constituents in SSRS?
Answer:
- Report Manager: A Web-based administration tool for managing the Report Server
- Report Server: Provides services for implementation and delivery of reports.
- Report Designer: A place where we can create a report.
2. How we can do Error Handling?
Answer: SSIS package contains mainly have two types of errors :
- a) Procedure Error: It can be handled in Control flow by the precedence control and redirecting the execution flow.
- b) Data Error: it is handled in the DATA FLOW TASK by redirecting the data flow using the Error Output of a component.
3. How do you deploy SSIS packages?
Answer: SSIS Project BUILD provides a Deployment Manifest File. We need to run the manifest file and decide whether to deploy this onto File System or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQL Server.
4. What are variables and what is variable scope?
Answer:Variables store values that an SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.
Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.
5. How are Cubes implemented in SSAS?
Answer: Cubes are multidimensional models that store data from one or more sources. They can also store aggregations. SSAS Cubes are created using the Cube Wizard.
6. Where do you store the SSIS package in the SQL Server?
Answer: MSDB.sysdtspackages90 stores the original content.
7. What are the apparatuses included in SSRS?
Answer: A Complete set of tools that can be used to generate, succeed and view reports.
A Report Server component that hosts and processes report in many formats. Output formats include HTML, PDF and more.
An API that allows developers to mix or spread data and report processing in custom applications, or create custom tools to build and manage reports.
8. How to log SSIS Executions?
Answer: SSIS includes logging features that write log entries when run-time events occur. They can also write custom messages although it is not enabled by default. Integration Services supports a diverse set of log providers, giving you the ability to create custom log providers.
The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.
9. What Is Rolap?
Answer: ROLAP – Relational OLAP.
Designates one or several star schemas stored in relational databases. This technology permits multidimensional analysis with data stored in relational databases. (E Learning Portal) Used for large departments or groups because it supports large amounts of data and users.
10. I have a sql table that I need to split into a more 90 excel sheet based on a code. I could create a SSIS package and use a conditional split and create more than 90 excel sheet. But creating more than 90 excel sheets one at a time will be time-consuming and if I have to use that package again for another table then I would have to make changes. Is there an easier/faster way to achieve split a table into more than 90 excel tabs? Is it possible to use a for-each loop and dynamically split and create an excel tab?
Answer: Assuming that you have some rules to split the data. Best way to save this information in a table.
- 2. Create an optimized stored procedure which will accept the input parameters (above) and return the data
- 2. Add a script task
- 2.1 Here connect to the DB
- 2.2 Create a for loop
- 2.2.1 Call the SP created in step 2 and get the data in the dataSet
- 2.2.2 Add a new worksheet to your workbook
- 2.2.3 Insert data to the new worksheet
11. What is the control flow?
Answer: A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. (Company) A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements:
- Containers that provide structures in packages
- Tasks that provide functionality
- Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.
12. What are Data-Driven Subscriptions?
Answer: Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios: Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example, distribute a monthly report to all current customers. Distributing reports to a specific group of recipients based on predefined criteria. For example, send a sales performance report to the top ten sales managers in an organization.
13. Differentiate between OLAP and ETL tools?
Answer: OLAP is an online analytical processing tool.
ETL stands for Extract, Transform and Load. This is a product to extract the data from multiple/single sources and transform the data and load it into a table, flat file or simply a target.
14. What is the partition?
Answer: A partition in Analysis Services is the physical location of stored cube data. Every cube has at least one partition by default. Each time we create a measure group, another partition is created. Queries run faster against a partitioned cube because Analysis Services only needs to read data from the partitions that contain the answers to the queries. Queries run even faster when partition also stores aggregations, the pre-calculated totals for additive measures. Partitions are a powerful and flexible means of managing cubes, especially large cubes.
15. What is the use of a property called non-empty behavior while creating a new calculated member in a cube?
Answer: Nonempty behavior is used for ratio calculations. An MDX expression will return an error if the denominator is empty, just as it would if the denominator were equal to zero. By selecting one or more measures for the non-empty behavior property, we’re establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then the expression is also treated as empty and no error is returned.
16. Which are the different Lookup Cache Modes Available in SSIS?
Answer:There are three Cache Modes available in SSIS Lookup Transformation:
- Full Cache Mode: In this cache mode, SSIS queries the database before the start of the data flow task execution (pre-execute phase) and loads all the data from the lookup/reference table into SSIS lookup cache.
- Partial Cache Mode: In this cache mode, SSIS queries the database against new rows coming in from the source. If there is a match, the row is cached into SSIS Lookup Cache for rows coming subsequently in the data flow which might have a match. When the cache is full, SSIS then proceeds to remove few rows from the cache based on the usage/match statistics for those rows and loads the new matching rows into the Lookup Cache.
- No Cache Mode: In this cache mode, SSIS does not cache any rows into Lookup Cache (except in cases such as two subsequent source data rows having a match with same lookup values). For every row coming from the source, the database is queried to get the matching value/data from the reference table.
17. What is a RAGGED hierarchy?
Answer: Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not at the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.
18. What is Attunity Driver and why do we user in SSIS?
Answer: Attunity provides 2 high-speed connectors. One for Oracle and one for Teradata. They have been selected by Microsoft to be included with SQL Server 2008 Integration Services (SSIS) SQL 2008 Enterprise Edition. These drivers are highly optimized and very easy to use.
Optimized, best-in-class performance: The connectors deliver unparalleled throughput for extracting and loading data to and from Oracle and Teradata. Architected with Microsoft, the connectors use direct integration into internal SSIS buffering APIs, cutting through .NET and other layers and also use the high-speed load/extract utilities offered by Oracle and Teradata.
Ease-of-use: The connectors are fully integrated into the Business Intelligence Development Studio (BIDS), part of Microsoft Visual Studio 2008, offering a user experience similar to that of the SSIS generic OLEDB Connector, with intuitive capabilities including configuration, metadata browsing, and error routing.
19. Can we use datagrids for our report in SSRS?
Answer:We have an ASP.NET project that populates a datagrid. Using datagrid as my datasource for my report using SQL Server Reporting Services. Is this possible? The simple answer is no. However, nothing’s ever simple. A set of reporting controls was added in Visual Studio 2010 allows you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.
20. How we can do incremental load?
Answer: Accurate and fastest way to do incremental load is by using the timestamp column in the source table and then storing the last ETL timestamp.
21. How can we organize an SSIS Package on production?
Answer:
- A)Through Manifest we can organize it : Create deployment utility by setting its properties as true.
It will be formed in the bin folder of the solution once the package is built.
Copy all the files in the utility and use a manifest file to display it on the Prod. - B) Using DtsExec.exe utility.
- C)Import-Package directly in MSDB from SSMS by logging in Integration Services.
22. Can we apply style sheet in SSRS and how?
Answer: There are two ways to apply style sheet in SSRS
1. Hard Code in RDL File
2. Dynamically – You can save your style in DB and pull that in a DataSet and then apply.
For details please refer – https://en.wikipedia.org/wiki/Software_testing
23. How to show “No Data Found” Message to end-user?
Answer: Add a text box. Set expression of the text box = IIF(Count(,”DataSet1″)=0,”No Data Found”, Nothing)
and set the visibility of this text box = IIF(Count(,”DataSet1″)=0,False,True)
24. What new data source types were added in SSRS 2014?
Answer: In addition to the data source types available in SSRS (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2012: SQL Server Analysis Services SQL Server Integration Services SQL Server Report Builder Models XML (through URL and Web services)
25. How can I add Reporting Services reports to my application?
Answer: Visual Studio / SSDT / BI Data Tools (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.
26. Sometimes we need to debug out SSIS Package but we do not want to insert records in the destination but still, we want to use all the transformations and dump these all records in some dummy destination. The goal can be to check the extraction performance from source OR view data at different points of Package but we do not want to insert data in destination at all?
Answer: In this kind of scenario you can use below solutions-
- Multicast transformation
- Row Count Transformation
27. How to create a Temporary Table using SSIS? How?
Answer: You can use Execute SQL task to create a temp table and set the property Retain Same Connection on the Connection Manager to True so that a temporary table created in one Control Flow task can be retained in another task.
28. We get the files in our Source Folder all day long. Each file is an appended copy of the previous file. Do we want to create an SSIS Package that will load only the most recent file to our table?
Answer: Its simple to create 2 variables, folder path, and filename. Now in a script task, create a loop and find out the latest file. Now you can read the latest file in the script task itself and insert the data into the table.
29. What are Lazy aggregations?
Answer: The processing mode property of a partition/measure group determines how partitions will be available to users. Processing mode has two possible options – Regular and Lazy Aggregations.
Regular – Default. When set to regular, partitions will be available to users after data has been loaded and aggregations are created completely.
Lazy Aggregations – When setting to lazy aggregations, partitions will be available to user queries immediately after data has been loaded. Aggregations will be created as a separate background process while users start to query the partition.
Process Full will internally execute the Process Data and Process Index before the partition can be used for queries. If processing mode is set to Lazy Aggregations, the partition will be released for user queries after Process Data is completed. Process Index will be executed in the background. As aggregations don’t exist while users begin to query the partition they may experience slow performance.
30.Difference between the Cached Report and Snapshot Report?
Answer:
- Cached Report: Here the system will save the last executed report. It is saved in the temp DB. It is not persisted. It has a lifetime e.g. 1 hour or so. We can have 1 only one “instance” per report (if you have parameters, you will have one per combination of the parameter)
- Snapshot Report: It is a persisted copy of the report. It is stored for good on the report database. You can have as many as you want. You can configure for example to save a snapshot of a report per day, so if you want to see how was your data 1 month ago, you just access the snapshot of that day.
- When to use which one: Most of my reports, I cache them for 2 hours, so the first user who runs it will experience a small delay and the next will get the report on demand (with the data from when the report was run, of course)
For large reports, execute them at night and configure them to be run from a snapshot (option “Render this report from a report execution snapshot.
Note: Browse latest MSBI interview questions and MSBI tutorial. Here you can check MSBI Training details and MSBI Videos for self learning. Contact +91 988 502 2027 for more information.