Forum Discussion

dbeavon's avatar
dbeavon
Contributor
3 years ago

Ad-hoc cube queries from client processes

I have heard that Onestream supports ad-hoc queries and reporting. But I haven't yet found an easy way to send ad-hoc queries to our OneStream cubes. I'm searching for some sort of query interface like SQL, MDX, or DAX.

 

So far I've discovered that there is a REST API which allows me to retrieve data from an existing "cubeview". This is a helpful API for extracting data programmatically. But this certainly isn't considered "ad-hoc" since it requires that a cubeview be prepared and deployed in advance.

I had also seen that the REST API supports SQL statements but I have been warned against using those; since it circumvents the logical definitions of the cube, and these queries might become fragile and break over time (eg. after future upgrades to the onestream platform).


Besides those two options, what mechanism is available to send "ad-hoc" queries to OneStream, and immediately retrieve the results. Our CPM data is very valuable to client processes, and we've been able to accomplish these types of queries in the previous implementation of CPM at my company.

We are currently using OneStream XF 6.3.0

Data Integration
  • ckattookaran's avatar
    ckattookaran
    VIP
    3 years ago

    I'm assuming you are trying to pull data from OneStream to consume in external tools. I would say a combination of FDX and REST API might get you there.

    You can use FDX to pull from a dataunit or stage. Since this works only on level zero, you might have to get creative with your rule to pull using functions.

    Shameless plug, there is a chapter in our book that talks about FDX and external SQL database in our book.

  • dbeavon's avatar
    dbeavon
    Contributor
    2 years ago

    Sorry for the trouble with that API.  It worked for us.

     

    However the simplest form of "CubeView" REST API was unreasonably slow for me.  We switched to using "data adapter" version of the API, along with a "BR" that relied on the "FDX" onestream-library to point (finally) back at the cubeview.  It is sort of a strange, round-about way of getting to the data ... but this ended up being the only thing that would perform reasonably will.  I'm not sure what black magic performance-powder they sprinkled into the "FDX" implementation ... but they should have put that everywhere else in the REST API as well.

    As a side, I'd be willing to spend more time participating in programming-related topics if they were done in stackoverflow.  I think this "one community" is probably not a suitable place for discussions about actual software development.  IMHO, This is not really a place to geek out to the level of depth that might be necessary.  I think onestream needs to start extending themselves outside these private/closed communities or it makes life very difficult for the average software developer.  No developers wants to log into multiple private portals in order to perform their day-to-day tasks.... 

  • dbug's avatar
    dbug
    New Contributor II
    2 years ago

    Hi , just curious how are you able to extract data from a cubeview with the REST API?  Do you use the 

    "DataProvider/GetAdoDataSetForCubeViewCommand?api-version=5.2.0" endpoint ?
    I've tried to print the result from the response but it doesn't contain data, only metadata ( i.e 'Title', 'HeaderLeftLabel1' etc)
    Im trying to see the numerical values within the cells. Can this be done ?