Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to define an indicator from a secondary table?

  1. #1
    Join Date
    Aug 2010
    Posts
    26

    Question How to define an indicator from a secondary table?

    Hello there,

    Here is my issue: I want to define an indicator in my Mondrian cube with a column coming from a table which is not the fact table.
    Let's say the cube is defined on a fact table "A". I already have defined a set of indicators, with columns from the A table. Now, I want to define a new one (aggregator: select-count), with a column coming from another table called "B". The relationship between A and B is "one to many". My problem is, within Mondrian Workbench, I could not find a way to define an indicator based on the B table, but only from A.
    One solution is to denormalize my star shema and bring the field from B to A. That works, but the number of rows in the A table is multiplied 100 times, and response time of the entire cube has really slowed down (where data from B is only required for that new indicator). So, I don't want to use the denormalized solution anymore.
    I noticed that a indicator definition can embed some SQL code in it, but I was not able to find the right syntax. Is that the way I should explore further? Anyone would have an example to give me?

    Thanks for your help.
    Regards,
    Franck

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    Perhaps you can use a view as a facttable?

  3. #3
    Join Date
    Aug 2010
    Posts
    26

    Default

    Actually, that is what I'm doing now, but with the same results, in terms of response time, as a denormalized table. The view is always solved (returning the results from "rows A" X "rows B"), even though the field from table B is not used in the report. That decreases the response time of the whole cube. The solution that I am after would look up table B only when the indicator is part of the report. With a denormalized table or a view solution, table B is always browsed.
    Thank you anyway.
    Franck

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.