Working with Oracle Analytics Cloud on August 11, 2020.
I have a simple analysis that lists all of the members of our loyalty program with a status of "Canceled - Abandoned". There are 9 such members.
If I exclude OR delete the Birth Date column, I see the grand total of 9. So far, so good.
Member Status and Birth Date are sourced from one dimension table ( "Dim - Member" )
# of Members is created in a logical fact table in the RPD with this formula: COUNT( DISTINCT "Dim - Member"."Member ID" )
When a member of the loyalty program makes a purchase, that member accumulates points that can be redeemed for merchandise. Those accumulated points are recorded in the transaction fact table, and are represented in OBI as the Accrual Points column. I will add Accrual Points to this analysis.
Notice that the member whose birthday is August 1, 1937 has never purchased anything, and therefore has accumulated no Accrual Points. But he IS in the member list, and carries the correct status, so he belongs here.
Now I will EXCLUDE the Birth Date column. Everything still looks good.
But when I DELETE the Birth Date column, the member count shown in the body of the table becomes inconsistent with the grand total row:
Can anyone offer a suggestion for returning '9' instead of '8' in the body of the result table?
One answer might be to just exclude the column. Unfortunately, my client uses DV exclusively, so this table has to be created in DV, where excluding a column is not yet possible. (And I checked - DV produces the same SQL as Classic for this report.)
If needed, here is the SQL that OAC generates to produce the last screenshot above:
WITH
SAWITH0 AS (select sum(T2297.ACCRUAL_POINTS) as c1,
count(distinct T4041.MEMBER\_ID) as c2,
T4041.MEMBER\_STATUS as c3,
T4100.SORT\_ORDER as c4
from
DT\_DW\_OWNER.W\_MEMBER\_MD1 T4041 /\* Dim\_W\_MEMBER\_MD1 \*/ ,
DT\_DW\_OWNER.W\_MEMSTAT\_D T4100 /\* Dim\_W\_MEMSTAT\_D \*/ ,
DT\_DW\_OWNER.W\_MEMBER\_D T238 /\* Dim\_W\_MEMBER\_D \*/ full outer join
DT\_DW\_OWNER.W\_TRANSACTIONS\_F T2297 /\* Fact\_W\_TRANSACTIONS\_F \*/ On T238.MEMBER\_ID = T2297.MEMBER\_ID
where ( T238.MEMBER_ID = T4041.MEMBER_ID and T238.MEMBER_STATUS = T4100.MEMBER_STATUS and T2297.MEMBER_ID = T4041.MEMBER_ID and T4041.MEMBER_STATUS = T4100.MEMBER_STATUS and T4041.MEMBER_STATUS = 'Canceled - Abandoned' )
group by T4041.MEMBER_STATUS, T4100.SORT_ORDER),
SAWITH1 AS (select distinct T4041.MEMBER_ID as c1,
T4041.MEMBER\_STATUS as c2,
T4100.SORT\_ORDER as c3
from
DT\_DW\_OWNER.W\_MEMBER\_MD1 T4041 /\* Dim\_W\_MEMBER\_MD1 \*/ ,
DT\_DW\_OWNER.W\_MEMSTAT\_D T4100 /\* Dim\_W\_MEMSTAT\_D \*/
where ( T4041.MEMBER_STATUS = T4100.MEMBER_STATUS and T4041.MEMBER_STATUS = 'Canceled - Abandoned' ) ),
SAWITH2 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c8 as c8
from
(select 0 as c1,
coalesce( D1.c3, D2.c2) as c2,
coalesce( D1.c4, D2.c3) as c3,
D1.c2 as c4,
D1.c1 as c5,
D2.c1 as c8,
ROW\_NUMBER() OVER (PARTITION BY D2.c1, coalesce( D1.c3, D2.c2), coalesce( D1.c4, D2.c3) ORDER BY D2.c1 ASC, coalesce( D1.c3, D2.c2) ASC, coalesce( D1.c4, D2.c3) ASC) as c9
from
SAWITH0 D1 left outer join SAWITH1 D2 On D1.c4 = D2.c3 and SYS\_OP\_MAP\_NONNULL(D1.c3) = SYS\_OP\_MAP\_NONNULL(D2.c2)
) D1
where ( D1.c9 = 1 ) )
select D1.c2 as c3,
D1.c3 as c4,
D1.c1 as c2,
D1.c4 as c5,
D1.c5 as c6,
D1.c8 as c7
from
SAWITH2 D1