Common Data Service and Microsoft Power BI: Introducing data of any shape and size - BRK2197

First of all thank, you for taking the time to come during. The lunch hour, my. Name is Mahesh, Pretoria and I. Work with miguel lopez in our team and i'm. Here. To talk to you today about, how. Do you integrate data into, the common data service and. In. Particular, that there, are three things we will cover the first of which is. Really. Overview, of common dated service in the common data model and. What. Does that mean and i think i was. Handing out some of the CDM mini. Posters, to all of you who, came in early and, will. Talk a little bit about that and i, think after, that we'll talk about the. Data integration for admins out demo i'll talk about the data integrator and the. Admin, scenarios, and demo some of that, and. Then after that Miguel's. Gonna sort. Of showcase, a bunch of demos across. The board for business users using power query so, that's sort of the structure of the. Presentation. And if. You have any questions happy to take them at the end and as. I mentioned earlier our entire. Team is in the expo hall the booth, whenever. We're not speaking so, basically, I intend to hang out there and the team in will be hanging out there so feel free to come by even. If you miss anything later. On and combine, chat with us as, well. So. Yeah and I think that's, the overview, I mean is this what you're expecting approximately. Yeah. Cool good. It's always good to be in line with the expectations on that, so. Well let's talk a little bit about. Where. We fit into the bigger picture the. First of which is data. Integration, is, really a set of technologies, that, enable. You, to get data from multiple and, disparate, data sources in. A. Common form. Using. Combinator model, into. C.d.s, for apps and that's, one of the first things we'll talk about and. Into. A TLS, we do the day Lake and. And. The reason to put things in the data Lake is to be able to do all, kinds of interesting sort. Of AI and, learning. And ml scenarios, and, Richmond of the data right. There where the data is and do in Lake transforms, because you don't want to move large, amounts of data out of the lake and, you. Can then pass on that intelligence into, things like power bi through. Which your report and other mechanisms as well and, we, directly through our connector, ecosystem, today, provide. Data, connectivity. Into. Power bi so. Today, we have over two, hundred and two, hundred plus, by. Under the air a suspect will have about three hundred connectors. Across the board if you add them all up in in, the ecosystem, that span. Everything. From like. Every major data source that's available out there and. That. Connector, model is also extensible. So. That if you don't find a specific connector to plug into the ecosystem to, get, the data you, can. Actually write a connector and a relatively, straightforward and easy manner. So. That you can actually go ahead and sort of integrate your data into, the ecosystem, in. A, pretty easy way so. Why. Do you get data into the ecosystem the reason, you get data into the ecosystem is to actually make, it useful through, applications, at the end of the day and we. Envision two types of applications, there's, all the. Dynamics, set of applications, which. Is really about, how. Do you do both sort of sales and, ERP, and others and, there. May be custom, applications, built on top of CES and some, of you might have seen this in the keynote, the.

Dynamics Keynote where ryan demoed, power-ups building power-ups as part, of that he said oh we get 280 sources and that's our connector ecosystem, so you, can build apps easily, through the data ecosystem. Or. Through, power bi and, power bi is our. Needle. In the hay still. In the haystack it's really about providing intelligence, over. That data and. How. Do you show more. And more so and I think we're doing tons of innovations, and. We're. Making investments, such that power. Bi can. Reason about data using. A. Mental integration and a bunch of the others as well and, I. Think, you'll see some elements of that in work so Miguel will start, on the left side and, the demos later I'll, do the admin scenarios, of the demos and then, they'll move to power bi later. On and the in. The presentation, so, this is the framing, of where data integration fits, into the overall picture, and what, we're going to talk about our. Elements, of each of these pieces and. Then. We're gonna go deeper. Into admin scenarios and then. Miguel, is going to demo some of the business. User scenarios, and then, they'll wrap up with, the set of pointers for each and every one of. The talks that we have as well. So. So. Most of you have seen the, open data initiative. As part of our keynote right. I mean I think part. Of the foundation, for that is something. We've been working on for a long time. Through. Last release it was part of the crm team where. The. Model. That we're developing is, really. Becoming the, lingua franca for business, applications. So, what CDM provides is really a. Set, of entities, that. Are business. Entities, that, allow you to actually have some, meaning. Or semantics, behind. A. Structured. Shape. And form to that data which, is also extensible, by the way you can take it and extend it if. You're so desired and then. So. That you, don't have to replicate these how many of you have islands, of data in your organization, all over which is pretty much the same but just slightly different, all over the place almost, every one of you have have. That scenario and the, problem, with that the problem with not having a common model is that you, cannot easily reason, about. Customers. Across different, silos, for example, is this, the same customer, that. Has visited my website and try. To look up something is this the same customer, that called my support, that. I'm actually also trying to sell to if they had a bad support experience and you're trying to sell to them you. Should know that a priori, I think that's those are the kinds of these, are just two. Examples there, are tons and tons of examples across, the board of. These types of scenarios. Second images it unifies. Data. And, I think I mean it's easy to say that people are like yeah that's. A data model but, it's really important, especially in, our age where, data is increasing, exponentially. And it. Is not about the lack of data it's about understanding. What. That data is and finding. Exactly, what you're looking for in a consistent way that. Is actually the difficult part once. You find it how. Do you know the shape structure. Meaning. The. Semantics, of that data and for. The things that you know about and how, do you reason about them in, a consistent, format and, that. I think is really a key problem that we have that we're helping solve and, by. Starting. This initiative across, the board I'll talk a little about that as well and, the. Third benefit, that you get as a result of having this common data model which is, underlies, this open data initiative is. Really. The ability for the ecosystem whether. It is application, ecosystem or, libraries, or others to write. Interesting. Functionality, so you can actually get. Machine. Learning library the. Reasons, about it. Knowing. The shape and format of the data and, knowing, the semantics, of the data so you can actually go ahead and buy these off-the-shelf, things go plug into to.

