03-31-2023 08:12 AM - last edited on 08-11-2023 11:34 AM by JackLacava
Is there a way to extract data from a cubeview with the OneStream REST API? I've tried using the
Solved! Go to Solution.
03-31-2023 10:01 AM
Yes. You can use that copy paste in the body of the API call.
{
"BaseWebServerUrl": "https://contoso.onestreamcloud.com/OneStreamWeb",
"ApplicationName": "GolfStream_v37",
"CubeViewName": "Gross Margin",
"DataTablePerCubeViewRow": false,
"ResultDataTableName": "ResultDataTableNames",
"CustomSubstVarsAsCommaSeparatedPairs": "",
"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" : true,
"IncludePovCons" : true,
"IncludePovScenario" : true,
"IncludePovTime" : true,
"IncludePovView" : true,
"IncludePovAccount" : true,
"IncludePovFlow" : true,
"IncludePovOrigin" : true,
"IncludePovIC" : true,
"IncludePovUD1" : true,
"IncludePovUD2" : true,
"IncludePovUD3" : true,
"IncludePovUD4" : true,
"IncludePovUD5" : true,
"IncludePovUD6" : true,
"IncludePovUD7" : true,
"IncludePovUD8" : true,
"IncludeMemberDetails": true,
"IncludeRowNavigationLink" : true,
"IncludeHasDataStatus" : true,
"IncludeAnnotation" : true,
"IncludeAssumptions" : true,
"IncludeAuditComment" : true,
"IncludeFootnote" : true,
"IncludeVarianceExplanation" : true
}
}
03-31-2023 10:04 AM
What I meant is: check the definition of your cubeview for things like the following:
That |WFYear| variable cannot be resolved automatically when executing the view from REST. So you have to pass the required value with the property "CustomSubstVarsAsCommaSeparatedPairs": [comma separated list of key value pairs as substitution variables with the following format: "VariableName1=[VariableValue1],VariableName2= [VariableValue2],..."]
In addition, make sure that all dimensions have a member specified either in the main POV or in row/columns, because via REST there won't be a UserPOV to fallback on.
03-31-2023 09:17 AM - edited 03-31-2023 09:23 AM
As far as I know, that's supposed to return data as well - note the format is organized by rows... This requires however that the cubeview will return some data with default settings, without relying on UserPov (you can pass variables in the post call).
If that's not possible for whatever reason, a simple fallback is to configure a DataAdapter that uses the cubeview, and then use the GetAdoDataSetForAdapter instead.
03-31-2023 09:49 AM
Hi @JackLacava Thanks for getting back so fast !
I have tried passing variables (the only ones I'm aware of are the ones from the doc - see screenshots below from the doc examples)
Are these the variables you're referring to ? If so, are you perhaps aware of other variables that would enable data to be visible? Otherwise I will attempt your DataAdapter suggestion instead
03-31-2023 10:04 AM
What I meant is: check the definition of your cubeview for things like the following:
That |WFYear| variable cannot be resolved automatically when executing the view from REST. So you have to pass the required value with the property "CustomSubstVarsAsCommaSeparatedPairs": [comma separated list of key value pairs as substitution variables with the following format: "VariableName1=[VariableValue1],VariableName2= [VariableValue2],..."]
In addition, make sure that all dimensions have a member specified either in the main POV or in row/columns, because via REST there won't be a UserPOV to fallback on.
03-31-2023 10:40 AM
This was indeed what was messing with my data - I first tried to remove the variables and hardcode the values in my cubeview layout and was able to see the data when i made the call request ! Thank you so much !
08-16-2024 11:55 AM
Can you provide an example of how this would be used? "CustomSubstVarsAsCommaSeparatedPairs" .
If for example I wanted to be able to pass the specific entity I want using this property how would I set it up in the cube view row and what would I then pass using that property? There aren't any examples in the documentation that I can find.
Thanks!
08-19-2024 04:44 AM - edited 08-20-2024 06:27 AM
Anything in the Cube View would use the standard Parameter syntax, e.g. |!MyParam1!| where you need to parameterize a name. There should plenty of examples of that in docs.
Then in the body of your json call, you would have it as just another property:
{
... all you other properties here ...
"CustomSubstVarsAsCommaSeparatedPairs": "MyParam1=MyValue,MyParam2=AnotherValue"
}
08-19-2024 12:01 PM
Hi Jack,
One question on the REST API itself:
How can we return something like a list of attributes that are not present in the receiving system to the calling system please? Any help would be greatly appreciated.
Thank You,
Indu
08-20-2024 06:26 AM
You can't, you have to know what the CubeView expects. Getting CV data requires admin rights, it is assumed that admins can log on and check. The REST API is not meant to be exploratory, but rather to automate admin tasks.
03-31-2023 10:01 AM
Yes. You can use that copy paste in the body of the API call.
{
"BaseWebServerUrl": "https://contoso.onestreamcloud.com/OneStreamWeb",
"ApplicationName": "GolfStream_v37",
"CubeViewName": "Gross Margin",
"DataTablePerCubeViewRow": false,
"ResultDataTableName": "ResultDataTableNames",
"CustomSubstVarsAsCommaSeparatedPairs": "",
"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" : true,
"IncludePovCons" : true,
"IncludePovScenario" : true,
"IncludePovTime" : true,
"IncludePovView" : true,
"IncludePovAccount" : true,
"IncludePovFlow" : true,
"IncludePovOrigin" : true,
"IncludePovIC" : true,
"IncludePovUD1" : true,
"IncludePovUD2" : true,
"IncludePovUD3" : true,
"IncludePovUD4" : true,
"IncludePovUD5" : true,
"IncludePovUD6" : true,
"IncludePovUD7" : true,
"IncludePovUD8" : true,
"IncludeMemberDetails": true,
"IncludeRowNavigationLink" : true,
"IncludeHasDataStatus" : true,
"IncludeAnnotation" : true,
"IncludeAssumptions" : true,
"IncludeAuditComment" : true,
"IncludeFootnote" : true,
"IncludeVarianceExplanation" : true
}
}
03-31-2023 10:49 AM
Thanks a lot for the reply @NicolasArgente ! At first I was going to omit them but then realized omitting them seems to set everything to 'True', which would show some unnecessary data for my purpose - hence I indeed needed to include them in the body.
As @JackLacava mentioned above, I had an issue with my custom variables in my cubeview which seemed to be hiding the data.
Thanks to you both !
10-13-2023 04:31 PM
Could someone please help with the BR code to parse the CustomSubstVarsAsCommaSeparatedPairs please. If I use an Extender then will that work. Any help is appreciated.
Thank You.