BI Analyst Interview Questions and Answers for Freshers

To show aggregation on whole data row by row in SQL, you can use the OVER() function with appropriate window specification.

• Use the OVER() function to perform aggregation on the entire data set

• Specify the window specification to define the scope of aggregation

• Add the aggregated result as a new column in the existing table

• Example: SELECT *, SUM(sales) OVER() AS total_sales FROM table_name

ALL function in DAX returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

• ALL function can be used to remove filters from a table or column.

• It can also be used to remove filters from a specific column in a table.

• ALL function can be combined with other functions like CALCULATE to perform complex calculations.

• Example: CALCULATE(SUM(Sales[Amount]), ALL(Sales[Product])) will return the total sales amount for all products...read more

Functions return a single value while stored procedures execute a set of SQL statements.

• Functions are used to perform calculations and return a single value while stored procedures are used to execute a set of SQL statements.

• Functions can be used in SELECT, WHERE, and HAVING clauses while stored procedures cannot.

• Functions can be called from within stored procedures while stored procedures cannot be called from within functions.

• Functions cannot modify the database while store...read more