Top 50 Msbi Interview Questions You Must Prepare 19.Mar.2024

HOLAP: Hybridization of OLAP.

Use could usefunction Set.Item(0)
Example:

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

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

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

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

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.

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.

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

A data flow consists of the sources and destinations that extract and load data, the trformations that modify and extend data, and the paths that link sources, trformations, 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, Trformations, and Data Destinations are the three important categories in the Data Flow.

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 decision support system, query reporting, OLAP, data mining, forecasting.

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] ) ;  

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

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.

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.

A "universe" is a "Business object" terminology. Business objects also happens 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.

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.

  • SQL Server Integration Services:- Used for data trformation 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.

  • AGGEGATE - 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 lower case to upper case.
  • 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 trform 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.
  • DERIVED COLUMN - Create a new (computed) column from given expressions.
  • EXPORT COLUMN – Used to export a Image specific column from the database to a flat file.
  • FUZZY GROUPING – Used for data cleing by finding rows that are likely duplicates.
  • FUZZY LOOKUP - Used for Pattern Matching and Ranking based on fuzzy logic.
  • IMPORT COLUMN - 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.
  • MERGE JOIN - Merges two data sets into a single dataset using a join junction.
  • MULTI CAST - Sends a copy of supplied Data Source onto multiple Destinations.
  • ROW COUNT - Stores the resulting row count from the data flow / trformation into a variable.
  • ROW SAMPLING - Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
  • UNION ALL - 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 demoralizing the data structure by converts columns into rows incase of building Data Warehouses.

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 20@

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

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 measurements metrics.

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

  • it monitors entire BIsystem 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.

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.

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.

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

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.

Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called as 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.

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

The types of parameters in SSRS are:

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

  • It’s an 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 this tools.

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 wers 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 me of managing cubes, especially large cubes.

  1. 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.
  2. ODS is more closer to OLTP system. We don't normally prefer to store aggregated data in OLTP. So it is with ODS.
  3. Unlike data warehouse where data is HISTORICAL, ODS is near real time(NRT). So data aggregation is less important is ODS as data keeps changing.

When a data flow component applies a trformation 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 Trformation Errors, which occur while data is being trformed over a Data Pipeline from Source to Destination.
  • Expression Evaluation errors, which occur if expressions that are evaluated at run time perform invalid.

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works 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];  

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

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

  • 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, trformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.

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

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

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

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

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

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.

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.

  • 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 the XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as a web service.

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.

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 chars. The drill-down and roll-over capabilities allows 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.

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.

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.

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