Posts

Posts

  • 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 YenikepalliBronze Medal: 1,250+ 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 VaithianathanGold Medal: 3,500+ 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 GubarRed Ribbon: 250+ 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.

  • 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  
  • Theophile Fieche
    How to modify the content of a column through a result...5.0
    Topic posted May 7, 2019 by Theophile FiecheGold Medal: 3,500+ Points, tagged Core HR, OTBI, SQL in General OBIEE public
    Title:
    How to modify the content of a column through a result similar to RPAD
    Summary:
    How to modify the content of a column through a result similar to RPAD - we want to create a report that will need to have a specific lenght per column & wish to have a result similar to RPAD function
    Content:

    Hi,

     

    We want to create a report that will need to have a specific length per column & wish to have a result similar to a RPAD function.

    In other words, the length of the column values is determined (for example 30 characters) and the column is filled with the data of the element selected in the analysis + spaces until it reaches 30.

     

    Is this possible through an analysis or a Logical SQL function (OTBI seems to use Logical SQL which doesn't know the RDAP function and therefore doesn't allow us this action until now ...).

     

    best,

     

    Theo

  • Shalindra Singh
    Is Orders data available through the OTBI layer in Order...15.0
    Topic posted November 20, 2018 by Shalindra SinghBlue Ribbon: 750+ Points, tagged BI Publisher, OBIEE Answers, OTBI, Report Output, Setup/Administration, SQL in General OBIEE public
    Title:
    Is Orders data available through the OTBI layer in Order Management / SCM?
    Summary:
    Is Orders data available through the OTBI layer in Order Management / SCM?
    Content:

    Is Orders data available through the OTBI layer in Order Management / SCM?

    Reason I am asking because when I querying direct table (doo_header_all) via BI Publisher, I get the data from this table.

    But when I use 'Issue SQL' on OTBI Administration section and write following query - 

    select_physical

    headerId, OrderNumber

    from "FscmTopModelAM.DooTopAM.Header"

    I don't get any records. 

    I need your help as I was asked not to use BI Publisher for data extraction (where I want order and line data to be extracted in every 10 minutes) hence evaluating data sync tool but I do not get any data when I used above query.