Force Join on Count from Lookup Table

[JRO061601]

I have a lookup table, CUST, with columns:

CUST_ID
HOUSEHOLD_ID

I have two fact tables, OUTBOUND and RESPONSE, where CUST_ID is part of the primary key.

I want to create a report that has two counts:

1) COUNT (DISTINCT A.HOUSEHOLD_ID)
FROM CUST A, OUTBOUND B
WHERE A.CUST_ID=B.CUST_ID

2) COUNT (DISTINCT A.HOUSEHOLD_ID)
FROM CUST A, RESPONSE B
WHERE A.CUST_ID=B.CUST_ID

In other words, there are fewer households than there are customers. So I need a count of households mailed and households that have responded, without putting HOUSEHOLD_ID on the respective fact tables.

Thanks.

 

[sunnyphoenixier]

You may want to try this.
1. Create a fact HOUSEHOLD_ID.
2. Create 2 metrics Count(HOUSEHOLD_ID), set the Count parameter of one metric to OUTBOUND and the other to RESPONSE table.
3. Put it on the report.

It should work.

Phoenixier

 

[chael]

My initial guess is that you can use HOUSEHOLD_ID as a relationship filter, and simply put the counts onto the report...and outer join the two metrics. What does the current report SQL look like?

 

[JRO061601]

Sorry, I was on hiatus from this client for a few weeks. I still have the problem.

sunnyphoenixier - this does not work. HOUSEHOLD_ID is not on the fact tables. specifying a factID under the count parameters using a column from the OUTBOUND or RESPONSE tables results in a SQL generation error. fact does not exist at specified level....

chael - i did toy with relationship filters, as well as fact level extensions, trying to force the join i want. no luck. the SQL looks like this right now:

create table ZZSP00 tablespace MRTMSTRTEMP1 as
select a12.CELL_CD CELL_CD,
count(distinct a11.HOUSEHOLD_MASTER_ID) WJXBFS1
from CUSTOMER_MASTER_PROFILE a11,
CAMPAIGN_HIERARCHY a12
group by a12.CELL_CD

