Business Intelligence Foundations and Data Modeling

Review questions

Integrated summary: Business Intelligence Foundations and Data Modeling

Subject 1 connects four layers that should be stable before a strong dashboard is designed. The first layer is the business question. Business intelligence is not the display of as many numbers as possible; it connects a user and question to data, calculations, context, and then a decision or action. A card saying “80% within target” is therefore incomplete unless the target definition, numerator, denominator, period, exclusions, and refresh time are known. In the teaching example, 720 of 900 eligible requests met the target, giving 80%. The number describes a result; the decision needs a comparison target and analysis of where the remaining cases are concentrated.

The second layer is the analytical model. Before choosing relationships or aggregations, define grain: what does one row represent? When a fact table is at part-line-within-request grain, the same request may appear in several rows. COUNT(rows) then counts lines rather than requests. A star schema places measurable events or observations in fact tables and descriptions such as service, team, and date in dimensions that support filtering and grouping. This separation does not solve every problem automatically, but it makes row meaning, relationships, and measures easier to inspect.

The third layer is data quality and fitness for purpose. Data can be complete but stale, or current but duplicated. Dimensions such as accuracy, completeness, consistency, timeliness, uniqueness, and conformity describe the type of issue, after which we ask how it could affect the decision. If 150 of 1,000 requests have no TeamID, 15% cannot be assigned to a team in a performance comparison. We should not invent team assignments; a temporary “Unknown” category can preserve visibility while the gap is disclosed and the source is fixed. Profiling helps reveal missing values, duplicates, ranges, and unusual patterns, but it does not by itself prove accuracy.

The fourth layer connects grain, aggregation, and filter context. In the part-line example, values of 30, 20, and 50 sum to part cost of 100. Three rows represent two requests, so average part cost per request is 100 ÷ 2 = 50, not 100 ÷ 3. If shipping is 5 per request but repeated on line rows, a simple SUM gives 15 even though the true fee for two requests is 10. This is double counting caused by mixing request-grain and line-grain values.

A measure also changes when filter context changes. If R100 has part cost 50 in North and R101 has part cost 50 in South, total cost is 100 with no region filter, 50 under North, and 50 under South. The formula is unchanged; the row set entering the calculation has changed. Measures should therefore be tested with small hand-checkable data, and relationship paths and active filters should be validated before differences are interpreted.

A transferable application from this subject is a small design card used before any dashboard: write the user, question, and decision; define fact-table grain, required dimensions, the measure and its definition, data-quality checks, a manual validation example, and the filters that should change the result. If this card cannot be completed clearly, the problem is not chart selection yet; it is still the question, data, or model definition.

Review questions

Test your understanding with these questions. Marking and saved results are available with a free account.

Question 1A manager asks for “a dashboard with all the numbers.” What is the most useful first step before choosing visuals? Collect as many available fields as possible. Frame the business question, user, and possible decision. Choose the most visually attractive chart. Calculate the average of every numeric column.
Question 2A card shows “80% within target.” Which information most improves traceability? The card color used in the previous version. The font used in the report. The numerator, denominator, period, and exclusions. The number of dashboard pages.
Question 3A team has 1,200 requests and satisfaction of 4.6/5, but the question is service-time compliance. Which information is closest to answering it? The share of eligible requests closed within target time. Total number of employees in the organization. Number of colors used in the dashboard. Average customer age.
Question 4In a table where each row represents “one product line within an order,” what does COUNT(*) directly represent? Number of distinct orders. Number of distinct customers. Number of product lines. Average order value.
Question 5Which design is closest to a star schema for analyzing part cost by service type? A line-cost fact table related to a service dimension that describes service type. One table that repeats the full service description in every text field. Only dimension tables with no measurable events or values. Several fact tables with no defined grain or clear relationships.
Question 6One table contains an order-total row and the individual lines for the same order. What is the main risk with SUM(Amount)? The row count will automatically decrease. The total is summed with its components, causing double counting. Text values become dates. Dimensions disappear from the model.
Question 7Fifteen percent of requests have no TeamID and the dashboard compares team performance. Which quality dimension is most direct? Completeness. Timeliness only. Visual attractiveness. File size.
Question 8A team-dispatch dashboard refreshes once a week while decisions are daily. What is the best interpretation? The data may be temporally unfit for the purpose even if complete. The data are excellent simply because a schedule exists. All older records should immediately be deleted. The issue has nothing to do with data quality.
Question 9Which profiling result needs deeper verification rather than being treated as proof of accuracy? All values fall between 1 and 100, so we conclude they are accurate. Twenty percent of records lack a category and we assess the analytical impact. Forty duplicate IDs appear, so we investigate why. Last refresh was five days ago, so we compare it with decision needs.
Question 10A line table has three rows representing two requests. What is the correct count of requests? 3 using row COUNT. 2 using DISTINCTCOUNT of RequestID. 100 using total cost. 50 using an average.
Question 11A shipping fee of 5 per request is repeated across three line rows for two requests. What does the incorrect SUM return? 5. 10. 15. 20.
Question 12The same measure returns 100 with no filter and 50 when North is selected. What is the best explanation? The formula automatically changed. Filter context changed the rows included in the calculation. All model relationships must be wrong. A result of 50 cannot be correct.