Press "Enter" to skip to content

Author: Kevin Feasel

Warning Signs with Power BI Development and Administration

Published 2020-02-06 by Kevin Feasel

Brett Powell has a great post warning you of common pitfalls with Power BI implementations:

Overly Broad User Classifications

It might be tempting to classify users in the organizations into only two segments or personas such as ‘end users’ and ‘creators’. You might logically reason that ‘creators’ will be assigned pro licenses and be trained to develop and publish content while ‘end users’ will be trained on how to consume and access content.

This simple binary distinction may be appropriate when you’re first getting started with Power BI but I’d suggest a bit more granularity reflecting the significantly different skills, features, and complexity associated with developing different kinds of Power BI content. At a minimum, I split the creators into ‘Report Authors’ and ‘Data Modelers’ with the report authors learning to build visually rich and intuitive user experiences based on the robust, secure, and performant datasets created by the data modelers.

There’s a lot of good reading in here.

Comments closed

Connecting to Snowflake with Power BI

Published 2020-02-06 by Kevin Feasel

Gilbert Quevauvilliers shows us how we can connect from a Snowflake DB instance to Power BI using DirectQuery:

The first thing I did was to install the ODBC Drivers.

I installed the 64bit drivers where I had my Power BI Desktop installed, and I also installed it on all the Servers where I had the On-Premise Data gateway installed.

Below is the link that I used which should always be the latest version

https://sfc-repo.snowflakecomputing.com/odbc/win64/latest/index.html

One thing to note is all that I did was I installed the ODBC driver I did not actually do any configuration of the ODBC driver, this is because it will be configured in Power BI Desktop.

Read on for the configuration instructions as well as getting past “it works in Power BI Desktop.”

Comments closed

Troubleshooting Slow Power BI Report Server Reports

Published 2020-02-06 by Kevin Feasel

Jamie Wick helps us figure out why that Power BI Report Server report is loading so slowly:

Troubleshooting “slow” reports in PowerBI Report Server (or SQL Server Reporting Services) can be an arduous task. End users are often unable to provide detailed (or reliable) data that a report took longer to load today than it did the last time it was run. Even if a user states that the report is now taking 10 seconds longer to load, that additional time needs to be attributed to a specific step in the report generation process before it can be improved/fixed.

In the report server database (ReportServer by default) there is a view (ExecutionLog) that can provide detailed statistics about each execution of a report. Note: ExecutionLog3 view is the newest/current version and the ExecutionLog and ExecutionLog2 views are for backwards compatibility. By default the execution log entries are retained for 60 days.

The view that Jamie shows also works for SQL Server Reporting Services reports, so it can help there as well.

Comments closed

Quick Hits on Azure Databricks Performance

Published 2020-02-05 by Kevin Feasel

Rayis Imayev has a few thoughts on optimizing delta table-based workloads in Azure Databricks:

2) Enable the Delta cache – spark.databricks.io.cache.enabledtrue
There is a very good resource available on configuring this Spark config setting: https://docs.microsoft.com/en-us/azure/databricks/delta/optimizations/delta-cache

And this will be very helpful in your Databricks notebook’s queries when you try to access a similar dataset multiple times. Once you read this dataset for the first time, Spark places it into internal local storage cache and will speed up the process of further referencing it for you.

Click through for several more along these lines.

Comments closed

Fun with Metaphors: Data Lakehouses

Published 2020-02-05 by Kevin Feasel

Ben Lorica, et al, have a new metaphor to try out:

Over the past few years at Databricks, we’ve seen a new data management paradigm that emerged independently across many customers and use cases: the lakehouse. In this post we describe this new paradigm and its advantages over previous approaches.

The Data Lake’s Aristotelian counterpart is the Data Swamp. I’m working on a similar comp for the Data Lakehouse (Data Swampboat? Data Swamphouse is too easy), but in the meantime, that one person who goes and slaughters your application’s performance by butchering the data in your Data Lakehouse? That’s a Data Jason.

1 Comment

Showing a Calendar in your Powershell Prompt

Published 2020-02-05 by Kevin Feasel

Jeffrey Hicks has fun with calendars in Powershell:

Some of you may be aware of my PSCalendar module which you can install from the PowerShell Gallery. The module contains commands that you can use to display a console-based calendar.  The calendar commands let you specify days to highlight. These might be days with special events or appointments. I typically use the Show-Calendar command as it writes to the host and colorizes output.

This command also has a parameter that lets you specify a position in your console. In other words, you can tell PowerShell where to display the calendar. I recently fixed a bug with the command that was producing less than optimal results. Now, I can use my PowerShell prompt function to display a calendar. 

The calendar module and functions are quite helpful, and the calendar prompt merits the Wacky Ideas category.

Comments closed

Cleaning Up Schema Ownership

Published 2020-02-05 by Kevin Feasel

Pamela Mooney doesn’t like user-owned schemas:

My colleagues and I take schema ownership seriously.  The owner (with few exceptions) should always be “dbo”.  Certainly, it should not be a user.  Why?  Because if the user leaves and their account is disabled or deleted, we have a problem.  If you’re a DBA, you have enough problems without adding this one to your list. 

So, how do you find these offenders, much less fix them?

That’s what you’ll find out, but only if you click through.

Comments closed

Using Windows Subsystem for Linux 2 in Windows 10

Published 2020-02-05 by Kevin Feasel

Max Trinidad is excited about Windows Subsystem for Linux 2:

First, I love WSL (Windows Subsystem for Linux)! It’s a great addition to Windows 10, and everyone should learn how to use it.

To get started, follow the instructions on how to get your WSL 1 Linux Distro installed. And, begin with installing Ubuntu 18.04.

Now, get Docker Desktop (), which can be installed in Windows 10 RTM Build 18363 with WSL 1.

One of the key benefits around WSL 2 is that your Docker containers will run natively rather than through a VM. That’s a pretty big deal in terms of performance and production-readiness. That Docker capability is currently in preview, but I’d expect it to make its way to production sooner than later.

Comments closed

When Identity Columns Skip Values

Published 2020-02-05 by Kevin Feasel

Kevin Hill explains why you might see SQL Server skip 1000 values in an identity column:

Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…

I’d probably avoid the fix and live with gaps. You also get gaps when you roll back an operation which inserted into an identity column, or if you have merge replication enabled on a table keyed by an identity column and SQL Server bumps the range on you. All of these are normal and good reasons not to expect contiguous numbering.

1 Comment

Tracking Query Store Changes

Published 2020-02-05 by Kevin Feasel

Erin Stellato shows how to watch for Query Store changes whether due to settings modifications or running out of space:

The Query Store feature is a bit unique in that its status can change without user interference, which means it is important to understand how changes to Query Store are logged.  A couple weeks ago John Deardurff posted a question on Twitter asking specifically whether the SQL Server ERRORLOG is written to when the OPERATION_MODE changes to READ_ONLY because  MAX_STORAGE_SIZE_MB is exceeded.  I had never tested to confirm, but I know there is an event in Extended Events that will fire when the limit is reached.  I also know that when a user makes a change to a Query Store setting, it is logged in the ERRORLOG.

Click through to see how to watch for this and what the changes look like.

Comments closed