Make Things happen whether it's apps or. Libraries. Across, the. System will. Help enable that. So. I think these are really key, problems. And it's almost sort of understated. But, I think this is the reason why. We. Really have, actually. Meant, to go to the next slide we, really have Adobe, NS ap joining us on. In. The keynotes or in the open data initiative and, that. We, are working with them in a very direct way in fact our architect, is. Working with them almost on a daily basis, to. Help. Integrate both. In terms of like if, you look at sa PE on the sort of like the finance. And other elements, in there and then it'll be on the marketing side and, whole bunch of others that they are pretty strong at how, do you integrate, the, data models, when. It comes to Adobe XDM. And CDM and make that the open data initiative by the way. I. Forgot to mention in the earlier slide there's, a link at the bottom on the github site that, you can go to to go check out the, common data model so. If. They see the imposter, but I think it's a let, me see if I can just all tab to, that. So, this, is basically the github site that. You go to if you go to a kms, city, and poster it's got a full PDF of the poster that I handed out so. You can print it out yourself, and we had these small and big posters a thousand of them at the booth as well if you want to combine Chavez, in. Addition to that there's. Actually tooling. That. Helps, you view there's a tool. That, we will demo in our later talks not today in the CDM talk that, helps you view basically, the. Entire sort of schema, interesting. Way I think those are the those are the kinds of things that are available and, everything, is being done in an open Bay -, all. Right on sort of github so I think it's just something that you can take a look at participate, in and help. Enable that as well so, that's a, that's. My pitch on CDM and open data initiative and so. In summary what you really have as. A result of this effort is. For. Business applications, hopefully. One data model that you can use with. A unified. Both. Structure. Schema, and semantics. We. Don't envision that they'll cover every, scenario it's extensible, so there are cases where you will need to extend it without a doubt I mean they're not foolish. Enough to think that this, will solve world hunger but now the less it's a great, place to get started so we'd have to repeat, who.

A Customer, is and you don't have to repeat what the Sales Lead is soon. And repeat many of the things that already exists, in the industry as the norms to, get started with that so. That's the that's, the summary of our like. Overalls, or a portfolio and, here's. Sort of talks that we have across the, entire, sort of conference starting, with this talk and what, I've just done is the overview and I'll start going into a deep. Dive on each, of the other, areas like the admin area that I talked about so, in particular if you're interested in CDM I'm gonna pitch that go. To the session by Robert. And Tessa Roberts, architect for that Tessa. Is the PM and, she. Does a great job at with these talks so I strongly, recommend that and many other talks as well and, the other thing you'll notice is that Miguel's, everywhere, and all these talks and so. I think it's just it's, really a pleasure to work with Miguel to be able to do across-the-board just, about anything to be, able to go do that so he's doing seven talks. At this conference so you'll, see him being. Pretty tired. He. Prepares just in time and okay. So, uh with that let's. Talk a little bit about data, integration for, admins so. I think the first question you have is why get data into seed yes, so. How many of you have heard prospect. Akash scenario. And what that means, some. Of you okay, okay. So let me play this because I think it underlies sort, of what, we're trying to do so, I'm gonna play with you here. In. This. Video we will provide an overview of the Microsoft, Dynamics 365, prospect, 2 cache integration. Prospect. To catch integration, enables sellers to handle and monitor their sales processes, with the strength from Dynamics, 365, for sales while. All aspects, of fulfillment, and invoicing, take place using the rich functionality and, finance and operations with. The. Prospect 2 cache integration, you get the combined power from both systems. In. This. Video we, will first discuss the key components, in data integration. The. Data integration is enabled, with the Dynamics 365, data integration, feature which. Creates connections between the online versions of sales and finance and operations. The. Standard templates are developed, to support the prospect, to cash integration, it, is easy to set up the desired integration. You. Can't control how often you want the synchronization. To occur from, minutes to months. From. A data integration, you can always modify and extend the integration, project, to fit your needs by modifying, the existing mapping. From the templates. You. Can modify the existing field, mappings. You. Can add additional fields to the mapping and. You, can even add an additional task to synchronize, additional, entities. Next. We will give you an overview of the standard prospect to catch integration, between sales and finance and operations.

