Business Intelligence Consultant Interview Questions and Answers

Query to display duplicate records without using group by

• Use self-join to compare records

• Select records where the values match but the primary key is different

• Example: SELECT a.* FROM table a, table b WHERE a.value = b.value AND a.id <> b.id

stuff() replaces a specified number of characters with another string, while substring() extracts a portion of a string.

• stuff() is used to replace a specified number of characters in a string with another string.

• substring() is used to extract a portion of a string based on the starting and ending index.

• stuff() can be used to mask sensitive data in a string, such as credit card numbers.

• substring() can be used to extract a specific part of a string, such as the first name in a ...read more

The connection string can be passed on runtime using various methods.

• One way is to use a configuration file and read the connection string from it.

• Another way is to pass the connection string as a parameter to the application.

• The connection string can also be stored in a database and retrieved at runtime.

• Using environment variables is also a common method to pass connection strings.

• Connection strings can be encrypted and stored in a secure location, and then retrieved at runt...read more

The 'FOR XML' command is used in SQL Server to generate XML output from a query result.

• It allows transforming the result set of a query into XML format.

• It can be used to generate XML documents for data exchange or integration purposes.

• The 'FOR XML' command supports different modes like RAW, AUTO, EXPLICIT, and PATH.

• It can be combined with other SQL Server features like joins, aggregations, and subqueries.

• Example: SELECT * FROM Customers FOR XML AUTO, ELEMENTS

Data can be extracted from various sources like databases, APIs, flat files, etc. and transformed using ETL tools before loading into BI.

• Identify data sources and their accessibility

• Choose appropriate ETL tool for data extraction and transformation

• Design data model and schema for BI

• Load data into BI using ETL tool

• Validate and verify data accuracy and completeness

Transaction property in SSIS ensures all tasks in a package are completed or rolled back as a single unit.

• Transaction property is used to ensure data consistency in a package

• If a task fails, all previous tasks are rolled back

• If all tasks complete successfully, they are committed as a single unit

• Transaction property can be set at package or container level

• Supported isolation levels are Serializable, Repeatable Read, Read Committed, and Read Uncommitted