3

We have our data marts/warehouse on Oracle 11g implemented as a star schema. Business reports are designed using OBIEE. I come from a ETL background and have very little knowledge in OBIEE.

Once the OBIEE RPD is designed, I see that OBIEE starts generating SELECT queries in the background to feed data into the reports. On many occasions, I have noticed that the SELECT queries are not optimized (big fact table is fully scanned more than once in separate WITH clauses).

When the report performance is bad, the OBIEE queries are sent to the ETL team for performance tuning. I'm confused about how I can tune them because they are auto generated. I know there is an option to write custom sql in OBIEE (without going via RPD) for each report, but our standards do not allow that and I also think it does not leverage the benefits of OBIEE.

Has anyone faced a problem like above? How to tune such queries?

James Z
  • 12,209
  • 10
  • 24
  • 44
museshad
  • 498
  • 1
  • 8
  • 18

4 Answers4

2

Firstly, you're right that custom SQL (known as direct database query) is not a good idea in principle, though it is useful on occasion. But it's not the solution to your problem.

Tuning the OBI queries generated is an OBI RPD task, for the OBI developer; tuning the database for the OBI queries generated is a database/ETL task. But you can't really do one without the other – OBI needs to be designed so as to generate suitable queries, and the database needs to be designed in such a way that suitable good queries can be generated to answer the question being asked.

OBI is basically a SQL generator, and if the RPD model is bad suboptimal, then the resulting query will be bad suboptimal. OBI will generate SQL based on the information it has in the RPD about the layout and structure of the data and database.

You're obviously coming at it from the database side, and so to you the SQL is bad because it isn't what you'd write. It's also possible that the database design is bad for getting an answer to the question that OBI is being asked.

jackohug
  • 440
  • 2
  • 9
  • Thanks for the comments. I understand your point. I'm trying to see if I can get more specific information- are there any indicators to say that the RPD is not well designed to generate optimal OBI queries? Similarly are there any indicators that the star schema is not well designed to execute the OBI queries optimally. – museshad Mar 21 '16 at 11:28
  • @museshad There aren't really an indicators, it comes down to knowledge of the data and skill with the RPD/database. – jackohug Mar 21 '16 at 15:49
2

As jackohug says, OBIEE is a SQL generator, and the general aproach is to try to optimize the query generated by OBIEE, not try to change this query. Somehow, depending on the performance problem, you can try some tricks. First all, is your table partioned and your reports can benefit from the partioning? Second, add indexes on the fact table so any filter on the dimensions can benefit the access to the fact table. Third, building agregate tables, resuming the fact table, so when reports don't show much detail you first access to the agregate table with much less data, and is only as the users drill down through structure (and while doing so, they are applying filters to the data they are interested in) that they access to the much detailed fact table but applying filters to avoid full scans. You could also tell OBIEE to use hints when accessing to the table, although, as with Direct Database Query I wouldn't recommend it, I would try first optimizing using the first three aproaches. Regards

Ana GH
  • 1,397
  • 1
  • 9
  • 19
1

if you have diagnostics and tuning pack licenses, you can run the SQL Tuning Advisor. The SQL Tuning Advisor is running the optimizer in tuning mode and it may be able to generate a SQL Profile with a better execution plan. Sometimes the advisor recommends indexes for tuning as well. Both SQL Profiles and indexes do not require a change to the application.

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • Thanks Roger. Yes, I understand that the execution plan can be analyzed and any index could be added to improve performance. What if the OBIEE generated query is bad, is there a way to handle this? – museshad Mar 18 '16 at 07:46
  • I'm not familiar with OBIEE, but I did a lot of Object Relational mapping from Java to Oracle. In this case the O-R mapper would occasionally generate poor performing SQL with complex join conditions. To solve in those cases I would usually create a tuned join view on Oracle side, then map to that. The side benefit was that If I needed to tune the query in the future, I didn't need to touch the app side, just the Oracle side. I think the same concept could be used on OBIEE where custom query is created against the view rather than the base tables. – Roger Cornejo Mar 19 '16 at 10:45
  • Thanks Roger. There is a way to write custom SQL(just like your views) in OBIEE but that is generally not a good practice because once you do that you can do other RPD optimizations(example: applying any statistical functions) within OBIEE. – museshad Mar 21 '16 at 11:30
  • One option along those lines could be to implement some materialised views with query re-write, but that's solving the symptom rather than the cause. – jackohug Mar 21 '16 at 15:50
0

I've yet to have much success with the SQL tuning advisor. Some experience in SQL tuning and a bit of research can typically produce a far better plan.

If all the layers are built well and all you need is a final tweak then add a hidden column to the start of the report (Answer/Analysis) with a SQL hint.

I'd be very careful about adding hints through the RPD layers because of the many different and unexpected ways that others will join and use the tables.

WhoWhatWhere
  • 1
  • 1