Synchronization. Between sales and finance and operations, is supported, with the standard, prospect, - cash templates. For. Account you, can create and maintain accounts, and sales and the, information, will be synchronized to finance and operations as customers, of type organization. For. Contact, you can create and maintain contacts. And sales once. Contexts are related to an account they, will be synchronized to finance, and operations as contacts, with a link to the customer. If. You sell to a contact, in sales it will be synchronized to finance and operations as a customer, of type person. For. Products, you can create and maintain released products in finance and operations and the, products will be synchronized to sales including a priceless item with the base sales price from finance and operations. For. Quote you can create quotes and sales and synchronize them to finance and operations once. Activated they will flow to finance, and operations for further processing. For. Sales order you can create sales orders and sales and synchronize them to finance and operations, once. Activated, they will flow to finance, and operations. Updates. On the existing, sales orders and all new sales orders from finance and operations will be synchronized back to sales which. Keeps the seller updated, about fulfillment, and order status. For. Invoice you, can invoice sales orders in finance and operations and the, invoice information will, be synchronized to sales which, allows the seller to view invoices, directly from sales. Thank. You for watching for. More information please search for prospect, to cast at this website. And. Then they live happily ever after. Okay. So. So. That's the train sort of like what it means or data integration problem the. Notion of a sales prospect. Hopefully. You get the cash after invoicing, and it's. The entire, sort of how do you make sure it's all about data at the end of the day not, about the application, the data generates, the application. I. Have. The song this. Is better, okay. Thank you I'll, turn it off just so there's not a, feedback. And. So. The point here is that all. The way from, like. An initial Sales Lead how do you flow the data is what we're going to show you and how do you synchronize that data using. The administrative, tools that we have in the first part of it so. I think there are two examples here on one name the customers but, these are very concrete examples, where they're using, both. Are then, bt65. Seee customer, engagement, also, known as CRM and. They're. Also using a d3 60 pop-ups, and, which. Is really also known as a X Dynamics. AX and they. Really want to make sure that data is sunk up in a way that. They can actually serve the customers and really make, sure that the end-to-end scenario, is taken care of and I, think there multiple different ways in the. Top case there are actually multiple entities, that get synchronized and, in. The bottom furniture, case they actually do, much more interesting, where the account is split across multiple different, entities in a X and so. Each of these scenarios there. It's. Doable, today how do you do data integration by. In. Each of these scenarios I think is doable so we'll show some elements, and examples of that, so. Now we talked about why would you want to get data into CVS you get data into CD s such. That your. As, a, whole your application, is multiple. Different applications, are executing, as if it's one this, is unified data and then. The kind of data that you can get in there are. Basically. The, finance and ops data into, CD SD 265, and, in. Addition to that we also have tons of sort of, sources. Through power query which. Miguel. Will demo quite a bit today and an. Example, of that by no means the only one is the Salesforce for example your, data doesn't necessarily, have to be part of dynamic. C-65 you still stated maybe in Salesforce. But, you're using our ax. For, actually, Finance knobs in which case how do you synchronize that data I'll show you some of the templates that we have that enable you to do that and. That's. Only one example they're, playing 20-plus other data sources that you can get data from as well and I. Think in. Addition to that if you are Salesforce. Customer and thinking of transitioning to CRM you can get the data as well through the same connector.

And Data. Integration experience, that we have on hey I want to transition to Microsoft. CRM and I want to start with this data to. Test, louder and try it out and you can give it and suck the data out and and go, enable it and help make that happen as well so I think these are scenarios that we enable on that. So. Ok now comes how do you get data into CVS, I'll show, you a few slides and do a quick demo and. Then I will hand off to Miguel as well so, the data integration platform, has, these concepts, called templates. Templates are about how do you map between, different entities and different fields and entities because, unfortunately. We're. Not yet in this world where. All data, is sort of unified, we ourselves are, working in Microsoft to unify that but we'll provide, you an experience, that's unified, so, that you don't have to worry about it and. So. We provide these templates, out-of-the-box and I'll show you the examples of those templates in addition, to that you create this connection set which are really a connection, information both on the source side the, environment, on the source side and, the, connection information on the destination side and then Marmon on the destination side you take all of that and you create a gate integration, project and then, you schedule it you execute it right then or you can schedule it as well and. That's really the summary of the entire sort of technology, that we have and. So. Typically, we see customers. Scheduling. These things to synchronize data in, their organization, and I think typically, you can schedule it whatever. Sort. Of frequency that you care about but Sabine rpm. That's responsible, for this domain and I'm doing the talking as behalf and. So. The templates, for examples look like this and I'll show you in the demo as well so basically the templates are relatively straightforward out-of-the-box. Templates but you can customize them you can do ad mapping, and say. Hey I want to go ahead and. This. Salesforce. Entity. And in this field in Salesforce, really should map to this. For, example there are, there. Are differences, in the semantics, of the data between how. Strong. A lead is in the sales data for, Salesforce, and what it means on the Microsoft, CRM side of it so you can map these entities and in. The way that you care about and use choose and. Then after that you can create your own template, and you can go ahead and publish it as well so pretty soon as of. End of October, we'll be releasing a, feature, that allows you to publish a Templar across, your organization right now we can take it and customize, it and use it yourself, as an admin but you can you, cannot really publish it in your org and will provide that ability as well and with. Power query integrated. Into the experience, what you get is really. A pretty. Strong ability, to transform, data while, you're doing that as well if you so choose for example if you wanted to create. Conditional, columns, as an example or if. You wanted to join the other data or if you want to bring in other data Park really provides some pretty strong functionality. To help enable you to do that and then. We. Talked about some examples of this and I think there are tons, of functionality, like the date function is a sort, of a great example of that in power query and you'll, see some demos of this the.

Filtering I think it's very powerful functionality, it's, a very excel like by the way power query that you see in this, tooling is the. Same power query engine, and. Infrastructure, even though this one is the online version of it that's in Excel, and down. The road it'll be a unified, sort of complete experience across all of these, so. The same power query online that you'll be using here is the same one if you go to the data tab and do, get data it is our tooling, that ships as part of excel as well. So I think it's the same functionality. On that so, let me do a quick demo. Thank. You. Okay. So what. You see here in admin. Dot parse comm is where you need to go as an, administrator, and what, you see here is a sort of data integration projects, that you have you, can always go ahead and create a new project if you so desire and. So. You don't, have to because I'm just gonna show you what it means to create a new project and then. In, and initially what you get asked, is actually I'll just say here. And. You. Get all kinds of templates that are, out of the box early start so, in this case so, this, is a template that goes from sales to finance an ops or. You might choose and say look my. Sales it is in Salesforce, what I really want to do is get that data into CD s by. The way CVS is a common, data's service, that, is so. How CES and CEM relate or CDM is the model, that defines. The common data service so cerium is just a moral part and Sirius. Is really an instance of that model, is how you should think about it and so. You. Can get get data from any of these I'll just go ahead and sort of select the first one and then you can go ahead and create next I'm gonna get out of this as well and then you select a connection set and then, you just create the project so for the purposes of this I'll just go ahead and cancel, out of here and then, show you the recent project that I've created and then. In some of these, you. Can go ahead and sort of run. Them or you can go ahead and schedule them as well so let's. Take a look at s, of DC the Salesforce one and here. You're getting. Data from Salesforce into. Our CRM and. Maybe. You want to just test it out and and see how that works and this. Map. Function. Here is, the template so on the left side is the, Salesforce. Entity and, attributes. And on. The right side is, the destination field that it maps to so. You, start out we provide, you out of the box with a predefined, set of mappings that we believe makes sense for you but, you're completely able to edit it you say look facts in my system is completely different in my, schema. And I. Want to map it to something else or addresses, typically, economically, the case that people, have different sort of mappings. That they want to go in and pick, so, I think what, you can do is you can completely customize it, and then, you can even add conditional, columns and new columns associated, with that that you create, as part of that so.

