This article shows how to integrate OBIEE with Teradata Temporal Tables. It details how to set up OBIEE for "as-is" and "as-was" type analysis. It is a cook book style write-up and assumes the reader knows OBIEE (Oracle Business Intelligence Enterprise Edition) and assumes that the reader knows what they want to do with temporal. It does not go into detailed analysis of different use cases for temporal tables.
Teradata Database 13.10, released in September of 2010, included a number of fully integrated, in-database data attributes, qualifiers, and predicates extremely useful for automating the management of time-varying data. Also included were a number of powerful functions to enable native time series analysis and comparison of periods of time.
By optimizing temporal database capabilities for a massively parallel processing platform, Teradata 13.10 (and subsequent releases) allows organizations to gather, manage, and analyze “time varying” data more effectively.
Teradata temporal capabilities rely on two period data type attributes, which are TRANSACTIONTIME and VALIDTIME. In a sense, these are the keys to data warehouse time travel. They allow the reproduction of a report that ran previously, such as six months ago, even though numerous changes have been made to the underlying data.
This ability can be vital for example when responding to inquiries from regulators who want to know what information organizations had and when they had it.
Temporal tables can be configured as “regular” OBIEE source tables. However, the data returned for queries will only reflect the current transaction time or valid time. In order to return data for a specific point in time, regular OBIEE source tables cannot be used. OBIEE requires special configuration to take advantage of a table’s temporal capabilities. There are two methods available. The first method takes a normally defined OBIEE table and converts it to a “select” type table (also known as an opaque view). A SQL select statement is then defined for the temporal table and can then include temporal keywords such as “as of”. The second method takes a normally defined OBIEE table and changes it to use a dynamic table name. The dynamic table name will use a variable that includes the temporal keywords such as “as of”.
The following is an example of an OBIEE dashboard that has a temporal query.
“Channel” is defined in Teradata as a temporal table with a valid time column. The dashboard above queries the channel table with an “as of” date based on the prompt for “calendar_date”. The temporal columns for valid start date and valid end date are exposed on the dashboard.
The first step is to define a session variable that will be used to determine and store the “as of” date for the temporal query. The session variable should be set so that any user can set the value. The default Initializer should be set to “current_date” (without the quotes). The screen shot below shows the properties for the “as_of_date” variable.
The next step is to define the temporal table in the Teradata database. Below is the DDL for the channel example table, the vt column (underlined) is the temporal column:
CREATE MULTISET TABLE SAMPLEDATA.channels_t ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
CHANNEL_ID FLOAT FORMAT '+9.99999999999999E+999' NOT NULL,
CHANNEL_DESC VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CHANNEL_CLASS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CHANNEL_CLASS_ID FLOAT FORMAT '+9.99999999999999E+999' NOT NULL,
CHANNEL_TOTAL VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CHANNEL_TOTAL_ID FLOAT FORMAT '+9.99999999999999E+999' NOT NULL,
vt PERIOD(DATE) NOT NULL AS VALIDTIME)
PRIMARY INDEX ( CHANNEL_ID );
Once the table is defined in the database use the OBIEE “import metadata” wizard to pull the table and column definition into the physical layer. The wizard is used so that all of the column definitions do not need to be manually added into the physical layer. At this point there are 2 methods for altering the table definition to take advantage of the temporal capabilities. The first method is to use the “select” table type. The second method uses dynamic table names. Descriptions of how to use each method follow.
To configure the “select” the table type for the physical table property needs to switch from “physical table” to “select” and the temporal sql needs to be added.
The SQL for the “select” table is entered into the text box:
The SQL needs to be carefully formed in order to support any valid “as of date” the user picks as well as to support the user not picking any value for “as of date”. In the case of no “as of date”, the query will default to the current date. The SQL is reiterated below with notes for key points.
A. “coalesce” is used to default the “as of date” when the end-user has not chosen a date
B. “cast” is used to convert the string returned from the “trim” function to the “date” data type
C. “trim” is used to get rid of the extra spaces in surrounding the “VALUEOF(…)” function
D. The string “VALUEOF(NQ_SESSION.as_of_date)” needs spaces surrounding it in order for the OBI server to substitute the chosen date for the string. If there are no spaces around the VALUEOF() function then OBIEE will generate errors like “the repository variable as_of_date has no value definition. Substituting “sp” for space, the string needs to be formatted as such:
E. “current_date” is used to default the “as of date” to today’s date
F. “VALUEOF(NQ_SESSION.as_of_date)” returns the chosen date stored in the variable that was defined in step 1
G. “as_of_date” is the variable that was defined in step 1
H. “Vt_start” is a derived column. Begin(vt) is a function that returns the beginning date of the valid time period (see Teradata temporal documentation for more details). This column is not mandatory in the physical layer. It is shown here as a way of exposing the data. The column alias name must match the column name in the physical layer.
I. “vt_end” is a derived column. End(vt) returns the ending date of the valid time period. This column is not mandatory in the physical layer. It is shown here as a way of exposing the data. The column alias name must match the column name in the physical layer.
The second method for configuring the physical table uses dynamic table names. Before the physical table can be configured a new variable needs to be added with the temporal SQL. For this example the variable will be called “dynTN”. The check box for “enable any user to set the value” should be checked. The “default initializer” should be set to the table name with the temporal “as of” clause all wrapped in single quotes. The SQL needs to be carefully formed in order to support any valid “as of date” the user picks as well as to support the user not picking any value for “as of date”. In the case of no “as of date”, the query will default to the current date. The SQL is reiterated in section 3a above with notes for key points.
To configure the physical table the check box for “use dynamic name” needs to be checked.
If you want to expose the temporal columns to the end user you will need to manually add columns to the physical definition. In the example below 2 columns were manually added: vt_start and vt_end (valid time start and end). Note that the additional columns need to be defined in the “select” SQL (as in the example above) as well as actual physical columns in OBIEE:
Note that they are defined as “date” data type columns as the functions return the “date” data type.
With all of the table and column and variable definitions correctly in place the “view data” action (right click on table name) should show data from the table:
That completes the configuration in the OBIEE Administrator. Next up is building the dashboard.
In OBI Answers, configure and save your query on the temporal table. This is a standard dashboard query – there is nothing unique about it.
In OBI Answers add a new Dashboard prompt.
On the dashboard prompt page add a new column prompt:
The column that I choose is vt_start. Since it has a data type of “date” OBIEE allows us to use a calendar type input. Under options the “set a variable” drop down should be set to “request variable”. Then the name of the variable that was defined in step 1 is entered:
Save the prompt.
Now create and save a dashboard with the query and prompt defined above.
When the dashboard is run, the data from the query defaults to the current date.
Clicking on the calendar brings up the prompt:
Select a date and select “ok” on the prompt and click “apply” on the dashboard. The data in the query now reflects the date selected on the calendar prompt.
This completes the tutorial on temporal dashboard set-up. This technique can be applied to many different temporal tables. They can use a common date, or dates can be set up for each table.
The SQL that was generated by OBIEE for the above query is below. The temporal bits are underlined:
select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5,
D1.c5 as c6
(select T2489."CHANNEL_CLASS" as c1,
T2489."CHANNEL_ID" as c2,
T2489."CHANNEL_TOTAL" as c3,
T2489."vt_end" as c4,
T2489."vt_start" as c5
(as of coalesce(cast (trim( ' 2011-12-09 ') as date),current_date)
channel_total,channel_total_id,begin(vt) vt_start,end(vt) vt_end
order by 2, 3, 6, 5, 4
A query that was generated by OBIEE that includes the temporal dimension “channel” and a fact table “sales” is shown below:
select distinct 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4
(select sum(T2086."AMOUNT_SOLD") as c1,
T2489."CHANNEL_CLASS" as c2,
T2489."CHANNEL_TOTAL" as c3
(as of coalesce(cast (trim( ' 2011-12-15 ') as date),current_date)
channel_total,channel_total_id,begin(vt) vt_start,end(vt) vt_end
where ( T2086."CHANNEL_ID" = T2489."CHANNEL_ID" )
group by T2489."CHANNEL_CLASS", T2489."CHANNEL_TOTAL"
order by 3, 2