Power BI continues to receive a huge amount of buzz. You may find yourself wondering what all the fuss is about. Could Power BI be a potential solution for your organization’s self-service analytical and visualization needs?
A deep and flexible tool, Power BI continues to evolve with each release. Watch this on-demand webinar to get our insights into Power BI’s continual transformation. Learn how Power BI fits into Microsoft’s overall business intelligence framework, get an overview of what it does well (and where it has limitations) and see a demo some of its more prominent features.
- What is Power BI
- Components of Power BI
- How Power BI fits into the larger Microsoft BI stack
- Limitations of Power BI
Microsoft BI Architect
Shawn is well-versed across the entire Microsoft BI stack and its wide range of offerings, having built ETL, data warehouse, reporting and analysis solutions from the ground up. In his various development and architecture roles, he often serves as the client’s project manager and business analyst, partnering directly with their team to gather requirements and deliver insight.Read more
Greetings and welcome to the latest installment of the Senturus Knowledge Series. Today’s topic is Power BI: Beyond the Buzz. We will give you the straight talk on Microsoft’s data visualization tool for BI. Those of you who attend, many of these may recognize this from a few months ago, or about maybe about 6, 7, 8 months ago, but we’ve reprised it here and updated it with fresh content. So we’re very excited about this. First, a few housekeeping items. You can use the GoToWebinar control panel to you can either minimize or restore that.
We do keep all the microphones muted for to allow the speaker to hear clearly, but we do encourage you to submit questions via the question pane. We do try to respond to all the questions while the webinars in progress, however, if we’re unable to reply immediately, we will cover it via a written response document that we will post at
senturus.com, which brings us logically to the next slide, and the question that we get asked repeatedly throughout the presentation is kind of get a copy of the presentation. And the answer is absolutely, you can get it at senturus.com, at the link above there, senturus.com/resources. If you select the Resources tab, and then Resources Library where you can find the webinar.
And you will find a copy of the deck as well as the aforementioned completed questions log and video of the webinar. A few weeks after the presentation gets wrapped up and be sure to bookmark that resource library as it has tons of valuable content addressing a wide variety of business analytics topics all for free.
So our agenda for today, we’ll do some quick introductions, and then I’ll hand it over to our presenter to talk about Power BI, its context, provide some context, an overview of the product as well as demonstrations and then towards the end, we encourage you all to stick around. After a brief Senturus’s overview, we get into some great additional resources and the Q and A session.
So today I am pleased to be joined by Shawn Alpay, who’s a Business Intelligence architect here at Senturus.
He’s a BI developer with Business Acumen and a good bedside manner.
In addition to being a world-class musician, he’s able to definitely divide the bridge, the divide between technology and management being well versed across the entire Microsoft BI stack and its wide range of offerings. He’s built ETL data warehouses, reports, and analysis solutions from the ground up, both as a single resource and as part of a team. And in those various roles, he’s also a, he’s has also often served as the project manager and a business analyst, partnering directly with the business to gather requirements and deliver insights. So, he’s a true utility player. My name is Mike Weinhauer. I head up the Tableau Practice and I’m responsible for the development of Senturus Analytics connector and I’ll be your MC for today.
So, we always like to get a finger on the pulse of our audience today. So, I’ve got about 140 of you out there right now. We have a poll that we’d like to ask for your input on.
So, what elements of the Microsoft BI stack are being employed at your organization, and please select all that apply? So, do you use SQL server? Do you use analysis services and or report services, Power BI, to use the Power Stack power query power pivot power view? Or none of the above? So go ahead and get your votes in. And then we will close the poll out and share the results back to all of you.
So we’ve got about two thirds of you guys in there.
I’ll give this about 10, 15 more seconds or until we get about 80% aim.
I think I should do this in an auctioneer voice, but resist the urge so far. At about three fourths as gets your last second votes in here.
Alright, requested three quarters. I’m going to call it a day here. So, share the results back. So, full, you know, unsurprising, 80% of you use SQL Server and then a third or so SSIS and or SSRS, half using Power BI.
I guess, maybe not surprising, given the audience and the ubiquity of Microsoft. And then about a quarter using the Power stack and a full 7% was none of the above. So that’s interesting, great. Thank you for being interactive and providing input to us and so with that, I will hand the microphone over to mister LP. Shawn, the floor is yours.
Thank you very much, Mike. Hello, everyone. Thanks for attending.
It seems based on the poll that we have a wide variety of folks on this webinar, so I’m going to want to provide you as much of a context, an overview of why we’re here as possible. For some of you, this may be a retread, but it’s always good to revisit the background of what we’re doing before we dive into presentation of, of what it is we’re here to see. So, our agenda is really simple. What is Power BI? I’m going to tell you, I’m also going to show you, but, before we can get to that, I really want to dive into why Power BI, why does this product exist in the first place? Why are we all here to talk about it? And once we’ve gotten through that, I feel like we have set the stage properly for all the things it does.
We can talk through those components, and then, I can then show you how those components work. I feel like too often, we dive into a technology, and we say, what can it do, without truly understanding why it does what it does?
And, you know, we’ll also talk about some of the features that it has that are new, Some of the features that are upcoming, some of its limitations, and all of that can’t really need to look at that through the lens of, you know, how it’s come to be where it is.
So, I first want to jump back even a little bit farther, and talk about what the BI part of Power BI is, business intelligence, right? And I want to talk about the fact that there are two modes of business intelligence. And there’s a definition I want to share with you by a company called Gartner. And they call these two modes bimodal so bimodal BI as a practice of managing two separate, but coherent styles of work, one that’s focused on predictability, and the other on exploration.
When we’re talking about business intelligence, we’re talking about getting data and delivering insight. But there’s really two different channels of that kind of BI. One is going to be repeatable. It’s going to be linear, it’s what we might call Enterprise BI. That’s what they call, moat. One.
Mode two is a non-linear approach self-service. You’re exploring the data. And there’s a little graphic I want to show you here.
Mode one, we see the little gear on the bottom. It’s predictable, it’s repeatable. It’s like a canned report. It’s going to be the same all the time. It’s well understood. Very highly structured information.
Mode two is more exploratory. You’re experimenting, you’re coming up with new measures, new solutions. You’re solving new problems. And Gartner feels very strongly that a good BI platform, good ecosystem for a given organization, has to be able to solve for both of these well, and I think we’re all really familiar with mode one, in terms of delivering like, highly structured reports. But Gartner is really putting a stronger focus on mode two and its analysis of BI tools.
You may have heard of something called the Magic Quadrant, where they analyze given technologies in, in the ecosystem for that particular realm, and BI is included in that, but they’ve also started analyzing BI tools through the lens of self-service and non-linear applications, not just that enterprise BI. So, I encourage you to go look up the Magic Quadrant and look at the various tools out there. What you may notice when you look there, is that Power BI is, is, is a real leader in, in self-service BI.
And so we’re going to talk a little bit about the particular use case, an example of self-service BI, and how Power BI and Microsoft like to solve that problem.
Let’s present this use case here. Let’s say I’m an eager analyst in a small company, or maybe I’m in a small business unit in a large company. And let’s say I have some business data sitting in some flat files.
CSV is Excel or something like that, and I’m not going to bother IT with any of this yet, because maybe they have a lack of resources. Maybe there’s some politics at play. Maybe I questioned their competence, maybe all three. But in any case, I want to build a solution by myself without incorporating classic IT enterprise resources to get BI out the door.
So, if I’m goanna do that, I have a lot to figure out, how am I going to do a few things. one, how am I going to prepare that data, how am I going to get it out of where it is, in this case, flat files, but you can also imagine databases, or what have you. How am I going to get that data prepared and transformed into the way that I want to consume it, because I probably don’t want to just consume raw flat files in my analysis.
How am I going to store that information once I transformed it?
All right, what’s my, what’s my data repository solution?
How am I going to model that information from a metadata semantic perspective? How, what I mean by that is, how am I going to infuse that information with measure definitions, business friendly names, A thing that I could use, that’s repeatable. Or, I could explore that information. So it’s not just sitting in tables. There are relationships between those tables. There’s measures, there’s friendly names, like I said.
That’s a metadata structure I want to impose upon my data. How am I going to do that?
After I do that, how am I going to leverage that that work to visualize the information in reports, dashboards, etc?
And then, lastly, how am I going to share that information with my colleagues, with executives, with, with anyone in my organization?
I need to figure all that out, and on top of that, how am I going to have this operate super-fast? Right, one solution might be to have the data stored in memory. I just want to flag that in your mind for later, because that’s important for what Power BI does it, but it needs to be very performant and it needs to be able to be done by just me. It needs to be self-service. Like I said, I can’t involve IT in this exercise.
So the question at hand is, Can one tool do at all, right?
And let’s look at that question through the lens of how Microsoft has tried to answer that question through the years. So we’re going to Flash. We’re going to get in the Time Machine.
We’re going to go back to look at all of Microsoft BI offerings over the years, because I think it sets a good context for why Power BI is the way it is. So, here’s our list on the left, to prepare, store, Model, et cetera. These are the things we want to do. And let’s look at Microsoft’s first crack at, at trying to solve for these things. And that is Microsoft Excel, which came out in 19 87.
Now, can Microsoft Excel prepare data? Well, kind of can store data kind of, it can do a lot of these things kind of. But it’s not really purpose built for that. It’s kind of a Swiss Army knife, which is why you’ve probably seen Excel over leveraged in many BI applications, and over the years. It can be a spreadsheet, yes, but it can be a data entry, it can be data repository. It can be a transformation, it can have reports with charts and graphs.
But most importantly, the one thing that does well over anything else, is that an analyst can do it themselves. It’s one application sitting on their machine and they can over leverage the application to do, to jump through whatever hoops they have, and maybe it’s not a very elegant solution, but it is a solution, and that’s really important.
But, obviously, Microsoft recognize this is not sufficient, necessarily for all of these needs, and so in 19 89, they, you know, introduced SQL Server. This is absolutely a database repository, so it can store data really well.
It can kind of prepare data, which is to say, import data into SQL Server, although this first version of it wasn’t that great at transforming information, And it, it does, now, in more recent versions, have the ability for data to sit in memory, but it’s not a self-service tool, you have to partner with your IT resources to get it up and running. You don’t, you can’t model metadata in the solution, it’s, it’s, it’s built around data.
So, then, Microsoft decided to create a Microsoft Access, which came out a few years later. This tool is very much built to prepare it store data. You can kind of model, but it’s not really built for, you know, having measure definitions and what have you, but you can absolutely build reports in this solution. And that’s a real milestone. In this kind of timeline, we’re going through its various self-service, like with Excel, I’m sure you’ve either experienced or heard tell of an Access database that has been over leveraged. And it is a nightmare and albatross, but it is doing something.
And how well it’s doing it, who’s to say But it is, it was, it is solving for something.
But obviously, Microsoft Recognized this is not a sufficient solution for all of these needs. So then they started looking at diversifying the product offering within SQL Server. So, then, we have Integration Services, Analysis, services, and reporting services. And these three tools were meant to prepare data, model data, and visualize data respectively.
So they all are like very specific tools to answer these questions, but they aren’t able to in a vacuum. Each of them do any of all of these things together. You kind of have to use them all at concert. But you absolutely have to partner with your IT resources to get all this spun up. It was not very simple. It’s still not extremely simple today to just get this up and running and start leveraging it. So this is very much part of mode, one, linear exploration of data having to incorporate your IT resources.
So Microsoft went back to the grindstone and they said, well, what about dashboards?
We want to be able to present dashboards to be able to interact with information more easily than the canned reports and reporting services? So that’s great, that’s another tool in our toolkit, but again, it’s suffers from the same issue as all these other pieces. It needs to be part of a larger enterprise platform.
So then there was another milestone release that Microsoft implemented in Excel. And as you see, these three tools that came out in 2010, Power Query power pivot Power View, very similar to integration services, analysis services, reporting services. But now this is all in Excel as add-ons, so it’s very much a self-service application, you, as the analysts can install these things on your computer.
And then you can prepare data, store Data model data, visualize data.
And it’s self-service, so that’s great but it’s part of Excel. And so even then you’re hedging your wagon to the star of Excel, which, you know, doesn’t get updated very often perhaps by your IT partners. It’s, you know, not necessarily the proper place for this functionality to reside. So, Microsoft decided to take all of these learnings, and particularly that power stack and develop, Oh, sorry, before I say that, I should say, they created Analysis Services, Tabular, which was a repackaging of power pivot, which is to say, another flavor of analysis services from before.
So now, Microsoft has two versions of analysis services for metadata and semantic model development. So then Microsoft took all of these pieces together. They package them together as Power BI, which came out 2015.
So now you have one tool that can do all of these things, right? One tool can now do it all.
Well, I’m going to tell you how that’s not quite true. But it is all living under one umbrella now. So with the stage set, I can finally tell you what Power BI is. All right? And this is my best attempt at distilling Power BI is functionality down to one sentence. It is a self-service business analytics solution that lets you prepare and present data for your organization.
So what does that mean?
It means that Power BI is serving five essential functions. It is a query engine, it’s letting us hit data, and transform it, and then landed in a data repository that sits in memory. So, it’s super-fast, it’s not sitting on disk, so when you ask it things, it’s not going to disk to get it. It’s sitting in ram and memory on your machine, or the machine that’s hosting this, so that you can get answers back very quickly.
It is a semantic model. We can build relationships, we can build measures, we can build business friendly definitions.
We can build hierarchies, et cetera, so that we have a model that the analysts can consume, so that is user friendly.
It is a visualization dev tool. We can build reports. We can build dashboards. They’re interactive. It’s very good at doing this.
And, finally, it has a distribution portal. So, you can take all of that stuff that you’ve built, publish it, and share it with your colleagues in the cloud.
So, the punchline here is that Power BI is actually a set of tools. It’s not just one tool, one tool can’t do all of these things. And so, you may have heard of some of these components. Before you attended this webinar, there’s Power BI Desktop, which is the tool that you install on your computer to develop reports, data, reports, and datasets. There is the Power BI Service, this is where we publish the artifacts that we build. There’s the Power BI Report Server, which is the on perm version of the Power BI Service. In case you can’t or don’t want to use the cloud for publishing your artifacts.
There’s Power BI Mobile, so, on Android or iOS, you can hit these, these components and pull up reports and dashboards on your phone.
There’s Power BI Embedded, so you can add these reports in an application directly that you may have a proprietary application in your organization. There’s a Power BI data gateway which allows us to actually get data from an on premises resource and pipe that all the way up to the cloud.
I’m kind of jumping through this quickly to kind of overwhelm you a little bit, because there’s so many components. I just wanted to kind of throw these in front of you.
And then lastly, but not least, there’s Excel. Excel is actually a really important component of this solution.
Because it is one way in which Microsoft facilitates us, accessing that semantic model, but we’ll get to that in the demonstration.
So, with all of that, I want to now talk through the core concepts and workflow in Power BI. There’s going to be a lot of information on the screen. I tried to make it as visual as possible. But just for before warned that, I’m going to show you a lot of info here.
So, imagine our data sources, either in flat files or databases, or what have you.
We’re going to create what we call queries within Power BI to connect to that information, transform it, as we see fit. And then we’re going to land it in our, in our model. It’s a data repository, and then we’re going to build our, our metadata framework on top of that, the queries in the model together. Form what we call a dataset, OK, When you’re using Power BI. Sorry, our dataset is the combination of the connection to the data, the transformations, and then the actual model. This is one concept together, and from the dataset, we will then build a report.
And this report has multiple pages, and on each page, we have multiple visuals. OK, so, Report page, Visual.
Keep those in mind, We in this little graphic, I have four pages and a bunch of visuals, that Forms one report, the sum total of all the development that we’ve talked about so far.
It’s happening in Power BI Desktop, This is a client that you install on your machine, on your Windows machine.
And it allows you to build these queries, allows you to build the model, allows you to construct these reports, and then, all of these things together, a foreign Power BI Desktop. So, before we move on, I want to review. The dataset consists of two object types. There’s the queries, which are definitions of how to transform our data sources and they’re accessed via what we call connectors.
So, Power BI has a bunch of native connectors built-in, such as connecting to Excel or SQL Server or Oracle. There’s a bunch of them, OK? You can also build your own custom connector. We’ll talk about that a little bit later, but there’s many that are native out of the box within Power BI.
Then we have our model. This is where we, we have our definition of tables. The relationships between those tables, the columns in those tables, the measures, the business friendly names, the hierarchies that are sitting on top of those columns. It’s the, it’s the metadata that the end user will access and, or we would use to build reports.
All right, and then our report points to that model. And it’s going to present data from the dataset on pages of visuals.
All right? So now, we’ll take all of that work that we have constructed. And we’re going to publish it. And we’re going to publish it out to the Power BI service. This is the cloud. This is Power BI.com, OK? You see here, I have now the dataset and the report living out in the Power BI service.
That is what we’re going to, we’re going to publish to what we call an App Workspace.
This is kind of our, our workbench. It is our development kind of space that we’re going to upload our artifacts and create a few more. So, we have one, data set, one reports so far imagined that we create another dataset.
Imagine that we create a couple more reports from that.
We can also create a different kind of artifacts.
We can take the visuals, like charts and graphs, matrices off of the visual off of the pages of the reports. And we can do what’s called pinning to what we call a dashboard. So, this is, this is an artifact that we actually build out in the cloud. We don’t build us the Power BI desktop. We take our visuals, we pin them to dashboards and then those are existing out in our app workspace, Right? So we have datasets, reports, and dashboards.
Then what we’re going to do is we’re going to prune this list of everything we’ve developed. Maybe it take a subset of these and we’re going to publish those to what we call an app. So in this case, we now have only one dataset, two reports, and one dashboard. This is the, kind of the production version of the workspace, which is more of our development space, but this is what’s going to be exposed to end users for what they’re going to do with the data. Now that’s been exposed to that. There’s a few different things the user can do with the app.
One is just consume the visuals, they’re going to look at the reports, they’re going to interact with the dashboards, etc.
They can also create their own vigils, this is actually really new functionality in Power BI where they can take your dataset that you publish to your app, and they can create a report in their own workspace against your dataset, and then they can go hog wild by building their own reports and extending functionality, et cetera.
One other really important concept is that they can analyze in Excel.
They can crack opening your dataset within Excel by connecting to it via an ODBC connection in the Cloud.
Well, I’ll show you that a little bit later.
So to review that, we have our dashboard. It’s presenting visuals that are pinned from reports.
We have our app workspace, which is the draft container for those objects, the reports, and the, the, the datasets, our app is the user facing container or production kind of space.
Then lastly, I wanted to mention the Gateway. This is, this is what lets the service connect back to those data sources. You know, you can trace that lineage from the data source up in the top left all the way over to the app on the bottom right. We need to install. It’s called a gateway to allow us to connect to, let’s say, an on premises SQL Server instance, or some Excel file that’s sitting on my machine, and the cloud needs to get to it.
Alright, so, I know I’ve presented a lot of information here. And there’s actually a bunch more stuff that I haven’t talked about yet, that I’m not going to get into in this webinar, because it’s just too much to cover. But this is what I perceive to be the core functionality, the core value proposition of Power BI.
From there, let’s move on. And I’m going to actually crack, open the solution, the technology, and give you a demo.
So, we’re now in Power BI Desktop.
This is, if you’ve used Power BI even just a couple of months ago, you may not be familiar with what we’re seeing here because they’ve really recently refreshed the look. And the feel of it is to have a more of a dark field.
Now, it has more of a light field, and they’re actually refreshing the Power BI Service right now, too, I’ll show you that a little bit later. But anyhow, we have a blank canvas here.
We have the ability to create visuals, some fields. This is all blank because we have no data yet.
So we’re going to take a very simple dataset and bring it into Power BI.
Here’s our dataset really, really straightforward. It is. It is one spreadsheet.
Well, one tab, it’s got date information. But you know, we also have quarter, month, year, and then we have Country and continent, and then we have product, we have Segment, and then we get to units sold and manufacturing price and gross sales and discounts and cogs so we want to present this information. Oh, and by the way, this are building new addition to Excel. It’s really just totally aside, but it’s exciting. If you just scroll in a dataset now within Excel, it automatically changes your ABC columns to actually the column headers, which is super cool. You don’t have to freeze panes anymore.
Anyway, that’s an aside. We now have our dataset, and we’re going to bring that into Power BI.
So let’s go back over to Power BI. We’re going to get data. This is the foundation of everything that we do in Power BI. We’re going to open up the Get Data Framework. We have a bunch of connectors in here.
Like I mentioned, there’s just a ton of them, I’m not going to dwell on any of those. I just want you to see there’s a lot of them.
But we’re going to focus on Excel because we’re importing data from Excel.
So we’re going to hit Connect.
And then we’re going to go get our information.
And it’s going to be it’s going to be our financials data sample. So we’ll open that.
We’re establishing a connection.
And it’s thinking real hard.
So now we can go and crack open that sheet, right. And here it is, the same data that we saw.
There’s some weird, null columns out here, so it wasn’t exactly sure what to do with that. But all of our data is here. What we could do is click load, but before we do that, I’m going to hit Transform Data, because I actually don’t want to import the data in this exact format. What I’m going to want to do is get this data into what we call a star schema. I don’t want to throw all this information at the user in one big blob. I want to actually have it be a meaningful relationship of tables between Rs.
Are fact data which is our sales are cogs or discounts and then then the, the entities by which we would slice that which is, date its country.
Its products if I flash forward a little bit, we want to get to this. We want to get to a model where we have sales here in the middle, and then a star schema around it with country, and date, and discount and segment, and product, right? Instead of one table, we’re going to actually present six tables to the user, because then it’s more meaningful to leverage in the model.
But I’m jumping too far ahead of myself, where back here. It was looking at the data. So I’m going to transform the data.
And the first thing I’m going to do is, I’m going to create my date table.
So how am I going to create a date table from a data set like this where I have a bunch of repeating data? Well, there’s let’s jump to the exercise first of all, I’m going to get rid of any fields that have Nothing to do with date, so here are my five fields that are date related. I’m going to right click. I’m going to save remove other columns. So all that other stuff is gone. It’s, there’s nothing left.
Now, what I’m going to do is I’m going to go to Transform, and I’m going to go to where is it? Is it in here?
Yeah, Remove Columns. I’m going to Sorry, not Columns, Remove Rows, I’m going to remove duplicates.
So now I’m down to just a list.
Oh, my apologies. I think I did it wrong.
Remove rows. Remove duplicates.
That’s fine, OK? So now, I’m down to a list of all months, OK?
And the cool part about this query tool is that it actually kept all of my work, so I can actually jump back farther into what I just did, and I can see the data step by step. So here’s my remove other columns step. Here’s my remove duplicate step. Here’s the remove duplicate step that I messed up, so I’m just going to get rid of it.
So, I just click X and it’s gone.
So now, we’re looking good. But let’s say that I want to have a column that is that Concatenation of Month, and Year.
Well, before I do that, I’m going to rename this column to, I don’t like the name, month number.
I’m going to call it Month of the year number, because that’s really the month of the year.
It’s not unique, so now I’m going to add a column, I’m going to do add column, I’m going to say custom column, and I’m going to concatenate the month and the year together.
OK, how am I going to do that? I’m just going to, I’m just going to grab a pre baked thing here.
So, I’ve got my number, it’s, this is the formula. This is what we call the M language, this is how we manipulate data under the covers. So I’m going to have number to text of year and then I’m going to concatenate the month number, but I’m going to call it month, year number.
So no syntax errors have been detected.
I hit OK, boom, here’s my custom column.
I have this, a six digit integer now which is meaningful for all months.
So this is what I want to call month number.
And then I’m going to rename this query.
And Oh, I’ll delete it.
I’m going to rename it.
Am I call it Dates? This is my date table now.
I’m going to not spend time now going through the exercise for all the tables. I’m going to like Julia Child, put the cake in the oven and pull out a pre baked cake.
So now we’re going to jump a little bit farther forward in the process. And now I’ve loaded all of my tables. I’ve got Country, Date, Discount, Product, Sales, and Segment.
And if we look over here on the Model tab, I’ve got those six tables, they’re just sitting here, pulled in, right? There, ready to be operated on. So now I need to create relationships between these.
So I’ve got sales, and I’ve got a date.
And what I want to do is create a relationship between these. So, I’m going to take my date.
I’m going to drag it over to date.
And it created a relationship. It has a one to many relationship, because that date table, remember it has one row per day, which in this case, is the first of the month, and this one has many of those same chino January first repeats over and over again, So that’s a one to many relationship, and I could do that with all of these.
Another thing I can then do is start to create my measures.
So I have my gross sales column here, but I want to actually define a gross sales measure, which is an important difference.
So what I’m going to do is I’m going to hide the gross sales column, And what I’m going to do then, is go to here, and go out to my report view.
I’m going to go into Sales, and I’m going to create a new Measure, All right?
Am I new measure?
Is going to pop up here, and I’m going to say Gross sales dollars equals the sum of the gross sales column.
This is a measure. It’s based on a column we don’t want to expose the column to the user. We want to expose a measure.
So now I hit return.
And now, you’ll see over here on the right, it’s got a little calculator icon that’s a measure.
So then I can just grab that and pull it over here, and it’s going to present a bar chart.
Let’s say I want to change this into a card. We’ll get to the definitions of visuals later, but it’s bringing up a 1, 2, 7, zero point nine three million.
So I actually want to format this measure a little bit more. So I’m going to go to Modeling.
I’m going to go to Format.
This is a currency. I want to tell Power BI, this is a currency.
So, I’m going to go to English, United States dollars, and it’s now going to always put a dollar sign in the front.
But let’s say that I don’t want to keep the decimal stuff. So I can actually click up here and put that away.
So, now, I am looking at, oh, yeah, because it is rounding’s rounds to millions automatically. I can tell it to, to not do that, so that it will only ever show actual dollars. Instead of millions, or billions or what have you, it defaults to a single decimal.
But what I can do is force more decimals, if I want, Right. So there’s some formatting we can do.
So I could go through the exercise right now of creating more measures. I’m going to skip that. Now also, I’m going to jump forward a little bit farther where I have now modeled all of my data. So, now what you see here on the right is, I defined cogs. I defined gross sales, I defined net sales. Some of these are, you know, they’re all pretty straightforward. You can see net sales is gross sales minus discounts, and I defined my gross sales discounts, my gross profit, is gross sales minus cogs. Like Gross Profit Margin is a division use the divide?
The Divide Operator, I should have mentioned earlier that this is what we call the DAX language.
It stands for Data Analysis Expressions. It’s a language that’s proprietary to Power BI.
It’s very similar to functions that you may have seen in Excel, but it is its own language, and, you know, I am several years deep, and I’m still learning how to best use the language. It’s one of those minute to learn lifetime to master kinds of languages. We’re looking at a very, very simple example of DAX, but it’s a very deep. You can have variables, you can have iterator,
It is a very powerful language in my opinion, but we’re not going to get into any of that.
So now we’ve got all of our measures. We also have our hierarchies. You notice in the Country table we had country and continent, we’ve created a hierarchy that has what we call geography, which is continent and country. So, one goes to the other, so the user could use them separately, but we’re going to show them the geography together. So, it’s meaningful. In the same way, we have a product hierarchy, which is the combination of, of product, category and product. So, we can start dragging these onto our Canvas, we can start bringing that product hierarchy over. It will automatically show up as a table, and obviously, it’s very, very small. So what we can do is go into our Format tab, and we can start increasing the size, so that you can see that information better. So we can see our product categories and our products.
But let’s say we want to create a slicer.
So we want to add our product here.
And we’re going to make that bigger. So we’re going to go to Values.
We’re going to scroll down, we’re going to make that bigger also. And it’s defaulting to what we call a table visual, but we’re going to create this. We’re going to convert this into what we call a slicer. So now we’re going to let the user choose these guys.
We’re going to re increase our size. So now we let the user choose which products they want to look at.
Let’s add another visual. So this is more meaningful. So let’s create a pie chart.
Let’s add gross sales to it.
You notice how quickly, I’m able to just, like, create a report, it’s, it’s a very intuitive interface, In my opinion, let’s add country here, So now we’re looking at gross sales by country, and we got the US, France, Germany, Mexico, Canada, And that slicer I created, allows us to start seeing the changes, as we choose, and Maria, Kara, Tara, Montana presale, et cetera.
We can also create, as we did earlier, we can create a card visual so we can see our cogs.
Oh, that’s not what I meant to do. Let’s ignore that. Let’s put cogs here. And let’s create a card visual.
We see 13 million as we click through per sale, vela, we see that changing, I’m going to flash forward one more time and get to an actual finished report where I’ve actually gone through the exercise of building out a report. So we have all our slicer is here on the left.
We have our pie chart, we’ve got a bar chart, we have some, some cards, and we have some detail here. And this is, you know, looking decent.
You know, you may not think it looks amazing, but it’s functional. And then we’re going to, we’re going to want to publish this. Now, the service, so, we’re going to click this Publish to the Service.
So, I’m not going to bother stepping through this, because I’ve already published this out to the cloud.
But you choose your workspace, and now we’re going to jump over to powerbi.com now that we’ve published this report. And here we are, over in powerbi.com.
Wage is not responding, of course. So, let’s create a new one.
Let’s just go to powerbi.com.
Let’s jump back here because it responded.
We got the sign in.
And we’re live in powerbi.com, and it’s going to pop up all of my workspaces that I can use to, you know, upload to, I’m going to go to my workspaces and I’m going to go to financials, that’s the workspace.
Right, it is demo.
Sean, we seem to have lost your audio, can you check your audio?
Sorry, everyone, we’re trying to get a hold of Shawn, so you can work on his audio here.
We’ll get them back online as soon as we can.
Just hold on, everybody. We are, I believe the Unshared is screen, and he’s going to re share it to see if the audio comes back, or he may have dropped out. So give us a second, I’m sure he’ll reconnect, and we’ll get things either. I’m back, can you hear me?
Hey, Sean. I do apologize, I’m very sorry. That’s all right. I will go back to sharing my screen.
We lost you right at the point where you jumped over to Power BI.com and had kind of logged in, and that’s where the sounds, correct. So if you want to start a jump back to that, we can pick up from there.
So, so here in Power BI.com, where we, we have our report, it’s the same report that we saw in Power BI Desktop.
It is opening here.
And we’re going to see the same visuals in terms of the slices on the left, the gross profit, net sales by month, et cetera. Right. And, you know, it has the exact same functionality. We can actually edit this report, if we wanted to, out here by clicking Edit Report. One other thing I want to point out, is that there’s a new look off, I could click new look on, and it would refresh with the same light, look, and feel, Remember. I mentioned there’s a dark versus light look. And feel that they changed in the in the desktop. They haven’t yet had changed by default. In the service, and that’s, that’s, they’re going to do that in the next couple of months. That’s a great example of how Power BI is changing, and shifting all the time. They’re updating it on a monthly basis, and this is a great example of how, you, know, they’re making it better, but they are changing it, and they are moving or cheese sometimes. So, there, you may experience frustrations when things change like that, but they’re trying to steward it as best they can.
The last thing I want to show you is, is analyzing an Excel.
So we’re going to go going to go back to our financials, and we’re going to go back to our dataset. And we’re going to do what we call, analyze in Excel.
What this is going to do is, open up an O D C file on my computer, and it’s going to open Excel, and it’s going to actually let us explore that, that model we created here in Excel.
Uh, Or it’s supposed to for lack of time, I’m not going to step through trying to troubleshoot this.
But what you would see in Excel would be that same set of facts and dimensions that we saw in our metadata model on the right hand side of power BI, except it would be in Excel. And then you can slice and dice and pivot tables. And that functionality is just a huge win for analysts who know how to use Excel. And they’re very comfortable with pivot tables.
So, definitely a huge piece of the value Proposition four for Power BI.
That concludes what I wanted to show you in the demo. So, I’m going to jump back to my presentation and jump through the last few slides I have.
To give you some more details for Power BI, one is the release cadence.
So, as I mentioned, the Power BI Service is being updated monthly, and that’s true for the Power BI Desktop application as well, and they’re on a rough quarterly cadence for the Power BI Report Server, which is the on premises version of the Power BI Service. Ideas can be submitted and uploaded on the Power BI Ideas forum, which is really cool. For lack of time, I won’t take us there.
But if you go to ideas that Power BI.com, you can see all of the ideas that have been uploaded and see the most highly uploaded ideas, and you can see that Microsoft responds to those and says, we’ve added it to the backlog. We’ve started work on it with completed work on it. It has a strong influence on what they’re doing on a monthly basis, which is very cool.
Next, we have licensing. I can only touch on the very, I can only scratch the surface here, because licensing, we could spend a whole webinar talking about. But essentially, we have four different kinds of licensing models. We have free, We have Pro, Premium, and Embedded.
So free is really powerful. You can use Power BI Desktop, you can actually even upload to the service, but you can’t share any of that with other people. You can’t connect to on prem data sources. You can’t analyze in Excel.
They really want you to, onboard, to having a pro license, which is necessary for content creation that you can expose to others. And it’s $10 per user, per month. If you have an enterprise agreement, it’s probably a little cheaper than that, but it’s roughly that amount. But then there’s premium, which is dedicated server capacity for the Power BI service, which is licensed by Node instead of by user. So people with free licenses can actually use Power BI Premium because you’re paying for the space rather than the users.
And some features that are new like paginated reports and incremental refresh, they’re only available in premium, that’s something to keep in mind.
Embedded is also dedicated server capacity, but that’s for Power BI Embedded.
That’s how we embed reports into proprietary applications that are our organization may have created, and those are also licensed by Node.
Um, we also have the ability to create custom connectors and visuals, so, there’s a bunch of stock visuals, stock connectors that we have available to us, but we can also create our own. I wanted to mention that some tourists recently released a connector to Cognos, for example, or you can use a Cognos A cube and connect to it with a Power BI report.
Very cool. You also can connect to any number of custom data sources that may not be available out of the box, in, in the, in the, in the stock solution.
Newer features. I just want to talk about a few things that have come out in the last few months.
Paginated reports. You may be familiar with reporting services within Power BI. This is the ability to create canned reports, and this is new functionality. They just added it in the last few months. It’s still very much V one. They only really recently added the ability to connect that report to a Power BI dataset. So, this is still maturing, but I wanted to put it on your radar. Data flows. We didn’t talk about this at all.
But these are actually taking your queries and putting them out in the cloud, and they can then be re-used for datasets, which is very cool. It’s adding to the idea of reusability, having a common data model across your enterprise.
Reports across workspaces, I mentioned this earlier.
You can create a report and workspace A, and it points to a dataset and workspace B, that is very new functionality. I think it is a total. It was a deal breaker for a lot of people before. A lot of a lot of our clients. And now that exists. People can. Now, it’s, this is now a functional tool, and they can incorporate it into their solution.
There’s a bunch of core components, like, filters, visuals, data modeling, that have been refreshed, and overhauls really recently, I showed you some of that.
We can now group visuals together, like in PowerPoint that actually is new to the August 2019 version of Power BI. But there’s, there’s limitations to that, like, you can’t click and drag to grab your visuals. You have to control click. That’s the very, very specific example of how Power BI is still maturing.
You would expect to be able to do that.
But when you can’t, it’s like, Oh, that’s right. This tool is only a few years old. They’re still working on it, they’re still maturing it.
The roadmap, which I encourage you to look at, if you just Google Power BI Roadmap. It lists a lot of other new stuff coming soon. Particularly in October 2019, that it’s like data lineage and increased data protection.
It’s really cool that they are now publishing what they’re going to do. And sometimes they change when they’re going to do it, which is a little frustrating, but it is nice to know what’s coming up.
Like I said, data lineage, better usage reporting, they’re going to actually increase datasets to be larger than 10 gigabytes, et cetera.
Some limitations of Power BI that I want to make you aware of. Despite, despite its recent growth spurts, there’s still a bunch of little shortcomings Like, I mentioned, you can’t drag across the screen to select multiple visuals. I think that’s really frustrating. I’m sure they’ll fix that at some point.
Does a given connecter had the ability to use direct query or single sign on, which are topics that we just don’t have time to get into? But, these are, these are attributes of a connector that a given connector may or may not have the ability to do. You have to go out and look online and see if it’s working. And then maybe in a few months, it’s going to start working.
There’s a lot of a lot of little things like that you have to keep in mind that Power BI is still a maturing tool.
It’s a competitor to Tableau but I think Tableau is a better visualization tool for now. Microsoft is absolutely gunning to close the gap between what they have presented and what Tableau can do.
I imagine in a few years they may be True peers from a visualization standpoint, or maybe Microsoft will have actually outstripped Tableau. It remains to be seen. But now that Tableau has been purchased by Salesforce, I think the race is on.
A monthly release cycle means some components aren’t always complete on first release, which is a little frustrating. Like I mentioned, sometimes they’ll release, they will have a component coming up in functionality, and then they push it out a couple of months, because it’s not ready. But then they do release it, and it’s not fully featured or fully functional. And then it takes them a couple more months to have it be fully functional before it goes to general availability. It’s just to say that keep in mind when you’re onboarding to Power BI, that there are many features that are in what they call Preview Mode and will not be in true general availability mode for some time.
In that regard, I really perceive Power BI to be a jack of all trades and a Master of None. And that brings us to our last slide, which is, what about all those Microsoft Power BI tools, besides Power BI that you mentioned at the top of the webinar?
Like Power BI can be used to gather and prepare data, but the options are limited, right, in terms of data sources, refresh cadence, and the destination, of course, has to be Power BI. If you want to land it into SQL Server or something like that, you need to be using something else. This is where a tool like integration services within SQL Server can be configured precisely and go to other destinations, like SQL Server.
Power BI obviously can be used as a data, a metadata repository.
But it has a 10 gigabyte limit, right, Whereas Analysis Services is built to scale.
But like I mentioned, that 10 gigabyte limit is going to go away soon. So this is an example of where they’re really trying to integrate have Power BI B feature, have feature parity with its other tools.
Power BI can be used to build reports, but they don’t necessarily print well. And they don’t get distributed by data driven subscription. They’re not necessarily pixel per Pixel. Perfect. But reporting services is very good at this. But paginated reports are now available in Power BI.
But like I mentioned, they’re still maturing. This is another example whether folding in the functionality of the other components they are coming into Power BI.
Power BI can be used as an ad hoc query tool, but we can connect to a Power BI dataset, an Excel, and we can use pivot tables much more easily for Ad Hoc Analysis than we necessarily could in the Power BI service.
So my last point here is that Power BI may eventually be the umbrella for all those Microsoft BI tools. I imagine, in 3 to 5 years, we may be talking about all of these components as one Power BI framework, which is really, really interesting.
So that’s all there is to know. Right? So, there’s so much we didn’t discuss. And even as it stands, I’ve kind of overrun my time with you, but there’s so many other components. I can only really scratched the surface of what Power BI can do. I encourage you to do your research and, and look out there, at everything it can do.
Power BI is a flexible and deep tool, so it’s tough to keep track of everything it can do, and will do in the future. I, myself am, I’m often like, Oh, there’s a new piece of functionality. I forgot about that, or, I didn’t know about that. That’s going to happen every 30 days, as long as I stay connected to the Power BI framework.
I encourage you to reach out, if you have questions, there’s my e-mail address. I hope I can answer any questions you might have or direct you to the proper resource, but, with that, I really want to thank you for your time and I’m going to turn it back over to Mike.
Thanks, Sean, that’s some great stuff. Everybody. Please stick around, because we do have some.
We have the Q and A at the end of a couple of slides here, but one of the things when you get beyond the eager analyst, who wants all the self-service and attaches to the Excel spreadsheet. And now, you’re thinking about scaling this out to the enterprise.
Well, then, the questions get a lot more challenging, and a lot thornier, so, if you’ve recently started with your Power BI implementation or are about to make that move, we have a service to help you ensure your success there. We offer a short collaborative engagement that will help put your organization firmly on the path to building a foundation with a scalable architecture and optimized performance. And we call that the Power Black Power BI Blueprint to Success.
A couple of quick slides, again, before we get to the resources in Q and A Senturus who we are.
Our clients know us for providing clarity from the chaos of complex business requirements, myriad disparate data sources, and constantly moving targets. We’ve made a name for ourselves because of our strength: Bridging the gap between IT and business users.
We deliver solutions that give our clients access to reliable analysis, ready data across their organizations, enabling them to quickly and easily get answers at the point of impact, and the decisions they make, and the actions they take.
On the next slide, our consultants are leading experts in the field of analytics with years of Pragmatic, real-world experience, and experience advancing the state-of-the-art.
We’re so confident in our team, and our methodology that we back our projects with a 100% money back guarantee that is unique in the industry. We offer a full spectrum of services, everything from requirements gathering, to data preparation, modern data warehousing to content creation in the form of dash boarding reporting, and visualizations.
The infrastructure around that, to helping you make the decisions between various tools, like Power BI, Tableau and Cognitive Analytics, managing the infrastructure, making the decision of being on prem versus cloud or hybrid, and, of course, a full spectrum of training.
On the next slide, we’ve been at this for a while nearly two decades. We have over 1500 clients, ranging from the Fortune 500 down to the mid-market, across over 2500 successful projects, ranging from the Office of Finance to Sales, Marketing, HR, and supply chain. So, the next time you need someone to help you out with an analytics project, we hope you’ll let us bring our experience and expertise and proven methodology to bear for you.
Couple of great additional resources. A lot of you in the question pane are asking about how certain functionality in Power BI compares to that of a Tableau or a Cognos.
Well, that’s prescient because we have a webinar on Thursday, September 26, where we’re going to, again, reprise, our comparison of Power BI Tableau and Cognos, where we do a demo and a comparison of a bunch of different functionality there. We do. This Webinar is sort of a subset and a teaser to an offering, that we have in that area, as well, that I alluded to earlier, where we analyze. And kind of keep up to date. Over 150 different attributes between these tools to help you make a better decision as to what makes sense in your organization, between those Mode, one, and Mode two tools.
As that’s an increasingly complex decision, made me even more complex by the toward release schedules that that Sean mentioned, and ever changing functionality. The, in terms of free resources, we invite you to head to senturus.com/resources, where you can see all of our upcoming events and register for those again, preponderance of those being free. You can visit our Resource Library again, where the recording and the slides of this webinar, as well as hundreds of other webinars on the latest and greatest and great tips and tricks about what is the state-of-the-art in Analytics. And then, of course, our blog is fantastic for great bite size learning on what’s top of mind at send tourists.
What I’d be remiss if I left out training, we provide training in the three top tools, IE, the Microsoft stack.
Predominantly power BI, Tableau, and Cognos, being multilingual. In the analytic space, we are ideal for organizations that are running multiple platforms, or those who are moving from one to another. We provide training and a lot of different flavors.
As you can see, ranging from in person to self-paced to very focused one-on-one or, you know, one to a few mentoring and self-lead.
Instructor led online courses, so a number of different modalities that enable you to best address the educational needs in your organization, OK.
So that’s a whirlwind about Senturus is here. So, let’s get into the question and answer, since we’re getting close to the top of the hour here, Sean, I think, you know, maybe you’ve looked at the questions here, but really, what I see a lot of our, you know, there were questions about, OK. How does how does the Power BI or what in Power BI compares to the framework slash metadata stack or maybe perhaps data modules and Cognos analytics? And, can you maybe speak a little bit to that?
I’m really sorry. I’m I was looking at the question pane. Can you please repeat the question? One more time?
Trying to get Atomy, I think you may have noticed looking at the question pane: there’s a lot of questions about datasets and, and, and the comparisons of various components of Power BI as they pertain to metadata in Cognos.
And, I’m wondering if, maybe, you could, you know, take a few seconds to talk about that. Addressed that. Yeah, I mean, you know, I will say, First of all, I’m, I am no Cognos expert, but I definitely have familiarity with the tool.
And from a, conceptual standpoint, you’re doing some of the same things, like, you know, in, in Analysis Studio.
You are creating a cube with the metadata repository in terms of you know your business friendly definitions. Your you know your hierarchies, your measures, and then you’re exposing that in a tool like I don’t know reports studio where you’re building reports It’s The same thing as with?
As I mentioned, Analysis Services and Microsoft versus reporting services, the concept is the same, but the execution is very different, and, and as we saw in Power BI Desktop, that’s all really integrated.
I think that the value proposition with Microsoft, the Microsoft stack is that they’re really trying to integrate all of this into one umbrella.
So, it’s not really dislike, ad hoc, a la carte kind of set of components that you need to understand. Separately. It’s all like they all talk to each other, it just works. Right?
So, conceptually, I see them as very, very similar, but I think in execution, I really like how streamlined the Microsoft stack is doing that now.
Yeah, I would agree, and as a as a relative cognizance expert. What you see in these, these mode, two tools, write the Tableau, and Power BI is as, if they condense that whole stack, the data acquisition, the metadata modeling, like you kind of went through in that slide 10 into, you can do it all more or less than a single desktop tool.
So, I think, relative to Tableau, Power BI is, has a more powerful metadata modeling stack with M and DACs, versus their Data prep pain. And neither of those is as robust as framework manager, which is a 20 year old, very rich client tool. But like you said, the Power BI experiences, I think much more streamlined. Yeah. I think going into the, sort of the next question that’s somewhat related to that is sharing those semantic models within the organization. So, what’s the, what’s the strategy or the sort of framework for that?
Yeah, so, so the, the dataset really is our semantic model, which, as we mentioned, is our queries and data queries and our model together, that is, you know, once we have put that out into the cloud, then we can connect to it via our reports via Excel.
What have you? now, you can’t connect to Tableau, necessarily, or any other, like, non-Microsoft products.
Which is a limitation, certainly, but once it’s out in the cloud, connecting to it is, is really the main mechanism.
Now, I will say that Microsoft has, they are exploring, opening up the endpoint for the data set to be exposed in a similar way that we can expose analysis services.
To a tool like Tableau.
Because Analysis Services, it plays with a richer, you know, set of front end components, like Tableau.
And the Power BI dataset was built to be more, little more limited.
But, as time goes on, I think we’re going to start seeing the ability to connect to a data set out in the cloud, to be possible with more and more front end tools, Microsoft, or non-Microsoft.
Yeah, I think that that’s, that’s a great point, and the stacks have their proprietary data sources like Tableau has extracts. And then, you know, Cognos has the Transformer Cubes in TM one Cubes and things of that nature, so, we, and even, so, I just to put a quick plug in there.
So, the organizations that are running Power BI and Tableau in addition to Cognos, we’ve even developed a connector that allows you to leverage those cognitive framework manager models using a Power BI, for example, custom connector, right in Power BI, so that you aren’t having people go through that whole exercise that you went through at the front, against those transactional operational, or even your data warehouse systems, wasting time, and kind of circumventing controls and things of that nature. So you can definitely check us out for that.
A few other questions here, I know are beyond the top of the hour, so I want to be mindful of people’s time here, but there was a question about being able to create linked Pivot Tables using a Power BI dataset where they can simply hit Refresh and the Power BI data is updated because it’s something you can speak to.
Yeah. I mean, that’s a huge part of the value proposition is that and, you know, I didn’t show it because I had some sort of connection issue.
But once you’ve got the OTC file on your computer, then you can open up your pivot table in Excel and his hit, you build out your model, and then save it, right? So you have your XLS, XXLSX files sitting on your machine.
And then, anytime you want to refresh it from the dataset, you just open it up and hit refresh. Or you could set up your Excel file to automatically refresh whenever you open it.
So that’s absolutely within the use case of Power BI. And I think it’s, it really opens up the possibility of analysts then going hog wild in terms of extending the model, maybe incorporating their own spreadsheets or other datasets.
And then, really, the next step is really important to then work with whoever created the Power BI dataset to then extend the functionality.
So there’s a partnership, right, because the analysis is always going to be running ahead of the, of the artifacts that are available in a model.
It’s always going to be by definition growing. So what Power BI allows us to do, via that framework, is, is, is close that communication gap, quote, bring those resources closer together to, then be always extending the BI semantic model and make it more usable for the analyst to then be leveraging in Excel.
Yeah, that’s great, and I agree, Or do you started creating that, that Holy Grail, where you have the feedback loop between the end users in the organization, instead of being a one-way trip, or IT sort of hex it over the fence, and you never touch it again. That’s, that’s great. So I agree that that’s really enabling ad hoc and collaborations and things like that.
So last one here, and then we’ll wrap it up here. So it says Power BI does not seem to support composite keys for joins. Are you able to handle that, or how do you handle that, or get around that?
Yeah. It definitely doesn’t handle composite keys. It very much wants, expects one column for your key. So, what you can do is concatenate.
You we saw that in the example that I did with year and month, but you could take instead of urine month, you could take all of your composite primary keys and just slam them together in a concatenates statement.
Then that exist in a single column, and then we can make that Arche.
So it’s an, it’s, is that the best solution?
That’s not necessarily, but that is the best solution within Power BI.
Do I think that they might support composite keys in the future?
It’s hard to read, the tea leaves on that. I suspect they may want not to because it does add a complexity that may be confusing.
But as it stands, no. It is true that there is not support for that today.
Got a great. Well, again, and we’re way past top of the hour here. Thank you Sean, for a fabulous webinar and gracefully handling a technical glitch. There, sort of, halfway through thank you to all of you for joining us today is Jonathan to advance to the last slide. I just want to thank you for your time and attendance today, and hope to see you on another event here soon. You can always reach us at our website at senturus.com. You can e-mail us at info at senturus.com, if you’re old school, and still use telephones. You have a AAA number there, and we encourage you to connect with us on our various social media outlets at LinkedIn, Slide share, YouTube, Twitter, and, or Facebook. So thank you, everyone, for your time today. Thank you, Sean, our presenter, and we’ll see you on another Senturus event soon. Thank you very much. Bye now.