MSBI interview questions

Q. What are the differences between TRUNCATE & DELETE clauses in SQL Server?
TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.

TRUNCATE removes all the records from a table without making a log entry for individual row deletions whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.

TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (optional) is used or not used respectively

TRUNCATE cannot be used on a table if it satisfies one of the following conditions:

The table is referenced by one of more FOREIGN KEY constraints
The table is marked/enabled for replicationTRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITy.

Q. What are the Command-Line Utilities available in Reporting Services?
Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file.

RsKeymgmt Utility: it is used to Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access.

Q. Name different types of data sources in SSRS?
SSRS uses a different data source. Some of them are listed below.

• SQL Server SAP Net weaver BI.
• Report Server Model.
• SQL Server Analysis Service OLEDB.
• SAP Net weaver BI.
• XML

Q. What are the differences between DTS and SSIS?
Data Transformation Services SQL Server Integration Services

Limited Error Handling Complex and powerful Error Handling
Message Boxes in ActiveX Scripts Message Boxes in .NET Scripting
No Deployment Wizard Interactive Deployment Wizard
Limited Set of Transformation Good number of Transformations
NO BI functionality Complete BI Integration.

Q. What are the benefits of using embedded code in a report?
The benefits are:

Reusability of Code: function created in embedded code to achieve a logic can be then used in manifold expressions
Centralized code: it helps in the better manageability of code.

Q. How we can schedule an SSIS package?
Using SQL Server Agent we can schedule an SSIS package.

Q. What Are Variables And What Is Variable Scope?
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.

Q. Can You Name Five Of The Perfmon Counters For Ssis And The Value They Provide?

• SQLServer: SSIS Service
• SSIS Package Instances
• SQLServer: SSIS Pipeline
• BLOB bytes read
• BLOB bytes are written
• BLOB files in use
• Buffer memory
• Buffers in use
• Buffers spooled
• Flat buffer memory
• Flat buffers in use
• Private buffer memory
• Private buffers in use
• Rows read
• Rows wrote.
• S Utility: this utility is mainly used to automate report server
• deployment.

Q. How to create Drill-Down reports?
By grouping data on essential fields

Then toggle reflectivity based on the grouped filed.

Q. What is MSBI (Microsoft Business Intelligence?
Answer: Microsoft Business Intelligence (BI) is a suite of products and tools that you can use to monitor, analyze and plan your business by using scorecards, dashboards, management reporting, and analytics.

Q. What are the tools in MSBI?
It contains the following tools:

• SQL Server Analysis Services (SSAS)
• SQL Server Integration Services (SSIS)
• SQL Server Reporting Services (SSRS)
• Performance Point Services (PPS) which was added as a free service in Microsoft Office SharePoint 2010.

Q. How we can create Drill-through reports?
By Using Navigation property of a cell and scenery child report and its parameters in it we can create Drill-through reports.

Progress execution by practicing Linux software invasion and striping over four extents.

Q. While creating a new calculated member in a cube what is the use of a property called non-empty behavior?
Nonempty behavior is an important property for ratio calculations. If the denominator is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are 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.

Q. Explain transfer SQL Server object task?
It allows us to allocate different SQL server objects between different instances of SQL Server.Object incomes from the table, stored procedures, user-defined functions, etc.

Q. Name a few Business Analysis Enhancements for SSAS?
The following table lists the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

Q. 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’t 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 relatives in that dimension.”

Q. What Are The Types Of Parameters In Ssrs?
The types of parameters in SSRS are:

• Single value parameters
• Multivalue parameters
• Cascading parameters.

Q. What Is Hyperion? Is It An Olap Tool?
It’s Business Intelligence tools. Like Brio which was an independent product bought over my, Hyperion has converted this product name to Hyperion Intelligence.

Yes. You can analyze data schemas using these tools.

Q. 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.

Q. What are the Transformations available in SSIS?
AGGREGATE ­ It applies aggregate functions to Record Sets to produce new output records from aggregated values. AUDIT ­ Adds Package and Task level Metadata ­ such as Machine Name, Execution Instance, Package Name, Package ID, etc.. CHARACTERMAP ­ Performs SQL Server column level string operations such as changing data from lower case to upper case. CONDITIONALSPLIT– Separates available input into separate output pipelines based on Boolean Expressions configured for each output. COPYCOLUMN ­ Add a copy of column to the output we can later transform the copy keeping the original for auditing. DATACONVERSION ­ Converts columns data types from one to another type. It stands for Explicit Column Conversion. DATAMININGQUERY– Used to perform data mining query against analysis services and manage Predictions Graphs and Controls. DERIVEDCOLUMN ­ Create a new (computed) column from given expressions. EXPORTCOLUMN– Used to export a Image specific column from the database to a flat file. FUZZYGROUPING– Used for data cleansing by finding rows that are likely duplicates. FUZZYLOOKUP ­ Used for Pattern Matching and Ranking based on fuzzy logic. IMPORTCOLUMN ­ Reads image specific column from database onto a flat file. LOOKUP ­ Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only. MERGE ­ Merges two sorted data sets into a single data set into a single data flow. MERGEJOIN ­ Merges two data sets into a single dataset using a join junction. MULTICAST ­ Sends a copy of supplied Data Source onto multiple Destinations. ROWCOUNT ­ Stores the resulting row count from the data flow / transformation into a variable. ROWSAMPLING ­ Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage. UNIONALL ­ Merge multiple data sets into a single dataset. PIVOT– Used for Normalization of data sources to reduce analomolies by converting rows into columns UNPIVOT– Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses.

Q. How to log SSIS Executions?
SSIS includes logging features that write log entries when run­time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives 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.

Q. How do you deploy SSIS packages?
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 . 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.

Q. 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.

Q. Can you name five of the Perfmon counters for SSIS and the value they provide?
SQLServer:SSIS Service SSIS Package Instances SQLServer:SSIS Pipeline BLOB bytes read BLOB bytes written BLOB files in use Buffer memory Buffers in use Buffers spooled Flat buffer memory Flat buffers in use Private buffer memory Private buffers in use Rows read Rows written

Q. 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.

Q. Explain the TWO­Tier Architecture of SSAS?
SSAS uses both server and client components to supply OLAP and data mining functionality BI Applications. The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services implemented as a separate instance of the Windows service. Clients communicate with Analysis Services using the standard XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as a web service.

Q. 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.

Q. What is FASMI ?
A database is called an OLAP Database if the database satisfies the FASMI rules : Fast Analysis– is defined in the OLAP scenario in five seconds or less. Shared – Must support access to data by many users in the factors of Sensitivity and Write Backs. Multidimensional – The data inside the OLAP Database must be multidimensional in structure. Information – The OLAP database Must support large volumes of data..

Q. 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.

Q. 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).

Q. While creating a new calculated member in a cube what is the use of property called non­empty behavior?
Nonempty behavior is important property for ratio calculations. If the denominator Is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non­Empty Behavior property, we are 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.

Q. What is a RAGGED hierarchy?
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 in 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.

Q. 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.

Q. 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.

Q. What is WriteBack? What are the pre­conditions?
The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.

Q. 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.

For more  Click Here