create table ZZSP01 tablespace MRTMSTRTEMP1 as
select a12.CELL_CD CELL_CD,
sum(a11.SKU_COST_AMT) WJXBFS1
from CUSTOMER_MARKETING_RESPONSE a11,
CAMPAIGN_HIERARCHY a12
where a11.CAMPAIGN_HIERARCHY_ID = a12.CAMPAIGN_HIERARCHY_ID
and a11.THE_DATE in (To_Date('2004-01-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
group by a12.CELL_CD

select pa1.CELL_CD CELL_CD,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2
from ZZSP00 pa1,
ZZSP01 pa2
where pa1.CELL_CD = pa2.CELL_CD


drop table ZZSP00

drop table ZZSP01


i appreciate the help.

 

[entaroadun]

Easiest way:

Create a new attribute on logical table Outbound with attribute ID element form = 'ApplySimple("1",[Outbound Table Key Field])'. Using the [Outbound Table Key Field] in the expression will allow the attribute to bind to the table. Name it "Outbound Force Join".

Do the same with Response.

Create a metric: Count([Household Fact]) Distinct=True with a metric filter of [Outbound Force Join]@ID=1. Make sure that the report filter override option is turned off.

Repeat with Response.

The SQL you get will be:

COUNT(DISTINCT A.HOUSEHOLD_ID)
FROM CUST A, OUTBOUND B
WHERE A.CUST_ID = B.CUST_ID
  AND 1 = 1

The database should compile the 1=1 test away, so performance takes no hit.

 

[JRO061601]

I'm not quite getting it. Here is where I am:

- created attribute, custom mapping, where it sees the column CUST_ID only on the RESPONSE table (not sure if this matters, but CUST_ID is only part of the PK on table RESPONSE. for [OUTBOUND TABLE KEY FIELD], CUST_ID is all you need, and not the whole PK, right?)

- created filter

- created metric, unchecked box 'Remove Related Report Filter Elements' under the Advanced section of the metric condition

I get the following SQL:

select count(distinct a11.HOUSEHOLD_MASTER_ID) WJXBFS1
from CUSTOMER_MASTER_PROFILE a11,
CUSTOMER_MARKETING_RESPONSE a12
where 1 = 1


However, if I put the attribute in the report objects window, but not on the report, so that it is included in the SQL genertion, I get:

select 1 CUSTOMER_MASTER_PROFILE_ID,
count(distinct a11.HOUSEHOLD_MASTER_ID) WJXBFS1
from CUSTOMER_MASTER_PROFILE a11,
CUSTOMER_MARKETING_RESPONSE a12
where a11.CUSTOMER_MASTER_PROFILE_ID = a12.CUSTOMER_MASTER_PROFILE_ID
and 1 = 1
group by 1

It's currently running (LARGE data volume), but is this what you expected?

Thanks.

 

[entaroadun]

First of all, I assume that your Customer attribute is linked to CUST_ID on both tables already.

For the ApplySimple expression, use any column EXCEPT for CUST_ID. The best would be an unique ID field specific to the fact table itself; if not, use any column that is specific to the table (i.e. not mapped to any other table in any other object).

You don't need the attribute on your template.

 

[JRO061601]

Still not getting it.

The Customer attribute is already linked to column CUST_ID on both tables already.

I changed the ApplySimple expression to use a column that is only on the RESPONSE fact table.

I defined a filter as [Response Force Join]@ID=1 under the custom expression option. The tool recognized it as a attribute qualification and changed it to this. So now my metric reads:

Count([Household Master Id]) {~} <[Response Force Join = 1]>

where Household Master Id is a fact.

I still get a crossjoin. If I put [Response Force Join] in the report objects window, I get the somewhat correct SQL I posted previously.

 

[entaroadun]

It should work... The Customer logical table should have the Customer attribute and Household fact mapped to it. The Response table should have the Customer attribute and Response Force Join attribute mapped to it.

A Count metric on Household fact with a Response Force Join = 1 filter should generate:

SELECT  COUNT(DISTINCT A11.HOUSEHOLD_ID)
FROM    CUSTOMER A11
  JOIN  RESPONSE A12
WHERE   A11.CUST_ID = A12.CUST_ID
  AND   1 = 1

Did you update the schema after the attribute change? Are you sure the Remove Related Report Filter option is disabled on the metric?

Ignoring the ApplySimple part for a moment; do you see my logic? If you have a two tables, with a fact on one table, an attribute bound to both, and another attribute on the second table, and you have a metric that is aggregating the fact with a filter on the second attribute, you should get SQL that joins both tables and filters on the second attribute. The ApplySimple has nothing to do with it...

 

[entaroadun]

Try restarting your I-Server. Unless I'm going schizoid, this solution is correct. I know it is...

 

[JRO061601]

Thanks for following up. I'm off site today and probably tomorrow, but will pick it up again on Monday. I did update the schema, didn't re-boot the i-server though. I see your logic, but I'm not sure why the engine is ignoring the filter criteria. If I get a chance, I'll test it in the tutorial project to see if it's a problem with my environment.

 

[entaroadun]

It may be an issue with the FactID parameter on the COUNT function. Since you're counting a Fact, this should be set to nothing.

If that doesn't work, try using an ApplyAgg (i.e. ApplyAgg("COUNT(DISTINCT #0)",[Fact])) instead of COUNT. ApplyAgg doesn't have any of the complexities that COUNT has.

 

[JRO061601]

OK, I got it to work, but I had to unscientifically toy with it first. I tried your latest suggestions, but still no luck, so I checked the MSTR kbase and found this tech note:

TN5200-7X0-0090 - "The filter is ignored when running a report containing the count metric of an attribute that has a fact table as its lookup table in MicroStrategy 7.x"

Not exactly what I had going on, but still suggested the SQL generation engine isn't perfect along these lines. I did three things:

1) Created an attribute on the Response table that was simply "1". Used this in the metric filter to create the 1=1 join.

2) Used the attribute HOUSEHOLD_MASTER_ID in the Count metric instead of the fact.

3) Under the Count parameter, set the FACT ID to something that is only in the Response table.

#1 probably had nothing to do with it working. It was probably a combination of #2 & #3, but I'm a little lazy to do the permutation testing and find out exactly which change made the SQL engine happy.

select count(distinct a12.HOUSEHOLD_MASTER_ID) WJXBFS1
from CUSTOMER_MARKETING_RESPONSE a11,
CUSTOMER_MASTER_PROFILE a12
where a11.CUSTOMER_MASTER_PROFILE_ID = a12.CUSTOMER_MASTER_PROFILE_ID
and (1 = 1)

Unfortunately now I'm getting an Oracle error "parallel query server died unexpectedly" when using this metric in a larger report, but the DBA thinks it's an Oracle bug and not the SQL.

Thanks for all the help. I really appreciate it.