Dynamic + Configurable SQL Reports in PowerBI and Databricks SQL

Dyanmic + Configurable SQL Reports on Power BI and DBSQL

Authors
Yatish Anand, Senior Solutions Architect @ Databricks
Cody Austin Davis

Introduction

Oftentimes in BI reporting applications, companies run into use cases where they want to create a report where the results (columns returned, logic) are dynamic based on some filter or configuration. This is common especially in External Data Products where a report for a specific customer logically differs by customer, especially when migrating from paginated reporting systems like SSRS or other custom reporting systems. Whether implementing custom contract logic, showing personalized persona-specific views of data, or just wanting to enable different features of a report by customers, the need to do dynamic report generation pops up across industries. Currently in Power BI, you could choose one of the following strategies to implement Data Products that are configurable and dynamic in nature:

1. Create many static reports based off each end user/customer spec: This method is the most simple to implement at first, but quickly turns into managing the implementation, creation, and governance of 100s or thousands of reports for end users.
2. Use Dynamic Measures + Calculated Columns: This is the most common method. You could implement dynamic reporting logic via dynamic measures and calculated columns to house all your configurable logic (here is a great blog on dynamic measures written by Enridge — article link) inside Power BI itself; however, this adds quite a bit of complexity and computational overhead to Power BI, as well as makes for a bunch of hidden logic in your Power BI data model itself. This is the best thing to do for simple and common calculations like being able to chart different metrics all with a single graph instead of creating multiple charts for each metric, but when there is a lot of logic that is configurable and many logical rules, the performance cost and governance story falls apart, as the end user has access to ALL logic in the report. You can’t just keep adding configurations without severe bloat to your data model and performance hits.
3. Create a Dynamic SQL Report on Databricks SQL: You can actually store custom logic in Databricks SQL, Secure it in Unity Catalog with the Permissions Table design pattern, map it to parameters, and dynamically pull the logic you need for a particular column and measure via Direct Query in Power BI on Databricks SQL. This is overkill for simple use cases where you want dynamic measures in one chart (like the example blog above) when you only have a few fixed set of metrics, but it is immensely powerful when you want to serve and run a data product at-scale with configurable logic by customer or end user. Not only do you not need to pull in more data than you need (performance of data model is very lean), but you can explicitly and scalably secure all the configurable logic via Unity Catalog on Databricks SQL to ensure that all your end users can only access the data that they have access to in this dynamic reporting scenario. This is the design pattern we will cover in the blog today.

Overall, when you need highly configurable reports that are dynamic and scalable, the Dynamic SQL design pattern on Power BI and Databricks gets you the following benefits:

1. Highly scalable — Your data model does not need to endlessly grow every time you have a new configuration. This makes the scalability of the report itself, as well as the management of the report nearly infinite. Your data model stays lean and so does your operational overhead.
2. Secure & Easy To Govern — With this pattern, you govern access to the data, the rules themselves, as well as the users and groups all in one place — Unity Catalog on Databricks. This ensures that users have access to only the data they need, and you get end to end lineage.
3. All Logic Is Trackable & Easily Extendable — With this pattern, you can easily add features, logic, and new customers by simply inserting a new rule and providing an access record in the permission table. This is secure and easy, and scales to any number of rules and users!

Prerequisites

The blog assumes that you already have the following :

1. A running Databricks SQL Warehouse
2. Power BI Desktop installed

Lets dive into an example by implementing the following architecture for our Dynamic Reporting Engine:

Dynamic SQL Design Pattern with Permission Tables

This will create the following simple reports that dynamically executes SQL logic based on selected rules:

Dynamic SQL Result Report

Let’s get started:

As a part of this blog we will act as a Data Analyst who has been tasked with creating dynamic Power BI reports to minimize the report management. These reports will have the same dataset but report logic will be different. We will start with a very simple example of creating configurable reports on top of the already-installed tpch dataset. We will create and manage SQL report rules for each country in our customer configuration. Lets implement a few simple dynamic rules: For country“Brazil”, we want to find the maximum account balance. For “Kenya” we need to find the minimum account balance. Lastly, for “France”, we will take the average. This is a small example of implementing custom configurable logic for different users and customers, but can be extended to incredibly powerful data products.

In our above architecture, we will implement the permissions tables and create the workflow of our dynamic query in Power BI. Lets walk through each step:

1. Backend — Understanding the dataset
2. Backend — Create the Dynamic rules table
3. Backend — Creating the permission table
4. Backend — Creating the dynamic VIEW
5. Power Query — Setting up our Power BI Reports to Query the View dynamically to get the logic in Power Query
6. Visual Layer — Serving the dynamic result in Power BI

Step 1 — Understanding the Dataset

Below are the details of our two tables we need for our example. To make it easy for anyone to follow along we will be using the “Samples” catalog and “TPCH” schema . In an actual production scenario this could be any table in Unity Catalog.

