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

Thread: MDX query

  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Default MDX query

    Hi,
    I am new to pentaho analysis services.
    I have a query regarding MDX.

    Here is the query:
    with member [Measures].[test] as 'Count(Distinct({[fact tran MEMBER ID].Children, [fact tran MEMBER ID.fact tran MEMBER ID].[fact_transactions.FK_MEMBER_ID].CurrentMember.Parent}))'
    select NON EMPTY Crossjoin(Hierarchize({([Measures].[COUNT OF EVER ACTIVE MEMBERS], [CITY.CITY].[All city]), ([Measures].[test], [CITY.CITY].[All city])}), {[YEAR.YEAR].[All year]}) ON COLUMNS,
    NON EMPTY {([ENROLMENT SOURCE.ENROLMENT SOURCE].[All enrolment source], [MEMBER RECENCY.MEMBER RECENCY].[All member recency])} ON ROWS
    from [test7]

    Here, I am trying to get a distinct member count from a table which has more than 1 entry for 1 member.
    Hence I have used distinct and count functions in the query.
    But, the the value of the expression "[Measures].[test]" does not change as we drill down different levels in the pentaho_demo_mysql5-1.6.0.GA.863 solutions server(OLAP). The value is static
    Foe Ex: If I drill down the year dimension to look at the data for a quarter, the distinct count still shows the entire count, not the distinct count for that quarter.

    Does anyone know how to solve the problem??
    Attached Images Attached Images

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Count(Distinct(...)) might be the right construct to use in SQL, but MDX is not SQL. If you want to find the number of distinct members of a given dimension contributing to a given cell, use the distinct-count aggregation function.

    Julian

  3. #3
    Join Date
    Jan 2008
    Posts
    4

    Default RE: MDX query

    It works with distinct-count aggregator.
    thanks.

  4. #4
    Join Date
    Feb 2008
    Posts
    1

    Default Distinct Count in Cube Designer

    I also have same problem for Distinct Count in Cube
    Cube Designer is do not have Distinct-count aggregate function.

    Does anybody implementated Distinct Count functionality, Please Let me know.

Posting Permissions

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