ssasdata-warehouseperformancepointscdscd2

Managing PerformancePoint Filters With Slowly Changing Dimensions

Just a bit of background info:

I have dimension table which uses SCD2 to track user changes in our company (team changes, job title changes etc) See example below:

Some of the User dimension columns

I've built an Analysis Services Cube and created all the necessary hierarchy's for the dimensions and it works well when navigating and drilling down through the fact table.

SSAS Hierarchy

The problem I have is with the filters on the PerformancePoint dashboard. As I'm using the User Dimension table with it's multiple instances of users it's showing duplicates up in the list. I can understand why as the surrogate ID is being referenced on the Dimension. But if I choose the first instance of the A-team I will see all their sales for a particular period and if I choose the second instance I will see all their sales for a different period.

Example of 1 of the duplicated team names

What is the best way to handle this type of behavior? Ideally I'd like to see a distinct list of teams in alphabetical order and when I choose the team name it shows all of their data over time.

I've considered using MDX query filters but I'd like to see if there's anything I haven't thought about.

I realise this isn't an easy and quick question but any help would be appreciated!

Solution

  • The answer was simple after having a trawl through my User Dimension table on the Cube.

    1. Under my user dimension I added 2 duplicate attributes to my attributes list ("Team Filter" is a copy of "Team", "User Filter" a copy of "User Name") these will be used only for filtering the dashboard.

    Dimension Designer Window

    1. Under the attribute properties for each duplicate I then set AttributeHierarchyOptimizedState to "Not Optimized", I also set their AttributeHierarchyVisible to false as I'd shown the two duplicate attributes in the hierarchy window in the middle.

    Dimension Attribute Properties

    1. Deploy your Cube to the server and go in to PerformancePoint. Create a new MDX Filter (this image shows the finished filter)

    Create a MDX Filter in PerformancePoint

    1. This is the code I used, it only shows dimension members which have a fact against them (reduces the list a considerable amount) and by using allmembers at the dimension it also gives me the option to show "All" at the top of the list.

    MDX Code

    1. Deploy the new filters and now you can see the distinct list of users and teams, works perfectly and selects every instance (regardless of the SCD2 row)

    Dashboard Filters now working