Download PrepIQ NWCA Business Intelligence Systems Ultimate Exam and more Exams Technology in PDF only on Docsity!
Intelligence Systems Ultimate
Exam
Question 1. Which of the following best describes the primary purpose of an Operational Database (OLTP) in a business environment? A) To store historical data for trend analysis B) To support day-to-day transaction processing C) To provide multidimensional reporting capabilities D) To host external public data feeds Answer: B Explanation: OLTP systems are optimized for fast insert, update, and delete operations that support routine business transactions such as order entry and inventory updates. Question 2. In the context of Business Process Management (BPM), which activity directly benefits from the use of a workflow modeling tool? A) Data mining for customer segmentation B) Designing a visual representation of a process flow C) Encrypting data at rest in a data warehouse D) Allocating budget for IT staff training Answer: B Explanation: Workflow modeling tools enable analysts to diagram each step in a process, making it easier to identify bottlenecks and improve efficiency. Question 3. Which stakeholder is most likely to request a dashboard that shows real-time sales revenue versus forecasted revenue? A) Chief Financial Officer (CFO) B) IT Network Administrator C) Human Resources Manager D) Procurement Officer Answer: A Explanation: The CFO focuses on financial performance and would need up-to-date revenue figures to compare against forecasts for decision-making.
Intelligence Systems Ultimate
Exam
Question 4. What is the key difference between a Data Warehouse and a Data Mart? A) Data Warehouses store only unstructured data, while Data Marts store structured data. B) Data Warehouses serve the entire enterprise; Data Marts serve a specific department or line of business. C) Data Warehouses are always hosted in the cloud; Data Marts are on-premises. D) Data Warehouses are used for OLTP, Data Marts for OLAP. Answer: B Explanation: A Data Warehouse consolidates data across the whole organization, whereas a Data Mart contains a subset tailored to a particular functional area. Question 5. Which of the following best defines metadata in a data warehouse environment? A) The raw transactional records from source systems B) The process of extracting, transforming, and loading data C) Information that describes the structure, origin, and usage of data elements D) The visual representation of data in a dashboard Answer: C Explanation: Metadata is “data about data,” providing context such as data types, lineage, and business definitions. Question 6. During the ETL process, the “Transformation” stage primarily addresses which of the following tasks? A) Pulling data from source systems B) Loading data into the target warehouse C) Cleansing, normalizing, and applying business rules to the extracted data D) Scheduling batch jobs for nightly runs Answer: C
Intelligence Systems Ultimate
Exam
D) RELATED()
Answer: B Explanation: TOTALYTD() computes a year-to-date total, accumulating values across a time dimension. Question 10. When designing a visualization, which chart type is most appropriate for comparing the proportion of market share among five competitors? A) Line chart B) Scatter plot C) Stacked bar chart D) Pie chart Answer: D Explanation: Pie charts are ideal for showing parts of a whole, making them suitable for displaying market-share percentages. Question 11. Conditional formatting in a BI report is primarily used to: A) Encrypt the underlying data source. B) Change visual attributes based on data values to highlight trends or outliers. C) Reduce the size of the data model. D) Convert numeric data to text strings. Answer: B Explanation: Conditional formatting dynamically alters colors, icons, or fonts to draw attention to specific data conditions. Question 12. Which KPI would be most relevant for measuring the efficiency of a call-center’s handling of customer inquiries? A) Gross profit margin B) Average handle time (AHT) C) Inventory turnover
Intelligence Systems Ultimate
Exam
D) Employee satisfaction score Answer: B Explanation: AHT directly reflects how quickly agents resolve calls, a core efficiency metric for call centers. Question 13. The Balanced Scorecard includes which of the following perspectives? A) Financial, Customer, Internal Processes, Learning & Growth B) Revenue, Cost, Asset, Liability C) Strategic, Tactical, Operational, Compliance D) Marketing, Sales, Production, R&D Answer: A Explanation: The Balanced Scorecard framework balances financial outcomes with customer, internal, and learning/growth measures. Question 14. In the project lifecycle of a BI implementation, which phase follows “Design” and precedes “Deployment”? A) Planning B) Development C) Maintenance D) Evaluation Answer: B Explanation: After design, the development phase builds the data models, ETL pipelines, and reports before they are deployed. Question 15. Which role is primarily responsible for translating business requirements into technical specifications for a BI system? A) Network Administrator B) Systems Analyst
Intelligence Systems Ultimate
Exam
C) HIPAA
D) SOX
Answer: C Explanation: HIPAA sets standards for safeguarding Protected Health Information (PHI). Question 19. Social media listening tools are most valuable for which type of BI analysis? A. Real-time inventory reconciliation B. Competitive market sentiment and brand perception tracking C. Payroll processing accuracy D. Server uptime monitoring Answer: B Explanation: Listening tools aggregate public posts, reviews, and mentions to gauge market sentiment and competitor activity. Question 20. Which AI capability is commonly integrated into BI platforms to automatically detect anomalous sales patterns? A) Natural Language Generation (NLG) B) Anomaly detection algorithms using machine learning C) Robotic Process Automation (RPA) for data entry D) Optical Character Recognition (OCR) for scanning invoices Answer: B Explanation: Machine-learning-based anomaly detection flags data points that deviate significantly from expected patterns. Question 21. Predictive analytics differs from descriptive analytics primarily in that it: A) Summarizes historical data without forecasting.
Intelligence Systems Ultimate
Exam
B) Uses statistical models to forecast future outcomes. C) Only visualizes data in charts and tables. D) Requires no data cleansing. Answer: B Explanation: Predictive analytics applies models to historical data to estimate future events, whereas descriptive analytics merely reports what has happened. Question 22. Which of the following is a key advantage of hosting a BI solution in the cloud (e.g., Azure) over an on-premises deployment? A) Unlimited on-site hardware control B) Automatic scalability and reduced upfront capital expense C) Mandatory use of proprietary file formats D) Elimination of all security concerns Answer: B Explanation: Cloud platforms provide elastic resources and a pay-as-you-go model, lowering capital costs and enabling rapid scaling. Question 23. In a Snowflake Schema, why are dimension tables normalized? A) To increase query performance by reducing joins. B) To minimize data redundancy and improve maintainability. C) To store measures directly within dimensions. D) To eliminate the need for a fact table. Answer: B Explanation: Normalizing dimensions reduces duplication, making the model easier to maintain, though it may add join complexity. Question 24. Which DAX function returns the distinct count of a column’s values? A) COUNTROWS()
Intelligence Systems Ultimate
Exam
Question 27. Which ETL loading strategy is most suitable for a data warehouse that must retain historical snapshots of data changes? A) Truncate-and-load B) Incremental load with Slowly Changing Dimension Type 2 (SCD2) C) Full refresh each night D) Direct query without loading Answer: B Explanation: SCD2 captures and stores each version of a record, preserving historical changes. Question 28. In the context of BI governance, what is the primary purpose of a data steward? A) To write application code for dashboards. B) To oversee data quality, definitions, and compliance across the organization. C) To manage network firewall rules. D) To approve purchase orders for hardware. Answer: B Explanation: Data stewards ensure that data assets are accurate, well-documented, and meet regulatory standards. Question 29. Which visualization is most effective for showing a correlation between two continuous variables, such as advertising spend and sales revenue? A) Bar chart B) Scatter plot C) Waterfall chart D) Tree map Answer: B
Intelligence Systems Ultimate
Exam
Explanation: Scatter plots display individual data points along two axes, revealing correlation patterns. Question 30. Which of the following describes “data lineage”? A) The process of encrypting data at rest. B) The tracking of data’s origin, movement, and transformations throughout its lifecycle. C) The visual layout of dashboards. D) The method of compressing large datasets. Answer: B Explanation: Data lineage documents where data comes from, how it is transformed, and where it is stored, supporting traceability and compliance. Question 31. A BI analyst needs to compare monthly revenue across fiscal years that start in July. Which date hierarchy feature should they use? A) Calendar Year hierarchy B) Fiscal Year hierarchy with a July start month C) Week-of-Year hierarchy D) Day-of-Month hierarchy Answer: B Explanation: A fiscal year hierarchy aligns periods to the organization’s fiscal calendar, correctly grouping July-June intervals. Question 32. Which of the following is a primary benefit of implementing a data catalog in an enterprise BI environment? A) Automatic generation of all dashboards B) Centralized discovery of data assets, including metadata and usage statistics C) Replacement of all ETL processes D) Elimination of the need for data governance policies
Intelligence Systems Ultimate
Exam
Answer: B Explanation: “Publish to Web” creates a shareable link that anyone can view without authentication, suitable for public consumption. Question 36. Which of the following is a characteristic of a “thin client” architecture for BI delivery? A) All processing occurs on the user’s machine. B) The server performs most computation, and the client only renders the UI. C) Requires high-end graphics hardware on the client side. D) Stores the entire data model locally on each client. Answer: B Explanation: Thin clients offload processing to the server, reducing client hardware requirements. Question 37. Which KPI would be most appropriate for measuring the success of a new product launch campaign? A) Days Sales Outstanding (DSO) B) Customer Acquisition Cost (CAC) for the product’s target segment C) Average inventory holding period D) Employee turnover rate Answer: B Explanation: CAC directly reflects the cost to acquire customers for the newly launched product, indicating campaign effectiveness. Question 38. In the context of BI tool selection, what does “self-service analytics” refer to? A) Only IT staff can create reports. B) End-users can independently build, explore, and share their own analyses without deep technical assistance. C. Analytics that run automatically on a schedule.
Intelligence Systems Ultimate
Exam
D. A feature that automatically writes DAX formulas. Answer: B Explanation: Self-service analytics empowers business users to generate insights without relying on developers. Question 39. Which of the following best describes “data virtualization” in a BI architecture? A) Physically moving all source data into a single warehouse. B) Providing a unified, logical view of data from multiple sources without replicating it. C) Encrypting data before loading it into the warehouse. D) Converting all data to a single file format. Answer: B Explanation: Data virtualization abstracts data location, allowing queries across disparate systems as if they were one. Question 40. Which of these is an example of an “external data source” that could be integrated into a BI solution? A) Transaction logs from the company’s ERP system. B) Publicly available economic indicators from a government website. C) Employee attendance records stored in the HR database. D) Internal inventory counts from the warehouse management system. Answer: B Explanation: External data sources are obtained outside the organization, such as public economic data. Question 41. In a BI dashboard, which design principle helps users quickly identify the most important metric? A) Use of a neutral gray background for all visuals. B) Placing the key metric in a larger, prominent card at the top of the page.
Intelligence Systems Ultimate
Exam
A) Employees to Departments B) Orders to Order Items C) Students to Courses D) Products to Suppliers (where each product has one supplier) Answer: C Explanation: Students can enroll in many courses, and each course can have many students; a bridge (junction) table resolves this relationship. Question 45. What is the main advantage of using a “measure group” in SQL Server Analysis Services (SSAS)? A) It stores metadata about dimensions only. B) It allows multiple fact tables to be queried together as a single logical unit. C) It encrypts the data in the cube. D) It automatically creates visualizations. Answer: B Explanation: Measure groups let SSAS combine several related fact tables, enabling unified analysis across them. Question 46. Which of the following is a typical use case for “forecasting” in a BI system? A) Determining the current number of open support tickets. B) Predicting next quarter’s sales based on historical trends. C) Displaying real-time server CPU utilization. D) Auditing user access logs. Answer: B Explanation: Forecasting applies statistical models to historical data to estimate future values, such as sales.
Intelligence Systems Ultimate
Exam
Question 47. In the context of cloud-based BI, what does “multi-tenant architecture” imply? A) Each customer has a dedicated physical server. B) Multiple customers share the same application instance while keeping data logically isolated. C) The system can only be accessed by one user at a time. D. All data is stored in a single global database without segregation. Answer: B Explanation: Multi-tenant designs enable cost-effective sharing of resources while maintaining data isolation per client. Question 48. Which DAX function would you use to calculate the average sales amount for the current filter context? A) AVERAGEX() B) SUM() C) COUNTROWS() D) MAXX() Answer: A Explanation: AVERAGEX() evaluates an expression over a table and returns the average, respecting the current filters. Question 49. Which of the following best describes “data profiling”? A) Creating visual dashboards for executives. B) Analyzing source data to assess quality, completeness, and structure before loading. C) Encrypting data at rest. D) Automating report distribution via email. Answer: B Explanation: Data profiling examines source data characteristics to identify issues prior to ETL.
Intelligence Systems Ultimate
Exam
Explanation: Data latency directly measures how current the displayed information is. Question 53. In a BI governance framework, what is the purpose of a “change control board (CCB)”? A) To develop new ETL scripts. B) To review, approve, and track changes to the BI environment ensuring stability and compliance. C) To design visual themes for dashboards. D) To manage user passwords. Answer: B Explanation: A CCB oversees modifications, reducing risk of unintended impacts on production systems. Question 54. Which of the following best explains “slowly changing dimension Type 1 (SCD1)”? A) Overwrites old attribute values with new ones, losing history. B) Adds a new record for each change, preserving historical versions. C) Stores only the most recent change in a separate audit table. D) Uses a hash to detect changes. Answer: A Explanation: SCD1 updates the dimension record in place, so previous values are not retained. Question 55. A BI developer wants to reduce the size of a fact table by removing rarely used columns. Which technique are they applying? A) Data archiving B) Column pruning (vertical partitioning) C) Index rebuilding D) Data masking
Intelligence Systems Ultimate
Exam
Answer: B Explanation: Column pruning eliminates unnecessary columns, decreasing storage and improving query performance. Question 56. Which of the following statements about “heat maps” is true? A) They are best for showing hierarchical data relationships. B) They use color intensity to represent magnitude across two dimensions. C) They require 3-D rendering to be understood. D) They are only useful for geographic data. Answer: B Explanation: Heat maps encode values with color gradients, making patterns across rows and columns easy to spot. Question 57. In the context of BI, what does “drill-through” enable a user to do? A) Export the entire data warehouse to Excel. B) Navigate from a summary visual to a detailed report showing the underlying transactions. C) Change the data model’s schema on the fly. D) Automatically generate predictive models. Answer: B Explanation: Drill-through provides a link from aggregated data to the granular records that compose it. Question 58. Which of the following is a primary function of a “data steward” in a BI environment? A) Write VBA macros for report automation. B) Define data standards, ensure data quality, and manage metadata. C) Install network switches. D) Conduct user acceptance testing for all dashboards.
