Hi everyone, and thank you for tuning in today. My name is Madison and I’m on the marketing team here at AgileThought. So here’s a little bit about AgileThought, we are a custom software development and consulting company that helps fortune 1000 clients transform, build and run their digital business. So right now I am joined by our presenters, Jose Chinchilla, our director of data analytics and Naga Vallamkondu, a technical architect at AgileThought. During this training, Jose and Naga are going to teach you how to get started with Common Data Service and Power BI Dataflows, as well as the benefits of using Power BI to create reports by connecting to dataflows using the Common Data Model. All right, so now I’m going to go ahead and hand everything off to you Jose.

Alright, thank you Madison for that introduction. Uh, so like Madison mentioned today, we’re gonna do a quick introduction on, uh, how to get started with Power BI dataflows and the Common Data Model. Uh, we’re going to do a quick introduction on the history behind the Common Data Model and how it came together. And then we’re going to do a quick demo. Uh, Naga, my colleague in the data analytics practice will show you how to map data entities using Power BI Dataflows in Dynamic 365 CRM data. So, uh, how data the Common Data Model came to be and what it is. Right. So let’s do find that really quick and, uh, run through a brief history or a brief background on how we go get started. So, uh, last year back in, uh, 2019, uh, or 2018, uh, Microsoft announced a partnership between SAP and Adobe. And these are the three major platforms for, uh, some of the, uh, CRM and ERP system that are commonly used by enterprise, um, organizations worldwide. Uh, they understood that some of their clients have multiple platforms. So there, there were clients that have SAP, ERP and CRM systems along with Microsoft’s, uh, ERP, CRM systems and Adobe’s, uh, CRM system as well. And they wanted to facilitate, um, the data integration. We can all use different platforms in a more unified, uh, in user, uh, uh, intuitive user experience. So what they did is they came up, with a collaboration called the open data and the open data initiative is meant to be a platform or to be able to see your data across all these different systems.

From, uh, the open data initiative. Uh, uh, the main goal was the ability to integrate all this, uh, system that were considered data science and bring them all together into a secure, uh, store that, uh, is managed by, uh, the collaboration and be able to have a standardized, uh, set of schema set of tables, entities, attributes, uh, metadata that allow all these three systems to come together in a more unified way. So for this, um, Microsoft put together what’s called the Common Data Service and this is just a platform that allows application, like a Power BI, Power Apps, Dynamics 365 that you find to be able to come together and leverage the collaborative, uh, data model that SAP Adobe, Microsoft put together, uh, in what’s known as the Common Data Model. Uh, the Common Data Model like I mentioned, is a set of, uh, entities, attributes, meta data, relationships that allow you to model your data in, map your data together from these different systems without you having to define your own schema, your own data model, um, and essentially allow you to jumpstart your, uh, efforts into integrating all these platforms.

So the Common Data Service, as you see, is part of the, uh, power platform, uh, set of, uh, services that allow you to integrate many applications together. Of course, all the power platform, uh, including Power BI, Power Apps, automate in virtual agents as well as other specialized, uh, data services that may be, uh, connect data connectors or more specialized data connection. These may include like Salesforce and, uh, Mercato, uh, HubSpot and many other, uh, ERP and CRM systems as well, um, as well as the ability to leverage the Common Data Model through, um, some of the machine learning in artificial intelligence, uh, services that Microsoft has made, uh, uh, available in the Azure, uh, services.

So the Common Data Model, uh, like I mentioned, uh, is it has a prebuilt set of entities. Uh, some of them are considered core entities, uh, which you see on the left side of this diagram, uh, including account activity, contact, uh, currency, uh, email, et cetera. So these are the core set of entities that are shared across many CRM and ERP systems, including Microsoft, SAP, and Adobe. Uh, it also includes some specialized data marks or some specialized models that allow you to quickly, uh, be able to import data and be able to view it in Power BI, uh, or use it in some Power App, for example. So they’re a Microsoft in a, of their, the partners are working on these specialized data mart, specialized data models around several industries and verticals that allow you to essentially jumpstart a analysis, for example, on services or sales or web traffic analysis, telling management alliances, uh, in one of the most important nowadays with all these, uh, COVID-19, uh, related, um, issues, uh, our health care, uh, based models, including some of the risk assessment, patient analysis, et cetera. So you can quickly jumpstart, uh, that analysis by using and leveraging, uh, Power BI Dataflows in a Common Data Model.

