Instead of exporting data manually to Excel, you can add your DataHawk-powered database as a data source in your Excel file, allowing you to easily get the up-to-date data you need automatically.

Set up

Connecting Datasets

Estimated configuration time of less than <5 minutes.

1. On DataHawk, create a Snowflake destination
2. Download and install ODBC driver for Snowflake - Win64 or Win32 or MacOS latest driver
3. Search for ODBC on your computer to open ODBC Data Source Administrator
4. In the User DSN tab, click Add > SnowflakeDSIIDriver > Finish
5. Enter the credentials you can find in DataHawk for your Snowflake destination in the relevant areas
   1. Give your Data Source any name you want, such as "DataHawk"
   2. Enter the User, Password, Server, Database, and Warehouse details you got from DataHawk
6. On Excel, from the Data tab, click Get Data > From Other Sources > From ODBC then select your data source
7. Re-enter your database User Name and Password
8. Select the tables you want to load

In case you want to load only partial data - such as a specific time frame, or specific columns - instead of the entire table, or merge data from multiple tables, you can transform it using Power BI Query Editor on Excel, or alternatively write a SQL query, notably as you get familiar with our datasets.

Scheduling Updates

To ensure your file contains the most recent data, you can schedule a refresh.

We recommend setting up a refresh when opening the file, or every 999 minutes if you'll keep your file open for days. There's no need to select a faster frequency as we update data once a day, and the max limit you can set on Excel is 999 minutes which is about every 16 hours.

1. Click Data in the navigation menu
2. Click Queries & Connections to open its tab if it wasn't already open
3. Right-click on a query
4. Click Properties
5. Tick Refresh data when opening the file
6. Optionally, tick Refresh every 999 minutes

Examples

Getting Sales Rank

Sales Rank or Best Sellers Rank data for products that you're tracking on your DataHawk workspace is available in the PRODUCT_SALES_RANK table in the PRODUCT schema.

This table gives you daily BSR data for every single category where a product that you're tracking has ranked.

You can either load the entire table or ideally transform it to pull only specific columns you need from it, while optionally applying filters such as on the time range. You can also alternatively write a SQL query to pull what you need or merge multiple tables.

Transforming Data

Instead of loading an entire table that's potentially containing thousands of rows and dozens of columns that you may not need, then using Excel formulas such as VLOOKUPS, HLOOKUPS, INDEX & MATCH, and more or Pivot Tables to extract what you need from it, and potentially running into Excel's rows limits, a better solution is to transform data first using Excel's Power Query Editor.

To open Excel's Power Query Editor

• When importing a table, you can click Transform Data before loading it
• Alternatively, for an already-loaded table
  1. Click Data in the navigation menu
  2. Click Queries & Connections to open its tab if it wasn't already open
  3. Double-click on a query

There are multiple transformations you can do on Power Query Editor such as

• Remove columns
• Insert columns
• Create filters
• Merge queries or tables
• Edit the query using SQL

As you finish your transformations, click Close & Load.

Limitations

Excel is not built to handle large volumes of data. While you can set up an automated import of data from your DataHawk-powered Snowflake database to Excel, keep in mind Excel worksheets have a limit of 1,048,576 rows and 16,384 columns, which you could easily hit.

To circumvent that, you will want to use sufficient filters to stay under the row limit. Else, you can also use a robust BI solution like Microsoft's Power BI.