Friday, June 24, 2016

Power BI: Custom Visuals - Hierarchy Slicer

I've been playing with PowerBI recently and have discovered their Gallery of Custom Visuals. I decided to try a few to see what they do and determine whether they'd be useful in future PowerBI projects.

Previously, I tested the "Meme generator" (Note: this visualization has been removed temporarily from the PowerBI website, but should reappear shortly). This time, I've chosen one that I hope to be useful to PowerBI users.

The Hierarchy Slicer is available from the PowerBI Gallery of Custom Visuals. Click on its icon to open up the Description and Download dialog. This dialog also offers the option to download a sample PowerBI file that demonstrates the use of the visual.


Testing

After examining the sample file, I decided to experiment with the new visual on my own.

As with all custom visuals, when you open a new PowerBI report, the custom visual is not automatically available in the visuals list. To make it available you must first import it into the Visualizations bar by clicking on the " . . . " icon.

PowerBI will pop up a warning that you're attempting to load a custom visual and that you should only open it if you trust its author and source.
 Click on Import to open a dialog to browse for the custom visual you just downloaded. It should be named, "HierarchySlicer.0.6.0.pbiviz" (or similar - they may have updated it since I downloaded it) and it should be located in the directory where you normally download files (unless you already moved it to another location).

After you select the custom visual file, PowerBI should pop up a message stating "The visual was successfully imported into this report."  You can now use it in the report you currently have open.

The new custom Visual will be between the last standard Visual and the ". . ." symbol, as highlighted in yellow, below.
Please keep in mind that for every report where you wish to use a custom visual, it will have to be imported into the current report because PowerBI only keeps the custom visuals linked in the individual reports and not in the application itself. 

For my first experiment with the Hierarchy Slicer, I used the Foodmart.mdb as the data source. I defined a hierarchy in the product_class table (product_family, product_department, product_category, product_subcategory), and set up a report for "Store sales by product hierarchy".

I then added the Hierarchy Slicer to my report and selected the Product Hierarchy for the "fields"

I could then click on various levels of data and observe the filtering of the products change as I made different selections. 

Observations

The Good

  • Easy to use
  • Works great for small hierarchies 
  • Can configure either as "single select" or "multi-select"

The Bad

  • The formatting option to switch the selection from single select to multi-select disappears after the first time the data is filtered, so choose wisely.
  • The hierarchy levels cannot be collapsed, so when working with Date hierarchies, be sure to remove levels below Month from the Hierarchy Slicer otherwise finding dates in the middle of the hierarchy (such as a date 2 years from earliest and 3 years from latest dates) will be extremely challenging. The slicer will also produce an error when loading more than 5 years of dates.

Conclusion

While the Hierarchy slicer is an excellent concept, because it lacks the ability to collapse the hierarchies, I think I'd rather use multiple instances of the standard "Slicer" visual to handle the filtering of the lower level hierarchy choices.


Tuesday, June 14, 2016

Power BI: Custom Visuals - "Meme Generator"

I've been playing with PowerBI recently, and have discovered their Gallery of Custom Visuals. I decided to try some to see what they do and determine whether they'd be useful in future PowerBI projects.

The one that caught my eye first was the "Meme generator". This was clearly created as an exercise in creating a new Visual. It shows that not all of the Visuals necessarily have to be "business ready", just fun or useful in some way. The "Meme Generator" is brilliant in its simplicity. All one can do with it is alter the text at top & bottom and choose the image that links to it.

As you can see to your left, I've tested the Meme Generator with an image of the famous "Steampunk Bacon Cat" embroidery from Urban Threads, a company that produces wildly awesome machine embroidery.

To obtain any of the custom visuals, visit the PowerBI Gallery of Custom Visuals, and click on the one that draws your interest. A dialog box with a description of the custom visual will pop up. The dialog box will also provide a download button and (at least in the case of the Meme Generator) a button to download a sample PowerBI file with examples of the custom visual in use.

After you've downloaded the "Meme Generator" custom visual and its sample file, double-click on the sample file to open it in PowerBI.
The "Meme Generator" custom visual sample file contains three examples of the Meme Generator in use.  If you want to try creating your own Meme, click on the Cat icon (next to the "R" icon as seen below:) 

This will create a "Meme template" for you, which conveniently has instructions for how to configure it.

In the Visualizations section, you will see the options for configuration. 

Change the Top Text & Bottom Text to words appropriate to your Meme, then enter the URL of the image you wish to use for the meme. You may also add a title, change the background, or add a border. You can take a screen shot of the image - I used the "Send to OneNote" screen clipper to select the image then paste it into Paint so I can save it as an image ready to be posted. 


Thursday, June 9, 2016

Better SQL Syntax to Test for Existence Before Dropping



For the longest time, I've been using code like this

IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[FakeProc]')
      AND type IN (N'P',N'PC')
    )
  DROP PROCEDURE [dbo].[FakeProc]
GO

IF EXISTS (
     SELECT [name]
     FROM sysobjects
     WHERE [name] = 'trFakeTrigger'
AND type = 'TR'
     )
BEGIN
DROP TRIGGER trFakeTrigger;

END
Go

to test for existence of a Procedure or Trigger prior to dropping it. The challenge inherent within using the above syntax was remembering the abbreviation used to define the object type.

Later on, I found that I could use

IF OBJECT_ID('FakeProc') IS NOT NULL
BEGIN
DROP PROCEDURE FakeProc
END;
GO

IF OBJECT_ID('dbo.fn_FakeFunction') IS NOT NULL
BEGIN
DROP FUNCTION dbo.fn_FakeFunction
END;

GO


This was certainly more memorable than the earlier version but was still not the best choice. I needed a template that could be easily copied multiple times and modified with minimal issues.

I then discovered the most efficient trick: surround the drop command with a TRY - CATCH block.

BEGIN TRY DROP TRIGGER FakeTrigger END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP TABLE #Test END TRY BEGIN CATCH END CATCH;

BEGIN TRY ALTER TABLE MyTable DROP CONSTRAINT FakeConstraint END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP PROCEDURE FakeProc END TRY BEGIN CATCH END CATCH;

The best part is that MSDN already has written the code for a TRY-CATCH snippet. This snippet doesn't come built into SSMS, but the page provides clear and simple instructions for creating and importing the snippet. Give it a try and Happy Coding!



Resoved: Error Creating Storage Event Trigger in Azure Synapse

My client receives external files from a vendor and wants to ingest them into the Data Lake using the integration pipelines in Synapse (Syna...