1 Reply Latest reply on Sep 20, 2018 2:03 PM by Patrick A Van Der Hyde

    Guidelines for creating custom OData Web APIs without Entity Framework

    Vijaya Krishna M M

      Hello!

       

      I am trying to create a custom OData APIs to serve Tableau using "Web API 2 OData v3 Controller with read/write actions" in Visual Studio 2017. I've created the controller with GetItem() and GetItems() methods (code is given in the end).

       

      I'm able to connect to this service from Tableau 10.5 but, when I click on the Sheet 1 which creates the extract, it throws the following error:

       

      Tableau failed to create and extract. The data source required an extract before you can start your analysis.

       

      Tableau logs shows the Bad OData Format. Make sure you are using a URL that points to a valid OData Source with the below log entries:

       

      {"ts":"2018-08-31T03:30:04.295","pid":15008,"tid":"1e4c","sev":"warn","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"msg","v":"SQLConnection::SetBaseDialect: No dialect found in registry for class 'odata', flavor '', and version ''"}

       

      {"ts":"2018-08-31T03:30:11.455","pid":15008,"tid":"2c28","sev":"warn","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"msg","v":"OData: Unable to determine the total data set size. This is not a criticial failure."}

       

      {"ts":"2018-08-31T03:30:12.251","pid":15008,"tid":"2c28","sev":"warn","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"msg","v":"throw DataSourceException (type=Unknown): [5] PQresultStatus: 7 ERROR:  cannot execute this statement in a read-only connection\n [SQL State: 25006] [Severity: ERROR] [Primary message: cannot execute this statement in a read-only connection]\r\n\r\n\r\n"}

       

      {"ts":"2018-08-31T03:30:12.336","pid":15008,"tid":"269c","sev":"error","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"msg","v":"    Bad OData Format. Make sure you are using a URL that points to a valid OData Source."}

       

      {"ts":"2018-08-31T03:30:12.336","pid":15008,"tid":"269c","sev":"error","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"msg","v":"    Unable to create extract"}

       

      {"ts":"2018-08-31T03:30:12.251","pid":15008,"tid":"2c28","sev":"warn","req":"-","sess":"-","site":"{2D900D3F-15B1-4595-A9EC-CAF7111996B0}","user":"-","k":"extract-task","v":{"message":"Error rolling back extract task: [5] PQresultStatus: 7 ERROR:  cannot execute this statement in a read-only connection\n [SQL State: 25006] [Severity: ERROR] [Primary message: cannot execute this statement in a read-only connection]\r\n\r\n\r\n"}}

       

      I wanted to know if is there any guidelines available for creating a custom OData Web APIs that is compatible with Tableau?

       

       

      Sample Code:

       

      public class OListItemsController : ODataController

          {

              #region Private Fields

       

       

              private static ODataValidationSettings _validationSettings = new ODataValidationSettings();

       

       

              #endregion Private Fields

       

       

              #region Public Methods

       

              // GET: odata/OListItems(5)

              [EnableQuery()]

              public IHttpActionResult GetOListItem([FromODataUri] int key, ODataQueryOptions<OListItem> queryOptions)

              {

                  // validate the query.

                  try

                  {

                      queryOptions.Validate(_validationSettings);

                  }

                  catch (ODataException ex)

                  {

                      return BadRequest("ErroeCode: 002. " + ex.Message);

                  }

       

                  var items = GetItems().Where(i => i.Id == key).AsQueryable();

       

       

                  if (items.Count() == 0)

                  {

                      return NotFound();

                  }

       

                  if (items.Count() == 0)

                  {

                      return StatusCode(HttpStatusCode.NotFound);

                  }

                  else

                  {

                      return Ok(items.SingleOrDefault());

                  }

              }

       

       

              // GET: odata/OListItems

              [EnableQuery()]

              public IQueryable<OListItem> GetOListItems(ODataQueryOptions<OListItem> queryOptions)

              {

                  // validate the query.

                  try

                  {

                      queryOptions.Validate(_validationSettings);

                  }

                  catch (ODataException ex)

                  {

                      throw new HttpRequestException("Bad request: " + ex.Message);

                  }

       

                  var items = GetItems().AsQueryable();

       

                  if (items.Count() == 0)

                  {

                      //return NotFound();

                      return null;

                  }

       

                  if (items.Count() == 0)

                  {

                      //return NotFound();

                      return null;

                  }

                  else

                  {

                      return items.AsQueryable();

                  }

              }

              // Other methods

              // ......

      }