Forum Discussion

Jeevan's avatar
Jeevan
New Contributor II
7 days ago

How to pull a field from Custom table into a Cube view

Hi Everyone,

I have a requirement to pull the data in a particular format in my cube view. I have entity dimension members in a row which gets pulled from the cube. I have to pull the respective opening dates of the stores from the custom table in the cube view as a column. I have created the custom table (with store and opening date) and used a business rule to pull the opening date from the custom table. Now I want to display this in the cube view w.r.t the stores we are pulling in rows. For ex if we have a row with Entity (store) tx0001 in the cube view which has an opening date for the store in the custom table. This opening date needs to be displayed in the column w.r.t to the store in the row. How to achieve this? 

 

Thanks,

Jeevan

 

Cubeview
Dashboards
Extenders
Finance
XFBR

3 Replies

  • Jeevan's avatar
    Jeevan
    New Contributor II
    7 days ago

    Thanks, rhankey​ for the response. we pull the store and its opening date into a custom table from external source. Also, users can override this opening date manually. How can this date be setup in member text property.

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      7 days ago

      Probably easier in a custom table for user maintenance particularly if security is an issue. Think ahead and see if there's any other store information you should bring in to the same table too. 

      Make sure you use global cache as rhankey hinted at. Retrieve all data on the first lookup and store it in memory; you can store it in a dictionary object for quick/easy lookup. There are a number of examples of utilising the global cache in these forums.

  • rhankey's avatar
    rhankey
    Contributor III
    7 days ago

    You need to define a Dynamically Calculated member, usually in UD8, that retrieves the opening date value.  Being non-cube data, you would normally surface the value as V#Annotation.

    Now, onto how you are retrieving the opening date....  While you can build the dynamic calc member to do just about anything, you should be considering performance, as that dynamic calc member will need to be invoked for every Cube View cell that is to display the value.  So, if you have 1000 stores in the rows, you will have to retrieve 1000 opening dates - one-at-a-time.  Every time the Cue View is refreshed, that is all done again.

    The reason I mention that is that unless you can cache that opening date table into in-memory cache, that means for each opening date you need to surface, you need have your dynamic calc formula open a connection to the DB and issue a SQL statement to fetch the specific opening date value you are interested in from your custom table.  That is VERY expensive to do.

    A far better option would be to set the opening date into a Store member Text[n] property.  Rather than reading directly from your custom SQL table, you would have your dynamic calc retrieve the value for from the member's Text[n] property.  OS caches all the metadata into in-memory cache for use with the api functions.  So, as long as you use the OS api functions rather than the BRApi functions, this method can get the opening date info cheaply.