none
SSAS Cube : De-duplication of facts RRS feed

  • Question

  • Hi,

    I have a question on offsetting some fact values from the cube based on business rules..

    Scenario : 

    I am getting sales figures for a Product from 2 different sources, with 2 different names (P1, P2). Now, the system treats them as separate products, as they need to be seen by different groups of users.

    However, for a person looking at the Overall Sales figures, the values double up (as its the same product actually).

    Hence customer now wants to offset the sales figure somehow, so that the figures appear correctly at Product Level, but one of them is ignored when the cube is aggregated at Company Level.

    One work-around is to generate as set of -ve figures as a 3rd product (say, P3).

    This solves the aggregation at Product and Company Level.

    But, the "P3" product is also visible on the cube with all -ve sales figures, which needs to be hidden cosmetically (but used in aggregation).

    Is there a way to do this?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 7, 2019 3:35 PM

All replies

  • Hi Ashu_Blueray,

    Thanks for your question.

    >>But, the "P3" product is also visible on the cube with all -ve sales figures, which needs to be hidden >>cosmetically (but used in aggregation).

    >>Is there a way to do this?

    You could configure roles and permissions for SSAS database, grant access permission to dimension table "P3" with dimension data, so that the product would not be visible to specific users.

    Reference

    Configuring permissions for SQL Server Analysis Services

    Roles and Permissions (Analysis Services)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 8, 2019 6:07 AM
  • Thanks Will, but configuring roles would mean, that the members will be hidden, and would not be included in aggregation too...

    I need the value to be in aggregation, but not shown to the users, as it will not be an actual sales figure, but something autogenerated to offset the values.

    Any approach other than generating negative offset figures is also welcome!


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 8, 2019 8:44 AM
  • Hi Ashu_Blueray,

    Thanks for your reply.

    You could consider granting custom access to cell data, so the specific aggregation measure would not be visible to some users. Please refer:

    Grant custom access to cell data (Analysis Services)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 6:03 AM
  • Hi Will, 

    Thank you for the help. However, this does not help resolve the business use case.

    It seems, the use case is not a possible thing with SSAS cube.. 

    I have tried custom access, but it hides the fact fully from a specific user. While I need the value to be seen by the user. Just that, the value should not be included at certain levels of aggregation...


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 12, 2019 8:56 AM
  • I have tried custom access, but it hides the fact fully from a specific user. While I need the value to be seen by the user. Just that, the value should not be included at certain levels of aggregation...

    Hi Ashu_Blueray,

    Thanks for your reply.

    Or you could follow the examples of the two blogs to achieve cell-level security custom access.

    Cell Data Access Vs Dimension Data Access in Analysis Services

    Cell Security: when Read permissions are actually Read Contingent

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 6:49 AM