Need documentation for *.analysis.windows.net/public/reports/querydata

user_3071981 0
Viewed 106

I am reverse engineering an app that sends queries to SOMESERVERNAME.analysis.windows.net/public/reports/querydata via an HTTP POST of an JSON-structured query.

Some initial lines of a sample query are at the end of this message.

I can't find any documentation on this anywhere. I don't know if this is some secret API or what. I ultimately would like to just ignore the aggregations altogether and just dump the raw data, which seems to sit in some flat-file type container on the back-end, but without some API documentation I'm stuck with just re-running the super basic handful of queries I've been able to intercept.

Note: this app is an embedded analytics page created with PowerBI, but the only REST API I can find for PowerBI has nothing to do with querying, but just basic object management.

Thanks!

{                                                                                                                                                                                                                                                           
"version": "1.0.0",                                                                                                                                                                                                                                       
"queries": [                                                                                                                                                                                                                                                
{                                                                                                                                                                                                                                                           
"Query": {                                                                                                                                                                                                                                                  
"Commands": [                                                                                                                                                                                                                                               
{                                                                                                                                                                                                                                                           
"SemanticQueryDataShapeCommand": {                                                                                                                                                                                                                          
"Query": {                                                                                                                                                                                                                                                  
"Version": 2,                                                                                                                                                                                                                                             
"From": [                                                                                                                                                                                                                                                   
{                                                                                                                                                                                                                                                           
"Name": "s",                                                                                                                                                                                                                                              
"Entity": "Sheet1"                                                                                                                                                                                                                                      
}                                                                                                                                                                                                                                                       
],                                                                                                                                                                                                                                                        
"Select": [                                                                                                                                                                                                                                                 
{                                                                                                                                                                                                                                                                       
"Aggregation": {                                                                                                                                                                                                                                            
"Expression": {                                                                                                                                                                                                                                             
"Column": {                                                                                                                                                                                                                                                 
"Expression": {                                                                                                                                                                                                                                             
"SourceRef": {                                                                                                                                                                                                                                              
"Source": "s"                                                                                                                                                                                                                                           
}                                                                                                                                                                                                                                                       
},                                                                                                                                                                                                                                                        
"Property": "Total"                                                                                                                                                                                                                                     
}                                                                                                                                                                                                                                                       
},                                                                                                                                                                                                                                                        
"Function": 0                                                                                                                                                                                                                                           
},                                                                                                                                                                                                                                                        
"Name": "Sum(Sheet1.Total)"                                                                                                                                                                                                                             
}                                                                                                                                                                                                                                                       
],                                                                                                                                                                                                                                                        
"Where": [                                                                                                                                                                                                                                                  
{                                                                                                                                                                                                                                                           
"Condition": {                                                                                                                                                                                                                                              
"In": {                                                                                                                                                                                                                                                     
"Expressions": [                                                                                                                                                                                                                                            
{                                                                                                                                                                                                                                                           
"Column": {                                                                                                                                                                                                                                                 
"Expression": {                                                                                                                                                                                                                                             
"SourceRef": {                                                                                                                                                                                                                                              
"Source": "s"                                                                                                                                                                                                                                           
}                                                                                                                                                                                                                                                       
},                                                                                                                                                                                                                                                        
"Property": "Year"                                                                                                                                                                                                                                      
}                                                                                                                                                                                                                                                       
}                                                                                                                                                                                                                                                       
],                                                                                                                                                                                                                                                        
"Values": [                                                                                                                                                                                                                                                 
[                                                                                                                                                                                                                                                           
{                                                                                                                                                                                                                                                           
"Literal": {                                                                                                                                                                                                                                                
"Value": "'2018'"                                                                                                                                                                                                                                       
 }                                                                                                                                                                                                                                                       
 }                                                                                                                                                                                                                                                       
 ]                                                                                                                                                                                                                                                       
 ]                                                                                                                                                                                                                                                       
 }                                                                                                                                                                                                                                                       
 }                                                                                                                                                                                                                                                       
 },                                       

............
powerbi rest api json
2 Answers

I have built a client that scrapes data off a specific Power BI report using the same API, but probably you'll be able to adapt it to your use case. Maybe we can even abstract the code into a more generalized Power BI client!

Having tinkered with the API for two days, I realised that there are many ways the data can be formatted:

  • "nested"/multidimensional data can be unflattened, flattened by 1 degree, etc.
  • a primary "table" of a result dataset (in data.PH) can reference others (in data.SH)

The basics are as follows:

  • A dataset is structured like a multidimensional table, with cells containing values.
  • In a set of cells, the first always has a field S that contains the schema of its and all subsequent cells.
  • The schema maps a field of each cell's object with a selection from your query, e.g. the G0 field with the queried column age.

My client seems to work only with a specific type of query (SemanticQueryDataShapeCommand), a specific nr of dimensions and a specific column marked as primary (via Binding.Primary). But maybe that helps! https://github.com/derhuerst/fetch-bvg-occupancy/blob/1ebb864b1ff7130f9d2f0ab031c6d78bcabdd633/lib/parse-dataset.js

user_10721290

The only documented way to use this API is through the ADOMD.NET or OleDb provider.

If you want to send a DAX/MDX query and retrieve data programmatically, there's a sample of how to front-end the service with a simple REST API here.

user_72977000
Related