About Physical Alias Tables

About Physical Alias Tables

An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a logical table source, and inherits all its column definitions and some properties from the logical table source. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these logical table source types. For more information, see Creating and Administering the Business Model and Mapping Layer in an Oracle BI Repository.

Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table:

• 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. For more information, see Identifying the Database Content For The Business Model.

You can allow an alias table to have cache properties that differ from its source table by setting an override flag in the Physical Table dialog box. In alias tables, columns cannot be added, deleted, or modified. Columns are automatically synchronized; no manual intervention is required.

NOTE:  Synchronization makes sure that source tables and their related alias tables have the same column definitions. For example, if you delete a column in the source table the column is automatically removed from the alias table.

You cannot delete source tables unless you delete all its alias tables first. You can change the source table of an alias table, if the new source table is a superset of the current source table. However, this could result in an inconsistent repository if changing the source table deletes columns that are being used. If you attempt to do this, a warning message appears to let you know that this could cause a problem and allows you to cancel the action.

NOTE:  Running a consistency check identifies orphaned aliases.

When you edit a physical table or column in online mode, all alias tables and columns must be checked out. The behavior of online checkout uses the following conventions:

• If a source table or column is checked out, all its alias tables and columns will be checked out.
• If an alias table or column is checked out, its source table and column will be checked out.
• The checkout option is available for online repositories (if not read-only) and for all source and alias tables and columns.

Alias tables inherit some properties from their source tables. A property that is proxied is a value that is always the same as the source table, and cannot be changed. If the source table changes its value for that particular property, the same will be applied on the alias table.

The following is a list of the properties that are proxied:

• IsCacheable (the inherited property can be overridden)
• CacheExpiry (the inherited property can be overridden)
• Row Count
• Last Updated
• Table Type
• External Db Specifications

The following is list of the properties that are not proxied:

• Name
• Description
• Display Folder Containers
• Foreign Keys
• Columns (tables don't share columns, ever. Aliases and sources have distinctly different columns that alias each other)
• Table Keys
• Complex Joins
• Source Connection Pool
• Polling Frequency
• All XML attributes

Creating and Administering General Properties for Physical Tables

Creating and Administering General Properties for Physical Tables

---

Use the General tab of the Physical Table dialog box to create or edit a physical table in the Physical layer of the Administration Tool.

This section contains the following topics:

• Creating or Editing Physical Tables
• Deleting a Physical Table

Creating or Editing Physical Tables

This section describes how to create or edit the general properties for a table. This includes physical cube tables and alias tables.

To create a physical table or edit general properties for tables and alias tables

1. In the Physical layer of the Administration Tool, perform one of the following steps:
   • To create a physical table, right-click the physical database and choose New Object > Physical Table.
   • To create a physical cube table for a multidimensional data source, right-click the physical database and choose New Object > Physical Cube Table.
     NOTE:  It is strongly recommended that you import cube tables, not create them manually.
   • To create an alias table, right click a physical table, and choose New Object > Alias.
     NOTE:  You can also create aliases on opaque views and stored procedures.
   • To edit an existing physical table, double-click the physical table icon.
2. In the selected Physical Table dialog box, complete the fields using Table 13 as a guide.

Table 13. Physical Table General Properties for Relational and XML Data Sources

Property	Description
Name	The Oracle BI Administrator assigns a name to new table.
Cacheable	To include the table in the Oracle BI Server query cache, select this check box. When you select this check box, the Cache persistence time settings become active. This is useful for OLTP data sources and other data sources that are updated frequently. Typically, you should check this option for most tables.
Cache never expires	When you select this option, cache entries do not expire. This could be useful when a table will be important to a large number of queries users might run. For example, if most of your queries have a reference to an account object, keeping it cached indefinitely could actually improve performance rather than compromise it. CAUTION:  This is only of use on some objects. Set this option for too many objects and the cache will become manageably large or objects might begin dropping out of the cache at inefficient times.
Cache persistence time	How long table entries should persist in the query cache. The default value is Infinite, meaning that cache entries do not automatically expire. However, this does not mean that an entry will always remain in the cache. Other invalidation techniques, such as manual purging, LRU (Least Recently Used) replacement, metadata changes, and use of the cache polling table, result in entries being removed from the cache. If a query references multiple physical tables with different persistence times, the cache entry for the query will exist for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry. If you change the default to minutes or seconds, type a whole number into the field on the left. For more information, refer to Troubleshooting Problems with an Event Polling Table.
External name	Applies to physical cube tables from a multidimensional data source. If you select a Table Type of Physical Table, the external data source name appears.
Override Source Table Caching Properties	Check box available for alias tables. When selected, the cacheable properties become available and you can clear or select the appropriate options.
Source Table	Applies to alias tables. The Select button allows you to choose the physical table from which to create an alias table.
Table Type	Physical Table values: Physical Table, Stored Proc (stored procedure), or Select. Physical Cube Table values: Physical Table or Select.
Use Dynamic Name	Check box available for non-multidimensional data source tables when you select a Table type of Physical Table. When selected, a dialog box opens in which you can choose a session variable.
Use Database Specific SQL Default Initialization String	For non-multidimensional data source tables (not alias tables), this appears if you choose a Table Type of Stored Proc or Select. For multidimensional data source tables, this appears if you choose a Table Type of Select. When you select the check box, you can specify the database and type the SQL.

Deleting a Physical Table

When you delete a physical table, all dependent objects are deleted. For example columns, keys, and foreign keys. When you delete a physical cube table, hierarchies are also deleted.

NOTE:  The deletion fails if an alias exists on the physical table.

To delete a physical table from the Physical layer

1. In the Physical layer of the Administration Tool, locate the table that you want to delete.
2. Right-click the table and choose Delete. 

Physical Layer – Physical Table Properties

A physical table is an object in the Physical layer of the Administration Tool that corresponds to a table in a physical database. They provide the metadata necessary for Oracle BI Server to access the tables with SQL requests. When data source definitions are imported, no actual data is moved. Data remains stored in the physical data source.

Physical Table Properties

Physical Column Property

1. Table Name: By default, the name corresponds to the table name defined during import, but you can rename it.
2. The Table Type: You can define physical table (default), store procedure and select statement. When you select either (Store procedure or select) options, a text pane below the Table Type drop-down list becomes active, allowing you to enter the stored procedure or the select statement.
3. Cacheable: Cacheable is selected by default (that means, If you enable cache in the NQconfig.INI under cache parameter, all the tables will default cache entries). You can select cache persistence time to purge cache entries.
4. Hints: Hints are instructions placed within a SQL statement that tell the database query optimizer the most efficient way to execute the statement. Hints override the optimizer’s execution plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan.
5. Columns: A physical column is an object in the Physical layer of the Administration Tool that corresponds to a column in a physical database.
6. Keys: It’s a primary key which uniquely identifies a single row of data, it consists of a column or set of columns and is identified by a key icon.
7. Foreign Key: A foreign key is a column or a set of columns in one table that references the primary key columns in another table.

Physical Layer – Alias Physical Table

An alias physical table type specifies that the physical table object is an alias to another table.
Uses of Alias Table in physical layer:
1. Single dimension table simultaneously appears several times in the same fact table.
2. To configure the self join in Physical Layer
3. Re structure and organize the physical tables
4. To separate two date columns used in the same table and requires join for some calculation.
5. It’s easier to give the access of objects to users in case of Multi User Development Environment.

Alias Synchronization
An alias table always inherits all of the column definitions from the source table and synchronization happens automatically. Consequently, columns in an alias table cannot be modified. All columns opened from an alias table become read-only. If you add or delete any columns in the source table, the same changes will automatically happen in alias table.

How to create an Alias table:
Right click on any table and select New Object and click on Alias, provide the name of the table and click Ok. The alias table appears with a green arrow alias icon in the Physical layer.

Creating an Alias vs. Duplicating a Physical Table

When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur.

Creating an alias creates a copy of the metadata table object that will be referenced in SQL with a new alias name. The alias name in SQL, as it is for all tables, will be derived its metadata ID.

To see the table IDs in metadata, use the Query Repository utility. Here are some physical tables (and aliases) in a repository that I’ve created. It’s the last five digits of the ID that will be used to create the table aliases in SQL.



PRODUCT_10 is a table that will referenced in a SQL FROM clause as PRODUCT_10 and will be given the alias T25248.

PRODUCT_119 is another physical table in the database, identical in structure to PRODUCT_10. It might have been created by duplicating the metadata table PRODUCT_10 and then renaming the duplicate as PRODUCT_119. It will be referenced in the SQL FROM clause as PRODUCT_119 with the alias T23388.

The metadata table Product_Retailer is an alias of PRODUCT_10. It has its own metadata ID, 3001:46690, and will be given the alias of T46690.

SQL using both the original table, PRODUCT_10, and its alias, Product_Retailer, will look like this, with the metadata alias name included (optionally, depending on your database features) as a comment:

FROM
PRODUCT_10 T25248,
PRODUCT_10 T46690 /* Product_Retailer */...

If you duplicate a table in metadata, then that new table (with its new name) must map to a table in the physical database that has that name. If it doesn't exist, the SQL issued will generate an error.

An alias is a reference to a table that already exists, not a separate database object.

Using Session Variables in Select Tables in the Physical Layer

There are many times when it is very beneficial to pass the value of session variables (or report variables) into the SQL used to define a Select table in the physical layer. This allows the select statement to focus on just the data you want, rather than creating a view with potentially millions of rows and then subsequently applying a filter to that result set.

There are three cases to consider, depending on whether the session variable is intended to filter a column that has a numeric, varchar, or date data type.

The first case is where a session variable has a numeric value. In the following example, the session variable RETAILERID has been assigned a numeric value. The intent is to filter that data just for that retailer. COMPANYID is the name of a physical column. The syntax is:

WHERE COMPANYID=ValueOf(NQ_SESSION.RETAILERID)

The second case is where a session variable needs to be evaluated as a string. In this case, enclose the ValueOf function (including the name of the session variable) in single quotes.

WHERE upper(SALESREP) = upper('valueof(NQ_SESSION.USER)')

The third case, dates, is the hardest. Dates are, frankly, inordinately messy in OBIEE. There are a plethora of ways that dates can get formatted depending on which application is being used to select the dates. It would be nice if there was a single place where you could say “I’d like dates to be formatted like this.” But there isn’t (a huge oversight, in my opinion), and if you attempt to descend into the javascript code forest to tweak things – well, good luck.

The approach I’ve used, which is not ideal but has worked for me, is to hedge your bets in the Select statements. For example, the format of a date report variable can vary, depending on whether the user has changed the default value set by a dashboard calendar prompt.

For example, here are dates as set by the default values in the prompt.





When the user modifies the date range using the first calendar, the format of the first date changes.



If these date prompts are setting report variables, you need to be able to deal with both formats. I’ve done it this way.

BETWEEN case when substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.StartDate)',1,10), 'yyyy-mm-dd') else to_date('valueof(NQ_SESSION.StartDate)', 'mm/dd/yyyy') end AND case when substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.EndDate)',1,10), 'yyyy-mm-dd') else to_date('valueof(NQ_SESSION.EndDate)', 'mm/dd/yyyy') end Note that the substring formulas, which have to span dates from 2000 through 2019, need the comparisons to both ‘200’ and ‘201’. Of course, next decade, the formulas will need further adjusting, but once every 10 years isn’t too bad!

