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 b...
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

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