Excel and Snowflake have had a rocky romance. Yes, people are finding ways to connect Excel and access Snowflake data, but it hasn’t been pretty and there isn’t native support for Pivot Tables from Snowflake. This is why we’ve created Kyligence Pivot to Snowflake; a cloud solution that solves this problem.

The Snowflake cloud data warehouse has seen tremendous growth in the past 5 years thanks to its straightforward user experience, transparent pricing model, and native cloud architecture. Users can literally create a data warehouse and start analyzing data in a couple hours, without the overhead of server provisioning, storage planning, network setup, etc. usually required for setting up a traditional data warehouse.

While many BI products can read data from Snowflake through purpose-built connectors, one of the most popular BI tools still has no easy way to work with Snowflake, and that is Excel Pivot Tables.

Pivot Tables

Pivot tables are probably the most powerful analytical feature in Excel. With pivot tables, users can gain valuable insights by summarizing detailed records across multiple worksheets. The following example shows a pivot table on the right and its corresponding detailed data on the left.

Figure 1: Transactions and Pivot Table

(Microsoft Support Document)

Pivot Tables enable analysts to conduct multi-dimensional analysis without writing complex SQL statements. The data in the pivot table can also be used to draw Pivot Charts and build dashboards.

Unfortunately, it has not been possible for users to run Pivot Tables against Snowflake data directly. A common workaround is to download a subset of data from Snowflake to local laptops and analyze them as a bunch of CSV files. But ideally, we’d like to use Pivot Tables on live data in Snowflake to get the most accurate results possible.

Kyligence Pivot to Snowflake: A Solution for Pivot Table on Snowflake

Kyligence Pivot to Snowflake is a solution for Snowflake users. It leverages Kyligence Cloud MDX, Unified Semantic Layer, and Query Pushdown capabilities to build Pivot Tables on top of Snowflake data warehouses.

Figure 2: Kyligence Pivot to Snowflake – A Solution for Pivot Tables on Snowflake

The above diagram shows these key components that comprise the Kyligence solution:

• Unified Semantic Layer: This is where the data model is defined based on the underlying table structure. Advanced logic, such as hierarchies, calculations, etc. are also defined here. The data model and business logic are exposed to the BI tools including Excel Pivot Tables.
• MDX Engine: The MDX engine parses MDX statements issued by the Pivot Tables and converts them into SQL queries, which is the language spoken by Snowflake.
• Access Control: Access to information at table, row, column, and cell level is centrally defined.
• Enterprise Security: Kyligence can be integrated with common cloud security services.
• Query Pushdown: Instead of working with a data extract, parsed SQL queries are sent to Snowflake directly. This is basically the equivalent of the ‘Direct Query’ mode in many BI tools.

Benefits of the Kyligence Solution

Users of Kyligence Pivot to Snowflake can expect the following benefits:

• Quick time-to-insight: This solution can be easily deployed on your preferred cloud platform, in your region of choice. A data model can be defined with several mouse clicks once we connect to the Snowflake tables.
• Maximize your Snowflake investments: Data stays in Snowflake. Kyligence collects metadata information from Snowflake without moving or loading the data.
• Business user friendly: Pivot Table users are working with business logics directly. They don’t need to know the technical details such as tables, columns, and scripts. 
• Reduced learning curve: The Kyligence solution support SSAS syntax, which is very familiar for many Excel users.
• Future-proof: With more data stored in Snowflake and more users querying the warehouse, customers can easily turn on the query acceleration capabilities to improve query performance.

Kyligence Pivot to Snowflake is available now on AWS and Azure. I encourage you to check out the webinar and this quick demo video or give us a call to see a live demo.

Excel users have been more or less locked out of using data in Snowflake until now. Isn’t it about time that they lived happily ever after?

TEST DRIVE with $500 worth of free usage

Resources

Webinar: Excel, Data Discovery, and Snowflake: Unhappy Together

Webinar: Snowflake: The Good, the Bad, and the Ugly

Webinar: Architecting Snowflake for High Concurrency and High Performance

Webinar: Kyligence Pivot to Snowflake - A Solution for Excel Pivot Tables on Snowflake

Demo: Kyligence Pivot to Snowflake Demo