Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Creating relationship on a non-star schema model

  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Default Creating relationship on a non-star schema model

    Hi,

    Question : Can Pentaho Meta editor support a non-star schema model?

    Scenario:

    We are trying to evaluate pentaho to embed as a BI tool for our DW product. Our datawarehouse is not a typical star scheme model in which dimensions are directly linked to facts;but we have group-bridge tables between dimenion and facts

    A typical join between DIM_PRODUCT& FACT_CASE looks like:
    DIM_PRODUCT.PRODUCT_ID = PRODUCT_BRIDGE.PRODUCT_ID and
    PRODUCT_BRIDGE.PRODUCT_GROUP_ID = PRODUCT_GROUP.PRODUCT_GROUP_ID
    PRODUCT_GROUP.PRODUCT_GROUP_ID = FACT_CASE.PRODUCT_GROUP_ID

    You can notice above the addtional joins in our model between dimension and facts. I had build this relationship in Pentaho meta editor and then created a business view of DIM_PRODUCT & FACT_CASE table in it. When I tried to pull a adhoc report from these two tables;it says invalid relationship.(Note: I didnt bring the Group & Bridge tables in business view;since user does need to view the intermediate tables)

    Currently we use SAP BO that supports this as we are planning to move to pentaho for cost-effectiveness.Please provide clarity on whether the above is achievable in pentaho.

    Thanks,
    Sreedev.

  2. #2
    Join Date
    Jun 2013
    Posts
    44

    Default

    just go through your post here get confused more now you have to wait some apt reply from any expert here . though I'm too keenly looking over the issue to add more tags to my knowledge in case if anything helpful come to your way through post here..

  3. #3

    Default

    Are you sure the relation is like that?
    You go from FactProductID to ProductGroupID to ProductID. That does not seem logical to me. I would assume that you would go from FactProductID to ProductID to ProductGroupID ... When the ProductGroupID is in the 'middle' how can you determine which ProductID a FactRecord has. Assuming a ProductGroupID can have multiple ProductID's?

    Jar

  4. #4
    Join Date
    Jun 2013
    Posts
    2

    Default

    Hi,

    Thanks for reply. Let me clarify why we do not have a star schema model:

    FACT_CASE - uniquely is identified by CASE_ID granularity.

    Each case could have Multiple products and one product can be associated with more than one case.To avoid this many-many relationship in the data warehouse and to get the reports on a case level ; we maintained the FACT_CASE as case-granular and have group-bridge associated tables.Each time there is new product is created/modified/deleted on a case ; a new group gets created on the PRODUCT_GROUP table ;links one-one with the FACT_CASE . PRODUCT_BRIDGE table contains all the multi value combination of Product & a case that will split the N:N internally with 1:N & N:1.

    We initially tried with the star schema model;that did not work out due the numerable multi value (many-many) relationship in the source system and Fact table gets exploded into terrabytes of data.

    This group-bridge model has been officially approved by KIMBALL group due to its efficiency.Due to the same reason;we do not have a choice to change the model in order to support a specific BI tool. With SAP BO this model works a charm.

    Our management decision is to cut cost in terms of SAP product licensing and to choose Open source BI to bundle reports with our product.

    I would be happy to answer more if you require more clarification. My challenge here is : How to use this same model for establishing the relationship in pentaho so that users can report seamlessly?

    Thanks,
    sreedev.

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.