To, um, view a complete list of all the different entities that have been created along with the different relationships, metadata, uh, in models that are now available. You can use the visual entity navigator, uh, which is made available through Github.io, so that link you see on the screen: Microsoft.github.io/cdm and you can navigate and see all the entities the definitions, uh, meta data, data types and relationships. For example. It’s a very, uh, very nice tool to quickly understand what is available and, uh, understand definitions, relationships. So with that said, we’re going to go into the demo mode. So I’m going to hand it over to my colleague, uh, Naga, who is going to show you how to, uh, um, map entities using Power BI Dataflows.

Yes. Uh, thank you, Jose. Yeah. So, uh, Jose as briefly talked about, um, you know, the introduction of Common Data Models and open data initiative. So today I’ll be doing a demo of it. And how do we use that concept in the data flows. So basically the reason the data flows have been developed by Microsoft had this major three reasons, three use cases. One is to self service data prep. It like having this service to act as a self service tool. And then it’s basically having that data use for advanced analytics including machine learning and AI. And then also have the capability of integrating the Common Data Model into the common dataflows.

So as you can see in the deck here, the Power BI Dataflows in a traditional way. We used to grab the data directly in the desktop by using a power query. We use the data connectors and then we transform the data and power queries and then build in a data set. So the problem here was, you know, once you pose the data set, you know it is very hard. That data set is usually limited to a desktop solution and reusing the data set is to be a pain. And now with the introduction of common dataflows, the Power BI service, the common data flow actually resides in the Power BI service. That way you don’t have to use it. Power BI desktopp to build the dataflows. We’ll be using the Power BI service and you can see the Power BI Dataflows will be created on the Power BI service. And the advantages of it is it is using the kind of a traditional power query approach, including having to reply that data into Azure Data Lake Gen2, which is kind of a fast and a have a as a kind of a background to store this data. And then we can get the data sets out of the Power BI Dataflow and then this would be available to all the uh, end users.

So as I was talking before, so this where the main use cases. The first one is self service. So consuming the data through the data, uh, desktop Power BI desktop is basically you have to use the connectors and transform the data and then make it available for the end users or in the desktop to be consumed. There’s a lot of mappings to be done, a lot of sources to be pulled in and you can see in the, in my demo like how easy it would be to do that in Power BI Dataflow. It not only cuts the time but also cost and expertise. So even like a power user and a like a kind of a a above average end user can do that. And but it also gives you the power greater experience and the data preparation experience that you already have. And the next use case is advanced analytics. That’s what I was talking about. You know like once you get the data from the common data flow, it is told in the azure data lakes gen2 the storage area. So which means now it is available not only to other Azure services like machine learning, azure data lales and Azure data SQL server data warehouse, but also for all the other data engineers and data scientists in their own custom applications. And the lastly is the Common Data Model where Jose has talked about, you know, building a Common Data Model which like as a part of open data initiative they have done that. So now the dataflow is a tool or a mechanism to consume that and the system that way it can make use of the common data flows that have been created. So the Common Data Models are nothing but the standard entities and contacts. In my demo you can see how those common data entities can be utilized in the common data flow.

So this is high level diagram which I was talking to you is you have this Power BI service where you create a data flow and then it creates a Common Data Model folder in the azure data storage engine, gen2 and then you can see how Azure data factory can ingest the data. And then as your data you can prep the data using azure data bricks and then get the data into SQL server data warehouse. And also the machine learning language can utilize this data as well as other custom applications. So you can see by if you’re a business analyst it is very easy and you don’t have to do much code. But as you progress this you can see it might involve like a, you know, data scientists or engineers to utilize it. And let me walk you through this slide, which is nothing but the open data initiative that Jose was talking about. As you can see the Microsoft products like Power BI, dynamics 365 or office 365, we, they can all use the Common Data Model and then you know, ingest the data in and not only that Adobe or SAP also can, you know, map the Common Data Model and as well as Azure IOT and then all of them can be utilized by Azure data services. As for the custom, uh, applications. With that, I will directly go into the demo.

