Updating a userid in the Web Catalog with runcat.cmd

Have you ever run into this error with OBIEE 11g?

The GUID (########################) for user ABC already exists in the catalog with username XYX. If this user was renamed in the backend, please use the catalog user management tools to rename this user in the catalog. If the username was not renamed, the catalog has to be cleaned of this GUID.[[
File:securityimpl.cpp
Line:610

This error occurs when an LDAP user is renamed, typically preventing the user from logging into the application. Oracle provides

documentation for updating the GUID, but there is little if any documentation to update a userid. Here are the steps to updating a userid in the OBIEE 11g Web Catalog.

Steps:

Log into the Oracle BI host.
Navigate to the Catalog Manager path:
<MiddlewareHome>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager

Create a file called boot.txt in the same directory
Update the file with credentials able to use catalog manager
login=userid
pwd=password

Replace userid and password with those appropriate for your system (eg. weblogic....)

Save the file and close

Execute the runcat.cmd utility to update the userid
runcat.cmd -cmd renameAccounts -old OLDUSERID -new NEWUSERID -online http://IPAddress:PORT/analytics/saw.dll -credentials boot.txt

No output indicates that the user has been updated. Test by impersonating the user and logging into OBIEE.  You should now be able to login successfully under the new userid.

forums https://forums.oracle.com/forums/thread.jspa?threadID=2360823

OBIEE RPD Repository Report

Hidden away in a dusty corner of the OBIEE Admin tool is the Repository report.

This is pretty useful for a knowledge transfer. Click on tools -> utilities and then select "Repository Report".

This will give you a csv file detailing the information from presentation layer through the BMM and then the physical layer.

Columns are

Subject Area,
Presentation Table,
Presentation Column,
Description - Presentation Column,
Business Model,
Derived logical table,
Derived logical column,
Description - Derived Logical Column,
Expression,
Logical Table,
Logical Column,
Description - Logical Column,
Logical Table Source,
Expression,
Initialization Block,
Variable,
Database,
Physical Catalog,
Physical Schema,
Physical Table,
Alias,
Physical Column,
Description - Physical Column

I am currently experimenting with exporting this data and including it in management dashboards, tying it in with the catalog and usage tracking.

Where you derive a Fact is Important

When you define a calculated measure column using a logical column, OBIEE performs the aggregation for the columns used in the calculation first, before applying the calculation.

So for example, if a calculated measure (Total Revenue) is derived from the product of two LOGICAL columns (Unit Price x Number of Items), then OBIEE will create SQL such that the Unit Price and Number of Items columns are aggregated first, and then the multiplication.

However, if the calculated measure is derived from the product of two PHYSICAL columns, then the multiplication will be executed first, and then the aggregation after.

This can make a difference in some cases, so know what you want to accomplish and then carefully choose the appropriate column type for your derived column.

OBIEE Date Expressions

1)  First Day of the Previous Year
-------------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.


2) First Day of the Current Year
----------------------------------
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 


This calculation returns the first day of the year by deducting one less than the total number of days in the year.


3) First Day of the Next Year
---------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.


4) First Day of the Previous Month
-----------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.


5) First Day of the Current Month
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.


6) First Day of the Next Month
-------------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.


7) First Day of Current Quarter
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.


8) Last Day of the Previous Month
--------------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.


9) Last Day of Current Month
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.


10) Last Day of the Next Month
-----------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.


11) Last Day of Previous Year
------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.


12) Last Day of Current Year
------------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.


13) Last Day of the Next Year
----------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.


14) Last Day of Current Quarter
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.


15) Number of days between First Day of Year and Last Day of Current Month
------------------------------------------------------------------------------------------------------------------
 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) 


For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

Using VALUEOF in OBIEE

VALUEOF

Use the VALUEOF function to reference the value of a repository variable. Repository variables are defined using the Administration Tool. You can use the VALUEOF function both in Expression Builder in the Administration Tool, and when you edit the SQL statements for an analysis from the Advanced tab of the Analysis editor in Answers.

Syntax

Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name. Note that variable names are case sensitive. For example, to use the value of a static repository variables namedprime_begin and prime_end:

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

