Querying fact tables for lower-level members (SAP BPC - MS version)

Top-level dimension members aren't represented at all in BPC fact tables (SQL side), so if you need to find records that belong to a top-level member, you either need to manually look up all of the lower-level members, or have a query do it for you. Here's one I put together using a basic recursive CTE that will give you all records that contain lower-level members, given a higher-level member:

WITH a
AS (
 SELECT *
 FROM mbrDataSrc_F
 WHERE [ID] = 'Top_Level_Datasource_Name' --top parent
         
 UNION ALL
         
 SELECT b.*
 FROM mbrDataSrc_F b
  JOIN a
   ON b.PARENTH1 = a.[ID]
        )
SELECT *
FROM tblFactForecast
WHERE DataSrc_F IN (
 SELECT [ID]
 FROM a
)

If you're not familiar with them, the SQL after the UNION is the recursive part (some more info on recursive Common Table Expressions). I use them a lot in place of derived tables for their slightly cleaner syntax, but don't usually have too much of a need for recursion.

BTW, I don't believe in SELECT *, except for ad-hoc queries, which this one was for me. There is an interesting discussion about it over at StackOverflow.com - basically, minimal performance gains in most situations, but more clean and safer to select the actual columns.