Is there a REST API for quickviews?

dbeavon
Contributor

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.

 

2 ACCEPTED SOLUTIONS

db_pdx
Contributor III

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-OneS...

https://community.onestreamsoftware.com/t5/Office-Hours/Question-Where-can-we-find-examples-of-FDX-C...

Hope this is enough to get you started.

-DB

View solution in original post

dbeavon
Contributor

@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-the...

 

 

View solution in original post

10 REPLIES 10

OS_Pizza
Contributor III

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

 

  • RefreshQuickViews()

  • RefreshQuickViewsForActiveWorksheet()

 

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.

db_pdx
Contributor III

@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.

>> 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.

db_pdx
Contributor III

@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.

db_pdx
Contributor III

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-OneS...

https://community.onestreamsoftware.com/t5/Office-Hours/Question-Where-can-we-find-examples-of-FDX-C...

Hope this is enough to get you started.

-DB

@db_pdx Thanks for this.... I am hearing good things about FDX.  One other person mentioned it too.  But there is surprisingly little in the documentation.  Nor have I found much in my google searches.

 

I will start playing around with this too.  Do you do your development work right in onestream, or is there a way to use Visual Studio?  I find the onestream programming environment to be a bit restrictive since none of the usual keyboard shortcuts work in there...  I guess as a last resort I can copy/paste back and forth between onestream and a real IDE.

db_pdx
Contributor III

I'm currently just using the provided Business Rule writing environment; haven't tried to use VS.  I swear I've seen others using it in screenshots though.... and found a post by @tsandi that suggests it is possible (https://community.onestreamsoftware.com/t5/Rules/Formula-extract-Notepad-language/m-p/3524#M131).

If you figure out how to get setup in VS please share! 

As far as writing the code goes, I don't think that is hard in and of itself.  I think all these OS assemblies are part of the "OneStream studio installation" (a reportwriter I believe?).  I suspect it isn't hard to load those assemblies into a console app in VS to get the full coding experience (with intellisense, reflection, and what-not).

 

But this won't help much with the code-debug loop, since I'll be coding on one machine but the onestream client is installed on a remote desktop.  The only thing I can think of is that folks have learned to host their code on a network share and have some way to quickly import that into onestream (maybe a file-watcher recognizes when code is changing, and auto-imports it) .  Or perhaps these onestream BR's are actually hosted on a network share and can be directly updated via the network without even using the "import" procedure....  

 

I am new to onestream so I'm just doing guesswork.  One thing that is clear to me is that anyone who spends more than a few minutes a day with Business Rules won't want to use the embedded editor.  Even a simple thing like the Ctrl+F shortcut doesn't work.  Clearly OneStream's isn't trying to compete with VS for features or productivity.

dbeavon
Contributor

@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-the...