Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Multiple primary key question

  1. #1
    Join Date
    Aug 2007
    Posts
    16

    Default Multiple primary key question

    I am using the Pentaho MetaData editor version 1.6.0.GA.162 and I have a question about how to get it specify a query in the correct way. The situation I have is as follows:

    I have 5 database tables that look like

    Name:  tables.jpg
Views: 83
Size:  10.3 KB

    The primary key from Table4 is a foreign key on all other tables, and forms a part of the primary key for each table.

    When I use the query editor and add columns from only Table1 and Table3, it generates a query that only specifies the link between Table1->Table4 and Table3->Table4. I need the query to also add in the relationships from Table1->Table2 and Table3->Table2.

    Additionally, if I have columns from only Table5 and Table3, it generates a query that only specifies the link between Table5->Table4 and Table3->Table4. I need the query to also add in the relationships from Table5->Table1, Table1->Table2 and Table3->Table2.


    How can I specify my metadata to have this happen?


    Thanks!

    -Andeep Toor
    ________
    REINSURANCE ADVICE
    Last edited by atoor; 03-11-2011 at 07:06 PM.

  2. #2
    wgorman Guest

    Default

    Hello Andeep,

    Individual relationships in Metadata are considered independent, MQL will search for the shortest relationship path between two tables to determine the joins and non-included tables to add to the SQL.

    I'm interested in looking at your database schema, there may be another way to represent the relationships either via views or with another relational design approach.

    Will

  3. #3
    Join Date
    Aug 2007
    Posts
    16

    Default

    Will

    I don't believe that I can send the database schema because it is of a sensitive nature, but I can answer any questions that you might have about it.

    We are currently using views. In the example that I provided before fields from Table3 are in View3 and fields from Table1 and in View1, etc.

    For finding the shortest path, as you mentioned, is there any way to specify that a path needs to be included, but is not on the critical path?

    As I mentioned before Table4.ID is the primary key for Table4, but also makes up part of the primary key for all other tables.
    For example, Table1's primary key would be something like:
    PRIMARY_KEY(Table1.ID, Table1.TABLE_4_ID)

    Table5's primary key is more complex, like
    PRIMARY_KEY(Table5.ID, Table5.TABLE_5_ID, Table5.TABLE_4_ID)

    In addition, joins between tables are complex, as they involve these multiple primary keys:

    Table1->Table2 is (Table1.ID = Table2.TABLE_1_ID AND Table1.TABLE_5_ID = Table2.TABLE_5_ID)

    -Andeep
    ________
    HONDA 1300 HISTORY
    Last edited by atoor; 03-11-2011 at 07:06 PM.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The whole idea behind BI in general and Pentaho metadata specifically is to make it as easy as possible and as fool-proof as possible for the user to create reports and get information out of the data.

    Creating wacky data model and circular references kinda conflicts with that idea.
    What I mean by that is that if you can't put all of this into a single model without creating problems for yourself or PMD, it's not going to be trivial for the user either. Look at the mess that BO for example made out of that problem.

    So if that's what it takes to make it fool-proof, create 2 business models in the same domain. Do whatever you need to do to make it simple and fool-proof for the user. While you're at it, why not create a real data warehouse.

    You'll find that your problems will go away.

    Matt

  5. #5
    Join Date
    Aug 2007
    Posts
    16

    Default

    Matt

    The database structure, in our case, is not something we have control over. It is what it is, and we are tasked to use it. Given that it is fairly wacky, the database structure is not totally out of the ordinary, as this specific situation involves tables having multiple fields that make up a single primary key. To my knowledge, this is something that occurs in many RDBS designs...it may not be the best idea in the world, but it does happen, and I'm hoping that Pentaho can support this structure.

    I understand your point of view, especially if we had control over how things are structured, but in the case that we have does Pentaho not support what we are trying to do?

    In particular, I took your suggestion of trying to create a separate business model, but I don't see where that gets me. We still need to select columns from a number of views and have the correct sql get generated. Correct me if I am wrong, but I thought that the columns that you can pick from to generate a query would have to be in the same business model?

    Is there anything else we can try?
    ________
    Electric Cigarettes
    Last edited by atoor; 03-11-2011 at 07:06 PM.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Your point of view is noted, however, using 3rd normal form databases to do business intelligence on *is* indeed something I consider out of the ordinary.
    Since you do have read access to the tables, you *can* in fact create a data warehouse on the data.

    But back on topic, to the best of my knowledge, you can indeed specify composite keys between 2 tables.
    IIRC it's the "complex join" option.

    Matt

  7. #7
    Join Date
    Aug 2007
    Posts
    16

    Default

    Matt

    Yes, we are already using complex joins option when specifying relationships. I appreciate the data warehousing points that you bring up, but in our case it may not be a viable option.

    Is there another way we should be specifying our relationships to get a query to generate correctly? Is there anything else we can try?

    I am currently looking into the BusinessModel class in both the createSQL and findShortestPath methods (which are called from the MQLQuery class when generating a query). Is any way to get the code to recognize the distinction of a table relationship that should be added to a path, but should be ignored in terms of figuring out when the path is created? The path I am referring to is the collection of relationships that will eventually be used to create a sql string.

    To note, I have gone back and tried some more simple queries and have found what appears to be a similar problem. In this simple example I removed the link to Table4 to test stuff out temporarily. It looks like this:

    Name:  tables2.jpg
Views: 78
Size:  9.5 KB

    I am doing a query that selects from a column from Table5 and one that selected from Table3. The query I get back includes joins from Table1, Table2, Table3, but also erroneously includes Table6's relationship. Why is this happening? Shouldn't this get removed as it shouldn't be a part of the shortest path?

    To correct myself from the first post as well, I didn't mention that in all the queries I was doing, I was filtering on fields from Table4.

    I hope this helps towards solving this issue. Please let me know if there is more info you require.
    ________
    Honda XL250
    Last edited by atoor; 03-11-2011 at 07:07 PM.

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There is a property called "Relative size" that will allow you to do scoring on the paths.

    If there are multiple paths: A-B-C, A-D-C, A-E-C for example, you can set a relative size of B=5, D=3, E=7
    As such, the score of the A-D-C path will be the lowest and taken.

    The path of the least resistance if you can put it like that.
    See if that helps you out in this situation.

    Matt

  9. #9
    Join Date
    Aug 2007
    Posts
    16

    Default

    Matt

    Is this a property that I can set on a table in the MetaData Editor, or is this something I can only manipulate in the Pentaho classes themselves?

    Thanks,

    -Andeep
    ________
    Bmw M40 History
    Last edited by atoor; 03-11-2011 at 07:07 PM.

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The former:

    Name:  pmd-relative-size.jpg
Views: 72
Size:  22.8 KB

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.