03-22-2023 05:31 PM - last edited on 05-24-2023 12:48 PM by JackLacava
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
Solved! Go to Solution.
03-23-2023 01:26 AM
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 😊
03-23-2023 08:06 AM
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
03-23-2023 01:26 AM
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 😊
03-23-2023 08:06 AM
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
03-28-2023 09:59 AM
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.