Forum Discussion

Bella_Yu's avatar
Bella_Yu
New Contributor II
5 months ago

Retrieve member property in SQL

In Application tab > Dimensions > Entity Dimensions > Member Properties, I have something configured in Text 4 and Text 5  field in Vary By Scenario Type And Time section. The goal is to display these 2 field values in a dashboard.

 

Questions are

1. How can I retrieve the data in SQL in data adapter? I input test data in Text 4 and find the value in both RelationshipProperty and MemberProperty tables. Which table and column should I use? What filters are needed to get the expected value?

2. Any data dictionary for OneStream application database? For this type of requirement, use SQL and data adapter to get the value for specific fields in the front end, how can I find the respective table / column / filter.

Much Appreciated!

 

 

 

Dashboards
Reports
  • FredLucas's avatar
    FredLucas
    Contributor III
    5 months ago

    Hi Bella_Yu,

    You should as much as possible use the api functions instead of querying OS application tables directly which is usually not recommended.

    In this case you might want to look into using the following function:

    Dim sValue As String = BRApi.Finance.Entity.Text(si, entityId, textPropertyIndex, varyByScenarioTypeId, varyByTimeId)

     

  • rhankey's avatar
    rhankey
    Contributor
    5 months ago

    I would word the other response a little more strongly - you should not be accessing any of OS's out of box SQL tables directly.  In fact, I can't recall which OS document I have read it, but I am 99% sure OS specifically say NOT to access the out of box tables directly.  That is what the OS Api/BRApi is for.  And where both are available, always use the OS Api over BRApi.  I realize the OS Api is a rather steep learning curve, that may not seem overly palatable for those who come from a SQL background where it is easy to see the underlying data you want to access in simple to understand SQL tables.

    To your question, you can embed Dashboard parameters or XFBR rules within Dashboard CommandType=SQL Data Adapters.  That works fine for simpler, relatively static SQL statements.

    However, if you are constructing more dynamic/complicated SQL statements, in my opinion, you are better off pointing the Data Adapter to a Business Rule, in which you construct and execute the SQL statement, returning a DataSet or DataTable back to the Data Adapter.  With this method you have full access to the OS BRApi and .NET libraries, where you can access any OS Metadata information and have more tools than you could ever imagine to construct any SQL statement you would like and to post-process the returning DataTable(s) before passing a DataSet or DataTable back to the Data Adapter.

  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    5 months ago

    Joining the tables required for this sort of query does require some dependance on things like property IDs which do seem like something that could easily change between versions and yes there is some complexity introduced by the vary by scenario/time options. Obviously the tables could change too. I guess it is a risk/challenge that it is up to you to take.

    You do have another option if you want to go down the SQL route and I have seen it work quite well in numerous instances. There is nothing stopping you from replicating your usage of the Text fields by creating your own custom table to store this information. You can provide a nice interface that doesn't require access to the dimensions/members and is not limited by 5 text fields amount of fields either.