You must refer to a dynamic repository variable by its fully qualified name. If you are using a dynamic repository variable, the names of the initialization block and the repository variable must be enclosed in double quotes ( " ), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in an initialization block named Region Security, use the following syntax:

SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

The names of session variables must be preceded by NQ_SESSION, separated by a period, and contained within parentheses, including the NQ_SESSION portion. If the variable name contains a space, enclose the name in double quotes ( " ). For example, to use the value of a session variable named REGION, use the following syntax in Expression Builder or a filter:

"SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)

Although using initialization block names with session variables (just as with other repository variables) may work, you should use NQ_SESSION. NQ_SESSION acts like a wildcard that matches all initialization block names. This lets you change the structure of the initialization blocks in a localized manner without impacting requests.



Reference : otn.oracle.com  

CASE statements in OBIEE

Conditional Expressions

Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions

include:

·         CASE (Switch)

·         CASE (If)

CASE (Switch)

This form of the CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

If expr1 matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.

Note: In a CASE statement, AND has precedence over OR.

Syntax

CASE expr1
     WHEN expr2 THEN expr3
     {WHEN expr... THEN expr...}
     ELSE expr
END

Where:

CASE starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.
THEN specifies the value to assign if the corresponding WHEN expression is satisfied.
ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.
END ends the CASE statement.

Example

CASE Score-par
  WHEN -5 THEN 'Birdie on Par 6'
  WHEN -4 THEN 'Must be Tiger'
  WHEN -3 THEN 'Three under par'
  WHEN -2 THEN 'Two under par'
  WHEN -1 THEN 'Birdie'
  WHEN 0 THEN 'Par'
  WHEN 1 THEN 'Bogey'
  WHEN 2 THEN 'Double Bogey'
  ELSE 'Triple Bogey or Worse'
END

In this example, the WHEN statements must reflect a strict equality. For example, a WHEN condition of WHEN < 0 THEN 'Under Par' is illegal because comparison operators are not allowed.

CASE (If)

This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.

If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is

specified, the system automatically adds an ELSE NULL.

Note: In a CASE statement, AND has precedence over OR.

Syntax

CASE 
     WHEN request_condition1 THEN expr1
     {WHEN request_condition2 THEN expr2}
     {WHEN request_condition... THEN expr...}
     ELSE expr
END 

Where:

CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.
THEN specifies the value to assign if the corresponding WHEN expression is satisfied.
ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE
  WHEN score-par < 0 THEN 'Under Par'
  WHEN score-par = 0 THEN 'Par'
  WHEN score-par = 1 THEN 'Bogie'
  WHEN score-par = 2 THEN 'Double Bogey'
  ELSE 'Triple Bogey or Worse'
END

Unlike the Switch form of the CASE statement, the WHEN statements in the If form allow comparison operators. For example, a WHEN

condition of WHEN < 0 THEN 'Under Par' is legal.

NTILE function in OBIEE

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks.

In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Syntax

NTILE(numExpr, numTiles)

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

BOTTOMN and TOPN

BOTTOMN

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The

BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BOTTOMN(numExpr, integer)

Where:
numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

TOPN

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax

TOPN(numExpr, integer)

Where:
numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

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

Percentile and Rank

PERCENTILE

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

The percentile is calculated based on the values in the result set.

Syntax

PERCENTILE(numExpr)

Where:
numExpr is any expression that evaluates to a numeric value.

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

RANK

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax

RANK(numExpr)

Where:
numExpr is any expression that evaluates to a numeric value.

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

MAX, MEDIAN and MIN Functions

MAX

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MAX(numExpr)

Where:
numExpr is any expression that evaluates to a numeric value.

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

MEDIAN

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When

there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax

MEDIAN(numExpr)

Where:
numExpr is any expression that evaluates to a numeric value.

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

MIN

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MIN(numExpr)

Where:
numExpr is any expression that evaluates to a numeric value.

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

LAST Function in OBIEE

LAST

This function selects the last returned value of the expression. For example, the LAST function can calculate the value of the last day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The LAST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the LAST function returns the last day in each level.

You should not use the LAST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

LAST(expr)

Where:

expr is any expression that references at least one measure column.

Example

LAST(sales)

First Function in OBIEE

FIRST

This function selects the first returned value of the expression argument. For example, the FIRST function can calculate the value of the first day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The FIRST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST function returns the first day in each level.

You should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

FIRST(expr)

Where:

expr is any expression that references at least one measure column.

Example

FIRST(sales)

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

OBIEE 11.1.1.5.4 bundle patch is now available for download

Oracle Business Intelligence Enterprise Edition - OBIEE 11.1.1.5.4 (a.k.a 11.1.1.5.0BP4) bundle patch is now available for download from My Oracle Support (https://support.oracle.com). The OBIEE 11.1.1.5.4 bundle patch includes 40 bug fixes.

It is cumulative, so it includes everything in 11.1.1.5.0PS1, 11.1.1.5.0BP2 and 11.1.1.5.0BP3.

Please note that this release is only recommended for BI customers.

This patch is available for these supported platforms:

Microsoft Windows (32-bit)

Linux x86 (32-bit)

Microsoft Windows x64 (64-bit)

Linux x86-64 (64-bit)

Oracle Solaris on SPARC (64-bit)

Oracle Solaris on x86-64 (64-bit)

IBM AIX PPC (64-bit)

HPUX- IA (64-bit)

Getting to Grips With WLST

The other day I decided to challenge myself into starting the dashboard Server using the command line using WLST, and what follows is an edited version of what I did.

Start the managed service using WLST

Start the managed server using the following commands, replacing servername with the servername on which you are running:

cd <MW_HOME>\wlserver_10.3\server\bin

run setWLSEnv.cmd

cd  <MW_HOME> \wlserver_10.3\common\bin

run wlst.cmd

connect(‘weblogic’,’<weblogicpassword>’,’t3://<servername>:7001’)

state(‘bi_server1’,’Server’)

• You should see SUSPENDED, or STOPPED

start(‘Server’,’bi_server1’,’<servername>’,’80’)

exit()

this will bring the dashboard 

Modelling Many-to-Many relationships:

The following provides a summary of some design options, which the development team should review and check whether any of the snowflaked models are mapped as a many to many relationships and can be re-design using one of these approaches:

Technique #1: Select a Primary

Although not a technical solution, the best way to solve the M:M problem is to eliminate it.  By selecting one of the many dimensional records that are associated with a fact, the entire problem can be avoided.  In the OLTP, Primaries are used throughout the model, which are carried over and used in the Oracle BI model.  If it is at all possible to identify a primary, and the use of the primary is acceptable to the user community, then it is recommended to use this technique.

Technique #2: Direct Modeling into the Dimension

A straightforward technique is where the table that serves as the intersection table is modeled into a lower level in the Dimension.  The specifics of this technique are similar to those outlined in Solution B of the No direct physical link between a base Dimension and a Fact table section above.

Note that over-counting will occur when performing the many-to-many join.

Technique #3a: Use of a Bridge Table

Instead of modeling the relationship table into a new lower level in the dimension as in Technique #2, the relationship table can become a separate logical table that servers as the Bridge between the dimension and the facts.  Create a new Logical table with the M:M relationship table as the source, mark the logical table as a Bridge table, and adjust the Business model to show the relationship of Facts:Bridge as 1:M and Bridge:Dimension as M:1.  The indication that the Logical Table is a Bridge table is merely an indicator to Analytics that the table is not a Fact table, which it assumes to be any lowest-level table in the data model.

Note that over-counting will occur when performing the many-to-many join

Technique #3b: Use a Weighted Bridge Table

Similar to Technique #3a, this technique is the classic Kimball approach, where the Bridge table employs weighting factors to prorate a total value over multiple records.  For example, if there is one Opportunity worth $1,000,000 and there are two Employees associated with it, the bridge table might contain a record for each with a weighting factor of 0.5.  In this way, each employee will be associated with 0.5 of the whole amount of $1,000,000, or $500,000.  If it is determined that Employee A should receive 75% of the credit, then the weighting factors would be stored as 0.75 and 0.25, which would give Employee A 75 of the total or $750,000.

It is important to note that the weighting factors must all add up to 1 (One), as they are effectively percentages of a whole.  Additional ETL effort will be required to complete this solution.

This technique eliminates over-counting, but may be difficult to implement if users are not comfortable prorating a value over several records.

Technique #4: Use Level Based Measures

As an enhancement to Techniques 2 and 3, the use of level based measures can help prevent the over counting problem associated with each.  When a metric or measure is explicitly bound to a specific level in a dimension, it is indicating that the metric will be viewed at that level. If the metrics in a fact table are to be viewed by a Dimension with which it has an M:M relationship, those metrics can be set to a level in the dimension, thereby forcing that the records be broken out across that dimension.  By forcing a breakout of rows (one fact row for each dimensional row), aggregation is prevented, and therefore over counting will not occur.

As an example, suppose there is an M:M between Employee and Fact_Opty_Revenue.  The data in the tables indicate that Tom, Larry and Bill are all linked to an Opportunity worth $9 million.  The user makes a report that asks for the Opportunity Type and the total Potential Opportunity Revenue.  Without level setting the metrics on the fact table, a report that does not include the employee dimension will over count, as each of the three dim records will be brought into the query and aggregated into one:

Opportunity Type	Potential Opportunity Revenue
Software Sales	$27,000,000

By level setting the Revenue metrics to the Employee level in the Employee Dimension, this same report will return the following:

Opportunity Type	Potential Opportunity Revenue
Software Sales	$9,000,000
Software Sales	$9,000,000
Software Sales	$9,000,000

Although not intuitively obvious as to the cause of the breakout to the end user, the over counting scenario is prevented.  When the user adds the Employee to the report, the breakout becomes clearer:

Opportunity Type	Employee	Potential Opportunity Revenue
Software Sales	Larry	$9,000,000
Software Sales	Tom	$9,000,000
Software Sales	Bill	$9,000,000

Technique #5: Lower the Fact Table

The most complicated and involved solution is to lower the level of the fact table, and create a 1:M between the Dimensions and the Facts.  This involves a business rule to split up the metrics and spread them over all possible dimensional records.  In the example above, the simplest spread would be to assign Larry, Tom and Bill each 1/3 of the total amount of $9,000,000, or $3,000,000.  Thus, a report that does not break out by employee still totals to the correct $9,000,000.  Note that this would require three records in the fact table instead of one, hence the concept of lowering the level of detail in the fact.

When modeling M:M bridge tables, ensure that the requirements for reporting against the measures are reviewed.

Also refer to “Siebel Analytics Repository Design Best Practices [ID 477431.1]” on Oracle My Oracle Support which provides details on modelling snowflake relationships in OBIEE.

Only Measure columns in the Facts

OBIEE Repository Best Practice

Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned

When executing a query that includes columns which are not measures (defined with aggregate) and generates a GROUP BY or ORDER BY, this will cause the Oracle BI Server to generate an error when the data returned is not at the lowest level of granularity (such as dimensional data queried on the fact source).

Recommendation

Generally, dimensional data is placed on dimension tables to prevent the fact table from increasing in size, and to ensure that the queries executed on the fact tables can roll up the data appropriately.

It is important to keep in mind that the fact table will contain a large number of records compared to the dimension tables.

Therefore storing dimension data on these tables will result in the tables being increased in size unnecessarily, thus potentially impacting the performance of queries executed against it. It could potentially lead to the grain of the fact data to change if/when further dimensional data is added at a future stage.

Note however, there are cases where it is more efficient to place the dimensional data on the fact tables where the data remains the same grain regardless of the queries performed, an example of this is the Activity fact table in the standard DW schema, which has data at the lowest grain, and by extracting the dimensional data out into a separate dimension table would not reduce the dataset held in the dimension table in comparison to the fact table.

If the reasons for placing the data on the fact table is because the data elements are unique to each record in the fact source (transactional data), such as order number. The Oracle BI RPD logical model should be built in a specific way to ensure that no potential errors occur. The following provides details on how this should be configured:

In this particular case, if there is no requirement to define an aggregate for this column, then the columns should be configured to be used as dimensional data and not fact data. This can be achieved by configuring the base physical fact table as a logical table source to both a fact and dimension logical table, whereby the fact table consists of all the aggregated columns and the dimension table will contain all other columns.

Guided Navigation

Guided Navigation:

is a way of making OBIEE dashboards and reports more dynamic, personal and relevant to the current user viewing the dashboard.

For example - specific regions of the dashboard can be made to 'only' appear when there is important information to report.

To do this you create a condition based on either an Analysis or KPI, where the result is boolean (true/false).
i.e. a count > 20 or no rows returned.

The conditions can be used to determine:

• If agents should run and /or to who they should deliver their contents.
• If action links are displayed
• If reports are shown

For example if the sales department has a target to meet, an anlysis could be built to only show a report if sales targets are not met and also send out an email report via the agent showing underperforming sales. If targets are met, then no email is sent, or a 'nice' email is sent out.

There are two types of condition - Named and Inline.

Inline Conditions:

If you only ever intend to use a condition once - and this condition is only for dashboard reporting, you can create an inline condition. This is created within the dashboard page when you build and design the layout and is saved as part of the page.

Named Conditions:

A Named Condition is created from the New dropdown or from the create --> more panel on the home page. It is as its name suggests, named, and therefore saved. This means that the same condition can be used in many places to guide action links, raise alerts or expose parts of the dashboard.

A judicious use of guided navigation using conditions can improve the entire BI experience for your users, but as always, too much of a good thing will confuse rather than clarify the story you tell using the dashboard as your canvas.

ODBC or OCI?

How should you set your connection pool - OCI or ODBC?

When possible, configure your connection pools to use a “native driver” to connect to your physical databases.  For example, use OCI for connecting to an Oracle database rather than ODBC

The native drivers understand the database better than ODBC. ODBC is the lowest common denominator and uses "simple" statements to select data and then passes the load to the business intelligence server for aggregation and transformation.

The native drivers allow the load to passed from the application server to the database, and the database then returns only the result required.

A simple example would be to ask for a sales total.

Using ODBC

In ODBC the query pushed to the database would be to select the relevant sales. The sales then are returned to the BI server and the total is calculated and sent on to the dashboard. YOu may even find that all the sales records are returned to the BI server, then filtered for the relevant records, before any aggregation takes place.

Whereas:

Using OCI

Using a native driver helps the OBI server understand what the database, where the data resides, is capable of. Just how much complexity can be pushed down and passed to the database, relieving the load on the OBI server.

You will find filters, aggregations and complex calculations appearing in the physical queries being passed down with the native drivers (eg. OCI for Oracle). I have examined some queries in the process of 'debugging' various parts of the repository and been surprised with new ways and functionality in SQL that the OBIEE server passes down. I have been writing SQL queries for a good many years and OBIEE still finds ways to expand my knowledge.

No more Complex Joins please!

Avoid Physical Layer Complex Joins.

While it is perfectly acceptable to use complex joins in the physical layer, this can (and usually does) result in poor query performance when OBIEE generates the query against the database. Far better to use the foreign key joins that should be implicit in the star/snowflakes of your data model.

For example where the complex join involves a between condition, then you are looking at index range scans on the database, repeat this several million times when you need an average on a large dataset, and you are potentially going to be looking at a spinning clock symbol for a long time.

Any work that has to be done by the BI server costs time. Much as you tune a query in the database to avoid making function calls in the 'WHERE' clause, so you should aim to do the same in your physical joins. - No more BICOLUMN = TRUNC(DATECOLUIMN) or BICOLUMN = SUBSTR(CHARCOLUMN,1,4).

Lack of Data Security Filters

Area of Impact is Data Integrity and Performance

Data Security Filter Consequence

A lack of data visibility filters will enable all users who have access to specific subject areas to have access to all records, regardless of any visibility restrictions which may be implemented within the source applications. It can potentially impact the overall performance of such queries, as the user may return a larger dataset than what they are permitted in seeing.

What should be done :
The following provides a high level summary of the functionality provided by data security filters:

When a user logs in, the following two processes should take place:
Authentication
• Verifies the user has the right to log in to the system
Authorization
• The process of defining which “Application Roles” should be assigned to the user once they have successfully authenticated

Once the above two processes have taken place, there are three OBIEE components that apply “security policies” based up the user’s Application Roles:
• Presentation Catalogue > Dashboard/report access and front-end privileges
• BI Repository > RPD metadata access and data-visibility
• FMW Policy Store > Access to BI Server (e.g. impersonation) and BI Publisher features

The default Roles has the following privileges in Oracle BI EE 11g:
BIAdministrator

• Manage BI Repository (RPD)
• Administer BI Publisher

BIAuthor

• Privileges defined in BI Presentation Catalog
• BI Publisher Develop / Design / Schedule Reports

BIConsumer

• Privileges defined in BI Presentation Catalog
• BI Publisher Excel / On-line Report Analyzer

Data filters can then be set within the OBIEE RPD via the Application Roles and Users through the “Manage > Identity” menu option.

The development team should use the above details provided and check whether similar security can be defined for the existing sources which are loaded into the data warehouse.

Further details on this topic can also be found in the following sections:
• Oracle® Fusion Middleware > Security Guide for Oracle Business Intelligence Enterprise Edition > 11g Release 1 (11.1.1)

Set a Dashboard Landing Page for all Users

If you want all your default and new users to go to a specific page when they login, and they have not set a specific page or their 'home' page is set to default then they can be directed via the PORTALPATH setting.

PORTALPATH

The PORTALPATH setting will only work if the user has not set a new default home page in their account settings.

You will need to make a few changes in the repository to enable this setting.

Open the repository and go to manage --> variables

Navigate the tree displayed and open up Session and then Variables and then System
You will create a new Session Variable here

Right click on the right hand side and select the new PORTALPATH or create a new variable PORTALPATH if no option is presented.

In the Default Initialiser - give the path to the required dashboard. eg /shared/samplesales/_portal/landingpage

Close the repository and those users without a default page in their account setting will instead land on the new landing page.

Patching support for OBIEE 10.1.3.4.1 ends on 30th September

Patching support for OBIEE 10.1.3.4.1 ends on 30th September 2012.

After September, there will be no more one-off patches available for OBIEE 10.1.3.4.1 or lower versions. If you wish to continue receiving one-off patches, should the situation arise, please apply the OBIEE 10.1.3.4.2 patch set.

Calendar Date and Time Functions

Calendar Date/Time Functions

The calendar date/time functions manipulate data of the data types DATE and DATETIME based on a calendar year. You must select these functions together with another column; they cannot be selected alone.

Functions

Current_Date
Current_Time
Current_TimeStamp
Day_Of_Quarter
DayName
DayOfMonth
DayOfWeek
DayOfYear
Hour
Minute
Month
Month_Of_Quarter
MonthName
Now
Quarter_Of_Year
Second
TimestampAdd
TimestampDiff
Week_Of_Quarter
Week_Of_Year
Year

---

CURRENT_DATE

Returns the current date. The date is determined by the system in which the Oracle BI Server is running.

Syntax

Current_Date

---

CURRENT_TIME

Returns the current time. The time is determined by the system in which the Oracle BI Server is running.
Note: The Analytics Server does not cache queries that contain this function.

Syntax

Current_Time(integer)

Where:

integer

Any integer that represents the number of digits of precision with which to display the fractional second.

---

CURRENT_TIMESTAMP

Returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.
Note: The Oracle BI Server does not cache queries that contain this function.

Syntax

Current_TimeStamp(integer)

Where:

integer

Any integer that represents the number of digits of precision with which to display the fractional second.

---

DAY_OF_QUARTER

Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

Syntax

Day_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYNAME

Returns the name of the day for a specified date.

Syntax

DayName(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFMONTH

Returns the number corresponding to the day of the month for a specified date.

Syntax

DayOfMonth(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFWEEK

Returns a number between 1 and 7 corresponding to the day of the week, Sunday through Saturday, for a specified date. For example, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.

Syntax

DayOfWeek(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFYEAR

Returns the number (between 1 and 366) corresponding to the day of the year for a specified date.

Syntax

DayOfYear(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

HOUR

Returns the number (between 0 and 23) corresponding to the hour for a specified time. For example, 0 corresponds to 12 A.M. and 23 corresponds to 11 P.M.

Syntax

Hour(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

MINUTE

Returns the number (between 0 and 59) corresponding to the minute for a specified time.

Syntax

Minute(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

MONTH

Returns a number (between 1 and 12) corresponding to the month for a specified date.

Syntax

Month(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

MONTH_OF_QUARTER

Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date.

Syntax

Month_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

MONTHNAME

Returns the name of the month for a specified date.

Syntax

MonthName(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

NOW

Returns the current timestamp. This function is equivalent to the function current_timestamp .

Syntax

Now()

---

QUARTER_OF_YEAR

Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.

Syntax

Quarter_Of_Year(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

SECOND

Returns the number (between 0 and 59) corresponding to the seconds for a specified time.

Syntax

Second(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

TIMESTAMPADD

Adds a specified number of intervals to a specified timestamp, and returns a single timestamp. Passing a null intExpr or timeExpr to this function results in the return of a null value.
In the simplest scenario, this function simply adds the specified integer value to the appropriate component of the timestamp, based on the interval. Adding a week translates to adding seven days, and adding a quarter translates to adding three months. A negative integer value results in a subtraction (such as going back in time).
An overflow of the specified component (for example, more than 60 seconds, 24 hours, twelve months, and so on) necessitates adding an appropriate amount to the next component. For example, when adding to the day component of a timestamp, this function makes sure that overflow takes into account the number of days in a particular month (including leap years). Similar measures are used to make sure that adding a month component results in the appropriate number of days for the day component (such as adding a month to '2010-05-31' does not result in '2010-06-31' because June does not have 31 days). The function also deals with the month and day components in a similar fashion when adding or subtracting year components.

Syntax

TimestampAdd(interval, intExpr, timestamp)

Where:

interval

The specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

intExpr

Any expression that evaluates to an integer value.

timestamp

Any valid timestamp.

Examples

Select {TimestampAdd(SQL_TSI_DAY, 3, TIMESTAMP'2000-02-27 14:30:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 3 days are added to '2000-02-27 14:30:00'. Since February, 2000 is a leap year, the query returns a single timestamp of '2000-03-01 14:30:00'.

Select {TimestampAdd(SQL_TSI_MONTH, 7, TIMESTAMP'1999-07-31 00:00:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 7 months are added to '1999-07-31 00:00:00'. The query returns a single timestamp of '2000-02-29 00:00:00'. Notice the reduction of day component to 29 because of the shorter month of February.

Select {TimestampAdd(SQL_TSI_MINUTE, 25, TIMESTAMP'2000-07-31 23:35:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 25 minutes are added to '2000-07-31 23:35:00'. The query returns a single timestamp of '2000-08-01 00:00:00'. Notice the propagation of overflow through the month component.

---

TIMESTAMPDIFF

Returns the total number of specified intervals between two timestamps. Passing a null timestamp to this function results in a null return value.
This function first determines the timestamp component that corresponds to the specified interval parameter, and then looks at the higher order components of both timestamps to calculate the total number of intervals for each timestamp. For example, if the specified interval corresponds to the month component, the function calculates the total number of months for each timestamp by adding the month component and twelve times the year component. Then the function subtracts the first timestamp's total number of intervals from the second timestamp's total number of intervals.
The TimestampDiff function rounds up to the next integer whenever fractional intervals represent a crossing of an interval boundary. For example, the difference in years between '1999-12-31' and '2000-01-01' is 1 year because the fractional year represents a crossing from one year to the next (such as 1999 to 2000). By contrast, the difference between '1999-01-01' and '1999-12-31' is zero years because the fractional interval falls entirely within a particular year (such as 1999). Microsoft's SQL Server exhibits the same rounding behavior, but IBM's DB2 does not; it always rounds down. Oracle does not implement a generalized timestamp difference function.
When calculating the difference in weeks, the function calculates the difference in days and divides by seven before rounding. Additionally, the function takes into account how the administrator has configured the start of a new week in the NQSConfig.ini file. For example, with Sunday as the start of the week, the difference in weeks between '2000-07-06' (a Thursday) and '2000-07-10' (the following Monday) results in a value of 1 week. With Tuesday as the start of the week, however, the function would return zero weeks since the fractional interval falls entirely within a particular week. When calculating the difference in quarters, the function calculates the difference in months and divides by three before rounding.
Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functions to Microsoft's SQL Server and ODBC databases by default. While Oracle BI Server can also push to IBM's DB2, the features table is turned off by default due to DB2's simplistic semantics. (IBM's DB2 provides a generalized timestamp difference function, TIMESTAMPDIFF, but it simplifies the calculation by always assuming a 365-day year, 52-week year, and 30-day month.) The features table is also turned off by default for Oracle, since Oracle databases do not fully support these functions.

Syntax

TimestampDiff(interval, timestamp1, timestamp2)

Where:

interval

The specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

timestamp1

Any valid timestamp.

timestamp2

Any valid timestamp.

Examples

Select {TimestampDiff(SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00', TIMESTAMP'2000-04-01 14:24:00')}
From Employee where employeeid = 2;

In the above example, the query asks for a difference in days between timestamps '1998-07-31 23:35:00' and '2000-04-01 14:24:00'. It returns a value of 610. Notice that the leap year in 2000 results in an additional day.

---

WEEK_OF_QUARTER

Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.

Syntax

Week_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

WEEK_OF_YEAR

Returns a number (between 1 and 53) corresponding to the week of the year for the specified date.

Syntax

Week_Of_Year(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

YEAR

Returns the year for the specified date.

Syntax

Year(dateExpr)

Where:

dateExpr Any expression that evaluates to a date.