AtScale in Action: How to Make Power BI Perform on Snowflake

Many enterprises today choose the Microsoft stack because it fits seamlessly with the Windows OS and existing business applications. That’s why AtScale has partnered with Snowflake to streamline reporting and analytics with Power BI.

If you haven’t seen our previous Snowflake demo yet, be sure to check out AtScale in Action: Scaling Self-serve BI Programs on Snowflake with a Semantic Layer.

In this post, we’ll discuss how AtScale’s semantic layer can streamline data analytics with Snowflake and Power BI.

Key benefits of a semantic layer

Before we get into the demo, it’s important to understand the value that a semantic layer can bring to a modern data analytics architecture.

First, a semantic layer can eliminate the silos that exist around multiple business intelligence (BI) tools and coordinate them into a single point of design and maintenance. This includes tools that use SQL, MDX, DAX and other querying protocols.

Second, AtScale’s semantic layer accelerates BI query performance while radically simplifying the data engineering needed to support an analytics program. AtScale’s Autonomous Data Engineering capability dynamically optimizes the physical data structures within Snowflake to best match what users are doing with that data. The intelligent use of aggregates enables more simplified queries and accelerated performance.

Third, AtScale provides a multidimensional interface to Snowflake data that allows business users and data scientists to slice and dice data using measures, dimensions and hierarchies just like SQLServer Analysis Services (SSAS) but without building cubes.

Most importantly, AtScale provides these benefits without egressing the data out of Snowflake, using data virtualization to directly tie analytics to real-time data.

Getting started with a Microsoft stack

The Microsoft data stack that an enterprise may use can vary depending on the organization’s current level of data and analytics maturity. That said, in this demo we’ll look at an example Microsoft stack, which has the following relevant components:

• Active Directory: manage users or groups using an on-premise solution or Azure Active Directory.

• Power BI & Excel: use Windows Authentication and DAX/MDX analytics interfaces directly with Snowflake.

• AtScale Semantic Layer: model data virtually, apply user permissions, accelerate queries, and translate queries between BI tools and Snowflake.

• Snowflake: store enormous amounts of data and automatically query the right warehouse.

AtScale in Action

In this demo, we’ll start with a data model in AtScale built on TPC-DS, which is a dataset used for benchmarking in the data warehousing and BI industry. The virtual model in AtScale has a number of fact tables, dimensions, and other modeling concepts based on the massive raw dataset in Snowflake.

Live data in Power BI

When we switch to Power BI, we can see the semantic layer directly within the interface in real-time. Despite the dataset containing hundreds of billions of rows, end users can get near-instantaneous responses within Power BI. That’s because there’s a direct connection to Snowflake, allowing business users to drill down, slice and dice, and perform other analytical tasks in Power BI with live data.

Instantaneous query translation

Within the AtScale console, we can see the actual queries coming in from Power BI. Even though the Snowflake platform does not have an interface for DAX/MDX, AtScale can accept these queries from Power BI and translate them into optimized Snowflake SQL. These SQL queries can be immediately executed without having to move any data.

Accelerated query performance

Besides translating queries, however, AtScale creates acceleration structures or aggregates of the data to improve performance. In fact, our recent cloud data warehouse benchmark report using this same dataset on Snowflake found that queries were 61x faster with AtScale. The autonomous data engineering engine improves performance by optimizing queriest and simplifying the logic involved, such as table joins, calculations, and governance constraints.

Windows authentication

In addition, security is being applied to users based on their Windows authentication, which comes from the single sign-on (SSO) integration with Active Directory. This enables organizations to limit access to certain datasets in Snowflake using Azure Active Directory or another authentication solution.

Making Power BI Shine on Snowflake

As you can see, AtScale’s semantic layer can make Snowflake data more accessible in Power BI while also bringing enormous performance improvements to queries. This is possible through autonomous data engineering and data virtualization, which simplifies the logic involved in queries, translates queries between Power BI and Snowflake, and creates data models that are more understandable to business users.

Want to learn more? Watch our live demo where we use AtScale to seamlessly interact with Snowflake data in Power BI.