Interview Questions.

Q1. What Is Holap?

HOLAP: Hybridization of OLAP.

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

Use should usefunction Set.Item(zero)

Example:

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

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

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

BUSINESS OBJECTS CRYSTAL REPORTS

MICRO STRATEGY

MS-OLAP SERVICES

COGNOS REPORT NET.

Q4. What Is The Need For Ssas Component?

Analysis Services is the best thing in SQL Server using which we will carry out Analysis and Forecast operations.

SSAS is very smooth to apply and interactive.

Faster Analysis and Troubleshooting.

Ability to create and manipulate Data warehouses.

Apply efficient Security Principles.

Q5. What Is Writeback? What Are The Pre-situations?

The Enable/Disable Writeback conversation box allows or disables writeback for a degree institution in a dice. Enabling writeback on a measure institution defines a writeback partition and creates a writeback desk for that degree institution. Disabling writeback on a degree organization removes the writeback partition but does no longer delete the writeback table, to avoid unanticipated statistics loss.

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

The distinction between a derived measure and a calculated degree is while the calculation is carried out. A derived degree is calculated earlier than aggregations are created, and the values of the derived degree are stored within the dice. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t saved in the cube. The number one criterion for choosing among a derived measure and a calculated measure isn't always performance, however accuracy.

Q7. What Is A Workflow In Ssis ?

Workflow is a hard and fast of commands on to specify the Program Executor on how to execute duties and packing containers within SSIS Packages.

Q8. What Is A Data Flow?

A facts float includes the resources and locations that extract and load records, the trformations that alter and expand facts, and the trails that link sources, trformations, and locations. The Data Flow mission is the executable in the SSIS package deal that creates, orders, and runs the information go with the flow. A separate example of the statistics drift engine is opened for each Data Flow challenge in a bundle. Data Sources, Trformations, and Data Destinations are the 3 important categories in the Data Flow.

Q9. Explain The Concepts And Capabilities Of Business Intelligence?

Business Intelligence is all approximately procedures, abilities, technology, practices and packages used for supporting selection making.

Business Intelligence applications could carry out.

Centrally initiated by way of the commercial enterprise desires.

It includes choice help gadget, question reporting, OLAP, records mining, forecasting.

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

Simply the use of bottomcount will return customers with null sales. You will should 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] ) ;  

Q11. 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 documents in use

Buffer reminiscence

Buffers in use

Buffers spooled

Flat buffer reminiscence

Flat buffers in use

Private buffer reminiscence

Private buffers in use

Rows examine

Rows written.

Q12. Name Few Business Analysis Enhancements For Ssas?

The following desk lists the business intelligence upgrades which can be to be had in Microsoft SQL Server 2005 Analysis Services (SSAS). The table also shows the dice or size to which each enterprise intelligence enhancement applies, and suggests whether or not an enhancement can be applied to an object that changed into created with out the usage of a records source and for which no schema has been generated.

Q13. How To Log Ssis Executions?

SSIS consists of logging features that write log entries while run-time events arise and can also write custom messages. This isn't always enabled through default. Integration Services supports a various set of log vendors, and gives you the ability to create custom log carriers. The Integration Services log carriers can write log entries to textual content documents, SQL Server Profiler, SQL Server, Windows Event Log, or XML documents. Logs are associated with applications and are configured at the bundle degree. Each venture or box in a package can log facts to any package deal log. The responsibilities and containers in a package deal can be enabled for logging even if the bundle itself isn't.

Q14. What Is A Universe?

A "universe" is a "Business object" terminology. Business objects additionally happens to be the call of the business enterprise. The universe is the interfacing layer among the purchaser and the information warehouse . The universe defines the connection many of the various tables within the data warehouse.

Q15. What Is Business Intelligence?

Business intelligence (BI) is a large class of utility applications and technology for gathering, storing, analyzing, and offering get admission to to statistics to assist corporation users make better business selections. BI applications encompass the sports of choice aid, question and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and facts mining.

Q16. Explain The Sql Server 2005 Business Intelligence Components?

SQL Server Integration Services:- Used for information trformation and advent. Used in records acquisition shape a source gadget.

SQL Server Analysis Services: Allows information discovery the usage of statistics mining. Using commercial enterprise common sense it supports information enhancement.

SQL Server Reporting Services:- Used for Data presentation and distribution get right of entry to.

Q17. What Are The Trformations Available In Ssis?

AGGEGATE - It applies combination capabilities to Record Sets to supply new output facts from aggregated values.

AUDIT - Adds Package and Task degree Metadata - consisting of Machine Name, Execution Instance, Package Name, Package ID, and so on.

CHARACTER MAP - Performs SQL Server degree makes string statistics modifications inclusive of changing facts from lower case to higher case.