In, The interest of time I'm actually gonna go ahead and sort of and and by the way you can also do power query as part of this which is what. This is bringing up is the ability. On the source data to. Use power query which is our creating. Tool here, what you see here, is an example of the source data and, then, you can actually go ahead and sort of. You. Can actually go ahead and sort of. Bring. Up even the advanced editor that we have which is the. Underneath, underneath power, query the powers all of this is what we call the M language, which, enables, you to write very interesting, expressions, to go map that you don't have to do any of this you, can use the GUI itself, to go do. Whatever the work that you need to but. If you're so interested. You can go ahead and sort of extend. This because Parc really supports, about 300 transforms, some, of which are supported. In the UX and some, of which are actually, available, only through code as of today so. I think what you can do is you, can customize it to, whatever form I'm gonna cancel it I'm not gonna customize anything and I'm. Gonna go ahead and and and. And. Run. It actually you can see the execution history, here as well it, ran it and then you can say I want to go ahead and rerun this, and then it will submit it and. It's. Basically, it's. Submitted and it's gonna start running and will. Transfer over the data from, Salesforce into, CRM, so. While I'm at it let's take some examples of, things that are error, conditions, and how we handled them I. Think either, one I'll pick this one for now and then, see if I can drill. Into the. Execution history, and then, say, why. Is this error I think there's a project validation, step in that which is really there's some error, in the project and then, you can double click on it and say ok. So. You can actually get, some all. The way to the error. Reports, to go fix things if you need it - and while scheduling, it you can even email. Yourself. If there are any errors on a regular, basis as well so. I think that's. The, summary of the data integration, admin. Functionality, and I think just basically, go ahead and sort of. Run. With each. Of these and I think it's easiest to go ahead and play with that and then, you can either run the project, which will only do the deltas, or the changes, if you ran it already before for example I just around the as of DC - CRM, or you, can rerun, it with all the data, by. Default if. You ran it before there's. Change tracking turned on on our CRM, and the X systems. To only look at the deltas and the changes, and it will only worry. About the changes or you can say look I want to rerun the whole thing again you can do that or you can go ahead and schedule it and on, and on and on so, it's a pretty powerful functionality, you. Can go ahead and schedule there and say said notify me if there any errors or warnings and, on and on so, I recommend that you play with this it's pretty pretty. Interesting and to. Get started all you need to do is go ahead and create a project, and in creating a project you just need to set up some connections, and, here. I've set ups from Salesforce. Connections and also. Then I'm is 365 connections. From. Both. The source to, the destination and, then, I use that in the context, of connection. Set which is a provided the source. Connection information plus, the environment. I'll actually I can neatly show you each, of these and then, these, are my connection, sets that are available and, then you can use that from there you, can also right-click on the dashboard here and you can see the details of how things ran and, go. Ahead and edit and and. Then, for example you can see the records here and then say give, me what, happened with this, this. Past weeks set. Of runs and it. Will say here are the things that ran at these times, and and, if there any errors will show you and then you can actually go ahead and sort of click, and preview that as well. So. That's the summary of it I've went through it in a pretty whirlwind, get some order to make sure I told. Miguel about a half an hour and I. Wanted to make sure that we are if. You can go back to five we're. Going through that but I think it's it's easiest, if you just set it up and play with it and integrate, data from different sources and one. Of the things haven't shown you is how do you integrate anything. Beyond ax, or CRM or Salesforce, but, you can actually use any any of our connectors and power query to. Go get other data as well to, integrate as part of that and to morph in slice and dice and you'll, see some demos of that as well, so. The that's the summary on the admin side and I think on the power user side you'll see.

Basically. On the business user side you'll see demos. From Miguel or, the, rest of it and I'll come back up at the end and answer. Any questions thank you so much so. Actually, before going into the next section for business users you guys have any questions well this is top of mind so we can. So. It's so, it's all sort of the one of the infrastructure, that you have in the cloud that's. Running it right I think the the. Versions that are supported, our CRM. 89 on cerimon line 89 and then. Ax, cloud so whatever you provisioned, is really, what it's about. Yes. Yes. Our templates. Actually. Provide, the ability you, can, actually let. Me go back and show you. Soon. The connection suits the. Project, and what, you can do here, is in. This. Ad task item, you, can actually. You. Can select disparities. That. You intern you can actually tear it apart and and, you, can go ahead and store it in multiple different places as well on the destination side and so. All of that is provided, and I think, power. Carry also helps, you with that as well so. If you need to slice, and dice data it's all doable then. You can save that template, once and say. This is my template and. You. Can even publish that starting, November, into. Your organization, and say hey this is our template for our organization, so, that, it's. Easier to share that as well. On. The back. So. The there's, caveat, or admin. Integration. Scenarios, it, is really the online, CRM, online that's. Supported, but, through, the connector, ecosystem, through, the gateway you can get it online, for. The other data. If. That makes sense or no, give. Me a scenario. So. The short answer is no. Yes. I'm sorry but it's, mostly the online build if, it's a the, ERP system no the. Template, would not allow. Compromised. Mappings, but if you go use the power, query online capabilities, particularly. With ERP you could consume data from the Oh data endpoint, shape it the way you want and do that through the on-prem gateway that's correct. There's. Not of a box templates because those are heavily customized, it's not like the dynamics. Online versions so. You have to do work in defining your template using, power query through the data connector with, the Gateway turned on to be to do that it's doable and it's, actually just a little bit more work not not as much but yes.

