Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Omit tuples with NON EMPTY Dimension PROPERTIES for each child

  1. #1
    Join Date
    Aug 2011
    Posts
    14

    Question Omit tuples with NON EMPTY Dimension PROPERTIES for each child

    Hello,

    I'm having a hard time figuring this out.

    I want to remove all the tuples from a fact table where a dimensions property is null.

    Here's what I'm working with, the SELECT statements ON COLUMNS and on ROWS are fine. The trouble starts in the WHERE CROSSJOIN, specifically in the set of UNION statements:

    Code:
          select NON EMPTY Hierarchize(Union({[platform].[All platforms]}, [platform].[All platforms].Children)) ON COLUMNS,
            NON EMPTY {(CurrentDateMember([eventDate], "[""eventDate""]\.[yyyy-mm-dd]").Lag(14.0) : CurrentDateMember([eventDate], "[""eventDate""]\.[yyyy-mm-dd]"))} ON ROWS
    from [product_offers]
    where CROSSJOIN(
                   Crossjoin(
                    {[clientType.ClientTypeID].[${ClientTypeID}]}, 
                    {[eventType].[Product Search], 
                      [eventType].[Product Shopping List], 
                      [eventType].[Product Featured], 
                      [eventType].[Product Add to Cart], 
                      [eventType].[Product Buy Online], 
                      [eventType].[General Product View]
                    }),
                    UNION(
                      FILTER({[productView.default].Children},
                        [productView.default].CurrentMember.Properties("ProximityPerk") = "ProximityPerk"),
                      UNION(
                        FILTER({[productView.default].Children},
                          [productView.default].CurrentMember.Properties("OfferType") = "Coupon"),
                        UNION(
                          FILTER({[productView.default].Children},
                            [productView.default].CurrentMember.Properties("OfferType") = "Offer"),        
                          FILTER({[productView.default].Children},
                            [productView.default].CurrentMember.Properties("OfferType") = "UniqueCoupon")
                          )
                        )
                      )
                   )
    The above actually works perfectly, but it's a bit brittle and quite slow.

    As far as the brittleness goes, the values: Coupon, Offer and UniqueCoupon represent all the non-null values for the dimensions property, but I'd have to add another nested UNION if any additional values begin appearing in this property.

    As far as the speed, i'm sure it would be faster if I could figure out how to avoid gathering all the possible values by nested unions. Instead, I'd rather just omit all the tuples whose [productView.default].CurrentMember.Properties("OfferType") IS NOT NULL. <- that doesn't work of course, but I'm looking for an equivalent statement, or if that's not possible, some function that "subtracts" one set from another based on each tuples value for a property.

    Thanks,

    Larry
    Last edited by lars30; 12-05-2011 at 12:46 PM. Reason: clarified that the MDX query does work, but what's wrong

  2. #2
    Join Date
    Aug 2011
    Posts
    14

    Thumbs up Brittleness solved, still slow though

    Ok so we've sovled the Brittleness problem (Thanks Kaapa in #pentaho IRC).

    Still quite slow, but at least it's more flexible now.

    Code:
    select 
        NON EMPTY Hierarchize(Union({[platform].[All platforms]}, [platform].[All platforms].Children)) ON COLUMNS,
        NON EMPTY {(CurrentDateMember([eventDate], "[""eventDate""]\.[yyyy-mm-dd]").Lag(14.0) : CurrentDateMember([eventDate], "[""eventDate""]\.[yyyy-mm-dd]"))} ON ROWS
    from [product_offers]
    where CROSSJOIN(
                  Crossjoin(
                    {[clientType.ClientTypeID].[41]}, 
                    {[eventType].[Product Search], 
                      [eventType].[Product Shopping List], 
                      [eventType].[Product Featured], 
                      [eventType].[Product Add to Cart], 
                      [eventType].[Product Buy Online], 
                      [eventType].[General Product View]
                    }),
                      UNION(
                        FILTER({[productView.default].Children},
                          [productView.default].CurrentMember.Properties("ProximityPerk") <> "" ),
                        FILTER({[productView.default].Children},
                        [productView.default].CurrentMember.Properties("OfferType") <> "" )
                      )
                  )

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.