9 Replies Latest reply: Apr 18, 2011 10:30 AM by Amith Y RSS

    Best OLTP Reporting Tool

    837471
      Hi Gurus,

      My company asked me to build a reporting framework to support their needs on OLTP, i am trying to do some research on which tool is the best tool to do reporting on OLTP..

      Need help to find out a best reporting tool to make reports on OLTP data base

      Right now we are using OBIEE for our OLAP reporting and we are open to implement any tools, need inputs and comparisons between all the available tools and pros and cons also.

      Appreciate all your inputs/suggestion.

      Thanks,
      Seshu.
        • 1. Re: Best OLTP Reporting Tool
          Turribeach
          In my opinion the best ad-hoc relational reporting tool is Business Objects. I think it covers all the spectrum of features much better than other tools do. There are many good tools around so BO might be an overkill depending on your requirements.

          We did a side-by-side comparasion of how we use BO to migrate to OBIEE and we found several issues:

          1) OBIEE does not handle large data sets efficiently. OBIEE is not recommended to be used as an extraction tool. BO's Deski is able to handle millions of rows effectevely.
          2) OBIEE does not support context restrictions. BO context restrictions prevent users from running queries with incompatible attributes. OBIEE's requires separate Subject Areas for each "context". Large relational schemas will get broken in too many pieces.
          3) OBIEE does not allow OOTB to force adhoc users to apply filters on specific fields (i.e. force users to set a business date). There is a way to add this via custom JavaScript code but this does not work when using pre-defined filters. BO can force users to specify filters on columns OOTB.
          4) OBIEE does not allow full control on the organisation of adhoc attributes as only two levels or hierarchies is supported. BO supports N levels of hierarchy.
          5) BO supports metadata hierarchy using the master/slave universe feature. This can be used to inherit or cascade metadata within the enterprise. OBIEE does not support this.
          6) OBIEE requires the logical layer to be modelled as a star schema. BO supports all data models. OBIEE also requires 3 layers, BO only uses 2.
          7) BO supports OOTB multiple metadata repositories (universes) per server instance. OBIEE supports only 1 (RPD).
          8) BO supports the use of user-created data sources (Access DB, Excel Spreadsheet, CSV, etc) OOTB and without metadata changes. OBIEE only supports MS Office sources on Windows and it requires metadata changes to be used in reporting.
          9) BO supports OOTB joining multiple reports into one using the multiple data providers feature. OBIEE does not support merging multiple reports, unless they are of the same output (Oracle Union).
          10) OBIEE is a web-based tool and as such is limited to what web browsers can do. This might be OK for basic needs but for real power users BO's "fat client" approach works best.
          • 2. Re: Best OLTP Reporting Tool
            770748
            Do you have a blog?
            • 3. Re: Best OLTP Reporting Tool
              837471
              Thanks a ton Turry.. It's really a nice comparison.. how about qlikview or cognos ( we are also using cognos for some other purpose )

              forgot to mention couple of points.. the reporting tool should support Cloud Computing environment( i mean multi tenancy data).. security in multi tenancy environment.. good performance.. need to support complicated queries and calculations.. drill down capability.. easy web interface with existing java application..

              Thus Business Objects support all of the above features?

              Thanks in advance.
              • 4. Re: Best OLTP Reporting Tool
                edz
                I've been working with OBIEE more than four years. Statements below indicate lack of OBIEE knowledge. I did not get the point of some statements. However let me make some notes on subject:

                1) I guess you tried to pull thousands or report's rows on screen. Firstly OBIEE does pagination perfectly. Secondly human being cannot handle more than 30-50 lines physically. It is simple psychology. As soon as you've jumped on other couple pages you have no idea what was on the first pages. I've never seen such an idiot who would need report even with few hundred rows. Such reports are example of bad practice.
                2) Here you are right if it is all about dimensional model. However here is the difference between OLTP and data warehouse. In data warehouse users should be educated to know conceptual and logical models. BTW you are getting requirements from them and you say they don't know which dims can be pulled with facts and which ones cannot? Wow )) Also OBIEE does allow to pull such dims ( not connected to facts ) using very simple OBIEE modeling technique (level-based measures). That makes OBIEE reporting/analytics very flexible.
                3) OBIEE has GUI object called PROMPT. There you have full power to force users to apply filters. And you can easily pre-defined your prompt's filter values.
                4) OBIEE has different concepts of dims hierarchies and visual presentations. Here you are mixing them together. Indeed OBIEE 10g has such a restriction on GUI to have maximum two embedded levels. But it is only visual presentation. In business model you can have N levels of hierarchy and multiple hierarchies in dim . My goodness !!!Do you really think that ORACLE does not follow dimensional modeling ABCs? BTW please check Gartner's magic quadrants. ............BTW did you see OBIEE 11g demo ??????
                5) Inheritance...Hm, maybe it is simple dimensional modeling concept of supertype/subtype described in any BI/DW books? I am not arguing. maybe BO does it with one click. yeah, convenient enough, but no critical.
                6) Again just lack of knowledge of dimensional modeling in OBIEE. The one support ALL types of models: snowflakes (3NF) and star. Only what OBIEE requires you cannot have standalone logical table without any relations to at least one another table. Heh...I cannot recall such an example even from Bill Inmon. Regarding 3 layers. This gives you tremendous advantage over BO )) Why? It is long story))
                7) Here I agreed. There is nothing to say.
                8) You I understood you correctly ))))))) None of BI tools today can beat OBIEE on number of supported data sources. I'm just wondering. When you add new column in dim/fact table you DO NOT change your metadata????? My assumption is that BO does always "select * from <table name>"? Yes, then you don't have to change your metadata. The what kind of query BO generates against database??? select d1.*,f1* from dim d1, fact f1...????
                9) That is not true AT ALL
                10) You r kidding me )) have you forgotten in what time are we living??))) Do you read latest news? By 2016 most of businesses will be in social networks. Everyone wants to be power user holding mobile device in his hands!!!
                AND.....OBIEE already DOES it !!!

                Please forgive me to be so sarcastic. But today the only real advantage BO over OBIEE is PRICE. I would really advise you to look at OBIEE11g. It is almost different product then OBIEE10g.

                Best regards!!
                • 5. Re: Best OLTP Reporting Tool
                  Turribeach
                  1) I guess you tried to pull thousands or report's rows on screen. Firstly OBIEE does pagination perfectly. Secondly human being cannot handle more than 30-50 lines physically. It is simple psychology. As soon as you've jumped on other couple pages you have no idea what was on the first pages. I've never seen such an idiot who would need report even with few hundred rows. Such reports are example of bad practice.
                  I agree with that but you missed my point. Enterprises are large "beats" and as such they have many departments where they use different tools. In an ideal world you will have one and only one DWH with one an only one reporting tool on top. The thruth is that this never ever happens in reality. So in many cases users are asked to provide larde data extracts to use as sources for other reporting systems or external entities. OBIEE can not handle these data extracts properly as it not designed to work as an "extraction tool". This is what you missed. BO can handle them very well as it was designed as an extration tool in mind.
                  2) Here you are right if it is all about dimensional model. However here is the difference between OLTP and data warehouse. In data warehouse users should be educated to know conceptual and logical models. BTW you are getting requirements from them and you say they don't know which dims can be pulled with facts and which ones cannot? Wow )) Also OBIEE does allow to pull such dims ( not connected to facts ) using very simple OBIEE modeling technique (level-based measures). That makes OBIEE reporting/analytics very flexible.
                  You are only thinking dimensional. That's very good for an OBIEE developer. But the world is more than just dimensional. You can't answer all reporting requirements with a dimensional model I am afraid. So keep this in mind. A DWH is not the solution to all report requirements just like saying "a truck is the best vehicle in the world because it's big and can transport big loads". BTW have you tried to implement cross-fact analysis in OBIEE? Do you know that you need to set the content level for each non-confirmed dimension in each measure individually or OBIEE will bring back the measure as "NULL"? It's non-sense really, it's too tricky and too flaky.
                  3) OBIEE has GUI object called PROMPT. There you have full power to force users to apply filters. And you can easily pre-defined your prompt's filter values.
                  Again you missed a key word on my statement: "adhoc users". Prompts are fine but don't exist unless the OBIEE developer creates them in advance, typically in a dashboard. I am talking about ad-hoc here so you know that's Answers. Try to restrict Answers users and force them to put a filter on a column and you will know what I mean. BO can do this in the metadata very easily. OBIEE can't. Prompts are fine (*) but they need to be created in advance by the report developer. (*) Actually they are not that fine, you can't even force them OOTB to use a prompt or not run a dashboard without any values. Sure there are work arounds, but such a basic feature should be part of the product!
                  4) OBIEE has different concepts of dims hierarchies and visual presentations. Here you are mixing them together. Indeed OBIEE 10g has such a restriction on GUI to have maximum two embedded levels. But it is only visual presentation. In business model you can have N levels of hierarchy and multiple hierarchies in dim . My goodness !!!Do you really think that ORACLE does not follow dimensional modeling ABCs? BTW please check Gartner's magic quadrants. ............BTW did you see OBIEE 11g demo ??????
                  Yes it's only visual presentation, but that's one of the most important things! It's a stupid restriction. If you can have N levels on the BMM why shouldn't you have the ability to prent the data in more levels. Yes, I have seen the magic quadrant and Microsoft is on top of OBIEE (see http://www.gartner.com/technology/media-products/reprints/microsoft/vol2/article15/article15.html). Yes I have seen OBIEE 11g and have running on one of our Dev boxes. It's one of the most buggy releases I have seen in years. Only last week the client tools were released for Windows. How can you have proper ecosystem without proper client tools? No Solaris or HPUX release so it's a partial release. No support for other J2EE web app servers. From our point of view OBIEE 11g is a beta release. We are seating and waiting for Oracle to clean it this mess and get the product stable. And if you think Oracle Support will help read this: http://searchoracle.techtarget.com/news/2240031826/Has-the-phrase-Oracle-Support-become-an-oxymoron
                  5) Inheritance...Hm, maybe it is simple dimensional modeling concept of supertype/subtype described in any BI/DW books? I am not arguing. maybe BO does it with one click. yeah, convenient enough, but no critical.
                  Consider this problem. You have a large DWH that a large organisation wants to share with different departments which they would then extend adding they own data sources and customise to their own needs. How would you handle changes in the "core" model that need to feed to the different departments metadata stores? RPD merging and MUDE is a mess to be honest, it barely works and it's too prone to human error. Once you worked in a very large project with many OBIEE developers working in different parallel projects you will realise that inheritance is the only sensible solution. I wish Oracle would see this as well. If the metadata was in a database it would have been so much easier...
                  6) Again just lack of knowledge of dimensional modeling in OBIEE. The one support ALL types of models: snowflakes (3NF) and star. Only what OBIEE requires you cannot have standalone logical table without any relations to at least one another table. Heh...I cannot recall such an example even from Bill Inmon. Regarding 3 layers. This gives you tremendous advantage over BO )) Why? It is long story))
                  You can support all models in dimensional, but should you be forced to use it? 3 layers is not advantage. It's an interesting aproach to solve how you need to model things in OBIEE. You have become too used to it and now think it's the best in the world. It's not, in a relational reporting solution 2 layers are more than enough (physical with all your joins and presentation with all your business names and hierarchies).
                  8) You I understood you correctly ))))))) None of BI tools today can beat OBIEE on number of supported data sources. I'm just wondering. When you add new column in dim/fact table you DO NOT change your metadata????? My assumption is that BO does always "select * from <table name>"? Yes, then you don't have to change your metadata. The what kind of query BO generates against database??? select d1.*,f1* from dim d1, fact f1...????
                  OK consider this requirement. You work for a top US bank. You have invested $5m in the state-of-the-art DWH with OBIEE that covers every requirement you may even need. The US FED comes in and tells you that it wants one of your excellent reports but filtered for a number of companies. They give the user a spreadsheet with 20k companies that you need to filter you report on. Can any user in OBIEE perform the filtering without any changes in OBIEE? No. In BO you can. End of the argument.
                  9) That is not true AT ALL
                  You saying that OBIEE can "merge" two Answers reports and get attributes from both queries in an INNER/OUTER/FULL OUTER fashion? Please show me how, I love to learn from you! (BO indeed can).
                  10) You r kidding me )) have you forgotten in what time are we living??))) Do you read latest news? By 2016 most of businesses will be in social networks. Everyone wants to be power user holding mobile device in his hands!!!
                  AND.....OBIEE already DOES it !!!
                  The web client works for 90% of the user population, as said in my comment for real "power users" a fat client approach works best. There browsers are still not powerful enough to handle large data sets. Going back to Gartner's 2011 Quadrant for Business Intelligence Platforms, which you seem to love, I quote from it: "[...] it (Oracle) has lacked innovation around mobile, in-memory, consumerization, interactive visualization and search [...]".
                  Please forgive me to be so sarcastic. But today the only real advantage BO over OBIEE is PRICE. I would really advise you to look at OBIEE11g. It is almost different product then OBIEE10g.
                  I love sarcasm. Sarcasm is my middle name. But you need to open your mind a bit. OBIEE has taken control of your brain! :-) One solution does not fit all. The world of IT is too big for that...
                  • 6. Re: Best OLTP Reporting Tool
                    gerardnico
                    I like this conversation ;-) Really ;-)

                    @turribeach
                    For the point 9, here the solution:
                    http://gerardnico.com/wiki/dat/obiee/multiple_subject_area
                    You have to write your own SQL. In 10g, the GUI doesn't help you
                    in this task whereas in 11g, it can help you.

                    I'm not a great practitioner of BO then it's not easy to give all pros and cons.

                    It depends of your needs, of the budget and of the knowledge that you have of the products.

                    The semantic layer
                    What I like in the semantic layer of OBIEE is that you have a third layer ;-)

                    The design of OBIEE is build on a declarative way.
                    http://gerardnico.com/wiki/declarative
                    i.e. you say what you want in a logical way based on a relational
                    star schema/cube and not how.
                    To define a level based measure, you just have to tag the column with
                    the good level and the BI server do the rest.

                    The language SQL is a good example of declarative language.
                    You don't say how you want physically retrieve the data.
                    It's a logical representation.

                    With BO, to avoid the sql data modeling issues:
                    http://gerardnico.com/wiki/data_modeling/issue
                    you have to define context.
                    A context is a query path. You prohibit the use of joins.
                    It's not the same way to think.

                    On a side, you think multidimensional with OBIEE (you have to define
                    dimension, fact, ...) and in the other side with BO, you have to think
                    as a SQL developer.

                    The two approaches work but I prefer the first one as it's a logic that is closer
                    to an OLAP point of view.

                    BI Tool
                    OBIEE is a suite of BI tool. You have:
                    - a semantic layer (repository for OBIEE, universe for BO)
                    - a dashboard tool (Dashboard and Answer for OBIEE, Xcelsius for BO)
                    - a Pixel Perfect Report tool (Business Intelligence Publisher for OBIEE, Webi Deski Crystal Report for BO)

                    I have to admit that the BO tool have a better GUI and is easiest to use for an end user.
                    Oracle have made a lot of improvement in 11g on this point but I didn't have made any test for the moment.

                    I don't have a lot of knowledge of Xcelsius then I can do a great comparison.
                    I have followed the getting started guide and I've found the OBIEE dashboard easier to use.

                    OLTP Reporting
                    The two tools can do it really well. You can bypass the semantic layer and then imagine all type of dashboard of report.
                    The positive point is that if you have already OBIEE, you have already Business Intelligence Publisher and you don't need to pay it twice.

                    Web Browser or Rich Client ?
                    The advantage of a Web Browser application is that you don't need any installation.
                    That you can access it from any computer with a simple browser.
                    It's for me already the future.

                    It's true that the weak point is when you need to handle a lot of data. As have said Turribeach, Desktop Intelligence
                    was designed as an extraction tool. From a reporting point of view, it's not needed as you have still to rework your data to gain insights. The Oracle direction seems to be to give the preference to an add-in in a spreadsheet application.

                    From an architecture point of view, I like the cloud and you can see that all new developments take this direction in a multi-tier architecture.

                    Conclusion
                    Write your specifications, see if the two tools can do them, get the prices, your architecture, your knowledge of the tools and take the best solution.

                    I think even so that OBIEE 11g is more advanced on map visualization and data integration but may be it's due to my lack of BO knowledges ;-)

                    This was my 10 cents ...
                    • 7. Re: Best OLTP Reporting Tool
                      edz
                      :) Alas and alack! At some points I should agreed with you))) Race after the market eventually decrease product quality.
                      My last 2 cents: recently I have implemented extraction from OBIEE though iBot (BI scheduled job) which runs Java program. And it pulls big result sets easily and fast. However the output is flat file only (but who needs more?)

                      Anyway, it was a great pleasure to talk with you!
                      Have a nice day!
                      Sincerely,
                      Ed
                      • 8. Re: Best OLTP Reporting Tool
                        RussProudman
                        Ok - to the original post ... what's the name of that product again ... oh yah ... Discoverer.

                        Great tool, integrates with BI Publisher, yada, yada, yada.

                        I'd go Disco for OLTP and OBIEE for OLAP.
                        • 9. Re: Best OLTP Reporting Tool
                          Amith Y
                          WOW! Great comparision between BO and OBIEE.

                          Thanks