UPDATE NEEDED: We have received vote saying this page is of low value and we agree that we can considerably improve it and we will do that very soon. Please bear with us.
Business Intelligence interview questions are more and more often searched due to very high demand and relatively low amount of experts (comparing to demand). At least that seems to be the trend in recent years when it comes to SQL Server Business Intelligence. Microsoft become serious BI player after release of SQL Server 2005 with proper BI tools.
In this post I will cover general business intelligence concepts so you can get a good understanding of the candidate grasp of the ideas before assesssing his technical skills. For technical questions related to BI tools (SSIS, SSAS, SSRS) Please visit our intervie questions and answers main page.
Question: Describe main areas (or tools) that represent Business Intelligence.
Answer: This is open question and answers will vary but the candidate might say ETL (SSIS), Data Warehouse and Cubes (SSAS) and Reporting (SSRS). This list can be longer and can include data mining, master data management, data profiling, data cleansing, analysis and data gathering also can be added here.
Question: What is the purpose of ETL?
Answer: ETL (Extract Transform Load) is used to extract data from various sources. Transform the data which means applying business rules and performing data cleaning (the latter being often overlooked that causes issues) and Load the clean data into data warehouse. Sometimes in ETL there is also master data management element so it can extract data, perform transformation and it may use proper tools to perform data cleansing and check business rules. In recent versions of SQL Server (including 2012) we can have Master Data Services (MDS) and Data Qaulity Services (DQS).
Question: What is the purpose of cubes?
Answer: Cubes allow to store larges amounts (usually track history) of data and get very good performance. Security is very detailed and it can be applied on "member level" which is an individual value in a column. Cubes usually track history so user get access to "point in time" data and accurate data. My favourite example is top 1 customer that moves its location from London to Bristol with cubes (or more data warehouses) historic data will not move to Bristol and it will remain in London which is very often not the case in providing reporting information from operational systems.
Question: What is the purpose of reporting?
Answer: That is more self-explanatory than previous questions. Ability to view or create report by users is the short answer but usually reporting tools like SSRS are much more powerful and they allow to set up subscriptions, provider reports in different usable for users format, secure reports and so on.
I hope to add more questions soon. Feel free suggest new BI Interview questions