Press "Enter" to skip to content

Author: Kevin Feasel

DAX Median & 2 Billion+ Rows

Published 2019-07-02 by Kevin Feasel

Chris Webb has bad news for people with tables holding 2 billion-plus rows in Tabular format:

What’s more, the error will always occur even if you apply a filter to the table that returns less than two billion rows. The same problem occurs with some other functions, such as Percentile(), but it’s worth pointing out that the vast majority of DAX functions work as normal with tables with more than two billion rows – for example, in the pbix file used here the Sum() and CountRows() functions not only work fine but return instantly.

I haven’t seen that many Power BI examples with 2 billion or more rows in a table, but it can be an impediment when trying to use Analysis Services Tabular in cases with enormous amounts of data.

Comments closed

Power BI: Showing Only Slicers with Data

Published 2019-07-02 by Kevin Feasel

Kasper de Jonge shows us a few new tricks with Power BI:

As of this month Power BI finally supports filtering slicers down to only show rows that have fact data. Before the only thing you could do to achieve this was some workaround like: I described here where you filter down the dimension using a calc table. The other approach was to use Bi Directional cross filtering which would filter down the dimension table appropriately. This leads to performance issues though.

Now you can use a measure to filter down the slicer. 

Click through for an example as well as a few other tricks you can do as a result.

Comments closed

Executing Azure SSIS Packages from Blob Storage

Published 2019-07-02 by Kevin Feasel

Andy Leonard cranks it to the next level:

I confess: I have been waiting for this feature since I first learned of Azure-SSIS.

When I first saw Azure-SSIS – which creates an Azure Data Factory Integration Runtime and SSIS Catalog in the cloud, my first thought was a paraphrase Ferris Bueller’s question about dying the river green: “If we can execute SSIS packages from the SSIS Catalog in Azure Data Factory, why can’t we execute SSIS packages from Azure Blob Storage?” Today, we can.

Read on to see how you can do it.

Comments closed

Column From Examples in Power Query

Published 2019-07-02 by Kevin Feasel

Matthew Roche shows off the “column from examples” feature in Power Query:

Here’s the quick overview:

1. In the Power Query editor in Power BI Desktop, choose “Column from Examples” from the “Add Column” tab.
2. Enter the values that the new column should have for rows that are already in your data set.
3. Review the values that Power Query is suggesting for the other rows, and when they are all correct, choose OK, and then say “Ooooooohhhhh” when Power Query does all the work for you.

It’s a little hard to see from Matthew’s image but this is a great feature when you have a regular pattern but don’t want to put together a regular expression yourself.

Comments closed

Pandas Multiindex and T-SQL

Published 2019-06-28 by Kevin Feasel

Tomaz Kastrun explains why you should never cross the streams:

1. SQL Server and Python Pandas Indexes are two different worlds and should not be mixed.
2. SQL Server uses Index primarily for DML operations and to keep data ACID.
3. Python Pandas uses Index and MultiIndex for keeping data dimensionality when performing data wrangling and statistical analysis.
4. SQL Server Index and Python Pandas Index don’t know about each other’s existence, meaning if user want to propagate the T-SQL index to Python Pandas (in order to minimize the impact of duplicates, missing values or to impose the relational model), it needs to be introduced and created, once data enters “in the python world”.

Read on for additional conclusions and the demos which bring us here.

Comments closed

CQL: Category Theory-Based Querying Language

Published 2019-06-28 by Kevin Feasel

John Cook looks at a querying language based on category theory:

My interest in category theory waxes and wanes, and just as it was was at its thinnest crescent phase I ran across CQL, categorical query language. I haven’t had time to look very far into it, but it seems promising. The site’s modest prose relative to the revolutionary rhetoric of some category enthusiasts makes me have more confidence that the authors may be on to something useful.

I’m going through some lectures on category theory now and am in a big functional programming phase, so this is interesting but I won’t be giving up SQL anytime soon for it.

Comments closed

Checkpoints Under Simple Recovery

Published 2019-06-28 by Kevin Feasel

Max Vernon shows us how checkpointing works when your database is in the simple recovery model:

Even though the transaction has been rolled back, the log records will not be cleared until a checkpoint occurs. An automatic checkpoint could be triggered by other ongoing transactions being written to the log, or a manual CHECKPOINT statement could be executed. However, for a database that is not seeing frequent transactions, the log may stay nearly full for an extended period of time. This scenario might be seen often during development where there are a very limited number of transactions being generated. 

Read the whole thing. Just because you’re in simple recovery mode doesn’t mean the transaction log becomes any less useful.

Comments closed

When Power BI Publish to Web is the Wrong Choice

Published 2019-06-28 by Kevin Feasel

Treb Gatte takes us through Publish to Web and the importance of getting the correct licensing for Power BI:

Some folks, either to avoid the need to buy a Microsoft Power BI license or in trying to embed Microsoft Power BI content in an On-Premises site like Microsoft SharePoint 2013, published their content using this function.

The risk is that if the content is on a page that gets indexed by a major search engine, like Google, the embed code will likely live in Google’s index forever. Then anyone can search for your data.

Read on to see the right way to do this. Treb also notes that there are good use cases for Publish to Web; you just have to make sure yours is one of them.

Comments closed

Unicode Escaping Across Various Languages

Published 2019-06-28 by Kevin Feasel

Solomon Rutzky shows how to perform Unicode character escaping in a dozen places:

The purpose of this post is to correct the overall lack of examples. Everything shown below are actual working examples of creating both a Unicode-only BMP character (meaning a non-Supplementary Character that would require Unicode) and a Supplementary Character. Most examples include a link to an online demo, either on db<>fiddle (for database demos) or IDE One (for non-database demos), both very cool and handy sites.

This includes T-SQL and MySQL as well as .NET languages, PHP, JavaScript, and even Excel. It’s a handy reference page.

Comments closed

Pre-Filtering Power BI Slicers

Published 2019-06-28 by Kevin Feasel

Matt Allington takes us through a new feature in Power BI:

Now back to the point of this post.  As of June 2019, it is possible to pre-filter slicers as well.  It may seem weird, but this previously wasn’t possible – it seemed weird to me, anyway.  This is now fixed and it is possible to use the side filter pane in the same way as other visuals.  I can think of quite a few useful scenarios, including:
– Hiding the dreaded (Blank) in a slicer.  [Actually, please don’t do that, but instead fix your data model].
– Filtering out items not relevant (eg category managers may only want to see their own products)
– Hiding items with no sales

Read on for a few examples of how to use this.

Comments closed