Monitoring what’s going on

With an increasing maturity of Business Intelligence support for CRM processes, it is a fact that specific Business Intelligence solutions will be oriented much more on usage stastical
techniques like linear regression. In other words, once we understand how data is represented on a graph, then, for evaluating the collected values, the next step is to perform statistical analysis.

In this way, the discussion of this article focuses on how you can fit (in a Dashboard) a linear regression, which has many applications for analysing trend.

By the way, I’ll begin by clarifying that out of frustration I have found a way to achieve a regression line in Oracle Business Intelligence. As a result:

It’s well known that, in order to model the relationship between two variables, linear regression line has the equation of the form Y = b + mX, where where b (intercept) and m (slope) are continuous functions of x.

First of all, it is consedered to be necessary to give a background in statistics (oriented to the subject of this article). In this way, in order to calculate the slope (m) of the line we obtain:

m (slope) = SP /SS_x

Where the sum of products equals (SP) is: SP = (∑x.y) – ( (∑x) . (∑y) / n )

And for X, the sum of squares (SS_x) is: SS_x = (∑x²) – ( (∑x)² / n )

To sum up:

m (slope) = ( n . (∑x.y) – (∑x) . (∑y) ) / ( n . (∑x²) – (∑x)² )

In order to calculate the b (intercept) of the line we obtain:

b (intercept) = ( (∑y) – m . (∑x) ) / ( n )

After that, following the above steps , linear regression can be achieved on Oracle BI dashboard.In this way, derived calculations can be obtained in Oracle Answers using expression builder, such as:

(In this case: we obtain sum(x^2) and ANSI SQL functions used: MAX(RSUM(POWER(RCOUNT((“Variable X”.”Value”),2)))))

Once invoked, ANSI SQL functions return a single value each time. In this way, Oracle Answers provides 99 standard ANSI SQL functions that can be used to manipulate dates, strings and numbers, as well as retrieve system information.

Functions fall into the categories listed in: Aggregate, Running Aggregate, String, Math, Calendar/Date, Conversion, System and Expressions.