Count in OBIEE

COUNT

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax:

COUNT(expr)

Where:

expr is any expression.

COUNTDISTINCT

This function adds distinct processing to the COUNT function.

Syntax

COUNT(DISTINCT expr)

Where:

expr is any expression.

COUNT(*)

This function counts the number of rows.

Syntax

COUNT(*)

Example

For example, if a table named Facts contained 200,000 rows, the sample request would return the results shown:

SELECT COUNT(*) FROM Facts

Result:

200000

Averages in OBIEE

AVG

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of AVG is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(x) in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).

Syntax

AVG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

AVGDISTINCT

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

AVG(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

SUM in OBIEE

SUM

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

SUM(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The SUM function resets its values for each group in the query according to specific rules.

SUMDISTINCT

This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

SUM(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Repository GroupBy

GROUPBYCOLUMN

For use in setting up aggregate navigation. It specifies the logical columns that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data grouped by store and by month, specify the following syntax in the content filter (General tab of Logical Source dialog):

 GROUPBYCOLUMN(STORE, MONTH) 

The GROUPBYCOLUMN function is only for use in configuring a repository. You cannot use it to form SQL statements.

GROUPBYLEVEL

For use in setting up aggregate navigation. It specifies the dimension levels that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data at the store and month levels, and if you have defined dimensions (Geography and Customers) containing these levels, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH) 

The GROUPBYLEVEL function is only for use in configuring a repository. You cannot use it to form SQL statements.

OBIEE Random numbers

RAND()

OBIEE allows us to generate random numbers using the RAND() function.

This function works in Answers and generates psudeo random numbers in the range between 0 and 1.

This is a presentation services BI server only function and is not pushed back to the database.

All normal arithmetic and number based calculations can be performed on the results of this function.

Where are OBIEE Logfiles?

This applies to OBIEE 11G logfiles.

When it comes to hunting down the source of a problem in OBIEE it is a good idea to go through the logfiles to see where an error has occurred. There are a number of OBIEE logfiles, with each component generating it own set.

Assuming that You have installed OBIEE with a middleware home of D:\OBIEE11G then

• the Administration Server has logfiles in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\AdminServer\logs\AdminServer-diagnostic.log
• the Managed Server has logfiles in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\bi_server1\logs\bi_server1-diagnostic.log
• the BI Component logs are in: D:\OBIEE11G\instances\instance1\diagnositc\logs\*
• the OPMN logs are in: D:\OBIEE11G\instances\instance1\diagnositc\logs\OPMN\opmn\*
• the Enterprise Manager logs is in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\sysman\log\emoms.trc

It may be worth putting a shortcut to these files / directories on your desktop.

Also helpful is wintail which allows you to view a logfile and all the writes being made to it without the constraints of notepad etc...

If you have configured windows services for AdminServer and bi_server1 under beasvc, then hopefully you configured them with a logfile. The logfiles from the services help in guiding you to any problems as they are the record of what you would normally see when starting OBIEE manually.

Until next time.

Creating a windows service for a managed server in OBIEE 11.1.1.6

Create a text file with the contents as below, the assumption here is that you have a middleware home of D:\OBIEE11G - set yours appropriately and fill in the servername in <myserver>.

SETLOCAL
set DOMAIN_NAME=bi_server1
set USERDOMAIN_HOME=D:\OBIEE11G\user_projects\domains\bifoundation_domain
set SERVER_NAME=bi_server1
set PRODUCTION_MODE=true
set ADMIN_URL=http://<myserver>:7001
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
call "D:\OBIEE11G\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL

and name the file something like "installManServer.cmd"

Assuming that you have created an AdminServer service and set the Java Memory configuration then you are nearly home and dry.

Refer to http://docs.oracle.com/cd/E14571_01/web.1111/e13708/winservice.htm#i1188099

especially the part on delay (-delay:delay_milliseconds) and depend (-depend:"beasvc Adminserver_Adminserver")
for example -delay:300000 will delay for 5 minutes

And in your  WL_HOME\server\bin\installSvc.cmd script edit in the delay and/or depend parameters.
Also remember to change your logfile destination, you don't want Adminserver and managed service in the same logfile, trust me, it gets very confusing.

This should give you a final line in the I=installSvc.cmd that looks something like

"%WL_HOME%\server\bin\beasvc" -install
-svcname:"%DOMAIN_NAME%_%SERVER_NAME%"
-delay:300000
-depend:"beasvc Adminserver_Adminserver"
-javahome:"%JAVA_HOME%" -execdir:"%USERDOMAIN_HOME%"
-extrapath:"%WL_HOME%\server\bin" -password:"%WLS_PW%"
-log:"D:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\bi_server1\bi_server1-stdout.txt"
-cmdline:%CMDLINE%

Update (27 Feb 2014)
This also works for 11.1.1.7