Exposing Metadata query editor to end user maybe in an action sequence

[timtiptoes]

I'm thrilled with the interactive reporting in Pentaho 4.5. I'm trying to use this ability to empower the end user to effectively construct their own query. The issue I have though, is that the returned data set is *MUCH* too large to give to the user, whether it's in the browser or downloaded to their machine. The results are about 5M rows. Even if this was feasible, my users would then upload this mega-csv to another system anyway for email execution. My users are not sophisticated enough to use PRD, btw.


My dream workflow is this:

1) User launches a web page, which might just be the PUC
2) User select data they want, effectively using something like the metadata query editor, to produce a SQL query behind the scenes
3) User clicks 'go'
4) A job is launched in the background that
i) executes the query
ii) Uploads the data to the destination system
iii) Sends the end user an email informing them of the completion


I love the high interactivity of analyzers and the new interactive reporting, I just want to leverage this kind of thing for big jobs that should happen in the background.

Ideally there'd be a way to launch the query editor in an action sequence to the end user and then take the result and pass it to a PDI job.


Anyone know of how I might proceed or an example? If worse came to worse, I could get make my own thing in Java that leveraged the right classes.

Rather than figuring things out myself, it would be very useful to start with someone else's accomplishment.

As it stands now, I'd have to make an action sequence with about a zillion pull downs, radio buttons, and checkboxes and every week they'd ask for something new to filter the data.

In the beginning it would just hit a mysql box but we could pop in a different dialect and use hive. Then my users could query our entire datawarehouse without even talking to me, which is the point of BI, right

Thanks

Tim

[nbaker]

That's an interesting system. It's certainly possible, but will require custom code. The MQL Query Builder can run in any page you want, so say a JSP file. That would get you the MQL query. You could then pass that up to the server to a Servlet or Content Generator if you're writing a plugin. On that side you have a couple options, you could create a new Metadata datasource that's basically a sub-select based on the MQL query (this is how the SQL datasource works in the data access wizard), or you could go the Kettle route and actually slice off the data into another table and generate a new metadata domain for that.

It's a significant amount of work, but possible.