Solved

Capabilities of CData excel adapter with BIgQuery

  • 29 November 2023
  • 1 reply
  • 55 views

Badge

Our users are using Oracle smartview for financial reporting and are looking for an excel based tool to have similar capabilities in BigQuery. I wanted to know if CData plugin for excel can do the below:

  1. Slice and dice data based on BigQuery data models
  2. Consume hierarchies maintained in bigquery. For example a legal entity hierarchy that would be maintained centrally in BQ and consumed in financial reports
  3. Schedule reports
  4. Do cell based formulas. So for example, I want to create a forumla that goes into a cube and returns net earnings for the year input by the user in one of the cells.
icon

Best answer by Ethem Q 6 December 2023, 18:40

View original

1 reply

Userlevel 4
Badge

Hi @TechWizard2023 

If you are looking for an Excel based tool then we have two solutions you might want to explore:

 

  • Excel add-in for BigQuery - An excel add-in that is plugged in Excel and allows you to make a connection with your BigQuery instance and have your data pulled and organized in Excel in relational fashion. Find more about this here: https://www.cdata.com/drivers/bigquery/excel/ 

 

  • CData Connect Cloud Add-in in Excel - Connect Cloud allows you to create an online hosted hub of connections to your desired data sources, including BigQuery. It's a cloud-native data platform that offers instant, always-on access to over 100 data sources and applications directly within various Client tools like Excel Desktop and Excel Online. Find more about this here: https://cloud.cdata.com/auth/sign-up-initial 

Regarding your questions, I will do my best to answer those here however I would recommend you evaluate the options I presented above by getting a free trial in order to try them out and see if they fit your use case.

1. If what you want is to fetch data from BigQuery and manipulate them then our BigQuery drivers can certainly help you with that. Our BigQuery connectors model the Bigquery data into relational fashion by exposing them organized in tables and views and stored procedures where you can work on those data with simple SQL queries. You can read more about the driver's data model here:https://cdn.cdata.com/help/DBJ/xls/pg_datamodel.htm 

2. It would really help if you could clarify what you mean with 'hierarchies maintained in BQ'? Are you referring to BigQuery Projects or Datasets? if not please do provide more context on this so we can have a better understanding.

3. You might need to elaborate more on this point as well. If what you are asking is if the driver offers an option to schedule reports then the answer is no. If you want to use some kind of BI tool for that then our Connect Cloud would allow you to connect to othe BI clients as well. 

4. While we do offer our own CData Excel functions for our Add-ins https://cdn.cdata.com/help/DBJ/xls/pg_lsfunctions.htm  I think from your description you might be asking for some Excel specific functions unrelated to our driver. From what I gather, we do not offer any custom CData formulas that would help you accomplish that. However I am positive that you can work with the resultset the driver returns by making use of any Excel formulas you want as long as they are supported by Excel.

 

If the above are unclear for you or you would like to know more about our solutions feel free to reach out to the support team at support@cdata.com. You can also submit a support ticket here

Reply