nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

sql to obiee

User538247

Hi,

Need to convert 200+ sql queries to obiee answers report by adding new features such as hierarchy n all but data should be intact. NO time to follow traditional data model approach, does someone find out fastest way to convert sql to rpd modelling.

Dont want to use views.

thanks,

Find more posts tagged with

Accepted answers

All comments

cesar.advincula.o

Hello,

There is no utility from obiee to convert from sql query to RPD in a direct way,

first, if we want to construct an RPD model, we need to analyze the business requirements, define your dimensional matrix, analyze

our transactional database model, etc, for create what is the best model RPD and fullfil technical and functional requirements.

However, there is a Oracle recommendation, in the section "Model First Development Methodology" which is part of a official course

Oracle Build Repositories https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&get_params=dc:D74747,clang:EN

, there is some key steps to create the RPD model:

In resume,Oracle BI recommend and top-down approach that focuses on the consolidation and abstraction of core business

requirements irrespective of the underlying physical architecture:

1– Build business model first (Dimensional Matrix), get the business requirements an construct the dimensional matrix.

2– Integrate with underlying physical architecture.

3– Quickly deploy baseline to end users.

4– Pursue iterative development based on user feedback.

Kind Regards,

rmoff

User538247 wrote:

Hi,

Need to convert 200+ sql queries to obiee answers report by adding new features such as hierarchy n all but data should be intact. NO time to follow traditional data model approach, does someone find out fastest way to convert sql to rpd modelling.

Either:

1) Do a lift & shift hack job, stick all the SQL in direct database requests. Miss the point of having OBIEE in the first place entirely

2) Take the time necessary to model your RPD properly.

If there were a magic click-button to do (2) then it would already exist. There isn't. You need to spend the time. People with experience will be able to do it more quickly than those without.

DanPryke

Agree with @rmoff, direct database request would work but then you wouldn't get your hierarchical data and OBIEE is a glorified SQL Dev.

I would question:


NO time to follow traditional data model approach.

Who's in charge of the timescale? If the user is saying "I need this in a week", you need to tell them it takes time to dimensionally model data and properly model the RPD.

If you've given them a really short timescale and given them unrealistic expectations then you need to do the same...

[Deleted User]

Almost everything needing to be said has already been said in here but just to drive home one point:

NO time to follow traditional data model approach

...is just about the worst thing one can give you as an order when doing OBIEE - or any other structured analytical project.

You know the saying "temporary solutions have a habit of becoming permanent"? Think about all the money spent on the solution, the licenses etc when you'll end up with hundreds of Direct Database Request (DDR) analyses that no one likes because they don't offer any functionality. Tool not utilized, users not happy, money thrown out of the window...nice way to destroy a project.

And one thing about functionality although that means contradicting @rmoff

by adding new features such as hierarchy n all but data should be intact

DDRs will not give you hierarchies and NOTHING which bypasses the RPD will give you hierarchies! You have to have things modelled in the RPD in order to have access to these functionalities!

cesar.advincula.o

Hello,

I agree with @Christian Berg

however you ask about the RPD, and in my opinion DDR in Answers , also you could use Oracle BI Publisher option, is an optional solution but is not an elegant solution,

it is kind of some exit to your requirement,

Kind Regards,

User538247

Thanks all for replying....let me clear few points....its actually transnational reports with no etl n no data model. each report query almost 100 of lines of sql code with inline or subquery or pl sql functions. modelling this kindoff sql is bit tricky in obiee rpd. can anyone suggest some shortcuts to convert this kindoff sql in rpd modelling.

thanks,

Joel Acha

All the responses to your original question couldn't be more clearer. There is no shortcut to properly define, develop and build your repository.

You'll have to build your model and then build your reports on top of that. You should spend your time doing this and managing your users expectations as there is no quick fix to building an Rpd model. You'll experience loads of problems down the line if you attempt to take detours at this stage of your project.

rmoff

Same question...same answer

Either:

1) Do a lift & shift hack job, stick all the SQL in direct database requests. Miss the point of having OBIEE in the first place entirely

2) Take the time necessary to model your RPD properly. Transactional, dimensional..whatever. The data will have a logical model to it, and *can* be modelled in the RPD if you're willing. If not, see above.

[Deleted User]

Tricky yes, but you asked for hierarchies and only the RPD can provide those. Other than that +1 to whoever spoke out in the last 30min.

cesar.advincula.o

Hello

Yes, you ask about some mechanism to construct RPD, however I said this direct mechanism not exist, we told some steps that Oracle recommend

to construct your RPD, model with key steps, that you have to do it if you want to construct your RPD model.

Kind Regards