Sure, it's possible. The BI tools are ignorant to the fact that the databases they report against are DW or Operational Support Systems (OSS). Your metadata-driven virtual data warehouse idea may seem attractive from the perspective of avoiding the need to extract, transform and load, but make sure you consider the cost of this approach too. Primarily, your OSSs are designed to support the posting of business transactions. Highly normalized and tuned for small scoped inserts, updates and deletes. Start running OLAP queries against that database, and I guarantee the creation of unhappy clients -- either the OLAP client, the OSS client or both. BI tools work best against databases designed for OLAP. The creation and maintenance of OLAP reports, charts, dashboards, queries, etc. are much less painful. The performance of your OSS database will suffer. You simply can't tune a single database to support OLTP and OLAP concurrently. The fact is, one or the other will suffer. You can prove it to yourself or take advantage of the thousands of companies before you and learn from their hard learned lessons.
Dig Deeper on Oracle database design and architecture
Related Q&A from Mike Lampa
When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to ... Continue Reading
What is a surrogate key in a table? Continue Reading