Ad-hoc interface or query language for programmatically extracting data
We recently migrated from the legacy “client API” to the “REST API”. They are fairly similar and the migration was straightforward.
We use these interfaces to retrieve data via custom “cubeviews”. The cubeviews act as a vehicle for exporting business data from onestream cubes.
However it would be more convenient if we did NOT have to build and deploy cubeviews each time we needed a new piece of data. I have not found any mechanism for this yet, either in the old “client API” or in the “REST API”.
In other words, it would be nice if there was an “ad-hoc” mechanism for retrieving data programmatically from dimensions and cubes. I haven’t found this yet, in the OneStream “DataProvider” interface. I see that there is an option for running SQL queries but I was told to avoid it since it is low-level and is used to interact with raw/internal SQL tables whose schemas would not be meaningful from a business perspective.
In short, I’m wondering if there is a way to extract data programmatically with a “quick view” (similar to what is possible in Excel). Please let me know. What I would really like to have is a general-purpose MDX or DAX query interface … but I’m guessing that is not available.
I thought I responded to your question earlier about DAX. It seems like my message didn't go through. The short answer is no; nothing in the REST API allows you to pull data in an ad-hoc fashion. It is merely a replica of client API with the client.
For the long answer, you can use FDX API methods to extract data in an ad-hoc way. Write those in a dashboard data set rule and pull it using the DataProvider methods. There is one to run method queries, and users can use that to run the Dataset rules, which will give you a DataSet, and there will be only one table (depending on how you wrote the rule). You can use this now for the downstream.
FDX is discussed in detail in our book (shameless plug)
ckattookaran I want to circle back and thank you again for your pointer to "FDX". Those API methods are really fast and flexible, but they aren't highly exposed in the docs. (They should be)
Another forums user also helped me connect the dots (to DataSet BR's, Dashboard Data Adapters, etc). See:
https://community.onestreamsoftware.com/t5/Reporting/Is-there-a-REST-API-for-quickviews/td-p/4652
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
... see that link above for more explanation.