But. Not out of the box this, is one of the things we're thinking through in. Helping improve the experience for the admins as a whole. The. Short, answer is today. There's, not an easy way to share these templates, but, we're doing the release at the end of October, where. This is exactly one of the key features, that we're working on so, starting in November you'll see that. And. There are other technologies, we're working on as well that. Will help improve the experience but I'm not at liberty yet to talk about those because I want to make sure that we deal with them first and then I'll talk about them in the team. Yes. Sir in the back. Yes. Yeah. You can set up for, each project it is, a given environment so, you can set up multiple different, projects and say that's, what a connection said is connections, that is connection information plus the environment, that you want to sync up and you can say you can say project. 1 Project 2 project 3 project 4 and you can set up set those up and schedule them as, well. It's. Provided. As. Part of that. Any. Other questions. Yes. You. So, so. Is there I mean there's nothing in particular that we do other than what the default security is I mean it's really under, the. Subtle sort of credentials, that you provide it's I mean it's an azure so it's I mean, it's about as secure as it gets but, is there something in particular you're, thinking, of or. Yes. Sure. So. I think I mean the two parts or does the data movement and the access sort of like that you give to your users and what, I've shown you is the admin scenario where they have the full access and they can move the data and integrate the data and then, your access control is completely up to you by and to go. Ahead and sort of give access to whatever the information that they need on the, destination side for example you move from Salesforce, you'd. Want to think a little bit about some of the user permissions as well as to what who what, access you give to whom and go ahead and sort of design that upfront. And. I'm happy to chat with you more if you have more details on this offline, miguel's. Always got cool demos see the demo guard so a lot, of side for a little bit good. All right thanks my hash so we'll focus, for the for, the next about, 38, 37, minutes on the in, there integration capabilities, for business users and if. You remember that diagram, that we, showed at the beginning we're going to talk about one. Of the purple boxes on top of a common data service for apps and then we're also going to show you on, the power bi side how you leverage, this. Data and create data flows to do analytics. With. That said just a little recap and a bunch of things that Mahesh already mentioned, there integration capabilities, that we provide built into the, common data service for apps are based on power query online it's, a much, newer version, of power query that would we've, had in desktop for the last few years in, Excel and power bi how, many of you are regularly used power query in desktop today either power bi desktop or Excel through the data tab okay, quite a few folks so.

For Those of you you'll feel pretty much at home with these, nice new capabilities. Because right. Now it's kind of a subset, and we're growing towards full parity, with power, query in desktop over the next hopefully, half year to a year there's, a lot of features, we've been adding to power query in desktop so it's a big mountain to climb but we'll half way through at least, we. Provide connectivity, to about, 20 the resources right now which. I have the. Next slide and. In terms of transforms, Mahesh mentioned, the underlying engine the mashup engine is the same across their, sterben service so everything, that you like copy-paste, a query or copy/paste a transform, it will just run fine there, will be some things that you do not have yet the user experience on top a good, example of that would be you something like column. From examples, in desktop we don't have a column from examples, experience on the web but we're working on it what. Else do we do in terms of building capabilities. For data integration as. The question that came up early about getting to on-premises, data we actually do that through the on-premises, data gateway this, is the same gateway that you use for power bi or, power ups or flow or logic ups or. Assure analysis, services, you can also use, it in this context, and remember here we are bound to. Your common data series for Apps account so it's if, you're already using power, ups and you've configured some connections, you could also leverage those same connections, here, the, last couple points, here we, talked a lot about CDM, and the open data initiative, through. The power query online experience, I'm going to also show you how you can map from. A data source schema to, an actual, definition, of an entity in the common data model so, you can stick. To that out of a box predefined, schema that we, provide so, that then you can leverage additional capabilities. On top just, because you're, going to a well-known schema for us so we can give you richer. Capabilities, the. Last one here the last point is about scheduled refresh this is something that we just added a couple weeks ago so I'm going to show you what the experience looks like as well, with. All that said we talked, about that so let's go and do a bunch of demos. Just. Before we get into the demos one apology. That I'm going to be using Chrome, the. Reason I'm going to show you chrome, is because if, you use edge it's. Actually so nice that when you open a new tab it gives you a bunch of news including your. Favorite team's results, and actually my favorite soccer team Atletico, Madrid is pain right now so.

