Gains:
- Ability to explain business intelligence layers (source, ETL, data warehouse, report) and the correct definition of key business metrics (KPIs).
- Ability to use artificial intelligence to produce metric definition, SQL draft and report narrative and provide the result with real data
- Ability to recognize risks of correlation-causation confusion and misleading metrics in AI-supported analysis outputs
Business intelligence (BI) is the discipline that collects the dispersed data of the organization, makes it ready for analysis, and produces decision-supporting information from this data. For an MIS professional, BI is the layer where “data turns into decisions.” Raw order records alone are not meaningful; but "in which region did the turnover decrease this month, why?" It produces value when it becomes a report that can answer the question. In this unit, we will see the layers of BI, the right metric design, and where artificial intelligence is an accelerator and a trap in this process.
BI architecture generally consists of the following layers. Source systems: Places where data originates, such as ERP, CRM, e-commerce. ETL process (English Extract-Transform-Load): The process that extracts the data from the sources (Extract), cleans it and converts it into a standard structure (Transform) and loads it to the target (Load). Data warehouse: A central repository where historical and consistent data designed for analysis is collected. Reporting/visualization layer: dashboards, reports, and ad-hoc queries. In this chain, the quality of each layer determines the next; If the source is dirty, the report is dirty too.
Defining Metrics and KPIs Correctly
A metric is a measured numerical value: total turnover, number of orders. A KPI (Key Performance Indicator) is a critical metric that measures performance against a target: "monthly customer churn rate below 5%". Not every metric is a KPI; A KPI is a metric that is tied to a business goal and triggers a decision.
The most insidious problem of BI projects is the vague definition of metrics. What does "active customer" mean? Ordered in the last 30 days or 90 days? Are returnees counted? If two teams mean different things by “number of active customers,” the same dashboard shows two different facts. That's why every KPI should have a one-sentence, widely accepted definition. AI quickly produces drafts of these definitions; but it is up to the business unit to decide which definition is "correct".
Tip: When designing a KPI, write down three things: (1) formula (what exactly is the numerator/denominator), (2) time window, (3) excluded cases. Having the AI say "extract the ambiguities in the definition of this KPI as questions" reveals hidden assumptions.
Step by Step: AI-Powered Report Generation
Step 1 — Clarify the question. What decision will the report serve? A concrete goal such as "we will decide which region we will shift the budget to", not "it should look nice".
Step 2 — Define metrics. Write the required KPIs with formulas, windows and exceptions. Artificial intelligence can produce draft definitions.
Step 3 — Generate SQL draft. Give schema information to artificial intelligence and produce a query draft. But read and understand the query before running it.
Step 4 — Verify with small data. Run the query first on a small sample with known results; Check totals manually. AI's SQL may be syntactically correct but logically incorrect.
Step 5 — Add narrative, test claims. AI can produce a narrative text for the report; but prove every causal claim (“that's why sales fell”).
The Correlation and Causation Trap
The most dangerous mistake in BI is to interpret two metrics that act together as "one creates the other". Correlation is when two values change together; Causation is when one causes another. The sentence "As ice cream sales increased, drowning cases increased" is true, but ice cream does not cause drowning; The common cause is summer (hot weather). Artificial intelligence can easily make causal sentences when producing a report narrative; The MIS expert responds to these claims by asking "is there any other explanation?" He should test it. Otherwise, the wrong decision will be made based on the wrong reason.
Three Mini Cases: By the Numbers
Case 1 — Cost of undefined metric. In a telecom company, the number of "active subscribers" presented to the board of directors was 2.1 million, and the finance team's report was 1.7 million. The difference was that one counted 90 days as "active" while the other counted 30 days. The wrong growth rate was discussed for two weeks until the common definition was clarified. A one-sentence KPI definition would avoid this confusion.
Case 2 — AI's wrong SQL. At one retailer, AI added return lines to the total when generating the query “average basket per customer”; The result was 12% above the actual value. SQL was syntactically perfect. When the expert manually verified a known day's total, he caught the deviation and had the returns filter added.
Case 3 — Fallacy of causality. At an e-commerce company, the dashboard was saying "sales are 18% higher on days an email campaign is sent" and the team was about to increase the campaign budget. The analysis showed that the campaigns were already timed to coincide with high-traffic campaign days (discount periods). It was the period, not the email, that drove sales. If the budget was increased without testing with a control group, the money would be wasted.
Weak Prompt / Strong Prompt
Weak prompt:
Write sales report SQL from this table.
Powerful prompt:
Your role: You are an attentive BI analyst.Write a DRAFT of SQL query according to the diagram below.Rules:- Use only the given tables/fields; non-fitting field.- EXCLUDE returns (status='Return') from the total.- Time window: last 30 days.- Comment line by line what the query does.- Suggest 1 sample line that can be manually validated for testing at the end.Schema:Order(id, customer_id, date, amount, status)Customer(id, name, segment)Purpose: last 30 days net turnover by segment.
The powerful prompt limits the schema, imposes the business rule (excluding return), specifies the window, and requests a verifiable output.
Four Copiable Templates
1) KPI definition clarification:
Write full description for the following KPI: formula (numerator/denominator), time window, excluded cases. Add any ambiguity in the definition as a question.KPI: [name, e.g. "customer churn rate"]
2) SQL logic check:
Examine the following SQL query: is there a risk of logical errors, incorrect JOINs, missing filters, or double counting? Write a justification for each finding. Don't change the query, just check it. SQL: [query]
3) Report narrative + claim control:
Write a brief executive summary from the results table below. Label [EVIDENCE REQUIRED] next to each causal claim and suggest an alternative explanation. Just rely on the data in the table. Table: [data]
4) Metric consistency check:
In the two reports below, metrics with the same name give different values. Possible differences in definition (time window, filter, calculation) are listed. Reports: [A] [B]
Comparison Chart: Good and Bad KPI
feature
Bad KPI
Good KPI
Description
"Active customer"
"Customer with ≥1 completed order in the last 30 days"
bond with target
None
"Staying below 5% loss rate"
measurability
ambiguous
formula clear
exception
uncertain
Excluding returns
Does it trigger a decision?
no
Yes
Common mistakes
- Leaving the metric undefined. If words like "active," "successful," "completed" are used without a formula, each team counts differently.
- Running the AI's SQL without verification. A syntactically correct query may be logically incorrect; double counting and incorrect JOIN are common.
- Confusing correlation with causation. Thinking that "it increased with that" means "this caused that" would lead to the wrong decision.
- Vanity metric pursuit. Mistaking fancy but non-decisive metrics such as "total clicks" as KPIs.
- Presenting numbers without context. "Turnover 4.2 million" alone is meaningless; Context is needed based on last month, goal, or budget.
Caution: Report narratives produced by artificial intelligence are convincing and fluid; This exactly magnifies the risk. A fluent sentence may carry a false claim of causality. Test each "because" and "therefore" statement with evidence.
In summary
Business intelligence is the layer that transforms scattered data into decisions and consists of source, ETL, data warehouse and reporting chain. A KPI is a critical metric tied to a business goal, with a clearly defined formula and exceptions; undefined metric is the most common BI error. Artificial intelligence provides significant speed in producing KPI definition, SQL draft and report narrative; but every SQL must be logically justified, every number must be supported by known data, and every causal claim must be tested with evidence. Correlation is not causation; Fluid narrative does not guarantee accuracy.
Application task
Design a “completion rate” KPI for an online course platform. (1) Write a one-sentence description with formula, time window, and exceptions (e.g., are canceled registrations counted?). (2) Make up a simple schema (Registration, Course, Progress) and have the SQL draft generated for this KPI with a powerful prompt. (3) Find at least one possible risk of double counting or incorrect filtering in the query. (4) Print an executive summary of the conclusion and mark each causal claim in it. (5) Set up an example of the correlation-causation trap and explain how you would test it.
checklist
- [ ] Formula, time window and exception of each KPI are written.
- [ ] I read and understood the SQL generated by AI line by line.
- [ ] I manually validated the query with little known data.
- [ ] I tested every causal claim in the report with evidence.
- [ ] I contextualized each number with a benchmark (target/last period).
- [ ] I achieved cross-team consensus on metric definitions.