Categories
Controlling the Count function in OAC
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
Answers
-
First question: Why does the count distinct run on the dimension? How is that really configured in the RPD? Can you screenshot?
0 -
Going in the same direction as Christian: your model isn't ideal. You are "cheating" by using a dim attribute as fact and this generate that "nice" query. If you have to count members, that one must be a real measure which can exist without needing the dimension (even if you will keep using it because of your filter on the member status).
0 -
Gianni, I think I understand what you and Christian are saying. You stated "if you have to count members, that must be a real measure which can exist without needing the dimension." With that in mind, let me explain where I'm getting stuck in my thinking.
How many members do we have in total? Let's say it's 20 total members. All 20 of those members can be found in the Member dimension table. Each member has a status. We can ask the question "how many members are found in each status?", and the total of all statuses will be 20.
But not every member is present in the fact table, because some members haven't yet made a purchase. Let's say there are only 18 of our 20 members who have earned any membership points by making a purchase. If I count the # of members in the fact table, I will only get 18 - the fact table has no records for the other 2 members.
That's probably why the column formula was written against the dimension table and not the fact table. The client wants to see the total number of members for each member status code, and the total number of points for each member status code. Querying against the fact table cannot return the total number of members - it would return 18, not 20.
So Gianni, you got me thinking: What if I modify my dimension table W_MEMBER_D and add a column of 1's, called MEM_COUNT. Then, I could create an alias table of W_MEMBER_D, join that new alias table with the dimension table (with this new alias table on the "fact" side of the join), and then source the logical # of Members column from that new alias table, which looks like a fact table because of the direction of the join. What do you think? Would that be a way to approach the requirement? Is there something better?
0 -
Christian, I inherited the RPD, so I'm not sure why the count function was written against the dimension table. Here's that screenshot confirming it:
0 -
Your purchase table is a fact table, nothing to say against this one.
But does this prevent you from having the member dimension also as fact table? Not really...
By using both as fact tables you can achieve your need without problems. The member dimension is used for the count(distinct member id), the purchase table is used as sum for the points.
But don't use the member dim playing the role of fact for any of your attributes: a fact table must only have measures (with aggregations), a dimension must only have attributes.
0 -
I think I understand, Gianni. I am testing, will reply back here with results.
0 -
Something along this line would make it ...
You can have a LTS with the dim members just for the count(distinct member ID), and another one with the dim members left outer join fact purchase which can answer to both the count distinct and the sum of points.
OBIEE will do the job of picking the best one for the need.
0 -
Gianni, your guidance was right on the money. Your first comment led me a significant way down the correct path. Once I started describing what I was doing, it was clear in my mind where you were indicating I should go. Thank!
0 -
In addition to everything else....this made me gape. Literally.
Never. Ever. EVER. Make a derived logical column that's a base measure. Just to begin with all that's wrong with this: That's executed post-aggregate. On a result data set. Not row by row.
Epic facepalm kinda begins to express my feeling right now That RPD needs some serious review. Derived measures post-agg when used "just because" are pretty much always outright wrong.
0 -
Thanks, Christian. That's probably one of the best pieces of advice EVER.
0
