BI Administration Tool: Howto to integrate metadata of a DB-View(or table) into the Physical Layer C

Question

I start working with BI Administration Tool, thus I just begin working with this tool. I work on  Oracle OBIEE E12c Oracle BI Administration Tool 12.2.2.0.0

In BI-Database  R12 of my environment there are a few fact and dimension tables, that have been modeled with OWB from my predecessor in the oracle dev job role. I have a good understanding of this star schema and with SQL I built a view PFAC_VENDOR_PERF_OV2_V that derives and aggregates a few new measures on a aggregated level of the cube's lowest grain. Thus the view realizes kind of a aggregate subcube of some upper hierarchical level.

I need now to add this view into the Physical Datamodel of OBIEE as a first step to propagate availability of the view's measures to BI-Analytics/Answers.

For this I think I have to use BI Administration Tool.

"Metadata Repository Builder's Guide for OBIEE - BIEMG (2017.10)"  states that I best import metadata from the database.

Doing the metadata import on the DB-Connection I get into this dialogue:

Anyway my DB-view is not listed here.

My questions:

* Why is my View PFAC_VENDOR_PERF_OV2_V not visible in the left selection pane [Data source view]?
* How can I get it to be visible there, with all it's glory (column definitions, column type, etc.)?
* How can I get my view into the Physical Layer Catalog   "PUR Recevings" in the left pane?

Im quite puzzled because Physical Layer Catalog  appear in the left pane, too. In contrast I expect to construct just these layers in the right pane, by the selections of this dialogue.

(Above pic shows traces of vained tries: The physical table PFAC_VENDOR_PERF_OV_V in the right pane was build manually with names corresponding to another real-life view on the database. This doesn't select data neither and throws an error when I try to select data for testing..)

Can you help me to resolve this confusion and get the things done?

regards

LPNO,

LPNO · Answer

Hi Gianni, Thanks for your answer, that gave me an idea. Now here's the setup to get it right. It was quite a long way…

I used an ill connection pool. This was created by my wizard choices, when I created a RDP-File from scratch and it had used

Username/pw: weblogic/pw.

Data source name: R12-Test (the ODBC-connection to talk to BI-Server)

Crucial point is the connection type. Default here is ODBC, thus this connects the physical layer back to BI-Server. Because of this I guess I got this strange circular reference, where I get in the input, what should be shown in the output. All other strange error were because this user did not have much permissions/rights After changing the connection pool properties to

Then it connected the right way round and meta data import started to fly.

regards,

LPNO

LPNO · Answer

Hi Gianni, thanks for you answer. Table DWH_R12.FAC_RECEIVING_TAB is visible in the physical layer. It does not carry any grants. DB-User of all OBIEE tables and views is the DB-user DWH_R12. That user has almost all rights like SYS. This user did CREATE OR REPLACE VIEW DWH_R12.PFAC_VENDOR_PERF_OV_V as .... BI Administration Tool connects with user weblogic. I'm don't know what DB-User is used by BI-Server/weblogic, but as checked above: other DWH_R12.tables without any grant are visible for BI-Server/weblogic. regards LPNO

Gianni Ceresa · Answer

Hi,

Isn't it a permission issue in your DB?

Are you sure the user you use to connect to the DB is allowed to see the view and use it?

You also can create the view yourself by hand in the physical layer directly, it will just be a bit annoying as you will have to create all the columns and set their types accordingly to your view, but still a way to do things (which doesn't help if you have permissions issues as the view will not be accessible).