1. Customer table — stores the customer demographic information like the address and also their account balance . A sample dataset is shown below :

2. Nation Table — A dimension table which contains the name of the tables and the corresponding nation key id which is used for joining with the customer table.

Lets create a separate database to build our workflow with the following code:

-- Create Catalog / Schema Env
CREATE SCHEMA IF NOT EXISTS main.dynamic_sql_demo;
USE CATALOG main;
USE SCHEMA dynamic_sql_demo;


CREATE OR REPLACE TABLE customer
AS SELECT * FROM samples.tpch.customer;


CREATE OR REPLACE TABLE nation
AS SELECT * FROM samples.tpch.nation;


CREATE OR REPLACE TABLE orders
AS SELECT * FROM samples.tpch.orders;

Step 2 — Creation of a Dynamic Rules Table with the Configurable Report Logic

In this step we will create a table (“dynamic_sql_rules”) that will hold the configurable logic for our report (example rules defined above).

To keep it focused, we will create a table with two columns : Nation and SQL . Nation will have the name of the nation and SQL will have the corresponding SQL logic for each Nation. We can create the first few simple rules with the following logic:

-- Create Dynamic SQL Rules Table
CREATE OR REPLACE TABLE main.dynamic_sql_demo.dynamic_sql_rules
AS
(
   -- Rule 1 - Any SQL Logic for Brazil
   SELECT 'BRAZIL' AS nation,
   "SELECT
       n.n_name,
       MAX(c.c_acctbal) AS acctbal
       FROM main.dynamic_sql_demo.customer c
       INNER JOIN main.dynamic_sql_demo.nation n ON n.n_nationkey = c.c_nationkey
       WHERE n.n_name = 'BRAZIL'
       GROUP BY n.n_name" AS sql
   -- Rule 2 - Any SQL logic for Kenya
   UNION ALL
       SELECT 'KENYA' AS nation,
   "SELECT
       n.n_name,
       MAX(c.c_acctbal) AS acctbal
       FROM main.dynamic_sql_demo.customer c
       INNER JOIN main.dynamic_sql_demo.nation n ON n.n_nationkey = c.c_nationkey
       WHERE n.n_name = 'KENYA'
       GROUP BY n.n_name" AS sql


   -- Rule 2 - Any SQL logic for Kenya
   UNION ALL
       SELECT 'FRANCE' AS nation,
   "SELECT
       n.n_name,
       AVG(c.c_acctbal) AS acctbal
       FROM main.dynamic_sql_demo.customer c
       INNER JOIN main.dynamic_sql_demo.nation n ON n.n_nationkey = c.c_nationkey
       WHERE n.n_name = 'FRANCE'
       GROUP BY n.n_name" AS sql
);

Step 3 — Creating the Permission Table for Secure Data Access Governance

In a real-world environment, it is not enough to create a rules table and embed this into the report, especially when dynamic SQL rules are involved. To better secure this model, we can use the permission table design pattern described in a previous SME blog here. Creating a permission table will automatically check to make sure the user querying a specific rule filter has permissions to the underlying rule and data. It is also good to explicitly track access to rules as a configuration instead of silently embedding rules into Power BI measures that are hard to monitor and govern. We can create this permission table like so:

-- Step 3 - Create the Permission Table


CREATE OR REPLACE TABLE main.dynamic_sql_demo.permission_table_mapping
AS
(
   -- Which users/groups have access to BRAZIL SQL logic
   SELECT
   NULL AS user_name,
   'data_analyts' AS group_name,
   'BRAZIL' AS nation_access_filter

   UNION ALL

   SELECT
   'cody.davis@databricks.com' AS user_name,
   NULL AS group_name,
   'KENYA' AS nation_access_filter


   UNION ALL
   SELECT
   'cody.davis@databricks.com' AS user_name,
   NULL AS group_name,
   'FRANCE' AS nation_access_filter
);

This allows us to add rules for users and or groups in a single permission table. So we explicitly know which logic users have access to.

Step 4 — Creating the final dynamic SQL rules table

Once the table is created, all we have to do now is link the permission table up to our dynamic rules table with a dynamic View that utilizes Row-level access control functions to check for permissions to a specific rule:

-- Step 4 - Create the final view that allows the users access to the underlying dynamic SQL rules
CREATE OR REPLACE VIEW main.dynamic_sql_demo.dynamic_rules_permission_table_view
AS
SELECT
nation,
sql
FROM main.dynamic_sql_demo.dynamic_sql_rules r
WHERE EXISTS (SELECT nation_access_filter
           FROM main.dynamic_sql_demo.permission_table_mapping ptf
           WHERE
           r.nation = ptf.nation_access_filter
           AND (is_account_group_member(ptf.group_name) OR user_name = current_user())
           )
;