Okay. So to start the demo, what I’m going to do is I’m going to open up the Power BI app services. So I am logged in here with my Power BI services and I’m in right now with the workspace called data analytics practice. So as you can see there’s a data flows, a section where you can create multiple data flows. So once you are in this section, you can go click on the create and there’s an option for you to create a data flow. So once you create a data flow, it will give you an option whether you want to create a new entity or important model. Right now what we are going to do is we are going to do as a new entity. So we are going to add new entities. So entities are nothing bad, you know, a set of objects, like a table structures. So as you can see, once I do that, once I click add entities, it allows me various types of connections to use. So in this section, so what I’m going to do is I’m going to utilize common data service. I’m going to click on common data service. So in my example, what I’m going to do is I’m going to use the CRM data and connects to the dynamics 365 so I have the URL here. So let me paste that URL. And since I’m already signed into the CRM and my power BI account is already tied to the CRM account. So all I can do is I click next.

So what it is going to do is now the common data service is like a power platform service, which connects the entities to the application that we are. So as you can see in common data service, once I connect, there are two types of options available. One is entities and one is six system. So we are going to choose entities for building this Common Data Model. So entities, I will tell you why we are using entities in the, you know, as you go. So right now, what for this exercise, I’m going to just choose three entities. One is account and then let me find the opportunities. It’s opportunity there. And then user system user. Okay. So I have chose three entities. So entities are kind of a prebuilt, uh, definitions of the tables. So which has not only the Pew system table but also has, uh, additional columns that are brought into the system. So as you can see, I have this three entities that I was able to connect to my CRM dynamics using the common data service and able to pull in all the columns. And now the data set, as you can see, there’s a whole set of transformations you can do, which is nothing but those transformations were available even in the power query. So this is a not a new, uh, new, uh, you know, get familiarized with you or if you already have a power query experience, you know, it is all available here with the exception. You can see here when I go to the home tab, we can see there’s a card mapped to standard CDM. So this option is not available for you when you try to do this common, when you try to connect the data through common data services. And when you go to power query, this option is not available. So what I’m going to do right now, I’m not going to build a whole set of transformations, but I’m going to show you how to use a Common Data Model.

So once I click and you can see there is a map to standard entities as also was talking to you in the last two flights where we have sets of lot of entities that are available to CRM or ERP or health systems. So since I’m connected to the CRM dynamics, so all the entities that are mapped to the applications are available here. Okay? So let me choose that account since I’m mapping it to the account. Okay. Now I have all the entity attributes on the right side you can see all this entity attributes are a part of the Common Data Model for the account entity. Now you have an auto map option. The reason I chose entities in the beginning was this, to make it to make my life easier is basically now what I do is click on the auto map. Now it automatically maps the output columns of the query to the map columns of the entities. That way I don’t have to do. Okay.

So I’m going to click. Okay. So now what it is going to do is it is going to take uh, the uh, the entities that are available in my query, which is nothing but account entity of the CRM and now maps to the account entity of the Common Data Model. Okay. So I’ll be doing the same thing for the rest of those uh, queries. So I’m now in opportunity query and then once it loads, it will allow me to transform it. So I’m going to do a CDM after CDM. So now I am an opportunity. So since it is a Microsoft product and this Common Data Model is also a Microsoft product. So you can see most of the names are very common and most of the columns that you can see, like Microsoft has done a lot making sure that the entities that we are querying, it’s almost mapped the Common Data Models because they know that eventually they want to turn all the entities into the Common Data Model. So, we click okay. So again, it is going to map column by column and then I’ll be doing the same thing for my other query, which is a system user.

