Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Distribution Chart query

  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Default Distribution Chart query

    Hi,
    I am trying to create a distribution chart in Pentaho BI from an OLAP cube:

    I have the following dimensions:

    • User (MAC address)
    • Time (month/date/etc)

    I have the following Measure

    • Session (Session ID with distinct-count aggregator)

    This is great for displaying number of sessions per unit of time. I can also select number of sesions per User, but this isn't so helpful as there are many many users.

    I'd like to show a chart showing number of sessions vs number of users.
    E.g.
    50 user had 1 session
    75 users had 2 sessions
    40 users had 3 sessions
    10 users had 4 sessions

    I'm thinking I need to add a calculated Measure, but I'm very new to MDX, and I can't think where to begin. I'd really appreciate any help or links to examples of this sort of thing.

    Many thanks
    Pat
    Last edited by itchyknee; 07-25-2011 at 12:08 PM.

  2. #2
    Join Date
    Jul 2011
    Posts
    13

    Default

    Hi,
    still struggling with this one. I have managed to get the figures I want from a straight SQL query:

    Code:
    select session_count, count(session_count) as user_count from
       (select dim_user.mac_add, count(fact_session.id) as session_count
       from fact_session, dim_user
       where fact_session.user_id = dim_user.user_id
       group by mac_add) as user_session_count
    group by session_count;
    But I have no idea how I can bring this into my cube definition. I'm wondering if I need to define another cube with User as the grain of the Fact table. But if I do this, how can I bring in time the dimension? Time is really associated with the user sessions, and not the user.

    Thanks for looking at this. Fingers crossed for some answers :-)

    Cheers
    Pat

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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.