Forum Discussion

vcirigiri's avatar
vcirigiri
New Contributor
2 years ago

Table Name where Dimension are stored

Whats the Table name where all the Dimensions are stored ? I need to generate/Automate a report which will give me a list of all Dimensions setup in our system.

I can do it using the Extract process But I need to write a business rule to SFTP the output of the dimensions data

Reports
  • OS_Pizza's avatar
    OS_Pizza
    2 years ago

    There are different tables in the backend which connects Dimensions and members together.

    1. Dim Table - This gives you dimension Name and Dim type id.

     

    2. Member Table -

    3. Relationship Table -

     

    You can write a sql query using the data adapter to output the records.

     Below example to extract unique members.

    SELECT  
Member.MemberId, 
Member.Name, 
FROM 
Relationship 
RIGHT OUTER JOIN Member 
ON Relationship.DimTypeId = Member.DimTypeId AND Relationship.ParentId = Member.MemberId
WHERE 
(Member.DimTypeId = 0) AND (Relationship.ParentId IS NULL) 
ORDER BY 
Member.Name

     

    If the answer helped you , Please give kudos and mark the solution as accepted answer 😊

  • ChristianW's avatar
    ChristianW
    2 years ago

    Why are you not just using the BrApi member functions?

    Like this one: objList = BRApi.Finance.Members.GetAllMembers(si, dimPk, includeInheritedDims)

    You need to loop all dimensions, join the lists together and clean up the duplicates, but with Linq, it shouldn't be too hard to do.

    Cheers

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    There are different tables in the backend which connects Dimensions and members together.

    1. Dim Table - This gives you dimension Name and Dim type id.

     

    2. Member Table -

    3. Relationship Table -

     

    You can write a sql query using the data adapter to output the records.

     Below example to extract unique members.

    SELECT  
Member.MemberId, 
Member.Name, 
FROM 
Relationship 
RIGHT OUTER JOIN Member 
ON Relationship.DimTypeId = Member.DimTypeId AND Relationship.ParentId = Member.MemberId
WHERE 
(Member.DimTypeId = 0) AND (Relationship.ParentId IS NULL) 
ORDER BY 
Member.Name

     

    If the answer helped you , Please give kudos and mark the solution as accepted answer 😊

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    2 years ago

    Why are you not just using the BrApi member functions?

    Like this one: objList = BRApi.Finance.Members.GetAllMembers(si, dimPk, includeInheritedDims)

    You need to loop all dimensions, join the lists together and clean up the duplicates, but with Linq, it shouldn't be too hard to do.

    Cheers

    • rhankey's avatar
      rhankey
      Contributor
      2 years ago

      Agreed.  The OS api allows one to access everything to do with Dimensions, Members and Relationships via api or BRApi calls rather easily with no need to do anything in SQL.  Evidence of such is that I have a dimension comparison utility I use to compare any and all metadata changes right down to the varying properties between to OS Apps, or even between two different OS Environments.  No SQL required for any of this.

      I appreciate that for those relatively new to OS, it is often easier to mine data via SQL statements from the underlying OS SQL Application or Framework DB Tables.  However, it is my understanding that OS frowns upon accessing the data from any of their core tables for a variety of rather obvious reasons.  Learning the OS api, especially the calls to get basic metadata info will pay huge dividends with most OS development.  It's only when you get to some of the less needed "varying" properties where it can get a little tricky.  Looking at how the data is stored in the SQL tables can often provide strong clues as to how to get at it via the OS api.