REST API - Cube view query that have parameters ?

[Sergey]

Dear community,

I am currently using the REST API (using the Personal Access Token that really works well !) in order to retrive a Cube View.

It's very easy to use as it works just like a regular Data Adapter configuration. However, the cube view that I query is using 2 parameters. If I don't specific the parameters and a value in the REST API query, nothing is returned.

I can specify the value manually, however I will lack the flexibility of the cube view to retrieve specific values. How can I change the REST API call for my cube view so that the parameters are indeed prompted ?

{
"BaseWebServerUrl": "XXXXXXX",
"ApplicationName": "Production",
"CubeViewName": "PVM_Graph2",
"DataTablePerCubeViewRow ": true,
"ResultDataTableName": "ResultDataTableNames",
"CustomSubstVarsAsCommaSeparatedPairs": "P_POC_INP_UD7_Part1=D1,P_POC_INP_UD7_part2=D2",
"CubeViewDataTableOptions": {
"IncludeTitle": false,
"IncludeHeaderLeftLabel1": true,
"IncludeHeaderLeftLabel2": true,
"IncludeHeaderLeftLabel3": true,
"IncludeHeaderLeftLabel4": true,
"IncludeHeaderCenterLabel1": true,
"IncludeHeaderCenterLabel2": true,
"IncludeHeaderCenterLabel3": true,
"IncludeHeaderCenterLabel4": true,
"IncludeHeaderRightLabel1": true,
"IncludeHeaderRightLabel2": true,
"IncludeHeaderRightLabel3": true,
"IncludeHeaderRightLabel4": true,
"IncludePovCube": true,
"IncludePovEntity": true,
"IncludePovParent": false,
"IncludePovCons": false,
"IncludePovScenario": false,
"IncludePovTime": false,
"IncludePovView": false,
"IncludePovAccount": false,
"IncludePovFlow": false,
"IncludePovOrigin": false,
"IncludePovIC": false,
"IncludePovUD1": false,
"IncludePovUD2": false,
"IncludePovUD3": false,
"IncludePovUD4": false,
"IncludePovUD5": false,
"IncludePovUD6": false,
"IncludePovUD7": false,
"IncludePovUD8": false,
"IncludeMemberDetails": true,
"IncludeRowNavigationLink": false,
"IncludeHasDataStatus": false,
"IncludeAnnotation": false,
"IncludeAssumptions": false,
"IncludeAuditComment": false,
"IncludeFootnote": false,
"IncludeVarianceExplanation": false
}
}

Regards,

[JackLacava]

I'm afraid the current REST api doesn't have anything to describe the CV, you can't use the regular endpoint to discover which parameters are required.

BUT! This is OneStream, there is always a hack. You can use the other GetAdoDataSet* calls to query the OneStream database to retrieve any information you need. CubeViews are in the CubeViewItem application table, where the field XmlData contains the entire CV description. You can retrieve that via REST, parse it to figure out the parameters, and then react.

From a performance perspective, the best way to do this would probably be to set up a Dashboard DataSet that uses SQL Parameterized Queries, then call it through GetAdoDataSetForMethodCommand. If that is too much bother (i.e. if this stuff is not called several times a day), you can just use GetAdoDataSetForSqlCommand or GetAdoDataSetForAdapter (calling a SQL adapter) instead, executing raw unoptimized SQL.