Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
6 months ago

Combine application table and UD dimension information

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?

Dashboards
  • AndreaF's avatar
    AndreaF
    5 months ago

    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!|)

     

  • Nikpowar97's avatar
    Nikpowar97
    Contributor
    6 months ago

    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's avatar
      AndreaF
      Contributor III
      5 months ago

      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!|)