I’m more than just emotional right now since I’ve finally got that bugger of my neck.
To those who’re interested in getting trend calculated in oracle — press page-down once.
It’s 2008 as my desktop calendar says and it’s “Targeted information to a broader range of users ” on top of the Cognos website. And there I am with simple requirement to calculate a sales trend line.
You know, there’s a Trend function in Excel. Since I don’t know when (it’s Office 2000 here and it’s already deep there). And, moreover, in PowerPlay I’d just clicked forecast and linear regression and voila.
But it’s single-scalable-mighty Cognos 8 platform you have to write in yourself.
With an example from Cognos Knowledge base article .
Just that in my case I had a DMR and it didn’t work “from the box”. And I even don’t know what is the “box”, since there is no such package as “Great Outdoors Company” in standard samples.
It turned out that I had to change aggregate properties of [value 1 for count] and therefore I doubt that this sample works on DMR’s as such.
Works now but it took me nearly 4 days to get it working right (mostly trying to understand how sets work with DMRs).
BI for masses? Easy analytics? Yeah…
After spending a day trying to do it in Cognos, I’ve decided to give it a try at a database level.
There’s a bunch of more than helpful analytical functions in Oracle:
See full list
And this wonderful article on using them to calculate m and b for whole table.
Then you can encapsulate the whole calculation in one statement, using sub queries. The problem is that if you have only one measurement then SXX=0 and you can’t divide by zero, that’s what case stands there for.
With Andy‘s masterly help the final variant turns into something like this:
with data_t as ( select 1 REG,1 X, 10 Y from sys.dual union all select 1 REG,2 X, 20 Y from sys.dual union all select 1 REG,3 X, 15 Y from sys.dual union all select 1 REG,4 X, 40 Y from sys.dual union all select 1 REG,5 X, 10 Y from sys.dual union all select 1 REG,6 X, 30 Y from sys.dual union all select 1 REG,7 X, NULL Y from sys.dual union all select 2 REG,1 X, 10 Y from sys.dual union all select 2 REG,2 X, 20 Y from sys.dual union all select 2 REG,3 X, 15 Y from sys.dual union all select 2 REG,4 X, 50 Y from sys.dual union all select 2 REG,5 X, 10 Y from sys.dual union all select 2 REG,6 X, 30 Y from sys.dual union all select 3 REG,1 X, 300 Y from sys.dual ) select REG,X, SUM( case when ((select REGR_SXX(Y,X) from data_t t1 where T.REG = t1.REG) = 0) then (Y) else X*(select REGR_SXY(Y,X)/REGR_SXX(Y,X) from data_t t1 where T.REG = t1.REG)+(select REGR_AVGY(Y,X) - REGR_SXY(Y,X)/REGR_SXX(Y,X)*REGR_AVGX(Y,X) from data_t t2 where T.REG=t2.REG ) end ) as trend from data_t T group by reg,X order by reg,X
Note the case — it slow as hell, you’d have to split the query into two + union to avoid this case. But it’s still rather slow, around 10 min on 1m row table.