Hitachi Vantara Pentaho Community Forums
Results 1 to 28 of 28

Thread: more joins in the same hierarchy

  1. #1

    Unhappy more joins in the same hierarchy

    I have the follow schema on my database



    Whit "SCHEMA WORKBENCH" I want create one hierarchie one dimension Libreria that contain:

    - dimension Libreria:
    -Hierachie: Libreria
    -level one : join (Libreria whit Libreira_nome) for to see Libreria_nome. descrizione_media
    -level two: citta (for see Libreria.citta)
    - level three: I don't create ANOTHER JOIN between Libreria and specializzazione because I want use Specilizzazione.descrizione_media


    Anyone know how to use the same type of join multiple dimensions?
    Schema Workbench does not give me the opportunity to create more joins in the same hierarchy ... It 'a limitation of the Pentaho product?
    I think if you can not do this, then Pentaho has the limitation: the database must be a star schema and not snowflake !!!



    Please help me!!!!
    Last edited by GianlucaF_exp; 03-24-2010 at 05:57 AM.

  2. #2

    Default

    less anger and more coherence please

    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  3. #3

    Default

    If I have a snowflake database, can I create a more join in one hierarchie of dimension?
    This is the problem!!!!
    Because in the SCHEMA WORKBENCH is not POSSIBLE!!!
    Schema WorkBench insert in one hierarchie one table or one join!!!!

  4. #4

    Default

    I asked someone to translate your rants... and he concluded if you are trying to create a 2nd level snowflake that's probably not possible. That being said, you get a nasty performance slowdown from snowflake anyway, ever considered doing some sensible refactoring?

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  5. #5

    Default

    Quote Originally Posted by bugg_tb View Post
    I asked someone to translate your rants... and he concluded if you are trying to create a 2nd level snowflake that's probably not possible. That being said, you get a nasty performance slowdown from snowflake anyway, ever considered doing some sensible refactoring?

    Tom
    I can make a sensible refactoring to the tables, but it seems absurd that the tool WorckBench Scheme and therefore the Pentaho platform has these limitations.
    Should you one day work with a complex database as I behave?
    I read that is possible to do with the snowflake and I wondered about all news here.

  6. #6
    Join Date
    Dec 2005
    Posts
    531

    Default

    Hi,

    have a look at the Mondrian documentation: http://mondrian.pentaho.org/document...p#Star_schemas

    According to these information you can have as many levels as you want by using nested joins.

    It could be, that the Schema Workbench does not support these nested joins. In that case open the schema XML file in your favorite text editor and edit it manually. You can even file a Jira case with an enhancement request.

  7. #7

    Angry

    Quote Originally Posted by ingo.klose View Post
    Hi,

    have a look at the Mondrian documentation: http://mondrian.pentaho.org/document...p#Star_schemas

    According to these information you can have as many levels as you want by using nested joins.

    It could be, that the Schema Workbench does not support these nested joins. In that case open the schema XML file in your favorite text editor and edit it manually. You can even file a Jira case with an enhancement request.

    I tried to make manual changes to the cube, the cube I posted to the repository, but unfortunately I still can not see the second join...

  8. #8
    Join Date
    Mar 2009
    Posts
    112

    Default

    The column descrizione_media from your fact table is from table specializzazione?

    Why don't you post your first join so we can see what you want to do?

    I had the same problem recently and figured it out by following the example on http://mondrian.pentaho.org/document...p#Star_schemas , but I didn't use another dimension, just another hierarchy.
    It took me more than a try though...

  9. #9
    Join Date
    Jun 2010
    Posts
    172

    Default Joining multiple tables

    Hi,

    Even I face a similar problem.

    I have 4 tables in mysql database:
    1: countrytbl (idcountry, country_name)
    2: statestbl (idstates, country_id, state_name)
    3: citytbl (idcity, state_id, city_name)
    4: storestbl (iD, location_id, store_name, sales)

    color - primary key
    color - foreign key

    And my schema is as follows:

    <Schema name="NVI">
    <Cube name="nviSales" cache="false" enabled="true">
    <Table name="storetbl">
    </Table>
    <Dimension foreignKey="location_id" highCardinality="false" name="Store">
    <Hierarchy hasAll="true" primaryKey="location_id" foreignKey="location_id" primaryKeyTable="storetbl">
    <Join leftKey="location_id" rightAlias="citytbl" rightKey="idcity">
    <Table name="storetbl">
    </Table>
    <Join leftKey="state_id" rightAlias="statestbl" rightKey="idstates">
    <Table name="citytbl">
    </Table>
    <Join leftKey="country_id" rightAlias="countrytbl" rightKey="idcountry">
    <Table name="statestbl">
    </Table>
    <Table name="countrytbl">
    </Table>
    </Join>
    </Join>
    </Join>
    <Level name="Store Country" table="countrytbl" column="country_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Store State" table="statestbl" column="state_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Store City" table="citytbl" column="city_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Store Name" table="storetbl" column="name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Measure name="Sales" table="storetbl" column="sales" formatString="#,###" aggregator="sum">
    </Measure>
    </Cube>
    </Schema>


    This gives me correct flow for the country->state->city->store. But it does not give me the correct sales corresponding to the store.
    Also I want to see the entire country, state, city list, that does not have any sales value but are there in the tables.

    Probably that is outer join.

    What changes in my schema will solve the two problems.

    please help

  10. #10
    Join Date
    Jun 2010
    Posts
    172

    Default prob solved

    Hey

    I managed to solve the problem using table view..
    but will still prefer if I could make use of joins..

    Please if anyone have tried, plz help me too...

    Thanks and Regards


  11. #11

    Default

    Assuming you are using something like MySQL and not Oracle, then using a view is a bad idea because Views make no use of indexes.....

    Oh and stop sending me private messages.
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  12. #12
    Join Date
    Jun 2010
    Posts
    172

    Default

    Quote Originally Posted by bugg_tb View Post
    Assuming you are using something like MySQL and not Oracle, then using a view is a bad idea because Views make no use of indexes.....

    Oh and stop sending me private messages.
    Hi,

    Yep, I use mysql.. and yes it has actually become slow... and I know that it is because of no indexing in views..

    Please can you help me with table joins...

    And I apologize for the inconvenience caused due to my private messages.... I just needed a quick help that day.. which i could not get..

    Thanks and Regards


  13. #13
    Join Date
    Jan 2006
    Posts
    205

    Default

    Your city and store tables arent joined to anything - so obviously you wont get the right values for the data there...

    This isnt the cold war - Country, State and City are pretty static data... Why not build that as a single, optimized table?

    Regards,

    Mike

  14. #14
    Join Date
    Jun 2010
    Posts
    172

    Default

    Quote Originally Posted by mikegreen View Post
    Your city and store tables arent joined to anything - so obviously you wont get the right values for the data there...

    This isnt the cold war - Country, State and City are pretty static data... Why not build that as a single, optimized table?

    Regards,

    Mike

    Hi Mike,

    Thanks for replying...

    Actually, this is just a simple example of four different tables that has to be joined. Can you please explain me how to join the tables so that I can replicate the same for my actual analysis.

    I have given the table structure below, where the primary key 'idcity' in 'citytbl' is saved as foreign key 'location_id' in 'storestbl'.

    The flow should be, Country->State->City->Store->Sales.

    Thanks and Regards


  15. #15
    Join Date
    Jan 2006
    Posts
    205

    Default

    The tables should be arranged from largest row-count to smallest. So my initial thought is reverse the order of your joins....

  16. #16
    Join Date
    Jun 2010
    Posts
    172

    Default

    Quote Originally Posted by mikegreen View Post
    The tables should be arranged from largest row-count to smallest. So my initial thought is reverse the order of your joins....

    Hi Mike,

    Yesterday for full day I tried various combination s of joining the tables. Knowing the fact that the table with maximum number of rows should be kept outermost in the join. With 'fact' table as 'storetbl' (since 'sales' measure belongs to this table). But could not get this working.

    The order of no. of rows contained in the tables is as follows:
    countrytbl < statestbl < citytbl < storestbl
    With countrytbl having the least no. of rows.

    I want correct 'sales' figure corresponding to the 'store' and list of all the countries, states, cities, stores (outer join) to be shown.

    Please help

    Thanks and Regards


  17. #17
    Join Date
    Jan 2006
    Posts
    205

    Default

    Can you elaborate on "not working"???

  18. #18
    Join Date
    Jun 2010
    Posts
    172

    Default

    Quote Originally Posted by mikegreen View Post
    Can you elaborate on "not working"???
    Hi Mike,

    The database structure and schema is defined in post no. 9 and the MDX query is as follows:

    select Measures.[Sales] ON COLUMNS, [Store].MEMBERS ON ROWS from [nviSales]

    The order of row-count is countrytbl < statestbl < citytbl < storestbl.
    With countrytbl having the least no. of rows, as mentioned earlier.

    I am attaching the images of the result I get and the database tables.

    It gives me the correct hierarchy of the country->states->city->stores data, but the sales values are incorrect.

    Please help.

    Thanks and Regards

    Attached Images Attached Images   

  19. #19
    Join Date
    Jan 2006
    Posts
    205

    Default

    Have you tried getting one join working? At what point in the 4 tables you are joining does it break?

  20. #20
    Join Date
    Jun 2010
    Posts
    172

    Default

    Hi Mike,

    That was a very good suggestion!

    I broke down the problem to just 2 tables. 'storetbl' and 'citytbl'. And surprisingly even that dint work!!! The hierarchy comes properly, but the 'sales' values corresponding to the 'store name' are incorrect. It gives the sum of all the sales values taken together.

    Is this happening because the fact table is also involved in the join?

    I am attaching the schema. Please have a look.

    Thanks & Regards

    Attached Files Attached Files

  21. #21
    Join Date
    Jan 2006
    Posts
    205

    Default

    Since you are using the same table for your dimension and your fact - change primary key for the hierarchy to the id column.

  22. #22
    Join Date
    Jun 2010
    Posts
    172

    Default

    Hi Mike,

    I changed the primary key for the hierarchy to the 'iD' column. Now the sum of all sales values appears only once in the measure sales column. That is, corresponding to the row entry where the first store name is present.

    What can be the problem now?

    Thanks & Regards


  23. #23
    Join Date
    Jan 2006
    Posts
    205

    Default

    What version of mondrian and what database are you using? Can you post your screenshot and latest schema?

  24. #24
    Join Date
    Jun 2010
    Posts
    172

    Default

    Hi,

    I create the schema(mondrian.xml) file through Schema Workbench - Product Version : 3.2.0.13583.

    And use it in cdf 3.6.0 stable. My database is MySQL Client Version 5.1.11.

    And here is the latest schema:

    <Schema name="NVI">
    <Cube name="nviSales" cache="false" enabled="true">
    <Table name="storetbl">
    </Table>
    <Dimension foreignKey="location_id" name="Store">
    <Hierarchy hasAll="true" primaryKey="iD" primaryKeyTable="storetbl">

    <Join leftKey="location_id" rightKey="idcity">
    <Table name="storetbl">
    </Table>
    <Table name="citytbl">
    </Table>
    </Join>

    <Level name="Store City" table="citytbl" column="city_name" type="String" uniqueMembers="true">
    </Level>
    <Level name="Store Name" table="storetbl" column="name" type="String" uniqueMembers="false">
    </Level>

    </Hierarchy>
    </Dimension>

    <Measure name="Sales" column="sales" formatString="#,###" aggregator="sum">
    </Measure>

    </Cube>
    </Schema>

    Sorry, there is some problem and I'm not able to attach files or even upload images here... so cant paste the screenshot..

    If you can give me your email id.. I can mail you the screenshot..

    Thanks & Regards


  25. #25
    Join Date
    Jan 2006
    Posts
    205

    Default

    test image post
    Name:  2010-08-09_1356.jpg
Views: 116
Size:  25.9 KB

    looks to be working - did you try advanced mode to post your attachment/screenshot?

  26. #26
    Join Date
    Jun 2010
    Posts
    172

    Default

    Hi,

    Attachment is working today... I am attaching the result screen shot and the latest schema.. please have a look..

    Thanks & Regards

    Attached Images Attached Images  
    Attached Files Attached Files

  27. #27
    Join Date
    Jan 2006
    Posts
    205

    Default

    Are you sure your source data is valid? Are the IDs unique in the store table (your fact table)?

  28. #28
    Join Date
    Jun 2010
    Posts
    172

    Default

    Hi,

    I am attaching a screen shot of my fact table, that is the storetbl. Here the primary key ids are unique.

    Thanks & Regards

    Attached Images Attached Images  

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.