Difference between revisions of "Odata Overview"
From SmartWiki
| Line 1: | Line 1: | ||
| − | |||
==What is OData?== | ==What is OData?== | ||
| − | |||
OData is described in the following article: [https://en.wikipedia.org/wiki/Open_Data_Protocol Wikipedia] article. | OData is described in the following article: [https://en.wikipedia.org/wiki/Open_Data_Protocol Wikipedia] article. | ||
| − | OData provides clients with the ability to publish their data to their communities. | + | OData provides clients with the ability to publish their data to their communities. It's as if each clients has their own API for their own communities. For example, a client can expose their annual grant/funding information so tax payers can retrieve in real time data for further analysis. |
==How does it work== | ==How does it work== | ||
| − | + | SmartSimple provides OData V2, V3 and V4 services. | |
| − | SmartSimple provides OData V2, V3 and V4 services. | ||
These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET. | These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET. | ||
| − | ==SmartSimple Entities Exposed to OData Services== | + | ==SmartSimple Entities Exposed to OData Services== |
| − | Currently Reports are the only entities exposed through Odata. | + | Currently Reports are the only entities exposed through Odata. In the future other entities may be added. Reports were chosen for the following reasons: |
| − | # Easier to control security and data format such as fields and fieldname | + | # Easier to control security and data format such as fields and fieldname |
# Most clients wish to consume read only data | # Most clients wish to consume read only data | ||
| − | # Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships | + | # Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships |
| − | # Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine | + | # Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine |
# Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations | # Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations | ||
# Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability | # Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability | ||
==OData Services Endpoints== | ==OData Services Endpoints== | ||
| − | |||
Depending on the version of the OData required the endpoint will vary. | Depending on the version of the OData required the endpoint will vary. | ||
| Line 34: | Line 30: | ||
==Public access== | ==Public access== | ||
| − | |||
If you wish the data to be exposed publicly then the different URL is used. Note the underlying report must be Internet and OData enabled. | If you wish the data to be exposed publicly then the different URL is used. Note the underlying report must be Internet and OData enabled. | ||
| Line 44: | Line 39: | ||
==How to setup OData from within SmartSimple== | ==How to setup OData from within SmartSimple== | ||
| − | |||
For '''authenticated access''' you will need to create a new user with the Web Service Client Access attribute. | For '''authenticated access''' you will need to create a new user with the Web Service Client Access attribute. | ||
| Line 54: | Line 48: | ||
==Modifying a Report for Use with OData== | ==Modifying a Report for Use with OData== | ||
| − | |||
# Edit the Report in Neo Report Builder | # Edit the Report in Neo Report Builder | ||
# Enable the OData checkbox on the Advanced tab | # Enable the OData checkbox on the Advanced tab | ||
# Enable the Internet Enabled checkbox '''if you wish the data to be public | # Enable the Internet Enabled checkbox '''if you wish the data to be public | ||
''' | ''' | ||
| + | |||
==Syntax== | ==Syntax== | ||
| + | 1. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only | ||
| − | |||
| − | |||
<pre>http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$search=steve</pre> | <pre>http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$search=steve</pre> | ||
| + | 2. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol) | ||
| − | + | <pre>http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$filter=first_name +eq+steve </pre> | |
| − | |||
| − | <pre>http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$filter=first_name +eq+steve </pre> | ||
| + | 3. Ordering: the URL below orders by first name | ||
| − | + | <pre>http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name+asc | |
| − | <pre> | ||
| − | http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name+asc | ||
or | or | ||
http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name | http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name | ||
</pre> | </pre> | ||
| − | |||
4. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return) | 4. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return) | ||
<pre> ?$top=2&$skip=3</pre> | <pre> ?$top=2&$skip=3</pre> | ||
| + | |||
| + | ==Troubleshooting== | ||
| + | * Column Headings '''must not''' contain special characters such as question marks or brackets. Some characters, such as hyphens, are supported. | ||
| + | * Some applications, such as older versions of MS Excel / .NET , may not support the appropriate level of TLS Encryption (i.e. https) required. | ||
=See Also= | =See Also= | ||
* [[Reports]] | * [[Reports]] | ||
Revision as of 13:25, 19 January 2018
Contents
What is OData?
OData is described in the following article: Wikipedia article.
OData provides clients with the ability to publish their data to their communities. It's as if each clients has their own API for their own communities. For example, a client can expose their annual grant/funding information so tax payers can retrieve in real time data for further analysis.
How does it work
SmartSimple provides OData V2, V3 and V4 services.
These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET.
SmartSimple Entities Exposed to OData Services
Currently Reports are the only entities exposed through Odata. In the future other entities may be added. Reports were chosen for the following reasons:
- Easier to control security and data format such as fields and fieldname
- Most clients wish to consume read only data
- Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships
- Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine
- Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations
- Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability
OData Services Endpoints
Depending on the version of the OData required the endpoint will vary.
Version 4: https: //mysmartsimpleurl/OData/V4/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/V3/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/V2/424623/Service.svc/
Public access
If you wish the data to be exposed publicly then the different URL is used. Note the underlying report must be Internet and OData enabled.
Version 4: https: //mysmartsimpleurl/OData/Pub/V4/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/Pub/V3/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/'Pub/'V2/424623/Service.svc/
How to setup OData from within SmartSimple
For authenticated access you will need to create a new user with the Web Service Client Access attribute.
- Create the new user and set access to Web Service Client Access.
- Set a password for this user.
For public access there is no requirement to create a user.
Modifying a Report for Use with OData
- Edit the Report in Neo Report Builder
- Enable the OData checkbox on the Advanced tab
- Enable the Internet Enabled checkbox if you wish the data to be public
Syntax
1. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only
http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$search=steve
2. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol)
http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$filter=first_name +eq+steve
3. Ordering: the URL below orders by first name
http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name+asc or http://localhost/OData/V4/pub/113/Service.svc/report_Neo_records?$orderby=first_name
4. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return)
?$top=2&$skip=3
Troubleshooting
- Column Headings must not contain special characters such as question marks or brackets. Some characters, such as hyphens, are supported.
- Some applications, such as older versions of MS Excel / .NET , may not support the appropriate level of TLS Encryption (i.e. https) required.
