Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Fact table with 100 million records and Aggregate table

  1. #1
    Join Date
    Jun 2007
    Posts
    128

    Default Fact table with 100 million records and Aggregate table

    Hi,

    I have a fact table with more than 100 million records per a day.
    I have create a aggregate table.
    I have created cube with three dimensions and one measure.
    Mondrian is using the aggregate table to get the measure when we click on the cube in pentaho.
    Then I drill down on the first dimension .Then the query is on the dimension and Aggregate table.
    Till here every thing is fine.
    Now wanted to drill down on the second dimension.
    This time the query is on the dimension and fact table to get all dimension values.
    Then measures are taken from aggregate table.
    Because my fact table has more than 100 million records per day querying the fact table is taking so much time.
    Instead if it can get second dimension values also from aggregate table it will be very fast.
    What is the work aroung for this?
    If there is a way to make all queries to use aggregate tables please help me.

    Thanks
    Sreelatha

  2. #2

    Default

    You need to create an additional aggregate table that has the granularity appropriate to the second dimension.

    Have a look at the CmdRunner tool. It can suggest the right aggregate table for a given MDX query and schema.


    Sherman
    JasperSoft

  3. #3
    Join Date
    Jun 2007
    Posts
    128

    Default

    Hi,

    Thank you very much for the reply.
    Do we need to generate aggregate tables for each dimension seperately.


    Thanks
    Sreelatha

  4. #4
    Join Date
    Jun 2007
    Posts
    128

    Default

    Hi,

    I have created an aggregate table as suggested by AGGGEN for the second dimension.But mondrian still using fact table itself.


    Thanks
    Sreelatha

  5. #5

    Default

    Hi,

    We have got a 6 dimension cube with leveled hierarchies and all aggregate tables and it seems to work for some queries but not others... I don't know if any of this will help you:


    1) Make sure you have enabled mondrian to use the aggregates in the properties file.

    2) Make sure there are no typos in your mondriam config file. Make sure everything is in the "natural" order in the file.

    3) For what I was doing I had to make "native.crossjoin=false" to use the deeper aggregate tables. This causes performace issues for cross-join queries however.


    Also yes, you can combine the aggregates in your dimensions tables and Mondrian can recognize and use them fine.

    Good Luck!

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.