AVG from a lower level ratio

[JRO061601]

Can I make MSTR do this or something like it?

select y.LOAD_DATE, avg(x.ratio)
from (

select a.LOAD_DATE,b.PRS_LN_ID,(a.total_debt/b.ln_bpo_low_value) ratio
from PRS_TBL_LN_PAYMENT a, PRS_TBL_LN_BPO_DETAILS b
where a.PRS_LN_ID=b.PRS_LN_ID
and a.LOAD_DATE=b.LOAD_DATE

) x,
PRS_TBL_LOAD_PERIOD y
where x.LOAD_DATE=y.LOAD_DATE
group by y.LOAD_DATE

PRS_LN_ID & LOAD_DATE are the primary keys on both tables.

let me know if you need more info...

 

[entaroadun]

Wouldn't that be the same as:

select y.LOAD_DATE,
       avg(a.total_debt/b.ln_bpo_low_value)
from PRS_TBL_LN_PAYMENT a,
     PRS_TBL_LN_BPO_DETAILS b,
     PRS_TBL_LOAD_PERIOD y
where a.PRS_LN_ID=b.PRS_LN_ID
  and a.LOAD_DATE=b.LOAD_DATE
  and x.LOAD_DATE=y.LOAD_DATE
group by y.LOAD_DATE

 

[JRO061601]

There is a mis-typed table alias in there, but yes, it would be the same. I tried to define the metric as AVG(F1/F2), but when I put it on a report with LOAD_DATE and PRS_LN_ID, I get a 'fact does not exist at that level...' error. I don't know if it's the SQL engine or my model. Any thoughts?

I did find another workaround that is giving the correct data. I defined a metric M1 as SUM(F1)/SUM(F2) and checked off 'allow smart metric'. Then I defined a metric M2 as AVG(M1) with dynamic aggregation set to Average. On the template, I have M2, load date, and prs_loan_id, but I have removed prs_loan_id from the grid, so it is still in the SQL but not in the display.

Appears to work. Will need to wait until we get actual test data to see if it is a robust solution.

 

[entaroadun]

What are the fact entry levels for the two facts? I don't see why it shouldn't work...

Dynamic agg probably won't be robust, but it depends on your data size and result set size.

 

[JRO061601]

Good call. I knew there was something about this model still bothering me. The primary key for the tables PRS_TBL_LN_PAYMENT and PRS_TBL_LN_BPO_DETAILS are PRS_LN_ID and LOAD_DATE. The DBA separated them (and others) out into multiple tables so as to eliminate sparse data (e.g. not every payment has BPO details). However on each detail table there are a host of 1 to 1 characteristic attributes. There is a surrogate primary key on each detail table, and I have that as the child of all these attributes. So on the PAYMENT table the fact entry levels were PRS_LN_ID, LOAD_DATE, and PAYMENT_ID. And then on the BPO table the fact entry levels were PRS_LN_ID, LOAD_DATE, and BPO_ID. When I made PRS_LN_ID and LOAD_DATE a joint child of the surrogate IDs, I get consistent fact entry levels across the fact tables and the metric calculation works.

Now the question is do I remove the surrogate key from the parent/child relationship trees or do I keep it and make only it the parent of the joint children. I tend to think the engine won't care.

 

[entaroadun]

I would take the surrogate keys out. I haven't had much experience with joint children, but the limited experience I have had has been buggy.

Theoretically, though, the engine doesn't care.