SQL to fetch 200+ rows – Direct Database Query – Obsolete
SQL to fetch 200+ rows – Direct Database Query – Obsolete

 1,020 total views

This article is obsolete now as Oracle removed Direct Database Query option.

Please check out the alternative to run adhoc queries.

When we create a Data Model using SQL Query, the max number of rows that we can fetch using the Data Model is only 200 rows unless we create a layout, tag it to data model and run the report.

Many a times, we would need to run queries on ad-hoc basis and need to get the complete output (More than 200 rows).. In these cases, we cannot waste time creating template and report each time. So, Instead of creating Data Model, we can use the Create Direct Database Query feature of Analytics to achieve this requirement.

1) Navigate to Reports and Analytics

2) Create > New> Analysis

Instead of Subject Area, select the Create Direct Database Query as shown below;













3) On the Criteria tab
 a. Connection Pool = For HCM   use “HCM_OLTP”.”Connection Pool” (Quotes are mandatory)

                                    For FSCM use “FSCM_OLTP”.”Connection Pool”
                                    For CRM   use “CRM_OLTP”.”Connection Pool”
 b. SQL Statement = SELECT Last_Name from PER_PERSON_NAMES_F
4) Then Click on “Validate SQL and Retrieve Columns” which will return the columns selected by the query. We can do the field formatting if required by clicking the column properties.



5) Then click on Results tab to view the results. Data can be exported to multiple formats like pdf, excel, csv, xml, etc.









6) If the requirement is to get the output in pivot table or bar graph, it can be achieved easily with drag and drop options by adding a new layout.










7) The analytics created using this method can be added to dashboard just like other analytics created using subject areas. 

Use Cases: If we need to run a simple BI query to extract more than 200 rows / generate a report in pivot table or bar graph.

Pre-requisites: BI Admin role is required for the user to try this feature. 

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 10 Comments

  1. Unknown

    Thanks charan, its very good article

  2. Unknown

    Hello Charan,

    Thanks for posting such a great article for new learners/beginners.

    Could you please help us in know we can pass parameter in above same example which you have used to explain article ?

    In case we wish to use Value Sets like BI report then how that can be achieved ?

    Your help much appreciated.

  3. Cloudx

    You could always use our free excel add in at CloudXHub.com to run SQL queries directly in Fusion.

  4. Mustafa

    Very useful! Especially for non-technical users/functional consultants like myself. 🙂

  5. Suresh

    Hi Sricharan,

    Good article, I am trying to setup same. I have BI admin role but I am not able to see “Create Direct Database Query” in Create –>New –>Analysis. Is there any other prerequisites are required?

    Thanks,
    Suresh

Leave a Reply