Power BI for the Developer | Peter Myers

[Music] welcome to this evening to the session let me go ahead and close what Adam was doing and put up here the title for tonight's session relevant to the software developer is what can you achieve with power bi as a service so here I am in Sydney and I was in Sydney last night presenting to the power bi user group much more focused on the business analyst and I'm going to kick off the presentation in the same way just to do two things learn more about you and also have some data to play with as I demonstrate different concepts so could I ask you just for the next 60 or so seconds to please use either the QR code or the tiny URL when it comes to your location I've only included Sydney addresses if there's anybody watching this live and you're not from Sydney you might choose a Sydney location where you would like to live all right let's just give you about 60 seconds to go ahead and submit some answers to some survey questions and this activity neatly demonstrates one of the topics this evening that a developer can achieve real-time delivery of data through power bi dashboards of which there's one before you at this night at this moment okay thank you number one has come in some software developer aged between 18 and 24 has submitted thank you and will reflect for a moment that what you see here is a dashboard that is designed to present to you key metrics that you would like to monitor awesome okay so there are two approaches or in fact three approaches a software developer can use which we'll cover tonight as one of the topics relevant to a developer is how can you achieve a real-time result in a dashboard yeah the rating only goes to ten if you're at the ten end of the scale there's not much more place to go 10 suggests what that you're the world's expert in power bi all right so thinks there's a great handful of survey results let's just focus on one of them some so exploring one of those tiles in detail they open up in focus mode to see that you know half of you are software developers which is the perfect audience for this evening last night when I presented to the business analyst it was a very different set of topics although there was some slight overlap we have a couple of bi developers a data scientist welcome some managers that might be just here to evaluate what can we or can't we achieve with power bi so thanks for submitting your result on the survey the next thing I'd want to know is well what is the experience here for power bi so what I might do is just drill into a report and this is a very different experience in power bi the dashboard as you just saw was about monitoring it's really a collection of tiles that display results back and can be designed as a real-time experience where as a report as we click through from a tile on a dashboard is more about interactivity you know if I focus just on the females that's submitted you know what age group are they from what is their experience let's take a closer look then in focus mode for the experience in the room so we have one guru of power bi it's mum it is it and then you know as is typical is we have people down at the zero end and so I'll make no assumption about your background and so therefore we'll begin with a very brief intro action of what is power bi and then focus very much on what capabilities are available for the software developer to exploit all right I do encourage questions at any time let's take a look from the location point of view just interesting to map here more people on the north side of Sydney than the south side alright so the other thing I might do is come and ask a question of the data so this dashboard can be interactive at times you know if I want to know what the average distance traveled 11.8 six kilometers if I want to look at this by gender I could see that guys are more likely to travel longer distances and females is that a fair conclusion maybe maybe not what about the coffee question are that you suggesting that the result is biased what is the coffee city by submissions ah well Sydney has the best coffee I get this something flawed here even even with the over sampling of Melbourne three times I would argue at Canberra didn't pop up here at him but arguably Canberra has the best coffees followed by Melbourne alright so the introduction to power bi that I want to give you follows immediately after the introduction to myself so keeping this rather brief I come all the way from Melbourne I'm a business intelligence expert and for the past eleven years have been a most valued professional with Microsoft pretty much because I do a lot of community presentations such as user groups like this my background is working with data and specifically with business intelligence so that's typically with sequel server but in recent years with Microsoft's introduction of power bi as a business analytics service I focus very much on it today as a consultant but also as a trainer and presenter so a good place to start this evening given that we've got people with zero or low experience with power bi is to begin with that overview of what it is we could describe it as a new generation of business intelligence I came in with my career at this point what we referred to as a first wave was when business intelligence was driven from the IT department or decision support systems the older terminologies that we had and essentially there was a recognition that the business needed to ask questions of data and therefore we would produce libraries of reports and and perhaps some interactive experiences and deliver them to the business but we had to recognize that we could never deliver eighty or a hundred percent excuse me a hundred percent of the business requirements that at best that data warehouse was going to deliver maybe 80 percent all right if we attempt to deliver 100 percent we would probably never leave the requirements gathering stage of the project all right so that's where the business intelligence tools evolved to suggest that that 20 percent could be answered by the business directly providing that they were empowered with tools access to data and certainly with education and what did Microsoft do in this space to deliver self-service bi about I don't know seven or eight years ago PowerPivot right so they took the world's most popular application which is Excel used by business analysts anyway and the extensibility story for office is through add-ins so they created a series of add-ins known as power pivot power query Power View and also the black sheep of the family I'll call it was power map anyway these were targeting the business analyst with tools to create small scale BI solutions and and that was Microsoft's contribution to self-service bi and I want to point out that while their names start with the word power they are not power bi that came in the next generation that was Microsoft's cloud-based business analytics service branded power bi and targeting the broadest audience possible not just the IT department not just developers not just business analysts but the consumers themselves should be able to work with this service and within minutes even less connect to software as a service providers bring in reports and dashboards and become almost immediately productive all right so that sets the scene about where power bi fits in but have to confess the BI challenges have not changed simply because technology has moved on and enabled more people to do more we still have the problem that we require analysis to take place over data that isn't necessarily in one source or one format or a bigger challenge today is that that data doesn't reside neatly on our desktop or on a server on a network increasingly we'll find that data is being hosted in a cloud managed service on our behalf it could be in a VM or it could be managed on behalf of a software as a service provider all right so dynamics online Salesforce and so on they have our data and yet there's still a need somehow to bring it all together to enable the analysis that's going to support the decision making that would take place within your business those users are demanding they want that right data at the right time at the right place and somehow we or with our support they need to be able to deliver this so to introduce power bi then almost always when we talk about power bi were referring to the cloud service the initial release of power bi in fact was part of office 365 if anyone recalls you could publish the excel workbook that had power pivot models in it to office 365 power bi so that was the first iteration but then the power bi part was removed from office 365 and it became a standalone service and that's the power bi that I'm referring to today now there is an asterisk if you look closely that it is a cloud-based business analytic service but in recent times that changed and Microsoft provided the ability for you to install on-premises the power bi report server which becomes a superset of sequel server reporting services so it includes the much-loved and mature paginate addres ports with the newer mobile reports delivered thanks to the data zone acquisition that surfaced in sequel Server 2016 and now the power bi report server supports a third type of report which is the power bi report on premises and the recent update of this supports much of the functionality that the service itself supports even data refresh on a scheduled basis can be achieved now on-premises all right my focus however tonight is on power bi as the service not with power bi report server and simply because that product on pram hasn't really evolved or matured to the sense of what the service has the rest API for example that I talked about tonight enabling you guys as developers to interact and do things with the service is not supported with power bi report server alright so everything that I referred to moving on is in relation to the cloud service power bi and I use this slide then to first highlight in the yellow box that it shouldn't matter what data you need to do analytics over providing that it's a modern source of data and reasonably well-structured then power bi is going to let you get to it whether it's software as a server solutions with Google Analytics being a great example for anyone that is responsible for marketing of their organization the fact is you can authenticate to Google Analytics and within less than a minute you could have reports and dashboards that enable you to analyze the traffic and the statistics that Google is capturing whether it's on-premises data and I think this is one of the strongest stories for power bi is that if you have investments with data warehouses and analysis services that could be multi-dimensional with cubes that could be the tabular models you can install the Gateway the on-premises gateway enabling power bi as a cloud service to connect and deliver live reporting over your on-premises investments the beautiful story about this is that the identity of the reporting user passes down to the model and therefore the security policies and the roles that you enforce on your data are honored by power bi alright so if you've already got these investments it's a no-brainer to start surfacing them through the cloud with the main benefit being portal access and mobile access through native applications on iOS Android and Windows alright so I think that's a really strong story organizational content packs really refers to the distribution of your own reports and dashboards within the organization this is in fact an older slide today we refer to these as apps as the mechanism to distribute and share content within an org as your services of which I'll talk about tonight of course the classic Azure sequel as a sequel data warehouse can have live connections from power bi but stream analytics is an example and in fact what drove the real-time dashboard that you saw this evening so it's one of three techniques for delivering real time through dashboards yes just a rename or does that come with extra functionality in the future so the question is about content packs and and being the initial distribution mechanism within power bi it is a it is being deprecated and it's replaced by apps so there are a new generation sharing technique and they strive to address many of the issues of content packs which I'm not sure I should go into right now but the recommendation is do not use content packs now even though that option is still available okay at some stage Microsoft is saying it'll be removed what are the issues with content what are the issues with content packs okay I'm glad you asked Adam and the issue is that when you publish a content pack and somebody goes to power bi and says get data search for content packs add this to my workspace it literally adds content in your workspace and it creates clutter you couldn't distinguish what was my content versus the content that came from a Content pack and then they added a capability called personalization that said oh okay I like what Adam published but I want to modify that dashboard therefore I'll personalize it and then if Adam republishes his content we have a conflict of which I'll be notified of do I then remove my updates and take Adams new version or do I ignore Adams new version and stick with my older version and it created quite a mess and and it really wasn't until Microsoft got feedback from customers that they learned old people literally have hundreds of items in their workspace and content packs have just increased the confusion so now when you publish an app they're available through an separate part of the portal and you just go straight to the app it opens the app and all of the content you've published is bundled together as a single experience all right so while you can still use content packs there's really not a lot of compelling reasons to continue using them the most important reason not to use them is Microsoft won't guarantee that they'll be around in the future all right as your services I've talked about excel files well the great news is if you have legacy solutions with PowerPivot and then you can continue to exploit this but I would strongly recommend that you would take those Excel workbooks and you would upgrade them to power bi desktop so there's a one way process where the Excel workbook becomes a p bi X file whereupon you can fully exploit the latest capabilities and features of power bi alright if you're going to continue working in Excel you will have limited access to new capabilities and features and also even the size of the data models you work with a restricted if you're in Excel so it gives me the opportunity to talk about the companion application which is a free download from Microsoft power bi desktop 32 or 64-bit Edition according to your operating system and it is a remarkable tool in fact I need to present on it separately because there's so much to say but essentially it is a modeling tool we don't need Excel anymore you don't need the power pivot add-in you can use PowerPivot power bi desktop to extract data transform data model that data and then build reports on top of it and the intention of power bi desktop is that your solution all stored in the P bi X file is then published to the service so that's why I mentioned it's a companion application to the service and this is basically the tool for the business analyst question the story the story behind the power bi desktop file and source control ok today it's a very short story in that you're responsible for that asset as a file so you can check it into source control systems as you see fit I don't believe there's going to be a strong story in the future but that's yet to yet to surface well at RDR file reporting services report definition language document is typically developed by the IT pro in a visual studio project there is no such concept for power bi desktop but we have to think of audiences here and I'm glad you've asked a question now because power bi desktop targets the analyst how often does a business analyst sitting outside the IT department ever engaged with a source control system I would say likely never if you would have developed models as an IT Pro then you would continue to use visual studios at Visual Studio and you develop analysis services projects you'd be deploying either to analysis services pram or the azure analysis services managed service all right then you can check those models into source control what is your recommended approach for devtest production scenarios devtest production scenarios I don't have a recommendation that working part with you right now and simply because power bi in its initial releases has focused on the business analyst okay but increasingly as we're seeing organizations and large organizations become really interested in power bi this is becoming a very common question and Microsoft today don't have a strong response to that all right so the concept that we might need you know better controls staging environments migration from environments we don't formally have that so you just have to make use and exploit the capabilities that you do have and what I would strongly suggest is at this point power bi desktop should always be your tool for modeling and reporting unless you're an IT Pro and then you can always republish reports from power bi desktop stew different workspaces and therefore workspaces could become your staging test dev environments all right there is not a strong story to today so you're likely to want to come up with your own internal policies that ensure that you mitigate any risks associated with you know corruption version control issues and so on all right to the website and downloading the P bi t file and manually putting their source control P bity a template file bi X you know just the template you can own so it's not binary no so the P bi T is a template that is parameter arrives that allows you to hand it off to somebody they fill in values and then it inflates to become a p bi X so the P bi X file you can only download it from the service if it was previously uploaded by the service if you created reports directly in the service you cannot download a PB IX file for that so therefore all report development should happen through power bi desktop which means you've got an asset to redeploy if you need to and then you can manually check this into source control systems awesome all right power bi desktop well for those of us in Australia you're no doubt aware that today was the publication of the marriage equality results so I just I just pulled these down a couple of hours ago and I'm not terribly satisfied by the you know the format of their data like since when should we need images and and not the most friendly format I mean this is an XLS X file it's it's not even XLS X it's the old excel file it shows how current our Bureau statistics in Australia is but it's been designed for human reading and that's a little disappointing because today we've got great tools that if you give it raw data like a CSV we should be able to suck it all in and then analyze it in the way that we would like to so they gave us two workbooks I'm not going to dwell on this but it's a good example of power bi desktop these are the responses yes no from State down the division and then we also have a second sheet that says this is the partition participation by age and state and division so some context that's right if you're not from Australia is that the Australian government proceeded with an online survey asking all enrolled voters yes/no do you approve of changing the marriage laws to support equality for all not just those of how can we say these days non matching genders whatever the right terminology and so the results of those polls were announced publicly today and the result was I believe what 61% yes across the country so what I did with this data was I came into power bi desktop and I'll take you quickly to the Edit queries because essentially from those two workbooks I was able to do things like this so for state participants or let's look at state division response um this is the data that I really needed for my analysis give me state division and then the yes is the no not clear and not responded now take a look on the right hand side because from the source document which was the Excel navigating to a specific worksheet I was able to perform all of the transformations to ultimately arrive at the data that I needed this is an enormous leap our fulfill theirs anyone recognize where it came from this capability of power bi desktop first appeared in Excel not powerpivot its power query right so the power query add-in available in Excel up until Excel 2013 it was a free download that you could install to excel but as of office 2016 exhale includes power query natively by the name of get and transform alright so if I look on the ribbon for excel on the data ribbon here get and transform is the power query capability now power bi desktop uses that as an ability to connect to a very wide range of data and perform transformations as you can see here to ultimately arrive at what you need load it into your model so what got loaded into my model you can then see through the relationship view here I bought in State Division response I've got a division table and relationships and enabling me then to produce this type of visualization so if I want to answer the question well by descending order of the number of yeses per number of responses so the green here says the Australian Capital Territory produced the highest es result of 74 percent but the sixty point nine percent says that Eve everybody that was enrolled to vote voted then the yes is 60% of all enrolled so the AC t you'd have to suggest is the most liberal conversely we came to the know we see it's New South Wales that were most in favor at no at forty two point two five percent of those that responded for those that didn't respond at all Northern Territory would have to be the laziest state or territory that we have and then this one boggles me a bit unclear responses so we can see that there were eleven thousand and twenty eight responses that weren't clear does that mean they just missed the box I mean yes no or I don't know or didn't write anything okay but there's a great example of why we might use power bi desktop is the source data we have is just not suitable for the types of questions that we need to ask more often the reason we use power bi desktop is that we want to integrate data from multiple sources the Bureau of Statistics but I'd like to bring in a population data set or I don't know anything else that would enhance the analysis we need to do so I cannot overstate the importance of power bi desktop but at the same time stress that this is a tool targeting the business analyst and please do not think that everybody that engages with power bi needs this it's probably around five percent of the organization that actually create data solutions as opposed to the reports they publish and the consumers the larger audience of power bi are those that simply need to find reports interact with them and hopefully answer their questions this is giving you a good idea I hope of what power bi is a service now I can start to talk to you guys as developers so you'll see the REST API here is an interface supporting you guys as developers to connect to the service authenticate and do various operations dwelling into who these users might be just generally business users are consumers and the service is geared towards that end user experience whether it's the web portal or whether it's a mobile device of their choice phone or tablet the business analyst is really the person that produces data solutions power bi desktop would be their number one tool and they would publish models and reports to the service the BI professional would come from the IT department they might work with configuring gateways enabling permissions licensing education I think is really important and that's one of the services that I offer but please don't assume that because it's an end user centric service they'll just work it out they might eventually through a lot of trial and error and time but it really does require internal training when you produce new models and reports you know have lunchtime sessions invite people and then show them how best to use those assets the other audience and the focus for this presentation this evening then is for the software developer and that they can integrate applications with power bi so that great report or dashboard really needs to be part of the application experience and especially where the users of your application are authenticated by your application and do not have power bi licenses so this can be achieved and in sense power bi becomes platform-as-a-service whereas every other discussion I'm having with you is power bi as software-as-a-service creating real-time dashboards developing custom visuals and developing custom connectors and that very much sets the agenda for the remainder of this evening before I go into any more detail upfront it's helpful to understand the licensing for power bi of which there are three different license tiers the power bi license itself is for free and this would enable you to create power bi desktop solutions and the most beautiful reports and dashboards however they will be constrained to your personal workspace only without the ability to publish them as apps or to share dashboards so that works perfectly well if you live in an isolated world of just me the moment however that you need to start sharing assets then you're likely to ensure that everybody has a power bi pro license and at the cost of maybe US dollars 10 per month per user or by the way office 365 f5 license I think includes power bi Pro so you may be lucky you already have it all right but this enables many other advanced features like scheduled data refresh connecting to live services like analysis services or direct connections to Azure sequel or on-premises sequel Oracle Tara data but most certainly when it comes to sharing as a sharer or as a recipient you will need a pro license now that can be not always the case when I talk about premium license which becomes very important for embedding a power bi and premium license allows you to reserve dedicated capacity in the data center whereupon if you create your data assets in a workspace on dedicated capacity this could be achieved with power bi premium license then you can share that content with as many power bi users as you want they don't need a pro license at that point the only people that would need Pro are the authors that would publish to that workspace and then you could share to a billion free users if you really wanted to all right at which point your consideration under premium license is how much and therefore what price am I going to pay for that dedicated capacity you'll learn this evening also that Asher has the power bi embedded managed resource which has dedicated capacity as well so you either get to it through the power bi premium or you get to it through as your power bi embedded in order to support the embedding scenarios that I mentioned this evening all right any questions about licensing yes right so the question is if we use power bi embedded can we connect on-premises with a the Gateway you are not restricted anymore so there was a v1 with power bi embedded that said well there is very limited functionality of what you can do that has gone away and if you have that background and in fact I think I was in this very building in February earlier this year talking about that topic that is now would just ask you to forget it Microsoft went down the wrong path they acknowledge this and the path that they got on track with was to say the app workspace that you're going to enable embedding from is no different from any other workspace that we work with well well the difference was that we were restricted to certain scenarios all right so when do I get to that topic this evening I'll mention it again pretty much anything you can do as a business analyst to produce reports and dashboards can then be embedded into your own application awesome so the following slide is just going to that detail that I just mentioned and that gives us the background I hope that supports the discussion I then have in talking about power bi for the developer alright we'll introduce to you the power bi rest api integration with Azure services custom visuals and connectors and then the embedding of power bi analytics all right so I suggest that there are five exciting opportunities for you guys as developers to leverage power bi either as software as a service or platform as a service let's begin with the REST API here is an application programming interface that enables you to do certain things with the service one of them and I'll demonstrate this up front is delivering real-time dashboards by pushing data to data sets that you can programmatically create alright so a little bit of theory here upfront when we talk about the object model hierarchy the workspace in power bi is the basic unit of containment and every Power bi user has a workspace known as my workspace alright please don't use this in an organizational setting because there is no supported path to migrate content from my workspace to the app workspaces that are independent and they have a membership of people that can collaborate together on content alright so I cannot think of a good reason why you should have content sitting in a personal workspace because imagine you get promoted you move to a different role in the company and then the person that comes in to continue your role has no access to that content alright so all the workspaces are the same structurally they store for fundamental object types the data set workbooks reports and dashboards alright so if I go to my power bi comm we see that right here that the demo workspace that I'm currently set to has a single data set no workbooks one report and one dashboard now if I click on a data set you'll then see that a data set is in fact one or more tables and tables then have fields and that's exactly what this slide is describing data sets have one or more tables and then we may add rows of data to individual tables all right now if we're going to work with the REST API we need a registered application with the azure active directory and let me then switch to demo here that if I just do a google search here and I type in the power bi app registration tool the easiest way to register is just use this tool you could go to the azure portal and you could sign in and create the app but step one here just says you just need to authenticate step one is complete you need to come up with a name demo Sydney dotnet which it probably doesn't like that dot net user group is it a server-side web app or a native app tonight I'll demonstrate with a native app and what is a redirect URI it doesn't even need this but as a minimum configuration we need to provide something so Sydney demo is sufficient step 3 what permissions will this app require to do what your application is doing does it need to read the data sets within a workbook doesn't need to write to them so if you're pushing rows of data then of course it needs that permission does it need to read dashboards reports does it need to read and write reports ie the editing of reports could be achieved as well does it need to read the groups or that really should say app workspaces now what app workspaces are you're a member of can we programmatically enumerate them and access the datasets reports and dashboards within those and can we create content so tonight I will just click them all for safety and then step four is the easiest just register the app that registers with the azure active directory that app is uniquely identified by this client ID or gooood right here when it comes to authentication then you need to authenticate the app and the power bi user that will be using your app these slides if you refer to the PowerPoint presentation later just talk about the authentication flow having achieved authentication your application will cache an or an access token and that token must accompany every request to the service and it's valid for 90 days so when you make a request of the service you're going to work with common operations like go in and numerate datasets create a data set list the tables in an existing data set I've changed my mind modify the schema of a table let's add a new column add rows to a specific table in a data set remove all rows from a data set list groups dashboards tiles and reports there are other operations but this is a set of common can you remove a single row no I wish we could so the best you can do is a raise all rows in one hit okay so there is a limitation we'll talk about that so here's an example we send a request to the service and so let's take a look at this a get method to this endpoint go get all datasets here is our access token if that succeeds ie 200 response you'll get a JSON document back and essentially it's JSON and you can see here an array of datasets and the objects that you have within power bi are always identified by a good the ID property and then the name is the friendly name and that's the name you see within your workspace a second example then adding the row to a table this time we see we've got a post request see how we go to the datasets and then the ID of the data set so part of your job as a developer is to look up the identifiers so they can actually do an operation on the table and then we see we go to the tables of that data set specifically the product table to its rows again we pass the access token and then the body attached to the request is an array of rows and they're simply column value pairs alright you can push in a single request a maximum of 10,000 rows just comma separate build up an array of rows alright so let's take a look at a first demonstration then so what I've written to demo this is a sample application and by sample I mean it actually shows you how the API works so it's not a finished solution so I'll come here and simply run it and then I open up the options for this application and it wants to know what the client ID will be well let's just copy the client ID that I've created into the options and the redirect URI needs to also be copied in now ordinarily these two are going to be stored in a config file right we don't expect the user to plug them in but for this demo experience this is how we do it these endpoints are fixed so the service isn't going to move you must target these endpoints to do the various operations then the application your application is going to challenge your user by having them authenticate with the service and note that when I submit my credentials I'm not a hundred percent confident I typed that right this is where we see aha you're being warned as a user that this application will have these permissions if you enable it this is no different that when you install an app on your phone you know how it pops up and says oh this app needs to access your contacts is that okay you really have to trust the logic of that app that it's not going to read the contacts and email unlawful or shoot them back to some central database right so you have to give a thought to the credibility and the trustworthiness of the publisher of the application because obviously this is a demo and I created the app I accept that and if authentication succeeds my application as you'll see here has cached the access token and it will accompany every other operation that I do all right let's take a look at groups of which we see I have a demo group and that represents the app workspace here we can see that I have a single data set named survey and in fact if I get data sets and this is the exact example that I showed earlier a get method request it says in fact is to the dashboard usage metrics model is in fact a hidden data set that supports the analysis of a dashboard all right it's not one that I created but the survey is where your survey results were and if I get tables then there's the survey table where all of your survey results were pushed now what I could do is right-click push more data update the schema of it or I could clear all of the rows but I don't want to do that just yet because I need the data what I'll do in demo then is create a new data set and I have a snippet that is sent essentially the JSON representation of the data set so let me copy and paste it and then show you what this looks like go ahead and create a data set with the name ecommerce operations we know that data sets have a collection of tables there's just the one named product browsing and it has a collection of columns these three of all data type string the only other consideration will be retention policy because when you push rows of data to a table there's a maximum storage limit of five million and when you hit that you're just going to receive errors and the only thing that you can do is either stop pushing or clear or rows if you opt for the basic FIFO as I'll do in the demo now it says that you'll store a maximum of 200 thousand rows and as you start inserting more it'll simply delete the older rows so you move forward with 200 thousand what this might mean then your strategy could be let's have a service that runs every 24 hours and clears the rows so when we come in in the morning we're seeing a reset from midnight that might be one strategy but I have to point out too that when you push rows of data and watch where I'm pointing immediately a data set was added to my workspace when you push rows of data this is not a store that you can easily retrieve from except to visualize if your intention is to permanently archive the survey results tonight then you should also push them to a permanent store like sequel database all right for the purpose of real-time analytics no worries push it to the service but don't expect that that's going to be useful for historic analysis later on alright now that I've created a data set I could get the tables of that data set to see there is my product browsing and then what could I do with this well I could push and it's just that JSON document that a row array of rows so before I actually do that when I return to power bi comm and click on the data set we can then see in the fields list my dataset consisting of the one table product browsing I could drag product to the canvas these are all text fields I'm going to express it as a card and I just ask it to count the number of products now whenever you see blank coming back over as a response it says there's zero rows that were evaluated of course there's nothing yet in this table but I go ahead and I save this as my product browsing report and I must do this in order to achieve the next step which is to pin this to a new dashboard named product browsing by pinning that visual I now have a tile on my dashboard which is the counter product so let's see what happens then when I take this JSON document that's going to push a single row a product of type helicopter into the table let's do this because it's something you want to see push that single row and there we have it and then I could continue to build visualizations but how about I just used QA so you know show count of product by country and power bi should be it wasn't smart enough to show that on a map and there we see there's a big one in the middle of Australia all right let's pin that back to the dashboard and then I might change this to say counter product by category and we'll pin this as well so with a dashboard that looks like this the next thing I'm going to do is just simulate some real activity and that's just achieved by me running a stored procedure and caching some data and randomly it's just going to push ten rows after ten rows after ten rows and there it is programmatically creating a data set and then using it to push rows to ultimately at some point you will need to clear rows well basic flyff oh I'm sorry I don't need to clear rows but if I use the clear rows then immediately it's reset so that's my first demo delivering a real-time dashboard with the REST API and I able to update a row now the only operations on a table are to add rows and to clear all rows all right so if you needed that capability what you're more likely to do is use sequel as your sequel database and a direct query connection to it okay but but here's the discussion you need to have with your clients is that what do you mean by real-time and especially when you start stacking up that you know there's there's a high cost for genuine real-time all right and then you start to say well that's gonna cost blah as an estimate they're like okay every 15 minutes is close enough all right so for the volatility of data and the decision-making that needs to happen and the frequency of decisions if it's every 15 minutes and you already build dashboard tiles on data sets that are doing direct query connections to sequel Oracle teradata then they will automatically update every 15 minutes anyway you don't need to do anything programmatic alright so please do inquire what do you actually mean by real time and what latency is acceptable to you and if it's less than 15 minutes then consider this technique alright anything greater than 15 minutes look at direct query just get the data into sequel and allow direct query over the top of it alright maximum rows per table five million unless your basic FIFO maximum ingestion is 1 million rows per hour and it will point out that this is also supported in the free power bi license there's no restriction free license allows you to do real-time pushing to a dashboard you just won't be able to share that dashboard with anybody there next discussion is Azure services so there's already the direct connect option with Azure services like sequel database sequel data warehouse even as your HD insight with SPARC workloads the story that I want to cover off on is an IOT scenario whereby you have all of these devices out there let's just say by example they're on pieces of equipment and they're constantly submitting temperature as a single value a Microsoft solution would then engage as your event hub so event hubs are designed for scale ingestion of events all right essentially typically JSON documents are just being pummeled and pushed into these event hubs the question is then well what are you going to do with those events and one option is that stream analytics as a service can perform time-based querying over them all right so what I'm going to do as I describe this is I'm going to jump to a simulator that I have here and I'm just going to start pushing web browsing statistics to an event hub it's exactly the same data that I just demonstrated and then we can see that we've got batches being sent representing people clicking on products on our web app and we want to know real-time where are they what are they clicking on and so on so the story here is that stream analytics can query across the event hub and it typically does so by using time window and let me explain so if I switch back to my browser and to my Azure portal what I have here is a job a stream analytics job the input here is already defined to connect let me reload to connect to the event hub where I'm currently pushing all of these events all right so the click stream and that becomes the input for my stream analytics job I then have a query but before I do let's consider the output which I'll create a new one which is going to output to demo the output for this will be power bi I need to authorize now we're using exactly the same REST API at this point as you can as a developer it caches an access token I tell it to use my demo workspace and to create a data set name click stream there must be at least one table in that data set so I'll name it also click stream and now it creates that as an output remember it's named demo lowercase D so I have an input from the event hub I have an output to power bi and then I can come back and it's the query that's going to take an input and transform to push to an output so let me go ahead and open up some snippets that I have here for Asha stream analytics and the snippet is this if you understand sequel as a database query language the azure stream analytics query language is based on this but there are some differences the into will be to my demo output and the from like a relational table is in fact the click stream input so when you look at this this is just like sequel but there's a bit of a twist first of all from the click stream input which is the event hub go ahead and give me system function the timestamp so UTC what time did this happen give me country category and product and count star what are the number of rows from clickstream group by those but interestingly group by the tumbling window so this is a specific function here that says consume two seconds of events and then run this query and push the output so every two seconds it's aggregating across time and it pushes using the into into my demo output let me go ahead and just save this and it takes about a minute for this job to start so having saved it I come and start that job this is a remarkable service so it's very IOT centric it's about aggregating lots of events into a smaller subset what else it could do is it could output to multiple outputs you're more likely here to also output to a persistent store and this is where as your data Lake would be a great example just output to timestamp to CSV files so we have a permanent historic archive forever in the data Lake but for real-time analysis in a dashboard we're also shooting it off to power the I okay you can also bring in multiple inputs and join them together so it's quite a versatile service and its job is simply to condense multiple events over time and output yes great question so will require you to come into the azure portal every 90 days and reor Fedak eight because the access token is limited to 90 days yes I know so I would suggest when you do this you're going to put a task in your calendar I am pretty sure there must be an API that allows you to update the token but I've not done this but I'd be surprised if there wasn't one this is going to take maybe another 30 seconds what we should see is either a data set pop-up here the data set will not be created by stream analytics until the very first output is pushed all right and because I'm pumping data there right now so 195 batches I've sent almost 14,000 events that as soon as that job is started and there it is still starting it's using exactly the same wrist API that I just mentioned to you guys alright so stream analytics doesn't have any secret backdoor into the service itself so now we see it's running if I switch back here if you don't see a pop-up or we do there we can see we have click stream go and show me the number of clicks that have happened it can't be blank refresh four thousand four hundred and eighty five so that's as many that have happened since the service started not when I started the simulator so just to wrap this up I'm gonna save this will become my click stream report pin this up to a dashboard and what I like about this example is there was practically no code right assuming that I had an event hub already it was just a matter of writing a query in a stream analytics job and then we've got real-time updates appearing here now one really cool thing though is if I come back to my snippets I can use the Q&A feature to say go and show me the clicks that just happen in the last 60 seconds and pin that so you can use English expressions providing that you have that time and remember how I had that time here as an output in your result all right so I could pin this and I think I already did and the other one that's quite cool is clicks buy time and there we see the last 60 seconds being pushed now you see that pause there remember it's every two seconds because that's our tumbling window duration we could have said every one second every half a second just keep push push push push pushing awesome so that's the delivery of real-time results using the azure stream analytic service and there's a very interesting story to tell here too with Azure machine learning which is the cloud-based predictive service is that you can publish predictive functions while power bi won't work with them directly stream analytics can and I can point out to you here in the portal that I could come back to my job here and I could define functions I could add a function that calls as your stream analytics and then in my query I can reference that function so you can have real-time predictions also so there's a very strong IOT story here they've also predicting when maintenance is required and power bi could also be used as an alerting service when a threshold is reached ie a certain prediction result is returned go ahead and trigger a flow that might then email or notify maintenance people in that region okay so that's that's not really a developer topic is it because I didn't do any code but it's showing an application of the power bi of REST API that was demonstration number two delivering a real-time dashboard with as your stream analytics I'll do one more quick demo because this does involve code for those that were the user group yesterday this was a feature of the power bi dashboard of which I'm now going to create a new dashboard called streaming and I'm then going to add a new type of data set named a streaming data set so as a streaming data set this is where stream analytics could also work but we can also create our own and the example that I typically use is just that room temperature we want to monitor this assuming we have a device so let's have a look at room temperature it's going to have a single feel of temperature only three data types so it's going to be numeric and we can see what the JSON document will need to look like if we're going to push a row of data to this streaming data set by the way if I turn history on it becomes a push data set no different to what the rest API would create for you when you turn it off it says it's purely a streaming data set therefore it's optimized for streaming of data and it will not store data beyond 60 minutes when I click create the streaming data set is added and we also get the PowerShell script here to work with it all right so we now see room temperature available but when I click it in fact you'll see this here become two data sets within the workspace there is no ability as we have for these three to build reports on them streaming data sets only work with streaming tiles in dashboards well let's see how it plays out so I'm going to open up PowerShell and paste in that sample code the first line is a variable declaration to the endpoint and there's two parts to the endpoint this here is a path uniquely to power bi end to the data set you've created the second part that includes the key is the authentication so unlike the rest API where you must have an application registered you must authenticate and gain an access token here all you need is this URL next the variable here for payload let's change the temperature to become something more in centigrade that's a twenty point five and then we just invoke the rest method a post to our endpoint and then converting this text into JSON before I run the script let me then go ahead and switch to my streaming dashboard I then add a tile that is a special tile called custom streaming next I choose my room temperature streaming data set i express this as a card showing temperature and let's show this to two decimal places so this icon here tells us it's a streaming tile on the data set and now I can return to PowerShell and run that script so much easier than the rest API approach where I will take this is one step further which is to create a second data set this time with two fields I'll call this room temperature two because I'm going to also include the time stamp of the reading and the temperature when I come to the powershell script i'm just going to copy the first line because the only thing that i'm going to update in my existing script is the end point in its new key and then here I'll create an infinite loop so while true do all of this and we just need to add in the time stamp which equals get date and we format this to universal format so now infinitely it's just going to push that let me trigger this so now it's just pushing infinitely some random temperature but with a timestamp and now I can come back to my streaming data set and add in a second time this time what I want to see is room temperature too as a line chart showing time stamp on the axes and values is the temperature under here is where you can see max 60 minutes is what you could show tonight I'll say just show the last minute of data and it doesn't like that we couldn't update the real-time tile why not did I you tell me this now what what did I spell time stare bears so I left the e out is that it temp standard you got to tell me earlier guys hmm okay well the way I would troubleshoot this is come to my data sets find this one go to the information for this one and I would see here it is temp stamp and it is temperature you're right Tim stamp and temperature and we should be pushing rose to it come back to streaming delete it start again add tile I don't know why I need to do that line we don't get an error this time that's a good start so one minute we might come back to that one what I do want to point out though is that when you see it running and you compare it to the click stream this one's a little jumpy in fact these are all jumpy when you push the tiles update within milliseconds eventually but the streaming tiles are designed for very fluid oh let's fix that not 20.5 but get random with a minimum value of 18 and a maximum value of 20 2.7 have a look at the fluidity of these streaming tiles alright so for a developer you have choice the benefit of the streaming tile is it looks sexier and doesn't require a complex authentication downside is it only shows a maximum of 60 minutes of data awesome so that's demo number three delivering a real-time dashboard with the streaming data sets moving pace now into the visualization of data and Microsoft provide a wealth of visualizations you can see this when you come to a report you know like my survey report here if I go into edit mode already you've got I believe over 26 visualizations out-of-the-box they work with the standard visualizations like bar and column charts pie charts donut charts Maps even and even slices and and and cards but that's not great for everyone and so for example I might come in here and say well why don't we just import a custom visualization it could be one that we've developed internally to meet the requirements of our customer or our business itself and let's start producing things that are way more sexy and that relate our data to our business users all right so custom visuals can be added to power bi desktop reports as I'm about to demonstrate you can add them direct to power bi service reports and they behave precisely like power bi visuals the native ones you can also optionally submit them to the office store and enable others to benefit from your hard work and creativity so from a development lifecycle perspective you create code test and package by using the custom visual tool and it's also a versioned API so if they release future versions it's locked in and the service will understand the version in which you develop it so once you've tested it and publish it even in the future we'll continue to work as you've designed it you can use a choice of development tools like Visual Studio code and then for the testing it is achieved through Power bi calm as I'm about to demonstrate if you choose to and it's optional you can submit your custom visual so that others can benefit from it as I'm about to demonstrate so let me import from store and a favorite one for demonstration is there is the enlightened aquarium so if you're a fish and chip shop and you want to show your data in fish of an aquarium then you'll see here then that I have now got an additional visual that I can work with that was achieved by importing a pbi vis file and that contains everything that the visual needs now the cool thing about developing these is that you don't have to worry about how to retrieve data from wherever the data is so power bi via the data set abstracts you as a developer it'll just deliver the correct results as and when and you just need to develop the logic to visualize that so let's take a look at where the best coffee in Australia comes from by the number of submissions and what was that big fish should have been Sydney so ten submissions for Sydney the small fish up here is for Perth the properties here enable certain customizations like the series we don't want round fish we would like sharks all right and this just gives you a taste of you know a fairly simplistic custom visual but really when it comes down to presenting the data in the way that makes sense to you and your organization then you can achieve this with custom visuals now to the interesting part for developers is it will acquire a very specific and advanced skillset JavaScript typescript html5 SVG CSS and typically and almost always web drawing libraries like d3 or WebGL so if you have those skills and you can put it to work by creating custom visuals the demonstration that I'd like to show you then is how we create the world's simplest visualization so this is my fish report fish coffee analysis what I'm going to do is save this and then I'm going to open up power shop I have pre-installed of the tools and so the first thing that I'm going to do is just show you that the toolset has been installed someone's go into a lot of effort that when you call pbi viz you get to see this and the range of commands that are supported okay let me go ahead and go to my D Drive c DD demo CD we're up to demo 4 and I'll create a new directory named demo biz I come back to PB I've is and I type in new I'm gonna create one named demo beers I've just created a custom visual if I go into demo beers there are all of the files required by the custom visual and I'm going to open this up in Visual Studio code now we could spend a whole day going through how to create custom visuals and it would still be the tip of the iceberg but what I'm going to show you is that the file here which is visual dot TS this typescript file the important method is this one right here okay the update method and let me explain why I am now going to start my custom visual it is now hosting the custom visual as a web source well that enables me to do is come to power bi to my settings and in settings I'm going to enable develop a visual for testing with that enable then I come back to the survey report I then edit the report let me add a new page what you may have noticed is that a new visual is popped up here and if we add this visual to the report it is the custom visual that is now hosted on my desktop so let me add an instance of this and it's telling me that the update count is zero and the reason for that is that the sample code is just doing something very very simple you can see it right here in the constructor it's created this HTML Appy tag it's put some text that says update count and then down here in the update method it's simply doing an incremental on a local variable update count so it's the world's simplest and not most interesting visual let's go and add the number of submissions as the measure now watch every time that I'm resizing or moving this that the update count updates that tells me that the update method here has executed 60 times and what that tells us as a developer is that this is where all your logic goes where you're going to take whatever input state there is and you're going to render something back as the visual now there's a lot you need to consider has somebody clicked on a fish that they hovered over a fish has a filter from another visual interacted with me therefore how should I highlight what they've cross filtered by so not only do you need deep skill sets but you also need a lot of programming skills and intelligence to know that I need to react to an awful number of things that are going on all right the great news is that Microsoft make all of their visuals available through source code so you can learn how they've developed their own to develop yours and as a very very simple thing to do here instead of returning here just an incremental number what I could do is go to settings no go to my options so options is being passed into the update method we have a data view collection of which I asked for the first and let me show you back here that I can switch to look at the date of you so look at this if I drill in and I treat this as a single value I can see here that the value that's been passed in ie the number of submissions is actually 17 so this is actually giving you the object hierarchy of the data view this is the input to the visual so that's from single to value so I can come here and make a simple update to show that value we then say go to single show the value to string now when I save this if you have a look back here it has rebuilt the visual and published it and now the service has access to it immediately and I just say refresh and we can see that 17 is now the number that is a number of submissions that came through that is tip of the iceberg as a developer you can also customize all of the properties you know whatever it makes sense can be exposed here and when you've done you build this exported as a pbi this file and then you use it by importing it into reports so it's an awesome story but it does require as I've said deep skills alright you can also moving to the next topic develop custom connectors alright so if power bi can't directly get to the data that you need you can use a connector that is either an ODBC driver based or odata service based or M based and M based means you're using the power query language to get to and transform the data and make it available to the service so possibly not a software developer task or possibly but you can then have an extensible story to allow power bi to connect to your proprietary data or older systems of data last story for this evening embedded power bi analytics so we move from the software-as-a-service story - now platform-as-a-service and the discussion then to be had is this the concept of workspaces is that as I've described earlier everybody has a workspace as a container for their datasets workbooks dashboards and reports when you create an app workspace you have the ability down here under advanced to turn premium on this requires either that you have a premium license with power bi that is dedicated capacity on a data center with a certain scale and you pay for this or you've gone to the Azure portal and you've gone ahead and provisioned a power bi embedded resource now when you've done either of those two when you turn this on you have a drop-down list that says this app workspace will be created on the dedicated capacity and then you can create your reports and dashboards inside there that is the first step you then need to create a dedicated account in your Azure Active Directory that will act as the master app account and it must have a pro license and what you're going to do when you embed power bi into your own application is you're going to authenticate with the REST API by using the master app account and then programmatically you can enumerate reports and dashboards retrieve their IDs and generate an access token to enable the author the ability to embed that resource into an iframe of your app you're then going to download and install the REST API the JavaScript API so this is client-side and what it comes down to is that you're going to have to call client-side by passing in the tokens and IDs to then embed the report on the dashboard so that's as far as I'll take a high-level discussion on it to say that it is all embeddable the other compelling experience is that you could also pass in security context that while my app is authenticated Mary and we know according to our own Identity Management that Mary comes from New South Wales and she's a manager then we can map that programmatically to role that enforced row-level security so that Mary only sees the patient data from New South Wales hospitals all right so the application developer your application continues to take responsibility for authentication using your own users and when it's ready to embed analytics from power bi it authenticates via a master app account presents reports and imposes filter context to ensure that your users only see the right data all right what it means is you can embed reports they become fully interactive dashboards can also be embedded but they're read-only individual tiles and even that QA capability can be embedded into your applications so scenarios might include authenticating users by using your apps identity store limiting data visible to them and this can work for both external or internal apps um the external apps is more interesting because it's you embedding on behalf of users that do not have power bi licenses you can also embed your current users who do have a license for power bi in which case you do not have to work with power bi embedded resources or power bi premium if you're just embedding on behalf of the power bi user examples would be we don't like the look and feel the power bi com then you could do this without anything special the moment you're embedding on behalf of non licensed users you have to consider power bi premium or Azure power bi embedded to this cap off with the JavaScript API the seamless integration of analytics into your apps can be achieved through the client-side JavaScript API whether it's embedding specific reports navigating the pages imposing filters this is best done through this quick demonstration on exploring power bi embedded and I include this link for you as well but Microsoft have published an embed sample whether you want to embed a single report or QA or a dashboard or a tile let me show you their sample for embedding in View mode a report so they already give you the embed token the identification for the report and the URL to the report or if you going to generate them based on for your own report you could paste them into here it then shows you the client-side code that would then embed into the iframe that you see down here so this is really simple to demo because I just run the client-side code and let me point out to you that this is where in this configuration object it sets all of the access tokens and everything required to achieve the embed and finally it is this one method here that performs the embedding into your iframe all right so let's run that code and then we see the embed happening right now and this report is fully interactive applying filters modifying filters okay and that's tip of the iceberg that gets an interactive report in this is platform-as-a-service power bi is now used to support your application if you switch to step 3 this shows off all of the power bi javascript api capabilities like take a look here these are actually different tabs report tab page tab events tab and edit and save all right so on the report page what are the what are the pages that this report has run that client-side script and it can tell you that there are four pages on this report as we see down here okay I want to set a particular page run this and you can see how a page was selected so it could be that you jump to a web page and via the URL you also pass in the page that's relevant to them all right setting filters removing filters printing full screen mode if you come down to page level you can make a page active you can apply a filter remove filters events is interesting so the data selected event if I run this now if I click on something like this bar here you get full intelligence back through this JSON document as to what was selected that allows your application when they click on that bubble to do something intelligent right so this is where the integration can be achieved all client-side with the JavaScript API just to remind you to achieve embedding on behalf of non power bi users this will require either power bi premium or the azure power bi embedded managed resource there's one very quick last demonstration I'd like to wrap up on when you guys if you participated in that survey did you keep the page open all right for example when I submitted these values I want to show you one feature that is a sharing technique that is for free and it's not really a developer topic but I like to show it because a lot of people are really surprised by this capability all right so when I click Submit there was this reminder on the survey to leave this page open and let me now demonstrate that when I come to that survey report that I want to share it back to you guys so let's come and edit the report save it here on the file menu I can publish this to web whereupon I can configure the size of the report and there's an embed code that supports the embedding all I'm going to do is come back to the online survey and to that Thank You page and publish an update to the page that embeds the report for those that kept the page open when the publication here succeeds all you'll need to do is reload that web page what wait yes quick quick quick so we published the page what that should mean then is if I reload I have an embedded report and notice at the bottom right corner you can switch to full screen mode so if you want to analyze the results of tonight's survey you have a multi-page report including swimming fish that enables you to analyze those results and interactively so this is an embedding story but it's not related to the power bi embedding topic that I just gave you this is a feature called published a web four reports only even free power bi users can do it but you have to be aware that it's non authenticated access to the report and anybody they can gain access to the page can then interact with your data so it's really designed for a capability that we call data journalism for you guys that are bloggers or need to describe something it's often better to achieve it through an interactive report then paragraphs and paragraphs are boring dull text alright so you do and sometimes you see tableau in the bottom left-hand corner as the advert alright so Microsoft are encouraging you where you have public data at no cost even on the free power bi license go ahead published a web there are some restrictions you can't do it if row level Security's enforced or there live data sources but if it's cached data as is the case with this push result then power the I published a web is for you all right I'm gonna wrap up with an extremely quick summary developer opportunities for you is will develop what makes sense to you for Business Analytics you can refer to power bi as software as a service or platform as a service you can use the REST API to create data sets and push rows of data to achieve real-time updates in dashboards you can integrate power bi with as your resources the one on topic this evening was real-time dashboards with stream analytics develop custom visuals or connectors to achieve what you need in visualization or data surfacing and then you can follow the embedded how the ice seamlessly into your application courtesy also of the JavaScript API lots of resources here so I'll make the presentation available in PDF to download from where Adam SSW comm so wherever you found this great video or the streaming this evening within 24 hours we'll have the PDF including these helpful links available for you including that embed sample which is enormous ly helpful if you're looking to embed and understand what you can do all of the functionality is in that updated sample from a pricing point of view there is a calculator you might want to consult as to what it would cost for the premium license to support embedding or if it's a sure there are pricing details on the edge of website lastly and a plug for my services I'm based in Melbourne but I do travel widely if you're ever looking for consulting or training services with power bi I offer not just power bi but sequel server bi as well feel free to reach out to me and I'd be happy to talk to you about how I could customize training for you and your company I think at this stage we can wrap up so I will finalize by saying look thank you very much for your time attendance an interest in this topic and I hope that you find it valuable thank you [Applause] [Music] [Music] you

Channel: SSW TV | Videos for developers, by developers

Views: 18,176

Keywords: ssw, software, industry, cutting edge, latest, developments, development, dev, design, functionality, microsoft, windows, computing, computer, advanced, expert, information, knowledge, dotnet, .net, angular, peter myers, powerbi, power bi

Id: D060qMhJ_mw

Length: 84min 14sec (5054 seconds)

Published: Fri Nov 17 2017