Ramith Attravanam

Background

An outbound Oracle FCCS integration needed a sequential row number on a Data Management file export. It sounded like a simple counter, but it sat at the intersection of three unique Data Management behaviours that made it more interesting than it first appeared.

1. The export needed opening and closing balances side by side — in FCCS this means assigning Movement dimension as the pivot dimension in export options and using two Movement members (FCCS_ClosingBalance and FCCS_TotalOpeningBalance) in the data load rule filter.
2. The row number had to reset with each run — meaning it had to be generated dynamically with each extract and could not be stored in the database.
3. The row number had to be the first column and the sort key — in FCCS this means creating a dimension for the export application and assigning it as the first column in the export.

The solution

The row number came from a single SQL mapping applied to the row-number dimension:

(SELECT LPAD(X.RN, 6, '0')
FROM (SELECT DATAKEY,
DENSE_RANK() OVER (PARTITION BY LOADID
ORDER BY ENTITY, ACCOUNT) AS RN
FROM TDATASEG_T) X
WHERE X.DATAKEY = TDATASEG_T.DATAKEY)

Why Choose DENSE_RANK Over ROW_NUMBER or RANK?

I tried the obvious options before landing on the right one, and it’s worth seeing why the first two don’t hold up:

• ROW_NUMBER() felt like the natural pick, but it gives each row its own number — the pair becomes 1 and 2, and the shared number the pivot depends on is gone.
• RANK() gets close but leaves gaps (1, 1, 3, 3…), so the sequence isn’t continuous.
• DENSE_RANK() allows pairs to share a number without gaps in the count.

The supporting touches

• PARTITION BY LOADID resets the count on every run, so each export starts cleanly at one.
• LPAD zero-padding keeps a character-based column sorting numerically when it sits in the first position.

Takeaways

Choose the window function for the behaviour you need — ROW_NUMBER, RANK, and DENSE_RANK look interchangeable until a requirement like a surviving-the-pivot row number forces the distinction.

Let PARTITION do the resetting. Scoping the count to LOADID is cleaner and more reliable than any post-processing step to zero out the counter between runs.

Zero-pad character sort keys. A numeric-looking column stored as text will sort lexically; padding restores the numeric order you actually want.

Scoping note: this approach assumes the export grain reduces to Entity and Account. If your file also carries dimensions that vary per row — ICP, Data Source, or custom dimensions — include them in the PARTITION BY and ORDER BY so two distinct output rows don’t collapse onto the same number.