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.

Setting a Sort Order Column

Have you ever noticed a property called Sort order column, located in the General tab of the Logical Column Properties Dialog Box? By default, this value is set to “None”, which is usually the desired value, but to demonstrate when this property comes in handy, let’s look at an example. Suppose your repository contains a column called “Month” and a column called “Month Number”. “Month” contains these values: Jan, Feb, Mar, Apr,…Dec. “Month Number” contains these corresponding values: 1,2,3,…12. If you have a Dashboard Prompt or a Report containing the “Month” column, the “Month” values will likely be sorted in this order (alphabetically as opposed to chronologically): Apr, Aug, Dec… Sept.

Sorting month names alphabetically is almost never the desired result; however, this sort order will be the default if the Sort order column for “Month” is set to None. You could try to correct this problem by using SQL in your Dashboard Prompt, or by adding additional sort columns to your reports, but there is a much easier way: simply set the Sort order column for the “Month” logical column in the repository. In this case, you would use the “Month Number” as the Sort order column. After reloading the metadata, the next time you use the “Month” column in a report or Dashboard prompt, OBIEE will automatically apply an ORDER BY Month Number clause in the SQL issued to the database, regardless of whether or not “Month Number” is included in the SELECT statement, or even in the Subject Area for that matter.

First published in :
http://www.biconsultinggroup.com/obiee-tips-and-tricks/setting-a-sort-order-column.html

Mixing Context Text Searching and OBIEE

Using EVALUATE and Context Searching

In OBIEE we are limited to using a LIKE operator in filters when we do text searches. There are more options available in the database like CONTEXT searching. This allows us to use a fuzzy matches operator where best matches are scored with the least likely matches receiving a zero score.

What we need is something where when we prompt for one or more keywords, results are returned even if the words are reversed, or misspelled.

This is not a tutorial in the intricacies of Oracle Text searching, which is a speciality in its own right.

As this functionality is not available nativly we need to get creative, and this solution, I'm sure there are more, relies on using the context searching capabilities of the database and the ability to call functions from OBIEE using EVALUATE. (more on EVALUATE here)

Setting up the example:
We will create a table that holds our text

CREATE TABLE text_test
(
   id     NUMBER,
   text   VARCHAR2 (1000)
);


SET DEFINE OFF;
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (1, 'cat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (2, 'dog');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (3, 'kat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (4, 'dug');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (5, 'dawg');
COMMIT;

Create a context index.

CREATE INDEX mytextindex ON text_test(text) INDEXTYPE IS CTXSYS.CONTEXT;

Create a function that scores our text against a search term.

CREATE OR REPLACE FUNCTION tsearch (id IN NUMBER, wrd IN VARCHAR2)
   RETURN NUMBER
IS
   retval   NUMBER := 0;
   lsql     VARCHAR2 (1000);
BEGIN
   BEGIN
      lsql   := 'select score(1) from text_test t where contains(t.text,''fuzzy(' || wrd || ',,,weight)'',1)>0 and t.id =' || id;
      EXECUTE IMMEDIATE lsql INTO retval;
   EXCEPTION
      WHEN OTHERS
      THEN
         retval   := 0;
   END;
   RETURN retval;
END;

More details on the FUZZY operator here.

Model the table through as a simple single table dimension and fact.

Lastly call this functionality from answers.

Create a new analysis and drag across the TEXT column twice, create a prompt that sets a presentation variable called p_text and edit the formula in the second TEXT column to be

evaluate('tsearch(%1,%2)',"TEXT_TEST"."ID" , '@{p_text}{''cat''}')

saving and running the report will give you something like

entering the search term cat will give you 

Happy Hacking

Making a Pivot Table Paginate

Wouldn't it be nice if you could have your pivot table split across several 'virtual' pages.

I must admit, it's not an everyday requirement and probably only applicable where your pivot table runs down and off the page.

So, how can we create a paging mechanism, as this is not native to pivot tables, and will only work under a closely defined set of circumstances.

In answers we create a new column, and this will take some fiddling around to get the optimal number, edit the column formula to something like

TRUNCATE((RCOUNT(1)-1)/12, 0) +1

and rename the column to something like page. This will give us an increasing number every 12 rows. I am assuming here that each year has 12 months and we are ordering our report year - month.

In the pivot table drag the 'page' column into the pages section. You should then see a drop down in the pages section that mimics the action of the next/previous buttons on tabular reports.

Now, not all reports - especially those that we will display as pivot tables align themselves so conveniently, where 'pages' can be grouped equally (12 months in a year). In that case you should think about a grouping using the BY clause.

Using a FILTER Function Instead of CASE Statements

Let’s face it; CASE statements are notorious for causing poor query performance. For certain kinds of simple CASE statements, there may be a more performance-friendly OBIEE alternative – the FILTER function. Like CASE statements, you can use the FILTER function to build a logical column expression. In the Expression Builder, this function can be found under Functions > Display Functions > Filter. Here is an example of how to use it:
Suppose you have two Logical Columns derived from the following expressions:

• Southern Region Units:
  CASE WHEN Paint.Markets.Region = ‘SOUTHERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END
• Western Region Units:
  CASE WHEN Paint.Markets.Region = ‘WESTERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END

Instead of using CASE statements, try using the following equivalent expressions involving the FILTER function:

• Southern Region Units:
  FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘SOUTHERN REGION’)
