Power BI for the Developer: Integrate, Embed, Extend
Myers, Peter
In this session you will learn about the exciting new generation of Power BI delivering access to data and insights with Software as a Service (SaaS), and specifically what can be achieved by the software developer. You will learn that the new generation of Power BI enables developers to configure real-time dashboard scenarios, create custom visualizations and also integrate rich analytics in application experiences by embedding Power BI reports into we apps. Several demonstrations will illustrate how to configure real-time dashboards that programmatically push data to the Power BI service by using the Power BI REST API, and also how to integrate Power BI experiences with applications. This session will be of interest to architects and software developers looking to understand and evaluate the developer capabilities of Power BI.
 So afternoon session on Power BI for the developer, the story on how you may integrate, embed or extend. Very brief introduction on myself, Peter Myers. I'm a business intelligence consultant. I've worked for some 15 years working with Microsoft business intelligence products, mainly with SQL Server delivering enterprise data warehousing solutions, but in more recent years. As Microsoft have reached out into self-service BI, tools and this new generation with Power BI, I've become an expert in this space and I produce the training content from Microsoft and I have the good fortune of sharing the message about what Power BI can do for users and also for developers. So in this 60-minute session, I'm interested to know, first of all, who has already had any experience with Power BI before? And so I'd poll at about one-third of the room and for those one-third, has that been any developer-related activity? Working programmatically with Power BI, two of you out of the third. All right, well, Power BI is a huge topic and 60 minutes would never do justice to the topic. And so I'm going to begin with an introduction that describes the fundamentals about what Power BI can achieve and then focus on the topic relevant to developers about integrate, extend and embed. Need to start somewhere. So the story with business intelligence is what we call a first wave is that businesses needed to get answers from the data that they store. Common business questions needed to be addressed and this was achieved through business intelligence. In the earlier days, back when I started, this meant enterprise data warehousing. It meant IT, long-term projects. And what we discovered is that perhaps 60, 80 percent of the business questions could be addressed through the data warehouse. It could never address every single business question. That arose then a second wave in BI, which was to deliver self-service capabilities to the business users themselves. Probably not to everybody, probably to a small select few. We might call them power users or advanced analysts. And these guys or girls are literally whizzers when it comes to Excel. They drive some amazingly sophisticated solutions. So Microsoft turned around back in the Office 2010 release and they delivered Power Pivot later on Power Query and Power View, supporting very similar capabilities to what the enterprise corporate BI would deliver, but on the desktop handling smaller, more focused projects. What I can describe to you today is that Power BI really delivers a third wave in BI, moving beyond just the analysts that are still reasonably well-skilled and proficient experts to the business users themselves. Those that have the questions can engage directly with a service to answer those questions. All right, so common BI challenges. How do we get an end-to-end perspective across all data, regardless of what type, what format, whether it's on-premises, whether it's in the cloud? And how do we deliver that right data to the right people and at the right time? Well, these are common challenges with business intelligence. And what Power BI delivers is a cloud-based analytics server, or service, excuse me, that is there to address most of those common business challenges. A quick list of key differentiators and attributes of Power BI is that it has software as a service, services available. So with a click and a matter of minutes, if you need your Google analytics data and you need to see it, you need to interact with it. You can then authenticate through Power BI, enable it to connect to Google analytics and build in reports and dashboards, enabling you within perhaps five minutes to start exploring, interacting with your data, and also potentially sharing this with others within the organization. What I'll talk to you and demonstrate today is real-time dashboard updates and how this can be achieved in two distinctly different techniques. The other ability for Power BI is to connect to data not just in the cloud but on-premises as well. So we need to respect that as a cloud-based service, how is it going to interact with your corporate stores? Perhaps your analysis services data models, your SQL server databases on-prem. And so the deal there is that Power BI also comes with gateways, that when installed on-premises, they create a secure channel up to the service, enabling either a scenario of real-time reporting, so it passes straight through to on-prem, results come straight back up, we call that a direct query mode, or if you're going to work with importing of data, you can schedule data refreshers to take place perhaps on an hourly or so basis via the gateways. Data exploration delivered through Q&A, so this is very much the web search engine concept that these days we start asking questions and amazingly, those questions get translated into reasonably decent search results. So Power BI also supports in some cases the ability to ask questions of your data and to have responses delivered back through visualizations. It even extends to the Cortana story, that if you configure it correctly, you can ask Cortana and Cortana will receive the Power BI reports and display them on your device. Integration with many Microsoft products, we'll talk about other Azure integration scenarios, specifically with stream analytics or machine learning or big data. And then finally, the ability to work with hybrid configurations and to work seamlessly within your Azure directories and within your infrastructures. I could really talk all day on those topics, but to finish off with the overview, when it comes to accessing data, it comes in many locations, shapes and formats, whether they are services or on-premises data, whether it's you distributing content within your own internal content packs, connecting to other Azure services, working with data in Excel files, and ultimately the Power BI desktop, which I'll work with today, Power BI desktop ultimately provides you a tool on the desktop enabling you to connect to these variety of sources, load in, integrate if necessary, enrich with exploration and hierarchies, enrich with business logic, build reports, and as a single file, the application is designed to publish it back to the service, whereupon you can build dashboards, configure automatic data refresh, enable Q&A natural querying, and then share this with others so they can access it through the device or the web browser of their choice. Note there, there's a REST API that supports a development story for the creation and manipulation of data. Essentially, it's a push data scenario that can be achieved with the service. Up front, before those questions might be in your mind, how much is this going to cost me? It may cost you absolutely nothing. All right, so there are two licensing tiers. The first is free, and that provides a rather generous one gigabyte of storage and a rather generous set of features, including some dev scenarios that may not cost you anything. If you're looking for the full feature set, and I'll provide you at the end of this presentation some differences in features between the two different tiers, then you'll be looking at the price per month per user. And I know in the US this comes to $10 in your region, I'm not exactly sure. Be aware that if you want to trial this, when you sign up for free, Microsoft are also very generous to provide you with a 60-day trial to allow you to try all of the pro features. All right, now Power BI, huge topic. With the focus just on developers, really, we're going to talk about how we could create real-time dashboards, how we could integrate Power BI into applications, and how we could also develop and integrate custom visuals to extend beyond the already 26 visualizations that come with Power BI. Are there any questions? I've set the scene. All right, let's begin with an activity. I've got a dashboard here, and what I'd like you to do is help and participate by filling in a brief survey. That brief survey will be asking six questions. So would you mind either use the QR code or use your mobile device to navigate to this URL? And what I'll ask you to do is answer the six questions. And my apologies up front. One of them is what city or the nearest city you come from. There are 600 items in a drop-down list. It was just the easiest way to quickly get this out. But I ask you not to submit, to simply fill in answers. They don't have to be true. You may be funny or you may use an alias. No details that you submit will be used against you. So let me explain what's going to happen. That when you submit, it's going to on the web server, and it's using Azure websites, it's going to go ahead and throw an event onto an Azure Service Bus Event Hub, and then Stream Analytics is going to query it live and push it to the dashboard. So don't hit submit yet. There's a nice experience. You'll see it when there's this influx of events coming through. It looks quite good. So what you see here is a very rudimentary dashboard consisting just of static data. No data at this stage. All right. Assuming that nobody else needs the URL I'm going to exit the full screen mode and I'll point out to you that here in the navigation pane, and the navigation pane exposes to me in my workspace the three groupings of fundamental objects in Power BI. Data sets that connect to data, and that full range of sources that I described to you will ultimately be delivered to you as a data set. Data sets are then the foundation upon which reports are built, providing rich interactive experiences, report consisting of multiple pages, and ultimately when you want to share key metrics, we then pin visuals from reports up to dashboards, and then we share the dashboards out. Still not clicking submit at this stage, I would hope. Here I am clicking on the data set. So Azure Stream Analytics has pushed the data set definition through. Take a look on the right-hand side, and you'll see I have a single table and a collection of fields that map to inputs coming from you. What I'm going to do at this stage is just build out a dashboard, and then when the dashboard is being shown, I'll ask you to submit your survey results. So I'll just ask for a single field, which is the number of submissions. And that's shown to me here in a column chart. I'm going to switch it across to a card. I can tell you that five people hit submit. Not a problem. All right, so already six messages have been read from the event hub. And what I'll do, and I have to do this, I need to save this report. So let me just call this demo. And having saved it as a report, I'm then able to pin a visual to a dashboard. All right, and while I'm here, one of the questions was where are you from? So I'm going to build out using a map that's related to all of the cities in that drop-down list with latitude and longitude. So let me bring in lat and long. And I'll just plot the number of submissions. And so I can say that the six people that submitted were coming largely from, I guess that's Oslo, right? And then what I'm going to do is save this and pin this one. And I think I made a slight error before. I erased that one. Let me just reconstruct it. Save. And pin. Now switching across to the dashboard, and I will now remove these URL rolls. In fact, let me just push them to the side. Oops. There is a, excuse me, there is a URL. I didn't mean to click. All right, so let's bring the number of submissions here. And I should have a map of the world somewhere. I'll have two of those. Have the map of the world somewhere. Down here. So a dashboard, a collection of tiles not necessarily being sourced from the same data set. When we drive a car, what we like to see in our dashboard is relevant metrics. We don't want to be overwhelmed by lots of irrelevant data because that distracts us and it's unsafe. So I'd like to see the speed. I'd like to see distance. I'd like to see fuel. And if fuel's running low I would like to see some status indicator like red telling me off track takes some action. And that's essentially what dashboards are designed to express. Let me clean this up a little by editing the tile. It might be nicer here to just call this survey responses. So we can provide titles and subtitles. This would be NDC Oslo 2016. Custom URLs could be set here as well. And then this one would be submissions by city. Before you still hit submit, to be able to demonstrate the Q&A capability I can ask questions of the data itself. So it might be something like show me average distance. So for the eight people that submitted I could learn here that the average distance travel was 1,231 kilometers. And we could make this a little more interesting by breaking this down by occupation. And that looks pretty cool. So let me pin that visual. So here's a second technique by which we can build up dashboards. And let me change that now to gender. And I'll pin it. And I think we also had age group. There it is there. And I could say sort by age group. And for this one I might also customize using the full styling that's available even when reporting. Like for example, let's add some data labels. Let's display them as none with zero decimal places and increase the font size of those. So you can see how rapidly you can build out these dashboards. Switching back to the dashboard page then. This is now what I see for the details that are so far been submitted. Let me then put this into a full screen mode. It's not going to work nicely with these. For those that are ready, why don't you just go ahead and submit your results and see what happens here on the dashboard. Okay, literally real time. With the backing of Azure there, event hubs can ingest millions of events per second. Streaming analytics. And I'll point this out to you. I won't build the solution but I'm going to show you the screens for it. Stream analytics will aggregate across time periods. Perhaps every second take the average, take the sum, take the count and output the result of aggregated events and push them to Power BI. Power BI receives it as a data set. We can build reports on it and the dashboards that are based on this type of data set will reflect real time values. What do you think? Literally no code. Taking the assumption that someone was responsible for the events arriving in the event hub, the story beyond the event hub, listening to events, the streaming analytics is pretty straightforward. Any questions at this stage? So what do we have out of statistics? We have how many people? I should have put numbers on here. So just as an idea, those that are between 95 and 99 traveled on average 11,000 kilometers. I'm thrilled that the people at that senior age would make the effort to come here to Oslo. My home city of Melbourne won, so I know that would be me. And from the people coming from Oslo, how can we just got 120 people a local? Thank you. It gives you a taste of what can be achieved. So let's talk in more detail about how we can extend Power BI, a closer inspection of the rest API, and how you might integrate Power BI with Azure services, the very new and still in preview Azure service called Power BI embedded, and then we'll talk about custom visuals also. So there are four very exciting opportunities that should inspire developers to consider Power BI, whether it's an integration, embed, or extend scenario. Let's start with a discussion then on the Power BI rest API. This rest API has been designed to interrogate Power BI to create data sets, create tables, and to push data to those tables, and when necessary to delete all data from those tables. So it programmatically manages resources, and as you push data into data set tables, they will be reflected real time into the dashboards that you've just seen. So essentially what Stream Analytics is doing is nothing more than what you can do and it's using the rest API to push the results of streaming queries into Power BI data sets. Alright, so in Power BI at the very top level when you authenticate, you're connecting to a workspace, and that could be a personal workspace or it could be a group. So one of the pro features in Power BI is that you can create groups and invite members into groups and collaborate on shared resources. As a best practice, if you're pushing real time data, you shouldn't push it to an individual's account. You would create a group, you'd create a user that is not a real person, you would make them a member of a group, you would push the data set definition to the group, and then you would allow collaboration on that and potential sharing from outside the group. The other benefit of course is that the individual leaves the organization and then you've lost all of that work. So please work with groups and note that groups are a pro feature, so it will require that $10 per user per month. So at that very top level, whether it's a personal workspace but much more preferably that it's a group, it contains a collection of data sets and in turn data sets contain collections of tables and tables contain rows. It's a very simple object model. In order to make this work, you will need to register an application and so if I just open up a web browser like this, there's an app registration tool that when you sign in with your Power BI account, you can provide a name for the app whether it's a server-side web app or a native app, in which case you'll need a redirect URL for web authentication and then tick, tick, tick, and I do not know why these ticks aren't working. Last time I used this a matter of weeks ago, those ticks used to work, so I have no idea. The good news is I don't need to create one. When you register the app, you will get a GUID back and essentially that is what you will use when using the REST API. It will be delegated permissions and the permissions could be whether they're allowed to view certain assets, whether they're allowed to insert or maintain data and you can see the list of them here. From an authentication perspective, depending on whether it's a native client or web app, the flow is somewhat different. I am not going to read through the entire flow but it should not be surprising to anyone else that's working and engaging with authentication on web service calls. A closer look at the operation, so the ability to enumerate, having authenticated and connected to a workspace, what data sets already exist. Data sets are described in terms of a unique identifier, which is a GUID and by friendly name. You can then create a data set that would require a post, an adjacent document describing the data set in terms of the collection of tables and in terms of the collection of columns for each table. The data types are reasonably straightforward. You can also enumerate tables contained within a data set. You can update an existing table schema. I can't think of a great reason why this would happen often but you might decide that we need a new column in a table. So if you do an update schema, hopefully, if you don't break the order of the columns, you can maintain persisted data and potentially add new columns or remove a column if you no longer need it. Add rows to a data set. So this is where you push data up and then you may clear all rows from a table. There's the ability to also list groups. So part of your application experience might well be you'll let the user authenticate and then you'll present them a list of, it's either your workspace you want to publish to or it's a group that you remember. So you can list the groups and then from a group you can call the list data sets. List dashboards and tiles have been added more recently. These are in preview. So this supports another embedding scenario that is that if you get the unique handle for a dashboard or for a report or a tile, you may embed these into other web applications. They will require authentication so you couldn't publicly make those resources available. Just take a quick look, listing all data sets. You'll send a request that looks like this. So you have an endpoint. You will need to add an authentication token and then it'll just basically be a request like this with a response coming back as a JSON document looking like this. There you see each data set in terms of an ID and a name. When it comes to inserting data, a post request, so you'll note what's been built out here is the GUID for the data set that you could have retrieved through an earlier call. It has a collection of tables. Tables have unique names, collections of rows and here you may push a collection of one or more rows up to the data set. Some restrictions. The maximum rows per single push will be 10,000. That just means you're going to chunk things down. If you really have that volume, chunk it down into 10,000 groups. But there is a restriction nonetheless that says that you can only send 10,000 rows per hour if you're on the free license and a million rows per hour if you're on the pro license. So I've noticed that if you take that million and you divide it by the number of seconds in that hour, that's actually the limit. I thought that it might let me do 900,000 in the first minute and maybe 100,000 in the remainder of the hour but it doesn't work that way. So it's actually an average. If you look at it per second, that is the maximum rate you can achieve. If you exceed that, the service will let you know and you will have to expect to handle an error. Maximum rows stored per table will be 5 million. And so at that point you'll end up with errors. You'll be forced to delete the table but there is an answer to this and that is that if you create the table using the basic FIFO method then it will store a maximum of 200,000. And imagine then when you insert the 200 and first record, 200,000 and first, it will store it but it will drop off the very first row. So you're moving forward then with 200,000 rows of data. Maximum pending requests at any stage would be 5. So let's take a look at a real-time dashboard implementation. And I'm not having a good feeling about this but let me see what happens. I'm having some very strange networking issues and certainly when I was setting up hoping that I had another 10 minutes but let's see what happens. One issue is that the Azure portal simply won't load. Let me just try that again. And I have absolutely no explanation for this. I use this VM all the time. And with that not loading, it leaves me with a bad feeling. Anyway, this application's been designed to show you how the API calls work. And while you wouldn't ordinarily wouldn't expose these types of options, they would be in a config file, that registered app, its GUID, would get stored in here. The redirect URI. The other URIs are fixed. So according to the API documentation, these are the endpoints that you'll communicate with for Power BI. Then you will come to connect. And at this stage what it wants to do is authenticate using a Power BI account. And on the first of authentication, it's going to return a token that must be placed in the header of all requests that follow. And I am double finger crossing here. And that's a different error and that's probably for a good reason. It worked perfectly in rehearsal 30 minutes ago. And I was so confident I said I don't need to bring the recordings. So that's a real shame because what it would show you in this tree view is the full structure of the workspace that I connect to, the collection of data sets, reports, dashboards, drill into data sets, there are the tables, create data set, create table. And I would have shown you the whole flow. I'm stuck. I'm sorry. There's absolutely nothing I can do. But I have more to talk about. And I can talk about this scenario. This one does work and this is what supported the opening demonstration with the surveys that you submitted. So the story with Power BI and Azure, there's a growing story with the Azure ecosystem. You can see clearly Microsoft putting Power BI as the presentation layer across the growing number of enterprise services, whether it's Azure SQL Database, Azure SQL Data Warehouse, even big data with HD Insights Spark. And then we see the Azure Machine Learning and we also see the Azure HD Insight with Hadoop. And so already we can connect to many of these Azure services and build Power BI solutions. But really, with a focus on a more developer-centric solution, what I'll share with you is that you can couple certain services together. And so really the recipe of how I delivered the real-time dashboard, just out of interest. No, where is my dashboard page? So with 57 responses coming in, what's driving this? Is that upon submitting your survey, it's adding an event to a service bus event hub. Listening is a stream analytics job that is then pushing an output to Power BI. So what I can show you here is, first of all, I've created and let's just assume the event hubs looked after. That's somebody else's project to push events upon some requirement. Here I've just gone into Azure service bus and I've created an input named Power BI Survey. And essentially it's just reaching and listening across to an event hub named this. Next, there's a query. And for anybody that works with SQL State Pro, I'm going to go ahead and show you anybody that works with SQL statements. This will look rather familiar. I'm selecting from the event details various fields. There's also not a lot but a sufficient number of functions that allow me to transform or cast because essentially those values were in JSON. They need to be cast to their correct type. That's important in Power BI because if we want to perform aggregation and summarization of values, they will need to be numeric. And essentially it's reading from the input that is reading from the event hub. Now, commonly, and not in this example because I want every single survey result to be sent through. But when there's the ingestion of millions of events, you're not interested in projecting those onto a dashboard. You're interested in summarizing them by time periods. So what you would do is add a group by in exactly the same way you would use to summarize a relational query result. And there's a special function there called tumbling or hopping window. And you can say the tumbling window is for every two seconds. So perform this query group by producing aggregate result in two seconds. Whatever events come in, aggregate push out. And the output for this query is defined by the into, into PBI workspace which is defined inside the job. And if we take a closer look at this, it's pushing to Power BI and to a data set name survey to a table name survey. Alright, so if I was to create a new output, nope, okay, the job's running so I can't actually touch it. That's how it worked. And so how much code was involved with that? Providing you had the events on an event hub, and this is a good IOT story, by the way, then it's just a matter of writing the right query, setting up the inputs and output of a job. You will need to when you build the output authenticate to Power BI so some account has to be used. The preference is that it's not going to be your personal account, that you've created a non-human account and that you've created a group that it belongs to and you're pushing the data set to that group. That's real-time dashboards with Azure Stream Analytics. Moving on to the topic then of custom visuals. So let me open up Power BI Desktop. As a free download from Microsoft, be it 64 or 32 bit, this is the companion application for the Power BI service. So its strength is allowing you to connect to a wide range of data sources. Using query logic then a very expressive query language to acquire, filter, shape, cleanse and bring in results. Every query that you bring in can come from a different source and ultimately you can create relationships and there's the integration story going on. And then with the power of DAX, data analysis expressions, you can build business logic, calculator columns, measures, time intelligence functionality and essentially what you're producing here is a model that integrates data, embeds business logic, adds hierarchies for navigation and then you'll visualize that data. So let me just do something very, very simple and that is to bring in some data from a local SQL database. And this is the important thing. You have the ability for certain data sources to import and therefore copy the data into your solution or simply a direct query which says that every request made by a report or indeed a dashboard will flow direct down to the source itself. Now if you stop for a moment and think of the benefits and in fact there are benefits from either approach depending on what you need to achieve. But the direct query opens up the potential for you not to have to be concerned about processing, not to have to be concerned about excessive volumes of data. And the direct query is open for SQL database on premises, analysis services, multi-dimensional or tabular models and in the cloud it's available for three sources that would be Azure SQL database, Azure SQL data warehouse and Azure HD Insights Spark. So you can have real-time querying across big data loads here on Power BI Desktop. Let me just choose for now it actually won't matter too much. I'll go for direct query. There are some limitations with direct query and that is you may not integrate multiple sources together. Your whole solution has to be pointing only at one. So I'll go ahead and connect using my Windows authentication. And then the navigator says well the next level down from a server will be the databases themselves. So if I just bring in something pretty simple like a fact table, factory seller sales, one table, I get a preview and I have the ability to load all of this data which is probably not a smart thing to do on a fact table or use the edit here to refine what I really want. So it presents to me in the query editor then the fact table and I'm going to keep this so simple that all I want to analyze will be sales amount by time so I find that there is an order date column somewhere here. Maybe not. Let me bring in dimry seller. Order date, order date. Okay, in this case we have three date columns because it's what we call a role playing dimension. There's a date dimension in the data warehouse but there are three foreign keys pointing to it. So I make the decision that it's actually the order date. Now what you see here with these links represent a foreign key enabling you in the query editor to introduce columns from that related table. For example, let's bring in the calendar year and let's bring it also the calendar quarter. Now I don't want all columns that are here so if I just bring in those two with a multi-select, right click remove other columns and I've narrowed down all of that data to just the three columns of interest, how about then I rename these to make them friendly. I'll rename quarter year and sales. Over here the applied steps represent from the sourcing of that raw data right down to the desired state that you want. And you could customize this logic. There's a very powerful set of expressions and functions and even the ability to function with parameters in here. For now I'm just going to load this and load wasn't the right term. This is direct query, no data moved. But it has now an understanding that I have a table that we can see here called factory seller sales. So sitting here on the report canvas then I'm going to go ahead and drag year and ask that to be visualized as a slicer. Enabling then filtering interactively on the report, that's 2012 and then I could show sales by quarter. And quarter should go on the axes. Alright so that's the old BI, right? How else might we choose to express the data? Now what you'll notice on the visualizations pane is there's some 26 out of the box but far more interesting and what we'd hope to inspire you guys as developers is to consider that you may create custom visuals and you may be able to integrate them with Power BI. Alright so what I'll do is go to the gallery and what you see here is a contribution both from Microsoft but also from other developers out there that have been generous enough to share with the world what they've developed. That's not to suggest that what you develop has to be published here. You may keep it private but I think one that's just sort of eye-opening and not so traditional is the Enlightened Aquarium. Alright so we have a closer look, we read about this and we say okay well let's see what this will do for our data. Downloading the visual, okay you need to trust whatever you're downloading and what it downloads then is a Power BI visual file. And then very, very simply here on the visualizations click the ellipsis and I'm sorry that that doesn't draw correctly sometimes. It's basically a warning with an okay and then it says well where is that file and then I add it to the toolbox and now we have the aquarium visualization. So perhaps what I do is just keep that bar chart selected and switch it across to become an aquarium. What do you think? Which would you prefer a bar chart or swimming fish? Alright by hovering over a fish he's going to stop and it's going to tell you the amount of sales that were achieved. Let me point out also in the service itself when I'm in the reporting you have a near identical experience here in the web browser. So what you can achieve on Power BI Desktop I can come in here and granted I have permissions and that I own the report I may edit. Let me introduce a second page here and I can do exactly the same thing. Let me go ahead and import that visual and use it to display what the survey results look like with fish. Okay. Or maybe not. Okay I think we know what that will end up looking like. That's the story with custom visuals. The visual gallery is growing week by week and this is Microsoft's approach of saying look we're not taking full responsibility we'll put it out to community and others. But do be aware that the visuals that Microsoft have produced they have made all of the source content available for you on GitHub. So anything that Microsoft publish and is available in Power BI Desktop you can gain access to that provides insights and understanding and you may learn from that and then start designing and developing your own visuals. The last topic that I'll cover is with the new Azure Power BI embedded service. Now this is a service in Azure and there's some confusion already up front. I've just shown you Power BI as a service but what you need to be aware of that when you sign up for Power BI you're using typically your workplace email address. If you've got Office 365 you already have a tenant and Power BI operates within that tenant. If there is not a tenant the first person that signs up will behind the covers create a tenant to support the Power BI and the provisioning of workspaces for individuals and for groups and for sharing content within the organization within the tenant. This service is different. This is an Azure service that allows you to provision your own workspace collections and your own workspaces whereby applications will be using them not individuals not groups. So it is designed to allow you to create compelling and interactive reports to embed these into your web apps and it supports quick and ease of management and deployment. Let's just walk through the key attributes and that is you will still use Power BI Desktop as an authoring tool. The requirement will be that you will build a Power BI Desktop file preferably using the direct query mode and pointing to your data that resides in a cloud source. Presently and in preview it won't support any on-premises sources. No word on where that's happening if that's going to change. So that would mean today that you can connect to Azure SQL Database, Azure SQL Data Warehouse and also Azure HD Insight with Spark. So you would create your Power BI Desktop pointing to those and my attempt to do that failed because this VM isn't reaching the service. I do not know why. Alright, so what's attempting to do is connect and the IP address isn't good but I have definitely used the IP address of this machine but on my host that can reach it and the VM can't. So I'm sorry I'm very stuck. But at this point I'll ask you to use your imaginations where the demo would go. I would come in here and I would open up the Power BI Desktop. Perhaps I developed it against an on-prem SQL Database and by dividing the structures with the same I would point it to the Azure SQL Database with the right connection strings and credentials. I would save and close the file. So you have the full authoring capabilities but do remember that with the Direct Query Mode it's only one source of data that you can connect to. You then can use your own visualizations or the out-of-the-box visualizations to come up with the reports based on the Direct Query representation of your data. You can easily embed interactive visuals into your app using REST APIs and the Power BI SDK. So I mentioned all of the methods. What happens is that you provision an Azure Power BI Embedded Workspace Collection, you create a workspace within it and there's an API that supports the upload of the Power BI Desktop file to that workspace. The next thing that you would need to do is to apply a connection string. Because it will be using embedded credentials they will not move. So the API call will update the connection string to the Power BI Desktop now that it's in the workspace. The next thing is programmatically by making the right calls you can with relative ease add it into an application. So you will need to authenticate by providing a workspace key and an authentication key that will be provided from the Azure portal and then pretty much this just runs. What that means is that when people are interacting with your application embedded within it in exactly the same experience that I'm showing you here on the desktop, let me go back to the fish, whatever you lay out here is exactly what they're going to see in the web app contained within the frame of your application. Your user existing authentication authorization methods within your app and also between the data sources, this does not require you to build or somehow embed analytics and reporting. You need interactive, users need to change filters, need to click on things, touch on things, it all happens through Power BI. And then you're probably asking well how much does this cost? Because it's a very different pricing model to the Power BI that we have as a service whereby individuals and groups work within their own work spaces. So it's currently in preview which means that there isn't a cost associated with it, not while it's in preview, but the discussion at this stage without a commitment from Microsoft is that the first thousand renders per month will be free and for every thousand beyond that it will be $2.50 US. All right, and then you might ask well what is a render? That's a great question. If I've got a report with a filter that allows me to modify year, and let's suggest that I have one, two, three, four visuals every time you modify a year that is potentially four renders going on. And so if you do the mathematics and you think of the number of users, the number of interactions that they have, that will give you an idea. But then again it's not quite right because if they move from one filter to another and back again, your application will have cached this, in which case you're not paying for something that has been previously cached within the same session. So I cannot even give you an idea about how much this might cost, but it's certainly something you can monitor from the Azure portal. That when you look at the Azure Power BI Embedded Service, you will see a collection of workspaces within a workspace you can drill in and you can then understand what the embed renders are at any point in time. Within a minute it reflects the up-to-date number of renders and I guess with every other type of a subscription you could set a cap at some point if you said look that's where we'd like to stop. What was this intended for? Alright, it's intended for a very different purpose with Power BI that I began talking about. While both are relevant for developers the scenario with Power BI Embedded is that you have a need for interactive rich visualizations delivered through reports within a web app and now as a service you may embed this and you will pay per usage not per flat rate per month per features as is the case with the service. Are there any questions about that? Alright, so essentially as a developer you'll go into the Azure portal you'll provision a workspace collection. The next thing is you will start developing a Power BI desktop file using direct query against an Azure source. By the way you could import data but if you publish that to the workspace it's static. But it's not beyond possibilities that you would actually just automate a push. It's not designed to do that but if you then refreshed it on the desktop interactively, opened it, refreshed, saved and closed you could just push it up maybe once a week once a month. But it really is designed to reflect real-time data coming from the application's store which can be achieved through the direct query mode. Alright, so with the workspace collection, provision workspaces upload the Power BI desktop files, configure connection strings and then go ahead and embed into your applications. Any questions? I'm sorry I'm running early without demo space there's not a lot I can do and I'm really really sorry for that because the demos shine. They absolutely do but nothing that I can do without the internet. Let's wrap up with a review and then I'm happy to go to the dashboard and share some information back to you about the survey results that you submitted in. Power BI as a service and take note this is a constantly and rapidly evolving service. Many new features, many new capabilities being delivered month by month but as a cloud based service it's designed to gain or give access to users to data and that data could come in different shapes, sizes, formats, different locations be it a prem, be it cloud. Power BI desktop really brings it all together while you can from the service connect direct to services and get reasonably compelling experiences, Google Analytics, Dynamics CRM online there are built in services and I should point these out to you that provide immediate visibility on your data. But in my thinking and coming from a business intelligence background it's really the Power BI desktop that allows me to do some amazing sophisticated things. I can connect to those very same sources from the desktop, integrate with other sources of data and enrich with logic, rich with reports and publish. Watch this list it's growing week by week with different services. I've demonstrated how that when you have a data set in your workspace you can build reports dashboards and you may use the Q&A natural language as I demonstrated to ask questions. Also be aware that that only works against data sets that are Power BI desktops that have been uploaded or push API scenarios as I've described to you in this session. The two licenses so free you can still work with this in a development scenario but typically it's going to be the Power BI Pro that's going to provide the full feature set to work with the service and certainly when it comes to the volumes of data you might be pushing if you need that one million per hour then it's going to need to be pro. Give consideration also that Power BI when you create these dashboards supports sharing them to others within the organization. And if you have a mix of licenses which I'm finding with customers is quite common then you've got to be careful that if you produce a dashboard that is using any pro feature and you attempt to share that to a non-pro user then they won't have access to it. So the discussions I have with the customers will be think about the pro features and think about the audience that you need to push dashboards out to. There's a complete pricing matrix that says what you get under each tier and I will summarize that here by saying that if you're prepared to pay the $10 US per month per user you'll get 10 gigabytes not one gigabyte of storage. When it comes to a data refresh scenario through the gateways so Power BI desktop uploaded to the service but needs to keep data current from on-premises free and I think this is generous. You can do this once a day at no cost but if you need more frequent updates the support up to 8 per day under the pro license. Relevant for the push scenario with the API is that you can do this for free but no more than 10,000 rows per hour and the streaming rate for pro is a million rows. Beyond that there's nothing really that directly impacts upon the development scenarios that I've spoken about. All the Azure Stream Analytics so the Azure subscription needs to be within the same org as the Power BI tenant. Alright so that's one thing that you would need to work with. Otherwise you would have to build your own service that would read the streaming result and push it using the REST API. So what I hope I've inspired you with in this session develop opportunities to develop what you need. You're not happy with the visualizations go ahead and extend and build your own. You may be not happy with the data that the REST source has supported. Perhaps you have some legacy system that there's no connectors to. Well take on the responsibility as a developer to read from that system and to push the results up and build a service that would manage that for you. Integrate with Azure services and embed with the new Power BI embed service seamlessly into your applications. Some resources that I'll leave you with and I think it's very important to keep your finger on the pulse of what's going on by reading the blog periodically. The reason for this is that the Power BI service itself goes through release every week. One of the frustrations I have is I maintain training content on this and it changes week by week which involves constant rechecking that screenshots haven't changed and so on. This is a great thing because what Microsoft are doing are reflecting the needs of customers. And so I'd like to point out to you at this stage that if you go to here, there's a whole community around Power BI and when people come to me and say can I do X, can I do Y and if not when is it coming? I just direct them straight away here and say if you've got a need or an idea then go ahead and register that. But before you do maybe search on that, you know, people are asking about I need Power Point to have live interactive Power BI embedded into it. And for anyone that could be familiar with PowerView which was a reporting services interactive tool, it did this on-premises with SharePoint and people want it. So what you'll find is that people are putting up those ideas, they're voting against it and you'll see the more people that vote, Microsoft will respond. And here, great news, yes, we've acknowledged this and work has started. So when I read the blogs, I will expect next month or the month after that there's going to be something that says hey, in preview, we have this new feature. And row level security is another one. There were enough requests that it's driven a whole new feature to be added to the to-do list. All right. Also, Power BI Desktop goes through monthly updates. So you need to download and install that to keep Power BI Desktop up to date. The great news about that blog is that they provide you a video and all the new features are described and demonstrated to you. All you need to do is watch it to keep up to date with what's going on there. There's a Power BI Developer Center. So all of the topics that I've shared with you today are documented and examples are available to you here. There's the Power BI Rest API console where they document all of the current API providing examples to you as well. And more news about the Azure Power BI Embedded. It's very new. It's available at this link here. Lastly for the custom visuals, the GitHub project would be pretty valuable. Download, see what Microsoft developed for their very own visuals. And if you simply want to borrow others, then the Power BI Visuals Gallery is available from here. If you haven't done so already, I think about one-third had said that they'd used Power BI at the beginning of the session. Power BI is available for you to sign up for today at no cost. Maybe you stay at no cost. Be aware that there are a couple of limitations when signing up with your email address that you cannot use public domains. So if a hotmail or Gmail won't work, typically it will need to be an org. It couldn't be a.gov and it cannot be a.military. Alright, so there are a couple of restrictions. Others that try to sign up without success find that it's because they're organization of lockdown self-registration. Alright, so there's a blog entry or rather a documentation article that will talk about the possible problems and the fixes for those problems. But otherwise I find that most of my customers have no problem going in there, signing up for free and going up and taking the 60-day trial to experiment with the Pro feature set. Alright, we have four minutes left. Are there any questions that I could answer at this stage? You just have one. What is your question? You just have one. What is your question? Normally when you create an important RBI, we share that you are able to access that report. So what happened if I sent this order to my colleague at the email and he had to forward this to us? So what you're describing there is a scenario which is called and sorry let me just show you under reports. With a report you could publish to web. Is that what you're describing? Yeah, if you have the exact you would allow it to be. Do you need an application to somebody? That depends. There's two scenarios. So if it's sharing a report, you could look up through the API and get the details required to embed it in an authenticated scenario. Or this that's still in preview and in fact I didn't mention is what they're building out is the fact for you to publish an interactive report to the web that would be publicly available. You can experiment with that today. What you will get is an embed code and you may manage those embed codes. So for example you gave it to somebody and you want to revoke that. What you'd have to do is delete the code and regenerate it and then hand it to the people that you intend to keep that code. So watch this story moving forward. I didn't mention it because there's still a lot of lack of clarity around it and certainly with pricing. If they're going to allow something to be anonymously accessed, imagine you place it on a blog and that blog entry goes viral and a thousand people are trying to hit it. I don't know what Microsoft are thinking here in terms of pricing because everything in the Power BI service that I've described earlier in this session is a flat rate per month. So all of a sudden we've got a feature that looks like it's going to be on some billable unit. All right. So does that help answer your question? All right. Are there any other questions? You're welcome to, if you would like to, interact with this dashboard by submitting events. And thank you very much for your time and attendance and patience. I do apologize for connectivity issues but I'd encourage you to go to PowerBI.com. There are a lot of videos backing up and describing the feature sets that I've mentioned today. Thank you and all the best with your Power BI adventures. Thank you. Thank you. Thank you.
 So afternoon session on Power BI for the developer,  the story on how you may integrate, embed or extend.  Very brief introduction on myself, Peter Myers.  I'm a business intelligence consultant.  I've worked for some 15 years working with Microsoft  business intelligence products,  mainly with SQL Server delivering enterprise data  warehousing solutions, but in more recent years.  As Microsoft have reached out into self-service BI,  tools and this new generation with Power BI,  I've become an expert in this space and I produce the training  content from Microsoft and I have the good fortune of sharing  the message about what Power BI can do for users and also for  developers.  So in this 60-minute session, I'm interested to know,  first of all, who has already had any experience with Power BI  before?  And so I'd poll at about one-third of the room and for those  one-third, has that been any developer-related activity?  Working programmatically with Power BI,  two of you out of the third.  All right, well, Power BI is a huge topic and 60 minutes would  never do justice to the topic.  And so I'm going to begin with an introduction that describes  the fundamentals about what Power BI can achieve and then focus  on the topic relevant to developers about integrate,  extend and embed.  Need to start somewhere.  So the story with business intelligence is what we call a  first wave is that businesses needed to get answers from the  data that they store.  Common business questions needed to be addressed and this was  achieved through business intelligence.  In the earlier days, back when I started,  this meant enterprise data warehousing.  It meant IT, long-term projects.  And what we discovered is that perhaps 60, 80 percent of the  business questions could be addressed through the data warehouse.  It could never address every single business question.  That arose then a second wave in BI,  which was to deliver self-service capabilities to the business  users themselves.  Probably not to everybody, probably to a small select few.  We might call them power users or advanced analysts.  And these guys or girls are literally whizzers when it comes  to Excel.  They drive some amazingly sophisticated solutions.  So Microsoft turned around back in the Office 2010 release and  they delivered Power Pivot later on Power Query and Power View,  supporting very similar capabilities to what the enterprise  corporate BI would deliver, but on the desktop handling smaller,  more focused projects.  What I can describe to you today is that Power BI really delivers  a third wave in BI, moving beyond just the analysts that are  still reasonably well-skilled and proficient experts to the  business users themselves.  Those that have the questions can engage directly with a service  to answer those questions.  All right, so common BI challenges.  How do we get an end-to-end perspective across all data,  regardless of what type, what format, whether it's on-premises,  whether it's in the cloud?  And how do we deliver that right data to the right people and  at the right time?  Well, these are common challenges with business intelligence.  And what Power BI delivers is a cloud-based analytics server,  or service, excuse me, that is there to address most of those  common business challenges.  A quick list of key differentiators and attributes of Power BI  is that it has software as a service, services available.  So with a click and a matter of minutes, if you need your Google  analytics data and you need to see it, you need to interact with  it. You can then authenticate through Power BI,  enable it to connect to Google analytics and build in reports  and dashboards, enabling you within perhaps five minutes to  start exploring, interacting with your data, and also  potentially sharing this with others within the organization.  What I'll talk to you and demonstrate today is real-time  dashboard updates and how this can be achieved in two distinctly  different techniques.  The other ability for Power BI is to connect to data not just  in the cloud but on-premises as well.  So we need to respect that as a cloud-based service, how is it  going to interact with your corporate stores?  Perhaps your analysis services data models, your SQL server  databases on-prem.  And so the deal there is that Power BI also comes with gateways,  that when installed on-premises, they create a secure channel up  to the service, enabling either a scenario of real-time reporting,  so it passes straight through to on-prem, results come straight  back up, we call that a direct query mode, or if you're going to  work with importing of data, you can schedule data refreshers to  take place perhaps on an hourly or so basis via the gateways.  Data exploration delivered through Q&A, so this is very much  the web search engine concept that these days we start asking  questions and amazingly, those questions get translated into  reasonably decent search results.  So Power BI also supports in some cases the ability to ask  questions of your data and to have responses delivered back  through visualizations.  It even extends to the Cortana story, that if you configure it  correctly, you can ask Cortana and Cortana will receive the Power  BI reports and display them on your device.  Integration with many Microsoft products, we'll talk about other  Azure integration scenarios, specifically with stream analytics  or machine learning or big data.  And then finally, the ability to work with hybrid configurations  and to work seamlessly within your Azure directories and within  your infrastructures.  I could really talk all day on those topics, but to finish off  with the overview, when it comes to accessing data, it comes in  many locations, shapes and formats, whether they are services  or on-premises data, whether it's you distributing content within  your own internal content packs, connecting to other Azure services,  working with data in Excel files, and ultimately the Power BI  desktop, which I'll work with today, Power BI desktop ultimately  provides you a tool on the desktop enabling you to connect to  these variety of sources, load in, integrate if necessary,  enrich with exploration and hierarchies, enrich with business  logic, build reports, and as a single file, the application is  designed to publish it back to the service, whereupon you can build  dashboards, configure automatic data refresh, enable Q&A natural  querying, and then share this with others so they can access it  through the device or the web browser of their choice.  Note there, there's a REST API that supports a development story  for the creation and manipulation of data.  Essentially, it's a push data scenario that can be achieved with the service.  Up front, before those questions might be in your mind, how much is this  going to cost me? It may cost you absolutely nothing.  All right, so there are two licensing tiers. The first is free,  and that provides a rather generous one gigabyte of storage and a  rather generous set of features, including some dev scenarios that  may not cost you anything. If you're looking for the full feature set,  and I'll provide you at the end of this presentation some differences in  features between the two different tiers, then you'll be looking at the  price per month per user. And I know in the US this comes to  $10 in your region, I'm not exactly sure.  Be aware that if you want to trial this, when you sign up for free,  Microsoft are also very generous to provide you with a 60-day trial  to allow you to try all of the pro features.  All right, now Power BI, huge topic. With the focus just on developers, really,  we're going to talk about how we could create real-time dashboards, how we could  integrate Power BI into applications, and how we could also  develop and integrate custom visuals to extend beyond the already  26 visualizations that come with Power BI.  Are there any questions? I've set the scene.  All right, let's begin with an activity.  I've got a dashboard here,  and what I'd like you to do is help and participate  by filling in a brief survey. That brief  survey will be asking six questions. So would you mind either use the QR code  or use your mobile device to navigate to this URL?  And what I'll ask you to do is answer the six questions.  And my apologies up front. One of them is what city or the nearest city you come from.  There are 600 items in a drop-down list. It was just the easiest way to  quickly get this out. But I ask you not to submit, to simply fill  in answers. They don't have to be true. You may be funny  or you may use an alias. No details that you  submit will be used against you. So let me explain what's going to happen.  That when you submit, it's going to  on the web server, and it's using Azure websites, it's going to go ahead  and throw an event onto an Azure Service Bus  Event Hub, and then Stream Analytics is going to query it live  and push it to the dashboard. So don't hit submit yet.  There's a nice experience. You'll see it when there's this influx  of events coming through. It looks quite good.  So what you see here is a very rudimentary dashboard  consisting just of static data. No data at this  stage. All right. Assuming that nobody else needs the URL  I'm going to exit the full screen mode and I'll point out to you that  here in the navigation pane, and the navigation  pane exposes to me in my workspace the three groupings of fundamental  objects in Power BI. Data sets that connect to data, and that  full range of sources that I described to you will ultimately  be delivered to you as a data set. Data sets are then the foundation upon  which reports are built, providing rich interactive experiences,  report consisting of multiple pages, and ultimately when you want to share  key metrics, we then pin visuals from reports  up to dashboards, and then we share the dashboards out.  Still not clicking submit at this stage, I would hope. Here I am  clicking on the data set. So Azure Stream Analytics has pushed  the data set definition through. Take a look on the right-hand side, and you'll see  I have a single table and a collection of fields that map to inputs  coming from you. What I'm going to do at this stage is just build out a  dashboard, and then when the dashboard is being shown, I'll ask you to submit  your survey results. So I'll just  ask for a single field, which is the number of submissions.  And that's shown to me here in a column chart. I'm going to switch it across to  a card. I can tell you that five people hit submit.  Not a problem. All right, so already six messages  have been read from the event hub.  And what I'll do, and I have to do this, I need to save this report. So let me just call this  demo. And having saved it as a report,  I'm then able to pin a visual to a dashboard.  All right, and while I'm here,  one of the questions was where are you from? So I'm going to build out using a map  that's related to  all of the cities in that  drop-down list with latitude and longitude. So let me bring in lat and long.  And I'll just plot the number of submissions. And so I can say that the six  people that submitted were coming largely from, I guess that's  Oslo, right? And then what I'm going to do is save this  and pin this one.  And I think I made a slight error before.  I erased that one.  Let me just reconstruct it. Save.  And pin.  Now switching across to the dashboard, and I will now remove  these URL rolls. In fact, let me just push them to the  side. Oops.  There is a, excuse me, there is a URL.  I didn't mean to click.  All right, so let's bring the number of submissions here. And I should have a map of the world  somewhere. I'll have two of those.  Have the map of the world somewhere.  Down here. So a dashboard, a collection of tiles  not necessarily being sourced from the same  data set. When we drive a car, what we like  to see in our dashboard is relevant metrics. We don't want to be overwhelmed by  lots of irrelevant data because that distracts us and it's unsafe. So I'd like  to see the speed. I'd like to see distance. I'd like to see fuel. And if fuel's running low  I would like to see some status indicator like red telling me off track  takes some action. And that's essentially what dashboards are designed to express.  Let me clean this up a little by editing the tile.  It might be nicer here to just call this survey responses.  So we can provide  titles and subtitles. This would be  NDC Oslo 2016. Custom URLs could be set here as well.  And then this one would be submissions by city.  Before you still hit submit, to be able to demonstrate the Q&A capability  I can ask questions of the data itself. So it might be something  like show me average  distance. So for the eight people that submitted  I could learn here that the average distance travel was 1,231 kilometers.  And we could make this a little more interesting by breaking this down by occupation.  And that looks pretty cool.  So let me pin that visual. So here's a second technique by which we can build  up dashboards.  And let me change that now to gender.  And I'll pin it.  And I think we also had age group.  There it is there.  And I could say sort by age group.  And for this one I might also customize using  the full styling that's available even when reporting. Like for example, let's  add some data labels. Let's display them as none  with zero decimal places and increase the font size of those.  So you can see how rapidly you can build  out these dashboards. Switching back to the dashboard page then.  This is now what I see  for the details that are so far been submitted.  Let me then put this into a full screen mode.  It's not going to work nicely with these. For those that are ready, why don't  you just go ahead and submit your results and see what happens here on the dashboard.  Okay, literally real time.  With the backing of Azure there, event hubs can ingest millions  of events per second. Streaming analytics.  And I'll point this out to you. I won't build the solution but I'm going to show you the screens for it.  Stream analytics will aggregate across time periods. Perhaps  every second take the average, take the sum, take the count and output  the result of aggregated events and push them to Power BI.  Power BI receives it as a data set. We can build reports on it and the dashboards  that are based on this type of data set will reflect real time values.  What do you think? Literally no code.  Taking the assumption that someone was responsible for the events arriving in the event hub,  the story beyond the event hub, listening to events,  the streaming analytics is pretty straightforward. Any questions at this stage?  So what do we have out of statistics?  We have how many people? I should have put numbers on here.  So just as an idea, those that are between 95 and 99  traveled on average 11,000 kilometers. I'm thrilled  that the people at that senior age would make the  effort to come here to Oslo. My home city of Melbourne  won, so I know that would be me. And from the people coming from Oslo,  how can we just got 120 people a local?  Thank you. It gives you a taste of what  can be achieved. So let's talk in more detail about how we can extend  Power BI, a closer inspection of the rest API,  and how you might integrate Power BI with Azure services, the very  new and still in preview Azure service called Power BI  embedded, and then we'll talk about custom visuals also.  So there are four very exciting opportunities that should inspire  developers to consider Power BI, whether it's an integration,  embed, or extend scenario. Let's start with a discussion  then on the Power BI rest API. This rest API has been designed  to interrogate Power BI to create data sets,  create tables, and to push data to those tables,  and when necessary to delete all data from those tables.  So it programmatically manages resources, and as you  push data into data set tables, they will be reflected  real time into the dashboards that you've just seen.  So essentially what Stream Analytics is doing is nothing more than what you can do  and it's using the rest API to push the results of streaming queries  into Power BI data sets.  Alright, so in Power BI at the very top level  when you authenticate, you're connecting to a workspace, and that could  be a personal workspace or it could be a group. So one of the pro  features in Power BI is that you can create groups and invite members into  groups and collaborate on shared resources. As a best  practice, if you're pushing real time data, you shouldn't push it to an individual's account.  You would create a group, you'd create a user that is not a real  person, you would make them a member of a group, you would push  the data set definition to the group, and then you would allow  collaboration on that and potential sharing from outside the group. The other benefit  of course is that the individual leaves the organization and then you've lost  all of that work. So please work with groups and note that groups are a pro  feature, so it will require that $10 per user per month.  So at that very top level, whether it's a personal workspace but much  more preferably that it's a group, it contains a collection of data  sets and in turn data sets contain collections of tables and tables  contain rows. It's a very simple object model.  In order to make this work, you will need to register an application  and so if I just open up a web browser  like this, there's an app registration tool that  when you sign in with your Power BI account, you can provide a name for the app  whether it's a  server-side web app or a native app, in which case you'll need  a redirect URL for web authentication and then tick, tick, tick, and I do not  know why these ticks aren't working. Last time I  used this a matter of weeks ago, those ticks used to  work, so I have no idea. The good news is I don't need to create one.  When you register the app, you will get a GUID back  and essentially that is what you will use when using  the REST API. It will be delegated  permissions and the permissions could be  whether they're allowed to view certain assets, whether they're allowed to insert or maintain  data and you can see the list of them here.  From an authentication  perspective, depending on whether it's a native client or web app, the flow  is somewhat different. I am not going to read through the entire flow  but it should not be surprising to anyone else that's working and engaging with authentication  on web service calls.  A closer look at the  operation, so the ability to enumerate, having authenticated  and connected to a workspace, what data sets already exist.  Data sets are described in terms of a unique identifier, which is a GUID  and by friendly name. You can then create a data set that would  require a post, an adjacent document describing the data set  in terms of the collection of tables and in terms of the collection of columns for  each table. The data types are reasonably straightforward.  You can also enumerate tables contained within a data set. You can  update an existing table schema. I can't think of a great reason  why this would happen often but you might decide that we need a new  column in a table. So if you do an update schema,  hopefully, if you don't break the order of the columns, you can  maintain persisted data and potentially add new columns or  remove a column if you no longer need it.  Add rows to a data set. So this is where you push data up  and then you may clear all rows from a table.  There's the ability to also list groups. So part of your application experience  might well be you'll let the user authenticate and then you'll present  them a list of, it's either your workspace you want to publish to or it's a group that you  remember. So you can list the groups and then from a group you can call the list  data sets. List dashboards and tiles have been added more recently.  These are in preview. So this supports another embedding scenario  that is that if you get the unique handle for a dashboard or for  a report or a tile, you may embed these into other web applications.  They will require authentication so you couldn't  publicly make those resources available.  Just take a quick look, listing all data sets. You'll send a request that looks  like this. So you have an endpoint. You will need  to add an authentication token and then it'll just basically  be a request like this with a response coming back  as a JSON document looking like this. There you see each data set  in terms of an ID and a name. When it comes to inserting  data, a post request, so you'll note what's been built  out here is the GUID for the data set that you could have retrieved through  an earlier call. It has a collection of tables. Tables have unique  names, collections of rows and here you may push  a collection of one or more rows up to the data set.  Some restrictions. The  maximum rows per single push will be 10,000. That just means you're going to chunk  things down. If you really have that volume, chunk it down into 10,000  groups. But there is a restriction nonetheless that says that  you can only send 10,000 rows per hour if you're on the free license  and a million rows per hour if you're on the pro license. So I've noticed  that if you take that million and you divide it by the number of seconds  in that hour, that's actually the limit. I thought that it might let me do  900,000 in the first minute and maybe 100,000 in the remainder of the hour  but it doesn't work that way. So it's actually an average. If you look at it per second,  that is the maximum rate you can achieve. If you exceed that, the service will let you know  and you will have to expect to handle an error. Maximum rows stored  per table will be 5 million. And so at that point  you'll end up with errors. You'll be forced to delete the table but there is an answer to this  and that is that if you create the table using the basic FIFO method  then it will store a maximum of 200,000. And imagine then when you  insert the 200 and first record, 200,000 and first,  it will store it but it will drop off the very first row. So you're moving forward  then with 200,000 rows of data. Maximum pending  requests at any stage would be 5.  So let's take a look at a real-time  dashboard implementation.  And I'm not  having a good feeling about this but let me see what happens.  I'm having some very strange networking issues and certainly when I was setting up  hoping that I had another 10 minutes but let's see what happens.  One issue is that the Azure portal simply  won't load. Let me just try that again.  And I have absolutely  no explanation for this. I use this VM all the time.  And with that not loading, it leaves me with a bad  feeling. Anyway, this application's been designed to  show you how the API calls work. And while you wouldn't ordinarily  wouldn't expose these types of options, they would be in a config file,  that registered app, its GUID, would get stored in here.  The redirect URI. The other URIs are fixed.  So according to the API documentation, these are the endpoints that you'll communicate with  for Power BI. Then you will  come to connect. And at this stage what it wants to do is authenticate  using a Power BI account. And on  the first of authentication, it's going to return a token that must be placed  in the header of all requests that follow.  And I am double finger crossing here.  And that's a different error and that's probably for a good reason.  It  worked perfectly in rehearsal 30 minutes ago.  And I was so confident I said I don't need  to bring the recordings. So that's a real shame  because what it would show you in this tree view is the full structure of the workspace that I  connect to, the collection of data sets, reports,  dashboards, drill into data sets, there are the tables, create  data set, create table. And I would have shown you the whole flow.  I'm stuck. I'm sorry. There's absolutely nothing I can do.  But I have more to talk about. And I can talk about this scenario.  This one does work and this is what supported the opening demonstration with the  surveys that you submitted. So the story with Power BI and Azure,  there's a growing story with the Azure ecosystem.  You can see clearly Microsoft putting Power BI as the presentation  layer across the growing number of enterprise services,  whether it's Azure SQL Database, Azure SQL Data Warehouse, even big data  with HD Insights Spark. And then we see the  Azure Machine Learning and we also see the  Azure HD Insight with Hadoop.  And so already we can connect to many of these Azure services  and build Power BI solutions. But really, with a focus on a more  developer-centric solution, what I'll share with you is  that you can couple certain services together.  And so really the recipe of how I delivered the real-time dashboard,  just out of interest.  No, where is my dashboard page?  So with 57 responses coming in, what's driving this?  Is that upon submitting your survey, it's adding an event to  a service bus event hub. Listening is a stream analytics  job that is then pushing an output to Power BI.  So what I can show you here is,  first of all, I've created and let's just assume the event hubs looked after.  That's somebody else's project to push events upon some  requirement. Here I've just gone into  Azure service bus and I've created an input  named Power BI Survey. And essentially it's just  reaching and listening across to an event hub named this.  Next, there's a query. And for anybody that works with  SQL State Pro, I'm going to go ahead and show you  anybody that works with SQL statements. This will look rather familiar.  I'm selecting from the event details  various fields. There's also not a lot but a sufficient  number of functions that allow me to transform or cast because essentially  those values were in JSON. They need to be cast to their correct type.  That's important in Power BI because if we want to perform aggregation  and summarization of values, they will need to be numeric.  And essentially it's reading from the input that is reading  from the event hub. Now, commonly,  and not in this example because I want every single survey result  to be sent through. But when there's the ingestion of millions of events,  you're not interested in projecting those onto a dashboard. You're interested in summarizing  them by time periods. So what you would do is add a group by in exactly  the same way you would use to summarize a relational query result. And there's  a special function there called tumbling or hopping window. And you can say the tumbling  window is for every two seconds. So perform this query group by producing  aggregate result in two seconds. Whatever events come in, aggregate  push out. And the output for this query is defined by  the into, into PBI workspace which is  defined inside the job. And if we take a closer look at  this, it's pushing to Power BI and to a data set name survey  to a table name survey. Alright, so if I was to create  a new output,  nope, okay, the job's running so I can't actually touch it.  That's how it worked. And so how much code was involved with that? Providing you had  the events on an event hub, and this is a good IOT story, by the way,  then it's just a matter of writing the right query, setting up the inputs and  output of a job. You will need to when you build the output authenticate to Power BI  so some account has to be used. The preference is that it's not going to be your personal  account, that you've created a non-human account  and that you've created a group that it belongs to and you're pushing the data  set to that group.  That's real-time dashboards with Azure Stream Analytics. Moving  on to the topic then of custom visuals. So let me open up  Power BI Desktop.  As a free download from Microsoft, be it 64 or 32  bit, this is the companion application for the Power BI service.  So its strength is allowing you to connect to  a wide range of data sources.  Using query logic  then a very expressive query language to acquire,  filter, shape, cleanse and bring in results.  Every query that you bring in can come from a different source and ultimately  you can create relationships and there's the integration story going on.  And then with the power of DAX, data analysis expressions, you can build business  logic, calculator columns, measures, time intelligence functionality  and essentially what you're producing here is a model that integrates  data, embeds business logic, adds  hierarchies for navigation and then you'll visualize that data.  So let me just do something very, very simple and that is to bring in some data from a  local SQL database.  And this is the important thing. You have the ability for certain data sources  to import and therefore copy the data into your solution  or simply a direct query which says that every request made by  a report or indeed a dashboard will flow direct down to the source  itself. Now if you stop for a moment and think of the benefits and in fact there are  benefits from either approach depending on what you need to achieve.  But the direct query opens up the potential for you not to have to be concerned about  processing, not to have to be concerned about excessive volumes of data.  And the direct query is open for SQL database  on premises, analysis services, multi-dimensional or tabular models  and in the cloud it's available for three sources that would be Azure  SQL database, Azure SQL data warehouse and Azure HD Insights Spark.  So you can have real-time querying across big data loads here on  Power BI Desktop. Let me just choose for now  it actually won't matter too much. I'll go for direct query. There are some  limitations with direct query and that is you may not integrate multiple sources together.  Your whole solution has to be pointing only at one. So  I'll go ahead and connect using my Windows authentication.  And then the navigator says well  the next level down from a server will be the databases  themselves. So if I just bring in something pretty simple like  a fact table, factory seller sales,  one table, I get a preview and I have the ability to load all of this  data which is probably not a smart thing to do on a fact table or use  the edit here to refine what I really want.  So it presents to me in the query editor then the fact table  and I'm going to keep this so simple that all I want to analyze will be  sales amount by time  so I find that there is an order date column somewhere here.  Maybe not.  Let me bring in dimry seller.  Order date, order date.  Okay, in this case we have three  date columns because it's what we call a role playing dimension.  There's a date dimension in the data warehouse but there are three foreign keys pointing to it.  So I make the decision that it's actually the order date.  Now what you see here with these links represent a foreign key enabling you  in the query editor to introduce columns from that related table.  For example, let's bring in the calendar year and let's bring  it also the calendar quarter.  Now I don't want all columns that are here so if I just bring  in those two with a multi-select, right click  remove other columns and I've narrowed down all of that data to  just the three columns of interest, how about then I rename these to make  them friendly.  I'll rename  quarter year  and sales.  Over here the applied steps represent from  the sourcing of that raw data right down to the desired state  that you want. And you could customize this logic. There's a very  powerful set of expressions and functions and even the ability to function  with parameters in here. For now I'm just going to load this  and load wasn't the right term. This is direct query, no data  moved. But it has now an understanding that I have a table that we can see  here called factory seller sales. So sitting here on the report canvas  then I'm going to go ahead and drag year and ask that  to be visualized as a slicer. Enabling  then filtering interactively on the report, that's 2012  and then I could show sales by quarter.  And quarter  should go on the axes. Alright so that's the old BI, right?  How else might we choose to express the data?  Now what you'll notice on the visualizations pane is there's some 26 out of the box  but far more interesting and what we'd hope to inspire you guys  as developers is to consider that you may create custom visuals  and you may be able to integrate them with Power BI.  Alright so what I'll do is go to the gallery  and what you see here is a contribution both from  Microsoft but also from other developers out there that have been generous enough to share  with the world what they've developed. That's not to suggest that what you  develop has to be published here. You may keep it private  but I think one that's just sort of eye-opening and not so traditional  is the Enlightened Aquarium. Alright so we have a closer  look, we read about this and we say okay well let's see what this will do for our data.  Downloading the visual, okay you need  to trust whatever you're downloading  and what it downloads then is a Power BI visual file.  And then very, very simply  here on the visualizations click the ellipsis  and I'm sorry that that doesn't draw correctly sometimes.  It's basically a warning with an okay and then it says  well where is that file  and then I add it to the  toolbox and now we have  the aquarium visualization. So perhaps what I do  is just keep that bar chart selected and switch it across to become  an aquarium. What do you think? Which would you prefer  a bar chart or swimming fish?  Alright by hovering over a fish he's going to stop  and it's going to tell you the amount of sales that were achieved. Let me point out  also in the service itself when I'm in the reporting  you have a near identical experience here in the web browser. So  what you can achieve on Power BI Desktop I can come in here and  granted I have permissions and that I own the report I may edit. Let me  introduce a second page here and I can do exactly the same thing.  Let me go ahead and import that visual and use it  to display what the survey results look like with fish.  Okay.  Or maybe not.  Okay I think we know what that will end up looking like.  That's the story with custom visuals. The visual gallery is growing  week by week and this is Microsoft's approach of  saying look we're not taking full responsibility we'll put it out to community and others.  But do be aware that the visuals that Microsoft have produced  they have made all of the source  content available for you on GitHub. So anything that Microsoft publish  and is available in Power BI Desktop you can gain access to that provides  insights and understanding and you may learn from that and then  start designing and developing your own visuals.  The last topic that I'll cover is with the new Azure Power BI embedded  service. Now this is a service in Azure and there's some confusion  already up front. I've just shown you Power BI as a service  but what you need to be aware of that when you sign up for Power BI you're using  typically your workplace email address. If you've got Office 365  you already have a tenant and Power BI operates within that tenant. If there is  not a tenant the first person that signs up will behind the covers create  a tenant to support the Power BI and the  provisioning of workspaces for individuals and for groups and for sharing  content within the organization within the tenant.  This service is different. This is an Azure service that allows you to provision  your own workspace collections and your own workspaces whereby  applications will be using them not individuals not groups.  So it is designed to allow you to create compelling and interactive  reports to embed these into your web apps and it  supports quick and ease of management and deployment. Let's just walk  through the key attributes and that is you will still use Power BI Desktop  as an authoring tool. The requirement will be that you will build a Power BI  Desktop file preferably using the direct query mode and pointing  to your data that resides in a cloud source. Presently  and in preview it won't support any on-premises sources. No word  on where that's happening if that's going to change. So that would mean today that you  can connect to Azure SQL Database, Azure SQL Data Warehouse  and also Azure HD Insight with Spark. So you would create  your Power BI Desktop pointing to those and my attempt to do  that failed because this VM  isn't reaching the service. I do not know why. Alright, so  what's attempting to do is connect and the IP address isn't  good but I have definitely used the IP address of this machine  but on my host that can reach it and the VM can't. So I'm  sorry I'm very stuck. But at this point I'll ask you to use your imaginations  where the demo would go. I would come in here and I would open up the Power BI  Desktop. Perhaps I developed it against an on-prem SQL Database  and by dividing the structures with the same I would point it to the  Azure SQL Database with the right connection strings and credentials. I would save  and close the file. So you have the full authoring capabilities  but do remember that with the Direct Query Mode  it's only one source of data that you can connect to.  You then can use your own visualizations or the out-of-the-box visualizations  to come up with the reports based on the Direct Query  representation of your data. You can easily embed interactive  visuals into your app using REST APIs and the Power BI SDK.  So I mentioned all of the methods. What happens is that you provision an  Azure Power BI Embedded Workspace Collection, you create a  workspace within it and there's an API that supports the upload of  the Power BI Desktop file to that workspace. The next thing that you would need  to do is to apply a connection string. Because  it will be using embedded credentials they will not move. So the API call  will update the connection string to the Power BI Desktop now that it's in the  workspace.  The next thing is programmatically by making the right calls  you can with relative ease add it into an application. So you will need  to authenticate by providing a workspace key and an authentication key  that will be provided from the Azure portal and then pretty much this just  runs. What that means is that when people are interacting with your application  embedded within it in exactly the same experience that I'm showing  you here on the desktop, let me go back to the fish,  whatever you lay out here is exactly what they're going to see in the web app  contained within the frame of your application.  Your user existing authentication authorization methods within your  app and also between the data sources, this does not require you to  build or somehow embed analytics and reporting. You need  interactive, users need to change filters, need to click on things, touch on things,  it all happens through Power BI. And then you're probably asking well how much  does this cost? Because it's a very different pricing model to the Power BI  that we have as a service whereby individuals and groups work within their own  work spaces. So it's currently in preview which means that there isn't a cost  associated with it, not while it's in preview, but the discussion  at this stage without a commitment from Microsoft is that the first thousand  renders per month will be free and for every  thousand beyond that it will be $2.50 US.  All right, and then you might ask well what is a render? That's a great question.  If I've got a report with a filter that allows me to modify year, and  let's suggest that I have one, two, three, four visuals  every time you modify a year that is potentially  four renders going on. And so if you do the mathematics and you think of the number  of users, the number of interactions that they have, that will give you an  idea. But then again it's not quite right because  if they move from one filter to another and back again,  your application will have cached this, in which case you're not paying for something  that has been previously cached within the same session. So I cannot even give you an  idea about how much this might cost, but it's certainly  something you can monitor from the Azure portal. That when you look at the Azure Power BI  Embedded Service, you will see a collection of workspaces within a workspace  you can drill in and you can then understand what the embed renders are  at any point in time. Within a minute it reflects the up-to-date number of renders  and I guess with every other type of a subscription you could set a cap at some point  if you said look that's where we'd like to stop. What was this intended  for? Alright, it's intended for a very different purpose  with Power BI that I began talking about. While both are relevant for developers  the scenario with Power BI Embedded is that you have a need for interactive  rich visualizations delivered through reports within a web app  and now as a service you may embed this and you will pay per usage  not per flat rate per month per features as is the case with the  service. Are there any questions about that?  Alright, so essentially as a developer you'll go into the Azure portal  you'll provision a workspace collection. The next thing is you will  start developing a Power BI desktop file using direct query  against an Azure source. By the way you could import data  but if you publish that to the workspace it's static. But it's not beyond  possibilities that you would actually just  automate a push. It's not designed to do that but if you then  refreshed it on the desktop interactively, opened it, refreshed,  saved and closed you could just push it up maybe once a week once a month.  But it really is designed to reflect real-time data coming from the application's  store which can be achieved through the direct query mode.  Alright, so with the workspace collection, provision workspaces  upload the Power BI desktop files, configure connection strings  and then go ahead and embed into your applications.  Any questions?  I'm sorry I'm running early  without demo space there's not a lot I can do and I'm really  really sorry for that because the demos shine. They absolutely do  but nothing that I can do without the internet. Let's wrap up  with a review and then I'm happy to go to the dashboard and share some information back to you about  the survey results that you submitted in. Power BI as a service  and take note this is a constantly and rapidly evolving  service. Many new features, many new capabilities being  delivered month by month but as a cloud based service it's designed  to gain or give access to users to data and that data  could come in different shapes, sizes, formats, different locations be it  a prem, be it cloud. Power BI desktop really brings it all together  while you can from the service connect direct to services and get  reasonably compelling experiences, Google Analytics, Dynamics CRM online  there are built in services and I should point these out to you that provide  immediate visibility on your data.  But in my thinking and coming from a business intelligence  background it's really the Power BI desktop that allows me to do  some amazing sophisticated things. I can connect to those very same  sources from the desktop, integrate with other sources of data  and enrich with logic, rich with reports and publish. Watch this list  it's growing week by week with different services.  I've demonstrated how that when you have a  data set in your workspace you can build reports dashboards  and you may use the Q&A natural language as I demonstrated to ask questions.  Also be aware that that only works against data sets  that are Power BI desktops that have been uploaded  or push API scenarios as I've described to you in this session.  The two licenses  so free you can still work with this in a development  scenario but typically it's going to be the Power BI Pro that's going to provide  the full feature set to work with the service and certainly when it comes to the volumes  of data you might be pushing if you need that one million per hour then it's going to need  to be pro. Give consideration also that Power BI when you create  these dashboards supports sharing them to others within the organization.  And if you have a mix of licenses which I'm finding with customers is quite common  then you've got to be careful that if you produce a dashboard that is using  any pro feature and you attempt to share that to a non-pro user  then they won't have access to it. So the discussions I have with the customers will be  think about the pro features and think about the audience that you need to push  dashboards out to. There's a complete pricing matrix that says  what you get under each tier and I will summarize that here  by saying that if you're prepared to pay the $10 US per month per user  you'll get 10 gigabytes not one gigabyte of storage. When it comes to a data refresh  scenario through the gateways so Power BI desktop uploaded  to the service but needs to keep data current from on-premises  free and I think this is generous. You can do this once a day at no cost  but if you need more frequent updates the support up to 8 per  day under the pro license. Relevant for the push scenario  with the API is that you can do this for free but no more than 10,000 rows per hour  and the streaming rate for pro is a million rows. Beyond that  there's nothing really that directly impacts upon the development scenarios that I've  spoken about. All the Azure Stream Analytics so the  Azure subscription needs to be within the same org  as the Power BI tenant. Alright so that's one thing that you  would need to work with. Otherwise you would have to build your own  service that would read the streaming result and push it using the REST API.  So what I hope I've inspired you with in this session  develop opportunities to develop what you need. You're not happy with the visualizations  go ahead and extend and build your own. You may be not happy with the data  that the REST source has supported. Perhaps you have some legacy system that there's no  connectors to. Well take on the responsibility as a developer to read from that  system and to push the results up and build a service that would manage that for you.  Integrate with  Azure services and embed with the new Power BI  embed service seamlessly into your applications. Some resources that  I'll leave you with and I think it's very important to keep your finger on the pulse  of what's going on by reading the blog periodically.  The reason for this is that the Power BI service itself goes through  release every week. One of the frustrations I have is I maintain training  content on this and it changes week by week which involves constant  rechecking that screenshots haven't changed and so on. This is  a great thing because what Microsoft are doing are reflecting the needs of customers.  And so I'd like to point out to you at this stage that if you go to  here, there's a whole  community around Power BI and when people come to me and say can I do  X, can I do Y and if not when is it coming? I just  direct them straight away here and say if you've got a need or an idea  then go ahead and register that. But before you do maybe search  on that, you know, people are asking about I need Power Point to have live  interactive Power BI embedded into it. And for anyone that could be familiar  with PowerView which was a reporting services interactive tool, it did  this on-premises with SharePoint and people want it.  So what you'll find is that people are putting up those ideas, they're voting against it  and you'll see the more people that vote, Microsoft will respond.  And here, great news, yes, we've acknowledged this and  work has started. So when I read the blogs, I will expect  next month or the month after that there's going to be something that says hey, in preview, we have this  new feature. And row level security is another one. There were enough  requests that it's driven a whole new feature to be added to the  to-do list. All right.  Also, Power BI Desktop goes through monthly updates.  So you need to download and install that to keep Power BI Desktop up to date.  The great news about that blog is that they provide you a video and all the new features  are described and demonstrated to you. All you need to do is watch it to keep up to date  with what's going on there. There's a Power BI Developer Center. So all  of the topics that I've shared with you today are documented and examples  are available to you here. There's the Power BI Rest API  console where they document all of the current API providing examples to you as well.  And more news about the Azure Power BI Embedded. It's very new.  It's available at this link here. Lastly  for the custom visuals, the GitHub project would be pretty valuable. Download, see  what Microsoft developed for their very own visuals. And if you simply  want to borrow others, then the Power BI Visuals Gallery is available from here.  If you haven't done  so already, I think about one-third had said that they'd used Power BI at the beginning of the session.  Power BI is available for you to sign up for today at no cost.  Maybe you stay at no cost. Be aware that there are a couple of limitations when  signing up with your email address that you cannot use public domains. So  if a hotmail or Gmail won't work, typically it will need to be an org.  It couldn't be a.gov and it cannot be a.military. Alright, so there are a couple of  restrictions. Others that try to sign up without success find that it's because  they're organization of lockdown self-registration.  Alright, so there's a blog entry or rather a documentation  article that will talk about the possible problems and the fixes  for those problems. But otherwise I find that most of my customers have no problem going  in there, signing up for free and going up and taking the 60-day  trial to experiment with the Pro feature set.  Alright, we have  four minutes left. Are there any questions that I could answer at this stage?  You just have one. What is your question?  You just have one. What is your question?  Normally when you create an important RBI, we share that you are able to  access that report. So what  happened if I sent this order to my colleague at the email  and he had to forward this to us? So  what you're describing there is a scenario which is called  and sorry let me just show you under reports. With a report  you could publish to web. Is that what you're describing?  Yeah, if you have the exact you would allow it to be.  Do you need an application to somebody? That depends. There's two scenarios.  So if it's sharing a report, you could look up through the API and  get the details required to embed it in an authenticated scenario.  Or this that's still in preview and in fact I didn't mention is what they're  building out is the fact for you to publish an interactive report to the web that would be  publicly available. You can experiment with that today. What you will get  is an embed code and you may manage those embed codes. So for example you gave it to somebody  and you want to revoke that. What you'd have to do is delete the code and regenerate it  and then hand it to the people that you intend to keep that code.  So watch this story moving forward. I didn't mention it because  there's still a lot of lack of clarity around it and certainly with pricing.  If they're going to allow something to be anonymously accessed, imagine  you place it on a blog and that blog entry goes viral and a thousand people are trying  to hit it. I don't know what Microsoft are thinking here in terms of pricing because  everything in the Power BI service that I've described earlier in this session is a flat rate  per month. So all of a sudden we've got a feature that looks like it's going to be  on some billable unit. All right. So does that help answer your question?  All right. Are there any other questions?  You're welcome to, if you would like to, interact with this  dashboard by submitting events. And thank you very much for your  time and attendance and patience. I do apologize for connectivity issues but I'd encourage  you to go to PowerBI.com. There are a lot of videos  backing up and describing the feature sets that I've mentioned today.  Thank you and all the best with your Power BI adventures.  Thank you.  Thank you.  Thank you.
Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_0_59.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_60_8471.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_8472_12263.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_12264_12347.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_12348_12395.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_12396_12479.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_12480_14519.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_14520_15539.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_15540_16823.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_16824_17183.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_17184_17807.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_17808_18095.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18096_18203.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18204_18323.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18324_18503.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18504_18599.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18600_18695.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18696_18743.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18744_18851.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18852_18983.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_18984_19031.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_19032_23099.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_23100_25151.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_25152_25367.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_25368_25595.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_25596_25727.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_25728_29459.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_29460_40715.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_40716_42575.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_42576_42659.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_42660_42983.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_42984_47183.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_47184_47567.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_47568_49043.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_49044_50375.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_50376_53867.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_53868_54263.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_54264_54503.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_54504_54575.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_54576_55523.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_55524_55847.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_55848_56231.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_56232_56279.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_56280_56915.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_56916_57503.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_57504_57575.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_57576_58175.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_58176_58487.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_58488_59267.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_59268_59387.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_59388_59483.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_59484_62951.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_62952_63959.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_63960_66023.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_66024_66131.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_66132_66371.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_66372_73115.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_73116_73187.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_73188_73343.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_73344_73847.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_73848_78983.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_78984_79067.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_79068_83723.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_83724_83783.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_83784_83843.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_83844_84023.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_84024_84383.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_84384_86603.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_86604_86651.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_86652_87143.png Power_BI_for_the_Developer__Integrate__Embed__Exte_PeterMyers_87144_87609.png
So afternoon session on Power BI for the developer, the story on how you may integrate, embed or extend. Very brief introduction on myself, Peter Myers. I'm a business intelligence consultant. I've worked for some 15 years working with Microsoft business intelligence products, mainly with SQL Server delivering enterprise data warehousing solutions, but in more recent years. As Microsoft have reached out into self-service BI, tools and this new generation with Power BI, I've become an expert in this space and I produce the training content from Microsoft and I have the good fortune of sharing the message about what Power BI can do for users and also for developers. So in this 60-minute session, I'm interested to know, first of all, who has already had any experience with Power BI before? And so I'd poll at about one-third of the room and for those one-third, has that been any developer-related activity? Working programmatically with Power BI, two of you out of the third. All right, well, Power BI is a huge topic and 60 minutes would never do justice to the topic. And so I'm going to begin with an introduction that describes the fundamentals about what Power BI can achieve and then focus on the topic relevant to developers about integrate, extend and embed. Need to start somewhere. So the story with business intelligence is what we call a first wave is that businesses needed to get answers from the data that they store. Common business questions needed to be addressed and this was achieved through business intelligence. In the earlier days, back when I started, this meant enterprise data warehousing. It meant IT, long-term projects. And what we discovered is that perhaps 60, 80 percent of the business questions could be addressed through the data warehouse. It could never address every single business question. That arose then a second wave in BI, which was to deliver self-service capabilities to the business users themselves. Probably not to everybody, probably to a small select few. We might call them power users or advanced analysts. And these guys or girls are literally whizzers when it comes to Excel. They drive some amazingly sophisticated solutions. So Microsoft turned around back in the Office 2010 release and they delivered Power Pivot later on Power Query and Power View, supporting very similar capabilities to what the enterprise corporate BI would deliver, but on the desktop handling smaller, more focused projects. What I can describe to you today is that Power BI really delivers a third wave in BI, moving beyond just the analysts that are still reasonably well-skilled and proficient experts to the business users themselves. Those that have the questions can engage directly with a service to answer those questions. All right, so common BI challenges. How do we get an end-to-end perspective across all data, regardless of what type, what format, whether it's on-premises, whether it's in the cloud? And how do we deliver that right data to the right people and at the right time? Well, these are common challenges with business intelligence. And what Power BI delivers is a cloud-based analytics server, or service, excuse me, that is there to address most of those common business challenges. A quick list of key differentiators and attributes of Power BI is that it has software as a service, services available. So with a click and a matter of minutes, if you need your Google analytics data and you need to see it, you need to interact with it. You can then authenticate through Power BI, enable it to connect to Google analytics and build in reports and dashboards, enabling you within perhaps five minutes to start exploring, interacting with your data, and also potentially sharing this with others within the organization. What I'll talk to you and demonstrate today is real-time dashboard updates and how this can be achieved in two distinctly different techniques. The other ability for Power BI is to connect to data not just in the cloud but on-premises as well. So we need to respect that as a cloud-based service, how is it going to interact with your corporate stores? Perhaps your analysis services data models, your SQL server databases on-prem. And so the deal there is that Power BI also comes with gateways, that when installed on-premises, they create a secure channel up to the service, enabling either a scenario of real-time reporting, so it passes straight through to on-prem, results come straight back up, we call that a direct query mode, or if you're going to work with importing of data, you can schedule data refreshers to take place perhaps on an hourly or so basis via the gateways. Data exploration delivered through Q&A, so this is very much the web search engine concept that these days we start asking questions and amazingly, those questions get translated into reasonably decent search results. So Power BI also supports in some cases the ability to ask questions of your data and to have responses delivered back through visualizations. It even extends to the Cortana story, that if you configure it correctly, you can ask Cortana and Cortana will receive the Power BI reports and display them on your device. Integration with many Microsoft products, we'll talk about other Azure integration scenarios, specifically with stream analytics or machine learning or big data. And then finally, the ability to work with hybrid configurations and to work seamlessly within your Azure directories and within your infrastructures. I could really talk all day on those topics, but to finish off with the overview, when it comes to accessing data, it comes in many locations, shapes and formats, whether they are services or on-premises data, whether it's you distributing content within your own internal content packs, connecting to other Azure services, working with data in Excel files, and ultimately the Power BI desktop, which I'll work with today, Power BI desktop ultimately provides you a tool on the desktop enabling you to connect to these variety of sources, load in, integrate if necessary, enrich with exploration and hierarchies, enrich with business logic, build reports, and as a single file, the application is designed to publish it back to the service, whereupon you can build dashboards, configure automatic data refresh, enable Q&A natural querying, and then share this with others so they can access it through the device or the web browser of their choice. Note there, there's a REST API that supports a development story for the creation and manipulation of data. Essentially, it's a push data scenario that can be achieved with the service. Up front, before those questions might be in your mind, how much is this going to cost me? It may cost you absolutely nothing. All right, so there are two licensing tiers. The first is free, and that provides a rather generous one gigabyte of storage and a rather generous set of features, including some dev scenarios that may not cost you anything. If you're looking for the full feature set, and I'll provide you at the end of this presentation some differences in features between the two different tiers, then you'll be looking at the price per month per user. And I know in the US this comes to $10 in your region, I'm not exactly sure. Be aware that if you want to trial this, when you sign up for free, Microsoft are also very generous to provide you with a 60-day trial to allow you to try all of the pro features. All right, now Power BI, huge topic. With the focus just on developers, really, we're going to talk about how we could create real-time dashboards, how we could integrate Power BI into applications, and how we could also develop and integrate custom visuals to extend beyond the already 26 visualizations that come with Power BI. Are there any questions? I've set the scene. All right, let's begin with an activity. I've got a dashboard here, and what I'd like you to do is help and participate by filling in a brief survey. That brief survey will be asking six questions. So would you mind either use the QR code or use your mobile device to navigate to this URL? And what I'll ask you to do is answer the six questions. And my apologies up front. One of them is what city or the nearest city you come from. There are 600 items in a drop-down list. It was just the easiest way to quickly get this out. But I ask you not to submit, to simply fill in answers. They don't have to be true. You may be funny or you may use an alias. No details that you submit will be used against you. So let me explain what's going to happen. That when you submit, it's going to on the web server, and it's using Azure websites, it's going to go ahead and throw an event onto an Azure Service Bus Event Hub, and then Stream Analytics is going to query it live and push it to the dashboard. So don't hit submit yet. There's a nice experience. You'll see it when there's this influx of events coming through. It looks quite good. So what you see here is a very rudimentary dashboard consisting just of static data. No data at this stage. All right. Assuming that nobody else needs the URL I'm going to exit the full screen mode and I'll point out to you that here in the navigation pane, and the navigation pane exposes to me in my workspace the three groupings of fundamental objects in Power BI. Data sets that connect to data, and that full range of sources that I described to you will ultimately be delivered to you as a data set. Data sets are then the foundation upon which reports are built, providing rich interactive experiences, report consisting of multiple pages, and ultimately when you want to share key metrics, we then pin visuals from reports up to dashboards, and then we share the dashboards out. Still not clicking submit at this stage, I would hope. Here I am clicking on the data set. So Azure Stream Analytics has pushed the data set definition through. Take a look on the right-hand side, and you'll see I have a single table and a collection of fields that map to inputs coming from you. What I'm going to do at this stage is just build out a dashboard, and then when the dashboard is being shown, I'll ask you to submit your survey results. So I'll just ask for a single field, which is the number of submissions. And that's shown to me here in a column chart. I'm going to switch it across to a card. I can tell you that five people hit submit. Not a problem. All right, so already six messages have been read from the event hub. And what I'll do, and I have to do this, I need to save this report. So let me just call this demo. And having saved it as a report, I'm then able to pin a visual to a dashboard. All right, and while I'm here, one of the questions was where are you from? So I'm going to build out using a map that's related to all of the cities in that drop-down list with latitude and longitude. So let me bring in lat and long. And I'll just plot the number of submissions. And so I can say that the six people that submitted were coming largely from, I guess that's Oslo, right? And then what I'm going to do is save this and pin this one. And I think I made a slight error before. I erased that one. Let me just reconstruct it. Save. And pin. Now switching across to the dashboard, and I will now remove these URL rolls. In fact, let me just push them to the side. Oops. There is a, excuse me, there is a URL. I didn't mean to click. All right, so let's bring the number of submissions here. And I should have a map of the world somewhere. I'll have two of those. Have the map of the world somewhere. Down here. So a dashboard, a collection of tiles not necessarily being sourced from the same data set. When we drive a car, what we like to see in our dashboard is relevant metrics. We don't want to be overwhelmed by lots of irrelevant data because that distracts us and it's unsafe. So I'd like to see the speed. I'd like to see distance. I'd like to see fuel. And if fuel's running low I would like to see some status indicator like red telling me off track takes some action. And that's essentially what dashboards are designed to express. Let me clean this up a little by editing the tile. It might be nicer here to just call this survey responses. So we can provide titles and subtitles. This would be NDC Oslo 2016. Custom URLs could be set here as well. And then this one would be submissions by city. Before you still hit submit, to be able to demonstrate the Q&A capability I can ask questions of the data itself. So it might be something like show me average distance. So for the eight people that submitted I could learn here that the average distance travel was 1,231 kilometers. And we could make this a little more interesting by breaking this down by occupation. And that looks pretty cool. So let me pin that visual. So here's a second technique by which we can build up dashboards. And let me change that now to gender. And I'll pin it. And I think we also had age group. There it is there. And I could say sort by age group. And for this one I might also customize using the full styling that's available even when reporting. Like for example, let's add some data labels. Let's display them as none with zero decimal places and increase the font size of those. So you can see how rapidly you can build out these dashboards. Switching back to the dashboard page then. This is now what I see for the details that are so far been submitted. Let me then put this into a full screen mode. It's not going to work nicely with these. For those that are ready, why don't you just go ahead and submit your results and see what happens here on the dashboard. Okay, literally real time. With the backing of Azure there, event hubs can ingest millions of events per second. Streaming analytics. And I'll point this out to you. I won't build the solution but I'm going to show you the screens for it. Stream analytics will aggregate across time periods. Perhaps every second take the average, take the sum, take the count and output the result of aggregated events and push them to Power BI. Power BI receives it as a data set. We can build reports on it and the dashboards that are based on this type of data set will reflect real time values. What do you think? Literally no code. Taking the assumption that someone was responsible for the events arriving in the event hub, the story beyond the event hub, listening to events, the streaming analytics is pretty straightforward. Any questions at this stage? So what do we have out of statistics? We have how many people? I should have put numbers on here. So just as an idea, those that are between 95 and 99 traveled on average 11,000 kilometers. I'm thrilled that the people at that senior age would make the effort to come here to Oslo. My home city of Melbourne won, so I know that would be me. And from the people coming from Oslo, how can we just got 120 people a local? Thank you. It gives you a taste of what can be achieved. So let's talk in more detail about how we can extend Power BI, a closer inspection of the rest API, and how you might integrate Power BI with Azure services, the very new and still in preview Azure service called Power BI embedded, and then we'll talk about custom visuals also. So there are four very exciting opportunities that should inspire developers to consider Power BI, whether it's an integration, embed, or extend scenario. Let's start with a discussion then on the Power BI rest API. This rest API has been designed to interrogate Power BI to create data sets, create tables, and to push data to those tables, and when necessary to delete all data from those tables. So it programmatically manages resources, and as you push data into data set tables, they will be reflected real time into the dashboards that you've just seen. So essentially what Stream Analytics is doing is nothing more than what you can do and it's using the rest API to push the results of streaming queries into Power BI data sets. Alright, so in Power BI at the very top level when you authenticate, you're connecting to a workspace, and that could be a personal workspace or it could be a group. So one of the pro features in Power BI is that you can create groups and invite members into groups and collaborate on shared resources. As a best practice, if you're pushing real time data, you shouldn't push it to an individual's account. You would create a group, you'd create a user that is not a real person, you would make them a member of a group, you would push the data set definition to the group, and then you would allow collaboration on that and potential sharing from outside the group. The other benefit of course is that the individual leaves the organization and then you've lost all of that work. So please work with groups and note that groups are a pro feature, so it will require that $10 per user per month. So at that very top level, whether it's a personal workspace but much more preferably that it's a group, it contains a collection of data sets and in turn data sets contain collections of tables and tables contain rows. It's a very simple object model. In order to make this work, you will need to register an application and so if I just open up a web browser like this, there's an app registration tool that when you sign in with your Power BI account, you can provide a name for the app whether it's a server-side web app or a native app, in which case you'll need a redirect URL for web authentication and then tick, tick, tick, and I do not know why these ticks aren't working. Last time I used this a matter of weeks ago, those ticks used to work, so I have no idea. The good news is I don't need to create one. When you register the app, you will get a GUID back and essentially that is what you will use when using the REST API. It will be delegated permissions and the permissions could be whether they're allowed to view certain assets, whether they're allowed to insert or maintain data and you can see the list of them here. From an authentication perspective, depending on whether it's a native client or web app, the flow is somewhat different. I am not going to read through the entire flow but it should not be surprising to anyone else that's working and engaging with authentication on web service calls. A closer look at the operation, so the ability to enumerate, having authenticated and connected to a workspace, what data sets already exist. Data sets are described in terms of a unique identifier, which is a GUID and by friendly name. You can then create a data set that would require a post, an adjacent document describing the data set in terms of the collection of tables and in terms of the collection of columns for each table. The data types are reasonably straightforward. You can also enumerate tables contained within a data set. You can update an existing table schema. I can't think of a great reason why this would happen often but you might decide that we need a new column in a table. So if you do an update schema, hopefully, if you don't break the order of the columns, you can maintain persisted data and potentially add new columns or remove a column if you no longer need it. Add rows to a data set. So this is where you push data up and then you may clear all rows from a table. There's the ability to also list groups. So part of your application experience might well be you'll let the user authenticate and then you'll present them a list of, it's either your workspace you want to publish to or it's a group that you remember. So you can list the groups and then from a group you can call the list data sets. List dashboards and tiles have been added more recently. These are in preview. So this supports another embedding scenario that is that if you get the unique handle for a dashboard or for a report or a tile, you may embed these into other web applications. They will require authentication so you couldn't publicly make those resources available. Just take a quick look, listing all data sets. You'll send a request that looks like this. So you have an endpoint. You will need to add an authentication token and then it'll just basically be a request like this with a response coming back as a JSON document looking like this. There you see each data set in terms of an ID and a name. When it comes to inserting data, a post request, so you'll note what's been built out here is the GUID for the data set that you could have retrieved through an earlier call. It has a collection of tables. Tables have unique names, collections of rows and here you may push a collection of one or more rows up to the data set. Some restrictions. The maximum rows per single push will be 10,000. That just means you're going to chunk things down. If you really have that volume, chunk it down into 10,000 groups. But there is a restriction nonetheless that says that you can only send 10,000 rows per hour if you're on the free license and a million rows per hour if you're on the pro license. So I've noticed that if you take that million and you divide it by the number of seconds in that hour, that's actually the limit. I thought that it might let me do 900,000 in the first minute and maybe 100,000 in the remainder of the hour but it doesn't work that way. So it's actually an average. If you look at it per second, that is the maximum rate you can achieve. If you exceed that, the service will let you know and you will have to expect to handle an error. Maximum rows stored per table will be 5 million. And so at that point you'll end up with errors. You'll be forced to delete the table but there is an answer to this and that is that if you create the table using the basic FIFO method then it will store a maximum of 200,000. And imagine then when you insert the 200 and first record, 200,000 and first, it will store it but it will drop off the very first row. So you're moving forward then with 200,000 rows of data. Maximum pending requests at any stage would be 5. So let's take a look at a real-time dashboard implementation. And I'm not having a good feeling about this but let me see what happens. I'm having some very strange networking issues and certainly when I was setting up hoping that I had another 10 minutes but let's see what happens. One issue is that the Azure portal simply won't load. Let me just try that again. And I have absolutely no explanation for this. I use this VM all the time. And with that not loading, it leaves me with a bad feeling. Anyway, this application's been designed to show you how the API calls work. And while you wouldn't ordinarily wouldn't expose these types of options, they would be in a config file, that registered app, its GUID, would get stored in here. The redirect URI. The other URIs are fixed. So according to the API documentation, these are the endpoints that you'll communicate with for Power BI. Then you will come to connect. And at this stage what it wants to do is authenticate using a Power BI account. And on the first of authentication, it's going to return a token that must be placed in the header of all requests that follow. And I am double finger crossing here. And that's a different error and that's probably for a good reason. It worked perfectly in rehearsal 30 minutes ago. And I was so confident I said I don't need to bring the recordings. So that's a real shame because what it would show you in this tree view is the full structure of the workspace that I connect to, the collection of data sets, reports, dashboards, drill into data sets, there are the tables, create data set, create table. And I would have shown you the whole flow. I'm stuck. I'm sorry. There's absolutely nothing I can do. But I have more to talk about. And I can talk about this scenario. This one does work and this is what supported the opening demonstration with the surveys that you submitted. So the story with Power BI and Azure, there's a growing story with the Azure ecosystem. You can see clearly Microsoft putting Power BI as the presentation layer across the growing number of enterprise services, whether it's Azure SQL Database, Azure SQL Data Warehouse, even big data with HD Insights Spark. And then we see the Azure Machine Learning and we also see the Azure HD Insight with Hadoop. And so already we can connect to many of these Azure services and build Power BI solutions. But really, with a focus on a more developer-centric solution, what I'll share with you is that you can couple certain services together. And so really the recipe of how I delivered the real-time dashboard, just out of interest. No, where is my dashboard page? So with 57 responses coming in, what's driving this? Is that upon submitting your survey, it's adding an event to a service bus event hub. Listening is a stream analytics job that is then pushing an output to Power BI. So what I can show you here is, first of all, I've created and let's just assume the event hubs looked after. That's somebody else's project to push events upon some requirement. Here I've just gone into Azure service bus and I've created an input named Power BI Survey. And essentially it's just reaching and listening across to an event hub named this. Next, there's a query. And for anybody that works with SQL State Pro, I'm going to go ahead and show you anybody that works with SQL statements. This will look rather familiar. I'm selecting from the event details various fields. There's also not a lot but a sufficient number of functions that allow me to transform or cast because essentially those values were in JSON. They need to be cast to their correct type. That's important in Power BI because if we want to perform aggregation and summarization of values, they will need to be numeric. And essentially it's reading from the input that is reading from the event hub. Now, commonly, and not in this example because I want every single survey result to be sent through. But when there's the ingestion of millions of events, you're not interested in projecting those onto a dashboard. You're interested in summarizing them by time periods. So what you would do is add a group by in exactly the same way you would use to summarize a relational query result. And there's a special function there called tumbling or hopping window. And you can say the tumbling window is for every two seconds. So perform this query group by producing aggregate result in two seconds. Whatever events come in, aggregate push out. And the output for this query is defined by the into, into PBI workspace which is defined inside the job. And if we take a closer look at this, it's pushing to Power BI and to a data set name survey to a table name survey. Alright, so if I was to create a new output, nope, okay, the job's running so I can't actually touch it. That's how it worked. And so how much code was involved with that? Providing you had the events on an event hub, and this is a good IOT story, by the way, then it's just a matter of writing the right query, setting up the inputs and output of a job. You will need to when you build the output authenticate to Power BI so some account has to be used. The preference is that it's not going to be your personal account, that you've created a non-human account and that you've created a group that it belongs to and you're pushing the data set to that group. That's real-time dashboards with Azure Stream Analytics. Moving on to the topic then of custom visuals. So let me open up Power BI Desktop. As a free download from Microsoft, be it 64 or 32 bit, this is the companion application for the Power BI service. So its strength is allowing you to connect to a wide range of data sources. Using query logic then a very expressive query language to acquire, filter, shape, cleanse and bring in results. Every query that you bring in can come from a different source and ultimately you can create relationships and there's the integration story going on. And then with the power of DAX, data analysis expressions, you can build business logic, calculator columns, measures, time intelligence functionality and essentially what you're producing here is a model that integrates data, embeds business logic, adds hierarchies for navigation and then you'll visualize that data. So let me just do something very, very simple and that is to bring in some data from a local SQL database. And this is the important thing. You have the ability for certain data sources to import and therefore copy the data into your solution or simply a direct query which says that every request made by a report or indeed a dashboard will flow direct down to the source itself. Now if you stop for a moment and think of the benefits and in fact there are benefits from either approach depending on what you need to achieve. But the direct query opens up the potential for you not to have to be concerned about processing, not to have to be concerned about excessive volumes of data. And the direct query is open for SQL database on premises, analysis services, multi-dimensional or tabular models and in the cloud it's available for three sources that would be Azure SQL database, Azure SQL data warehouse and Azure HD Insights Spark. So you can have real-time querying across big data loads here on Power BI Desktop. Let me just choose for now it actually won't matter too much. I'll go for direct query. There are some limitations with direct query and that is you may not integrate multiple sources together. Your whole solution has to be pointing only at one. So I'll go ahead and connect using my Windows authentication. And then the navigator says well the next level down from a server will be the databases themselves. So if I just bring in something pretty simple like a fact table, factory seller sales, one table, I get a preview and I have the ability to load all of this data which is probably not a smart thing to do on a fact table or use the edit here to refine what I really want. So it presents to me in the query editor then the fact table and I'm going to keep this so simple that all I want to analyze will be sales amount by time so I find that there is an order date column somewhere here. Maybe not. Let me bring in dimry seller. Order date, order date. Okay, in this case we have three date columns because it's what we call a role playing dimension. There's a date dimension in the data warehouse but there are three foreign keys pointing to it. So I make the decision that it's actually the order date. Now what you see here with these links represent a foreign key enabling you in the query editor to introduce columns from that related table. For example, let's bring in the calendar year and let's bring it also the calendar quarter. Now I don't want all columns that are here so if I just bring in those two with a multi-select, right click remove other columns and I've narrowed down all of that data to just the three columns of interest, how about then I rename these to make them friendly. I'll rename quarter year and sales. Over here the applied steps represent from the sourcing of that raw data right down to the desired state that you want. And you could customize this logic. There's a very powerful set of expressions and functions and even the ability to function with parameters in here. For now I'm just going to load this and load wasn't the right term. This is direct query, no data moved. But it has now an understanding that I have a table that we can see here called factory seller sales. So sitting here on the report canvas then I'm going to go ahead and drag year and ask that to be visualized as a slicer. Enabling then filtering interactively on the report, that's 2012 and then I could show sales by quarter. And quarter should go on the axes. Alright so that's the old BI, right? How else might we choose to express the data? Now what you'll notice on the visualizations pane is there's some 26 out of the box but far more interesting and what we'd hope to inspire you guys as developers is to consider that you may create custom visuals and you may be able to integrate them with Power BI. Alright so what I'll do is go to the gallery and what you see here is a contribution both from Microsoft but also from other developers out there that have been generous enough to share with the world what they've developed. That's not to suggest that what you develop has to be published here. You may keep it private but I think one that's just sort of eye-opening and not so traditional is the Enlightened Aquarium. Alright so we have a closer look, we read about this and we say okay well let's see what this will do for our data. Downloading the visual, okay you need to trust whatever you're downloading and what it downloads then is a Power BI visual file. And then very, very simply here on the visualizations click the ellipsis and I'm sorry that that doesn't draw correctly sometimes. It's basically a warning with an okay and then it says well where is that file and then I add it to the toolbox and now we have the aquarium visualization. So perhaps what I do is just keep that bar chart selected and switch it across to become an aquarium. What do you think? Which would you prefer a bar chart or swimming fish? Alright by hovering over a fish he's going to stop and it's going to tell you the amount of sales that were achieved. Let me point out also in the service itself when I'm in the reporting you have a near identical experience here in the web browser. So what you can achieve on Power BI Desktop I can come in here and granted I have permissions and that I own the report I may edit. Let me introduce a second page here and I can do exactly the same thing. Let me go ahead and import that visual and use it to display what the survey results look like with fish. Okay. Or maybe not. Okay I think we know what that will end up looking like. That's the story with custom visuals. The visual gallery is growing week by week and this is Microsoft's approach of saying look we're not taking full responsibility we'll put it out to community and others. But do be aware that the visuals that Microsoft have produced they have made all of the source content available for you on GitHub. So anything that Microsoft publish and is available in Power BI Desktop you can gain access to that provides insights and understanding and you may learn from that and then start designing and developing your own visuals. The last topic that I'll cover is with the new Azure Power BI embedded service. Now this is a service in Azure and there's some confusion already up front. I've just shown you Power BI as a service but what you need to be aware of that when you sign up for Power BI you're using typically your workplace email address. If you've got Office 365 you already have a tenant and Power BI operates within that tenant. If there is not a tenant the first person that signs up will behind the covers create a tenant to support the Power BI and the provisioning of workspaces for individuals and for groups and for sharing content within the organization within the tenant. This service is different. This is an Azure service that allows you to provision your own workspace collections and your own workspaces whereby applications will be using them not individuals not groups. So it is designed to allow you to create compelling and interactive reports to embed these into your web apps and it supports quick and ease of management and deployment. Let's just walk through the key attributes and that is you will still use Power BI Desktop as an authoring tool. The requirement will be that you will build a Power BI Desktop file preferably using the direct query mode and pointing to your data that resides in a cloud source. Presently and in preview it won't support any on-premises sources. No word on where that's happening if that's going to change. So that would mean today that you can connect to Azure SQL Database, Azure SQL Data Warehouse and also Azure HD Insight with Spark. So you would create your Power BI Desktop pointing to those and my attempt to do that failed because this VM isn't reaching the service. I do not know why. Alright, so what's attempting to do is connect and the IP address isn't good but I have definitely used the IP address of this machine but on my host that can reach it and the VM can't. So I'm sorry I'm very stuck. But at this point I'll ask you to use your imaginations where the demo would go. I would come in here and I would open up the Power BI Desktop. Perhaps I developed it against an on-prem SQL Database and by dividing the structures with the same I would point it to the Azure SQL Database with the right connection strings and credentials. I would save and close the file. So you have the full authoring capabilities but do remember that with the Direct Query Mode it's only one source of data that you can connect to. You then can use your own visualizations or the out-of-the-box visualizations to come up with the reports based on the Direct Query representation of your data. You can easily embed interactive visuals into your app using REST APIs and the Power BI SDK. So I mentioned all of the methods. What happens is that you provision an Azure Power BI Embedded Workspace Collection, you create a workspace within it and there's an API that supports the upload of the Power BI Desktop file to that workspace. The next thing that you would need to do is to apply a connection string. Because it will be using embedded credentials they will not move. So the API call will update the connection string to the Power BI Desktop now that it's in the workspace. The next thing is programmatically by making the right calls you can with relative ease add it into an application. So you will need to authenticate by providing a workspace key and an authentication key that will be provided from the Azure portal and then pretty much this just runs. What that means is that when people are interacting with your application embedded within it in exactly the same experience that I'm showing you here on the desktop, let me go back to the fish, whatever you lay out here is exactly what they're going to see in the web app contained within the frame of your application. Your user existing authentication authorization methods within your app and also between the data sources, this does not require you to build or somehow embed analytics and reporting. You need interactive, users need to change filters, need to click on things, touch on things, it all happens through Power BI. And then you're probably asking well how much does this cost? Because it's a very different pricing model to the Power BI that we have as a service whereby individuals and groups work within their own work spaces. So it's currently in preview which means that there isn't a cost associated with it, not while it's in preview, but the discussion at this stage without a commitment from Microsoft is that the first thousand renders per month will be free and for every thousand beyond that it will be $2.50 US. All right, and then you might ask well what is a render? That's a great question. If I've got a report with a filter that allows me to modify year, and let's suggest that I have one, two, three, four visuals every time you modify a year that is potentially four renders going on. And so if you do the mathematics and you think of the number of users, the number of interactions that they have, that will give you an idea. But then again it's not quite right because if they move from one filter to another and back again, your application will have cached this, in which case you're not paying for something that has been previously cached within the same session. So I cannot even give you an idea about how much this might cost, but it's certainly something you can monitor from the Azure portal. That when you look at the Azure Power BI Embedded Service, you will see a collection of workspaces within a workspace you can drill in and you can then understand what the embed renders are at any point in time. Within a minute it reflects the up-to-date number of renders and I guess with every other type of a subscription you could set a cap at some point if you said look that's where we'd like to stop. What was this intended for? Alright, it's intended for a very different purpose with Power BI that I began talking about. While both are relevant for developers the scenario with Power BI Embedded is that you have a need for interactive rich visualizations delivered through reports within a web app and now as a service you may embed this and you will pay per usage not per flat rate per month per features as is the case with the service. Are there any questions about that? Alright, so essentially as a developer you'll go into the Azure portal you'll provision a workspace collection. The next thing is you will start developing a Power BI desktop file using direct query against an Azure source. By the way you could import data but if you publish that to the workspace it's static. But it's not beyond possibilities that you would actually just automate a push. It's not designed to do that but if you then refreshed it on the desktop interactively, opened it, refreshed, saved and closed you could just push it up maybe once a week once a month. But it really is designed to reflect real-time data coming from the application's store which can be achieved through the direct query mode. Alright, so with the workspace collection, provision workspaces upload the Power BI desktop files, configure connection strings and then go ahead and embed into your applications. Any questions? I'm sorry I'm running early without demo space there's not a lot I can do and I'm really really sorry for that because the demos shine. They absolutely do but nothing that I can do without the internet. Let's wrap up with a review and then I'm happy to go to the dashboard and share some information back to you about the survey results that you submitted in. Power BI as a service and take note this is a constantly and rapidly evolving service. Many new features, many new capabilities being delivered month by month but as a cloud based service it's designed to gain or give access to users to data and that data could come in different shapes, sizes, formats, different locations be it a prem, be it cloud. Power BI desktop really brings it all together while you can from the service connect direct to services and get reasonably compelling experiences, Google Analytics, Dynamics CRM online there are built in services and I should point these out to you that provide immediate visibility on your data. But in my thinking and coming from a business intelligence background it's really the Power BI desktop that allows me to do some amazing sophisticated things. I can connect to those very same sources from the desktop, integrate with other sources of data and enrich with logic, rich with reports and publish. Watch this list it's growing week by week with different services. I've demonstrated how that when you have a data set in your workspace you can build reports dashboards and you may use the Q&A natural language as I demonstrated to ask questions. Also be aware that that only works against data sets that are Power BI desktops that have been uploaded or push API scenarios as I've described to you in this session. The two licenses so free you can still work with this in a development scenario but typically it's going to be the Power BI Pro that's going to provide the full feature set to work with the service and certainly when it comes to the volumes of data you might be pushing if you need that one million per hour then it's going to need to be pro. Give consideration also that Power BI when you create these dashboards supports sharing them to others within the organization. And if you have a mix of licenses which I'm finding with customers is quite common then you've got to be careful that if you produce a dashboard that is using any pro feature and you attempt to share that to a non-pro user then they won't have access to it. So the discussions I have with the customers will be think about the pro features and think about the audience that you need to push dashboards out to. There's a complete pricing matrix that says what you get under each tier and I will summarize that here by saying that if you're prepared to pay the $10 US per month per user you'll get 10 gigabytes not one gigabyte of storage. When it comes to a data refresh scenario through the gateways so Power BI desktop uploaded to the service but needs to keep data current from on-premises free and I think this is generous. You can do this once a day at no cost but if you need more frequent updates the support up to 8 per day under the pro license. Relevant for the push scenario with the API is that you can do this for free but no more than 10,000 rows per hour and the streaming rate for pro is a million rows. Beyond that there's nothing really that directly impacts upon the development scenarios that I've spoken about. All the Azure Stream Analytics so the Azure subscription needs to be within the same org as the Power BI tenant. Alright so that's one thing that you would need to work with. Otherwise you would have to build your own service that would read the streaming result and push it using the REST API. So what I hope I've inspired you with in this session develop opportunities to develop what you need. You're not happy with the visualizations go ahead and extend and build your own. You may be not happy with the data that the REST source has supported. Perhaps you have some legacy system that there's no connectors to. Well take on the responsibility as a developer to read from that system and to push the results up and build a service that would manage that for you. Integrate with Azure services and embed with the new Power BI embed service seamlessly into your applications. Some resources that I'll leave you with and I think it's very important to keep your finger on the pulse of what's going on by reading the blog periodically. The reason for this is that the Power BI service itself goes through release every week. One of the frustrations I have is I maintain training content on this and it changes week by week which involves constant rechecking that screenshots haven't changed and so on. This is a great thing because what Microsoft are doing are reflecting the needs of customers. And so I'd like to point out to you at this stage that if you go to here, there's a whole community around Power BI and when people come to me and say can I do X, can I do Y and if not when is it coming? I just direct them straight away here and say if you've got a need or an idea then go ahead and register that. But before you do maybe search on that, you know, people are asking about I need Power Point to have live interactive Power BI embedded into it. And for anyone that could be familiar with PowerView which was a reporting services interactive tool, it did this on-premises with SharePoint and people want it. So what you'll find is that people are putting up those ideas, they're voting against it and you'll see the more people that vote, Microsoft will respond. And here, great news, yes, we've acknowledged this and work has started. So when I read the blogs, I will expect next month or the month after that there's going to be something that says hey, in preview, we have this new feature. And row level security is another one. There were enough requests that it's driven a whole new feature to be added to the to-do list. All right. Also, Power BI Desktop goes through monthly updates. So you need to download and install that to keep Power BI Desktop up to date. The great news about that blog is that they provide you a video and all the new features are described and demonstrated to you. All you need to do is watch it to keep up to date with what's going on there. There's a Power BI Developer Center. So all of the topics that I've shared with you today are documented and examples are available to you here. There's the Power BI Rest API console where they document all of the current API providing examples to you as well. And more news about the Azure Power BI Embedded. It's very new. It's available at this link here. Lastly for the custom visuals, the GitHub project would be pretty valuable. Download, see what Microsoft developed for their very own visuals. And if you simply want to borrow others, then the Power BI Visuals Gallery is available from here. If you haven't done so already, I think about one-third had said that they'd used Power BI at the beginning of the session. Power BI is available for you to sign up for today at no cost. Maybe you stay at no cost. Be aware that there are a couple of limitations when signing up with your email address that you cannot use public domains. So if a hotmail or Gmail won't work, typically it will need to be an org. It couldn't be a.gov and it cannot be a.military. Alright, so there are a couple of restrictions. Others that try to sign up without success find that it's because they're organization of lockdown self-registration. Alright, so there's a blog entry or rather a documentation article that will talk about the possible problems and the fixes for those problems. But otherwise I find that most of my customers have no problem going in there, signing up for free and going up and taking the 60-day trial to experiment with the Pro feature set. Alright, we have four minutes left. Are there any questions that I could answer at this stage? You just have one. What is your question? You just have one. What is your question? Normally when you create an important RBI, we share that you are able to access that report. So what happened if I sent this order to my colleague at the email and he had to forward this to us? So what you're describing there is a scenario which is called and sorry let me just show you under reports. With a report you could publish to web. Is that what you're describing? Yeah, if you have the exact you would allow it to be. Do you need an application to somebody? That depends. There's two scenarios. So if it's sharing a report, you could look up through the API and get the details required to embed it in an authenticated scenario. Or this that's still in preview and in fact I didn't mention is what they're building out is the fact for you to publish an interactive report to the web that would be publicly available. You can experiment with that today. What you will get is an embed code and you may manage those embed codes. So for example you gave it to somebody and you want to revoke that. What you'd have to do is delete the code and regenerate it and then hand it to the people that you intend to keep that code. So watch this story moving forward. I didn't mention it because there's still a lot of lack of clarity around it and certainly with pricing. If they're going to allow something to be anonymously accessed, imagine you place it on a blog and that blog entry goes viral and a thousand people are trying to hit it. I don't know what Microsoft are thinking here in terms of pricing because everything in the Power BI service that I've described earlier in this session is a flat rate per month. So all of a sudden we've got a feature that looks like it's going to be on some billable unit. All right. So does that help answer your question? All right. Are there any other questions? You're welcome to, if you would like to, interact with this dashboard by submitting events. And thank you very much for your time and attendance and patience. I do apologize for connectivity issues but I'd encourage you to go to PowerBI.com. There are a lot of videos backing up and describing the feature sets that I've mentioned today. Thank you and all the best with your Power BI adventures. Thank you. Thank you. Thank you.
BI, tools and this new generation with Power BI, capabilities to the business users themselves. real-time dashboard updates and how this can be Azure integration scenarios, specifically with real-time dashboards, how we could integrate Power BI into applications, and how we could also gives you a taste of what can be achieved. So tables, and to push data to those tables, and the REST API. It will be delegated permissions companion application for the Power BI service. to just the three columns of interest, how about create custom visuals and you may be able to in Power BI Desktop you can gain access to that somehow embed analytics and reporting. You need a need for interactive rich visualizations now as a service you may embed this and you will data and enrich with logic, rich with reports and in this session develop opportunities to develop