Free SQL Data Profiling Tool: Idera SQL Data Profiler

In this tip I will provide an overview of a completely free data profiling tool (at least as of the time of this post) that is easy to use if you are in the Microsoft database stack. You can download the IDERA SQL Profiling tool and immediately put it to work to perform basic column profiling and analyses. The tool will display a summary of the data contained in a selected table and each of its columns. 

Use this tool on the following systems:

  • Microsoft SQL Server: 2008 R2, 2012, 2014, 2016; 2017 Windows & Linux (provisional); Express, Standard, Enterprise editions
  • Microsoft Azure SQL Database
  • Amazon Relational Database Service (RDS)

IDERA SQL Data Profiler has some minor quirks but you can’t beat the price. Check out my review in the video above.

Just remember that data profiling should always be done initially before you start analyzing a new dataset or designing a new visualization. Always start with the basics.

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Advertisements

Filter Top N Values with a Slicer in Power BI

In this video you will learn how to filter the top N values shown in your bar chart visualization using a slicer.

  1. This technique uses one measure that generates a number 1-10, that will be applied to a slicer.
  2. Another measure will basically rank all of the values associated with your data bars and only return the values that are less than or equal to the number you select in the slicer.

The comments that I apply to the DAX function should help make it easy to understand. I have to give a shoutout to GilbertQ from the PowerBI community for coming up with the  initial approach which I tweaked for the video.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel!

How to Compare Actuals vs. Forecast in Tableau

Forecasting in Tableau uses a technique known as exponential smoothing. This is when an algorithm tries to find a regular pattern in your data that can be continued into the future.

In this video I’ll share some helpful tips to help you determine which options you should select that will enable Tableau to make the most predictive forecast for your data. By the end of the video you will be able to differentiate between an additive and multiplicative data pattern and to evaluate MASE to measure the accuracy of the forecast.

I’m not talking about this Mase:

Harlem World

Rather, you’ll learn about the mean absolute scaled error (i.e., MASE) and how it helps you judge the quality of the model.

In addition, you’ll also also learn how to compare your actual data to the Tableau forecast in order to judge if the model is doing its job.

If you’ve used the forecasting capabilities in Tableau without knowing about these concepts, you might have generated an inaccurate error riddled forecast. Don’t just set a forecast and forget it. Watch this video and generate better forecasts in Tableau!

Here is additional reading from Tableau on the forecast descriptions (including MASE).

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

How to Generate a Forecast in Power BI

In this video I’ll demonstrate how to use the forecasting analytics option in Power BI. Although Power BI’s forecast algorithm is a black box, it’s more than likely using exponential smoothing to generate results. At a very high level, exponential smoothing is an algorithm that looks for patterns in data and extrapolates that pattern into the future. To help exponential smoothing perform at an optimal level, it is very important to pick an accurate seasonality estimation, as this will have an outsized effect on the time series forecast.

If your data points are at the daily grain, then you’d use 365 as your seasonality value. If your data points are at a monthly grain, then you’d use 12 as your seasonality value. Generally, the more seasonality cycles (e.g., years) that you provide Power BI, the more predictive your forecast will be.

Without giving away the whole video, here is a pro and a con of using forecasting in Power BI.

Con: As I stated earlier the exact algorithm is a black box. Although based upon a Power View blog post, we can reasonably assume exponential smoothing is involved. Furthermore, the results cannot be exported into a spreadsheet and analyzed.

Pro: The ability to “hindcast” allows you to observe if the forecasted values match your actual values. This ability allows you to judge whether the forecast is performing well.

Check out the video; I predict you’ll learn something new.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Row and Column Highlighting in Tableau

In this post you’ll learn how to highlight values in your Tableau table using set actions. The dashboard in this video displays the number of total points scored by NBA teams by position in the 2017-2018 season. I will give you step by step instructions on how to implement row and column highlighting on this dataset downloaded from basketballreference.com.

I’ve only made a few minor tweaks but this technique was developed by Tableau Zen Master Matt Chambers. You can check out his blog at sirvizalot.com and follow him at Big shout out to Matt for sharing this technique with the Tableau community!

You can interact with my visualization on Tableau Public:

If you find this type of instruction valuable make sure to subscribe to my Youtube channel!

Make Flashy Maps in Tableau with Mapbox

The default maps in Tableau are just fine but sometimes you need to kick up the flamboyancy factor in your visuals. Integrating maps from Mapbox with Tableau is the perfect way to add some Liberace flash to your development game.

Mapbox is an open source mapping platform for custom designed maps.  By creating an account with Mapbox, you can either design your own maps on the platform or use their preset maps, which are all more impressive than the out of the box option in Tableau.

All you need to do is enter your generated API token (provided by Mapbox) into Tableau’s Map Services interface and you’ll have access to some pretty impressive mapping options.

If you’re interested in Business Intelligence & Tableau subscribe to my  Youtube channel.

 

 

How to Drill into Data Using Set Actions in Tableau

Drilling with Set Actions

If you’ve ever tried to use the default drill functionality within Tableau, you know that it could be a more user friendly experience. The default table drill functionality opens all of the options at the next drill level which can force a user to lose sight of the data upon which they’re focusing. A more user-friendly option enables the user to only drill into a specific selected value where focus and attention can be maintained. This is otherwise known as asymmetric drill down.

Fortunately as of version 2018.3, Tableau has added Set Actions as a new functionality. At a high level, developers can take an existing set and update its values based upon a user’s actions in the visualization. The set can be employed via a calculated field within the visualization, via direct placement in the visualization or on the marks card property.

In lay terms this means empowering a user with more interactivity to impact their analyses.

In this first video, I’ll demonstrate a use of set actions on an NBA data set. We’ll drill from Conference to Division to Team to Player. This tip will be easily applicable to your Tableau data. And with the bonus tree-map tip you’ll release your inner Piet Mondrian.

Feel free to interact with the set action example on Tableau Public and then download and dissect the workbook.


Drilling with Level of Detail (LOD) Calculations
If you want to stay with a classic approach, a nice Level of Detail (LOD) workaround can be employed to drill into the next level. Here is a tip that accomplishes a similar outcome where I demonstrate a technique originally presented by Marc Rueter at Tableau Conference 2017.

Now that I’ve equipped you with the knowledge to incorporate customized drilling functionality into your analyses, go forth and do some great things with your data!

References:

https://onlinehelp.tableau.com/current/pro/desktop/en-us/actions_sets.htm
https://www.tableau.com/learn/tutorials/on-demand/set-actions
https://www.basketball-reference.com/leagues/NBA_2018.html
https://www.youtube.com/watch?v=d22A4XVoUEs

Image Copyright dzxy on 123rf.com