Skip to main content

Expose Excel Data as OData feed using Teiid

For that matter you can expose any data source that is supported by Teiid through its translators as OData service. You can convert
  • Relational databases
  • XML, JSON, CSV files
  • Web Services 
  • Sales Force
  • Excel documents
sources, or write your on top of your specific source.

Teiid added support for exposing its virtual database (VDB) as OData service without any additional work. Create a virtual database with your required sources and create any additional views and deploy in the the JBoss EAP 6.1 server, that has Teiid 8.3 or greater installed.  See https://docs.jboss.org/author/display/teiid84final/OData+Support for more information.

So, lets build a simple example of exposing the Excel Sheet. Teiid accesses Excel sheets via JDBC-ODBC bridge, and Windows OS provides a ODBC driver for Excel documents. 

Edit the standalone-teiid.xml, and create Connection Factory
 

    
        jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=c:\ODBC\ExcelData.xls
        odbc
        TRANSACTION_NONE
        
            false
            false
            FailingConnectionOnly
        
        
            {user}
            {password}
        
         
    
        
         sun.jdbc.odbc.JdbcOdbcDriver
     
     



Now create VDB, using the excel as the source
 


    
    
        
    



save the above as "odbc-vdb.xml" and deploy into Teiid Server. See https://docs.jboss.org/author/display/TEIID/Deploying+VDBs for deployment instructions.  Make sure your VDB deployed in "ACTIVE" state.

Now, you can access your Excel data using OData API. Either using the your Web Browser or favorite OData client, go to 

 
     http://:/odata/odbc/excel.Sheet1
For more complete information about OData API see https://docs.jboss.org/author/display/TEIID/OData+Support

Thanks
Ramesh..

Comments

  1. I tried to do this with a test Excel file but when I started ran standalone.sh, I got this error:
    Caused by: org.xml.sax.SAXParseException: cvc-complex-type.2.1: Element 'property' must have no character or element information item [children], because the type's content type is empty.

    java version "1.6.0_37"
    JBoss EAP 6.1
    Teiid Server 8.4

    ReplyDelete
  2. Try querying your Excel based vdb using any SQL client such as SquirreL and make sure it is returning results before you use the OData interface, that should give you correct error.

    ReplyDelete

Post a Comment

Popular posts from this blog

Teiid Runtimes Explained

If you have been following Teiid lately we have been going through a whole lot of renovations. Yes, renovations or reorganization or refactoring or whatever you want to call it. Basically, we are making Teiid more modular with fewer dependencies that can be used by however your use case dictates rather than use it as one monolith application deployed into WildFly JEE Application Server. There is nothing wrong in using Teiid as server model, but with the proliferation of container-based workloads and cloud-based architectures, the previous server-based model does not work or simply won't scale. So, we needed to think of alternatives, thus Teiid team introduced a couple different versions modular Teiid what we are calling as "Teiid Runtimes".

Note that in these modular Teiid runtimes, not all the features you were used to using in Teiid Server model may not be there but you will have extensions to add in those that are most appropriate for your domain. If you are looking …

Teiid Platform Sizing Guidelines and Limitations

Users/customers always ask us about the sizing of their Data Virtaulization infrastructure based on Teiid or the JDV product from Redhat. Typically this is very involved question and not a very easy one answer in plain terms. This is due to fact that it involves taking into consideration questions like:
What kind of sources that user is working with? Relational, file, CRM, NoSQL etc.How many sources they are trying to integrate? 10, 20, 100?What are the volumes of data they are working with? 10K, 100K, 1M+?What are the query latency times from the sources? How you are using Teiid to implement the data integration/virtualization solution. What kind of queries that user is executing? Even small federated results may take a lot of server side processing - especially if the plan needs tweaking.Is materializing being used?Is query written in optimal way?and so on..Each and every one of the question affects the performance profoundly, and if you got mixture of those then it become that much…