MSBI INTERVIEW QUESTIONS
What is SSIS? How it is related with SQL Server.
- SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server. This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.
What are the tools associated with SSIS?
We use Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) to work with Development of SSIS Projects. We use SSMS to manage the SSIS Packages and Projects.
How does Error-Handling work in SSIS
When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.
Type of typical Errors in SSIS:
-Data Connection Errors, which occur incase the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.
-Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.
-Expression Evaluation errors, which occur if expressions that are evaluated at run time perform invalid
What is a workflow in SSIS 2014 ?
Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.
What is the control flow?
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. 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, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.
What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable. Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
What are variables and what is variable scope?
Variables store values that a 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.
What is the need for SSAS component?
Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations.
SSAS is very easy to use and interactive.
Faster Analysis and Troubleshooting.
Ability to create and manage Data warehouses.
Apply efficient Security Principles.
What are the components of SSAS?
An OLAP Engine is used for enabling fast adhoc queries by end users. A user can interactively explore data by drilling, slicing or pivoting.
Drilling refers to the process of exploring details of the data.
Slicing refers to the process of placing data in rows and columns.
Pivoting refers to switching categories of data between rows and columns.
In OLAP, we will be using what are called as Dimensional Databases.
What languages are used in SSAS ?
Structured Query Language (SQL)
Multidimensional Expressions (MDX) – an industry standard query language orientated towards analysis
Data Mining Extensions (DMX) – an industry standard query language oriented toward data mining.
Analysis Services Scripting Language (ASSL) – used to manage Analysis Services database objects.
How Cubes are implemented in SSAS ?
- Cubes are multidimensional models that store data from one or more sources.
Cubes can also store aggregations
SSAS Cubes are created using the Cube Wizard.
We also build Dimensions when creating Cubes.
Cubes can see only the DSV( logical View).
What is the difference between a derived measure and a calculated measure?
The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure arent stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.
What are the roles of an Analysis Services Information Worker?
The role of an Analysis Services information worker is the traditional “domain expert” role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.
What are the different ways of creating Aggregations?
We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO Usage Based Optimizations. Always, prefer UBO method in realtime performance troubleshooting.
What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.
What is processing?
- Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators. We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.
What MDX functions do you most commonly use?
This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells dont roll up to a natural ancestor. Indeed, CrossJoin has easily been my bread and butter.
Where do you put calculated members?
The reflexive answer is in the Measures dimension but this is the obvious answer. So I always follow up with another question. If you want to create a calculated member that intersects all measures, where do you put it? A high percentage of candidates can not answer this question, and the answer is In a dimension other than Measures. If they can answer it, I immediately ask them why. The answer is Because a member in a dimension cannot intersect its own relatives in that dimension.
What is SSRS?
SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applicatons. It can be competed with Crystal Reports and other business intelligence tools.
How to finetune Reports?
To tune-up the Reporting Services, follow the below mentioned ways: – Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report applications logic and characteristics can have a duplicate copy of data. – Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.
How does the report manager work in SSRS?
Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.
What are the Reporting Services components?
Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server.
How does Report Builder support Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.
Can you import Microsoft Excel data to SSRS?
Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you’re asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You’ll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.
Explain what data regions are and what are the different data regions?
Data regions are report items that show repeated rows of summarized information from datasets.
Different data regions include
List out what other servers you can use with SSRS?
Most of the times companies use SQL server with SSRS, but there are other servers you can integrate
ODBC and OLEDB
Flat XML files
Name some of the open source software that you can use in alternative to SSR?
- Some of the open source software alternatives are
Jasper Reports JFree Report BIRT (Business Intelligence Reporting Tool) OpenReport DataVision and so on
Mention what is the main function of a query parameter?
The main function of a query parameter is to filter data in the data source.
What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.