So now I am doing a system user, so I’ll be doing the same thing. Common data model. So our mapping, so I’m going to type in the user. So see as you can see in my query behind the scene in the CRM the entities are called system user. But you know in the Common Data Model it is called user. So that would be if Adobe or SAP or any other system like you want to map it, you can use the common name. So as you can see the Common Data Model, when I click on the Common Data Models, you saw various entities available to the Common Data Models, which means, so if you have any other type of connector that you’re using, basically you can map it to the Common Data Model which means the Common Data Model entities are not only available to azure or like Microsoft dynamics or SAP or Adobe. Any system can, we can utilize the Common Data Model as long as you are able to physically map each one of them. So now all of the entities mappings are done and I’m not doing much transformations other than the mapping. So I’m going to save and close. So it is good to take some time. So what it is going to do is it’s going to validate the queries and try to build up all the mappings and and then it will give me an option. Hey, do you want to save this uh, data flow? So once I give the name then probably it will be uh, available in the data flow section shortly.

So we are going to call it as a yeah, dynamics 365. Okay. So you know, this is connected to CRM dynamics 365. I am giving it a common name that we know that we are pulling it from, so no one is going to do is it gives me an option to refresh now or set up a refresh later. Right now I’m not going to do a refresh. Now just I want to talk more about this common data. So what it did right now is basically it created this three entities, you know, we created the CRM dynamics 365 dataflows. And behind the scene there is a structure available for this entities. So let me switch gears and just show you, you know, how to utilize that in the power BI. And I’ll just show you, you know, building a brand new, uh, desktop solution. So I’m now in my power BI desktop, I’m creating a brand new solution and now I’m going to utilize the git data. So in git data, you can see there’s an option for power BI Data flows can go to more and it will be available in the power Bi platforms.

Since I’m already logged in into my power BI account, it is going to give me all the data flows that I have access to. So right now I have access to these two data flows. As you can see, my CRM dynamics 365 data flow is here. So I’m choosing all the entities here. Let me choose that. Just take care. Now while I’ve been here. Okay. And then I’m going to transform the data. So now I’ll be coming to the power query part portion of my power BI desktop. So the power query is again the same thing. Um, that is available. As you can see in the power query I was telling you the power query is the same thing, but the CDM option is not available, which means you can map the Common Data Model and the Power BI desktop because that is not what you want to do. You want to do it in the data flow. That means you are making it available. So as you can see, none of the tables are being populated because those tables are empty. Because the reason for this, I’m going to switch back to my, uh, data flows here. The reason I did not have any data is because I did not refresh the data. Okay. Before I go and show you how to refresh the data, I want to talk to you really a little bit more about the incremental data. I know once you refresh the data, how do you maintain that data, uh, going forward. So you can do one of the ways you can have that refresh schedule once every very few hours to refresh all the tables or if you want, you can do an incremental refresh. For power BI pro, you have the capability of refreshing the data on an interval of time, which means it refreshes the entire data set or entire entities. But if you have a power BI premium license, this option is available to you. I know it gives me an option of on and everything, but once I click save then it will be prompted to, you know, purchase the license. But I wanted to go ahead and show you what can be done in incremental refresh and how that capability of self service was. You know, the first self service was, you know, we are able to connect easily to the entities and then map it very easily. And now this is another very important, you know, settings where you can easily try to do an incremental, you don’t have to write complexes as high as packages and all that.

So, as you can see here to the date time field to filter by, there are various uh, columns available. So the most common uh, date time I will be frequently changing whenever you update is modified on. So I’m going to choose that data and I can, so to store the historical data or you want to archive the data or you want to see in my data set only the past five years or three quarters, five quarters of the data, you can set all that here. So for right now I’m going to do okay, give you the data for the past two years, three years, which means going forward, it always tools the three year incremental period. So basically the fourth year will automatically drop, you know, as a year passes by and then refresh roles from the past. It’s basically, I know that we are pulling three years worth of data. Uh, and what is the data you actually want to refresh? I want to refresh the last three days of the data. So as you can see when our the day whenever the records have modified within the last three days, that is the data I want to refresh, which means if a row has not been touched, you know far more than three days back. So those will not be touched which means this will be common that way either you are reducing the time. So that’s what I said, time and costs are very important and now the time is being reduced in incremental processing. That way you don’t have to wait much time and you can add up. So as you see this brief description on what you’re doing as well and that gives you a sense of what you’re doing. And now in some cases like financial data, sometimes you don’t want to make changes, you don’t want to do anything, but you want to keep all the settings. But you want to configure this incremental only if a particular column or some flag you said. Yeah, especially it will be good in financial where you know you don’t want to refresh a quarter until certain transactions have happened or certain processing of the data has happened. So you can choose those kinds of columns which means it doesn’t trigger unless there’s a change in this case. Say for example, say if there’s a change in open deals, deals then only the whole incremental step would be running. So this just kind of the features available. I’m not going to save it, I’m just going to cancel. But I wanted to quickly show you how to do the incremental refresh. So right now I’m done with my uh, solution here for the data flow and I’m just going to close. Okay. As you can see, the CRM dynamics 365 is available and I was telling you how to refresh the data. So this is the, you know, an option which gives you immediately how to refresh now. So we can click on that. But I’m not going to do, because we already have similar setup data set that I will walk you on that we don’t have to spend time. Also you can go to the settings and then basically in the settings you will be able to uh, you know, do the schedule refresh on, you know.

