Posts

Posts

  • Maarten van der Burg
    What application do you use to write your SQL74.8
    Topic posted October 25, 2019 by Maarten van der BurgSilver Medal: 2,000+ Points, tagged BI Publisher Data Models, Data Models, Fusion, Reports, SQL in General OBIEE public
    Title:
    What application do you use to write your SQL
    Content:

    I'm very curious as to what other people use to write their SQL. Personally I am using NotePad++ (with the SQL language setting to help with syntax).

    I do feel that there could be more software assistance and there might be some more automated applications out there which allow searching the repository, automatic joins, etc. Having a tool like this would speed up my work tremendously.

    Would like to discuss in this topic what you use and what you like/dislike about it.

    Regards,
    Maarten

  • Praveen Kumar
    How to get and SQL for our own OTBI AnalysisAnswered35.0
    Topic posted May 15, 2019 by Praveen KumarRed Ribbon: 250+ Points, tagged Analyses, BI Publisher, BI Publisher Report Templates, Customizing Reports, Data Models, OBI Answers, OBIEE Answers, OTBI, SQL in General OBIEE public
    Title:
    How to get and SQL for our own OTBI Analysis
    Summary:
    SQL for OTBI Analysis
    Content:

    Hi All,

    How to get and SQL for our own OTBI Analysis.I know we can get this in the advanced tab but there we cannot see the sub query and all.

    Where to get the SQL including sub query.Also help me with the navigation.

    Thanks in Advance.

    Regards,

    Praveen G.

  • Jean-Pierre Dijcks
    Welcome to the Big Data Forum25.0
    Topic posted July 19, 2018 by Jean-Pierre DijcksSilver Medal: 2,000+ Points, tagged Big Data Appliance, Big Data SQL, Data Management, Hadoop, Python, Spark, Spatial and Graph, SQL, Tip, Zeppelin in Data Management > Big Data public
    Title:
    Welcome to the Big Data Forum
    Summary:
    Welcome message for the big data cloud forum
    Content:
    Welcome everyone to the new Big Data Cloud Service (BDCS) Community.

    Here you'll be able to ask any questions you have about big data. Ranging from the BDCS service itself, to its components including Big Data Manager, Cloudera CDH, Data Science leveraging ORAAH, Spark, Python, our Zeppelin notebook support and Spatial and Graph, as well as the SQL engines like Big Data SQL, Impala, Hive and more.

    Remember that this is a community site and the people helping here are all part of a community of big data enthusiasts - so please be respectful and helpful.

    In order to get the best answers as fast as possible it is important that you'll provide some basic information in your post.

    • Have a meaningful title to your post
    • If you got an error - tell us what the error specifically says and if you have logs or details, provide them
    • If you are trying to do something - it might help us if you tell us why your are trying to do this - sometimes there are other ways to achieve the same goal.

    Looking forward to having a great conversation and community.

  • Nina Manns
    Using an SQL Date Formula in OBIEE Report84.0
    Topic posted September 20, 2018 by Nina MannsRed Ribbon: 250+ Points, tagged Reports, SQL in General OBIEE public
    Title:
    Using an SQL Date Formula in OBIEE Report
    Summary:
    How do I display previous 30 days of data
    Content:

    Could someone please show me how to tweak the below formula.  I’d like to pull data from the previous 30 days; however, it seems to be pulling for the future 30 days instead. 

                   timestampadd(sql_tsi_day, 30, current_date)

    When I change the formula to the below example it gives me an error and doesn't return any data:

                    timestampadd(sql_tsi_day, -(30), current_date)

    Thank you.

    Version:
    OBIEE 11.1.1.9.0
  • Koteswararao Yenikepalli
    Need A Report To Find Out The PR Is Pending With Whom And...14.7
    Topic posted January 16, 2019 by Koteswararao YenikepalliSilver Medal: 2,000+ Points, tagged BI Publisher, BI Publisher Reports, BICS, Business Intelligence, OTBI, Procurement, Product Development, Report Scheduling, Reporting and Analytics, Sample Reports, SCM, SQL, Tip, White Paper in Reporting and Analytics for SCM public
    Title:
    Need A Report To Find Out The PR Is Pending With Whom And Its History
    Content:

    We are looking for some SQL  query to find out the requisition is currently pending with whom and what is the history of the approval 

     

     

    To achieve this requirement is there any report or SQL query to find out 

     

     

    Thanks In Advance,

    Koti

    Version:
    R13 18D
  • Senthilrajan Vaithianathan
    Oracle Fusion OTBI Direct Database Query/Report95.0
    Topic posted February 4, 2019 by Senthilrajan VaithianathanBronze Trophy: 5,000+ Points, tagged Analyses, BI Publisher Data Models, Customizing Reports, Data Models, Discussion Forums, Fusion, OBI Answers, OTBI, Report Output, Reports, Setup/Administration, SQL in General OBIEE public
    Title:
    Oracle Fusion OTBI Direct Database Query/Report
    Summary:
    Querying Oracle Fusion OTBI feature is being deprecated for CLOUD environment from 19A releases
    Content:

    Creating Reports with Direct Database Query in Oracle Fusion OTBI is being deprecated for CLOUD environment from 19A releases.

    Refer Doc ID 2141412.1

    Regards

    Senthil

    Image:
    Code Snippet:
  • Marty Gubar
    Big Data SQL 3.2 - Cloudera Certified5.0
    Topic posted October 29, 2018 by Marty GubarBlue Ribbon: 750+ Points, tagged Analytics, Big Data Appliance, Big Data SQL, Data Management, Hadoop, SQL, Tip in Data Management > Big Data public
    Title:
    Big Data SQL 3.2 - Cloudera Certified
    Summary:
    Big Data SQL 3.2.1 is now officially certified with Cloudera 5.14.1
    Content:

    Big Data SQL 3.2.1 is now officially Cloudera Certified (see Oracle certified products here).  The certification took place with Cloudera 5.14.1.  The testing captures real world functional integration usage of the product on a cluster that meets certain security requirements, including:

    • Kerberos
    • Sentry
    • HDFS Encryption
    • Auditing
    • Network Encryption. 

    It also examines the product architecture, including integration with Cloudera Manager and key CDH Components.  Finally, it reviews how the product leverages scale-out processing on Hadoop clusters.

  • Tim Welsh
    Setting relative dates in a filter for an OTBI AnalysisAnswered173.7
    Topic posted April 28, 2017 by Tim WelshBlue Ribbon: 750+ Points, tagged Analyses, OBIEE Answers, OTBI, Reports, SQL in General OBIEE public
    Title:
    Setting relative dates in a filter for an OTBI Analysis
    Summary:
    In an OTBI Analysis is there a way to add into a relative date range into a filter?
    Content:

    I need an Analysis with a filter to pick up dates within the last week.  I don't see a way to do this in the normal filter properties and I'm getting errors when i add the below code into "Convert this filter to SQL"  Is there a way to do this either with the regular filter or with the Advanced SQL filter?  

     BETWEEN (SYSDATE - 7) AND SYSDATE

  • Jean-Pierre Dijcks
    Cloudera 6 released - when will we have it on BDCS/BDCC &...4.5
    Topic posted August 31, 2018 by Jean-Pierre DijcksSilver Medal: 2,000+ Points, tagged Big Data Appliance, Data Management, Hadoop, Spark, SQL in Data Management > Big Data public
    Title:
    Cloudera 6 released - when will we have it on BDCS/BDCC & BDA
    Summary:
    Quick update on Cloudera 6 on BDCS and BDA as well as BDCC
    Content:

    With the release of C6 on August 30th, we are now working towards releasing this on the Oracle Big Data stack (cloud and on-premises).

    We did want to ensure that we communicate some of the important functionality bits and when what would arrive, purely based on the Cloudera schedule:

    (1) The current 6.0 version does not really support Erasure Coding yet. This is because, while the HDFS service supports it, the query engines are not yet running on Erasure Coding. Consequently, you will only be able to do some testing with Erasure coding in terms of cold storage or experimenting with the sizes and reduction in sizes you could achieve. Erasure coding will arrive fully in C6.1.

    (2) Almost all major components in C6 will do a major version uptick. So, rather then wait for C6.1, Oracle will uptake 6.0.1 on BDA, which will enable our customers to move to these newer versions of the components and make use of the new functionality asap on the Oracle Big Data platforms. This will include an upgrade from 5.x to 6.0.1.

    Stay tuned for a more formal update with details on migration, versions, dates etc. which we will post here and on our blog in the next week or so.

  • Kim Welker
    Return Candidate Name only for Specific CSW Selection...114.0
    Topic posted March 2, 2017 by Kim WelkerBlue Ribbon: 750+ Points, tagged Analyses, Customizing Reports, OBI Answers, OBIEE Answers, SQL in General OBIEE public
    Title:
    Return Candidate Name only for Specific CSW Selection steps/statuses
    Summary:
    How to modify my SQL statement to remove redundancies
    Content:

    Hi!  I've created a report to return all Open Requisitions as well as Filled Requisitions for the past year.  The report filters work well, however I have additionally created a field using the following SQL statement in an attempt to display Candidate Name only when they match specific Steps and Status conditions:

    CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" ELSE ' ' END

    The names are populating as desired, however I am getting an additional row for Requisitions that not only have an Offer/Hired Candidate against them, but also have other Candidate submissions which simply aren't named based off the above SQL statement.  Basically the extra row is representing a placeholder for all the other Candidates that have applied but do not meet the conditions of the SQL statement. 

    Any ideas on how to suppress those extra blank rows?  If a Req does not have anyone in the Offer/Hire process, then I would still want the Req. to be displayed.  In that case the Candidate Name should be blank.

    Below is a sample of what's happening, however the attached doc is a littler easier to read.  Thanks in advance for any advice!

    Requisition Number_Position Number Name
    0044262_P0048293 John Smith
    0044262_P0048293  
    0044360_P-0019999 Jane Doe
    0044360_P-0019999  
    0044365_P0048452 Charlie Johnson
    0044366_P0048453 Sally Thomas
    0044367_P0048454 John Doe
    0044372_P0048457 Jeff Johnson
    0044372_P0048457