I Don't want to know the score I'm recording, it I'll. Watch it tonight that's, the only reason why I'm using something. Other than it's. With. That said let's. Let's get going so bear with me as we use Chrome that so. I'm looking right now the power apps for all the power ups maker portal how many of you already use, power. Apps, okay. You're familiar with this portal so you probably seen the data group, in here, and, if you open that you see a few things you, see all, of your existing entities, already in the entities list or. A spinner for a long time and then you see the entities so. Here's all the entities that you have that, we've predefined, some of them are also custom, so you can see by type we have custom. And standard, entities so there's a few pre-loaded, custom ones that I've created up front you, can actually go to the data integration tab and here, is where you'll come to actually create some, of the data integration more complex projects you could also do from, the entities tab you could also get straight into get data either, by, doing a one-time upload, of an excel file through get data from excel or if, you do get data I will actually by default create a data integration project, behind the scenes but you could come to the integration tab to see the full list which, as you can see I have really meaningful, names of projects, so. I can find my way back but. Let's go create a new one so, it's a new data integration project, and we're going to get you into the power query online experience. From here so for those of you familiar this. Is the equivalent, to the get data screen in power bi desktop we, have about 20-25 sources, right now compared, to the 85, plus we have in power bi desktop as. I said we're working our way to parity, so we'll, get there the. First time I'm going to show you is to just very simple I'm going to connect. To an excel file that's, in onedrive I'm going to show you what the file looks like and we're. Going to reshape these data so we have some data in here with, you know the first role Seattle. Portland Vancouver looks. Like you. Know on bridges or cities that we have some, data for then. We have bikes, accessories. Miscellany. So these are categories. And then we have a bunch of years and a bunch of numbers in the intersection, so. We. As humans because we're so smart, we know that for. For, example cell. For. Cell b3, that. 23,000. And 432. Number. We, understand, that this means probably, means sales. For, bikes category. In, C are all in 2002. Right, so. That's very intuitive but this is not in many ways in a machine readable format, like. You know like you would put in a database for example so. We would use we are gonna use power query to actually clean this data and then properly load, this into the common data service and. How. Many of you have actually tried loading, of a file. Sitting in onedrive into power, query either power, bi or the stub and how many of you have actually struggled, to do it okay. Yeah so if you try to do it and you come to the onedrive UI it's. Really nice you can right-click and say copy link if, you do that and use that link it's not going to work you. Could also do open, in Excel online so. If you come here you, have this other link if you copy that it's, not going to work either so. The. Easiest way to actually get to the file is you, open it in Excel desktop. And. Then you go to, the file backstage, and. You click here and then you do copy path to clipboard. Once. You do that and. You, come back and. You select Excel and you paste that if. I click Next it's not going to work either. Because. You, need to remove the last parameter here, this web equals one with a question mark thing, now. It should work and. We do credentials, I'm already signed in with my organizational. Account my ad account when, I click next it should work hopefully, unless, the my. Friend Murphy is coming to my session instead of watching the atletico madrid game but it did work so he's watching a game probably just.

So You know this is the same thing you would do in either Excel or power bi and we're working on fixes, for this so we're working on an experience to browse drag, from within the power query experience, browse for onedrive, contents, so you can actually get, the actual file without. Having to copy/paste, stuff from here and there but once, we've made our way here this. Is very, consistent. Experience, with a1 in desktop this is equivalent to the Navigator you are in power query in the desktop you, can just find the interesting. Elements in this case with this one sheet. One and you can preview it you can select it. And then, you can click Next and. Now we're going to go into the Edit queries or the query editor UI so here's where we're going to do all of our data preparation steps, all of our data, transformations. The, first thing we need to do here is to actually turn columns. Into rows or rows into columns so for that you can use transpose. It's. Going to kind of flip that by 90 degrees now. It's starting to look better but we still have you know a first row here is the these, numbers so it's actually the years so, what we need to do is use that first row as the column headers. Now. We are starting to look better we. Can clean up these. Column. Names so, this is category. Great. So, now we have these two columns. Here with Syrian category, and the, rest of columns we have one per year with the actual values and of course we would like that next, year when we refresh, this, data integration project, and a new row or some new data has been added to that Excel online spreadsheet with data, for 2018, we, would like that data to come in as well right so to do that we. Need to use empty. Words. Which, is under transform column MP bought the other columns it's, basically saying all columns, except those first two and pivot, them turn them into rows and, it's gonna give us this format, so we have attribute, and value which is basically attribute. Is the Year column. And then value is the total sales on that year, so. Next next year when we refresh this file we're going to see a new row that will say C are all by X 2018, and some number hopefully a really huge large number because we'll doing well in our business but, that's how you doing people so it's dari see it's. Actually go rename this so this is actually, our role. Sales, by year, and. From. Here we can actually also adjust some of column, types so let's say this is a text column. So. With City. Year. Should actually be, a whole. Number and. Total. Sales it's a. Currency. Great. So once we've done all of that if you're familiar with power query you know all of these but I'm just going to call out a few things you. Have apply the steps here on the side if you paid attention to that you notice that we were having one, step at a time you could, actually go back and almost like replay a movie, here so you go back to a step you see what the preview, the output the results, look like at that point you.

Could Even come back and make changes to this either the. Formula, bar or in some cases you get this icon to change some, of the things we can figure out the way, you. Could also access as my point about the, advanced editor. It'll. Give you access to the underlying M code, so M naught. This Spinney but the actual text that will come up on screen there we go so this is M code, so M is the underline query. Language, behind power query and it allows you to query data, uniforming. Uniformly, across all of these data sources so, it doesn't really matter if you're connecting, to an excel file like we did or a sicko server database or, an S AP system or dynamics. M, and the connectors, that we build on top of M will take care of actually, pushing. Down as many operations, as possible, like depending on the, backend capabilities. If you do a group by against sequel so generally the group I statement. If you do a filter against, and. All data endpoints that will do a no data filter, close. But. The experience right from, the provide, is consistent, across all of these sources and you can even very easily combine, data across all of them using, things like merge. Queries which is a join in database terms up, n which is like a Union so gross. We're. Doing actually a deep dive session, into power query on. Thursday. Morning. One. Of the sessions in our, we'll. Go back to that later if. You want to learn more about power query ok. We can go hit the next, but actually, before doing that the search go get some more data I'm. Going to do more PQ magic so. Ram. Prasad, show. You a sure sequel, database and in. Here you can actually just, go in and provide the server name so that's. Gonna be something. Like. These. The. Moment I did that did you notice that it popped in my username and the password this, is because it's already been storing, my profile but otherwise I could configure it here so I could provide basic. Username, password credentials, if this was an on-prem source I could select one of my gateways here and my. Gateways from power bi and power ups are automatically, showing up here by default I'll leave it as none because it's a cloud-based database. It's a natural sequel database. The. Moment I hit Dax we're going to give you a very similar experience to the one you saw with the excel file except. That here we're actually going to show you databases. Within this server I have to and then, if I expand the database I'm actually going to see also the tables, so, I can click say, also TDOT customer, and. I can also pick the order. Headers. Let's. Pick those two and we'll, get the previews for them and we can just select more than one so let's move on and. Then we land back in the query editor so now I have three queries here, and, let's go and do some transforms, so, we are in the cells, or their header. Table so here we're, gonna subset the number of columns so what I'm after is just showing the, customer, table with the top 10 customers, the most pain the top 10 most pain customers, and for. That we start with the source or the one I'm gonna subset, these choose columns, and. I'm gonna pick just a couple of them so I'm gonna pick the. Customer, ID and I'm. Also gonna pick something that's called, total, do. So, those two. And. It looks something like this and now that I just have a customer, ID which I'm going to enjoin with the other table I can do a group by operation, so I can right-click on customer ID and say, I'm going to do row.

