Author: Mitchell Pearson

Azure Data Factory–Foreach / Filter activities

/ Mitchell Pearson / Leave a comment

Hello! This is the fourth video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series leverages and explores the filter activity and foreach activity within Azure Data Factory. Please take a look at a quick overview below and then watch the video! Enjoy!

Filter Activity in Azure Data Factory

The filter activity in ADF is used in a control flow to filter down an existing array. The filter activity will take an input array and then output a separate array that has the filtered results. Each row of the input array is evaluated using the expression language within Azure Data Factory.

Foreach Activity in Azure Data Factory

The foreach activity iterates over an array performing an action or set of actions on each item within that list. In this video each item in the array is passed into a stored procedure activity, which then loads specific metadata information into a table in an Azure SQL Database.

Video on YouTube:

 

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Advertisement

Azure Data Factory–Lookup and If Condition activities (Part 3)

/ Mitchell Pearson / Leave a comment

Hello! This is the third video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series leverages the lookup and if condition activity to return a set of results and then determine what operation should occur next based on an expression within the control flow. This is a great demo for learning new activities, expressions and referencing output parameters. Below is a couple of quick highlights on each of the activities featured here and then a link to the ADF video!

Lookup Activity in ADF v2

The lookup activity within Azure Data Factory allows you to execute a stored procedure and return an output. Interestingly, the stored procedure activity does not return any outputs.

  • Leverage the Lookup activity to execute SQL Code or a Stored procedure and return an output.

If Condition Activity in Azure Data Factory

  • Leverage the If Condition activity and ADF Expression language to help control operations in the ADF Control Flow

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory – Stored Procedure Activity (Part 2)

/ Mitchell Pearson / Leave a comment

Hello! This is the second video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

First blog in series: Azure Data Factory – Metadata Activity

Stored Procedure Activity in ADF v2

  • Writing data to an Azure SQL Database via a stored procedure.
  • Populating input parameters from the output properties of other activities in ADF.
  • Limitations for the Stored Procedure activity

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Metadata Activity (Part 1)

/ Mitchell Pearson / 8 Comments

Hello! This is the first video in a series of videos that will be posted on Azure Data Factory! This series will be primarily in video format and can be found on YouTube! Check it out there and if you like, subscribe and encourage me to keep posting new videos!

Metadata Activity in ADF v2

  • Metadata Activity of a file
  • Metadata Activity of a folder
  • Metadata Activity from an Azure SQL Server Table
  • Debugging and output parameters

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Using R in Power BI to check if file exist

/ Mitchell Pearson / Leave a comment

I have done a series of blog posts on how R can be used in Power BI. I have also created quick videos for each of those posts and I will do the same for this post. I find it easier and more efficient to share this information via video than through a narrative with screenshots. In this blog post, I’m going to share the code that can be used to check if a file exist prior to processing of that file. I will record a video that goes in more depth and include that in the blog post later.

Check if file exist

As someone who comes from an enterprise BI background, I am always looking for ways to handle things I would have previously done with SSIS. With R integration we have a lot of new possibilities.

The Code:

fileexists <- FALSE
fileName   <- “C:\\Backup\\Blogs\\R – Check if File Exist\\CustomerSales1.csv“

while (fileexists == FALSE)
    {
   #fileexists = TRUE ##Test Expression
   fileexists <-
            if(
                file.exists(fileName))
               {TRUE} else {FALSE}
   print(fileexists)
   ## Add 3 second Delay
   Sys.sleep(3)   
    }
   
    data  = read.csv(fileName)
    head(data)

PASS Summit–Lightning Talk on R

/ Mitchell Pearson / Leave a comment

I’m excited to have the opportunity to speak at PASS Summit 2018. I am doing a quick 10 minute lightning talk on R integration with Power BI. Somehow I was able to sneak in with this incredible group of speakers! SHHHHH, don’t tell anyone lest they find out!

Speakers

I have loaded my slide deck here, this includes what you need to get started with R! Slide Deck

In this talk I am focusing on how R can be used to extend the capabilities and functionality of Power BI. More specifically, I will be using R to show the following four demos:

  • How to extract data from Power BI using R
  • How to unzip and process files with R
  • How to Download, unzip and process files
  • How to check if file exist before processing the file

Also, I have created blog posts with corresponding YouTube videos already on 3 of the 4 demos listed above!

YouTube

My YouTube channel is simply MitchellSQL

Blog: MitchellPearson.com

Below are the three blog posts that I have previously posted:

Export Data from Power BI using R

Unzip and Process files into Power BI with R

Download, unzip, and process with R

Using a Journal

/ Mitchell Pearson / Leave a comment

