25+ Common MSBI Interview Question & Answer - Complete Guide
MSBI Interview Questions and Answers

25+ Common MSBI Interview Question & Answer – Complete Guide

Last updated on 04th Jun 2020, Blog, Interview Questions

About author

Prathap (Sr Business Intelligence (BI) Developer )

Delegates in Corresponding Technical Domain with 11+ Years of Experience. Also, He is a Technology Writer for Past 7 Years & Share's this Informative Blogs for us.

(5.0) | 16547 Ratings 1701

MSBI stands for Microsoft Business Intelligence. MSBI (MicroSoft Business Intelligence) is one of the ETL tools related to the database side. It empowers users to gain access to accurate and up-to-date information for better decision making.It is composed of tools which help in providing best solutions for Business Intelligence and Data Mining Queries. Also provides an analytic solution to visualize your data in the organization.

1. What Is Ms Bi (microsoft Business Intelligence)?

Ans:

 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.

2. What Are The Tools In Ms Bi?

Ans:

It contains the following tools:

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

3. What Is Ssis? How Is It Related With Sql Server?

Ans:

  • 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 the 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.

4. What Are The Tools Associated With Ssis?

Ans:

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

5. What Is A Workflow In Ssis ?

Ans:

Workflow is a set of instructions on how to specify the Program Executor on how to execute tasks and containers within SSIS Packages.

6. What Are The Differences Between Dts And Ssis?

Ans:

Differences Between Dts And Ssis

7. What Is The Control Flow?

Ans:

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. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server 2005 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.

8. What Is A Data Flow?

Ans:

A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

9. How Does Error-handling Work In Ssis?

Ans:

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 in case 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.

10. What Is Environment Variable In Ssis?

Ans:

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

11. What Are The Transformations Available In Ssis?

Ans:

  • 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.
  • CHARACTER MAP : Performs SQL Server level makes string data changes such as changing data from lowercase to uppercase.
  • CONDITIONAL SPLIT : Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
  • COPY COLUMN : Add a copy of column to the output we can later transform the copy keeping the original for auditing.
  • DATA CONVERSION : Converts columns data types from one to another type. It stands for Explicit Column Conversion.
  • DATA MINING QUERY : Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.

12. How To Log Ssis Executions?

Ans:

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.

13. How Do You Deploy Ssis Packages?

Ans:

BUILDing SSIS Projects 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 fast and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQL Server.

14. What Are Variables And What Is Variable Scope ?

Ans:

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.

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

Ans:

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

16. What Is Analysis Services? List Out The Features?

Ans:

 Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting us design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets we design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence also including reporting features.

Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.

Some of the key features are:

  • Ease of use with a lot of wizards and designers.
  • Flexible data model creation and management
  • Scalable architecture to handle OLAP
  • Provides integration of administration tools, data sources, security, caching, and reporting etc.
  • Provides extensive support for custom applications.

17. What Is Udm? Its Significance In Ssas?

Ans:

The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the construction of the UDM, additional benefits accrue from the richness of metadata that the model can provide.

The UDM provides the following benefits:

  • Allows the user model to be greatly enriched.
  • Provides high performance queries supporting interactive analysis, even over huge data volumes.
  • Allows business rules to be captured in the model to support richer analysis.

18. What Is The Need For A Ssas Component?

Ans:

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

19. Explain The Two-tier Architecture Of Ssas?

Ans:

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

20. What Are The Components Of Ssas?