Query optimization using HINT

In this paper I would like to share the experience with an attribute Hint, we were forced to use to optimize queries. After the implementation of the dashboard with one unnamed client duration of all queries conducted at the required times (1-3 seconds).Approximately 2 months after deployment to production operation there was a sudden drop in performance of queries with a few tens of minutes. This was because the facts table monthly "reproduced" by about 10 million records. optimization, we decided to solve the deployment of metadata attributes hint at the physical table. After deployment, the performance of queries returned to the original desired time intervals (for some queries, the duration of the query even lower than after the start of production operation). To set a HINT everything works as it should: for example. syntax for using a particular index is: INDEX (TableName, název_indexu) Hint is given in tables in the physical layer of the metadata repository: But we had to deal with demand, which for a given query can specify different filters (conditions) on several key attributes - at least one of them.On any given main attribute was used in a database index on which we wanted to set the hint. The syntax used is more than hint at a time (again for the index) this (separated by a space): INDEX (TableName, název_indexu) INDEX (TableName, název_indexu) INDEX (TableName, název_indexu) Unfortunately the version used in the OBI EE (10.1.3.3.0), this can not write decipher. The solution is simple ("vypocené") for 2 and another hint to not use the name of a physical table, but the alias under which is listed in the resulting physical SQL. Fortunately, the alias invariant (apparently generated from the identifier under which the table is stored in the metadata). Example: INDEX (F_ATM, IDX_ATM_CARD) INDEX (T1391, IDX_ATM_TERM) INDEX (T1391, IDX_ATM_POSTDAT_AUDIT) Miroslav Petr (Consultant Adastra).