How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?

[Miracle]

Hi I have written a post about How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?

Please read and provide your feedback if this is useful. 

How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?

I'm sure most of you must be wondering how can I run a SQL script in fusion applications because we don't have the direct access to Fusion application databases. 
Therefore I thought of sharing this very useful document on how to do that. I have used here the screenshots from Release 13 ! Which is awesome isn't it if you are wondering how the Release 13 GUI looks like !

Here are steps that you have been looking for to run the SQL scripts in fusion applications !!

In order to run a SQL query against the Fusion Applications Database the user is required to add the SQL to a BI Publisher SQL Data Model and then to execute the Data Model.
The process is a two stage one, the first stage being to create the data model containing the appropriate SQL and the second being to display the result set in a BI Publisher report.

Stage 1 - Creating the Data Model  

There are two approaches to access BI Publisher dependent on the starting point.The most straightforward approach is to directly access BI Publisher via its own URL, this being /xmlpserver. For users working in an existing Fusion environment this extension can be added to the existing Fusion Application host, for example :

If the Fusion Application is being accessed through a URL such as 'https://fs-aufsn4x0cxf.oracleoutsourcing.com/homePage' then the equivalent URL to access BI Publisher directly would be 'https://fs-aufsn4x0cxf.oracleoutsourcing.com/xmlpserver'
This can be seen in the screenshot below ;

The second approach is to access BI Publisher via the Reports & Analytics link in the Fusion Applications Navigator as shown below :

Once the user has access the Reports & Analytics link, clicking on Create > Report will take the user into Report creation in BI Publisher, cancelling this leaves the user in BI Publisher mode (the URL will reflect as ..../analytics/saw.dll?bipublisherEntry as you are working in BI Publisher implicitly) :

 

From this point on the user can then click on New > Data Model 

[size=undefined]
The Data Model defines the SQL which we are going to issue. For the purposes of this document and the functionality detailed within it it may be useful to define and save a data model which you can then re-use to issue SQL statements as required. There is no need to create and save multiple data models, one for each SQL statement, unless you have an expectation that a given SQL statement may be issued regularly, in which case having a static data model for that statement may be useful. In this case we are going to create and save a dummySQLDataModel which we will use to issue our SQL statement.
The user will be taken to the default Data Model definition page. Click on the Data Set dropdown highlighted in the screenshot and select 'SQL Query' :

Once the SQL is defined, click on OK, and the screen will then change to show a diagram and the fields which were included in the SQL statement :

Click on the 'View Data' link in the top right hand corner of the screen, set 'Rows' to a value like 200 (note this is the max value allowed in the Data Model), click on the 'View' button, and finally, select 'Table View', at which point the user will be presented with the result set :

"You can use the Query Builder button to view all the tables and columns however; you have to select the FUSION schema rather than the FUSION_RUNTIME schema,    which is what the Query Builder defaults to."
Stage 2 - Viewing the full output
The second part of the process involves the creation of a report template which will then allow generation of the full set of output for the query being issued.
Continuing from the preceeding steps, assuming the output is as required the next step would be to save the data model which we can then use for the template creation later :

With the data model saved at a suitable location we should then perform  a 'View Data' should then be performed again and the output saved as sample data for the data model, this is then used to build the report template :


Once the data is saved as Sample Data you can then click on 'Create Report' and will be taken to the Report Creation wizard, the data model you have just created will be shown, click on Next :


Select a basic Landscape layout and Table output to aid presentation of the results, and then click Next :

A preview of the results will be displayed at which point you can adjust the layout and drag and drop columns from and to the table, and then click Next :


Select View Report and then click 'Finish' :
[/size]

[viggora]

инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинйоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо

[viggora]