So since I don’t have a power BI premium license, what it is going to do. So if I say daily, you know, on a particular time, then basically goes and refreshes my, uh, entities, you know, so I can add, you know, multiple, uh, dates and then every incremental on that particular timing it goes and gets the data for the entire, uh, entities. Okay. So I’m not going to do that. Let me switch back to the demo piece for the Power BI. So I am definitely going to be a solution that I just kind of built briefly on this. As you can see, uh, in my query sections, um, if I’m building a new page, you can see all those three entities are available here and you can see our account. And, uh, since it is a big table, it has taken time to drop down the column values. So what I can do is I might be no such bye full name. Okay. I have my system user, a like a full name and then we’ll grab some opportunities here. So you can see that a lot of columns available here. So as I was saying, they have a transformation section in the power desktop. You can use that to hide and show the columns depending on the solution. But in general I would recommend leaving all those columns in the data flows because we don’t know like, you know who else would be using the data flow. So you don’t want to restrict all the columns in the data flow yourself. You can do it on the power query now I have that and then I can convert into a chart, you know, and then, you know, try to do some data labels, you know, it’s some fancy.

Okay. So I don’t want to take a time here, you know, in showing how to build this. But yes, you can see, you know, I’ll build one with similar capabilities where you can see how opportunities like here by customer here. And then by tap, you know, uh, you know, which step they are, the opportunities are, you can click on that and basically have the data drill down, you know? Okay. And, uh, since you don’t have the, uh, uh, power BI premium, what we have to do is basically you have to do all the connections manually if you’re trying to match, you know, get the entities. So let me switch back to the data flows. So this is how you can use a data flow that you created here. And uh, let me go back to my workspace or real quick and I’ll show you the data flows that we created. Okay, so you might have a question. Now I created a data flow. Now how do I add entities? So basically it is very easy. You can directly click on that data flows and then you can add more entities. Okay? So let us click add entities here. It will show you, you know, those options. Okay So when I click add entities, I know that I’ve restricted this data flow. If you go to the CRM dynamics, okay, so here is a concept that data flows are not restricted to any particular application. So it is restricted and you can bring in the data from multiple applications. The only thing is right now I did was I just created data flow to have a set of entities. Then I connected my to the common data service and then connected to the Azure data CRM and then bring in the entities.

So again, if you want to go back add entities from that a CRM application, you use a common data service and bring in that. But if you think you want to bring in, you know, information from other systems like SharePoint or excel or SharePoint folder, a, you know, kind of a SQL server database or Oracle database, you can do that. But highly, highly encouraged not to build a data flows, especially when you are trying to build a data flow with big applications like the dynamic CRM. You don’t want to build, uh, uh, all the entities in one data flows. You can have multiple entity data flows. You can have one data flows for sales, SAP system, one data flow for you know, a phone call system, one data flow for HR system and one data flow for financial system. And behind the scene if you have power entity, you know, power BI premium, you can connect those entities manually here at with end user. Doesn’t have to do, if you don’t have power BI premium, the end user like is the consumer like Power BI desktop. They have to create those, uh, links manually and do that. So in short, we discussed briefly on how to create a data flows, how the CDM is available in the data flows, make use of common data service to create, connect to the data flows. And then, uh, how about the solution connect to the data flows. With that, you know what I’m going to do is I’m going to, um, uh, have Madison take over from here. Thank you.

