US and Worldwide: +1 (866) 660-7555
Results 1 to 8 of 8

Thread: How to combine / join Cube Facts

  1. #1

    Default How to combine / join Cube Facts

    Hello Community.

    I have three cubes. Two of them share three dimensions (one of them a Date Dimension), the third one only shares two (one of them a Date Dimension) with the other ones.
    The cubes are like that:

    Cube #1 defines operational parameters of a maschine, like hours it run per day. The dimensions are Date and Maschine Specifications.
    Cube #2 defines problems per maschine, per day. The dimensions are Date, Maschine Specifications and Error Code Specifications.
    Cube #3 is quiet similar to cube #2, it only differs in the facts, the dimensions are the same.

    The Fact Tables have foreign keys to the same Dimension Tables. As standard aggregation, the fact fields should all get summed up.

    I read about virtual cubes and shared dimensions but I'm missing an example how to build the cubes in Schema Workbench.

    My approach was to build a schema, which contains three cubes and the thee dimensions. I think if I add the Dimensions directly to a Schema but not to a cube, it's a shared dimension, right? I added "Dimension Usage" to the cubes.

    But now I don't know how to build the virtual cube or if this approach is the right one at all? I want to be able to see dependencys between the facts of the three cubes like: How did problems, which are logged in cube #2 affect the operational parameters logged in cube #1.

    Thanks & Regards!

  2. #2
    Join Date
    Jan 2013
    Posts
    535

    Default

    It sounds like your on the right track, and yes it sounds like this is the right approach. Virtual cubes are a great option from performing cross-fact table analysis.

    The standard example schema used with Mondrian is the Foodmart schema. It contains the "Warehouse and Sales" virtual cube. Take a look at it, it might give you some ideas.

    You can grab the schema off of github: https://raw.github.com/pentaho/mondr...o/FoodMart.xml

  3. #3

    Unhappy

    Quote Originally Posted by mcampbell View Post
    It sounds like your on the right track, and yes it sounds like this is the right approach. Virtual cubes are a great option from performing cross-fact table analysis.

    The standard example schema used with Mondrian is the Foodmart schema. It contains the "Warehouse and Sales" virtual cube. Take a look at it, it might give you some ideas.

    You can grab the schema off of github: https://raw.github.com/pentaho/mondr...o/FoodMart.xml
    Hello mcampbell,

    thanks for your reply. I looked at the example and it could help me a bit.
    But now I have the problem that only some combinations of facts and dimensions return values
    I tried in saiku, the bi server analysis view and manually in a mdx querie in wbc.

    If I select the normal cubes from this schema, I can do all queries and everything seems allright. But when I choose the virtual cube, it only returns values if I choose the [ALL] Level of my Dimensions.

    Maybe someone can see a problem in my schema (I had to rename some parts because I'm working on confidential data)

    Code:
    <Schema name="CombinedCubesSchema">
    
     <!-- BEGIN SHARED DIMENSIONS -->
     
     <Dimension type="StandardDimension" visible="true" highCardinality="false" name="ERROR">
        <Hierarchy visible="true" hasAll="true" primaryKey="ERROR_ID">
          <Table name="ERROR_dim">
          </Table>
          <Level name="ERROR2" visible="true" table="ERROR_dim" column="ERROR2" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
          </Level>
          <Level name="ERROR4" visible="true" table="ERROR_dim" column="ERROR4" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
    
      <Dimension type="TimeDimension" visible="true" highCardinality="false" name="DATE">
        <Hierarchy visible="true" hasAll="true" primaryKey="DATE_ID">
          <Table name="date_dim">
          </Table>
          <Level name="YEAR" visible="true" table="date_dim" column="year" type="Integer" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never">
          </Level>
          <Level name="MONTH" visible="true" table="date_dim" column="month" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
          </Level>
          <Level name="DAY" visible="true" table="date_dim" column="day_of_month" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
    
      <Dimension type="StandardDimension" visible="true" highCardinality="false" name="MASCHINE_SPEC">
        <Hierarchy visible="true" hasAll="true" primaryKey="MASCHINE_SPEC_ID">
          <Table name="MASCHINE_SPEC_dim">
          </Table>
          <Level name="TYPE" visible="true" table="MASCHINE_SPEC_dim" column="TYPE" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
          </Level>
          <Level name="SERIES" visible="true" table="MASCHINE_SPEC_dim" column="SERIES" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
          </Level>
          <Level name="MODEL" visible="true" table="MASCHINE_SPEC_dim" column="MODEL" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
          </Level>
          <Level name="maschine_code" visible="true" table="MASCHINE_SPEC_dim" column="maschine_code" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
      
     <!-- CUBE # 1 -->
    
      <Cube name="ERROR_EVENT_CUBE" visible="true" cache="true" enabled="true">
        <Table name="ERROR_events_facts">
        </Table>
        <DimensionUsage source="ERROR" name="ERROR Dimension" visible="true" foreignKey="ERROR_ID" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="DATE" name="DATE Dimension" visible="true" foreignKey="DATE_ID" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="MASCHINE_SPEC" name="MASCHINE_SPEC" visible="true" foreignKey="MASCHINE_SPEC_ID" highCardinality="false">
        </DimensionUsage>
        <Measure name="SEVERE" column="SEVERE" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
        <Measure name="MEDIUM" column="MEDIUM" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
        <Measure name="FATAL" column="FATAL" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
      </Cube>
      
       <!-- CUBE # 2 -->
      
      <Cube name="OP_PARAMS_CUBE" visible="true" cache="true" enabled="true">
        <Table name="op_parameter_facts">
        </Table>
        <DimensionUsage source="DATE" name="DATE Dimension" visible="true" foreignKey="DATE_ID" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="MASCHINE_SPEC" name="MASCHINE_SPEC Dimension" visible="true" foreignKey="MASCHINE_SPEC_ID" highCardinality="false">
        </DimensionUsage>
        <Measure name="TOT_HRS" column="TOT_HRS" datatype="Numeric" aggregator="sum" visible="true">
        </Measure>
        <Measure name="REV_HRS" column="REV_HRS" datatype="Numeric" aggregator="sum" visible="true">
        </Measure>
        <Measure name="TOT_CYC" column="TOT_CYC" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
        <Measure name="REV_CYC" column="REV_CYC" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
      </Cube>
      
       <!-- CUBE # 3 -->
      
      <Cube name="ENGINEER_STAFF_CUBE" visible="true" cache="true" enabled="true">
        <Table name="ENGINEER_STAFF_facts">
        </Table>
        <DimensionUsage source="ERROR" name="ERROR Dimension" visible="true" foreignKey="ERROR_ID" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="DATE" name="Date Dimension" visible="true" foreignKey="DATE_ID" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="MASCHINE_SPEC" name="MASCHINE_SPEC Dimension" visible="true" foreignKey="MASCHINE_SPEC_ID" highCardinality="false">
        </DimensionUsage>
        <Measure name="MAINTENANCE" column="MAINTENANCE" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
        <Measure name="MASCHINE_LEADER" column="MASCHINE_LEADER" datatype="Integer" aggregator="distinct-count" visible="true">
        </Measure>
        <Measure name="FOREMAN" column="FOREMAN" datatype="Integer" aggregator="sum" visible="true">
        </Measure>
      </Cube>
      
       <!-- Virtual CUBE -->
      
      <VirtualCube enabled="true" name="VirtualCube" visible="true">
        <VirtualCubeDimension name="ERROR" visible="true">
        </VirtualCubeDimension>
        <VirtualCubeDimension name="DATE" visible="true">
        </VirtualCubeDimension>
        <VirtualCubeDimension name="MASCHINE_SPEC" visible="true">
        </VirtualCubeDimension>
        <VirtualCubeMeasure cubeName="ERROR_EVENT_CUBE" name="[Measures].[SEVERE]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="ERROR_EVENT_CUBE" name="[Measures].[MEDIUM]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="ERROR_EVENT_CUBE" name="[Measures].[FATAL]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="OP_PARAMS_CUBE" name="[Measures].[TOT_HRS]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="OP_PARAMS_CUBE" name="[Measures].[REV_HRS]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="OP_PARAMS_CUBE" name="[Measures].[TOT_CYC]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="OP_PARAMS_CUBE" name="[Measures].[REV_CYC]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="ENGINEER_STAFF_CUBE" name="[Measures].[MAINTENANCE]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="ENGINEER_STAFF_CUBE" name="[Measures].[MASCHINE_LEADER]" visible="true">
        </VirtualCubeMeasure>
        <VirtualCubeMeasure cubeName="ENGINEER_STAFF_CUBE" name="[Measures].[FOREMAN]" visible="true">
        </VirtualCubeMeasure>
      </VirtualCube>
    </Schema>
    I'm grateful for any hint

    Kind Regards!

  4. #4

    Default

    pstoellberger helped me out in the pentaho IRC chat.
    The problem was, that the "name" attributes of the dimension usages were not the same as the actual dimension names and the virtual dimension names.
    Now everything works as expected

  5. #5

    Default

    Quote Originally Posted by Heisenberg View Post
    pstoellberger helped me out in the pentaho IRC chat.
    The problem was, that the "name" attributes of the dimension usages were not the same as the actual dimension names and the virtual dimension names.
    Now everything works as expected
    I am having the same issue and this is not resolving the problem. I only get values if I choose the [ALL] Level of my Dimensions.

    Any suggestions from the house?

    Many thanks

  6. #6

    Default

    Quote Originally Posted by Osdeadlock View Post
    I am having the same issue and this is not resolving the problem. I only get values if I choose the [ALL] Level of my Dimensions.

    Any suggestions from the house?

    Many thanks
    Hi!

    Let me guess, you only get the the ALL Level for a fact if you use a dimension which is not linked to the fact table, right?
    To get rid of the problem google for the "ValidMeasure()" function of Mondrian and make sure you use the latest version of Mondrian, not the one which is provided with Penthao 4.8 by default (except saiku, it always includes the trunk snapshot of Mondrian).

    If the problem is not as I gussed, please provide you schema so it's easier to help you

    Regards

  7. #7

    Default

    Quote Originally Posted by Heisenberg View Post
    Hi!

    Let me guess, you only get the the ALL Level for a fact if you use a dimension which is not linked to the fact table, right?
    To get rid of the problem google for the "ValidMeasure()" function of Mondrian and make sure you use the latest version of Mondrian, not the one which is provided with Penthao 4.8 by default (except saiku, it always includes the trunk snapshot of Mondrian).

    If the problem is not as I gussed, please provide you schema so it's easier to help you

    Regards
    Many thanks. That is exactly my problem, same as what you described in your original post. I will do as suggested and let you know. BTW - I am running 5.1

    Regards

  8. #8

    Default

    Quote Originally Posted by Heisenberg View Post
    Hi!

    Let me guess, you only get the the ALL Level for a fact if you use a dimension which is not linked to the fact table, right?
    To get rid of the problem google for the "ValidMeasure()" function of Mondrian and make sure you use the latest version of Mondrian, not the one which is provided with Penthao 4.8 by default (except saiku, it always includes the trunk snapshot of Mondrian).

    If the problem is not as I gussed, please provide you schema so it's easier to help you

    Regards
    Solved.. The virtual cube just needed a foreign key map to the right dimensions.

    Many thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •