We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Power BI for the Developer: Integrate, Embed, Extend

00:00

Formal Metadata

Title
Power BI for the Developer: Integrate, Embed, Extend
Title of Series
Number of Parts
96
Author
License
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Identifiers
Publisher
Release Date
Language

Content Metadata

Subject Area
Genre
Abstract
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.
Expert systemIndependence (probability theory)SQL ServerComputing platformDatabasePower (physics)Software developerIntegral elementInclusion mapSoftware as a serviceReal numberFormal languageQuery languageCommitment schemeScale (map)DisintegrationProduct (business)Configuration spaceHybrid computerSystem programmingSupersonic speedContent (media)Visualization (computer graphics)CollaborationismSource codeSummierbarkeitMaxima and minimaGamma functionNatural numberElectronic meeting systemComputer fileFreewarePay televisionServer (computing)Information technology consultingPower (physics)Self-organizationPresentation of a groupMathematical analysisReal-time operating systemSearch engine (computing)Machine learningDirection (geometry)Data warehouseRight angleData storage deviceKey (cryptography)Differential (mechanical device)Axiom of choiceMultitier architectureContent (media)Web 2.0Integral elementExpert systemMessage passingEnterprise architectureSpacetimeGateway (telecommunications)Address spaceEmbedded systemWave packetVisualization (computer graphics)Web browserQuery languageRepresentational state transferDifferent (Kate Ryan album)Scheduling (computing)Basis <Mathematik>Focus (optics)ResultantElectronic mailing listProjective planeAnalytic setSingle-precision floating-point format2 (number)Service (economics)WaveAttribute grammarProduct (business)Configuration spaceElectric generatorDependent and independent variablesFundamental theorem of algebraTraffic reportingCartesian coordinate systemPerspective (visual)BuildingSoftwareVariety (linguistics)Structural loadOffice suiteDirectory serviceTerm (mathematics)Metropolitan area networkSequelDatabaseNatural numberPoint cloudPivot elementMultiplication signAsynchronous Transfer ModeUniformer RaumGoogolCASE <Informatik>Uniform resource locatorShape (magazine)File formatVirtual machineModal logicData modelHybrid computerLogicStreaming mediaHierarchyComputer fileSource codeSet (mathematics)Theory of relativityComputer programmingType theoryUMLComputer animation
DampingBoss CorporationLevel (video gaming)WebsiteEvent horizonNumberRight angleFerry CorstenArchaeological field surveyTouchscreenAnalytic setResultantElectronic mailing listField (computer science)Service (economics)SpacetimeGroup actionMessage passingAsynchronous Transfer ModePoint (geometry)Source codeTraffic reportingArmStreaming mediaObject (grammar)PlotterSet (mathematics)Wage labourRange (statistics)Latin squareDrop (liquid)Table (information)AliasingWeb 2.0Metric systemPersonal identification numberServer (computing)Visualization (computer graphics)Web pageoutputQR codeDemo (music)Bus (computing)Power (physics)PlastikkarteFundamental theorem of algebraComputer animation
GenderLocal GroupEvent horizonIdentifiabilityUniform resource locatorWeb pageStreaming mediaTouchscreenEvent horizonCodeTraffic reportingType theorySet (mathematics)DistanceComputer fontAnalytic setReal-time operating systemDecimalArchaeological field surveyFrequencyQuicksortFunction (mathematics)ResultantGroup actionTesselationPoint (geometry)Metric system2 (number)Price indexVisualization (computer graphics)Asynchronous Transfer ModeNumberLevel (video gaming)Personal identification numberAverageElectronic mailing listTrailReflection (mathematics)ArmGraphics tabletDependent and independent variablesStatisticsString (computer science)Collatz conjectureMultiplication signMoment (mathematics)Service (economics)Video gameSource codeComputer animation
Software developerPower (physics)DisintegrationService (economics)Client (computing)Table (information)Object modelHierarchyIntegral elementSet (mathematics)Power (physics)Representational state transferCollaborationismReal-time operating systemOrder (biology)Web browserGroup actionService (economics)Table (information)Query languageObject modelVisualization (computer graphics)ResultantAnalytic setSpacetimeSelf-organizationStreaming mediaLevel (video gaming)Computer programmingCartesian coordinate systemProcedural programmingComputer animation
Power (physics)Software developerActive DirectoryClient (computing)Identity managementToken ringEmailAuthorizationObject (grammar)Uniform resource locatorElectronic mailing listTable (information)Group actionOperations researchContent (media)Data typeMaxima and minimaLimit (category theory)Image registrationMobile appDataflowSingle-precision floating-point formatImplementationGroup actionRow (database)Electronic mailing listTerm (mathematics)Client (computing)Electronic program guideAuthenticationWeb 2.0Service (economics)Maxima and minimaPower (physics)Limit (category theory)Bit rateUniform resource locator2 (number)Cartesian coordinate systemReal-time operating systemOrder (biology)NumberWeb applicationSeries (mathematics)Web serviceRepresentational state transferData typeTable (information)Einbettung <Mathematik>Traffic reportingPoint (geometry)MereologySystem callTesselationCASE <Informatik>Token ringMultiplication signDependent and independent variablesLevel (video gaming)Set (mathematics)ArmOperator (mathematics)Volume (thermodynamics)Uniqueness quantificationIdentifiabilityNumbering schemeSpacetimePerspective (visual)Context awarenessError messageComputer animation
Structural loadMultiplication signArchaeological field surveyAuthenticationSet (mathematics)Physical systemType theoryTable (information)Revision controlSpacetimeTraffic reportingLevel (video gaming)Figurate numberDataflowOpen setData structureNetwork topologyComputer configurationCartesian coordinate systemRow (database)View (database)Error messagePower (physics)Drill commandsConfiguration spaceElectronic program guideEmailComputer animation
Directed setPower (physics)Streaming mediaDatabaseDisintegrationService (economics)Data warehouseConnectivity (graph theory)Scale (map)Event horizonTimestampService (economics)BuildingFocus (optics)Presentation of a groupShared memoryArchaeological field surveyEnterprise architecturePower (physics)Open setNumberWeb pageData warehouseReal-time operating systemMachine learningDatabaseMeasurementSequelComputer animation
Power (physics)Streaming mediaScale (map)Event horizonService (economics)DisintegrationTimestampBackupSoftware developerDatabaseSQL ServerScalable Coherent InterfaceSource codeThermal expansionService (economics)Dependent and independent variablesArchaeological field surveyEvent horizonInternet der DingeCASE <Informatik>State of matterFunctional (mathematics)AuthenticationDimensional analysisOrder (biology)Volume (thermodynamics)Table (information)Parameter (computer programming)Vector potentialStructural loadRange (statistics)Mathematical analysisFactory (trading post)Traffic reportingVisualization (computer graphics)Decision theoryPoint cloudCuboidEndliche ModelltheorieKey (cryptography)Projective planeEmbedded systemMultiplication signoutputNumberLimit (category theory)WindowQuery languageLogicProcess (computing)NavigationLocal ringGroup actionLevel (video gaming)Single-precision floating-point formatBus (computing)HierarchyFrequencyResultantServer (computing)Direction (geometry)Source codeFunction (mathematics)Cartesian coordinate systemReal-time operating systemFreeware2 (number)DatabasePower (physics)Selectivity (electronic)CodeText editorSpecial functionsIntegral elementData warehouseRelational databaseLink (knot theory)Data analysisRight angleGoodness of fitPower setRaw image formatBuildingComputer iconAnalytic setStreaming mediaMeasurementTerm (mathematics)Boss CorporationMoment (mathematics)MultiplicationTheory of relativityProgram slicingSound effectCellular automatonOpen setSequelField (computer science)Statement (computer science)Digital electronicsSet (mathematics)Type theoryTesselationBitSpacetimeComputer animation
Power (physics)Field (computer science)Interface (computing)Software frameworkSoftware developerSource codeVisual systemCodeType theoryVisualization (computer graphics)Software testingDisintegrationMobile appAttribute grammarInteractive televisionSoftware development kitConsistencyVisualization (computer graphics)ResultantSequelGroup actionQuicksortAttribute grammarCuboidWordMeasurementSpacetimeQuery languageCycle (graph theory)Key (cryptography)Open setArchaeological field surveyDatabaseSource codeGoodness of fitFrame problemTraffic reportingSingle-precision floating-point formatService (economics)Lipschitz-StetigkeitWeb 2.0Machine visionPoint (geometry)Computer fileRepresentation (politics)Data warehouseIP addressWeb applicationRight angleUsabilityData managementDemo (music)Web browserAuthenticationCellular automatonCartesian coordinate systemString (computer science)Dependent and independent variablesInteractive televisionAddress spaceWeb pageEmailAsynchronous Transfer ModeComputer programmingContent (media)Self-organizationSign (mathematics)Point cloudCovering spaceSystem callData structurePower (physics)Office suiteAuthorizationMobile appRepresentational state transferDirection (geometry)Computer animation
Software development kitPower (physics)Attribute grammarCodeInteractive televisionConsistencyAuthenticationAuthorizationData modelFormal languageQuery languageNatural numberContent (media)CollaborationismElectronic meeting systemTraffic reportingInternetworkingCartesian coordinate systemTouch typingType theoryVisualization (computer graphics)InformationAsynchronous Transfer ModeDemo (music)Commitment schemeSpacetimeDifferent (Kate Ryan album)Level (video gaming)VolumenvisualisierungGroup actionMultiplication signCustomer relationship managementQuery languageConfiguration spaceMathematicsResultantNumberService (economics)Dynamical systemSource codeDirection (geometry)CASE <Informatik>Web applicationInteractive televisionBit rateArchaeological field surveyCloud computingSpherical capPower (physics)Point (geometry)Analytic setEndliche ModelltheorieCurvatureComputer fileAuthenticationAuthorizationMobile appFrame problemFile formatShape (magazine)String (computer science)Range (statistics)Filter <Stochastik>Replication (computing)Pay televisionPhysical lawUniform resource locatorReflection (mathematics)Point cloudReal-time operating systemData miningComputer animation
Content (media)Power (physics)CollaborationismFormal languageNatural numberQuery languageFreewarePay televisionMatrix (mathematics)Source codeChannel capacityLimit (category theory)Bit rateSoftware developerIntelService (economics)Integral elementEmbedded systemDisintegrationClient (computing)Content management systemCodeBlogWebsiteData storage deviceContent (media)Set (mathematics)Power (physics)Physical systemDifferent (Kate Ryan album)Natural numberRepresentational state transferService (economics)Matrix (mathematics)Bit rateSource codeResultantFrustrationMultitier architectureRow (database)Wave packetPulse (signal processing)Complete metric spaceVolume (thermodynamics)Ocean currentPoint (geometry)Mixed realitySelf-organizationLevel (video gaming)Integral elementCartesian coordinate systemBlogDependent and independent variablesPay televisionFreewareAreaVisualization (computer graphics)Streaming mediaLogic gateAnalytic setMereologyTraffic reportingElectronic mailing listLogicNatural languageBuildingComputer animation
Power (physics)Integral elementNetwork topologySign (mathematics)Address spaceEmailTraffic reportingInformation securityLevel (video gaming)CurvatureSet (mathematics)Row (database)Power (physics)Interactive televisionElectronic mailing listBlogPublic domainRepresentational state transferService (economics)Term (mathematics)VotingLimit (category theory)Visualization (computer graphics)Projective planeEmailAddress spaceWeb 2.0Video game consoleLink (knot theory)Bit rateCodeAdventure gameVideoconferencingExecution unitOnline helpPoint (geometry)Identity managementShared memoryFreewareBuildingOptical disc driveSound effectMoving averageRight angleSelf-organizationMultiplication signComa BerenicesImage registrationComputer animation
Transcript: English(auto-generated)
This afternoon's 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 for 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 there 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 the 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% 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 refreshes 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 will 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 and 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 U.S. 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 streaming analytics is going to query it live and push it to the dashboard. All right, 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, a 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 related to all of the cities and that drop-down list will 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 roles. In fact, let me just push them to the side.
There is a, excuse me, there is a URL that 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. Can I have the map of the world somewhere?
Ah, 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, take some action. And that's essentially what dashboards are designed to express. Let me clean this up a little by editing the tile. Like 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 traveled 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 have so far been submitted. Let me then put this into a full screen mode. It's not gonna 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 gonna 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. Making 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? So 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 people at that senior age would make the effort to come here to Oslo. My home city of Melbourne, one, so I know that would be me.
And from the people coming from Oslo, how come we've just got one? 20 people are local, thank you. 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, 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.
All right, 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, 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 it's allowed to view certain assets, whether it's 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 datasets already exist, and datasets are described in terms of a unique identifier, which is a GUID, and by friendly name. You can then create a dataset that would require a post, an adjacent document describing the dataset in terms of the collection of tables
and in terms of the collection of columns for each table, and the data types are reasonably straightforward. You can also enumerate tables contained within a dataset. 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 dataset, 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 wanna publish to or it's a group that you're a member. So you can list the groups and then from a group you can call the list datasets. List dashboards and tiles have been added more recently.
These are in preview. So this supports another embedding scenario and 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.
Let's take a quick look, listing all datasets. 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 the response coming back as a JSON document looking like this.
There you see each dataset 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 dataset 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 dataset. Some restrictions. The maximum rows per single push will be 10,000. But that just means you're gonna 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 five 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 201st record, 200,000th and 1st, 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 five. 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 has been designed to show you how the API calls work
and while you ordinarily wouldn't expose these types of options, they would be in a config file, that registered app, it's 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 will 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 success of authentication, it's gonna 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 and 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 HDInsight Spark and then we see the Azure Machine Learning
and we also see the Azure HDInsight 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 and just out of interest, 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 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 wanna perform aggregation and summarization of values, they will need to be numeric. But 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 produce an 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 dataset name survey to a table name survey. All right, 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 dataset 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 and 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, calculate a column's 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 HDInsight Spark. So you could 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 gonna keep this so simple that all I want to analyze will be sales amount by time. So I'll find that there is an order date column somewhere here, maybe not.
Let me bring in dimre 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 in 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?
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. All right, 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 integrate them with Power BI. All right, 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. All right, 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? All right, by hovering over a fish he's gonna stop
and it's gonna 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 looked like with fish, or maybe not.
Okay, I think we know what that'll 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 published
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. All right, 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 the 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 could connect to Azure SQL Database, Azure SQL Data Warehouse and also Azure HDInsight 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. All right, so what it'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 will 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 providing the structures were 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 DirectQuery 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 DirectQuery 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. You'll use your existing authentication and 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 workspaces. 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 1,000 renders per month will be free,
and for every 1,000 beyond that, it will be $2.50 US. All right, and then you might ask, well, what is a render? And that's a great question. If I've got a report with a filter that allows me to modify a year, and let's just 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 caged this, in which case you're not paying for something
that has been previously caged 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, 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 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? All right, 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?
All right, 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. Oh, by the way, you could import data,
but if you publish that to the workspace, it's static. But it's not beyond the 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. All right, 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 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, enrich 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. All right, 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 eight 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.
All right, 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, developer 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 sources 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.
People are asking about, I need PowerPoint to have live interactive Power BI embedded into it. And for anyone that could be familiar with Power View, 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. 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 of 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, is 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 use 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 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. All right, so there are a couple of restrictions. Others that try to sign up without success find that it's because the organization of lockdown self-registration. All right, 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. All right, we have four minutes left.
Are there any questions that I could answer at this stage? You just have one. What is your question?
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? 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. All right, 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. All right, 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.