Faster Power BI and Analysis Services Development with Automatic Data Subsets

If you have worked with larger tables in either Power BI or Analysis Services, you'll know that developing against them is painful. Any time the data needs to be refreshed (which happens often), you spend a lot of time sitting around waiting for it to happen.

Now our buddy Chris Webb proposed a way of getting sampled data during development. That's pretty good. And fellow MVP Marc Lelijveld also uses a similar method.

In both cases, they are using a TOP N inserted to limit the number of rows. But it's never been what I want.

What I Really Want

First, I don't really want a TOP N, as I generally want a specific range of dates. For example, while I might want all data when the model is in production, or I might have a starting date for that, I usually want just a specific subset of the data for development. I often want say, just the last two months.

Second, when I'm working with source control systems, I don't want to be changing the BIM or PBIX files in any way at all, as they move between development and deployment. I don't even want to use different parameters.

Ideally, I wish the development tools like PBI Desktop, Analysis Services Tabular Designer in Visual Studio, etc. automagically included an extra limiting predicate while I'm developing.

My Workaround

In the meantime, I've come up with what I think is a pretty good workaround. I'm making the views that I connect to, determine what to return, based on either the HOST_NAME() or APP_NAME() from the SQL Server connection. Let me show you.

First I'll create two of the schemas that we commonly use:

IF NOT EXISTS (SELECT 1 FROM sys.schemas 
                        WHERE [name] = N'DataModel')
BEGIN
    DECLARE @SQL nvarchar(max) = N'
      CREATE SCHEMA DataModel AUTHORIZATION dbo;';
    EXEC (@SQL);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas 
                        WHERE [name] = N'Analytics')
BEGIN
    DECLARE @SQL nvarchar(max) = N'
      CREATE SCHEMA Analytics AUTHORIZATION dbo;';
    EXEC (@SQL);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas

WHERE [name] = N'DataModel')

BEGIN

DECLARE @SQL nvarchar(max) = N'

CREATE SCHEMA DataModel AUTHORIZATION dbo;';

EXEC (@SQL);

END;

IF NOT EXISTS (SELECT 1 FROM sys.schemas

WHERE [name] = N'Analytics')

BEGIN

DECLARE @SQL nvarchar(max) = N'

CREATE SCHEMA Analytics AUTHORIZATION dbo;';

EXEC (@SQL);

END;

(Another thing I really wish for: CREATE SCHEMA IF NOT EXISTS or CREATE OR ALTER SCHEMA).

Next I'll create a table to hold a list of the hosts that I'm using for development:

DROP TABLE IF EXISTS DataModel.DevelopmentHosts;
GO

CREATE TABLE DataModel.DevelopmentHosts
(
    DevelopmentHostID int IDENTITY(1,1)
        CONSTRAINT PK_DataModel_DevelopmentHosts
            PRIMARY KEY,
    HostName sysname NOT NULL
);
GO

CREATE INDEX IX_DataModel_DevelopmentHosts_HostName
ON DataModel.DevelopmentHosts (HostName);
GO

DROP TABLE IF EXISTS DataModel.DevelopmentHosts;

CREATE TABLE DataModel.DevelopmentHosts

(

DevelopmentHostID int IDENTITY(1,1)

CONSTRAINT PK_DataModel_DevelopmentHosts

PRIMARY KEY,

HostName sysname NOT NULL

);

CREATE INDEX IX_DataModel_DevelopmentHosts_HostName

ON DataModel.DevelopmentHosts (HostName);

Note: I often get questions about the data type sysname. It's the data type for system objects, and is currently mapped to nvarchar(128).

Then I'll create and populate a table that I'm pretending is part of my data model:

DROP TABLE IF EXISTS DataModel.Transactions;
GO

CREATE TABLE DataModel.Transactions
(
    TransactionID bigint IDENTITY(1,1)
        CONSTRAINT PK_DataModel_Transactions
            PRIMARY KEY,
    TransactionDate date NOT NULL,
    TransactionAmount decimal(18,2) NOT NULL,
    IsFinalised bit NOT NULL
);
GO

SET NOCOUNT ON;

DECLARE @DateCounter date = DATEADD(year, -1, SYSDATETIME());

WHILE @DateCounter < SYSDATETIME()
BEGIN
    INSERT DataModel.Transactions 
    (
        TransactionDate, TransactionAmount, IsFinalised
    )
    VALUES (@DateCounter, RAND(12) * 100.0, 0);

    SET @DateCounter = DATEADD(day, 1, @DateCounter);
END;
GO

DROP TABLE IF EXISTS DataModel.Transactions;

CREATE TABLE DataModel.Transactions

(

TransactionID bigint IDENTITY(1,1)

CONSTRAINT PK_DataModel_Transactions

PRIMARY KEY,

TransactionDate date NOT NULL,

TransactionAmount decimal(18,2) NOT NULL,

IsFinalised bit NOT NULL

);

SET NOCOUNT ON;

DECLARE @DateCounter date = DATEADD(year, -1, SYSDATETIME());

WHILE @DateCounter < SYSDATETIME()

BEGIN

INSERT DataModel.Transactions

(

TransactionDate, TransactionAmount, IsFinalised

)

VALUES (@DateCounter, RAND(12) * 100.0, 0);

SET @DateCounter = DATEADD(day, 1, @DateCounter);

END;

I've added a transaction for every day in the last year.

Next, I'll create the type of analytic view that we often use:

CREATE OR ALTER VIEW Analytics.[Transaction]
AS
SELECT t.TransactionDate AS [Transaction Date],
       t.TransactionAmount AS [Transaction Amount],
       t.IsFinalised AS [Is Finalised] 
FROM DataModel.Transactions AS t
      -- normal start date for this table
WHERE t.TransactionDate >= '19900101'; 
GO

CREATE OR ALTER VIEW Analytics.[Transaction]

SELECT t.TransactionDate AS [Transaction Date],

t.TransactionAmount AS [Transaction Amount],

t.IsFinalised AS [Is Finalised]

FROM DataModel.Transactions AS t

-- normal start date for this table

WHERE t.TransactionDate >= '19900101';

There's a hard cutoff date for loading data (perhaps the start of sensible data) and if I query this, I see all 365 rows.

SELECT * FROM Analytics.[Transaction];
GO

1 2	SELECT * FROM Analytics.[Transaction]; GO

And the Secret Sauce

The trick is to change the view so that it makes decisions based on working out if I'm in development or not:

CREATE OR ALTER VIEW Analytics.[Transaction]
AS
SELECT t.TransactionDate AS [Transaction Date],
       t.TransactionAmount AS [Transaction Amount],
       t.IsFinalised AS [Is Finalised] 
FROM DataModel.Transactions AS t
WHERE t.TransactionDate >= 
    CASE WHEN EXISTS (SELECT 1 FROM DataModel.DevelopmentHosts 
                               WHERE HostName = HOST_NAME())
              -- development subset for this table
         THEN DATEADD(month, -2, SYSDATETIME())
              -- normal date range for this table
         ELSE '19900101' -- normal date for this table
    END;
GO

CREATE OR ALTER VIEW Analytics.[Transaction]

SELECT t.TransactionDate AS [Transaction Date],

t.TransactionAmount AS [Transaction Amount],

t.IsFinalised AS [Is Finalised]

FROM DataModel.Transactions AS t

WHERE t.TransactionDate >=

CASE WHEN EXISTS (SELECT 1 FROM DataModel.DevelopmentHosts

WHERE HostName = HOST_NAME())

-- development subset for this table

THEN DATEADD(month, -2, SYSDATETIME())

-- normal date range for this table

ELSE '19900101' -- normal date for this table

END;

If the query isn't coming from a development host, it'll return all data since the same hard-coded start date (i.e. start of 1990). But if I'm on a development host, it'll just return the last two months of data.

I'll add my client to the list of development hosts:

INSERT DataModel.DevelopmentHosts
(
    HostName 
)
VALUES (HOST_NAME());
GO

INSERT DataModel.DevelopmentHosts

(

HostName

)

VALUES (HOST_NAME());

And then query the view again:

SELECT * FROM Analytics.[Transaction];
GO

1 2	SELECT * FROM Analytics.[Transaction]; GO

And now I see only 62 rows, without changing the code at all.

Back in Power BI or Analysis Services Tabular Designer, if I'm on my client, I see the subset, but on the server, I see all the data without changing the BIM or PBIX file at all.

What if Host Won't Work for You

There might be situations where making the choice based upon the host name just won't work for you.

In that case, I'd suggest checking the APP_NAME() function instead of the HOST_NAME() function, and having a list of apps that get development subsets instead of full data sets.

6 thoughts on “Faster Power BI and Analysis Services Development with Automatic Data Subsets”

Andrew Pirie says:

February 28, 2020 at 8:26 pm

That is very clever Greg.

I've wasted several two-hour periods waiting for a dev Power BI data source update. This would avoid that, leaving the full dataset loads to the refresh on the service.

Thank you!

1. greglowblog says:
  
  February 28, 2020 at 8:39 pm
  
  You are most welcome !
  
Bryan Campbell says:

March 4, 2020 at 4:03 pm

This is good stuff. But I've accomplished the same with simple views on SQL, parameters in PBI (service and desktop), and dataflows.

It is a well written piece… much was learned… 🙂

1. greglowblog says:
  
  March 5, 2020 at 10:22 am
  
  Hi Bryan, the reason that I didn't go down that path is I want to neatly integrate with GitHub, DevOps, etc. and I want to be able to deploy the files unchanged, and without needing to change anything after deployment. I can't see any way that I can do that with parameters. What would make it better is if the tools themselves offered an option for adding say another query predicate while in design mode.
  
Paul says:

March 5, 2020 at 4:47 am

Hi Greg,

This looks very useful. I wish I could apply something similar to a dataset I'm combining from SharePoint folders that is > 2GB.

Any ideas for that scenario?

1. greglowblog says:
  
  March 5, 2020 at 10:18 am
  
  Hi Paul, no idea on that one sorry. The upside for T-SQL is that there are connection properties (like APP_NAME, HOST_NAME), etc. that we have access to.