Sales Oops. Sorry, I missed type that you can edit so I demoed edit as well you, can pick a different operation, like a sum and four, column in this case oh I wonder sum of total do you and group by the customer, ready, once. I do that it's going to show me the aggregated numbers or one row per customer now. Let's go back to the say, also T dot customer, table here. We also have a bunch of columns that I don't need so let's start by for. Example picking, these. Five columns with title, first name middle name last name and suffix let's. Just combine them into one single column, that's a full name for this customer, so, it's going to merge columns, we, want to space the limited and then we'd say this column is going to be a full name. And is. There is it to just combine five columns into one, now. Let's go and pick only a subset of these columns so we'll pick the customer, ID the full name the company name and then the email address. Remove. The others and. Now let's go and do join with, the other table, with the order headers or the total by customers, so we're going to use merge, queries and the. Experience is very, simple, actually you just select the, right table, so we're already merging, with customer, table we'll pick in sales order header and which, columns, to use to, compare, you. Could also pick different types of joints or left order so all the rows from the first table and the matching ones from the right and all the combinations, for, merchants there's a really. Nice picture with, multiple, band diagrams, that explain all of the joint types but. We'll stick to the left outer join, for this demo so, once we do that we get this new column basically. Will give us all of our matching rows from the right table sales order header that, much, that, specific, customer ID here and, we can go up on the. Results here so if we look at this, menu is going to give us the two columns from the other table that we, got matches for in, this case we're just going to bring total, sales we. Don't need column. Name perfect we'll just do total sales and this is gonna. Propagate. Or lift that, total. Sales number of, total sales column to be a top-level column we, got a bunch of notes because we have, all of these customers, none of the. Purchases. That's. Why we don't have we. Have some with no we. Can sort, descending. But. Once we do that we're gonna see the top ones so now we have you know 19. Million is like the customer, this mystery, hey minister guy with line, purchasing, bicycles, so now let's go and with use rows. We could do keep, top rows we're. Gonna say keep the top 10. And. There we go so that's our top 10 most, paying customers, so let's actually go rename this table. Okay. So we've combined, the results in this one we don't need to load. This one right now anymore so we're going to disable load for that where, it was an intermediate query and, then we have total sales on the top ten let's, actually go and load those two into the common, data model so, into the common data service. When. I click Next we go into the next screen this is actually going to give us the mapping UI, and. This is where you say for, each of our queries that we generated how, you want to load them into the common data service you have three options not.

Loading Them which, is what we're going to do for the last one so all order. Headers is, do not load the. Other two you could say either load to an existing, entity and these are from the list of entities that I showed you at the very beginning in the power-ups Paulo combination. Of custom entities that have, already been defined and also, their standard, or CDM based entities. We. Could say load to neo so just to show you these, are all of the predefined. Entities including city entities and. I'm going to show you later on I. Won't do. That now but, you could do lot of new entity, if you want to create something fully customized, so. This is the total sales so we, could give it a description, so, some, description. When. You create a new entity in, the company to. Provide what, he, field is which in this case I don't. I. Guess, say, here and. For primary name I don't have a good one either so let's actually switch to something more interesting like this one here. We can do also load two new entity in we say we love. Because. They pay us a lot of money and, then, we can pick the key file in this case I do have a customer, ID and for, the primary name field I'm going to pick the company name, and. Once we do that we're all set in terms of defining the mappings we, can go click Next and that's, going to ask us about the reefer setting so this is the screen that I mentioned we just started like a week or two ago and, you. Have two options refresh, manually so basically do not define, the schedule refresh I mean it would mean that you could always go back to that list of data integration projects, and manually say we run or refresh this project, or, you could say refresh automatically, so you do that you, could say refresh maybe, we say refresh every, one. They. Starting. Out today at 5:30, UTC. And. Then you could create the project so creating a project will actually, run the project one time one. Initial time to define those in this case those two new custom entities and it will populate the results, into the common data service and from there on it will just continue running on this schedule. Okay. So with that. We'll. Start blogging but this actually switch gears to a, second, demo and, to. Do that now we are going to switch. Gears to power. Bi data flow so let me actually show you this. Diagram again as a refresher, so, what we've shown you so far in this session are the, blue bar the purple, boxes so we've shown you how, to get data into, CDs for apps across first party dynamics, 365, apps using data. Integration, for admins and we've also show you from the power apps maker portal, how, you can get data from other random sources like an excel file or a sequel Azure or ash or sequel database and many other sources now, I'm going to switch gears to the yellow boxes, and I'm going to show you how. You can start leveraging this. Data from power bi. So. For that I'm gonna switch to a, different tab. Here, so, I'm sitting in power bi and I, mean, this is very a special tenant, that already has enabled, the, data flows preview, data, flows power bi data flows how many of you have heard about it, okay.

