Combine application table and UD dimension information

[AndreaF]

Hi, I have an SQL data adapter that queries an application table, and one of the columns is the CostCenter. This CostCenter is also a dimension in our cube, where the table values correspond to the base-level members of that dimension. In the CostCenter dimension hierarchy, each cost center is either a descendant of Member A or Member B.

I want to add a column to the data adapter that indicates whether each cost center is a descendant of Member A or Member B, so that users can filter cost centers more easily in their dashboards. Does anyone have any ideas on how I could achieve this?

[Nikpowar97]

@AndreaF ,

I think this is possible using 2 ways, 
1. (Suggested method) Write a sql query in the adapter the Application DB tables doing some joins. below are the table names:
a. Member
b. Dim (Can be optional)
c. Relationship

OR 
2. (Avoidable due to low - performance)Since u have the Data adapter already you can you can use the same query in a dashboard data set Business Rule/ OR use function (BRApi.Dashboards.Process.GetAdoDataSetForAdapter(si, isSystemLevel, workspaceIDIfNotInName, combinedWsAndAdptrName, resultsTableName, customSubstVars)) and Can loop through the rows. and check if the member is a Descendant of a particular member and store the same creating a new column which u will add before looping.

Call the same in a adapter using a Method query Business rule 

{Dashboard_DSBrName, FunctionName, Param1 = Value1)

Dim Dt As Datatable '(Original Data table already created with your code (May be retrieved from a stored procedure/Sql query using Extrernal DB)

Dt.columns.Add("DescendantName") ' add new column

For Each dr as DataRow in Dt.Rows
    if BRApi.Finance.Members.IsDescendant(si, Brapi.Finance.Dim.GetDimPk(si,MyDimensionName),Brapi.Finance.Members.GetMemberId(si, dimtypeid.MyDimensionType,"A"),Brapi.Finance.Members.GetMemberId(si, dimtypeid.MyDimensionType,dr("CostCenter")) then   'check if member in column CostCenter is descendant of member A in the looped row
       dr("DescendantName") = "A"
    else if BRApi.Finance.Members.IsDescendant(si, Brapi.Finance.Dim.GetDimPk(si,MyDimensionName),Brapi.Finance.Members.GetMemberId(si, dimtypeid.MyDimensionType,"B"),Brapi.Finance.Members.GetMemberId(si, dimtypeid.MyDimensionType,dr("CostCenter")) then   'check if member in column CostCenter is descendant of member B in the looped row
       dr("DescendantName") = "B"
    end if
Next

Return Dt

Providing a pseudo code here

[AndreaF · Accepted solution]

@Nikpowar97 thank you for your reply. Option 1 is too complex in my case because the cost centers are not just children of Member A and B, are descendants at various levels down the hierarchy, so it would not be enough to join the Member and Relationship tables, I would need to do in a kind of recursive way.

I briefly tried the option 2 and I think it would work. However, I ended up implementing what I believe is an easier solution for my specific use case:

I have created 2 Literal value parameters each calling an XFBR rule returning the list of Member A descendant on one parameter and the list of Member A descendant on the other parameter, and then modified the SQL in the data adapter like this:

Select 
{Other Columns}
,CostCenter
,'A' As CostCenterType
Where CostCenter (|!prmAList!|)

UNION

Select 
{Other Columns}
,CostCenter
,'B' As CostCenterType
Where CostCenter (|!prmBList!|)