Forum Stats

  • 3,874,156 Users
  • 2,266,675 Discussions


Table Joins in Discoverer !!!

12412 Member Posts: 15
edited Mar 12, 2010 3:16PM in Discoverer
I am new to the Discoverer Tool.. Can any one please tell me how
does one go about creating joins of tables in discoverer when
creating a workbook..



  • 6399
    6399 Member Posts: 3
    You need to create the joins in the Admin edition of Discoverer.

    You can't create joins in the User/Plus/Viewer editions. Once
    the joins are created and you have selected the first column to
    include in the workbook, the joined tables will be highlighted.
  • 44099
    44099 Member Posts: 4
    Note: you may also want to create views which contain the
    joined tables and then import the view as a table into Admin.
    You could also use the "custom folder" option to create a view
    in the Admin. module.
  • 13067
    13067 Member Posts: 8
    I just wanted to add that you can create Custom SQL folders in
    the Admin edition. They equate to views and are very flexible
    when you want to create joins between tables where there are no
    FK constraints between two or more tables.
  • 14728
    14728 Member Posts: 633
    Lisa is correct the tool to use for this is the Admin Edition.

    Worth adding that when you create your EUL joins will
    be picked up from any foreign key definitions you
    have on your schema for Tables.

    If you want to create joins between views however, these
    will have to be manually created in the EUL.

    Good Luck.
  • 512745
    512745 Member Posts: 11
    I am new to Discoverer too and i am finding that the un-ability for users of plus to create joins is going to be a BIG problem.

    Can someone please tell us WHY? they did it this way?
  • Russ Proudman
    Russ Proudman Member Posts: 2,079
    This is a potentially huge question.

    But in a nutshell, before Discoverer, Oracle had a tool that allowed end users to perform joins and then run reports (well their version of a report). Unfortunately, your average end user is not an Oracle technical person and therefore, in my opinion - and like it or not, I've seen it over and again - the end user screws up.

    An end user has no idea of the ramifications of joining Oracle tables / views. Hell, I've been working with Oracle / Apps, etc. since around 1995 and I still don't know every time I sit down to work. Sure, we all know are favourite tables / views, but thank God for TRMs to actually try to figure out what the heck is going on - and even then, crossing our collective fingers.

    So, the idea of a middle layer was created. At just around the same time, the other big report companies did the same thing. Cognos created the catalog, Business Objects the universe, etc.

    So, the idea is a Discoverer Administrator has the brains - and the Oracle technical knowledge - to either bring the tables in and join them (or write SQL in folders directly, etc.) or create database views that work and then point at them via the EUL. Then, there's about 3 million and two, other things you should do in the EUL as the Admin (ie: security, privs, LOVs, hierarchies, alternate sorts, pre-calculations, pre-conditions, sorting of folders, defaults to set, scheduling of reports, materialized views, etc., etc.).

    So, if a company has a good Disco Admin, it can make a huge difference to reporting at a company.

    It may seem dumb when you first come across it, but in the something like 10+ years I've been working with Discoverer, it makes alot of sense ...

  • 512745
    512745 Member Posts: 11
    Russ, thanks for your response. it makes sense in some environments but not all.

    We tend to have reporting users who are pretty savvy at joins. We don't want them to be admins but plus users (report writers) and should have the ability to do joins. Focus and WebFocus has had this ability for over 15 years. I am beginning to see why we might need a more robust reporting tool than discoverer.

    Oh well, live and learn. La de da.

    By the way this forum is great and there are a lot of good things about Discoverer we are just unhappy with this part and it is going to slow us way down......
  • Russ Proudman
    Russ Proudman Member Posts: 2,079
    edited Nov 21, 2006 5:44PM
    I would guess it comes down to using the correct tool then for what you want. The thing that you find a hinderance is actually what many companies fully support (ie: the Sarbox boys and girls don't like end users doing joins, etc. in PROD as if they screw up and base numbers on it ... you get the idea).

    I'm wondering if you have end users who are 'intelligent technical types' then you could look into using TOAD, or for that matter, the new Oracle freebie, SQL Developer. Both allow you to create reports against the Oracle tables / views, etc., create your own view / tables, joins, etc. and may be more to what you want.

    I'd be worried about allowing that much power, but I was at a client once that actually based all their custom reporting against TOAD. So it is possible.

    But, the theory is that with a good Disco Admin who sets up all the logical joins, etc. and write the views in the database, etc. and works with your power users, then they should be able to manipulate reports from there (ie: using desktop or plus). And when I say a good Disco Admin, I mean someone who works with the report writers on a recursive process to keep adding what they want.

    If you're in an Oracle Apps environment and you allow users to perform joins, etc., then you've definitely got more 'cahoonies' than I.


    PS. Oh, and don't worry about maligning Discoverer if it's warranted. Many on this forum have been using it for years and can come up with a list of bad things about it that should be fixed / updated. However, I would strongly doubt that Disco will ever do what you're wanting as it's just not part of the product's mandate.
  • Hi
    If I may throw my tuppence into the mix, and come in on the defence of Oracle, I would say that the whole point of having an EUL is to protect the users from having to know the database. This is one of Discoverer's great strengths. Rememeber, when using an EUL you are NOT seeing the database objects directly but a semantic layer that sits between the users and the database. This layer not only manages the joins but manages security to sensitive data and prevents users from making joins to areas where they should not looking. It also has the ability to make the data available in a style which is infinitely more palatable than the core database tables - namely a star schema.

    I have to say that in my opinion if users find that the joins they need are not there then they are working on a poorly designed EUL that needs fixing. The correct approach would be to ask the administrator to add the required joins. These could then be done correctly, taking into account that some joins need multiple columns, that some joins are 1:N whereas others are 1:1, and that some joins are outer joins either on the master end or the detail end.

    End users are not IT professionals and should not be forced into having to know this information in order to create a report. The tools should be so well designed that users should only need to drag in what they need with all the joins being slotted in seamlessly by the tool, preferably in a star schema format.

    Well I know I was only supposed to input my tuppence but it seems more like sixpence. Sorry about that but I am one of the Discoverer folks who fully support Oracle's approach and so am against users having the need to create their own joins. If an organization does not have the admin bandwidth to support this approach then that is a serious issue because Discoverer will not function at its best without a good, dedicated administrator.

    Best wishes
    Michael Armstrong-Smith
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy

    Nobody has mentioned that you can of course devolve the Discoverer Administration to many users and in some environments it makes sense to do this. So in this environment you have some 'lightweight' Discoverer Administrators who can create folders and joins within their business areas but do not have full Administration privileges over the whole EUL. You can set this up in a secure way so that only users that you are confident have the ability to create the right structures in the EUL are given access to the EUL.

    These users will need access to the desktop Disco Admin tool, but this is the right tool for maintaining the EUL.

    Rod West
This discussion has been closed.