User-Defined Table Examples

In Essbase Studio, you create user-defined tables, which are similar to an RDBMS view, by entering SQL syntax in the Table Definition text box of the User-Defined Table Definition dialog box. The examples in this topic show the difference between the SQL statement you use in an RDBMS and the SQL statement you use in Essbase Studio.

The following example illustrates a SQL statement to create a view in an RDBMS:

CREATE VIEW
View_Prod_Proddim 
(Caffeinated, FamilyID, Ounces, PackageType,
ProductID, SKU, Family) as 
SELECT
a.caffeinated, a.familyid, a.ounces, a.pkgtype, 
a.productid, a.sku, b.family
FROM tbc.product a, tbc.productdim b
WHERE a.sku=b.sku

Using the example above, in Essbase Studio, when you create a user-defined table, the SQL syntax does not include the CREATE VIEW portion of the SQL statement. Your SQL table definition syntax begins with the portion of the statement starting from SELECT.

The following user-defined table SQL example is written for a database where table names are expressed in the format catalog.table:

SELECT
a.caffeinated,
a.familyid, a.ounces, a.pkgtype,
a.productid, a.sku, b.family
WHERE a.sku=b.sku
FROM tbc.product a, tbc.productdim b

The following is the same example written for a database where table names are expressed in the format catalog.schema.table:

SELECT
a.caffeinated,
a.familyid, a.ounces, a.pkgtype,
a.productid, a.sku, b.family
WHERE a.sku=b.sku
FROM tbc.user1.product a, tbc.user1.productdim b