April 21, 2021
The Data & Analytics Maturity Model: What is It and Where Does Your Team Stand?
Attention Power BI enthusiasts, what I am about to say is huge! AtScale now lets you get Live Query access to cloud data warehouses within a full, multi-dimensional model. This means you get live access to your cloud data with the same utility as connecting to SSAS.
Why is this such huge news? To date, there has never been a great, live query experience on Power BI for Snowflake, BigQuery, RedShift, Databricks and even Azure Synapse SQL. Historically, Power BI has only supported “DirectQuery” access for cloud data sources. “Live Query” provided by AtScale’s Power BI DAX connector will deliver extreme performance benefits for these applications. Coupled with the AtScale semantic layer, the combination of analytics-ready data with the ease of use of Power BI is a terrific combination.
In this blog, I’ll talk about why this matters to you and what makes this enhancement so important to users. You can also read our press release about this here.
It’s aLIVE!: A Better Alternative to DirectQuery
Today, with Power BI, you can connect to cloud data platforms by using Power BI’s DirectQuery mode. While DirectQuery enables cloud data access, the user experience can be pretty poor. While the raw queries may execute fast on the backend data platform, rendering within Power BI was slow due the reliance on DirectQuery. Because of these shortcomings, we found many customers would resort to using “Import” mode to load data directly into Power BI. In fact, Power BI defaults to “Import” mode for most data platforms, as you can see below.
Image 1: Power BI’s Snowflake “DirectQuery” Connector Dialog
Of course, importing data into Power BI is a poor workaround, since it creates scalability issues and results in stale, out of date data.
Leveraging DAX to Connect PowerBI to Cloud Data
AtScale’s Power BI DAX connector changes the game. Instead of connecting to cloud data platforms using the DirectQuery interface, AtScale leverages Power BI’s native DAX interface to “Connect Live” as you can see below.
Image 2: Power BI’s SSAS “Live” Connector Dialog Used to Connect to AtScale
Instead of importing data into Power BI for interactive performance, AtScale’s live connection provides up-to-date data at full fidelity, without pre-aggregation or data engineering work. The DirectQuery latency associated with loading metadata and rendering results disappears to deliver Power BI users the snappiest and smoothest experience possible.
The Power of a Semantic Layer for Power BI Users
Beyond raw performance and live data connectivity, AtScale + DAX delivers another benefit to Power BI users. Traditionally, Power BI users model their data inside of Power BI first before they can create a single visualization. Navigating cryptically named data tables and columns is time- consuming and beyond the modeling capabilities of many data analysts. It is not practical to expect business users to become data engineers to get their work done. This limits the productivity of teams and reduces the usability of Power BI as a platform.
AtScale’s semantic layer has the ability to easily build and maintain libraries of models that translate raw data structures into business-oriented, logical models. Instead of forcing end users to model and wrangle their data in Power BI, the AtScale Power BI DAX connector delivers AtScale data models automatically to Power BI users. AtScale dimensional models appear as Power BI tabular models, with no work for Power BI users. As you can see below, the AtScale data model automatically appears in Power BI’s model canvas.
Image 3: AtScale’s Data Model In Power BI’s Model Canvas
With a single, governed data model, all Power BI users will be speaking the same language and producing the same results using a single source of truth.
Who’s Who in the Zoo?: Delegation & Impersonation
I have a lot of empathy for database designers and security experts. They spend a ton of time securing their data platforms. And then, BI tools just ignore all their carefully crafted data access rules by using a proxy account to query the data.
For example, if the user “david_mariani” belongs to the finance group, he should only be able to view revenue tables and columns, but not employee social security numbers or employee data, right? This seems simple enough, and it makes sense to implement these rules in the data platform itself (i.e. Snowflake), not the BI tool. However, if a Power BI user is viewing a report with a Snowflake proxy user account called “powerbi_user,” those data access rules are effectively useless. This is another big issue with the “DirectQuery” connectors: there’s no way for report authors and report viewers to appear as themselves without hard coding their data platform credentials inside the Power BI workbook.
The AtScale Power BI DAX connector solves this problem because users authenticate in Power BI using NTLM (LDAP/Active Directory). AtScale will make sure that regardless of whether the user authored the report, or is just viewing a report authored by someone else, they will appear as themselves to the cloud data warehouse. This means that with AtScale’s Power BI DAX connector, Power BI reports will generate queries using the correct data platform accounts automatically, thereby respecting data access rules.
Related to this is the issue of resource management, DirectQuery connectors require that users embed data platform connection parameters inside the Power BI workbooks. For data platforms like Snowflake, that means associating cluster sizing configurations within a user’s Power BI workbook, as you can see below.
Image 4: Power BI’s Snowflake Connector Dialog with Virtual Warehouse Selection
This is problematic because an end user may choose an improperly sized virtual data warehouse configuration. Anyone accessing the reports in the affected Power BI workbook may unintentionally consume excessive resources and costs. With the AtScale Power BI DAX connector, these important data platform configurations are centralized and mapped accordingly, ensuring that data platform access is consistent, safe, and correctly managed.
In summary, with the AtScale Power BI DAX connector, users can now connect using their preferred LDAP or Active Directory (AD) service. When publishing reports and dashboards to the Power BI Service, AtScale’s query impersonation support will ensure that the report viewer appears as themselves. By supporting single sign on and impersonation, AtScale enhances and simplifies security for BI programs built on PowerBI and cloud data.
Best in Class Integration with Power BI
I love that Power BI’s clear strength is the power of its native, dimensional engine. Traditional DirectQuery connectors translate dimensional queries into SQL, creating an impedance mismatch, resulting in slow queries and limiting Power BI functionality. With AtScale’s Power BI DAX connector, that impedance mismatch disappears, delivering full Power BI functionality at fast speeds on all major cloud data platforms. By combining the analytical “power” of Power BI with AtScale’s dimensional semantic layer, users get consistent, “analytics ready” data at their fingertips.
I just covered a few of the important benefits and functions of AtScale’s new DAX connector for Power BI. We’re really excited to show you just how great the live Power BI experience can be on AtScale. Please check out the following resources to learn more or let us show you a live demonstration.
Want to see for yourself? Request a custom demo for your PowerBI users.
Additional PowerBI resources:
Power BI/Fabric Benchmarks