[teiid-issues] [JBoss JIRA] (TEIID-2309) Add support for "conformed" tables in Teiid

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2309?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739073#comment-12739073 ] 

Steven Hawkins commented on TEIID-2309:
---------------------------------------

There are two possibilities here from a run-time perspective.  One would be to introduce the concept of a conformed schema/model.  It would look like a multi-source model in that it would be a physical model with multiple sources listed.  Then the user would be expected to use the conformed instance whenever Teiid is expected to choose the correct table automatically.  The conformed tables could also be left in their respective schema/models so that a reference to a particular table could be forced.

This first option is the least amount of work from a planner/metadata perspective.  Although it's not clear how well this jives with feature requested here.  This feature seems to be written as a somewhat post development activity where tables are being replicated for performance and thus references need update in existing definitions to take advantage of the now replicated table. This implies some additional Designer work.

The second option would be to handle the notion of conformed at a table level.  This would either be through extension metadata or an additional construct.  If added as an additional metadata construct, then this is something that could be handled as a post-development activity more easily.  Being able to control which source(s) a table is bound to is potentially useful beyond this feature since it give more flexibility in schema design.


                
> Add support for "conformed" tables in Teiid
> -------------------------------------------
>
>                 Key: TEIID-2309
>                 URL: https://issues.jboss.org/browse/TEIID-2309
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Debbie Steigner
>
> Teiid would support tables from different data sources being marked as "conformed", meaning they are the same (or perhaps a different name).  When optimising a query, it would take the conformity into account and choose the appropriate copy of the table (presumably one in the same database as other tables in the query, if available).  I would not regard it as a problem if Teiid *required* the dimensions to be strictly the same as opposed to permitting subsets, though as with so many areas, it would be up to the user to ensure this was really true: I would not expect the engine to do anything to verify that the tables really were conformed.
> Usecase:
> In Data Warehousing, it is relatively common to have multiple copies of the same dimensions spread over multiple Data Warehouses or Marts, or in the same Data Warehouse when associated with different Fact Tables.  If these copies are either identical or strict subsets of an idealised dimension (and, by extension, share *exactly* the same naming and structure), then they may be said to be "conformed".  It is expected that the dimension includes at least the values required to support the facts in the database in which it occurs or the Fact Table to which it is paired.
> Example:
>  
> Source S1:
>  
> BIGBIGBIG  (millions of rows)
> bigkey
> ccy
> other_stuff
>  
> CURRENCY   (100s of rows)  let's call it S1_CCY if we need to distinguish
> ccy
> ccy_name
>  
>  
> Source S2:
>  
> BIGGER     (millions of rows)
> biggerkey
> bigkey
> ccy
> more_stuff
>  
> CURRENCY   (100s of rows)  similarly, S2_CCY
> ccy
> ccy_name
>  
>  
> When executing:
>  
> SELECT  B.*
> FROM    BIGBIGBIG    B,
>         CURRENCY     CCY
> WHERE   B.ccy        = CCY.ccy
> AND     CCY.ccy_name LIKE "%DOLLAR%"
>  
> Then it is clearly advantageous to use the copy of CURRENCY in S1 and re-write the query using S1_CCY.  In this situation, federation is eliminated completely.
>  

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira