Problem with Query

[sekar211]

I have report with 1 attribute and 2 metrics.The attribute is Bill rep and the metrics are sum(adjamt) and count(trackingnum).I am using 3 tables

uv_adjustdetails->Sum(adjamt)
uv_payadjust
uv_recorddetails->count(trackingnum)

The sql is pretty simple and i am getting correct result on query analyzer:

select sum(a.fldtotaladjamt) as totalamt,count(distinct(c.fldtrackingnum))as trackingnum,c.fldbillreplastname
from uv_adjustdetails a
join uv_payadjust b
on a.fldrecnum=b.fldrecnum
join uv_recorddetail c
on b.fldtrackingnum=c.fldtrackingnum
group by c.fldbillreplastname

But MSTR generates 3 passes and gives some erroneous result.The MSTR SQL is as follows:

Pass0 - Duration: 0:00:00.23
select a13.fldBillRepUserID fldBillRepUserID,
sum(a11.fldTotalAdjAmt) WJXBFS1
into #ZZT3C010PHLMD000
from uv_AdjustDetails a11
join uv_PayAdjust a12
on (a11.fldRecNum = a12.fldRecNum)
join uv_RecordDetail a13
on (a12.fldTrackingNum = a13.fldTrackingNum)
group by a13.fldBillRepUserID

Pass1 - Duration: 0:00:08.45
select a11.fldBillRepUserID fldBillRepUserID,
count(distinct a11.fldTrackingNum) WJXBFS1
into #ZZT3C010PHLMD001
from uv_RecordDetail a11
group by a11.fldBillRepUserID

Pass2 - Duration: 0:00:03.76
select distinct pa1.fldBillRepUserID fldBillRepUserID,
(a11.fldBillRepLastName + ', ' + a11.fldBillRepFirstName) CustCol,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2
from #ZZT3C010PHLMD000 pa1
join #ZZT3C010PHLMD001 pa2
on (pa1.fldBillRepUserID = pa2.fldBillRepUserID)
join uv_RecordDetail a11
on (pa1.fldBillRepUserID = a11.fldBillRepUserID)

Pass3 - Duration: 0:00:00.09
drop table #ZZT3C010PHLMD000

Pass4 - Duration: 0:00:00.06
drop table #ZZT3C010PHLMD001

Do any one have any idea how this query can be simplified to get correct result set?

Thank you,

 

[entaroadun]

What numbers are you trying to get? I would argue that Microstrategy's SQL is right and your's is wrong.

If your data model is tight, the two SQL sets would return the same results.

There are two possible reasons for a discrepancy. The first is if fldrecnum and fldtrackingnum aren't unique on at least one of the tables that they come from. For instance, if there are two instances of fldrecnum on both of the tables, you will end up doubling the result. Normally, this would generate incorrect results that would at least be consistent between both sets of SQL. However, if both of your key fields exhibit this behavior, then the problem compounds; if fldrecnum is doubled and fldtrackingnum is doubled, then the final results of the first SQL will be quadrupled (2x2) while the final results of the second SQL will simply be doubled.

You should never join an attribute table to a fact table on a non-unique key. This is a fundamental assumption that Microstrategy makes about your data model. It's good design as well.

The second is if you're dropping records with your join. In the first SQL, you will end up dropping more records, since all tables are joined. In the second set, you will drop fewer records, since only two tables are joined at any given time.

In the second case (which I think is more likely for you), the Microstrategy SQL is more correct, since it drops fewer records.

Your attribute tables should NEVER drop records when joined. That is a fundamental assumption that Microstrategy makes about your data model. It's good data architecture as well.

In any case, there's nothing you can do to get the first SQL. OK, there's one thing you can do, but I wouldn't recommend it. It's hard to implement and to maintain. Better to have a good data model instead.

 

[sekar211]

Hi entaroadun,

I completely agree with what you say and thank you for giving some insight on how MSTR generates SQL.

But in this particular report, when i count(fldtrackingnum) i want to have the 2 joins
on a.fldrecnum=b.fldrecnum
join uv_recorddetail c
on b.fldtrackingnum=c.fldtrackingnum.

as per the MSTR SQL is concerned,while counting it doesn't do that join since the fldtracknum and Bill rep is present in the same table.so i get lot many count's than actually needed.

is there any way i can force the join even when counting? i hope u understand my question.

Once again
thank you.

 

[entaroadun]

I understand your question. I was merely pointing out that a clean data model wouldn't NEED to have the specialized SQL.

But since I'm not a puritannical jerk...

Modify the Count metric. Set the FactID parameter to the fldTotalAdjAmt fact. This should force MSTR to treat fldtrackingnum as if it came from the same logical tables that fldTotalAdjAmt came from, forcing your joins.