• Western Region Units:
  FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘WESTERN REGION’)

The SQL generated by the FILTER expressions will typically perform better than the SQL generated from the CASE statements. The FILTER SQL may look something like this (pretending all the columns come from the same physical table):

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year

The SQL generated from the CASE statements may look more like this:

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units ELSE 0),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units ELSE 0)
FROM physical_table
GROUP BY year

The major difference is that the FILTER SQL includes the criteria in the WHERE clause. In most cases, this means that the WHERE clause would run first, constraining the result set before the CASE statements are run, hence the improvement in performance.

Article From: http://www.biconsultinggroup.com/obiee-tips-and-tricks/using-a-filter-function-instead-of-case-statements.html

What's it all About OBIEE?

Follow this talk on OBIEE.

Some history around OBIEE and how it fits into the whole BI picture in the Oracle suite.

Alias vs Duplicate

A point of confusion sometimes arises over the difference between a Table Alias and a Duplicate Table.

The Alias Table

A Table Alias is created from a physical table, in the physical model of the repository, and any changes in the physical are immediately reflected in the alias. It is good practice to only use alias tables to build the Business model in the repository. You can rename your alias to be something more meaningful that will help other users understand it's purpose. Another use for alias tables is to resolve circular joins.

Creating an alias creates a copy of the table in metadata that will be referenced in SQL with that alias name. It will have its own ID, distinct from the parent table.

For example:
Parent Table Invoice -- id 1000
Alias Table CancelledInvoice -- id 2021

when the OBIEE engine writes the physical query it resolves the true table names, but looking at the query generated you will see:
.......
FROM INVOICE T1000 ,
INVOICE T2021 /*   CancelledInvoice  */
WHERE ...........

The Duplicate Table

Duplicate Tables on the other hand allow us to create the structure or template of a table, but at the database level you will find no physical structure. When creating an opaque view where the structure is the same as a table in the physical layer then we duplicate the table.

Using the Invoice example from above we can create a duplicate table in the physical layer. We will change the name of the table to CancelledInvoice and then the table type is set to "SELECT", and the details as "select * from Invoice where canc = 'Y'". This gives us an opaque view whic is available to be used but not deployed in the database.

10 principles of good design:

Back in the early 1980s, Dieter Rams (an industrial designer) was becoming increasingly concerned by the state of the world around him — ‘an impenetrable confusion of forms, colours and noises.’ Aware that he was a significant contributor to that world, he asked himself an important question: is my design good design?” In response to this question, he developed the following 10 principles of good design:

Good design is innovative.
The possibilities for innovation are not, by any means, exhausted. Technological development is always offering new opportunities for innovative design. But innovative design always develops in tandem with innovative technology, and can never be an end in itself.
Good design makes a product useful.
A product is bought to be used. It has to satisfy certain criteria, not only functional, but also psychological and aesthetic. Good design emphasises the usefulness of a product whilst disregarding anything that could possibly detract from it.
Good design is aesthetic.
The aesthetic quality of a product is integral to its usefulness because products we use every day affect our person and our well-being. But only well-executed objects can be beautiful.
Good design makes a product understandable.
It clarifies the product’s structure. Better still, it can make the product talk. At best, it is self-explanatory.
Good design is unobtrusive.
Products fulfilling a purpose are like tools. They are neither decorative objects nor works of art. Their design should therefore be both neutral and restrained, to leave room for the user’s self-expression.
Good design is honest.
It does not make a product more innovative, powerful or valuable than it really is. It does not attempt to manipulate the consumer with promises that cannot be kept.
Good design is long-lasting.
It avoids being fashionable and therefore never appears antiquated. Unlike fashionable design, it lasts many years — even in today’s throwaway society.
Good design is thorough, down to the last detail.
Nothing must be arbitrary or left to chance. Care and accuracy in the design process show respect towards the consumer.
Good design is environmentally-friendly.
Design makes an important contribution to the preservation of the environment. It conserves resources and minimises physical and visual pollution throughout the lifecycle of the product.
Good design is as little design as possible.
Less, but better — because it concentrates on the essential aspects, and the products are not burdened with non-essentials. Back to purity, back to simplicity.

Found in an article by Stephen Few from Visual Business Intelligence - really worth spreading around.

Can you honestly say that what you do addresses any of the points above?

Big Data

A fascinating article demonstrating the challenges of Big Data, what it is, and how companies are starting to approache the problem.

"Increasingly large amounts of data are collected and stored but to no discernible advantage. While the data is actually there, the in-house relational database tools are simply not equipped to deal with such a massive amount of data in anything like a realistic time frame. Instead, companies are turning to solutions that combine in-memory computing engines with the distributed parallel processing power of multiple servers."

But the problem as always is what to do with all the data. When you have torrents of data hitting your system, making an intelligent and accurate decisions starts to get challenging. Getting the data to tell the story you want or match your conclusions is easy enough, but letting the data tell the story is the challenge. The bigger challenge is accepting the results.

Some basic string manipulation in OBIEE.

Manipulating strings using SUBSTR

To extract parts of a string of characters like prefixes or file extensions (as an example) we involve a few of the base string functions available in OBIEE.

So what basic features do we need to know about strings, or what basic functionality do we need to manipulate strings.

SUBSTRING

To extract a substring from a string we need to be able to determine several things about the string.
1. Its overall length
2. If a character or sequence of characters is in the string.
3. the start position of a sequence of one or more characters within the string.

So how long is a string?

We can use the LENGTH function to return the number of characters in the string.

We have the LOCATE function to determine the position of a particular string within the string.

Used together they will allow us to extract the second word from the string 'one two three'.

Let's do a worked example

Start a new Analysis and select any column. We will change the contents as we go. Select Edit formula

and edit the column formula to contain 'one two three' (the single quotes are important as they tell OBIEE this is a string).

Add another column and this time we will edit the formula to show the length of the first column. Select the dropdown on the new column and along the bottom of the Column formula panel you will find a column button with a down chevron, click this and select 'one two three'. Highlight 'one two three' then using the f(...) button at the bottom left we will expand the string functions and select LENGTH.

LENGTH('one two three')

Similarly we will now add columns for finding the first occurence of space ' '.

LOCATE(' ', 'one two three')

and another for the next space

LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)
and one to extract the second word, (remember the word starts one character after the first Locate).

SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three')))

The SUBSTR has the following syntax SUBSTR(string FROM start FOR number). So we can fill in the blanks

• string is obviously 'one two three'
• start is LOCATE(' ', 'one two three')+1
• and number is the length of the word which we can calculate as (the second occurence of space) minus (the first occurence of space + 1)

and finally a test of the length of the second word, just make sure.

LENGTH(SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three'))))