Enjoy the Podcast?
Useful for Develops and DBA those who want to know what is Power BI and How we can utilize various features.
Also, session will be useful for anyone who wants learn Power BI from basic.
In this session, We will walk through various features of Power BI, How Power BI can transform your company’s data into rich visuals and Easy yet powerful Analytics solutions for your whole organization.
At end of session with following Power BI Dashboard example
- sp_Blitz in Dashboard
- SQL Server Info Dashboard
- Twitter Dashboard
- World Dashboard
Most important takeaways from session –
- You will be learning basics of Power BI with the additional perk of analyzing sp_Blitz in Power BI.
- Various features of Power BI making you from ZERO to HERO
- After this session, you will be able to analyze data into Power BI
Why I Want to Present This Session:
Business Intelligence is my one sided love but knowledge sharing about Business intelligence technology is my passion
Presentation Slides & Download links –
The 2017 Data Professional Salary Survey Dashboard http://bit.do/salary2017pbix
Brent Ozar: In this session at GroupBy, Vishal is going to talk about Power BI, something near and dear to my heart because I want to see how it works, especially with all the data that I need to analyze. So, Vishal, take it away.
Vishal Pawar: Thank you, Brent, and I would like to welcome everyone to be on this session. Hopefully in the next one hour you’ll be surely downloading the Power BI and start analyzing your data. That’s my promise to you.
Let’s start with the introductory slide starting with about me. I will just make this slide open and I will not discuss anything but, just my email, firstname.lastname@example.org. Twitter the best place to reach out and you can send the queries, questions, anything related to Power BI. We do have a very huge support of global Power BI user group. There more than 22 leaders who are supporting that user group, so make sure if you have any questions related to Power BI, tweet about it and I will get back to you.
Moving over to my demo. It might look very huge but believe me, most of the points will get covered in the minutes. So, what I have done is I have this little agenda over here. We might refer or we might not refer to this but it will be always on the screen so you know that which slide or which thing we are doing in the Power BI.
So, moving over to that, why Power BI? To answer that, first of all we have to go a little bit a step back. Actually, there was a first wave, that is technical wave, what we call. It was around 2006, 2008 where IT was providing reports for the end user. IT was writing everything and giving the reports to the end user. Then there was second wave, which is the self-service BI where analysts and end users were trying to reach out and see how they can visualize. Right now, we are into the third wave of BI. That is end user BI. That means BI for everyone. So BI is not that you put the request to what you need to see in the report, it’s now that you can do your own analysis if you have access to the data. So how Power BI is fitting into this perfectly third wave of the BI? That is BI for everyone.
So first of all, let’s see what are the challenges of previous BIs. So BI, we need to have an end-to-end view. The data was often, like on the disparate location, there was different sources other than the SQL. There was the CSV connectors, text file, those data sources, how we can bring it together? Consider end user is doing like entire integration of CSV text file very easily and seamlessly. So that was the main BI challenge that Power BI solved. Multiple data sources, yes, you can fetch the data from different data sources such as Facebook, Twitter, and relate them together and, you can do your analysis. So that also are challenges solved by the Power BI. And, right data for the right user at the right time. This is the main code word Power BI gives you all the time.
Let’s see what was the old approach to solve any BI problem? So we have the ETL. ETL process was—you know that we developed the SSIS package to solve the ETL problem. We have data warehouse SSAS where our cubes are sorting and we process the cubes to get the data into other SSRS report. Then we hosted that particular SSRS report into SharePoint to get it access to the enterprise. We do have a little background of in the [inaudible] data mining.
So, that was our old approach where we have ETL, analytics, reporting. What is the new approach that people are going to take going further? Whatever we are going to do in SSIS is exactly you can do in Power Query, which means you can combine the different data sources, you can massage it, you can transform. In fact, you can add expressions. You can do everything. SSAS is nothing but the power pivot side of it. SSRS is power view and power map. SharePoint is nothing but power query with the sharing features and you have the predicting and mining built-in also. And, you also have the Q&A. You can ask natural questions to the Power BI and get the data.
So, this is the entire line that Power BI has. You don’t have to remember anything of this power query, power view, power—it was very confusing during the first phase of the Power BI launch. Now, it is everything known as the Power BI. So just remember the word Power BI and you can do entire of this thing inside the Power BI. So, that was the new and the current Microsoft BI approach that you can definitely try out.
Let’s move on to our main slide that is what is Power BI? Power BI is actually a suite of business analytics tools, which you can analyze the data and share your insight. You can monitor your business, you can get answer pretty much quickly, you can monitor your business, you can get answers pretty much quickly with all the rich dashboards. Most important thing is Power BI is available on all the devices. That is Android, Apple. It is available on all the devices.
So, this is why Power BI? I have given part one and the next one is the part two, part three, part four… I’m just kidding. It’s not like that. Key benefits. The main key benefits for learning Power BI, you have pre-built dashboards and reports. So if you don’t know anything about this product, you can just go online. You can get the pre-built dashboard and you can learn those and you can apply those exact things what we’re looking into pre-built dashboard in your own dashboard.
You can learn by your own self very easily and all the dashboards are live, real-time dashboards and updates you will get. This is definitely going to secure because it has Office 365 single sign on, how you are logging into OneDrive. How you log in to your Skype account online. Office 365. Similarly, you’re going to log in on Power BI online account. So these are the main key benefits.
The next slide is why Power BI Part 30. So, if you want to learn about part 2 to 29, you have to visit the Power BI site. That is powerbi.com, that is powerbi.microsoft.com and you will learn probably many, many things. You will definitely love it. The most important I would like to highlight here, it has more than 50 connections, which are very popular business like Project Online, like Dynamic CRM, Salesforce. So these connectors are right away available for you. You don’t have to do anything. You just sign in your credentials and you will get the data on the fly.
So who are the most important users of the Power BI? According to me, I will not read this at all, I believe that everyone should use Power BI. Why? Because it’s very easy, it’s very intuitive, and you will definitely like the rich dashboard and I know you want to share your dashboard with everyone so you will definitely—everyone will be use this Power BI for sure.
So, I feel that this slide, everyone should listen very quietly because this slide is going to get you enterprise platform of the Power BI. So starting with you have a Power BI desktop. So Power BI desktop is the standalone tool sitting inside your own laptop or computer, which will give you like Power BI desktop. It is a software where you can do analysis. You can publish that particular Power BI file into the Power BI service, which is the cloud platform.
Think of it like this, how OneDrive has files on your own local machine and how you can publish those one time and access from everywhere? Similarly, Power BI has its own extension that is PBIX, and you can publish those files into the Power BI service. Once you publish your file into the Power BI service, you can get it access through all the devices like mobile, tablets, or even your laptop, any browser. It almost supports all the browsers right now. It additionally also gives the gateway feature, which means you can not only connect to your own local files, you can connect to your organization data and that is also very secure, which is known as Power BI gateways.
In this enterprise platform, you will have more than 50 apps where you are definitely going to connect directly. You don’t need any extra connector or you don’t need to do anything. You can just go on Power BI service and get those 50 app directly connected, like QuickBooks. If you have QuickBooks accounts, you can go Power BI service right now and there is a dashboard ready for you. You just have to place your credentials over there and the dashboard is ready for you. So those 50 apps, you can directly connect from the Power BI service.
Moving over to the installation. I’ll just read the highlighted part that is it requires only two cores and 4 gigabytes of RAM. But believe me, it will be good if you have a higher RAM because the technology is in memory. So right now, I tried most of the things with the 16GB of RAM and it works pretty well with the 16GB of RAM. If it is you’re going to use in the production environment where there is a huge database, then you have to go to the next level, like 32 or 64GB of RAM.
So, there is a link I have provided where you can go and install. It’s fairly easy. Just do next, next, next as provided here, how you can access Power BI service. You can access through all of these browsers such as Microsoft Edge, Internet Explorer, as well as Chrome, almost every browser is listed over here.
Now, the most important thing that everyone is interested in, how much it will cost to me. So first of all, Power BI is free. So everyone can right now have their own Power BI account whether it is a Power BI desktop or a Power BI service. You can definitely have that on yours right now because it’s free. So the current data capacity is one GB per user for free, if you want the advance users then you can purchase just the $9.99 per month and you will get 10GB per user. Microsoft is trying to change this day by day, adding features over here. So it’s suitable to go this link whenever you want to know how much it will cost. So you can just go over this link and you will get to know what is the current situation.
So, what are the connectors that we will have in the Power BI? We have the file connectors, we have database connectors. We also have the online services such as SharePoint Online, Exchange, everything we have. So, before finishing this, let’s jump over to the microsoft.com and just see what are the features we have on the Microsoft PowerBI.com. So this is the main website, we will go here.
If want to sign in and have access the Power BI services, you will sign in here. If you want to download Power BI desktop, you will go here. You will click on Power BI desktop, and you will download here. It will just download the exe file and just do next, next. It is always preferred that always use the highest level of version because it will have a lot of features and Power BI is releasing this every month so whenever you are installed, make sure that you already know that you are working on the latest and greatest of the version.
Let’s start now our actual real demos. First of all, what is my demo dataset? So, in the afternoon, Brent discussed this with everyone that the data will be available and I was shocked because my demo data was also almost equal to the salary data but it was not that, with the various features like whether it is a part-time, full-time, everything was not there.
When I go into this website, I downloaded the raw data and as I was listening, I just created the Power BI dashboard for everyone, and believe me—see, this is just side work and just within 15 to 20 minutes, you are good to go with the dashboard. But, what dashboard I have created, I’m going to show you at the end so you can see the actual difference between what we are going to create and what I have created just within 15 to 20 minutes.
Let’s see what is this raw dataset first. This raw dataset definitely has the time stamp, what is your annual salary, country, primary dataset, primary database you are working on. Brent, you want to jump in you can definitely jump in about this data.
Brent Ozar: No, this is good.
Vishal Pawar: What is the job type. I really like the questions asked in this survey because it’s almost considered each and every aspect like what is your job title. Do you manage the staff or not. Years of doing the same this job. How many people are doing exactly the same job as you are doing. Because if you have this data, we can actually see—there is almost I think 2,700 records. And, if you have this how many, then it’s actually plus that you know you have exact that many amounts of records.
So, that means you are not analyzing one person, you are analyzing whole bunch of people who are exactly similar position, similar job, and how many database servers. This raw data definitely has more number of servers. You will know this very soon. I just learned at the beginning, the most important for getting the job because most of the job postings, they definitely mention this stuff. This job requires this much education level or degree, for sure. Some have even mentioned that they do have the certification so I like that there is a question related to that also.
Let’s go to Power BI and let’s see fetching this data. When you will first open the Power BI, you will first see the welcome screen. In the welcome screen, you will have the options get data, this is the welcome screen. You will have options of get data, recent data, so this is what I have used. The most important thing, whenever you are going to open the Power BI, make sure that you know what’s new because most of the things might be integrated what you are thinking right now. So what is the forum? You can go and click here and see what other questions. If you have any questions, you can definitely post into this forum as well.
Before getting the data, I will just explain what is the Power BI in short. So, you have this top menu where you have home, view, modeling, and the different types. We’ve already seen the file option. So, file option, you have new, open, save as. Remember the save as option has actually two options. One is to PBIX and the PBIT. What is this PBIT option? It’s nothing but the template. How you are going to share your dashboard without giving the data. So, if you have a dashboard, just create parameterized data source connection and do all your analysis.
You want to share that dashboard with your colleague but you don’t want to give the data, then you will save that particular Power BI dashboard with the PBIT and you will just give the name, and you will share that file with your colleague. He’s going to receive that file. He’s going to replace the parameter with his data source and, boom, he will see your dashboard in his computer in your format. This is actually very interesting. That’s why I showed you file save as option PBIT. It’s similar to how we have the PowerPoint execution format. Also, you have what’s the new, sign out. Make sure that whenever you are going to log in, first you will see a sign in. It will ask you your username and password, what you are going to use so you can use that.
Now, let’s go to the visualization plane. We have visualization plane in the right hand side, which is nothing but here. It has a chart on the top. It has the Format Painter fields as well as the formatting options over here and you can definitely play around with it and you will know that these are really interesting. Visualization is mainly supports with the chart formatting options. Let’s get the data, what we downloaded right now. So, demo final and this is the file, what we have. This is how we connect the data source. I will show you that one more time because it was very quick.
You have all the file connectors, CSV, XML, text, you have different databases right from Oracle, SAP, IBM, SAP Hana. Believe me, I have used most of it and it has worked flawlessly. Microsoft is adding these data connectors day by day. So probably if you want to include any database, you can just go into Power BI Ideas and add your request. You will see that if there is a lot voting for that particular idea, they are going to implement for you. Then you have Azure, you have online services like SharePoint List, Google Analytics, GitHub, MailChimp. Yes, if your survey is on MailChimp, you just get the data from MailChimp and you can further analyze it. You can do that. And, you have other data sources.
Other data source is very interesting because the first one is the web. Consider that your data, you have seen some tables on the web. Most important, if you have data in Wikipedia, you can get that data right away using this web data source. It is very interesting if you have one dataset and you want to compare that particular dataset with your Wikipedia, just use a web data source, get your data from Wikipedia, and just relate it and massage it and just do your analysis. You have all data feed, all the RSS feeds and everything comes from the old data feed, you have that also. In fact, you have the blank query also.
Right now, what data we have is written Excel format so let’s get the data from Excel format. I just selected the file which we have downloaded. I have selected all the responses and here you can directly load. So this load will directly load your data into your field plane. Or, edit option will edit you in the query editor. So query editor is another world inside the Power BI where you can do everything like adding the column, adding the measure, and you can massage it, you can append the queries.
You see here all the columns listed on the top and Power BI is intelligent enough to apply a first few steps. For example, this was your source and Power BI applied this three step like navigation, where the data is navigated and it promoted the headers. You can see the row one is having the headers and it just promoted. By the way, this applied step is the—all you of what are doing inside the power query editor, it’s going to list it over here in the applied step. Next, it promoted the headers. After that, it changed the data type.
So, Power BI is intelligent enough to know your data type and it’s going to change your column data type, as per what it understood. For example, timestamp is already in day/time format, you can see here. These small little icons inside the column heading show you what is the data type. Here it’s timestamp. Here it’s numerical. Here it’s varchar. Here it’s again numeric. So it changes data type by its own. I just changed the column name like what is your annual salary, I just converted into salary. Then I just converted this into country. So likewise, I just changed the column names so that Power BI will easily understand and then I started doing analysis. So for now, I will just do close and apply and it is going to give me all the fields, which are listed on that particular dataset.
By the way, one thing you will like to always do in your dataset, is just add the row measure. So, actually I don’t know its—nobody mentioned it anywhere but what I will suggest, add the row measure, what it’s going to do is it’s going to give you a count of everything. So how to add a row measure? It’s very simple. Just click on the custom column.
After clicking custom column, just mention a row or you can say rows and just give the measure as one. So one will consider as that particular row count. I’m going to show you how. It’s interesting. Just having this row as a measure, you will get your most of the analytics done right away. So close and apply. Now we have the row measure. Here is our row measure. Just dragging that row measure and selecting the card.
So card data type is just giving the numbers in front of you. Let’s do the count. This count is nothing but number of people who have replied on their salary. So 2,800 people replied. Maybe this is raw data so let’s consider this as raw data. You can see how easily you can count all the things. For example, how many different type of job types we have. So you have three job types. And how many people replied for that particular job type? First of all, let’s convert that into tables so that it will be very easy to understand. It is already in table format and just add row onto the values. Here you can see. Let’s do row and the count. Here you can see the full-time employees are 2,498. So this is the feature of adding the rows as a measure. I hope you will definitely like it and you are going to use it in all of your…
Most of the time, some of the dashboard data will have it but some of them will not have it so you can add it as a measure. You can see here clearly how many full-time employees were there and how many independent, or freelancers, company owner.
Every chart you can have a formatting option. Let’s say I’m going to convert this into a normal bar chart. In the formatting, you can view a lot of options such as you can change your data labels, you can make data labels on. When you are going to make data labels on, let me little bit zoom it so that you can see it. I’m just going into formatting options and just making it off. By default, it is off. When you are going to make it on, you can see there is a count popping up inside your bar chart. Not only that, you can change the data colors, you can change these labels, you can add labels, you can decide what will be the border, you can decide what will be the background, what will be the x-axis, what will be the y-axis.
I think most of the people are liking Power BI because it really has high visualization features such as formatting, such as copy/paste option. For example, if you have another chart, you can do copy/paste. I just do Control+C, Control+V, it just copied the chart. Now, I want to see a similar kind of stuff but this time, I want to see according to—let’s say I want to see highest level of the degree. I will remove the job type. So you have the same chart, but you’re having the categories different. Now you have the number of counts. What are the associate level people, what are the bachelor level, doctorate, and all this stuff. So this is the formatting option.
Most important, like the future, is the custom visuals. So what is this customer visuals? Everyone is contributing to the custom visuals. You can just go this visuals.powerbi.com and you will see lot of already created custom visuals for you. Microsoft is adding these custom visuals day by day. When I was logged in last week the tachometer was not there. I know the pyramid, just like three, four weeks back someone had populated it. So you can use these custom visuals right away in the Power BI file.
For example, let’s say I want to use this liquid fill gauge inside my Power BI file. What I will do, I will go to this site, I will see what custom visual I like and then I’m going to download the visual [inaudible]. And by the way, if you want to learn any visual you can definitely download the sample and learn it.
Brent Ozar: Are those all free or do they cost anything?
Vishal Pawar: Those are absolutely free.
Brent Ozar: Cool.
Vishal Pawar: So I have this liquid gauge feature. What I’m going to do, I’m going to put this on my custom visual folder. By the way, it can sit in anywhere. How you are going to import that? Very simple. You see a little three dots over here. Import from file. Just click on it. Import the custom visual and then navigate to your visual that is liquid fill gauge. Import custom visual successfully. You can see here.
Now, let’s try to convert one. Here you go. This is how easy to import all the visuals. You can definitely play with all of them. Believe me, some of the visuals I feel that they were very costly, like this SandDance.
Brent Ozar: Yeah.
Vishal Pawar: Microsoft was working on the SandDance maybe eight, nine months they were working and they just release it for free. If you see how SandDance works, it’s very visual. You can definitely if there is a conference and you want to showcase some of the data, the SandDance will just make people wow. Believe me. And you have this little WordCloud. If you just download it, it’s going to display according to the value of the word. For example, here social is highest count of the word inside your data.
So these visuals take the Power BI into next stage, and you can make your own visuals. For example, if you have anything in your mind that you want to develop, there are people—if you know any C# developer, just you know that is your teammate because every visual ad needs D3 support and D3 as your technical background. You can develop that visual. There are demo videos even by Microsoft people how you can develop those visuals.
Most important is info graphics. It solves the most of the people’s problem because creating info graphics in Salesforce was very big pain because first you have to analyze the data, then you have to give that analysis to your designer, which doesn’t know anything about the data. Then you have to make him understand that this is what we want, this is the size, this is the pie chart. But, with the info graphic designer, you can design a high level of info graphics using the Power BI itself. Okay, I’m going to stick my agenda because I know once I talk about one feature I get lost about it.
Brent Ozar: Yeah, a couple of people in Slack are like, “We should have him design this. Design this.” I’m like, “Hold on, let’s go back to what he’s supposed to do.”
Vishal Pawar: Sure. And because I know there are two people in our Power BI global community, they are ready to develop anything. We were discussing just few visuals one day and those people come up within three days just posted it over here and you can download and you can use. It is that fast.
Brent Ozar: Very cool.
Vishal Pawar: So moving over to our next slide. What I’m going to do is now, I’m going to open Power BI dashboard file, which I have created in today’s afternoon by just looking at the raw data on the Brent Ozar website.
Brent Ozar: Woohoo.
Vishal Pawar: I just name it also job analysis on Brent Ozar data. And, believe it, I feel that this is just the work of 20-25 minutes. There are people who can come up with a better design in less amount of time.
This is the main page and this is just the stats. In Power BI, you have these pages. You can have as many pages, no problem. Every page uses a meaning. It will be good. It will be very easy to navigate when it is going to publish on the Power BI website. First of all, I always go with the stats first and then search and then whatever you want to do you can do. Because first, what the people are most interested in, what are the stats. What are the facts in my database? So facts are nothing but just accounts like what is the total response time? Let me click on View Auto Recovery. I am going to keep my fingers crossed.
So for my total responses, there are 2,800 total responses. How many countries where those responses are from? So there are total 87 countries. Let me see what it’s doing. It’s loading from data model. Okay. I accidentally closed the last testing Power BI document so it is asking me whether you want to save that or not. That means it’s really intelligent, right? So this is really good. So total responses. How many distinct countries? In the raw data, we have responses from 66 countries. What is the average week hours? That is 43.23. And how did I know this is average week hours? It’s very simple. I will showcase over here in the new page. I’m going to expand this field.
So where is average work hour? So this is the average work hour and let’s use the card. This is the card. It is right now showing the sum. Let’s put into average. And this is the average. I didn’t run anything. So this is how I put the average. By the way, there are total 20 different job titles on that raw data. Brent, I want to ask you one thing. This raw data is actual raw created by manual stuff or there is some live data also inside that Excel?
Brent Ozar: It’s interesting. Originally, we let people type in their own job titles but it is just coming straight out of the survey. We overwrote some of them because they put in things that just no other human being would have that job title. Like some people with CIO, some people with CTO, we just collapsed those into manager. One person put in like a million-dollar salary and I’m like, “I don’t think you’re really making a million dollars.”
Vishal Pawar: Actually, that’s why I asked because if the raw data would have been—it would have taken time to make that clear, because the data was in really good format, I was able to create it pretty quickly.
Brent Ozar: I only gave them so many choices. I gave them so few choices that they had to pick things.
Vishal Pawar: By the way, this is the work from home option. You can see hardly few people are getting option for work from home. None or less than one day.
Brent Ozar: More than half.
Vishal Pawar: I was shocked by looking at that. Maybe this is raw data but surely this was [inaudible] because how the market is going, this count is definitely going to be less for sure.
Brent Ozar: I thought it would be more because there’s still so many people who don’t get the chance to work from home and they just have been working in the same company for forever.
Vishal Pawar: That’s true. I would like to, you know, every data has its own value. If I would like to give a value to that particular data, then this is how I will do it. This data together holds 254 million dollars’ worth because that’s the totality of the people who have taken the survey.
Brent Ozar: That’s true.
Vishal Pawar: The total database experience of these people is 40,000 years of experience. Believe me, if we are going to put all those 2,000 people in one company, they’re definitely going to like take NASA on.
Brent Ozar: Well, it gives me new hope for like when we go to the PASS summit and you’re surrounded by 3,000 people. Suddenly, if you think about it in terms of how many years of experience they have and how much they make, it’s huge.
Vishal Pawar: Yeah, that’s true. If you see most of them having a DBA background. How much time it took me to give this little formatting? Let’s see in one page. So what I’ll do is I’m going to replicate one of the table format. Table format is nothing but the normal table structure. Let’s say get the job title, get the rows, and row actually take the count. So I have taken the count. Then go to the field and here you have the table style as a formatting option. So what you are going to do here, just give the bold headers and your header is all formatted, and just give a little border and it is formatted.
Now, what’s going to happen? You have another option that is format painting. Believe me, Format Painter solves lot of the formatting complexity. For example, I have another employee sector and I want to know what is the rows. Now, I want to get this table also exactly into this format, just use Format Painter. Format Painter, just paste it here. Oh, that was not in the same format. Okay, let’s do another one. So this is into table format, let’s make this also in table format. Make sure that when you are using Format Painter, both the things are in the same type. I don’t know why it doesn’t copy, but most of the time it did copy.
Brent Ozar: Live demos are always like that.
Vishal Pawar: Let me try one more time… It’s not copying. No problem. But usually, if you see, I have just created one and just used Format Painter and all other visualizations and it gives me exact look and feel for all the visuals. Then, once you have the stats, stats will—actually the first look on your data. So this is your first look. How many education degrees we have? You have 1,400 bachelors. You have 19 PhD people. So these stats are first glance and then your second slide should be around search.
Search is nothing but adding all the master data into the filters. For example, highest degree, employee sector. According to data, these are the master dataset where you can just add a filter and you want to get the count according to that. For example, I want to know how many bachelors are there in my data. So I will just filter in bachelors and all the data will get filtered, which means the bachelors are averagely working on 43 hours. The total different job types of the bachelors are 18. The total responses of the bachelors were 1,479. If you want to further, like just selecting the bachelors, and you want to see how many bachelors are there, those are having a computer degree, you will go in this computer degree filter and just select yes. If you see every salary is little bit bigger than not having the computer degree.
So these are the filters that you can definitely add into your dashboard which will make your user very easily to select and compare. For example, if you want to compare the doctors with the bachelor, what you are going to do, you are going to unselect all the filters. Then you can see the first bachelors and then unselect and select the doctors. So you can see total DBA experience, total salary, every salary is changing. Definitely you have option of select all option also, so where you can select all and get…
Now, how to add filters like this? Very easy. Just add a filter option over here and select any filter. Let’s say “highest education.” You got the highest education filter but if you go into the formatting options, here you have a selection control where you want to give people select all option and you want to give people also a comparison selection. For example, they can select two of them together. Or, if you don’t want to give a two comparison option, just have single selection option on where they can select just one. They cannot compare the two things together. This also you can give—just controlling your filter visual.
So, in the first slide we called job stats, then search, and then you have the graphical that is country-wise. You can compare your stats into the graphical format like how many responses were more from any specific country. You can see North America. From US, you get 1,800 responses, then the rest of the world. You can further compare the two countries together so if you want two countries together, first you want to make sure that you have selection control on and then you can compare the two countries together using this country-wise filter option. And, what is the average salary? Like, what is the average salary for the ba—?
If I want to have a same kind of filter option and I want to change just the middle visual, what I’m going to do? I’m going to select all the filters, like Control+A, Control+C, I’m going to go into next page, Control+V. All the filters are getting pasted over here. Just delete whatever you don’t want and you can add whatever chart you want inside this.
So I have this little pyramid chart which it just three weeks back got launched. So you can add here job type and then add your salary. You can add the average or median or variance. You can add anything. I just added salary by average and you can see compare how many salaries in all the stuff inside your pyramid visualization. This is how Power BI is very intuitive and very fruitful and you can do your own analytics very quickly.
I would like to give a last feature option, then I’m going to jump right away on Blitz dashboard and SQL Server dashboard. I know people are waiting for that. The group option is very interesting because sometimes when you have the data, like suppose I’m going to get salary groups. I have the server option. So how many servers you have. Let me see where is the server option… Number of servers your team manages.
So if I want to see the grouping, for example, people have responded on many things for example, let’s convert this into table and give the row count and don’t summarize. When you give the option “don’t summarize,” it will not summarize. Right now, you have this many people who tell you that this many number of servers they have used. But, if you want to analyze them in groups, for example, 13 people have responded that they have used one server. Like 54, they have one server. One or two people said two servers.
Not only that, you can just sort over here and you will see that most of the ten servers—ten is the number where 203 people have responded. Now, as an analyst or as a—like when I want to analyze this, I want to analyze in groups. Like how many people were there in 0 to 10 group? How many people were there in 10 to 20 group? Believe me, doing that in SQL Server query, it is difficult. Doing that in SQL Server Excel, it is difficult. Doing that in your SSRS, it is difficult. But, let’s see how easy you can do this inside the Power BI. What you want to do, just very simple thing, just click on your number of servers and just do new groups, okay? When you are going to click on the new groups, it’s going to ask what is the name of your group.
Brent Ozar: Wow.
Vishal Pawar: Yes, it’s very easy. Just say server groups. Okay? Once you write the server groups, click on the dropdown list and just select the group. What I’m going to do I’m going to select 0 to 20, let’s say 0 to 30 first, and then group. Once you group them, name them 0 to 30. Take the next group, then name them 41 to 80, whatever you have selected. So, once you select this kind of group, rest all will be in order. So I’m going to click on okay. Now, your group is ready.
I’m going to use already predefined one server group, which is 0 to 10, 100 to 200. This gets your visualization on to groups. Everybody wants to visualize the data into group rather than you having just sequential data. Once you have this group just use your rows, then you will use your counts. Then you will know what are the number of 0 to 10 users. There’s [inaudible]. You can see there are more than 400 people who are using a number of servers ranging from 40 to 100. And, you can definitely play with your groups. In fact, it’s very interesting. When I added the salary into this server groups. Let’s add the salary and let’s make the salary average.
Brent Ozar: Wow, yeah.
Vishal Pawar: Now, you can see the people who are using less servers, I mean those who mention that they are using more than 11,000 servers maybe, I’m not sure why they are saying that, but they’re having more salary. Those who are using less servers, they are having less salary. It’s not I am saying that we’ll go and add the servers but this is what the data is saying to us.
Brent Ozar: That’s neat. That’s cool. This is going to be so much fun.
Vishal Pawar: Yes. Let’s go one step further. If you want to know that, like let me see what is that formula. That formula is nothing but net salary or you can say what is the hourly salary for that particular user? So, we know that what US government recommended that average employee should have 2,087 hours if it is paying hourly. It should have that much if they are hiring you as a full-time employee.
So, we already know everyone’s salary. Let’s convert that into an hourly salary. How you are going to do? Very simple. You are going to click on new column and what you are going to do right now is DAX. Some people thought that DAX might be very difficult but it very easy. You don’t have to do anything. So I just selected salary divided by the number of hours that US government recommend and then hourly rate.
I know some people might not be agree with this analysis but that’s okay. You can create your own functions like this but this is just an example I want to use. Now, if I want to add that into here, hourly rate and then just make an average. Now you can see with the groups what the average hourly rate of that people by adding just a new column. Now, think of if there is a SSRS report and there is a request coming to you that, “Hey, this is not enough. I need one more column as average hourly salary,” then you might go to the SQL Server Reporting Service, you will add a column. Probably you might try the expression and then you will further publish the report. Some people, if the data is very huge they will add a SQL column in fact then they will pull that onto SQL queries into SSRS report. But, Power BI introduces that all the functionality and you can add the columns, you can add the measures very easily.
Okay, so jumping on to my actual main [inaudible] dashboard that is SQL Blitz. For that, actually I have to go to my different machine. Okay, so this is my other machine because this is really a good machine and the data is real huge because I have also integrated Stack Overflow. Thanks to Brent for making Stack Overflow database so easy. In fact, within one or two hours because my experience was very less, downloading and integrating was that so easy and right away I have the entire Stack Overflow database. You can go on his blog, just follow a few of the steps, you’ll have that big beast of database sitting on your machine just for your research. Thanks, Brent for that.
Brent Ozar: You bet. Thanks to guys at Stack Overflow for making it public. That’s just absolutely amazing.
Vishal Pawar: Yeah. So, I’m just opening one SSMS instance. First of all, I will just run few queries that I have used inside this dashboard. Might be you will write a better query than this because I know there are lot of DBAs who have their folder sitting on their machine which they use all the time. Every DBA has their own toolkit. Just use those toolkits and create your own dashboard for what you need.
Now, I am just going to pull the server info. What this query is going to do, it’s just going to pull all the server-related information for you and it’s going to show up, like what is the machine name. What is the binaries, analog part. What is the startup user. Everything related to your server whether it is collated. What RAM it is having. What SQL server version it is using. Everything related to server instance.
Similarly, I have a bunch of queries sitting on this particular dashboard so I have listed all those queries like database name, server info, and the most important was sp_Blitz and maybe, I’m not the best person to talk about it. Maybe, Brent, you can just explain what is sp_Blitz for next two, three minutes. That will be useful for people.
Brent Ozar: Yeah, sure. So I got so frustrated as a database administrator because people kept giving me servers with all kinds of surprises in them. They had left things turned on. They had triggers in there, strange settings. So I wrote sp_Blitz to analyze the health of my SQL Servers and alert me on things that would surprise me later. Plus, what it ended up giving me over time was something I could show to bosses and say, “Here’s what it looked like at the beginning of the year. Here’s what it looks like today, that there’s all these less critical problems now than there was when I back started the year.” It’s totally free. It’s open source. If you go to brentozar.com/blitz, you can go download it and run it on your servers to understand how unhealthy they are.
Vishal Pawar: This First Responder Kit is million-dollar worth.
Brent Ozar: Thanks, glad you like it. We put a ton of work into that.
Vishal Pawar: I was trying to analyze what you did in this sp_Blitz and after two, three hours I realized that nowhere in this life I’m going to read that.
Brent Ozar: What’s amazing is because it’s open source, I’ll get code contributions from people and I almost want to apologize to them for them having to read my code. Erik does an amazing job on sp_BlitzCache, which is really hard to read. That one is really tough.
Vishal Pawar: All right. Well, thanks Brent for that awesome summary of sp_Blitz. So this is Brent’s sp_Blitz. If I’m playing with your folders here, please bear with me, Brent, for a minute.
Brent Ozar: Yeah, yeah.
Vishal Pawar: What I did is I’m just going to command this section because I want to create a dashboard which can work on any server. I don’t have to go and write the [inaudible] on that particular server. Instead, I will write this script inside the dashboard and I will just change the parameter to that particular server instance and I will get the information right for me in my dashboard.
If I execute this, you will see that you will get the information here as well. So I just executed this. It has all the sample databases like AdventureWorks 2008 to 16, all the Northwind as well as the WideWorldImporters, which is 2016 [inaudible], as well as my favorite and thanks for Brent making it available for download, that is Stack Overflow. Stack Overflow is also sitting on this server right now.
So, this is the output you get when you just fire the query, just [inaudible] this. What I did is I did the exact same thing inside my dashboard. In the sp_Blitz query window, let’s go into edit query window. By the way, I’m going to make available for this dashboard and everything onto my session link, what Brent is giving all the time. So Blitz, edit query, you can see here source. I just clicked on the source. You can see I just copy/pasted here and I just clicked on okay.
I just made this thing as a parameter that is SQL Server name so that next time if I’m just—actually no, it will be very smart and just go with your Power BI file and tell, “Hey, give me your server name.” Okay, once you have the server name you’ll tell this is what is happening. But don’t forget to mention Brent’s name because when you are going to explain to him all the queries, all the links are going to go into the Brent site.
Brent Ozar: That’s true.
Vishal Pawar: And you cannot avoid that at all. So I added that. You will just refresh the dataset and close and apply. So little bit of other dataset also I will try to explain very quickly because we are really running out of time. One server info what I show I added here.
After adding all my queries, what I did is I make it little consistent. What consistency I made is I made a database name as a name everywhere so that Microsoft Power BI can understand very intelligent enough to make the relationship pretty quickly. For example, if you want to jump from the database backup file to the current process and compare both of them, that you can do if your database are already related. If you are finding that your database datasets are not related, you can relate them. How? That I will show you right away.
So, you have this [inaudible] one report. Another one is the data, and third one is the report, so relationship. So when you click on this relationship, you will see how you’re going to see in SQL diagram, you will see here all the dataset tying to each other. If suppose you want to link one to another, you can just pick that and drag in to another. I’m not going drag in because it will mess up the data model. So you can do that.
Mostly, if your names are same, BI is intelligent enough to relate them. Sometimes it does link wrong things, but if you can correct those, it will be very good. You can see once you click here, you can see how sp_helpdb dataset db ID is getting connected with database. In fact, I didn’t map them. They themselves map it intelligently. So if it is not linked, you can just come over here, just drag that particular column into another dataset, boom, your relationships are done and you’re ready to do a cross dataset analysis also.
Now, next thing, some of the dataset like sp_Blitz has URL. So if you want to know that your Power BI should read that particular dataset as a URL, you have to tell that, “Make this column as a URL.” What you are going to do, you are going to just select that particular column. You are going to go into modeling and you have different types of data categories. So you can see here image and all those stuff, I make it web URL because this is web URL so that when you are going to develop anything so if the visualization will know that this is the link, that it has to go through that link. So I’m going to close this. I’m going to showcase the report. This is what is sp_Blitz dashboard. Let me first cover the overview and I try to make the color combination same as the Brent Ozar [inaudible PLF] and if anything is wrong…
Brent Ozar: Look at that.
Vishal Pawar: So how many total Blitz counts I have in my database? That is 210. How many distincts are there? 26. How many distinct priorities I have? So those are the ten and the groups, eight. In fact, if you are very much interested into any of the finding, you can click and make sure that you can get that data.
For example, let’s see the Stack Overflow. When I’m clicking on Stack Overflow, you can see I have not performed the backup because it’s already close to 100 GB database. I don’t want to back up that. So all the database [inaudible]. Probably Brent, you might be able to guess this because you are very much familiar with this standard, and in fact, you can filter on the top according to priority. For example, if I want to solve first the high priority one only, then I will do select all and then I’m going unfilter it and then select whatever the important. Let’s say priority one is most important, then I will select priority one. Then I will backup only those very easily.
So let’s see what is there in deep dive. So for example, sometimes your dataset has a very huge dataset. Let’s say sp_Blitz definitely use really good output. Frankly, very easy to understand. But on top of that, if you want to search only groups related to performance, you have one visualization, which is search, which is also a custom visual. You can just download, import, and you will get this here.
I imported this smart filter visual. This is group. I can just type over here, like performance. It will filter this particular dataset according to whatever it has, so it has no backups and restores. So let me filter according to backup. Even as I’m typing, it’s just filtering the data, your dataset, and now I want to know backup not performed recently. I will just select that and all the dataset will get refreshed with that particular search filter. Isn’t it very fast? Isn’t it very easy?
Brent Ozar: Yeah, that’s really slick.
Vishal Pawar: The search functions make Power BI very easily searchable. If I want to search anything, I will just add this search type of buttons in a search type of visualizations everywhere and people can come here and search their specific work or their specific visualization.
With respect to this all, we also have the SQL Server information dashboard where you will get all your SQL Server information on your just sitting dashboard. What is your memory size? CPU uses? What is the free disk space you have? So I have only one drive sitting on that machine. It has 39% free and 60% full because of the Stack Overflow but I really like it and I’m going to keep Stack Overflow at least for next two years.
To get all the information, I have to dig. There were a lot of SQL people who helped me to get their queries and to make this, so this is the part where I had little bit of tough time getting the RAM but it was very easy with a few queries. So this is the RAM what I have right now as well as the memory details, everything related to server.
Consider now we already see there are people who have 100 and 200 servers, think of like you have filter for each server, how it will be very easy to just store this information in the Power BI and filter your servers in the Power BI dashboard. And you don’t know everything about it. Isn’t it very interesting? Like, if you have 100 servers, make sure that all are getting connected and you will get all the information using open query and all the 100 server information, with all this information sitting right for you in the Power BI dashboard.
Brent Ozar: Very slick.
Vishal Pawar: I know people always use the current process which is the [inaudible] right now, nothing is running on my server so that’s why it’s not showing anything, but if you have any running process you can just come over here and see over here. This Power BI will take you to the next smart person and take you to the next stage where you can be very smart. You can be very visualized. You can showcase your work in visualized format. So, this is what is SQL Server dashboard information and Blitz.
One more thing I would like to show here and that will be my last demo. Because there was one requirement where people wanted to see what are the Twitter live feed for that particular event on the projector, which means real time. They want to see the Twitter feed, count, everything. So what I did for that is, I’ll just first explain. By the way, this is the oldest method. New method, we’re using Azure. You will have the Twitter integration right away, very easy than this.
What I did, I used Twitter and there is IFTTT service which is very freely available. And, what does it will do? Any Twitter hashtag for that specific event, it is going to put the one row into Google Sheet. Okay? Then, what I did is I interfaced this Google Sheet to the web interface. I mean I published this as a web and Power BI can take that as a source and show all the different data on the projector.
I will open up that dashboard and this Twitter dashboard also, I have make it available for download for everyone on my link. I’m just opening that with the live dashboard. Right now, what I did is I’ve just taken Power BI as a hashtag and I’m just taking all the tweets happening related to Power BI. I’m going to refresh it so that it will refresh the latest tweet data. You can see January 13, 4:00 p.m., someone tweeted about it. So, it’s just refreshing, it’s just going into Google Sheets. So you can see, 6:39, someone twitted about pro-licensing difference kind of…
So, this dashboard was sitting in the projector for that specific event. Everyone was so interested. They were replying, they were seeing their tweets. I mean, don’t think just Power BI as an analytics tool. Think how you can utilize that for your own research. Right now, I’m very new to the stock market. First thing came into my mind, how I can analyze all stock offers, any company, from last 30 years. Believe me, I was able to import. I was able to download all the whole data. Actually, the dashboard is not ready. That’s why I’m not showing. Maybe in near future I will show it in a different session. But, it was very easy and I’m getting more interested in the stock market. This is just another example how you can utilize Power BI into your life. So Brent, I am open for the questions now.
Brent Ozar: Perfect. Several people have said they want to play around with this. They’re very excited to play around with these. J.D. says, “If you have a Power BI dashboard of whatever variety that’s connected to some data, can you publish the dashboard without giving them access to the underlying data?” Giving them just the dashboard but not the data underneath.
Vishal Pawar: Yes, and let’s do that. Actually I forget that so let’s do that. This is the job analysis, Brent Ozar PBIX file. You will just open it and I am going to publish it into my session. So you will open the GroupBy and then I will open up my session. It will be very quick, believe me.
Brent Ozar: Sure.
Vishal Pawar: So here you have the publish option. You will publish to web. This is getting published onto the web. Once it is going to publish onto the Power BI service, we can see that onto the web service and I will cover that little bit of part. Okay, so it’s getting published. Meanwhile, I will just open up the blog. I already logged in, so it will very easy for me to just add that link.
So here is my WordPress. It’s published to the web service. Here you will get the link to access it on the web. So right now nobody has this link because I didn’t publish it to it. So you can see you will get the same kind of look and feel into the web feature also. Here, you have this option, file, and publish to the web, okay? So I’m going to clicking on publish to the web, create embedded code, publish. Once I will have this iFrame, I’m going to copy paste this iFrame and I’m going to paste into the text. I’m going to paste it, dashboard and, I didn’t share dataset. I’m just sharing the dashboard. So it’s getting updated. Now let’s see the preview.
Brent Ozar: I’m going in there, too.
Vishal Pawar: Actually, I don’t know why it’s not showing here.
Brent Ozar: I don’t think I have iFrames turned on on my mine. I can fix that pretty easy.
Vishal Pawar: No problem. You know what I will do? Let’s see, I have this link ready. What I am going to do is I’m going to quickly—I’m not good in HTML but, let’s see. I’m just pasting this here and I’m saving this file as test.html… Here is my Chrome file, I opened it and you can see the dashboard is opening. So, this is how people can access your dashboard without sharing your dataset with them. In fact, if you have role level security, they can see the data, whatever access they have. That also highly featured in the Power BI.
Brent Ozar: Nice. All right. J.D. says, “Can you still change your filters and all that?” When you’re looking on the web version, like can you change, click on things?
Vishal Pawar: Yeah, in the web version also you can definitely change your filters. For example, I’m here and I can go into the web. Here I can come and change the filters, yeah.
Brent Ozar: Okay, cool.
Vishal Pawar: Edit report. Yeah, see? The same functionality over here. So definitely you can change the filter. You’ve got all the features of Power BI desktop onto the web portal, so you can change it.
By the way, Power BI is integrated in SSRS so near future you will see using Power BI embedded how you can have very flexible reporting. In our previous version of SSRS, we just have those dropdowns and everybody knows how troubling those SSRS dropdowns were. You cannot change. You cannot format. You cannot do anything. But, using Power BI embedded, you can do everything. You can have very much search functionality, any app, like C# or webpage, anywhere.
Brent Ozar: Yeah, looks gorgeous. Well, thanks so much for presenting, Vishal. This is fantastic and I know people are going to be excited to play around with the Salary Survey data. So many people said they wanted to play around with those Excel files. J.D. says, “This is absolutely awesome. This solves a major problem and gets our BI analyst off our back. Thank you.” So thanks for volunteering to speak here. A big warm round of applause of Vishal.
Vishal Pawar: Thank you for the opportunity.
Brent Ozar: No, thank you. Adios, everybody.
Latest posts by Vishal Pawar (see all)
- Power BI Dashboard in an Hour with Various Examples - January 13, 2017