I have following oracle query which need to be used as a Data Source in OBIEE Physical Layer. I guess I have to create stored proc. How do I implement this in OBIEE RPD and how do I implement the respective Dashboard prompts for the parameters.
CIRC.PATH_NAME, CIRC.BANDWIDTH , CIRC.CATEGORY, CIRC.CUSTOMER_ID,
CIRCUIT_PATH circ, VAL_CUSTOMER cust,
DISTINCT CIRC_PATH_INST_ID, LEVEL LEV
CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID IN ( SELECT
WHERE SITE_NAME = @variable('Enter a Site Name')
AND CARD_SLOT = @variable('Enter a Card Slot')
PRIOR CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
AND ELEMENT_TYPE != 'K' ) QUER
circ.circ_path_inst_id = QUER.CIRC_PATH_INST_ID
and circ.cust_inst_id = cust.cust_inst_id (+)
LEV DESC , CIRC.PATH_NAME ASC, CIRC.BANDWIDTH ASC
Where do SITE_NAME and CARD_SLOT exist in your physical model? The way I would do this is by creating a view that exposes SITE_NAME and CARD_SLOT so that you can join it in a another view or the physical layer. It looks like this is a hierarchy table so I would suggest you prepopulate a hierarchy table to avoid performance issues.
This query is from Business Object. I am trying to convert BO Report to OBIEE solution. The SITE_NAME and CARD_SLOT exists in PORTS table but I can not join to other outer tables. What do you mean by prepopulate a hier. table? The hier. table CIRC_PATH_ELEMENT is already populated. The INNER table PORTS having parameter is used only for feeding START BY clause. Please clarify
Thanks. I looked at your URL. I do have package. Just using procedure. So my initialization string (For stored proc in Physical Layer of RPD) is
But when I run request in Answer I get the error
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 900, message: ORA-00900: invalid SQL statement at OCI call OCIStmtExecute: exec demo.add_employee(105); . [nQSError: 17011] SQL statement execution failed. (HY000)
What should I put in initialization string in RPD?
That's probably a question for the PL/SQL forum although Google should help you. But you basically pass in the parameter of the function to the SP. Functions must return a value so you will need an output parameter on the SP to get the data out of the SP and then you can return it at the end of the function.
Check out here to the end of the article for the pipelined function.
But are you sure that OBI is the good tool for what you want.
Have you a fact table ? Which kind of report do you want with a flat bill of material ?
If you only want to run a report based on two parameters, I will work with BI Publisher.
If I assume that you don't need all bill of material, an other work around will be to store all your data in a flat table by procedure.
Thanks for info. I am making progress. The following in Initialization String of RPD Physical Layer for Data Source as Stored Proc worked.
BEGIN demo.add_employee('VALUEOF(NQ_SESSION.empid1)'); END;
But my demo.add_employee SP need OUT parm as well. How do I RETURN OUT PARM back to OBIEE so that I get the result set in Dashboard?