Jump to content

OBIEE report generation - bulk creation, avoiding GUI??

Recommended Posts

I'm looking at this from a different angle.  We are about to convert a LARGE number of Oracle Discoverer reports to OBIEE.  I originally was looking for ways to compare the old Discoverer reports to see if I can find similar reports.  Now I'm interested in finding out if there is a way to create a bunch of the OBIEE reports programatically (or at least by avoiding the graphical interface).  It looks like the documentation I'm looking at is all GUI based... so I'm wondering if there is a way to take some SQL and generate a simple report.  Then someone can go back into the GUI and "pretty it up" so it looks like the users want.  If so, I could possibly parse out the information from a bunch of the output created by the workbook dump utility and at least get a skeleton report made quickly.  Here's an example...

The workbook dump utility is generating something like this:


Sheet Number 1 
 Sheet Name = Tabular Layout 
 Sheet Unique Name = {8690F66A-B9C8-11D1-ADB2-0080C7CDEA89} 
 Query(s) used =  
 Query 1 
  Items :- 
  EUL Item - Video Analysis Information.Calendar Year 
  EUL Item - Video Analysis Information.Department 
  EUL Item - Video Analysis Information.Region 
  Calculation - Profit SUM 
  Sort On   EUL Item - Video Analysis Information.Region 
  Filters :- 
  EUL Filter - Video Analysis Information.Department is Video Rental or Video


I'm guessing this becomes SQL that looks something like this: (not a "quote" but using to format)


select calendar_year as "Calendar Year", department as "Department", region as "Region", profit as "Profit"
from video_analysis_information
where department = 'Video Rental' or department='Video Sale'
group by profit
order by region;


So, does anyone know if there is a way to take something like this SQL (or any other formatted info) and create an OBIEE report WITHOUT using the GUI?  In the interest of attacking a bunch of these reports quickly, this would be really helpful.  FYI - it looks like 3rd party tools may not be not allowed, so I'm not looking for purchased solutions at this time.
Note:  We are using OBIEE 12


Share this post

Link to post
Share on other sites

Well, they're looking to transition the users into using OBIEE, with the hopes that most users will learn how to write their own reports and use all the wonderful new features.  Only problem is, the mandate to turn off the old servers is looming and we'll have no way to keep the users operational.  So, for now, we have to get the reports up and running so they can continue to function.  I was asked to figure out how to get the reports converted "in bulk" to meet the deadline, and then they'll train the users and get them working on their new reports.  Problem is, we know how that goes... old reports are not re-written (because they function and no one wants to reinvent the wheel).  It's an ugly situation to be creating, but my job is to get them off Discoverer (converting 500-1000 reports in 2-3 months) and using OBIEE.  I'm hoping the training will teach good programming practices (like not creating a new report every time because you hardcode the values.)

It has been suggested "yes it is possible to bulk-create the XML definitions of the OBI "direct database request" analyses."  Also, "As said before: by web services you can create analysis and inject the XML with your query for direct database request analysis. All you need is to define in the RPD physical database with connection pools containing the required username/password/host to point to your DB, the direct database request require the name of the connection pool to use when sending the contained SQL to a database (to know where to send it and using which credentials). So can definitely be automated quite nicely."

Any other insight?  I am new to the product so I have to learn what all of this means and how to do it.

FYI - Phase 1 is to use the EUL to generate a simple RPD and convert the reports so we can shut down the old servers.  Phase 2 is to create a Data Warehouse and re-write the reports to take advantage of this along with any new reports.  I'm hopeful that they'll see that effort through.  The timeframe is just too short to take on that endeavor.  This is a fire fighting effort for now.


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now