PerformancePoint Planning Hierarchy Sorting

We ran into an issue earlier in the year where we wanted to sort our PPS Planning Product hierarchy through an automated process as part of the nightly batch cycle.  The Product dimension is custom defined and we refresh the data for both D_Product and H_Product tables daily.  We created a flat hierarchy in the Member Set (all children have the same parent in the Member Set or H_ table).  So in order to create the hierarchical relationships, we use a Member View based off of member properties (attribute columns) in the Product dimension to create the actual hierarchy relationships.  Regardless of how you decide to create your hierarchical relationships (Member Sets versus Member Views), you’ll likely want to sort the records.  I have not had success trying to update the SequenceNumber field using BizSystemFlag = 300 methodology.  My goal was to avoid writing an actual UPDATE statement against the Appliction database, so I use the work around below when loading data to the H_Product table.  Through this SQL, I am able to set the Sequence Number using the ROW_NUMBER() OVER() functions when inserting records into the H_ table on the StagingDB using BizSystemFlag = 200.  PerformancePoint automatically increments the SequenceNumber by 1024, so I follow the same increment standards in the example below.  The SQL statement sorts the data based upon member property columns (attributes) that are user defined in the dimension.  If you are using SSIS to perform the ETL, the SQL statement would be in your OLE DB Source and the destination would be the H_Product table.  For this process to work, you would first need to delete the data from the H_ table prior to loading using BizSystemFlag = 400.

SELECT
   NULL as RowId
  ,d.MemberId
  ,0 as ParentMemberId
  ,a.SequenceNumber
  ,200 as BizSystemFlag
  ,NULL as BizSystemErrorDetails
FROM D_Product d
JOIN (   
     SELECT MemberId, 
     ROW_NUMBER() OVER(
            ORDER BY Attribute1, Attribute2, Attribute3, [Name])* 1024 as SequenceNumber
     FROM D_Product
     WHERE Label NOT IN (‘NONE’)
     ) a
ON d.MemberId = a.MemberId
ORDER BY a.SequenceNumber

 
I hope this helps.
– ez

Advertisements