In this blog, I’m going to introduce you to my personal journaling method. I have, over time, developed a journaling technique that works great for me. I began my journal experience by scribbling aimlessly in journals with no form of organization. Eventually I was introduced to a journal technique called bullet journaling. I started initially with this technique and over time made many adjustments that have grown to fit me very well.

This blog post is broken down into many sections with smaller sections.

  • What’s in my journal
  • Why do I have a journal
  • How do I set up my journal

Part 1 – What’s in my Journal

So what’s in my journal? Glad you asked Smile, at a high level my journal contains checklists, goals, tasks, ideas, stories, quotes, notes and much more from virtually every aspect of my life. Yes, you read that right. Somehow, I have created a journal that allows me to store information about my work, family, faith, personal development and everything in between into a single journal.

My journal is a checklist, it keeps me on track and focused on specific goals. It’s also a diary. I keep track of successes and failures. I track stories of friends and families. I also include contacts that I meet, mistakes that I make, processes that I feel can be improved.

In the back of my journal I have an area called Brain Dump, this is where I dump ideas and thoughts and to do list items that pop in my head. I also have an index, organized categorically, that helps me quickly find items stored in my journal, this is the latest addition to my journal method and one I’m very excited about.

Part 2 – So why do I journal?

I journal because I want to remember, I want learn, I want to grow and improve and become better in virtually every aspect of my life.

I want to remember stories of my children growing up, good times that I have with family and friends. I want to remember people I meet, I want to remember details that they shared with me, I want to remember the sad times and the tough times so I can appreciate the good times and use persevere through difficult times.

I want to learn, grow, improve, and become better in virtually every aspect of my life. The journal gives me an opportunity to record a written record of my life. If I have successes I record those so I can try to duplicate those efforts, likewise if I experience failure or make mistakes then I record those so I can use my past as a learning experience to grow from.

Jim Rohn encourages journaling for many reasons, he especially encourages one to record their mistakes:

“Mistakes in judgement are nothing to be ashamed of, surely most of our personal growth comes as a result of our errors. But what is truly unforgivable is to make the same mistake twice, every mistake has its’ own price tag. But the most costly error anyone can make is an error unlearned and often repeated.” – Jim Rohn

The process of reflection and refinement

I have found the most valuable part of keeping a journal is not recording a history of events, no, I have found that the most valuable part of keeping a journal is going back over a day, a week, a month and reflecting on those events. I reflect on mistakes and remember what those mistakes felt like and try to make sure that I make the necessary changes to not repeat those mistakes. I reflect on my successes and look for ways to improve those processes, I reflect on my failures and, likewise, improve the process to verify those failures don’t occur again.

This is an eye-opening process and one that has helped me to evaluate and redefine even the most mundane of activities on a weekly basis.

  • I no longer take coffee or water on the plane. Having these uncovered drinks disrupts the process of doing work and so I opt for always carrying water on the plane with me.
  • I no longer turn down the air when I enter a hotel room. I have discovered that I personally am much less motivated to get work done when the hotel room is cold!
  • I have removed all games from my phone.
  • I schedule high-priority tasks during high-energy parts of my day and I schedule meetings at lower, less efficient parts of my day.
  • I now regularly sit in the back of the car when the family is commuting across town, this allows significant time with the kids.

This is a tiny list of modifications I have made in my day to day life and are literally items that came to mind as I was writing this blog. I have a lot of other things that are being recalled to memory at this very moment, the process of reflection can not be underestimated!

Part 3 – The Setup!

I’m very excited about the way I set up and configure my journal, the best way to see this is by watching, take a look at the following video:

https://youtu.be/9cpjLzqnOME

2 things you need to know about Power BI Security

/ Mitchell Pearson / Leave a comment

In this blog post, I want to quickly discuss 2 things you need to know about Power BI Security as it relates to the Power BI Service. Many of the clients that I visit and perform training for are just getting into an implementation of Power BI and are not aware of these two items. I will probably come back and update this list as there are a couple more items I want to add, but those will take slightly more explanation! Smile

Publish to Web should be disabled

The publish to web feature provides an Iframe that allows a user to take a report and then embed that anywhere an Iframe can be accepted. The problem with this is that now the report is 100% security free and anyone who has access to the report has access to everything in it.

If you choose not to disable this feature it should, at the very least, be limited to specific people within the organization who have received proper training. To disable or manage this feature you must perform the following steps:

  • You must be a Power BI Administrator
  • Next, select Admin Portal from the settings wheel found in the Power BI Service.

image

  • From the admin portal, select Tenant Settings and then expand Publish to Web.
  • Click on the button to disable Publish to Web feature.

image

Direct Query to a relational database is not a RLS enabled data source

Many users, especially users on later versions of SQL Server, assume incorrectly that a direct query connection to SQL Server will use the security restraints applied at the database level. Unfortunately, this just isn’t the case.

