Abuse of Teiid for SaaS Fun and Profit

[amalter]

Hi all,

First off, I've followed the project for a while and have been very impressed with what you all have built.

So - I have a technical requirement that I'm concerned is not quite in Teiid's wheelhouse, (or might be completely outside of what Teiid's designed for) but I'm wondering if their is a way it could help us.

The explanation is a bit long, so bear with me.

We are a hosted application that provides functionality for partners in international trade transactions. Single big database for everyone with view rights granted dynamically depending on who's part of any "transaction". This is acomplished with a "Viewer" table like so:

Shipment ShipmentID ShipmentDate, etc... ViewerID

Viewer ViewerID PartyID

Aka - To find all shipments that partyID 123 can see:

SELECT * FROM Shipment, Viewer WHERE Shipment.VIEWERID = Viewer.VIEWERID AND Viewer.PARTYID = 123;

Additionally, some fields in these transactions are represented as columns on tables, and some are "custom" - aka specific to a customer, and as such are represented using generic tables (FieldID, TransactionID, FieldName, FieldVal, FieldDataType).

The Problem: Provide an ad-hoc reporting enviornment for those customers wanting it. Traditional business intel/reporting server tools don't seem to fulfill what we want.

• Really awesome would be a jdbc connection (or web service/api/etc) that customers could use to run simple reports or that they could connect a fat client reporting design tool at. (Reports could either be run remotely, or even better, designed and then sent to us to be run).
• This JDBC connection would differ from the actual physical db in at least two important ways.
  • All data tables like the "Shipment Table" expressed above would be views with the Viewer auto-magically connected.
  • The generic field tables would be "pivoted" out to appear to the customer like normal tables.

I'm aware this is pretty much what Salesforce's web services do, and I'm jealous.

Some constraints that make me worry that I'd need to abuse to Teiid to get it to help:

• Number of unique 'schemas' - probably one per customer (4-5k). If I used a VDB per customer, would Teiid even be okay deploying that many? And then creating that many by hand.. Hmm, sounds rough.. Dynamic VDB's seem cool, but View layers sound like what we would need, and they don't seem to be supported dynamically.

Brainstorming I've done on how I might get my way anyway:

• If VDB's are mostly the same with just different PartyID's hardcoded into the Views, maybe I could programatically post process an xmi from the designer. (I'm aware this is frowned upon or could be impossible)?
• Looking at custom resource adapaters, it occurs to me that some of this customization could be pushed back into that part of the stack. We have a pretty roboust SQL parser built off antlr. Maybe less VDB's and inject this join/constraint at statement level?
• A custom resource adapater could also present it's own version of metadata. Perhaps this could allow the use of dynamic vdb's while still allowing some control. Am I going to just end up re-inventing Teiid (poorly) if I do this?
• Original idea was to point Teiid at the database and have it handle security/view transformation. Maybe we create custom adapater to push data through our app server. Teiid becomes our highly available JDBC server?

Any general throughts on this would be very much appreciated. I'm aware this is a *big* problem, but if anyone could let me know if I'm thinking even remotely reasonable things.. Do I need to go back to the drawing board? Am I missing some simple solution??

Well - thank you all for your time and any thoughts.

-adam malter

[shawkins]

Hi Adam,

What you're trying to do doesn't seem that abusive yet.  Views of the form:

SELECT * FROM Shipment, Viewer WHERE Shipment.VIEWERID = Viewer.VIEWERID AND Viewer.PARTYID = <party id expression>

Would be easy to generalize in a single vdb by selecting the appropriate party id via a user lookup (udf/case statement/subquery/lookup function/etc.).  It would be best from a planning perspective to use a deterministic udf, so that it can be pre-evaluated. 

To have custom fields appear as actual table columns would require additional work.  I'm not sure if this would be easy to generalize across all users, or if it would require per user vdbs.  Do you want the table structures to vary depending on the user, or can you pre-define metadata that represents the superset of what's possible and use roles or other mechanisms to filter unwanted columns?

Note that it is possible even now for dynamic metadata to contain Teiid view definitions - it's just not part of the metadata load logic which only fetches physical tables.  If you have a custom translator that returns Table records that are marked as virtual and have select transformations, then they will be functional views.  See the org.teiid.deployers.PgCatalogMetadataStore for how we use this functionality internally to define the pg system views.

- Number of unique 'schemas' - probably one per customer (4-5k). If I used a VDB per customer, would Teiid even be okay deploying that many?

It is really a matter of what the memory footprint of all that metadata is.  All metadata records are currently held in-memory, so a small vdb ~hundreds of tables/views * 4-5k would probably be ok.  Larger vdbs with thousands of view would likely put a memory strain - especially if the view definitions differ.  We are looking to refine vdb reuse logic (currently only non-persistent merging is available mainly for vdb preview logic from Teiid Designer) which could help in such a scenario so that common tables/views could be defined only once.

- If VDB's are mostly the same with just different PartyID's hardcoded into the Views, maybe I could programatically post process an xmi from the designer. (I'm aware this is frowned upon or could be impossible)?

It could be possible you'd have to check with the Designer folks on headless eclipse usage.  I would think that the better approach would be dynamic vdbs with custom translators injecting views.

- Looking at custom resource adapaters, it occurs to me that some of this customization could be pushed back into that part of the stack. We have a pretty roboust SQL parser built off antlr. Maybe less VDB's and inject this join/constraint at statement level?

If the metadata you present to Teiid is already in the from that you desire, then you could use the translator logic to modify incoming commands to have the necessary joins.  Since the command will be in object form there is no need to re-parse.  If you are accessing a single source, this approach is straight-forward.  If accessing multiple sources, you can even have your custom translator call back into Teiid through an embedded Teiid connection to perform further processing.

Hope this helps,

Steve

[amalter]

Thanks so much Steven!

I'm very excited to try an integration with Teiid, but wanted to make sure what I was attempting was reasonable. So good to know it is

I'll need to wrap my head more around the Teiid concepts and then try to digest your suggestions.

Thank you again..

-adam