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

Thread: MDX query to get the number of users that done some action

  1. #1
    Join Date
    Mar 2014
    Posts
    4

    Default MDX query to get the number of users that done some action

    Hi,

    I'm trying to create a query that returns the number of users that done some action (e.g. liked something). I have a Likes fact table, a Users dimension and some other dimensions. I initially used the NonEmptyCrossJoin function, but it's deprecated and returned unexpected results. This is the query I'm trying to use, but it returns always the total number of users, instead of the ones who liked something under certain conditions.

    WITH
    MEMBER [Measures].[UserCount] AS Exists( [Users].[Id].Members, [Measures].[Likes] ).count
    SELECT
    { [Measures].[UserCount] } ON COLUMNS,
    { [Genders].[Gender].Members } ON ROWS
    FROM Likes
    WHERE ( SOME_CONDITIONS )

    Any one knows what am I doing wrong here? or what is the correct query to obtain what I want?

    Thanks in advance

  2. #2
    Join Date
    Mar 2014
    Posts
    4

    Default MDX query to get the number of users that done some action (query with COUNT slow)

    I managed to create a query that returns what I want, but it's very very slow. This is the query:

    WITH
    MEMBER [Measures].[UserCount] AS COUNT( [Users].[Id].Members, EXCLUDEEMPTY)
    SELECT
    { [Measures].[UserCount] } ON COLUMNS,
    { [Countries].[Country].Members } ON ROWS
    FROM Likes
    WHERE ( SOME_CONDITIONS )

    Any thoughts on why is it so slow? I saw on the PostgreSQL logs that Mondrian was making 1 query per country, which seems to me a waste.

    Thanks,
    João Horta
    Last edited by jphorta; 05-21-2014 at 07:38 PM.

  3. #3
    Join Date
    Jan 2013
    Posts
    551

    Default

    Could you create a distinct-count measure on customer_id?

    Doing a count as you've done in your calculated member will iterate through the members of the set for each intersection in which [UserCount] is evaluated, so it doesn't surprise me that it's slow for you. Using a distinct-count aggregator will push the count to the db.

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
  •