OBIEE allows RPD developers to define a table in the physical layer as a select statement (basically a view), without with view actually being created in the database. The definition of the view then shows up in the physical SQL sent by the BI server (as follows):
SELECT T1234.DATE,SUM(T2345.SALES) FROM TABLE1 T1234, (SELECT DATE, SUM(SALES) AS SALES FROM TABLE2 JOIN TABLE3 USING (column1,column2)) T2345 WHERE T1234.DATE = T2345.DATE GROUP BY T1234.DATE;
I’ve used these before from time to time, but usually for smaller “tweaks”, such as selecting a distinct subset of columns from a dimension table. I recently had a use case where I ended up “creating” a fact table using an opaque view.
An existing “P&L Exception Report” had been create years prior by an end user within Answers. The report showed one row per store, showing submission for each month, with a monthly status based on a variety of factors (when the store opened, when the submission occurred, if the current date was more than a certain number days from the end of the previous month). All the logic was built into the myriad of Answers column expressions themselves, and then two pivot tables were used, with the pivot table showing percentages having the aggregation rules set to percent of some totals.
Because there are two pivot tables, users had to page through them both independently. Additionally, the line spacing on the percentage pivot table was off, making it difficult to read.
In the RPD, we only had a single fact table for P&L submissions, based on a database view joining four detail tables together. This was required to be able to see the specific ledger accounts for other reports, but for this report, we really just need to know for a given store and month, was a P&L submitted and when it was submitted. This data was available in one of the four tables in the view. Even worse, we also needed to know which stores did not submit a P&L for a given month, which had previously been solved for by the use of a dummy fact, causing physical SQL to be written join two other tables only on the condition 1=1.
I was able to write a much simpler SQL statement that returned one row per store per month, showing the with a submission status as well as several additional columns with a 0 or 1 to use to count the number of stores to find percentages (all of this previously has been done via complex case statements in Answers).
Within the RPD, I then used the SQL statement I had prepared as an opaque view within the physical layer.
You can then treat this as any other “table”, and join as needed (in my case to our stores and dates dimensions). I created a new logical table in the applicable BMM, again joining logically to our store and dates dimensions.
Within the new logical table, I was able to create measures as needed per report requirements, and exposed them within the presentation layer.
I was able to recreate the original report using the new measures, within a single pivot table, allowing for proper formatting and readability. Additionally, reporting performance improved significantly; and it now presents the measures to our Answers users directly in the presentation layer, ensuring that all users are using the same measures definitions. Lastly, this now allows the data to be easily rolled up our store dimension, showing overall exception reporting at higher levels, while the original report design was limited to one row per store.
Notes of Caution:
I did discover that if I had written my original SQL statement using WITH sub-query factoring, I would get a database error at run-time about improper use of WITH. I found this was because the BI server was wrapping the entire query in it’s own WITH clause, and the database does not like nested WITH clauses. I found a site that did discuss some workaround, but it involved modifying the supported database features in the physical layer, which was not something I wanted to do, given the unknown impact to our existing reports. I ended up re-writing the SQL to not use a WITH clause.
I also realize that by using the RPD opaque view instead of an actual database view, it does make impact analysis’s in the future more difficult – for instance, if there was a change being made to one of the tables in my SQL statement. If the view was within the database, it would show up under the dependency list within the database metadata. In my case, an OBIEE developer would now need to check the definition of all the opaque views instead.