What is the Purpose of an Alias Table?

An Alias table (Alias) is a physical table with the type of Alias. Physical aliases can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these table source types.


Alias Tables can be an important part of designing a physical layer. Here are some of the main reasons to create an alias table:
-To rename physical tables allowing them to be easily identified in the business model layer.
-By renaming you can impose a form of ordering on the tables shown in the physical layer.
-To reuse an existing table more than once in your physical layer (without having to import it several times)
-To set up multiple alias tables, each with different keys, names, or joins
-To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

You need to create aliases to
-Eliminate physical joins that cross dimensions.
-Eliminate circular joins.

More on Dates

Do you have timestamp values????

And do you want to......, well join them to a date????

Cast(timestamp_value as DATE)

Does the same as TRUNC(timestamp_value) in Oracle. It removes the time section and you are left with just the date part.

Works in Prompts as well as prompted columns (fortunately).

Which table is selected at runtime

You have many tables under your logical table in OBIEE. Is there any way to guarantee the order in which they are used when multiple paths through the data are available?

Well there is a way to ensure that given tables are ranked lower in the list for selection.

Priority Group

Found on the General Tab of the Logical table source properties.

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have a lookup table and another very large table where the data is repeated. Both have the same data, just one has many more rows. Often, scanning a large (many million row table) for distinct values is expensive, while access to a lookup table is “cheap.” In this situation, you can assign a higher priority to the lookup to ensure that all queries are fulfilled using the smaller (more efficient) table if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Note that the default value for Priority Group is 0 (zero), and changing this value to 1 (one) makes it lower in priority. Zero is therefore highest priority.

OBIEE - AVG

Avg

Calculates the average (mean) value of an expression in a result set. Must take a numeric expression as its argument.

The AVG is calculated as the sum of all rows aggregated divided by the number of rows aggregated. If the function is pushed down to the database, rows with nulls are excluded from the calculation by default. If AVG is done in Answers, then Nulls will cause errors in AVG.

Syntax

Avg(numExpr)

Where:
numExpr - Any expression that evaluates to a numerical 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.

OBIEE 11G and Flex using crossdomain.xml in Weblogic

For Flex viewer to communicate with a remote data source you need to deploy a small XML file in the root directory of the domain containing the data source.

The crossdomain.xml file has the structure


<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.adobe.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
<site-control policies="all"></site-control>
<allow-access-from domain="*"></allow-access-from>
</cross-domain-policy>



With OBIEE 10G we had a physical directory.

Now with 11G we are presented with weblogic and no root directory, .......bother.

The solution is to deploy an application for the root directory.

All is explained below

in the %BI Home%\Oracle_BI1\bifoundation\jee directory you create a directory called (in this case) apps
in the apps directory you place your crossdomain.xml file and create two further directories.

META-INF and WEB-INF

in the WEB-INF directory you place two files with the contents detailed below

web.xml



<web-app xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<welcome-file-list>
<welcome-file>crossdomain.xml</welcome-file>
</welcome-file-list>
<mime-mapping>
<extension>xml</extension>
<mime-type>application/xml</mime-type>
</mime-mapping>
</web-app>





weblogic.xml



<?xml version='1.0' encoding='UTF-8'?>
<wls:weblogic-web-app xmlns:wls="http://xmlns.oracle.com/weblogic/weblogic-web-app" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd http://xmlns.oracle.com/weblogic/weblogic-web-app http://xmlns.oracle.com/weblogic/weblogic-web-app/1.1/weblogic-web-app.xsd">
<wls:weblogic-version>10.3.3</wls:weblogic-version>
<wls:context-root>/</wls:context-root>
<wls:container-descriptor>
<wls:default-mime-type>application/xml</wls:default-mime-type>
</wls:container-descriptor>
</wls:weblogic-web-app>

Then you deploy the open directory (apps) as an application on the BI server and accept all defaults.

Char to Date

How to convert a CHAR to a Date datatype.

Assuming that the format of the char date is consistent the easiest is to use the EVALUATE function

EVALUATE('TO_DATE(%1,%2)' AS DATE,,'dd/mm/yyyy')

Here's a good article on more with changing a CHAR using the CAST function in OBIEE.

Happy Hacking

Upgrade done.

Upgraded from 10g to 11g without major incident.

It was a fairly steep learning curve, what with the shift in technology from a 'simple' oc4j to a full on weblogic enterprise level webserver, and yes I was caught out by not having a loopback adaptor. Here's an article.

Apart from that it was learning all the new file locations, the new ways to stop and start services and acclimatising to the look and feel of OBIEE 11G.

A few things didn't convert straight out the box.

• Images are handled differently.
• Some graphs needed a new sort column.

Just worked slowly and methodically through the existing dashboards, report by report - learning all the time.

Having a great time though and wouldn't go back.