How to create a PL/SQL package to discard multiple level of cascading views

Question

I am working on a CR where I need to create a PL/SQL package and I am bit confused about the approach.

Background : There is a View named ‘D’ which is at end of the chain of interdependent views in sequence.
We can put it as :
A – Fact table (Populated using Informatica, source MS-Dynamics)
B – View 1 based on fact table
C – View 2 based on View1
D – View 3 based on view2
Each view has multiple joins with other tables in structure along with the base view.

Requirement: Client wants to remove all these views and create a PL/SQL Package which can insert data directly from MS-Dynamics to View3 i.e., ‘D’.

Before I come up with something complex. I would like to know, is there any standard approach to address such requirements.

Any advice/suggestions are appreciated.

Answer 1

It should be obvious that you still need a fact table to keep some data.

You could get rid of B and C by making D more complex (the WITH clause might help to keep it overseeable).

Inserting data into D is (most likely) not possible per se, but you can create and INSTEAD OF INSERT trigger to handle that, i.e. insert into the fact table A instead.

Example for using the WITH clause:

Instead of

 create view b as select * from dual;
 create view c as select * from b;
 create view d as select * from c;

you could write

 create view d as
   with b as (select * from dual), 
        c as (select * from b)
   select * from c;

As you can see, the existing view definition goes 1:1 into the WITH clause, so it's not too difficult to create a view to combine all views.

Answer 2

A few things first

1) A view is a predefined sql query so it is not possible to insert records directly into it. Even a materialized view which is a persistant table structure only gets populated with the results of a query thus as things stand this is not possible. What is possible is to create a new table to populate the data which is currently aggregated at view D

2) It is very possible to aggregate data at muliple levels in Informatica using combination of multiple inline sorter and aggregater transformations which will generate the data at the level you're looking for.

3) Should you do it? Data warehousing best practices would say no and keep the data as granular as possible per the original table A so that it can be rolled up in many ways (refer Kimball group site and read up on star schema for such matters). Do you have much sway in the choice though?

4) The current process (while often used) is not that much better in terms of star schema

Answer 3

If you are on Oracle 12c you might look at DBMS_UTILITY.EXPAND_SQL_TEXT, though you'll probably want to clean up the output a bit for readability.