Using WITH in Physical Tables (“Opaque Views”)

Common practice is to use derived tables (sometimes referred to as “inline views”) when creating a physical layer “Select” table.

What about using the SQL-99 WITH clause instead, which does not begin with SELECT? After all, WITH clauses have some advantages: the SQL can be easier to read and execute faster.

My experience is that a WITH clause will work in an opaque view as long as the database supports WITH, of course. Oracle started supporting it in version 9i. However, there is an important caveat: the SQL will error out if the opaque view itself is embedded in another WITH clause generated by the BI server.

To repeat: if the BI server generates SQL that is of the following form, the Oracle database will execute it.

SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …

However this form of SQL will fail.

WITH
SAWITH0 AS
(SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …

You can control whether the BI server generates SQL using the first or second form by changing the database features in the metadata.

I ran the same logical query under different sets of database features using Oracle XE. The query had measures from two logical fact table sources, one of which was an opaque view that used WITH. Both LTSs had to be queried and the results outer joined together.

The default features for Oracle produced the second form of SQL and this error message:

Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause 

Turning on PERF_PREFER_MINIMAL_WITH_USAGE resulted in a different error, an internal Oracle error (an Oracle bug there are patches for). If patched, the SQL should run (but I did not test this) since the SQL generated is of the first form.

The error I saw in this case was

ORA-00942: table or view does not exist


This is an error message that is symptomatic of the Oracle bug.

Turning on the PERF_PREFER_INTERNAL_STITCH_JOIN fixed that problem and the SQL generated, which was of the first form, ran successfully. Turning off WITH_CLAUSE_SUPPORTED, logically the safest way to prevent the second form of SQL from being generated, also worked, as expected. Conclusion: You can use WITH in opaque views. In the admin tool, you can right click on the opaque view and update row count or view data to make sure that the opaque view is syntactically correct.





However, you will have to adjust database features so the BI server will not generate its own SQL WITHs. Probably the safest coure is to turn off WITH_CLAUSE_SUPPORTED. This may be too drastic for some people’s tastes – though, to be honest, the WITH SQL that the BI server generates tends to be about as “baroque” as derived table SQL. So you’re not gaining a lot of readability by having the BI server use WITH.

Even if you do not want to use WITHs in opaque views in the final metadata version, it can still be helpful to use them during metadata development. If you are writing an opaque view that is complex when using derived tables, writing it using WITH can be easier. Test that SQL verify that the logic is correct, and then translate it back to use derived tables in your finished product.

Multi-Select with an All Values Choice, and Fragments

Here’s a not uncommon scenario. You are designing a dashboard page with a report such as sales by customer. To show sales by customer, you design a report containing the CustomerName column so that facts are grouped by customer. You provide a multi-select prompt to allow the user to select which customer data to view. But you also want to provide the ability to view the data for all customers as a total, without grouping by customer.

For example, consider the following sales data for a group of retailers and suppliers.

Each supplier wants to view the data by customer, but also wants to see the data for all customers as a total.

Mike wants to see data for A and C as separate items, but also has the need to see what sales are for the total of A + C. Ned wants to see data for C, D, and the total of C + D. Oscar wants to see the data for A, B, C, or D as well as the total of all four.

In other words, Mike wants a multi-select prompt for CustomerName that offers him three choices: A, C, and All Customers -- something that looks like this:

This would allow Mike to see a report like this

or this

 

using the same prompt and the same report.

This can be done by creating multiple sources for the CustomerName logical column – one source for individual customers and another source for All Customers – and then combining both sources (unioning all) as separate fragments.  The SQL you need to have the BI server generate should be of the form:

select D0.C1 as C1
,sum(D0.C2) as C2
from (
(select
T3476.CUSTOMERNAME as C1
,T3484.AMOUNT as C2
from …)
union all
(select
'**All Customers' as C1
,T3484.AMOUNT as C2
from …)
)D0
group by D0.C1

To get this union all to happen, you need to use the Fragmentation content feature of the logical table source object. In this case you could create two sources as fragments.

To get the union all to work, you not only need to have checks in the checkboxes, you also need to define different fragmentation content. It does not really matter what the fragmentation content is! In this example, there is a logical column, “Customer Selection”, that is used to define the fragmentation content. This logical column does not even have to be exposed in the presentation layer. Its sole function is to tell the admin tool that these two fragments have different content and therefore need to be combined to get all the values for the CustomerName logical column.

In the business model I generated for this topic, I have a table called “SupplierCustomer” that lists all the Customers doing business with each supplier. This table is included in each logical table source. In the source for the individual customer names, it joins to the Customers table.

Note the logical column to physical column mapping.

In the other logical table source, the only table is the SupplierCustomer table (or, in this case, an alias of it).

The logical column CustomerName maps to the value
‘**All Customers’. (The double asterisks help to sort this value first in the list of customers).

This example also uses an init block to set the value of a SupplierID session variable when the user (i.e. the supplier) logs in. This session variable is also used in the WHERE clause of each LTS.

This technique works reasonably well, but there is one big problem: it fails when a column based on a time series function (Ago or ToDate) is used. For example, this query succeeded.

When logged in as Ned, it produced these results:

However, when the column Month Ago Amount, defined using the Ago function, was added

an error occurred.

[nQSError: 22049] Function AGO may not be used in a query that refers partitioned (fragmented) Logical Table Sources.

I was surprised when I saw this error. When the time series functions were introduced (and sadly, in my opinion, the Time Series Wizard was deleted) they imposed many restrictions in the metadata. In recent releases, some of these restrictions have been eliminated,  but not this one. It is too bad, since fragments in general are a valuable feature and have always been a core feature of OBIEE. So you have two choices: abandon fragments entirely or abandon time series functions and do time series the old fashioned way.  But if you choose the second path, you have to manually perform each step to set up Period Ago comparison measures, since you no longer have the time series wizard to do the steps for you.