Skip to main content
D
dbeavon
Contributor
February 8, 2022
Solved

Ad-hoc interface or query language for programmatically extracting data

  • February 8, 2022
  • 3 replies
  • 0 views

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.

Best answer by ckattookaran

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)

3 replies

C
ckattookaranAnswer
Newcomer
February 10, 2022

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)

    D
    dbeavonAuthor
    Contributor
    February 10, 2022

    ckattookaran  Thank you for your reply.  Yes, I saw another reply from you on a similar question.  I had posted another question related to external reporting from onestream financial data. 

     

    This FDX API has not come up on my radar yet.  It hardly appears in any google searches.

     

    I revisited the documentation available to me (6.3.0), and finally found something about FDX but it was in an odd place.  It is only in the "BI Blend" documentation.  We have not yet made plans to use BI Blend. As of now we only use the base product for our reporting.  (We do much of our mission-critical reporting with the help of cube views, quick views, and data management).

     

    You didn't make any mention of BI Blend in your reply.  Based on the way you described FDX, it sounds like this can be used independent of that.  

     

    I would like to learn more and I wouldn't mind purchasing a book.  I don't find that much in my google searches about onestream, let alone can I find any third-party books about onestream.  If you can point me in the right direction, I'd greatly appreciate it.  

    C
    ckattookaran
    Newcomer
    February 10, 2022

    It got introduced with BI Blend maybe that is why it is there in the documentation. You can use it without BI Blend. Well, the book deals with quite a lot of Planning stuff (So maybe that is not what you are looking for). It is available on Amazon (search for OneStream Planning).

    D
    dbeavonAuthor
    Contributor
    May 27, 2022

    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.

     

      Terms & ConditionsAccessibility statement