2016 1 20 Analyzing and Visualizing Data with Power BI MOD PowerBI Desktop Jan UPdate

[Miguel] Hello everyone. Welcome. Happy New Year 2016. We're here to talk about the first update to Power BI Desktop this year, which is the January 2016 update. We have a bunch of new features this month. And let's just dive in and see all of the awesomeness that the team has been working on for the last few weeks. We have new features this month spread across three main categories. We have new features around report authoring, data connectivity, and other improvements. Let's talk a little bit about the report authoring features. The first feature this month is the ability to add borders to a visual. So users can now add borders to any given visual within the reports by just using the formatting pane for that. Let's go into an existing report, and we're going to go ahead and add a border to one of the visuals in this report. The first thing that I need to do is select my visual. And then I will go into the formatting pane, and if you scroll towards the bottom you'll find the border section. You can turn on border and then you can expand the section for additional settings. In this case you can actually set the color. So if leave it like this it's a black border. You can actually go in and modify the color just like you could do with any other color formatting within the product. So you can, for instance, set this to purple. And that will give you the border that you specified. Just another nice formatting feature. You know, along the way every month we keep adding a few of these. So this is one. Another one that's pretty cool is actually support for background image for Cartesian charts for the plot area and condition charts and also for an entire page. Let's go ahead and take a look at how this feature works. Within this report, I could just click on the page background and go into the formatting pane. And then there is this new page background section where I can either select the color, which was something we enabled a few months ago, or I could add an image. So if I click add image, I can browse for the actual BMP, GIF, or any other bitmapping mix file that I would like to add. And this will create the background image, as you can see, and you could also set the transparency, make it more or less transparent depending on the color and the color of your visuals on the front just to make sure that the data still stands out in front of the image. So that's pretty cool, but how about actually setting an image also for the background of a Cartesian chart over the plot area? So for instance, you could select a visual like this scatter chart, and maybe you want to do something like a little image of a quadrant, something like that, or pick any other random image. For instance, let's go into formatting pane plot area. And let's pick the same image that we were using for the page background. And you can actually have that be the background for the plot area in the visual. So notice how it actually doesn't take the entire background of a visual. It doesn't include the axes and so on. It's just the area where we're plotting the data. Within here you can of course set the transparency. And you can also change the behavior for how the image should fit the space available in this visual. So you can go between normal to fit mode to actually fill mode. Let's just stick to fit mode. And you can see how actually now as we also resize the image, maybe even if you're going to focus more, it resizes accordingly. As I resize the chart within my report page, it also resizes accordingly the plot area image. So that's very cool, another formatting option that we're giving you this month. Let's go back into the list and we are actually now going to talk about improvements within data connectivity. We have quite a few improvements this month around data connectivity. It ranges from support for new capabilities from data transformations to GA support for some of the connectors that we shipped a few months ago as beta preview. So let's dig into this area. The first item this month is an enhancement to DirectQuery mode, which as you know we shipped a few months ago. This month we're actually enabling something that many of our customers have asked in using DirectQuery which is the ability to create measures. Let's go and take a look at how this feature works. I have this workbook, or this Power BI Desktop report, where I already have imported a table, all the details. This table comes from SQL Server, and it actually is connected in DirectQuery mode. So as you can see in the bottom right of the screen, this report is adequately enabled. So now I'll say drag things into the screen, of course. Let's pick, for instance, the product IDs. We're actually running light queries against the underlying SQL Server to actually retrieve the total list of products in this case. And this actually just—well, let's not aggregate that. This doesn't summarize. Here's my entire column width product IDs. So one typical scenario now would be trying to add something like the total sales for all of these products. And that's something that was not possible before with DirectQuery enabled because we actually didn't support the ability to create new measures. Now actually notice how you can access new measure, all of the other modern options are still disabled. We're going to be turning them on over the next few months incrementally, but the first one we started with was new measures. So now from here I can actually create my total sales measure—or actually let's call it total units sold. And that's going to be close to the sum of quantity. So that gives me my new measure, and I can now drag it into the canvas, turn that into a card visual, maybe change the formatting here so that it doesn't automatically aggregate. And it shows me the full number. So that's the 51,000 total units sold that we calculated through a new measure. There's actually a couple of different types of measures when you think about the implications and the impact on a DirectQuery report. The first one which we just saw, our basic measures, these basic measures actually will allow you to do a set of things that we know are safe to execute and right on the server when executing in DirectQuery mode. But there are also a bunch of other measures that you could construct using DAX formulas and DAX expressions that actually could be pretty harmful if you ran them in DirectQuery mode because they could put the underlying data source under a big load in terms of processing of queries. So we actually disabled those measures by default. So for instance, something that could be really expensive could be a total year-to-date calculation. And that's something that we explicitly disabled. So let's go ahead and try to create that new measure. We'll say total year-to-date units. So, and let's actually try and type that function— that dark function—which if you're familiar with it, it's actually total AYTD. So you see that it actually is not recognized. This is why this type of measure, which we're calling unrestricted measures, are not actually enabled by default when working in DirectQuery mode. What you can do instead is go into the file menu and go into the options dialogue. And from here we have a new option, new tab, that's called DirectQuery. This allows you to actually enable this unrestricted measure. So basically it will allow you to create measures without any restrictions on the DAX expression that you used when you were creating the measure. When I turn that on, it's going to ask me to actually restart Power BI Desktop. After restarting Power BI Desktop, you'll be able to start using any other DAX function to create unrestricted measures in DirectQuery mode. Okay, the next feature this month is actually not a new feature but it's a graduation from a better feature or a preview feature which we shipped a couple months ago, which was support for building reports on top of analysis services and multidimensional models in exploration mode. So we shipped that feature as a preview back in November. On top of that feature in December, we actually released hierarchies' support. This month we're actually getting the feature out of preview mode, preview state, and we're making it generally available. This is a huge milestone for us. It means that while there will still be incremental enhancements and future updates to the feature, we have reached the point where we believe that it's an acceptable level of functionality and quality in this feature so that is is not functionally supported as a GA feature. We know that this was a very long requested feature by many of you, and we want to thank you all for trying out the preview feature over the last couple of months. Your feedback and useage of the feature has been extremely valuable for us in order to push this feature to moving forward and make it hit the GA stage. The next feature this month is set up enhancements to hierarchies' support. As we mentioned previously, we released support for hierarchies back in the December update for Analysis Services Multidimensional Models. We released an initial set of capabilities that would allow users to consume hierarchies, but these capabilities lacked the ability to customize which levels of the hierarchies should be added to the report. Instead it was an all or nothing approach. This month we are allowing for flexibility so that users can control which levels from the hierarchies should be added when they configure drill down behaviors and so on. Users can now drag the individual levels that they would like to include in the drill behavior. And Power BI Desktop will automatically include all the levels from within the route of the hierarchy to the level that the user selected. The next feature this month is GA support for SAP HANA connector. So this is another connector that was added a few months ago to Power BI Desktop. And it was added in beta states, and now it actually graduated. So we're very glad to announce that the SAP HANA connector is now GA, and it's fully supported inside Power BI Desktop. And thank you all for trying it out over the last few months and sharing feedback for us so we actually got it to this point. The next feature this month is a new data transformation capability which basically allows you to append multiple tables into a single table in just a single shot. If you think about the append tables or append queries capability that we've had in the past, it would actually allow you to append two tables at a time. So if you had to actually append three or five or ten different tables, you actually had to do it two by two which could actually get pretty painful if you have several tables to actually append. So let's go ahead and take a look at the enhance append queries dialogue that will allow you to do appending multiple tables into a single shot. We're going to go into this workbook that I have which already has three tables on it. It has sales data for a set of products by date for the first three days of the week. So what I would like to do is actually have a single query, a single table, that actually has total sales this week. We can achieve that now by, for instance, referencing the Monday query. And we're actually going to repurpose this one to be called total sales. And now from here, we can go into the append queries dialogue. It shows up here in the real one from the home tab. This is the default mode, the single append or two table append mode, which actually is the one that we've supported for a long time now. And it will actually allow you to take another table to append. So I could pick sales Tuesday and then click okay. And that would give me Monday and Tuesday. But then I would need to do that again to actually append Wednesday to the Monday and Tuesday query. So instead of that, we've enabled this radio button that will let you switch between modes. Now you can go into the three or more tables mode. That will bring up this full fledged UI for actually letting you pick from the list on the left, which is actually all of the available tables, all of the available queries, in your current report file, and add them to the tables to append list. So we started with sales Monday, which is already here. That's a current query. Now we're actually going to select Tuesday and Wednesday, and we're going to add them all here on the right. Notice how you can actually also configure the order. This will append tables from top to bottom in the same order as they are called out in this list. But you could move them up or down. So I could move sales Wednesday up or down depending on what I want to do. If I change my mind and I want to exclude sales Tuesday, I could also do that by clicking remove. I could also add it back. Notice also how I can actually add the same table multiple times in case I want to append it multiple times to the final output. So in this case let's actually stick to just the three queries so I'll move sales Tuesday up. And click okay, and this is actually going to give me the table that has data for all three dates, 18th, 19th, and 20th of January which then I can easily just close and apply and land this area into my report. Notice how actually I kept enable load for this query but I disabled it previously for the other three. So this is something that if you didn't know that's also very useful and convenient for cases where you append intermediate tables so you don't need to load them all. So in this case I just loaded total sales. And from here I can actually do a report by date and product. So let's actually turn this into a color chart and show sales by product. Sales by product and date. So there you go. That's our chart for daily sales. Today we actually didn't sell much because we are still halfway through the day. But you see how easy it is to actually combine data from three or more tables in just one shot. And this is one of the biggest strengths of Power BI Desktop in how it makes it extremely easy for you to combine data across many different data sources. It's not just about joining data or making merge operations, which is basically adding new columns to a table, but also being able to union multiple tables that have the same structure, which is what we call append. And we are actually making that easier and easier with every new release as you can see with this type of usability improvement so please keep your feedback coming that will continue improving all of these capabilities. The next item on the list this month is the ability to refresh data for individual tables from the field list on both the report and the data views. So let's go ahead and take a look at how this works. Before this update if you wanted to refresh data from your report, you actually had to click the refresh button on the home tab in the ribbon which will actually trigger refresh for all of the tables that have been already loaded into the report, which could be really time consuming, especially if you just wanted to refresh just one or two tables instead of the eight tables that I'm refreshing here. In order to improve that experience, we've actually introduced a new option on each of the tables within the fields pane. For instance, if I go into the orders table, I can now click refresh data which will trigger refresh for just that one table. I can do this from either the report view or the data view itself. Or I can also click refresh data. And in this case, refresh can tackle this table really quickly. The next feature this month is a new option to disable data previews to be downloaded in the background. So as you might already know, there's background evaluation and processing of queries within your Power BI Desktop reports so that data previews when you're working in the query editor are readily available to you as you switch to them in the query editor. We've observed and we've received customer feedback that this option actually could be really processing— CPU processing consuming when you open certain reports. So we've actually introduced a new option to disable this downloading of the data previews in the background that you can configure per a specific Power BI Desktop report. So it's a file scope option. It's not a global option but rather it applies to the current file. This new option can be found in the options dialogue, so let's go back and take a look at that. You can go to the file menu, options, and within current file, data load. At the bottom you will find background data, allow data preview to download in the background. So that's a default behavior. If you uncheck this box, you will experience that as you open files that have lots of queries on them, you will not see a spike in CPU usage on your machine and the processing coming from Power BI Desktop process. But on the flip side, you will actually also experience that as you select a specific query in the query area, it will take longer to actually give you the preview because it will be only when you select that query that we will go ahead and try to calculate the data preview for you. So it's a trade-off decision you have to make. We believe this is really very dependent on the nature of the file that you're working with. And that's why we really wanted to introduce this at the file level rather than making it a global option. So just something for you to keep in mind. It's a way to fine tune existing reports if you see that they're getting really complex and consuming in terms of resources in your machine. Great. The last category of improvements this month is about other improvements. So we have actually made two big improvements this month. The first one is that we are making it easier for many people to actually start using Power BI Desktop because we are relaxing the system requirements that we require you to have when you try to install Power BI Desktop. We're actually removing the restriction that the user must have Internet Explorer 10 or above and we're actually lowering it down so that people who have Internet Explorer 9 are also going to be able to start using Power BI Desktop right away. The second big improvement this month, it's about performance. So regardless of the browser version that you're using, we are actually going to see a lot of performance improvements around report rendering especially around things like filtering and cross-highlighting and selection of different elements within your charts. That's also tied to the improvements in the way that Power BI Desktop interacts with your browser for rendering these reports. So really, nonfunctional change but actually pretty important and significant in terms of boosting the usability and performance of the product. And as you know, we continue making these types of changes every month, so if there is something that you are still experiencing a pain point with, don't hesitate to present us a frown. Use our forums in the Power BI community site so that we stay on top of these things and will be able to turn them around quickly. That's all for this month. It's the first one update for this year, and we hope it's going to be a very, very exciting year for us in 2016 with Power BI and Power BI Desktop. Thank you so much for your attention.