Lets test and see if it works! We have 3 rules, 2 giving my user name access to the “Kenya” rule and “France” rule, and the other giving access to a “data_analysts” group for the “Brazil” rule. Currently, my user name is the only way I have access to a rule, as I am not currently in a group called “data analysts”. If our permission table is working correctly, we should only see the “Kenya” and “France” rule when we select from the table:

We have our backend set up and secured! Next we will link this into our Power BI report.

Step 5 — Creating Power BI Report Model in Power Query

Step 5.a. Create Parameter in Power BI:

Creating a Parameter in Power BI is a way to easily store and manage value that can be reused again . This link has more details on how to create parameters in Power BI . For our requirement we will create a parameter called “Nation_Name”. This parameter will be used to pass to an M query to filter data based on Nation name and apply the selected report logic. We can either choose to create a static list, or we can make the parameter dynamic based on our permission table. When we make the parameter dynamic from a query list, the parameter will automatically show the user the tailored list of available parameter rules they are allowed to use. We first create a distinct list of selectable filters for the user by creating a new query (import mode) that selects the distinct filters from our dynamic table (If you do not know how to connect to a Databricks SQL Warehouse, check out this link to do so):

Next, we can convert this to a list to be the input list for our parameter:

convert to list

Be sure to click “Remove duplicates” for better performance and reliability.

We have our query that provides the list of filter rules the user has access to, now lets create our parameter by clicking the “Manage Parameters” button and click “New Parameter”:

parameter from query

We make the “suggested value” be from our list query we created above (we renamed that query to “Distinct Nation List” so it is more clear, and provided a default value for the parameter.

Step 5.b — Create the Dynamic Final SQL Result in Power Query

Now that we have the parameter set up, our final step to make this dynamic SQL model is to create a Power Query in M to dynamically set the rule based on the selected parameter value and run the SQL logic that is returned. We can implement this pattern for entire SQL commands, or we can piece SQL statements together and only dynamically build certain columns. The pattern is the same. To do this, create a new Raw Query Source and put in the following M code:

let

    // Define Data Source Once
    DataSource = Databricks.Catalogs("<<server_host>>", "<<http_path>>", [Catalog="main", Database=null, EnableAutomaticProxyDiscovery=null]),


    // Get SQL to execute from Paramter
    DynamicSQL = let
        sqlQuery = "SELECT sql FROM main.dynamic_sql_demo.dynamic_rules_permission_table_view where nation ='" & Nation_Name & "' LIMIT 1",
        sqlResult = Value.NativeQuery(DataSource{[Name="main",Kind="Database"]}[Data], sqlQuery, null, [EnableFolding=true]),
        FetchedSQL = sqlResult{0}[sql]
    in
        FetchedSQL,

    // Execute the Retrieved SQL
    Source = Value.NativeQuery(DataSource{[Name="main",Kind="Database"]}[Data], DynamicSQL, null, [EnableFolding=true])

in
    Source

With this final result, this should be what our Power Query Model Looks Like (you can ignore the DynamicSqlQuery query, as it is just to display the SQL executed for demo purposes):

Final Power Query Data Model

Step 4 — Bind the Parameter to Visuals in the Report

Once we apply our Power Query model and go to the report model view, we should see this in the model tab:

Model Tab of the Power BI Canvas

Next, we can use the distinct nation list (which is our parameter) in reports as a live filter that will now automatically look up the dynamic SQL rule and return the result. As the final step to make this all work together, we just need to bind the parameter to the column in the model by selecting the “Distinct Nation List” column, doing to the “Advanced” Section of the properties tab, and binding that column to our created parameter like so:

Bind the parameter to the column / list

Now we can move to our final step — creating a report!

Step 6 — Report Creation

Lets create a Power BI slicer visual showing the nation names from the “Distinct Nation List” table created in above and we create a simple table visual showing the Nation Name and its corresponding amount from the query that is stored in our rules table.

When we select “Kenya” — this above logic shown gets run. But when we select “France”, the new logic is retrieved and run dynamically:

There are a few very key things to note here:

• Notice that we do NOT see “BRAZIL” in our filter select list. This is our permission table at work. I am not in the “data_analysts” group in Databricks, so I do not even get the option exposed to run the logic for Brazil.
• We have a very lean data model! All queries are direct queries, and thus we do not need to wait to import a whole bunch of data just to execute our custom logic, this is very fast and lean.

Conclusion

As we have shown above we can move the logic of multiple reports running against the same dataset to Databricks SQL which in turn helps in creating reports dynamically using M query parameter binding. This approach is helpful for minimizing the number of reports that really need to be created and managed by Analysts while giving the same functionality the end users want. This design pattern is often seen in business applications where there are many custom rules that need to be implemented for downstream users such as coding custom contractual logic (i.e. Supply Chain), application behavior logic, etc. This design pattern allows users to have 1 report for all the custom rules while being able to dynamically secure access to the underlying data and queries via Unity Catalog and the Permission Table design pattern.