As you probably realise, this post deals with apportionment in finance data warehouses,  not the U.S. Constitutional Apportionment Problem, interesting though that topic is.

ETL procedures may include apportionment logic, calculating the ratio of an individual value to the aggregate for the group it belongs to. The calculation is very simple: [individual value]/[aggregate of group values], but if data quality is poor it may cause your process to error if you don’t code around it; and even if there is no processing error, you are likely to see erroneous values in your output.

The most common run-time error I’ve seen is where the aggregate group value is pre-calculated in a joined table and something has gone wrong, resulting in a zero. To see this in practice, run the “Test Setup” code at the bottom of this article (it’s written in ANSI syntax and I’ve tested it in Sql Server and Oracle). Then execute the following query:
select T.NominalValue/TotalIssuance As Apportionment
from Trade T
left outer join Issuance I
on T.ISIN = I.ISIN;

You’ll see “Msg 8134 Divide by zero error encountered.” (Sql Server), or “ORA-01476: divisor is equal to zero” (Oracle). How can this be avoided? See below:

Eliminate divide by zero error
Sql Server/Oracle
select T.NominalValue/NULLIF(TotalIssuance, 0) As Apportionment
from Trade T
left outer join Issuance I
on T.ISIN = I.ISIN;

The result of the above is Null, which is probably the most correct answer, since we can’t do an apportionment using the method chosen. Will that work? Perhaps the target column doesn’t accept Nulls. This needs careful thought – I’ve seen a zero aggregate substituted with “1” which of course leaves individual values unchanged, but the numbers will be much too big. We could write a CASE statement that returns zero; we’re not dividing by zero, so it’ll work, but obviously we’ll lose all the individual values.

Oracle Only
An alternative is using an analytical function as shown below (something similar could be done in Sql Server but not quite as neatly) – but note that this is subtly different. It works out the proportions from the left table, and only uses the right-hand table for partitioning the values. In fact the query could be rewritten to ignore the right hand table completely by using T.ISIN for partitioning. Using this function might be an improvement over the NULLIF approach, or it might not. It would need careful consideration. Will we still need the join to the Issuance table? If so, is it OK if our apportionment is inconsistent with the Issuance table? If we retain the join, do we want the rows where there is no match between the two tables? If so, we’ll change it to a LEFT OUTER JOIN: but now we’ll still get an apportionment even if there’s no value at all on the issuance side. Is that consistent?
select RATIO_TO_REPORT(T.NominalValue) OVER (PARTITION BY I.ISIN) As Apportionment
from Trade T
left outer join Issuance I
on T.ISIN = I.ISIN;


Other problems
That’s not the end of the problem. Sometimes I have found that values are missed out from the aggregate calculation, so it’s a smaller number than it should be. We could test that each individual value is not larger than the aggregate before allowing an apportionment  – but it may be that although every individual value is less than the calculated aggregate, altogether they sum to a greater value than the aggregate. So if we anticipate missing values from the calculated aggregate, we may as well sum all the individual values for our comparison. This is shown in the sql below.

You will probably notice that in the query we are testing for [TotalIssuance] > 0; all this extra code is probably not very costly in performance terms, but it’s getting so complex that alternative approaches should probably be considered.

It would probably be a better idea to put a column constraint on TotalIssuance in the Issuance table, requiring values to be greater than zero. DML error logging in the queries that insert data would avoid batch failures, and the bad rows could be automatically copied back from the error log into the table, but with the negative and zero values substituted with Null.

If the decision is made to persevere with defensive coding, we should also test for negative numbers among the individual values. We provide a column in the derived table with a count of the negatives, and forbid an apportionment if the count is greater than zero.

select T.ISIN
,T.TradeId
,T.NominalValue
,I.TotalIssuance
,CASE WHEN TotalNominal <= I.TotalIssuance And I.TotalIssuance > 0 And NegativeValueCount = 0
THEN T.NominalValue/I.TotalIssuance ELSE Null END As Apportionment
from
(
select dt.*
,SUM(dt.NominalValue) OVER (PARTITION BY dt.ISIN) AS TotalNominal
,SUM(CASE WHEN dt.NominalValue < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY ISIN) NegativeValueCount
from Trade dt
) T
left outer join Issuance I
ON T.ISIN = I.ISIN
order by I.ISIN
,T.TradeId


,CASE WHEN TotalNominal <= I.TotalIssuance And NegativeValueCount = 0 THEN T.NominalValue/I.TotalIssuance ELSE Null END As Apportionment2

Test Setup
/*
drop table Trade;
drop table Issuance;
*/

create table Trade
(
TradeId character varying(20) NOT NULL
,ISIN character varying(12) NOT NULL
,NominalValue float NOT NULL
);

insert into Trade
(
TradeId
,ISIN
,NominalValue
)
VALUES
(
'00100'
,'XXXXXXXXXXXX'
,1000000
);

insert into Trade
(
TradeId
,ISIN
,NominalValue
)
VALUES
(
'00101'
,'XXXXXXXXXXXX'
,1000000
);

create table Issuance
(
ISIN character varying(12) NOT NULL
,TotalIssuance float NOT NULL
);

insert into Issuance
(
ISIN
,TotalIssuance
)
VALUES
(
'XXXXXXXXXXXX'
,0
);

Summary
This problem doesn’t come up all the time, so it’s quite easy to apply a quick fix without thinking through all of the possibilities. I hope this blog is some help.