Three Of you are bi data flows allows you to define. ETL. Jobs to actually load. Data and break from power bi into. Azure data like storage. Legends. You to be more precise. It's. Currently in private preview we'll, have a couple other sessions, later this week about this capability. And it will go into public preview hopefully. Sometime in October or still exact, date TBD, but it's, coming soon let's. Actually go and start. Defining data flow so you can come to you, probably familiar with this view if you use power bi today where you have for, a given workspace, you have a list dashboards, and reports, and data sets we're, adding a new tab for data flows and, you. Can go to the create menu and those be a new option for creating a data flow and. From here you can start adding new entities and, when, you do that, surprisingly. You're gonna get the same power, query online get data experience, it's very consistent, with, the experience that I just showed you from. The common data service perhaps data integration point of view except. We're adding a new, option. Here. Which will soon also come to. The dairy integration experience that I show you for common data service which, is a Salesforce template, this, contains, the connections. Through power query to our Salesforce, and. To actually start pulling a bunch of entities I think we do seven or eight and it is we're going to see them in a little bit and it also contains a bunch of data preparation steps, and mappings, so these are actually mapping Salesforce. Entities into, CDM. Schema, the. First thing I need to do is sign in with my source, account. Dams. Ok, so now I'm signed in if. We click next. We're. Gonna go straight into, the query, editor view where you can actually preview. The, output of each of those queries and as you can see we, do about eight account. And, user, and opportunity. And. We do a bunch of applied, predefined, steps so we do a lot of things like removing. Some fields from Salesforce, that aren't relevant on the other side we. Do some fix ups of values and. At the end of all we do the mapping, let. Me actually show you, one. Do that I'll show you later. At, this point I'm going to just click Next you know it's a confirmation. You know a preview my data looks, good. And. Now if I click done it's actually just going to create the definition, of those entities, in. My data flow in my current data flow project, here so now I see all those entities are predefined, and, I can go and save, this. Data float it as, force demo. Ignite. Okay. Okay. See. This is why I'm using Chrome but for some reason this button is not really, clicking. Now. Click, ok now it's a spinning. Okay, so now it's actually gonna save that definition, and I could go and create a schedule, to refresh that data set, and, so that data flow actually. Cancel out. Okay. So now I'd work from, here I could either refresh, now or set up a schedule. Yes. I wanted to save my changes I told you five times. Okay. So, the. Last time other I want to show you is to actually get data from, common. Data service for apps into, a power bi data flow, let. Me find my way back. My. Data flow authoring UI. Okay. So we are in the same place we where we actually have the sauce for demo data flow in, there let's actually go create another one. We're. Gonna go add new entities. And. In this case we're going to pick one connector. That it actually did not show up in the common. Data service for Apps experience, which is the, common data service for Apps connector, so you can extract data from the. Common data service into, data flows, experience. Here it asks you for your server, URL all your dynamics.

Endpoints. This. Is another one of those really hard to find URL so. I'm going to show you where, to go find. That by, the way this project. Completely just fine we're, gonna go to settings, in, a powerups below and advanced. Customizations. To. Go all the way to the bottom to developer, resources, and. Then you get to this resources, page which, is actually real fanta kate's you again then. You need to copy one. Of these which is actually not easy, to copy. And. Copy that and then you need to stripe out some so you end up with a URL like this you're. Probably more. Familiar with dynamic than I am but it took me a while to actually figure it out there's, actually a article that, explains, how to get to that. Explains. What I just did here step by step. Google. That I guess. So. If we switch back here, and. I type in my server, URL I'm. Already signed in again because I just tried this earlier today but I'm signed, with my organizational. Account then, we click Next and we're going to go, query. Your, common, data service for apps instance and, we're going to show you three, buckets we have common data model based, and it is and, then we have custom. Entities and then we have the system, or underlying entities. Let's. See if my custom, entities the Reg is created are here a top. 10 paying customers, is here so let's pick that one this is an example and, then let's also pick something. That we. Loaded, before. As. A, CDM. Entity I think, I have my users, table here system user, so. I'm going to pick that one and. I'm going to click Next so. Now I have these two queries and, I'm. Going to show you actually how to map and, this. Is kind of a no op but I'm going to show you the mapping screen in the data flow experience, to map to see the M as well which. In this case it's, gonna look very simple because if you click map map to stand out here this new transform, in the toolbar it's. Actually going to allow me to pick entity. From CDM, it's, called user. And. It. Actually looks so simple because we do out of mapping based on column, names and types and in this case because I'm mapping from already. In CDM shape entity, which was system users user. CDM, entity definition, is just a one-to-one mapping but. You could actually come in here and change that. Mapping or do it for any other, source. Source. Table but, once you do that it's going to define that mapping step it, will just look like this and you saw how the entity, type now says user and. This will give you a little bit more information about what that is but, then you can click done. And it's gonna you know create. That same. Entity. With, that CDM. Schema. In this case in the power bi data flow so in inertial data Lake I have these two and I could do the same save, and scheduled. Refresh in there, so. That's the, end. Of the demo portion, I.

Could, Keep going on, and on with power query demos, but and, if not the main focus of the session we'll have another. Session on our power query later this week I think, now it's a good, to actually do any questions, on the data flow spar or the common data series for apps for business users part. Okay. Okay good that's. Clear so, just, recapping on the session goals we did an overview of a common data service and the common data model we show you the, experiences, for integrating, data from admins and business users then, we show you also sneak peek on the upcoming power bi data flows capabilities, and how to synchronize, between both as well, please. Evaluate the session, and here's a reminder, about the sessions, I know some of you took pictures about this earlier so we have quite a few sessions the CDM, session is tomorrow I believe, tomorrow at noon. 12:45. So, I recommend, you to go to that one to learn more about CDM, and, we'll have more sessions on power query the Gateway and a few other things throughout. The week mostly, ending on Thursday and. Hope you guys enjoy the rest of a week and thank you for coming thank. You. So much.

2018-10-08 09:49

Other news