Any user connecting to a report with Direct Query is using the stored credentials in the enterprise data gateway and therefore will have access to whatever the data gateway has access to. So if you have enabled row level security on the tables for your database, this is not going to work with direct query.

However, you can and should add row level security to your power bi data model (pbix) file. This row level security will work and limit access as it is intended!

Not sure how to set up Row Level Security in Power BI? Thanks ok! Click Here

Download and unzip files using R in Power BI

/ Mitchell Pearson / Leave a comment

In a previous blog, I wrote about how you could use R to unzip files. Well, that raises the question, can I download a zip file and then unzip it using R?

You can view the previous blog post on how to unzip files here.

Update: YouTube video can be found at the bottom of this post!

Download files using R

In general, downloading and processing a file can be done in Power BI very easily using “web” as the data source and you never have to worry about using R. However, if you need to do something more complex then R can be very helpful. For example, you may want to download a zip file, unzip it, and then process it.

In this example, I am going to download a zip file that contains names by state, this zip file is stored in the following location:

https://www.ssa.gov/oact/babynames/state/namesbystate.zip

This zip file has data for each of the different states, for this example I only want to look at the data for Florida. So we are going to unzip the file and only extract the data from the single file that we need data from.

Let’s take a look at the code in R Studio and break it down:

image

  1. First, I want to store the destination location and filename in a variable, this is done in lines 1-2.
  2. Next, I want to download the zip file and store it locally, this is done in lines 4-6.
  3. Thirdly, now the file will be unzipped and the file FL.TXT will be stored in the variable FLnames, lines 8-9.
  4. Lastly, we will read the text file and store it in the variable babynames.

The only thing we added in this example that was not in the previous blog post was the ability of downloading the code. If you, missed the previous blog and want to know how to import the data into Power BI, you can check that our here.

 

Thanks, for checking out my blog!

Using R to unzip and process files into Power BI

/ Mitchell Pearson / 4 Comments

There are a lot of blogs and videos out there on how to use R to enhance the visualization layer of Power BI. In this small series, I want to focus on how R can be used to improve the data importing and data cleansing capabilities of Power BI. Previously, I wrote how you could use the R language in Power BI to export data out to a CSV file. You can view that blog by following this link.

Update: Video embedded at bottom of blog post.

Although, I personally am not an expert when it comes to R, I’m learning more each day, it’s one of the topics I enjoy teaching the most in our Advanced Power BI Boot Camp.

Now it’s time for the required Pirate Picture: ARRRRRRRR. By the way, this image is from a tabletop game I like playing called Merchants & Marauders!!

Image result for pirate arrrrr

Getting Started with R in Power BI

To work with R you will need to have R installed. I use Microsoft R open, link provided below. You can optionally download R studio as well. Both of these are free downloads.

Required: Download the Microsoft R Open

Optional: Download R Studio

Discloser: At the time of this blog post, there is no built-in intellisense in Power BI to help write and debug R. Therefore, I use R Studio to write and test my code! R Studio is not a requirement, it is, in my experience, just very helpful.

UNZ and READ.CSV in R

The problem I want to solve in this blog post is how to unzip a file with Power BI and then process that file. Because I come from an enterprise BI background my first thought is to approach this problem with a tool like SSIS or PowerShell. However, there is an easier way, why else would we be here! Smile

I want to show you how you can use R to unzip and then extract data from a file all in one step. By the way, it’s really, really easy!

Let’s start off by looking at the code, this is all of it!:

image

  1. On line one, we are declaring a variable called data, this variable will store the results of our expression.
  2. On line two we are using read.csv to extract the results from the csv file, Customer Sales, and these results are stored in the variable from step one, please note that this process can not occur until after the file has been unzipped.
  3. On lines three and four we are using UNZ to unzip the file Customer Sale.zip and then return the file Customer Sales.csv.

And that’s it!

Wait, wait, wait…. What is line seven? Well, line seven allows me to test the results in R Studio. This is not needed for the importing data into Power BI Desktop. Oh, that reminds me, we need to import the data into Power BI Desktop!

Importing data using R in Power BI Desktop

As you are already aware, there are a lot of data sources available in Power BI, it shouldn’t surprise you that one of those data sources is R. Now let’s walk through how we can quickly import data from an R script into Power BI Desktop.

From the home ribbon in Power BI, click Get Data –> Other –> R Script. See screenshot below:

image

This will launch the R Script editor. As I previously mentioned, this script editor is simply a text box. Now we simply paste in our code from R studio, or if you happen to be an R aficionado you can just type your code in this box, as seen below:

image

Once we click ok, the Navigator window will open. Since we only declared one variable in our script, only one option will be provided. From here, everything is exactly the same as any other data source.

4_Navigator Window

Ok, now we’re done! I hope you enjoyed this blog post and I look forward to writing more, until next time.