Author Archive

Slicer Pane in Power BI

March 18, 2019 Leave a comment

When watching the February 2019 update video and learning about the new Filter Pane experience (in preview).  I learned about another feature, which had gone again unnoticed by me.

I am trying to keep on top of new features, but it is a daunting challenge.  Anyhow when I first heard about the “Slicer Pane”, I went hunting under visualizations, options, properties for a slicer on how to make a pane out of this.  With no luck, I was getting frustrated.  Where was this functionality, because it was annoying so much of my page space was stolen by slicers.  To get around this, I created a page for slicers, and relied on slice syncing.  Which was good, expect when slicers didn’t update as expected under sync scenario.  Then like any good IT geek, I took to Bing! (Yaah! I use Bing!) and found video Guy in Cube).

So learning about slicer pane was cool.  What made it more cool was it was not a new feature, rather it was creative way to use bookmarks.

High-level approach:

  1. Build slicers you need for the report page.
  2. Build a button to show slicers.
  3. Build a button to hide slicers.
  4. Build a bookmark where slicers + hide slices button is visible but show slicers button is hidden.
  5. Build another bookmark where show slicer is visible but slicers + hide slicer button is hidden.

You can make this a bit more fancy by booking box around it, but it is just steps above. Let’s walk through an example.


I have a simple report with graph showing sales by product.  It has two slicers by product category and region name.  I want to change the slicers into a slicer pane to allow for bigger graph view.


So first, I’ll create a box around my slicers and grow my graph.  So slicers and box over lap with graph.  Then I’ll make sure the view order is correct (under Format for visual, or by using the selection pane).  So when slicer/box is visible they on top of the graph.


Next I add couple of buttons (I chose right/left arrows).  One button will be used to hide the slicers and another to show them.


Next we are going to create couple of bookmarks, name them “Show slicers” and “Hide slicers”.  Right now they are just place holders, we will update them later.


Now we’ll open the Selection Pane and hide the left arrow button and update the Show Bookmarks.


Next we’ll hide the right arrow button, slicers, and the box and update the Hide Bookmarks. We are have the main work done, now we just need to program the action for each button and we have a slicer pane that can show up on demand.


While having the right arrow selected, update the action property.  To bookmark, and set the bookmark to “Hide bookmark”.


Next select having the left arrow selected, update the action property again.  This time set it to “Show Bookmarks”.  Now you can test the behavior out in Power BI Desktop by CTRL+Left Clicking arrow to making sure it works as intended.

So this was cool learning for me :).  Here’s to more Power BI learning!

PS With this post I am caught up for my promise of one post/month :). Now the next challenge not to fall behind again.

Categories: Power BI Tags:

PowerBI Syntax Highlight in Advance Editor

March 11, 2019 Leave a comment

This is a continuation of the post from last week.  Where I discovered by accident smart guides.  Next thing I discovered was Syntax Highlight.  Again another update — rather preview feature — I missed.

I tell customers to update their PowerBI Desktop frequently as Microsoft updates it monthly.  However, I get caught in daily grind and forget about my own updates.  So I miss important functionality that will make my life easier.

I was working on a function in Power Query Language (M Language) in advance editor.  However, I was running older version, so editing code was in single color (black).


Now most of the code for Power Query is written by Power BI (thank-god).  But if you had to do some custom coding, then you are on your own.  No syntax high-light, now brackets intelligent.  It got very frustrating when working on a more complex code.

So after turning on preview feature “M Intellisense”, now …


Again, I was jumping up and down.  Because I hated writing all black code.  I am a developer at heart.  And all Microsoft IDE have syntax high-light.  It was a big disappointment PowerBI did not have this.   Next thing that was disappointment in PowerQuery no intellisense.  Whoever heard of Microsoft products without, I was in hell.  Dam it!  Looking up references for each function was nightmare. This update made that pain go away! Yeeeh for preview features *bows*.


See, my PowerBI life is complete! Yes! Well until the next thing that gets me all happy :).

Power BI Smart Guides for Aligning Objects

March 5, 2019 Leave a comment

It seems all I am teaching for last year is PowerBI.  In my on journey to learn PowerBI development, one of the biggest things I hated was lack of alignment options in visualizations.

We have the align objects, when we select more the one visualization:

PBI Align Options

