Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: How do I use multiple sql commands including DML inside my report's query ?

  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Question How do I use multiple sql commands including DML inside my report's query ?

    Hi everyone,
    I am a report developer. I am only able to read data and create temp table in my user schema and am not able to schedule a job or run a stored procedure. I am wondering if pentaho let me to use multiple queries(including DML queries) in my report.
    My issue is that my current report is running from DW DB and since this DB is so huge , it never returns any data. So I want to create a temp table insert the required data from big tables (ex last 2 weeks) into them and run my queries on those temp tables and then drop them. is it possible in Pentaho reporting?
    Do you have any other suggestions for me ?
    Thanks,
    Sarah

  2. #2
    Join Date
    May 2016
    Posts
    280

    Default

    I don't know if that is possible within Reporting, but I think is more proper to use Pentaho Data Integration (previously known as Kettle) or whatever tool you prefer for ETL (Extract, Transform, Load) tasks and use Pentaho Reporting to query the data already loaded in proper tables.
    Also, it doesn't matter the size of your DB, if you can't properly run queries into it, it's not useful, it can happen, but before deciding to build temporary tables with a few data so your reports work you have to analize the source of the problem.
    So my first approach would be to optimize the queries you want to use in your reports. Analize if you are using table indexes properly, or if you need to add indexes/partitions to that tables so you can have good time responses. Building tables with summary data so frequently used queries/reports have good time responses is a usual technique in a DW when the amount of data is big, so you can drill down from a more generic report with less data but a more general view of the Business to a more detailed one. But creating summary tables or temporary tables it's not a decision made only by the report designer by him/herself, it has to be made by the DW team with a deep knowledge of the DB Model and different reporting needs.
    Here I'm assuming you're only a report designer, if you have already covered all this, then as I said before, I would look into an ETL Tool to get my temporary data into a smaller table.
    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.