audiobookkeeper.rucottagenet.rueyesvision.rueyesvisions.comfactoringfee.rufilmzones.rugadwall.rugaffertape.rugageboard.rugagrule.rugallduct.rugalvanometric.rugangforeman.rugangwayplatform.rugarbagechute.rugardeningleave.rugascautery.rugashbucket.rugasreturn.rugatedsweep.rugaugemodel.rugaussianfilter.rugearpitchdiameter.ru
geartreating.rugeneralizedanalysis.rugeneralprovisions.rugeophysicalprobe.rugeriatricnurse.rugetintoaflap.rugetthebounce.ruhabeascorpus.ruhabituate.ruhackedbolt.ruhackworker.ruhadronicannihilation.ruhaemagglutinin.ruhailsquall.ruhairysphere.ruhalforderfringe.ruhalfsiblings.ruhallofresidence.ruhaltstate.ruhandcoding.ruhandportedhead.ruhandradar.ruhandsfreetelephone.ru
hangonpart.ruhaphazardwinding.ruhardalloyteeth.ruhardasiron.ruhardenedconcrete.ruharmonicinteraction.ruhartlaubgoose.ruhatchholddown.ruhaveafinetime.ruhazardousatmosphere.ruheadregulator.ruheartofgold.ruheatageingresistance.ruheatinggas.ruheavydutymetalcutting.rujacketedwall.rujapanesecedar.rujibtypecrane.rujobabandonment.rujobstress.rujogformation.rujointcapsule.rujointsealingmaterial.ru
journallubricator.rujuicecatcher.rujunctionofchannels.rujusticiablehomicide.rujuxtapositiontwin.rukaposidisease.rukeepagoodoffing.rukeepsmthinhand.rukentishglory.rukerbweight.rukerrrotation.rukeymanassurance.rukeyserum.rukickplate.rukillthefattedcalf.rukilowattsecond.rukingweakfish.rukinozones.rukleinbottle.rukneejoint.ruknifesethouse.ruknockonatom.ruknowledgestate.ru
kondoferromagnet.rulabeledgraph.rulaborracket.rulabourearnings.rulabourleasing.rulaburnumtree.rulacingcourse.rulacrimalpoint.rulactogenicfactor.rulacunarycoefficient.ruladletreatediron.rulaggingload.rulaissezaller.rulambdatransition.rulaminatedmaterial.rulammasshoot.rulamphouse.rulancecorporal.rulancingdie.rulandingdoor.rulandmarksensor.rulandreform.rulanduseratio.ru
languagelaboratory.rulargeheart.rulasercalibration.rulaserlens.rulaserpulse.rulaterevent.rulatrinesergeant.rulayabout.ruleadcoating.ruleadingfirm.rulearningcurve.ruleaveword.rumachinesensible.rumagneticequator.rumagnetotelluricfield.rumailinghouse.rumajorconcern.rumammasdarling.rumanagerialstaff.rumanipulatinghand.rumanualchoke.rumedinfobooks.rump3lists.ru
nameresolution.runaphtheneseries.runarrowmouthed.runationalcensus.runaturalfunctor.runavelseed.runeatplaster.runecroticcaries.runegativefibration.runeighbouringrights.ruobjectmodule.ruobservationballoon.ruobstructivepatent.ruoceanmining.ruoctupolephonon.ruofflinesystem.ruoffsetholder.ruolibanumresinoid.ruonesticket.rupackedspheres.rupagingterminal.rupalatinebones.rupalmberry.ru
papercoating.ruparaconvexgroup.ruparasolmonoplane.ruparkingbrake.rupartfamily.rupartialmajorant.ruquadrupleworm.ruqualitybooster.ruquasimoney.ruquenchedspark.ruquodrecuperet.rurabbetledge.ruradialchaser.ruradiationestimator.rurailwaybridge.rurandomcoloration.rurapidgrowth.rurattlesnakemaster.rureachthroughregion.rureadingmagnifier.rurearchain.rurecessioncone.rurecordedassignment.ru
rectifiersubstation.ruredemptionvalue.rureducingflange.rureferenceantigen.ruregeneratedprotein.rureinvestmentplan.rusafedrilling.rusagprofile.rusalestypelease.rusamplinginterval.rusatellitehydrology.ruscarcecommodity.ruscrapermat.ruscrewingunit.ruseawaterpump.rusecondaryblock.rusecularclergy.ruseismicefficiency.ruselectivediffuser.rusemiasphalticflux.rusemifinishmachining.ruspicetrade.ruspysale.ru
stungun.rutacticaldiameter.rutailstockcenter.rutamecurve.rutapecorrection.rutappingchuck.rutaskreasoning.rutechnicalgrade.rutelangiectaticlipoma.rutelescopicdamper.rutemperateclimate.rutemperedmeasure.rutenementbuilding.ruultramaficrock.ruultraviolettesting.ru