Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Dynamic Datasource

  1. #1

    Default Dynamic Datasource

    Hi,

    I am new to Pentaho and any help in the following topic will be of great help for me.

    The issue is that we have a dedicated db for each of the clients that we have and all of them are having the same db structure.

    In this case if I create a report and publish it to the BI server, is there a way by which I can specify which db to connect to at run time rather than specifying it at the time of report creation?



    Thanks in Advance

  2. #2

    Default

    I pretty sure you can do it but haven't done something like that my self. My best guess would be to use a Pentaho Data Integration transformation as your data source in the report. Than set up a transformation which uses the parameter of which client's DB you are try to hit. You would have to create a table input step for each different DB but all the coding except for the DB link would be the same.

    Here is a link to how to set up transformations as data source for reporting:
    http://www.packtpub.com/article/inte...-pentaho-suite

  3. #3
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    in order to make sure I understood your need correctly:
    You have ONE BI-Platform which can access different databases.
    You want to publish ONE report and every client who acceesses this report should see data from "his" database?

    Tom

  4. #4
    Join Date
    Jul 2013
    Posts
    11

    Default

    hi i come to this subject because i have the same problem. i have ten sites and all site have one database with the same structure.
    all this site have access to the same PENTAHO platform and so i want to make one unique report with a parameter who is the name of the database to access.
    so like this i dont have to make ten times the same report. and i don´t see how to do with kettle how to make that?

  5. #5
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Use JNDI datasources. (Note that this works both with Kettle, Reporting, and any other tool that is able to use JNDI datasource definitions. Even XActions, belch!)
    http://www.on-reporting.com/blog/don...ames-use-jndi/

    This way, your server provides the connection under a common name, but the connection details are outside of the report. THus when you deploy the report to the various BI-server instances, you end up with reports that automagically use the server's connection definitions.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  6. #6
    Join Date
    Jul 2013
    Posts
    11

    Default

    yes but i have only one BI server instance , each ten sites go the central server to launch reports, so i want one parameter in the report that tell me which datasource it will use so i don t know how to use JNDI in my case, because i have only one server instance

  7. #7

    Default

    I have a similar structure in my Pentaho environment and I was able to find a solution. I am still testing it but it seems to work for me. It is based on a solution I found with a google search for a similar issue.

    I start with creating a JDBC connection called "GeneralConnection". I set up my connection as usual (pointing to one of my databases) and create my report. I then created a parameter called "SelectedDatabase". This parameter is going to contain the name of the database I want to connect to. I then open my datasource and select the "Global Scripting" tab. I select ECMAScript as the scripting language and enter the following script:

    function init(dataRow)
    {
    var s_selectedDB = dataRow.get("SelectedDatabase");
    var databaseUrl = "jdbc:sqlserver://MyServer;DataBaseName=" + s_selectedDB;
    dataFactory.getConnectionProvider().setUrl(databaseUrl);
    }

    Now when I run the report I pass the database in using the parameter. The global script runs, changing the database connection, and I get the correct data for my report. My single report can now run against any of my databases without have to manually change the database connection or having to create a connection to each database.

  8. #8
    Join Date
    Jul 2013
    Posts
    11

    Default

    yes thank you you give me the good idea but for now it doesnt work. i put this in query script

    function init(dataRow){
    var s_selectedDB = dataRow.get("SelectedDatabase");
    switch(s_selectedDB){
    case 'NAME1':
    var databaseUrl = "jdbc:sqlserver://192.168.70.100;DataBaseName=MYBASE";
    break;
    case 'NAME2':
    var databaseUrl = "jdbc:sqlserver://192.168.100.100;DataBaseName=MYBASE";
    break;
    case 'NAME3':
    var databaseUrl = "jdbc:sqlserver://192.168.90.100;DataBaseName=MYBASE";
    break;

    }
    dataFactory.getConnectionProvider().setUrl(databaseUrl);
    }

    but when i change parameter in the report, the report reload again but with the same database, how can i debug this script. is there one place where we have console to see output to debug?

  9. #9
    Join Date
    Jul 2013
    Posts
    11

    Default

    after try , it was the switch structure the problem, i replace with simple IF and it works... thanks

  10. #10
    Join Date
    Mar 2013
    Posts
    8

    Default

    I used to use similar code in Report designer 3.9.1 for switching database connection when report initiates (using generic type connection).

    Now I am using PRD 5.0.1 with datasource (PME 5.0.1 generated) .xmi file. Here Global ECMAScript throws exception for using

    var v=dataRow("parameterName");

    What can be the possible workaround?
    Last edited by Meherun; 03-05-2014 at 06:20 AM. Reason: adding more info
    With thanks
    M. Nessa

  11. #11
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    The correct call is

    dataRow.get("parameterName").

    dataRow is not a function, it is an object with a "get" method.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Tags for this Thread

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.