Microstrategy Blog

I remember it well. Long night spent trying to figure out how best to line metrics up next to each other. Hours spend trying to figure out if it’s more work just creating you’re own logical table to solve that stupid cross join problem that won’t go away. I’m reticent to consult the MicroStrategy web site because it really does just feel like I’m copping out…

If you have a static warehouse that the designers are committed to, what else is your option?

Late night..

Addendum: You know sometimes I forget that SQL optimization is just that…optimization. I can some time putting together a quality query that’s going to return data on a specific products ships and returns. Let’s assume that each of these products have a distinct way of being tracked. I might throw together a hand done query that populates the list of these specific reference numbers from the ship table so that I can do a left join against the shipping data so I can see specific returns against the specific ships, and then aggregate those solutions, but did you see what I just did? I went through the trouble of matching thousands of records against each other…and for what? So that I can later aggregate the data with some sort of group by.

Enter SQL optimization. Solves the problem in a quicker way that I probably wouldn’t have crossed my mind since it works in a linear fashion.

1. Get ships.
2. Get returns.
3. Left join ships to returns based on unique ID.
4. Count records in relevant columns.
5. Group by necessary items.

But…why is the detail necessary?

MicroStrategy fixes.

(
select a12.date,
count(a11.sales_number) SALES_COUNT
from sales a11
where (a11.item_desc = ‘widget’)
group by
a11.date
) pa11

full outer join

(
select a11.date,
count(a11.rma_number) RMA_COUNT
from rma a11)
where (a11.item_desc = ‘widget’)
group by
a11.date
) pa12

on
(pa11.date = pa12.date)

Makes much more sense. Why join early when all you care about is the aggregate…join on the aggregate!

Duh.