CONDITIONAL SPLIT – Separates available input into separate output pipelines based on Boolean Expressions configured for every output.

COPY COLUMN - Add a copy of column to the output we are able to later trform the copy maintaining the original for auditing.

DATA CONVERSION - Converts columns statistics kinds from one to some other type. It stands for Explicit Column Conversion.

DATA MINING QUERY – Used to perform records mining question towards evaluation offerings and manage Predictions Graphs and Controls.

DERIVED COLUMN - Create a brand new (computed) column from given expressions.

EXPORT COLUMN – Used to export a Image particular column from the database to a flat record.

FUZZY GROUPING – Used for records cleing by using finding rows which are probably duplicates.

FUZZY LOOKUP - Used for Pattern Matching and Ranking primarily based on fuzzy logic.

IMPORT COLUMN - Reads image precise column from database onto a flat file.

LOOKUP - Performs the research (searching) of a given reference object set towards a facts supply. It is used for exact matches most effective.

MERGE - Merges two taken care of records sets into a single facts set into a single facts flow.

MERGE JOIN - Merges  facts sets into a single dataset using a join junction.

MULTI CAST - Sends a copy of provided Data Source onto a couple of Destinations.

ROW COUNT - Stores the ensuing row count number from the records glide / trformation right into a variable.

ROW SAMPLING - Captures pattern records by using using a row count of the overall rows in dataflow detailed by using rows or percentage.

UNION ALL - Merge a couple of facts sets right into a unmarried dataset.

PIVOT – Used for Normalization of information resources to reduce analomolies by using changing rows into columns

UNPIVOT – Used for demoralizing the data shape by converts columns into rows incase of constructing Data Warehouses.

Q18. What Are The Tools In Ms Bi?

It includes the subsequent equipment:

SQL Server Analysis Services (SSAS)

SQL Server Integration Services (SSIS)

SQL Server Reporting Services (SSRS)

Performance Point Services (PPS) which turned into brought as a unfastened service in Microsoft Office SharePoint 20@

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

Microsoft SQL Server 2005 Analysis Services (SSAS) supplies on line analytical processing (OLAP) and statistics mining functionality for commercial enterprise intelligence programs. Analysis Services helps OLAP through letting we design, create, and manipulate multidimensional systems that include data aggregated from other records sources, including relational databases. For data mining programs, Analysis Services shall we we layout, create, and visualize data mining fashions which might be created from other data sources by using using a huge form of industry-trendy facts mining algorithms.

Analysis Services is a center tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of records and provides get right of entry to to hundreds of facts including aggregation of information. One can create data mining models from facts resources and use it for Business Intelligence also consisting of reporting features.

Analysis service presents a combined view of the information used in OLAP or Data mining. Services here seek advice from OLAP, Data mining. Analysis services assists in developing, designing and managing multidimensional systems containing records from various sources. It offers a big range of information mining algorithms for unique trends and needs.

Some of the key capabilities are:

Ease of use with a variety of wizards and designers.

Flexible records model advent and control

Scalable architecture to deal with OLAP

Provides integration of management gear, facts assets, protection, caching, and reporting and so forth.

Provides full-size support for custom programs.

Q20. What Are Dashboards?

Dashboard is a user interface that organizes and affords statistics in a manner that is easy to read. It’s a reporting device that consolidates aggregates and arranges measurements metrics.

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

Auditor is utilized by the enterprise objects directors to recognise the whole facts of the commercial enterprise intelligence machine.

It monitors complete BIsystem at a glance.

Analyzes utilization and alternate impact.

Optimises the BI deployment.

In BOXI, auditing isn't always a entire package deal - Desktop Intelligence reviews aren't absolutely audited due to the truth these reviews are not constantly held within XI's repository (well, that and other reasons related to XI's structure), however WEBI reports may be completely audited.

Q22. What Is Fasmi ?

A database is called a OLAP Database if the database satisfies the FASMI guidelines :

Fast Analysis– is defined within the OLAP state of affairs in five seconds or much less.

Shared – Must aid get entry to to records through many customers in the elements of Sensitivity and Write Backs.

Multidimensional – The statistics inside the OLAP Database need to be multidimensional in shape.

Information – The OLAP database Must support massive volumes of information.

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

Microsoft Business Intelligence (BI) is a set of products and tools that you may use to monitor, examine and plan your commercial enterprise through using scorecards, dashboards, management reporting and analytics.

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

OLAP: It’s a web analytical processing device. There are various products to be had for facts analysis.

ETL: Extract, Trform and Load. This is a product to extract the information from multiple/single supply trform the facts and load it right into a desk, flat file or absolutely a goal.

Q25. What Are Variables And What Is Variable Scope ?

