1

We are using the ServiceStack framework to build internal APIs.

Our API (web service) serves multiple front-end clients very well (iPhone app, web application).

Our business team would also like to query the API service using PowerPivot. Is there an easy way of using PowerPivot to query our internal API.

An example ResponseDto looks as follows:

public class GetTransactionResponse
{
    public List<TransactionView> Result { get; set; }
}
Mark
  • 2,175
  • 20
  • 23
  • do you have an example request available that PowerPivot creates? maybe a custom deserializer for a specific `Accept`-header will suffice (aka [RequestBinder](https://github.com/ServiceStack/ServiceStack/wiki/Serialization-deserialization)) ... –  Mar 11 '16 at 14:25
  • @AndreasNiedermair - not sure what you mean? I am hoping to query PowerPivot using our api restURL. e.g. http:// apiuri/transactions?format=Json or XML? But this does not seem to work. – Mark Mar 11 '16 at 14:29
  • I am not fully aware of how PowerPivot works internally, or which format it expects. Therefore I wanted to know which request- and response-format it uses or can use. –  Mar 11 '16 at 14:30
  • Indeed also don't know how PowerPivot works internally. Hence why partly I am asking this. It seems to accept ODataFeed. – Mark Mar 11 '16 at 14:33
  • ServiceStack does not ship with OData but AutoQuery though ... see http://stackoverflow.com/questions/9577938/odata-with-servicestack –  Mar 11 '16 at 14:34
  • @AndreasNiederMair - SS AutoQuery does not ship with OData. Note that mythz suggests in that answer that OData promotes poor practices & thus SS won't support this datatype. – Mark Mar 11 '16 at 14:40
  • 1
    I would install Power Query and use it to consume your API sort of like this article. Power Query is really the best way to get data into Power Pivot. Sorry I can't help more. http://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/ – GregGalloway Mar 15 '16 at 01:13

1 Answers1

3

Greg's answer is the best here. You should use Power Query to load the data from your API. You'll need to ensure you're using a compatible form of authentication (basic auth, keybased access) so that you can complete the auth handshake and then if your API is RESTful, PQ will do the rest.

Lukasz P.
  • 2,219
  • 12
  • 21