Tis is an informational offering to the forum - enjoy. Fell free to ask more.
You may already have figured this out but just in case I figured out how to create a report tab from a Data Providor, then save the report as text and use the text file as a new Data Provider to then allow ExportTo RDBMS of the second DP.
This allows me to “massage” the data in the first DP using the report formulas and formats. So then BCA becomes an ETL tool. There are two issues: 1) is that the ExportToRDBMS drops/creates the table each time and 2) The name of the data provider is used to create the table and cannont be changed by VBA at run time. Soooo I run a third DP that does a PL/SQL select from the ExportToRDBMS table created and inserts the “new” rows to a premenant table. Extract to ODS and append to DM.
Finally we then run other reports against the ExportToRDBMS table or the permenant table. Thus we are effectively doing what a PL/SQL developer does in creating temp tables to then select from or a DataWarehouse developer does with Informatica and Oracle, for example, in ETL’s.
This is especially usefull for queries against a large number of tables with alot of rows. The normal complex joins cause a single SELECT to run very slow as it is asking Oracle to do everything in one step. The above solution allows the task to be broken into as many steps as needed for performance gains. We us it to create ODS’s (Operational Data Store) or small extracts in the form of a single table that can be selected from quickly. Sometimes we create multiple ODS’s and then run a single select against the multiple ODS’s, or we select from regular application tables and one or more ODS tables in a single select.
With the performance gains we handle ODS’s and DM’s with manny millions of rows and get reasonable results in terms of the time to build the ODS, and DM and select from them for the reports.
Finally we also use DBLinks from one DB to another to actually extract from one machine and ODS on another and then select from the ODS and other local tables on the ODS machine. We have gained permissions to create ODS and DM tables on all the application databases that we extract from and need to boost performance. The DB’[s like it because it’s faster.
zing (BOB member since 2002-11-01)