Variables store values that a SSIS package and its bins, responsibilities, and event handlers can use at run time. The scripts within the Script mission and the Script element also can use variables. The precedence constraints that collection obligations and boxes right into a workflow can use variables when their constraint definitions consist of expressions. Integration Services supports  sorts of variables: person-described variables and machine variables. User-described variables are defined through package developers, and device variables are described by way of Integration Services. You can create as many person-described variables as a bundle calls for, but you can not create extra gadget variables.

Q26. What Is A Master Package?

Master package is not anything but it is also a everyday SSIS bundle, it act like a grasp to run the all different (infant) packages. If you call the alternative SSIS applications in a new SSIS bundle the use of ‘Execute Package Task’, the brand new package deal referred to as as master package deal. The use of this package deal(Master) is, in preference to jogging all character packages if we will run the unmarried grasp bundle, then routinely all the toddler programs will run. Normally we clearly agenda the master package deal in Sql server agent by adding the all child applications values in the grasp package config report.

Q27. What Are The Different Ways Of Creating Aggregations?

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

Q28. What Are The Types Of Parameters In Ssrs?

The sorts of parameters in SSRS are:

Single cost parameters

Multi fee parameters

Cascading parameters.

Q29. What Are The Differences Between Dts And Ssis?
 

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

It’s an Business Intelligence equipment. Like Brio which became an independent product offered over my Hyperion has converted this product name to Hyperion Intelligence.

Yes. You can examine records schemas the usage of this tools.

Q31. What Is A Partition?

A partition in Analysis Services is the bodily place of stored cube information. Every dice has at the least one partition by way of default. Each time we create a measure institution, any other partition is created. Queries run faster in opposition to a partitioned dice due to the fact Analysis Services most effective desires to read statistics from the walls that comprise the wers to the queries. Queries run even faster whilst partition also shops aggregations, the pre calculated totals for additive measures. Partitions are a powerful and bendy me of coping with cubes, especially large cubes.

Q32. Why We Can't Create An Aggregate On An Ods Object?

Operational Data Store has very low records latency. Data moved to ODS totally on occasion primarily based in preference to time based ETL to Data Warehouse/Data Mart.

ODS is extra toward OLTP gadget. We don't usually opt to store aggregated records in OLTP. So it's far with ODS.

Unlike facts warehouse in which statistics is HISTORICAL, ODS is close to actual time(NRT). So statistics aggregation is much less critical is ODS as statistics continues converting.

Q33. How Does Error-managing Work In Ssis?

When a statistics float aspect applies a trformation to column data, extracts facts from sources, or loads facts into destinations, mistakes can arise. Errors often arise due to surprising information values.

Type of common Errors in SSIS:

Data Connection Errors, which arise in case the connection manager cannot be initialized with the connection string. This applies to each Data Sources and Data Destinations along side Control Flows that use the Connection Strings.

Data Trformation Errors, which arise while facts is being trformed over a Data Pipeline from Source to Destination.

Expression Evaluation errors, which occur if expressions which can be evaluated at run time perform invalid.

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

By default Analysis Services returns individuals in an order specific in the course of characteristic layout. Attribute homes that define ordering are "OrderBy" and "OrderByAttribute". Lets say we need to see order counts for each yr. In Adventure Works MDX query could 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 zero,  TopCount([Date].[Calendar].[Calendar Year].Members,three,   [Measures].[Reseller Order Quantity]) ON 1  FROM [Adventure Works];  

Q35. What Is Dolap?

DOLAP - Desktop OLAP.

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

Designed for low-stop, unmarried, departmental consumer. Data is saved in cubes at the laptop. It's like having your personal spreadsheet. Since the information is neighborhood, cease users don't need to worry approximately overall performance hits towards the server.

Q36. What Is Environment Variable In Ssis?

An environment variable configuration units a package deal property same to the value in an environment variable.

Environmental configurations are beneficial for configuring residences which can be dependent on the pc that is executing the bundle.

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

SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to carry out a wide variety of Data Migration and ETL operations. SSIS is part in MSBI procedure of SQL Server.

This is a platform for Integration and Workflow programs. It is known for a fast and flexible OLTP and OLAP extensions used for records extraction, trformation, and loading (ETL). The tool will also be used to automate protection of SQL Server databases and multidimensional facts units.

Q38. How Cubes Are Implemented In Ssas ?

Cubes are multidimensional models that shop statistics from one or extra sources.

Cubes also can save aggregations.

SSAS Cubes are created using the Cube Wizard.

We additionally build Dimensions while growing Cubes.

Cubes can see handiest the DSV( logical View).

Q39. Explain The Advantages Of Business Performance Management?