This is handy but it was still annoying because coming from other Microsoft products that have guides right on-screen for alignment, this was nice but was a big miss for me :(.

Next my pain was, if I want all the visuals to be the same size, now what?  There is no option for same size under format.

General Format

This was again handy but still annoying. Because doing this for each visual is painful.  To come over this I can use snap objects to grid, which helped a bit was not enough.   So when I discovered the guidelines I was in cloud 9 for a while (I am amused very easily these days :P).  I am very lazy in updating in my PowerBI Desktop, and I am further busy with reviewing all the updates released each month.  So I had completely missed this update from January 2019, Smart Guides!~  Discovered this while helping client build reports.

If you don’t have the newest version, I recommend updating it.  If you are running older than January look at preview options.  Anyhow, now when we move objects around we start seeing red-line to let me see the smart guidelines.  For bottom of object alignment, top of object alignment, center of an object, plus to help with sizing to make sure they are same size.

Center Align Smart Guide

Top Align Smart Guide

Bottom Align Smart Guide

PS. This is post for Jan 2019. I am a bit behind, goal was to get one post/month. So I hope to catch up for Feb post and Mar post before end of March :).

Where have I been?

January 10, 2019 Leave a comment

Lost I guess.  I had to put my blog on the back burner while I focus on life and work.  I hope to come back to it this year.  Hopefully with more topics than just SQL Server.  Lately, I find I been only teaching PowerBI.

So as time permits, I’ll get posts rolling her this year :).

New year resolution; 1 post-month :).

Categories: Administration

Help! I have -2, -3, or -4 Session ID!

June 21, 2016 Leave a comment

We can kill a session by using KILL command.  However, KILL command requires a positive number; executing KILL with negative number returns an error:

Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.

In order to kill the session ID, you need to find the unit of work (UOW) guid.

SELECT DISTINCT(request_owner_guid) AS UOW
  FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2,-3,-4)

Now you can kill this using UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.

The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?

Session ID Description
 -2 The blocking resource is owned by an orphaned distributed transaction.
-3 The blocking resource is owned by a deferred recovery transaction.
-4 Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

Reference: Books Online, sys.sysprocesses (Transact-SQL)

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Issues with CU6 for SQL Server 2014 SP1

June 14, 2016 2 comments

Microsoft released an update to CU6 (link) for SQL Server 2014 SP1; this update was to address an issue where even having NOLOCK hint in queries was leading to blocking and deadlocks in the default SQL Server lock-based isolation level or high levels. From the KB article:

Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, and specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.

While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.

Reference: CU6 for SQL 2014 SP1 (Deprecated) [Link].

Therefore if you have the older build installed (12.0.4449) please update to newer build (12.0.4457).

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.


Dissecting SQL Server Execution Plans

May 31, 2016 7 comments

I remember my days before, Microsoft SQL Server PFE.  I wanted to learn everything and know everything about SQL Server.  However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator.

Along the way I did pick up lots of books and references.  One of such books is Dissecting SQL Server Execution Plans.

I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once.

Grant Fritchey (Blog|Twitter) wrote the book back in 2008; I would still recommend take ready.  This book will only help you be better DBA and Developer.

SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans

Amazon, SQL Server Execution Plans

SQL Central, Red Gate, EBook

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

SQL Server 2016 Launch Event in Edmonton SQLPASS

May 24, 2016 1 comment

SQL Server 2016 release date is June 1st; to help get the moment started, Edmonton SQL Pass is holding a launch event on June 25, 2016.  Keep your calendar clear and come on down.  Not a lot of seats open.  Register here!

I am going, see Edmonton SQL Geeks there :).


SQL Skills: SQL Server Wait Types & Latch Classes Library

May 17, 2016 Leave a comment

Microsoft has documented wait types and latches in a minimum fashion on books online; often there are wait types that are just not documented.

Paul Randel (Blog|Twitter) has put together compressive list of these with recommendations and even SQL dump traces.

Check it out here..


SQL Server 2016 Release Date

May 3, 2016 Leave a comment

SQL Server 2016 GDR has been set to release on June 1, 2016 (Ref).

SQL 2016 is going to be a major blast in how SQL Server is used for OLTP, OLAP and how it integrates with Microsoft Azure.  If you are not already playing with it, take time to install it and explore the gems hidden in multitude of new functionality.


Categories: Announcement, SQL 2016 Tags:
%d bloggers like this: