Skip to main content
D
dbeavon
January 30, 2022
Solved

Is there a REST API for quickviews?

  • January 30, 2022
  • 3 replies
  • 3 views

I have some experience retrieving structured cube data from the REST API named "GetAdoDataSetForCubeViewCommand".


However, I'd like to get ad-hoc information. Ideally there would be another method that is analogous to retrieving data from an Excel "quickview".

Did I overlook a "quickview" mechanism in the REST API? I am using the documentation named "REST API Implementation Guide", but it doesn't mention anything that looks promising. Perhaps there may be some undocumented approach?

The cubeview already does much of what I need. However it requires preparation (the cubeviews must be prepared ahead of time and deployed to the server). It would be better if the API allowed "quickviews", since they don't require any previous preparation or infrastructure.

Currently we are using Onestream v.6.3.0.

 

Best answer by db_pdx

This was bugging me so I played around some more.  I think you can get a SQL like extract which can be filtered as needed.  Its certainly not the full functionality of an MDX query, but might be sufficient for your needs?  Basic setup:

Dashboard DataSet BR > Data Adapter > GetAdoDataSetForAdapter.

The key is/are the BRApi.Import.Data.FdxExecute... functions which are DataTable objects by default.  I was able to use FdxExecuteDataUnit to pull a major data unit and then filter the results down to relevant items with the filter string component (filters using SQL syntax). Note, this is base level data, not aggregated. Reference threads:

https://community.onestreamsoftware.com/t5/Reporting/Exporting-Data-automatically-to-outside-of-OneStream/m-p/3796#M528gg

https://community.onestreamsoftware.com/t5/Office-Hours/Question-Where-can-we-find-examples-of-FDX-Connectors/ta-p/1192

Hope this is enough to get you started.

-DB

3 replies

O
OS_Pizza
January 31, 2022

Are you looking our for functions related to quickview . Few of them which i have used -

 

  • RefreshQuickViews()

  • RefreshQuickViewsForActiveWorksheet()

 

D
dbeavonAuthor
January 31, 2022

No, I only mentioned Excel because that is where I first heard the term "quickviews".  I'm looking for a programming interface (REST API) that can retrieve data from onestream.  Ideally the data could be retrieved in a way that is comparable to what we can do with an Excel quickview.

 

The benefit of a quickview, over a cubeview, is that the quickview will allow you to retrieve ad-hoc data in any way you need.  This is easier than preparing the cubeview structure in onestream ahead of time.

D
db_pdx
January 31, 2022
dbeavon wrote:

The cubeview already does much of what I need...

What doesn't it do that you want added?  That might be the easier question to answer.

In general, if you are trying to make it more adhoc have you considered CustomSubstVarsAsCommaSeparatedPairs from the REST API.  Make your CV parameterized and use the subsitituion variables to make the query more dynamic.

D
dbeavonAuthor
January 31, 2022

>> What doesn't it do that you want added?  That might be the easier question to answer.

Ideally it would process a full MDX query.  Short of doing that, I would like to use the REST API to send my dimension selection (and levels and members) in a dynamic way rather than having to deploy a pre-defined cubeview ahead of time. 

 

Imagine if Excel users were limited to using cubeviews and didn't have the ability to build their ad-hoc quickviews?   That would  be extremely limiting and inconvenient.

 

I have used the parameters (CustomSubstVarsAsCommaSeparatedPairs ) but that doesn't allow me to make any substantial structural adjustments to the rows or columns of the report.

D
db_pdx
January 31, 2022
dbeavon wrote:

Ideally it would process a full MDX query.  Short of doing that, I would like to use the REST API to send my dimension selection (and levels and members) in a dynamic way rather than having to deploy a pre-defined cubeview ahead of time. 

Mmm, that would be nice.  I'm not aware of a means for during that currently, although I'm by no means an expert, hopefully someone else can jump in and provide a solution for this.

D
dbeavonAuthor
May 27, 2022

db_pdx Thanks again for your pointers.  I may have taken a lot longer to find this stuff if you hadn't pointed me to it.  I can tell you are quite a bit more familiar with the platform than I.

I forgot to circle back and update the forums.  Just to recap, there was no generic "quickview" mechanism for retrieving arbitrarily shaped data from the REST api.

 

However with the help of the new "FDX" api's, a developer can retrieve a large superset of the data they may need, and then do post-processing on the client side.  This is possible because of optimizations in the FDX api's.

 

There are some steps.... You first have to create a cubeview that defines the superset of data you are interested in having.  Make it sufficiently large so that it can answer a lot of questions.  Mine is defined to a scope that almost matches the "data unit" (ie. retrieves all interesting data for an entity and year).


Once you have a cubeview defined, you can access it via REST using the "GetAdoDataSetForAdapter" endpoint.  There are a few unintuitive hoops you need to jump thru to get to your cubeview.

  • REST calls Dataset Adapter
  • Dataset Adapter calls Dataset BR
  • Dataset BR calls FDX Execute (BRApi.Import.Data.FdxExecute...)
  • FDX Execute runs your cubeview

 

Unfortunately this approach isn't ideal (as you can probably see for yourself).  It is quite unintuitive and I'd go so far to say that experienced software developers might look at many, many alternatives before they finally land on this one.  By that time they've rebuilt their client software a number of times.  It is a pity that some of these layers couldn't be removed or simplified.    I think the complexity is related to the fact that OS product team may assume a normal cubeview REST call will perform well under most instances (and you won't need FDX).  This is not the case.  Also the complexity is probably temporary - and may be related to the fact that FDX is a recent addition to the product and isn't exposed directly via the REST API.

Hope this helps others who need faster, or more flexible data out of onestream.

 

PS. Here is some additional explanation of FDX

https://community.onestreamsoftware.com/t5/Reporting/FDX-methods-like-FdxExecuteCubeView-Why-are-they-faster/m-p/7228#M1297

 

 

Terms & ConditionsAccessibility statement