locked
Proclarity Selector not Selecting any Items when choosing to filter by Time that are Fiscal Year (or Month) to Date up to the Current Day RRS feed

  • Question

  • I have two Time Dimensions and two Measure Groups (Invoices and Sales Orders)  The Time Dimension (Invoice Date) that is joined to the Invoice Fact Table is working correctly, but the Time Dimension (Order Entry Date) is not working when I try to use the Selector to create a set that will pull the Days that are in Fiscal Month to Date, I even tried Fiscal Year to Date, but it comes up with 0 Items selected.

    Here is what i did to trouble-shoot this..
    1.  I verified that the Sales Order Entry Date Time Dimension was setup as a Time Dimension (Properties of the Dim: Type = Time)
    2.  I verifed that the Fiscal Day, Fiscal Week, Fiscal Month, Fiscal Year all had the appropriate Types also (Date, Weeks, Months, Years).
    3.  I verified that I had the appropriate Attribute relationships setup.
    4.  I made sure this non-working dimension looked exactly like the working dimension, it looked good.
    5.  When using Proclarity, it shows the dimension as a Time dim as it shows the clock icon beside it.
    6.  When I use the hierarchy to drill down, i made sure I had data that was in my Fiscal Month and there is.
    7.  I reviewed my DSV to make sure the joins between my fact and dim table was accurate and it was.
    8.  I reviewed the Dimension Usuage to make sure it was using the correct fields for the keys and it was accurate.
    9.  I deleted it and re-added it to see if it would work, it didn't.
    10.  I copied the mdx used from the Invoice Date and swapped out the appropriate fields and it gave me an error message about it being an empty set.

    I haven't tried putting this calc into the cube because that kinda defeats the purpose of letting my business users be able to use the Proclarity Selector to create other time calcs.

    Any other suggestions for trouble-shooting and I hope I didn't forget to indicate other steps that I took to resolve this.  I wasn't sure if maybe Proclarity has a restriction on the number of measure groups and time dimensions.  In the DSV, I am using the same time table for both dimensions and just joining on the appropriate fields in the two different fact tables.

    Here is the Invoice Date MDX that is created by the Selector...

    Intersect ([Invoice Date].[Invoice Date].[DAY].Members, PeriodsToDate ([Invoice Date].[Invoice Date].[FISCAL MONTH], Tail(Filter([Invoice Date].[Invoice Date].[DAY].Members, NOT IsEmpty(([Invoice Date].[Invoice Date].CurrentMember, [Measures].DefaultMember))), 1).item(0)))


    Sharon J
    Tuesday, July 7, 2009 10:33 PM

Answers

  • Thank you... I did use the profiler and discovered that the MDX that is getting generated by the Selector is using the [Measures].defaultmember and then in the Where, it's using a measure that is from Invoice Measures.  I have two measure groups and the Invoice measures are not connected to the Sales Order dates in the Dimension Usage tab. 

    I have figured out another method for getting the data/mdx I need, but did it in the Cube rather than being able to let the User use the Selector to build this.

    Is there a way to control or set the default measure for each of the measure groups based on what dimension they are in?  That way the Selector will pick up the correct measures?

    Thanks

    Sharon J
    • Marked as answer by jorg2195 Thursday, August 20, 2009 11:44 PM
    Monday, July 20, 2009 4:36 PM

All replies

  • Well, it certainly seems you've done some work on this already.  Have you tried running a profiler trace on SSAS while building the set to see what is being sent to the cube?  Once you have that, you can then run that in SSMS and see what the results are.  That should tell you if the Selector is building MDX that isn't working, or if in fact the cube isn't structured properly for the MDX, or maybe some combination of the two.  Sorry to be so vague, but the Selector is a complex tool and it's usually pretty difficult to troubleshoot it based solely on a description of behavior.  I hope this keeps you moving in the right direction though.


    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 9, 2009 3:52 PM
  • Thank you... I did use the profiler and discovered that the MDX that is getting generated by the Selector is using the [Measures].defaultmember and then in the Where, it's using a measure that is from Invoice Measures.  I have two measure groups and the Invoice measures are not connected to the Sales Order dates in the Dimension Usage tab. 

    I have figured out another method for getting the data/mdx I need, but did it in the Cube rather than being able to let the User use the Selector to build this.

    Is there a way to control or set the default measure for each of the measure groups based on what dimension they are in?  That way the Selector will pick up the correct measures?

    Thanks

    Sharon J
    • Marked as answer by jorg2195 Thursday, August 20, 2009 11:44 PM
    Monday, July 20, 2009 4:36 PM