Consider the following two example metrics:
Sum(Sum(Revenue) - Sum(Profit))
(Sum(Revenue) - Sum(Profit))
The following are the SQL passes when each one has just one attribute, Call Center, on the rows of the report:
SQL for Sum(Sum(Revenue) - Sum(Profit)):
Pass0 - Duration: 0:00:00.06
select a11.CALL_CTR_ID CALL_CTR_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS2,
into #ZZTTT0200AQMD000
from CITY_CTR_SLS a11
group by a11.CALL_CTR_ID
Pass1 - Duration: 0:00:00.03,
select distinct pa11.CALL_CTR_ID CALL_CTR_ID,
a12.CENTER_NAME CENTER_NAME,
(ISNULL(pa11.WJXBFS1, 0) - ISNULL(pa11.WJXBFS2, 0)) WJXBFS1
from #ZZTTT0200AQMD000 pa11
join LU_CALL_CTR a12
on (pa11.CALL_CTR_ID = a12.CALL_CTR_ID)
Pass2 - Duration: 0:00:00.03
drop table #ZZTTT0200AQMD000
SQL for (Sum(Revenue) - Sum(Profit)):
Pass0 - Duration: 0:00:00.07
select a11.CALL_CTR_ID CALL_CTR_ID,
max(a12.CENTER_NAME) CENTER_NAME,
(sum(a11.TOT_DOLLAR_SALES) - sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST))) WJXBFS1
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
group by a11.CALL_CTR_ID
Because (Sum(Revenue) - Sum(Profit)) is only using two facts and subtracting them directly, then it only needs one SQL pass and can just subtract them automatically. However, since Sum(Sum(Revenue) - Sum(Profit)) needs to do a summation of the results of the two individual metrics calculated, then it needs two passes to calculate this out. The first pass is used to get the two individual calculations, and then the second pass is used to add them together.
When using addition and subtraction operations, it will not matter if there is an additional summation ('Sum(Sum(Fact1) - Sum(Fact2))') or not. However, for division and multiplication it does matter. Refer to the following example:
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Login to pALogin to pB (statistics is disabled for pB)Complete Session Logging2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pA is the designated project)2 entries; 1 for login to server for pA + 1 for login to pA1 entry; 1 for login to server for pBSingle Instance Session Logging (pB is the designated project)1 entry; 1 for login to pANo entryxy110220330440550
|
Addition
Sum(Sum(x) + Sum(y)) = Sum(15 + 150) = 165
Sum(x + y) = Sum(1 + 10) + Sum(2 + 20)..... Sum(5 + 50) = 165
Multiplication
Sum(Sum(x) * Sum(y)) = Sum(15 * 150) = 2250
Sum(x * y) = Sum(1 * 10) + Sum(2 * 20) ...... Sum(5 * 50) = 550
Therefore, users need to be mindful of what operation they are using and the implications of them. As stated above, with addition and subtraction, it does not matter if there is an additional operation or not. This will just result in an extra SQL calculation which may cost users some performance based on their database systems.
