Lesson 7 Practice — Booking Statistics with Aggregation

The guided lesson summarized:

/DMO/FLIGHT

Now summarize:

/DMO/BOOKING

Your goal is to answer:

For each carrier and currency, how many bookings exist and what do their flight-price values look like?

The architecture is:

/DMO/BOOKING
      ├──► ZI_BOOKING_STATS_###
      └──► ZI_BOOKING_HEAVY_###
                    ↓
                  Reader
                    ↓
                  Runner

You will also create a second grouped CDS View Entity that uses:

HAVING

to keep only sufficiently large booking groups.

---

Objects to create

ZI_BOOKING_STATS_###
ZI_BOOKING_HEAVY_###
ZCL_BOOKING_STATS_READER_###
ZCL_CDS_L07_P_###

Replace ### with your own suffix.

---

Part 1 — Inspect /DMO/BOOKING

Open Data Preview for:

/DMO/BOOKING

Find at least:

travel_id
booking_id
carrier_id
connection_id
flight_date
flight_price
currency_code

Answer:

What does one /DMO/BOOKING row represent?

What are the table key fields?

---

Part 2 — Define the output grain before writing CDS

Your summary should contain one row for each:

CarrierId + CurrencyCode

Write this sentence before coding:

One result row represents:
____________________________________________

Do not continue until the grouping requirement is clear.

---

Part 3 — Explain why CurrencyCode is part of the group

Consider:

100 EUR
200 USD

Answer:

Why would a total of 300 be semantically incomplete?

Why does grouping by currency solve the basic exercise?

Does grouping by currency perform currency conversion?

---

Part 4 — Create ZI_BOOKING_STATS_###

Create a Data Definition:

Name:        ZI_BOOKING_STATS_###
Description: Booking Statistics

Use a modern CDS View Entity.

Do not use obsolete:

legacy generated SQL-view-name annotation

---

Part 5 — Required grouping dimensions

Expose:

CarrierId
CurrencyCode

Use the source fields:

carrier_id
currency_code

Both dimensions must participate in GROUP BY.

---

Part 6 — Add BookingCount

Create:

BookingCount

using:

COUNT(*)

Answer:

What exactly does the count represent for one result row?

---

Part 7 — Add total booking value

Create:

TotalBookingValue

using:

SUM(flight_price)

Preserve the semantic relationship to:

CurrencyCode

using the amount/currency annotation learned in Lesson 6.

---

Part 8 — Transfer the AVG rule without mixing in currency complexity

You already implemented AVG safely in the guided Flight view using the numeric field:

seats_occupied

Before continuing, write the correct CDS expression from memory for:

average occupied seats with two decimal places

Then verify it against ZI_FLIGHT_STATS_###.

Answer:

Why does CDS require an explicit result type for AVG?

Why are we not making AVG( flight_price ) part of the required Booking solution?

Use these ideas:

amount semantics
currency context
keep the practice focused

---

Part 9 — Add minimum and maximum

Create:

LowestBookingValue
HighestBookingValue

using:

MIN
MAX

Preserve the amount/currency semantics for both.

---

Part 10 — Write GROUP BY

Your grouping must match the intended output grain.

Do not group by:

TravelId
BookingId

because that would make the groups too detailed.

Do not use the current select-list aliases in place of the source fields.

---

Part 11 — Activate and preview

Run:

Ctrl + F2
Ctrl + F3
F8

Inspect several result rows.

For each row, verify mentally:

CarrierId
CurrencyCode
BookingCount
TotalBookingValue

---

Part 12 — Check that currencies remain separate

Look for a carrier that may have more than one currency in the demo data.

If present, confirm that it produces separate rows such as:

Carrier A + EUR
Carrier A + USD

rather than one combined monetary total.

If your current Trial data does not contain such a carrier, explain what the model would do if it did.

---

Part 13 — Create a HAVING view

Create:

ZI_BOOKING_HEAVY_###

Requirements:

one row per CarrierId + CurrencyCode
BookingCount
TotalBookingValue

Keep only groups with at least:

2 bookings

Use:

HAVING

not an upper CDS layer for this exercise.

---

Part 14 — Explain the HAVING condition

Write the condition in plain language:

First ______________________________________
Then  ______________________________________
Finally ____________________________________

Use these concepts:

group
count
filter groups

---

Part 15 — Change the threshold experimentally

Preview with a threshold of:

2

Then try a larger threshold.

Record:

Threshold:
Result group count:

Restore the course threshold when finished.

The exact data depends on your Trial system.

---

Part 16 — Compare WHERE and HAVING

Complete:

Question	WHERE	HAVING
Filters individual source rows?
Filters grouped result?
Happens before grouping?
Happens after aggregation?
Can directly contain aggregate expressions for this purpose?

---

Part 17 — Reader

Create:

ZCL_BOOKING_STATS_READER_###

Add public table types based on:

ZI_BOOKING_STATS_###
ZI_BOOKING_HEAVY_###

Create:

GET_ALL
GET_HEAVY

Requirements:

• GET_ALL returns all carrier/currency statistics;
• GET_HEAVY returns only the groups kept by the HAVING view;
• use ABAP SQL against the CDS entities;
• sort both results by CarrierId, then CurrencyCode;
• do not select directly from /DMO/BOOKING in the Reader.

---

Part 18 — Runner

Create:

ZCL_CDS_L07_P_###

Implement:

IF_OO_ADT_CLASSRUN

The Runner should:

1. call GET_ALL;
2. print a heading and all grouped statistics;
3. call GET_HEAVY;
4. print a second heading and only the groups that passed HAVING.

The Runner must not contain manual aggregation loops.

Compare the two outputs and explain what the HAVING view removed.

---

Part 19 — Code-pushdown explanation

Compare:

A)
SELECT all bookings
→ ABAP LOOP
→ manual grouping
→ manual totals

with:

B)
CDS GROUP BY / aggregates
→ ABAP receives summary rows

Explain at least three reasons why B is a better model for this requirement.

Do not write:

CDS is always faster.

Use a more precise explanation involving:

set-oriented processing
data transfer
reuse
appropriate database calculation

---

Part 20 — SQL aggregation vs analytical annotations

Answer:

Does this lesson require @Aggregation.default?

What is the difference between this grouped CDS query and an analytical model whose measures are aggregated dynamically by an analytical consumer?

For this assignment, do not add analytical aggregation annotations.

---

Part 21 — Deliberate over-grouping experiment

Temporarily add:

booking_id

to both:

select list
GROUP BY

Preview the result.

Answer:

What happened to BookingCount?

Why did the aggregate become less useful?

Restore the correct model afterward.

---

Part 22 — Alias experiment

Temporarily try to use:

CarrierId
CurrencyCode

as if they were the source-field names in the same CDS view's GROUP BY.

Run syntax check.

Then restore:

Booking.carrier_id
Booking.currency_code

Record what ADT tells you.

---

Part 23 — AVG syntax recall

Without changing the Booking view, write a valid CDS AVG expression for:

Flight.seats_occupied

with result type:

abap.dec( 15, 2 )

Compare your answer with the guided ZI_FLIGHT_STATS_### source.

This keeps the AVG syntax in memory without turning amount typing into an unrelated obstacle in the Booking exercise.

---

Part 24 — DISTINCT reasoning

Would this solve the requirement?

SELECT DISTINCT carrier_id, currency_code, flight_price

Answer:

Why or why not?

Explain the difference between:

removing duplicate result rows

and:

calculating grouped summaries

---

Part 25 — Money reasoning

Suppose the requirement changes to:

Show one total booking value per carrier, regardless of original currency.

Would simply removing CurrencyCode from GROUP BY be correct?

Explain what extra modeling concern is required.

Hint:

currency conversion

---

Part 26 — SQL Console comparison

Write an ABAP SQL query in SQL Console that calculates at least:

CarrierId
CurrencyCode
BookingCount
TotalBookingValue

from /DMO/BOOKING.

Compare its result with your CDS Data Preview.

Explain why a reusable CDS entity can still be preferable when the same semantic summary is needed by several consumers.

---

Part 27 — Architecture explanation

Complete:

Runner
  ↓
____________________________
  ↓
____________________________
  ↓
GROUP BY + aggregate functions
  ↓
____________________________

Use:

Reader
ZI_BOOKING_STATS_###
/DMO/BOOKING

---

Part 28 — Certification-style requirements task

Without looking at the guided source, write the skeleton for a grouped CDS View Entity that has:

two dimensions
COUNT(*)
SUM(...)
AVG(... with explicit result type)
GROUP BY
HAVING count(*) >= n

Use placeholder business fields if necessary.

Then compare the skeleton with SAP Help/your lesson.

---

Part 29 — Reasoning questions

1. What is an aggregate expression?

2. What does GROUP BY define?

3. What is the output grain of your Booking statistics view?

4. Why are CarrierId and CurrencyCode grouped?

5. Why are TravelId and BookingId not grouped?

6. What does COUNT( * ) mean for one group?

7. Why does AVG need an explicit result type in CDS?

8. What does MIN return?

9. Does MAX( FlightPrice ) return the complete booking with the largest price?

10. What is the difference between WHERE and HAVING?

11. Why can HAVING use an aggregate expression?

12. Why should monetary totals preserve currency semantics?

13. Does grouping by currency perform conversion?

14. What is code pushdown?

15. Why should an ABAP consumer not rebuild the same aggregation with a manual loop without a good reason?

16. When could a higher CDS layer with WHERE be preferable to direct HAVING?

---

Optional challenge 1 — Layered filtering

Create a third CDS View Entity on top of:

ZI_BOOKING_STATS_###

Filter:

BookingCount >= 2

using:

WHERE

Compare this with:

ZI_BOOKING_HEAVY_###

which uses HAVING directly.

Explain which view is more reusable and why.

---

Optional challenge 2 — Aggregate by connection

Create another summary with grain:

CarrierId + ConnectionId + CurrencyCode

Compare its row count with the carrier/currency summary.

Explain why adding a grouping dimension increases the number of possible groups.

---

Submission checklist

• /DMO/BOOKING inspected.
• Output grain written before coding.
• ZI_BOOKING_STATS_### created.
• Modern define view entity used.
• CarrierId selected.
• CurrencyCode selected.
• BookingCount uses COUNT(*).
• TotalBookingValue uses SUM.
• Amount/currency semantics retained.
• AVG(... as type) rule recalled correctly from the guided Flight example.
• MIN and MAX implemented.
• GROUP BY uses source fields.
• CarrierId + CurrencyCode defines the intended grain.
• No accidental grouping by BookingId/TravelId remains.
• ZI_BOOKING_HEAVY_### created.
• HAVING filters the groups.
• WHERE vs HAVING explained.
• Reader consumes both aggregate CDS entities.
• Runner prints both the full and HAVING-filtered results.
• Runner contains no manual aggregation loop.
• Code pushdown explained accurately.
• Over-grouping experiment completed and restored.
• AVG syntax recall completed.
• Currency-conversion reasoning answered.
• Reasoning questions completed.

---

Useful ADT shortcuts

Action	Shortcut
Open ABAP object	Ctrl + Shift + A
Content assist	Ctrl + Space
Quick Fix	Ctrl + 1
Syntax check	Ctrl + F2
Activate	Ctrl + F3
Activate inactive objects	Ctrl + Shift + F3
CDS Data Preview	F8
Run console class	F9
Format source	Shift + F1