Q1. How comparable is OData to Power BI or Tableau reporting?
A: Tableau and Power BI are Business Intelligence Analytics (BIA) tools. They do much more than aggregate and report on data like the OData.
In general, many consider Power BI to be more user friendly with a minimal learning curve. It provides pre-build dashboards. When aggregating very large datasets, some people have noticed some performance issues. The root cause analysis of these performance issues are beyond our scope to discuss here.
But, industry thoughts are that Tableau handles large data sets better than Power BI. Tableau requires more time with training because it offers an extensive amount of data visualization.
OData is a REST-based protocol for querying and updating data. It is somewhat analogous to the open database connectivity approaches like the ODBC or JDBC. It is built on HTTP, XML, and JSON. Think of OData more like a 'front-end to SQL interface" to access the data and report on the data often in a tabular format.
Q2. Can I combine OData queries with SSRS?
A: Microsoft SQL Server Reporting Services (SSRS) doesn't directly work with OData. If you have access to the data through SSRS, you may not need OData.But, if you are trying to connect Spira OData interface with SSRS, at this point this functionality is NOT supported.
Q3. What are the best practices when working with OData?
OData is a user-interface to build your SQL query in an iterative fashion. So, don't write complex interactions right away.
Avoid bringing all the columns in a table. In other words, please refrain from the "Select * from Table" mindset especially on large tables
To my knowledge, there is no "Select top X from table" approach in OData from its native interfaces. So, when building large queries especially with joins, be careful
Know that it is not a BIA tool. So, when building complex queries, you are building reports rather than dashboard of charts.