BPM may be very beneficial in studying the present day performance of your enterprise. After studying the overall performance of different operations for your organization you can easily perceive the diverse areas in which you want to consciousness on in an effort to boom the earnings and Revenue.

This technique will virtually assist you in preparing yourself for the destiny strategies which in flip will assist you in competitive advantage and taking corrective powerful decisions on the right time.

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

You can create multiple queries with the aid of the use of Data Provider choice to be had inside the reporting tool.

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

Business content material can come from numerous components of your commercial enterprise operations. Some from the finance branch and some from the sales department.

If these commercial enterprise content are consistent and may be used by both departments then it is all approximately loading the statistics right into a statistics warehouse for evaluation functions.

In case data are inconsistent and cannot be easily as compared, then a statistics integration tool would assist to integrate information from diverse statistics resources earlier than it's miles loaded into the information warehouse.

Q42. What Are The Tools Associated With Ssis?

We use Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) to paintings with Development of SSIS Projects.

We use SSMS to manage the SSIS Packages and Projects.

Q43. What Is Udm? Its Significance In Ssas?

The function of a Unified Dimensional Model (UDM) is to provide a bridge between the person and the facts resources. A UDM is constructed over one or more physical data sources, after which the stop user problems queries in opposition to the UDM the use of one among a ramification of customer equipment, together with Microsoft Excel. At a minimum, while the UDM is built simply as a thin layer over the data source, the advantages to the quit user are a easier, more with no trouble understood version of the facts, isolation from heterogeneous backend statistics resources, and advanced performance for precis kind queries. In a few scenarios a easy UDM like this is constructed completely automatically. With more investment inside the construction of the UDM, additional blessings accrue from the richness of metadata that the version can offer.

The UDM gives the following benefits:

Allows the user model to be significantly enriched.

Provides high overall performance queries helping interactive evaluation, even over huge data volumes.

Allows enterprise guidelines to be captured inside the version to aid richer analysis.

Q44. What Is Flat Aggregation?

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

Q45. Explain The Two-tier Architecture Of Ssas?

SSAS uses each server and purchaser components to deliver OLAP and statistics mining functionality BI Applications.

The server element is carried out as a Microsoft Windows provider. Each instance of Analysis Services carried out as a separate instance of the Windows carrier.

Clients speak with Analysis Services using the standard the XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as an internet service.

Q46. What Is Molap?

MOLAP - Multidimensional OLAP.

This time period designates a Cartesian facts structure greater especially. In impact, MOLAP contrasts with ROLAP. In the previous, joins between tables are already suitable, which enhances performances. In the latter, joins are computed for the duration of the request.

Targeted at corporations of customers as it's a shared environment. Data is saved in an extraordinary server-primarily based layout. It performs greater complicated analysis of facts.

Q47. Explain The Dashboard In The Business Intelligence?

Dashboard in enterprise intelligence is used for rapid prototyping, cloning and deployment for all databases, operational applications or unfold sheets thru an agency.

A dashboard in BI allows an corporation’s fame/position, heading to, by way of using graphs, maps and chars. The drill-down and roll-over abilties permits organizing matters without revealing essential facts. It is completely customizable, consisting of unfastened-form design options. Dashboard consolidates important information of enterprise into an clean-to-study page.

Q48. What Do You Mean By Conditional Reports?

A conditional report is primarily based on the statistics accumulated, then analyzed in the course of the file generation, which reviews out outcomes of the analysis within the report's output.

Q49. What Is Processing?

Processing is a critical and useful resource in depth operation within the information warehouse lifecycle and desires to be carefully optimized and completed. Analysis Services 2005 gives a high performance and scalable processing structure with a complete set of controls for database administrators.

We can process an OLAP database, person dice, Dimension or a specific Partition in a cube.

Q50. Difference B/w View And Materialized View?

Views

A view takes the output of a query and makes it appear like a digital table. You can use a view in maximum places where a table can be used.

All operations done on a view will affect information inside the base table and so are problem to the integrity constraints and triggers of the bottom desk.

A View can be used to simplify SQL statements for the person or to isolate an software from any destiny trade to the base desk definition. A View can also be used to enhance security through proscribing access to a predetermined set of rows or columns.

In addition to operating on base tables, one View can be based totally on any other, a view also can JOIN a view with a desk (GROUP BY or UNION).

Materialized Views

Materialized perspectives are schema gadgets that can be used to summarize, precompute, mirror, and distribute statistics. E.G. To construct a records warehouse.

A materialized view affords oblique get admission to to desk information by way of storing the results of a query in a separate schema item. Unlike an everyday view, which does no longer soak up any storage space or incorporate any data.

The life of a materialized view is trparent to SQL, but while used for question rewrites will improve the overall performance of SQL execution. An updatable materialized view helps you to insert, update, and delete.