locked
PPS 2010 filter using CustomData() RRS feed

  • Question

  • I've followed reference for creating MDX query filter from http://nickbarclay.blogspot.com/2008/01/pps-data-connection-security-with.html

    Cube was processed with Two Roles.
    ----------------------------------
    1st Admin Role :(using Unattended Service Account ie. sp_admin account). This will be used for process the cube and the same used for PPS service set to.
    2nd PPSRead Role: Read only on certain Locations.

    When I create a connection in PPS Dashboard Designer, I've used both roles (Admin,PPSRead roles). With out Admin role, I couln't able to connect to Cube.

    Followed same steps as Nick Barckely mentioned. When dashboard open with differenent user account, SQL Profiler lists CustomData property populated correctly.

    Dimesion 1: User
    Dimesion 2: Location.
    factless table: UserLocation.

    Expressions used in Allowed set of Cube Dimension Data tab in SSAS:
     
    EXISTS(
    [Loc2].[TotalLoc].MEMBERS,
    FILTER(
      [User].[User].[User].MEMBERS,
      [User].[User].CURRENTMEMBER.PROPERTIES("User name") = USERNAME
     )
     ,"User Location"
    )


    The same works fine by executing the MDX query, Locations were filtered as defined in "User Location" table.

    BUT, The PPS filter is showing as per the User Locations. It shows all locations.

    Any help Appreciated.

    Tuesday, October 4, 2011 7:21 PM

Answers

  • Bye the way, Thanks indeed for help & valuable information.
    • Marked as answer by SamTippu Thursday, October 6, 2011 9:28 PM
    Thursday, October 6, 2011 9:28 PM