Ans:

  • An OLAP Engine is used for enabling fast ad hoc 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.

    Subscribe For Free Demo

    21. What Is Fasmi ?

    Ans:

    A database is called a 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.

    22. What Languages Are Used In Ssas ?

    Ans:

    • 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) – u

    23. How Cubes Are Implemented In Ssas ?

    Ans:

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

    24. What Is The Difference Between A Derived Measure And A Calculated Measure?

    Ans:

     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 aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

    25. What Is A Partition?

    Ans:

     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.

    26. While Creating A New Calculated Member In A Cube What Is The Use Of Property Called Non-empty Behavior?

    Ans:

     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.

    27. What Is A Ragged Hierarchy?

    Ans:

    Under normal circumstances, each level in a hierarchy in Microsoft SQL Server 2005 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 drill down paths. Expanding through every level for every drill down path is then unnecessarily complicated.

    28. What Are The Roles Of An Analysis Services Information Worker?

    Ans:

    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.

    29. What Are The Different Ways Of Creating Aggregations?

    Ans:

    We can create aggregations for faster MDX statements using Aggregation Wizard or through UBO – Usage Based Optimizations. Always, prefer the UBO method in real time performance troubleshooting.

    30. What Is Writeback? What Are The Pre-conditions?

    Ans:

     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.

    31. What Is Processing?

    Ans:

     Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 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.

    32. Name Few Business Analysis Enhancements For Ssas?

    Ans:

     The following table lists the business intelligence enhancements that are available in Microsoft SQL Server 2005 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.

    Business Analysis Enhancements For Ssas

    33. What Mdx Functions Do You Most Commonly Use?

    Ans:

    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 to identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

    Course Curriculum

    Become a Expert in BI & Data Mining Tools By Enrolling MSBI Training

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    34. How Do I Find The Bottom 10 Customers With The Lowest Sales In 2003 That Were Not Null?

    Ans:

    Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

    • SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
    • BOTTOMCOUNT(
    • NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
    • , [Customer].[Customer Geography].[Customer] )
    • , ( [Measures].[Internet Sales Amount] ) )
    • , 10
    • , ( [Measures].[Internet Sales Amount] )
    • ) ON ROWS
    • FROM [Adventure Works]
    • WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

    35. How In Mdx Query Can I Get Top 3 Sales Years Based On Order Quantity?

    Ans:

    By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy ” and “OrderByAttribute ”. Let’s say we want to see order counts for each year. In AdventureWorks MDX query would be:

    • SELECT {[Measures].[Reseller Order Quantity]} ON 0
    • , [Date].[Calendar].[Calendar Year].Members ON 1
    • FROM [Adventure Works];
    • Same query using TopCount:
    • SELECT
    • {[Measures].[Reseller Order Quantity]} ON 0,
    • TopCount([Date].[Calendar].[Calendar Year].Members,3, 
    • [Measures].[Reseller Order Quantity]) ON 1
    • FROM [Adventure Works];

    36. How Do You Extract First Tuple From The Set?

    Ans:

    Use could usefunction Set.Item(0)

    Example:

    • SELECT {{[Date].[Calendar].[Calendar Year].Members
    • }.Item(0)}
    • ON 0
    • FROM [Adventure Works]

    37. How Can I Setup Default Dimension Members In Calculation Script?

    Ans:

    You can use the ALTER CUBE statement.

    Syntax:

    • ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION <dimension name>, 
    • DEFAULT_MEMBER='<default member>’;

    38. Differences Between Script Task & Script Component And Its Advantages?

    Ans:

    Script task is control flow level item whereas script component is data flow level item, both of the functionalities are same. These 2 are very powerful items in SSIS.

    Normally we can implement custom code by using these components. By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well.

    39. What Is A Master Package?

    Ans:

    Master package is nothing but it is also a normal SSIS package, it acts like a master to run all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package is called a master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.

    40. Difference B/w Cube-deploy And Process?

    Ans:

    Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual Sql server analysis server. This process is called DEPLOYMENT of the cube. (like creating one object(table) in the relational database).

    So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table) So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to a SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube.

    41. What Is The Use Of Data Source View?

    Ans:

    Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source.

    42. Differences Between Calculated Members And Calculated Measures?

    Ans:

    • Member is nothing but columns on the dimension table. Measure is nothing but columns on the fact table.
    • The term calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the measured dimension where a simple MDX expression such as addition or subtraction of two or more base measures results in a new measure. Such calculated members on the measure dimension are referred to as calculated measures.

    43. What Are The Types Of Parameters In Ssrs?

    Ans:

    The types of parameters in SSRS are:

    • Single value parameters
    • Multi value parameters
    • Cascading parameters.

    44. Difference B/w View And Materialized View?

    Ans:

    Views:

    • A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
    • All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
    • A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
    • In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

    Materialized Views:

    • Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
    • A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
    • The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

    45. What Is Hyperion? Is It An Olap Tool?

    Ans:

    • It’s a Business Intelligence tool. 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.

    46. What Is The Difference Between Olap And Etl Tools?

    Ans:

    • OLAP: It’s an online analytical processing tool. There are various products available for data analysis.
    • ETL: Extract, Transform and Load. This is a product to extract the data from multiple/single sources, transform the data and load it into a table, flat file or simply a target.

    47. What Is Olap?

    Ans:

    OLAP : On-Line Analytical Processing.

    Designates a category of applications and technologies that allow the collection, storage, manipulation and reproduction of multidimensional data, with the goal of analysis.

    48. What Is Molap?

    Ans:

    MOLAP : Multidimensional OLAP.

    This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances performances. In the latter, joins are computed during the request.

    Targeted at groups of users because it’s a shared environment. Data is stored in an exclusive server-based format. It performs more complex analysis of data.

    49. What Is Dolap?

    Ans:

    DOLAP : Desktop OLAP.

    Small OLAP products for local multidimensional analysis Desktop OLAP. There can be a mini multidimensional database (using Personal Express), or extraction of a datacube (using Business Objects).

    Designed for low-end, single, departmental users. Data is stored in cubes on the desktop. It’s like having your own spreadsheet. Since the data is local, end users don’t have to worry about performance hits against the server.

    50. What Is Rolap?

    Ans:

    ROLAP : Relational OLAP.

    Designates one or several star schemas stored in relational databases. This technology permits multidimensional analysis with data stored in relational databases. Used for large departments or groups because it supports large amounts of data and users.

    Course Curriculum

    Enroll in MSBI Certification Course From Real-Time Experts

    Weekday / Weekend BatchesSee Batch Details

    51. What Is Holap?

    Ans:

    HOLAP: Hybridization of OLAP.

    52. Explain About Auditing In Bo Xi R2? What Is The Use Of It?

    Ans:

    Auditor is used by the business objects administrators to know the complete information of the business intelligence system.

    • it monitors the entire system at a glance.
    • Analyzes usage and change impact.
    • optimises the BI deployment.

    In BOXI, auditing is not a complete package – Desktop Intelligence reports are not completely audited due to the fact these reports are not always held within XI’s repository (well, that and other reasons related to XI’s architecture), however WEBI reports will be fully audited.

    53. Why We Can’t Create An Aggregate On An Ods Object?

    Ans:

    • Operational Data Store has very low data latency. Data moved to ODS mostly on event based rather than time based ETL to Data Warehouse/Data Mart.
    • ODS is closer to the OLTP system. We don’t normally prefer to store aggregated data in OLTP. So it is with ODS.
    • Unlike a data warehouse where data is HISTORICAL, ODS is near real time(NRT). So data aggregation is less important than ODS as data keeps changing.

    54. How Do You Define Multiple Queries In A Document?

    Ans:

    You can create multiple queries by using the Data Provider option available in the reporting tool.

    55. What Is A Universe?

    Ans:

    A “universe” is a “Business object” terminology. Business objects also happen to be the name of the company. The universe is the interfacing layer between the client and the data warehouse . The universe defines the relationship among the various tables in the data warehouse.

    56. What Is Business Intelligence?

    Ans:

    Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. BI applications include the activities of decision support, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining.

    57. How Do We Enhance The Functionality Of The Reports In Bo?

    Ans:

    You can format the BO Reports by using various features available. You can turn the table reports into a 2-Dimensional or 3-Dimensional chart. You can apply an Alert to show some data in a different format, based on some business rule. You can also create some prompts, which will ask the user to give some input values before seeing the reports, this way they will see only filtered data. There are many similar exciting options available to enhance the reports.

    58. What Are Dashboards?

    Ans:

     Dashboard is a user interface that organizes and presents information in a way that is easy to read. It’s a reporting tool that consolidates aggregates and arranges measurement metrics.

    59. Explain The Concepts And Capabilities Of Business Intelligence?

    Ans:

     Business Intelligence is all about processes, skills, technologies, practices and applications used for supporting decision making.

    Business Intelligence applications could perform:

    • Centrally initiated by the business needs.
    • It includes a decision support system, query reporting, OLAP, data mining, forecasting.

    60. Name Some Of The Standard Business Intelligence Tools In The Market?

    Ans:

    The following are the standard Business Intelligence tools in the market:

    • BUSINESS OBJECTS CRYSTAL REPORTS
    • MICRO STRATEGY
    • MS-OLAP SERVICES
    • COGNOS REPORT NET.

    61. Explain The Dashboard In Business Intelligence?

    Ans:

     Dashboard in business intelligence is used for rapid prototyping, cloning and deployment for all databases, operational applications or spread sheets through an organization.

    A dashboard in BI allows an enterprise’s status/position, heading to, by using graphs, maps and charts. The drill-down and roll-over capabilities allow organizing things without revealing important information. It is fully customizable, including free-form design options. Dashboard consolidates vital statistics of business into an easy-to-read page.

    62. Explain The Sql Server 2005 Business Intelligence Components?

    Ans:

    • SQL Server Integration Services:Used for data transformation and creation. Used in data acquisition form a source system.
    • SQL Server Analysis Services: Allows data discovery using data mining. Using business logic it supports data enhancement.
    • SQL Server Reporting Services: Used for Data presentation and distribution access.

    63. Explain The Advantages Of Business Performance Management?

    Ans:

    BPM is very useful in analyzing the current performance of your business. After analyzing the performance of different operations in your organization you can easily identify the various areas where you need to focus in order to increase the profits and Revenue.

    This process will certainly help you in preparing yourself for the future strategies which in turn will help you in competitive advantage and taking corrective effective decisions at the right time.

    64. What Is The Main Use Of Business Content? How Data Can Be Loaded Into Business Content After Installation?

    Ans:

    • Business content can come from various parts of your business operations. Some from the finance department and some from the sales department.
    • If these business content are consistent and can be used by both departments then it’s all about loading the data into a data warehouse for analysis purposes.
    • In case data are inconsistent and can’t be easily compared, then a data integration tool would help to integrate data from various data sources before it is loaded into the data warehouse.

    65. What Do You Mean By Conditional Reports?

    Ans:

    A conditional report is based on the data gathered, then analyzed during the report generation, which reports out results of the analysis in the report’s output.

    66. What Is Flat Aggregation?

    Ans:

    If you create an Aggregate with more than 16 characteristics, then we call that aggregate as FLAT Aggregate. When an aggregate is active then new data is not available for reporting, these aggregates are called Flat Aggregates.

    67.What is writeback? What are its preconditions?

    Ans:

    The writeback dialog box enables or disables writebacks 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.

    68.How are cubes implemented in SSAS?

    Ans:

    Cubes are multidimensional models that store data and aggregations from one or more sources. They are created using the Cube Wizard. Also, dimensions are built while creating cubes.

    69. What is the architecture of SSAS?

    Ans:

    SSAS has a 2-tier architecture. Below are the key features of SSAS architecture:

    • Both server and client components supply OLAP and data mining functionalities to BI applications.
    • Server components are implemented as individual Microsoft Windows Services.
    • Clients communicate with Analysis Services using the standard XMLA (XML for Analysis).

    70. What languages are used in SSAS?

    Ans:

     There are four languages that are used in SSAS. They are:

    • Structured Query Language (SQL)
    • Multidimensional Expressions (MDX) for analysis
    • Data Mining Extensions (DMX) for data mining
    • Analysis Services Scripting Language (ASSL) for managing Analysis Services database objects

    71. How to send SSRS Reports from SSIS?

     In order to do this, users need to create a subscription to the report. This can be done with the Report Manager. The report format and the email address of the recipient can be mentioned at Report Subscription. When a schedule is created for the SSRS report, an SQL Server Agent Job will be created. From SSIS, using sp.start_job, the relevant job name can be passed and, henceforth, the SSRS report subscription can be executed.

    MBSI Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    72. What are the different data sources used in SSRS?

    Ans:

    Several data sources are used in SSRS for ingesting data:

    • OLEDB
    • SQL Server SAP NetWeaver BI
    • Oracle
    • Report Server Model
    • SSAS OLEDB
    • ODBC
    • Hyperion
    • Teradata
    • XML

    73. What are the three different parts of RDL files?

    Ans:

     In Visual Studio, RDL files have three parts:

    • Data that covers the dataset on which queries are written
    • Design that creates tables and matrix reports
    • Preview that is used to check the preview after a report is run

    74. Define RS.exe Utility.

    Ans:

    RS.exe utility is used for organizing reports on Report Server. It comes with the Report Server and can be modified accordingly.

    75. Define Report Model Project.

    Ans:

    Report Model Project is used for ad-hoc reporting. We can create ad-hoc reports with Report Builder. Having a simple view, Report Model Project can be created on BIDS or Report Server.

    76. What do you mean by matrix in SSRS? What are sub-reports and how do we create them?

    Ans:

     A matrix is a data region that is related to a report set. Matrices permit us to create crosstab reports with the report variables showing on rows and columns.

    A sub-report is like any other reports which can be termed in the main report and can be generated through the main report. Parameters can be conceded from the main report to the sub-report, and on the basis of that a report can be created.

    77. When shall we use a null data-driven subscription?

    Ans:

    Users should first create a data-driven subscription that uses the Null Delivery Provider. When the Null Delivery Provider is specified as the method of delivery in the subscription, the Report Server targets the Report Server Database as the delivery destination and uses a specialized rendering extension called the null rendering extension. Different from the other delivery extensions, the Null Delivery Provider does not have delivery settings that can be configured through a subscription.

    78. Explain the Reporting Life Cycle in SSRS.

    Ans:

     SSRS reporting life cycle includes several aspects:

    • Report designing: It is done in Visual Studio Report Designer. It generates a class that embodies the report definition.
    • Report processing: This brings the report definition from the report data source. It performs all grouping, sorting, and filtering calculations.
    • Report rendering: This stage starts by passing the Report Instance to a specific rendering extension that needs to be in HTML or PDF formats. The expressions of items are evaluated in the header and footer sections for every page. Finally, the report is rendered to a specific output document.

    79. What are variables? Define the variable scope.

    Ans:

    Variables are used to store values that SSIS packages and their containers, tasks, and event handlers leverage at the run time. Scripts can also use variables. Additionally, precedence constraints that sequence tasks and containers into a workflow use variables when constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables.

    80. Define query parameters in SSRS.

    Ans:

    Query parameters are revealed in the query of data sources that are to be included in the SQL WHERE clause for accepting parameters. Query parameters begin with the symbol ‘@’.

    81. How can we organize SSIS packages on production?

    Ans:

    There are three ways to do so:

    • Through Manifest:
    • Create a 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 the manifest file to display it on the product.
    • Using the DTExec.exe utility
    • By importing the package directly into Microsoft database from SQL Server Management Studio (SSMS) by logging into Integration Services.

    82. How can we do logging in SSIS?

    Ans:

     SSIS includes logging features that not only write log entries when run-time events occur but also write custom messages. Integration Services supports a diverse set of log providers and empowers users with the ability to create custom log providers. Integration Services log providers can write log entries into text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. The logging configuration provides an in-built feature that can log the details of various events like OnError, OnWarning, etc.

    83. What are different lookup cache modes available in SSIS?

    Ans:

    Different lookup cache modes in SSIS are as follows:

    • Full cache mode
    • Partial cache mode
    • No cache mode

    84. Define environmental variables in SSIS.

    Ans:

    The configuration of environmental variables sets package properties equal to the value in an environment variable. These are used to configure properties that are dependent on computers that execute packages.

    85. What is a workflow? What do you mean by a control flow and a data flow?

    Ans:

     A workflow is basically a set of instructions that is shared with the Program Executor as a guideline to execute tasks and containers.

    A control flow mostly consists of one or more tasks and containers that execute when a package is run. To define conditions for running tasks in the package control flow, precedence constraints are used that connect the tasks and containers in a package.

    There are three control flow elements that SSIS provides:

    • Containers that provide structures in packages
    • Tasks that provide functionality
    • Precedence constraints that connect the executables in an ordered control flow

    A data flow includes sources and destinations that extract and load data, transformations that modify and extend data, and paths that link sources, transformations, and destinations. The data flow task is executable within SSIS packages that create, order, and run the data flow.

    86. Which different versions of any of the tools in MSBI stack have you worked on, any illustrations on the difference in functionalities?

    Ans:

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

    87. What is the difference between Flat files and Raw files?

    Ans:

    Flat Files: A flat file that stores data in a plain text file. Each line of the text file holds one record, with fields separated by delimiters, such as commas or tabs. Flat files are data files that contain records with no structured relationships.

     Raw Files :

    • A raw file is a collection of unprocessed data(Binary format).
    • This means the file has not been altered, compressed, or manipulated in any way by  the computer.
    • Raw files are often used as data files by software programs that load and process  the data.

    88. Explain Fuzzy Lookup Transformation with an example.

    Ans:

    The Fuzzy Lookup transformation differs from the Lookup transformation in its use of fuzzy matching.

    •  The Lookup transformation uses an equi-join to locate matching records in the reference table.
    •  It returns either an exact match or nothing from the reference table.
    • The Fuzzy Lookup transformation uses fuzzy matching to return one or more close matches from the reference table.

    89. Explain Precedence Constraints with an example

    Ans:

    A task will only execute if the condition that is set by the precedence constraint preceding the task is met. By using these constraints, it will choose different execution paths depending on the success or failure of other tasks.

    •  Success: Workflow will proceed when the preceding container executes successfully. Indicated in control flow by a solid green line.
    • Failure : Workflow will proceed when the preceding container’s execution results in a failure. Indicated in control flow by a solid red line.
    • Completion: Workflow will proceed when the preceding container’s execution completes, regardless of success or failure. Indicated in control flow by a solid blue line.
    • Expression/Constraint with logical AND: workflow will proceed when specified expression and constraints evaluate to true. Indicated in control flow by a solid color line along with a small ‘fx’ icon next to it.  Color of line depends on logical constraint chosen (e.g. success = green, completion = blue).

    90. What is Checkpoint?

    Ans:

    Checkpoint is the property in SSIS which enables the project to restart from the point of failure. When we set the property to true, the package creates the checkpoint file which stores the information about package execution and uses it to restart the package from the point of failure. So, If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package runs.

    91. Explain Bulk insert task with an example

    Ans:

    The Bulk Insert task can transfer data only from a text file into a SQL Server table or view.

     If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. we cannot perform any ETL operations during the data transfer. Destination table should already be present in sql server.

    92. Explain Derived Column Transformation  with an example

    Ans:

    The Derived Column transformation creates new column values by applying expressions to transformation input columns:

    • An expression can contain any combination of variables, functions,operators, and  columns from the transformation input.
    • The result can be added as a new column or inserted into an existing column as a replacement value.
    • The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions

    93. Explain Import Columns and Export Columns Transformation

    Ans:

    Import Columns:

    • The Import Column transformation is used to load binary data (photos, documents, media etc.) from the file system into a Data Flow.
    • From the Data Flow it can then be loaded into a table, moved to a different location, modified – whatever you need to do.
    • It is the reverse of the Export Column transformation, which pushes binary data out of a Data Flow and into the file system.

     Export columns:

    • The Export Column transformation reads data in a data flow and inserts the data into a file.
    • For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.
    • Specify the data columns and the columns that contain the path of files to which to write the data.
    • Specify whether the data-insertion operation appends or truncates existing files.

    94. Differentiate between Union all and Merge Join?

    Ans:

    • Merge transformation can take only two inputs whereas Union all can take more than two inputs.
    • Data has to be sorted before Merge Transformation whereas Union all doesn’t have any foundation like that.

    95. Which programming language can be used to code embedded functions in SSRS?

    Ans:

    Visual Basic .NET Code is used to code embedded functions in SSRS.

    96. Differentiate between SSRS 2005 and SSRS 2008?

    Ans:

    The major differences between SSRS 2005 and SSRS 2008 are as follows:

    SSRS 2005 report server requires IIS, whereas SSRS 2008 comes with a built-in web server and hence does not require IIS.

    97. What are the three stages of the Enterprise Reporting Life Cycle?

    Ans:

    • Management
    • Authoring
    • Access and Delivery

    98. In which SQL Server type report builder introduced?

    Ans:

    Report builder presented in SQL Server 2005. While creating or arranging a report model project on a report server we can get an error or it might not get formed. For this, we need to check whether the service pack 22 is installed or not.

    99. What do you understand by the chart in the report?

    Ans:

    Chart reports are for graphical representation. We can get pie charts, column charts and various other options. 3d charts are also presented in reporting services.

    100. What is the advantage of using MSMQ?

    Ans:

    MSMQ helps in communicating two applications with each other asynchronously. Specialty is two applications may be built using dissimilar technology and it works even offline messaging. The sender will stock messages inside the queue and the reader reads it wherever required.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free