Great. Thank you for that Jose and Naga. So prior to this training, we did receive a few questions about the Common Data Model. And again, if any of our viewers have any questions or are interested about our power BI Ignite offerings, please go ahead and email them to us at marketing@agilethought.com. So we’re going to go ahead and move forward with the first question which is why should I use CDM versus my own data model?

That’s a good question. Um, so essentially CDM allows you to jumpstart your mapping and visualization of data, right? You don’t have to build what Microsoft partners, uh, have built a, you don’t have to build it from scratch. You can leverage, uh, the Common Data Model that caters to, I would say 80% of the use cases and analysis, uh, that you might need, um, these data to be integrated, uh, through these different entities. So you don’t have to build this from scratch. You have a headstart by using the Common Data Model, um, as well as the ability to leverage this Common Data Model with other applications like we mentioned, power, the power platform, including power apps. Um, any even other services like machine learning, AI or uh, uh, use those Common Data Model entities in a more specialized flows, uh, for, for example, with Azure data factory. So you can leverage this from one single place and you don’t have to build this from scratch.

Okay, great. And another question we received is, are there other vendors like Salesforce using CDM other than Adobe and SAP?

Yeah, so, uh, open data initiative that, uh, Jose talked about was initiated by Microsoft, uh, SAP and Adobe. But, uh, Salesforce kind of have a similar thing that they, you know, core customer 360, which they are trying to do that. But that said, you know, the CDM model that we have been discussing not only limit, not only Microsoft or Adobe or SAP can utilize various applications including your native application or Salesforce or HubSpot. You know, all those applications can use as uh, Common Data Models. If you think like Common Data Model entities or entities with a defined data structure, as long as you have entities you bring in those columns or the entities that matches those entities for Common Data Model, then you will be able to create a utilizer of CDM model irrespective of what application you might be used a different connectors to get the data in. But the common methodology is once you get the data, you use the Common Data Model in the data flow to connect to that entity yes.

Okay, great. And another question that people reached out to us about is what is the difference between power BI data flows and power platform data flows?

Oh, that’s a good question. I can take that. So essentially the power BI platform or sorry, the power platform data flows, uh, is kind of a new feature that Microsoft has added. Um, and the difference between power BI data flows and power platform data flows, uh, is essentially that power BI data flows. Like the name implies are, are specific, uh, only for power BI. So only power BI can access that endpoint or that service. Well, the power platform data flows can be used by power BI as well as power apps automate dynamic 365 and some other service specialized services for machine learning, AI, uh, including Azure data factory. Uh, so the power, uh, platform data flows, uh, I believe that would be the standard going forward. But it’s the same similar concept that we just talked about, uh, about power BI data, uh, data flows. Uh, so is that an essence is essentially, uh, Microsoft expanding the use case and, uh, additional services that can leverage these same data flows. Uh, at some point I believe they’re gonna just become the power platform data flows, which can be leveraged with power BI power apps, uh, automate and some other services.

And we have time for one more question. So is the CDM limited to CRM data?

Uh, no. The CDM is not just limited to CRM. Uh, in my demo I just did CRM, but you can connect to various, uh, multiple other application like ERP systems, like Microsoft dynamics, CRP, or you know, other kind of systems. So we have lot of entities that are available. The one we connect, the CRM, probably those entities were available, but depending on the application, so those entities would be available as well. So you have some with a health related, uh, you know, uh, entities, um, with, uh, ERP related, some with like office 365 entity related. So there are a lot of entities available. So Microsoft is kind of a, as a part of this initiative, keeps on adding those entities as they need. So, and they’ll be some changes also to some entities depending on, uh, so it is a constantly evolving process.

Okay, great. Thank you both for answering those. And that concludes today’s training, so we’d like to thank you all for watching and if you have any additional questions, please go ahead and send them our way. And we hope you have a great rest of your day.

Thank you.

Thank you.