All replies

  • It looks like lots of posting about related issue.  Followed everything as explained, where I'm missing.  Pl..

    Sam 

    Tuesday, October 4, 2011 7:32 PM
  • I tried to modify the MDX query expression above by adding CustomData(), the "|" syntax is not recognized. The below is the error

    TITLE: Edit Role - PPS
    ------------------------------

    Check MDX script syntax failed because of the following error:

    An error occurred in the MDX script for the dimension attribute permission's allowed member set: Parser: The following syntax error occurred during parsing: Invalid token, Line 5, Offset 70, | .

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.AnalysisServices.ManagementDialogs.MgmtDlgsSR&EvtID=CheckSyntaxFailMessage&LinkId=20476

    Any Ideas?

    sam

    Tuesday, October 4, 2011 7:50 PM
  • Just use CustomData() and do not include the second portion with the USERNAME. The USERNAME is if you are using Per User Security setup.

    So the MDX should look like the following:

    EXISTS(
    [Loc2].[TotalLoc].MEMBERS,
    FILTER(
    [User].[User].[User].MEMBERS,
    [User].[User].CURRENTMEMBER.PROPERTIES("User name") = CustomData()
    )
    ,"User Location"
    )


    Dan English's BI Blog
    Wednesday, October 5, 2011 11:11 AM
  • Thanks Dan!.

    After removed USERNAME, MDX expression syntax resolved. But still not seeing what expecting on PPS Dashboard Filter.

    When I exectute the MDX query, the results display exactly what I'm expecting

    -----------------------------------------------
    -- Displays 3 locations
    -----------------------------------------------
    select {} on 0,
    EXISTS([Loc2].[Loc].MEMBERS, FILTER([User].[User].MEMBERS,[User].[User].CURRENTMEMBER.PROPERTIES("Username")='AdvWorks\test1'),"User Location") on 1
    from [SecurityTest]

    -----------------------------------------------
    -- Displays 2 locations
    -----------------------------------------------
    select {} on 0,
    EXISTS([Loc2].[Loc].MEMBERS, FILTER([User].[User].MEMBERS,[User].[User].CURRENTMEMBER.PROPERTIES("Username") ='AdvWorks\test2'),"User Location") on 1
    from [SecurityTest]


    On PPS side, as Nick explained, Created MDX Query Filter on [Loc2] dimension
    MDX Query Filter : [Loc2].[Loc].MEMBERS

    Added Filter to Dashboard Page and deployed to sharepoint.

    Login to sharepoint as user "AdvWorks\test1", Seeing All locations. NOT FILTERED LIST

    Where I'm missing...


    One otherthing, In PPS data connection, I've used Admin Role, & PPS Role.


    How do I resolve this.. It seems very staight forward..

    Any help is greatly appreciated.
     

    Wednesday, October 5, 2011 5:48 PM
  • You might want to run a SQL Profiler trance on Analysis Services. Check the syntax of username passed from PPS. There may be a difference in format of username.
    http://dailyitsolutions.blogspot.com/
    Wednesday, October 5, 2011 6:43 PM
  • Thanks Umair,

    I've checked profiler. The connection I' using is "UnattendedAccount+Customdata ,  the Customdata has Username and format seems to be correct. below is a sample.

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
       <DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility>
       <Catalog>AdvWorksDW</Catalog>
       <Roles>AdminRole,PPSCustomData</Roles>
       <CustomData>AdvWorks\test1</CustomData>
       <LocaleIdentifier>1033</LocaleIdentifier>
       <Content>SchemaData</Content>
       <Format>Tabular</Format>
    </PropertyList>

    Is this the same you menioned?.

     

    Wednesday, October 5, 2011 7:16 PM
  • Is there any web.ini settings needed to added/changed for PPS 2010?. Nick's article is based out of PPS 2007 version I believe.

    I haven't touched any PPS related seetings in Server/Service side.

    Do we have to for CustomData ?.  

     

    Wednesday, October 5, 2011 7:55 PM
  • Nope, that has all moved to the data source authentication settings area now, so you can configure it by data source type and it is no longer global.

    Have you tried to create say a analytical chart, modify the MDX, and use custom MDX to create a measure and return the CustomData value? Just want to verify that on the dashboard.

    Can you provide the information on how you have the MDX on the SSAS Role? Sounds like that should work if the MDX queries worked in SSMS though.

    I think your issue right now is the results being returned in the MDX filter right?


    Dan English's BI Blog
    Wednesday, October 5, 2011 8:03 PM
  • Yes Dan!. When I created a MDX PPS Filter on [Loc2], based on user login, I shoud see limited set of locations.

    Analystic chart:

    I've created a simple Analytic Grid report with below query and it diaplays Username correctly with CustomData() function

    Query
    ----------------------------------------------------
    WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()
    Select [Measures].[MyUsername] on columns,
    [Loc2].members on rows
    From [AdvWorks]


    Regarding MDX on SSAS Role:

    I've two Dimesions [User] & [Loc2]. Per Nick, created Measure group "User Location" to filter on.

    The Dimension Security on Allowed Set on CUBE DIMENSION is
    -----------------------------------------------------------
    EXISTS(
    [Loc2].[Loc].MEMBERS,
    FILTER(
    [User].[User].[User].MEMBERS,
    [User].[User].CURRENTMEMBER.PROPERTIES("Username") = CustomData()
    )
    ,"User Location"
    )
    ------------------------------------------------------------

    Pl. help where I'm missing?.

     

    Wednesday, October 5, 2011 8:22 PM
  • Dan,

    Do you have/place any samples on Customdata how to use?. It's looks very close, but achieving the results the way expecting.

    sam 

    Wednesday, October 5, 2011 8:25 PM
  • With Profiler on analysis services, the CustomData is coming correctly

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
       <DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility>
       <Catalog>AdvWorksDW</Catalog>
       <Roles>AdminRole,PPSCustomData</Roles>
       <CustomData>AdvWorks\test1</CustomData>
       <LocaleIdentifier>1033</LocaleIdentifier>
       <Content>SchemaData</Content>
       <Format>Tabular</Format>
    </PropertyList>

    If queried MDX query for same user, getting correct filtered locations

    select {} on 0,
    EXISTS([Loc2].[Loc].MEMBERS, FILTER([User].[User].MEMBERS,[User].[User].CURRENTMEMBER.PROPERTIES("Username")='AdvWorks\test1'),"User Location") on 1
    from [SecurityTest]

    where I'm missing?

     

    Wednesday, October 5, 2011 8:29 PM
  • After several attempts and tryouts, figured out how to use apply security on PPS filter using MDX Query dashbaord filter in PPS 2010 with Customdata() option.

    This trick is in Filter Itself. As Nick Barckly (attached his article link above), PPS 2010 Filters won't work automatically by controlling security Dimension in SSAS. It may work for him with earlier PPS versions but not in PPS 2010.

    We need to do all that dynamic dimension security stuff (i.e using User dimension & factless association table etc.).
    In order to work Filters, We have to do the same CustomData() function in PPS filter itself.

    Here is the MDX query for PPS filter to display only those Resellers related to User:
    -------------------------------------------------------------------------------------------------------
    EXISTS(
    [Reseller].[Reseller].Members,
    STRTOSET("[User].[User Name].["+CustomData()+"]"),
    'Reseller User'
    )

     
    By applying CustomData() in PPS filter, You will only see those Resellers that were assigned in Association factless Table .

    Thursday, October 6, 2011 9:27 PM
  • Bye the way, Thanks indeed for help & valuable information.
    • Marked as answer by SamTippu Thursday, October 6, 2011 9:28 PM
    Thursday, October 6, 2011 9:28 PM