How to Design and Build a Data Model in Microsoft Dataverse
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 2024
- In this video I break down the thought process of creating a data model in Microsoft Dataverse, taking you step-by-step through the process of designing and building a data model with multiple tables and relationships. This can help you prepare for exams including PL-100 where you need to understand how to create a high level design for your app and data source. #MicrosoftDataverse #DataModeling #PowerApps
0:00 - Designing a data model in Dataverse
0:37 - Why Dataverse?
4:46 - Create a solution
6:02 - Design a table and columns
12:53 - Create a Power App connected to your dataverse data
18:11 - Designing your data model
22:05 - Contact, User, AAD User Tables
25:40 - Create 1 to many relationships between tables - วิทยาศาสตร์และเทคโนโลยี
Connect with me:
☕ Buy me a coffee: www.buymeacoffee.com/lisacrosbie
🖇 LinkedIn: www.linkedin.com/in/lisa-crosbie/
📼 TikTok: www.tiktok.com/@lisa.crosbie
🐦 X (Twitter): twitter.com/LisaCrosbie
📚Take my LinkedIn Learning Course: Microsoft Power Platform Fundamentals (PL-900) Exam: Power Apps www.linkedin.com/learning/microsoft-power-platform-fundamentals-pl-900-cert-prep-power-apps/
Excellent way of explaining the subject and its insights. Extremely helpful Lisa! Subscribed! 🙂🙂
I am loving these tutorials. You deserve a Golden Medal Lisa. Thank you for all your profound help and support, taking your family time to impact other professional lives. Really appreciate you.
Thank you so much ☺️
Thank you so much Lisa for sharing your knowledge. Very simple, useful and practical.👏👍
Thank you so much for this video Lisa. I truly appreciate your work.
Thanks Lisa, I REALLY enjoy your content on Model Driven app's. Helps me a huge amount as I am trying to teach myself to replace Access app's that I have created in the past😊.
Another great vid Lisa, really helpful 🙏
Fascinating stuff you've been putting out there, Lisa. Love it.
Thank you so much.
Greetings from Germany.
Hello all the way over there in Germany, thank you!
Thanks so much, Lisa! That’s really helpful!
Thanks heaps for this video Lisa. The Crows nest notation and how it translates to the backend with regards to Creating relationships between a myriad of tables was really helpful. Would love to go deeper into more complex model designs to get indepth understanding and also would love to see the AAD User table put to use :) Thanks again!
Excellent, tnanks, the AAD video and more of this are both on my list
Yes... VERY helpful 😀 Thank You 💥💥
Excellent Tutorial on Power App - Dataverse , Ma'am. Watching your video, I am pretty confident and started preparing my own tracking report. Appreciated for preparing such an informative video and your efforts.
Thank you very much for your videos, especially for scheme at 35:20. It's very helpful for understanding relationships between the entities.
I found it most helpful to think of it like this: If you have a form on Table 1, and you want to select a lookup record in Table 2, that is a One to Many Relationship. It means you have a lookup field on Table 1 Form, and a Subgrid on Table 2 Form. Many to many requires a subgrid on both forms. With many to many, by default, you can't create a new record from a subgrid so the related records have to be created in other ways. Idk it's putting it into this kind of context that i think is the most helpful for creators.
Glad it was helpful, thank you
Really great video Lisa! I really like you delivery/production too. This is the first one I watched on a whim tonight, looking to answers some questions I've had for a while. I'll definitely be checking out some of your others.
I have a couple questions for you. First, when you choose any of the standard OOTB tables, are you getting a copy of the table? Inheriting all of the structure, but your applications own copy? Is that how/why you are able to customize the table? You're not changing the "business" table to supplier for the rest of your organization, and the rest of the organization is not seeing your data, and you're not seeing the data from other application. when you choose it. I can imagine there may be some times when you might want to share a table like customer across all your organizations applications, so perhaps that is also an option somehow? But, in general, you're just getting a copy of the structure and underlying capabilities, no data concerns outside of your application. Correct?
On a related note, I gather you recommend using the available standard tables vs. doing everything custom. I understand they can deliver much of the structure you often want, and more. Things you might not even know you want - yet. However, all of the columns can be a little overwhelming, distracting and perhaps of concern in terms of efficiency. Both in working with the tables and the data size, no?? Can you remove columns, or do they need to remain to ensure some underlying capability, one you might not even leverage in the application, remains possible? Even if you can, should you? Do you just learn to live with all the baggage columns for their capability - kind of like you need to accept all the tables that are available to you as a benefit?
Lastly, being old, I find the web based GUI interface to be somewhat limiting in terms of speed and perhaps storing the database structure/definition as code for quick modification and replay. Have there been any developments of a more code based data model for the Dataverse?
Thanks again!!
Great videos Lisa. Would like to hear your thoughts about using AAD Users table in MDAs. Namely its limitations (ex. power automate), limited of AAD amount data being pulled.
yes please! more videos with aad user tables!
Excelente aporte y muy bien explicado Muchas Gracias
Wonderful explanation
It is really Amazing, Lisa
I love your content! It helps me a lot. Thankyou :)
One of the best videos I have seen. Better than Pluralsight videos! I have a project coming up where my first steps are Dataverse modeling which I have never done before. This video makes me confident. You saved my job!
Awesome, and good luck!
Another great Dataverse tutorial, Lisa! I'm coming from canvas apps, so I've got to admit that the lookup method of creating a table relationship makes the most immediate sense to me. I think that thinking about it as a crow's foot diagram is a really good idea, though. The reason being is that one can make the mistake of creating lookups going both ways between two tables and not really thinking about which table is playing more of a dimensional role. (Note that you can use Excel's (or Power BI's) PowerQuery environment to model these tables out visually.)
Have you done content of tying a Document Library in SharePoint with a DataVerse model? Use case: contract lifecycle management (storing contract metadata in Dataverse and using SharePoint as the DMS). Business-user front-end in Power Apps canvas app, admin tools in a model-driven app. Happy to have a chat about it!
Really excellent tutorials 🙂
Hello Lisa, thank you for your great content. You're doing a great job. I noticed a mistake in this ER diagram, specifically in the connection between "Asset Allocation" and "Asset Check". The connection should be one-to-many (1:N) from "Asset Allocation" to "Asset Check". With this configuration, we will have the key from "Asset Allocation" present in "Asset Check", as shown in the picture at 00:08. Additionally, there should also be a one-to-one (1:1) relationship between them.
Love this video!! Please create the data model for the AAD user
Thank you Lisa! Great video. Your explainations are really comprehensible, especially for us coming from the "user" side of things.
When are we expecting that video on AAD Users though? I think that would be a great one 😁
On my (long) list of videos to make!
Up until today, I only had Lisa Kudrow on my famous Lisa list. Now, I've got another delightful addition - the wonderful Lisa Crosbie! Double the Lisas, double the charm!🎉❤
hahaha, that's very great company, thank you!
Glad to see you address this topic! I think Model driven apps are relevant as a low-cost, enterprise alternative to SAP/Salesforce/Dynamics 365 and not sure organizations realize. 90% of the functionality and development is required, and is really centered around the data modelling and translating business processes, and does not necessarily need to be paired with high licensing costs!
100% this! Thank you!
Thanks, excellent video as always.
Is there any information on the recommended tables and how to use them?
We are using Account and Contact at the moment.
And do you have any video on how to use alternate keys in a Data driven app?
Hi Lisa,
Thanks for the detailed video on entity relationship, awesome material for learning, I would request you to make an model driven app at the end of creating the tables, and show go through a cycle of asset allocation, asset check, returned etc
That will help us a lot.
Actually the model driven app tutorial based on this was the one I did first - you’ll find it here Power Apps Model Driven Apps FULL COURSE for Beginners
th-cam.com/video/LIC8DFW8fOE/w-d-xo.html
I have a lot of respect for this knowledge - I am so far behind on CRM using Excel all the time, I am hopeful I can put these things together for my radio and TV station.
Great to see you on the learning journey and good luck.
Hi Lisa, It appears this is the video you did on Data Modelling before I requested you do one when we met at the South Coast Summit at Farnborough in the UK about 2 weeks back. I should be because it seems you released this about a month ago. I've just watched it and I think its great! It's already given me clues as to how the thought process should be in terms of identifying and creating the required relationships. Would be great if you can expand on this and go into a bit more depth, either in another video or continuing from where you left off. Awesome presentation. Thanks.
Yes, when we chatted about that, I had already done this one, I'm taking it as feedback that you'd like more along these lines! Glad you found this one, and lovely to meet you at SCS.
@LisaCrosbie Yeeeeeees, more of that, please 😀. Was good meeting you as well. Looking forward to the Part 2. Thank you
Also auditing / governance is a dataverse advantage too. I would actually say never use Excel for business data. Good video Lisa
Absolutely, yes. And thanks.
thanks a lot, you are amazing girl, and wonderful teacher
Thank you! This is so helpful. Is it possible. to do this in the context of collecting requirement, team project roles, user stories, the design of the data model with MS .. and how all that translate in dataverse. and Apps.
You're stellar 🌟
🥰🤩
If you need a ERD for dataverse you can just use power Bi to connect to your environment and then your solution, and choose ERD, it will give you a very very good ERD
Noted, thanks. However, I do think these are steps to take before those tables are created (though I think there may be more efficient ways to create the Entity info before creating the tables, in which case your method could show/export a final version)
Thanks for the tip. There are also some great tools available in XRM toolbox for this.
@@LisaCrosbieHi Lisa, that's what I don't know! Do you have a good ERD tool in xrm tool box for recommendations?
I am very grateful for this wonderful lesson and I hope that an make video will be presented about the HR app, such as the employee database, attendance, and vacations
Thanks for watching and glad this was helpful. I haven't done much with HR though at this stage.
Hi Lisa! Thanks for the video, very helpful. Let's say I have some data already, and I want to port it over to my new Dataverse tables, how could I import it while maintaining the table relationships? Would I do it on the front end like in your video "How to Turn Your Excel Spreadsheets into Power Apps"? Or could I simply import the data in my tables section? Thanks again
Hi. Excellent video. Just wondering how would you apply row level security so that a user would not see certain data (at either the row level or column level)? Thanks again for all you do for the Power Apps community.
I would love you to explain, based on this example how interrelated tables work togehter or how the mapping has to be set up. For example that we can immediately show which Contact had been served by which supplier already.
Can you do a mock up on creating a reporting data mart concept using DataVerse? The ideal topic covered would be data pipelines from csv/excel, sharepoint lists and calculated tables/views that summarize content from underlying tables.
Good stuff Lisa. Are you aware of any modeling tool that could eventually be helpful with Dataverse?
Great tutorial. How would you connect a form (in a model-driven app) to the data model so that input values can be stored in the appropriate tables when a user submits a form?
When you build your table in Dataverse you will find the form component right there in the build experience, you don’t need to do anything extra to connect it. This tutorial will help Power Apps Model-Driven Apps: Tutorial for Complete Beginners (2023 UPDATED)
th-cam.com/video/JtMKcvQcKNY/w-d-xo.html
Thank you
Great video Lisa. I've been a Software Engineer for over a decade and am taking a look at Power Apps for the first time. I'm digesting how Power Apps abstracts away many software design fundamentals that people like me know. It seems it'd still be a good investment for anyone getting into Power Apps to at least have a basic grasp of these principles. We seem to agree the Asset Allocation table is a join table for the many to many relationship between Asset and Contact. I see you have the option to create the many to many relatonship rather than manually creating the join table and the two many to one relationships for this join table that sits between Asset and Contact. Does creating a many to many relationship table automate the setup of this join table so it still lives?
You're absolutely right, understanding how to do the data modelling is the most important part, and if you have that background then learning the click and point here should be relatively easy for you - although I do find with software engineers there is a path of "unlearning" things that are abstracted away, as you point out. Have a look at the last chapter in this video for what many to many relationships do - th-cam.com/video/ICLGQ1cIo9M/w-d-xo.html You can create them that way if you don't need or want access to the join table, but if you do, then the way I've done it here will be the better solution.
This is absolutely great, Lisa!
I had a question around Dataverse.
let's say there is a canvas apps, which is used firmwide(around 10K users).
It is wise to use Dataverse as the backend source as this might come as huge licensing cost?
In other words, how can we handle leveraging Dataverse for a Canvas app which we would like the entire firm with 10k users to consume.
Using Dataverse is what requires the additional licensing. There is not a way to create a model driven or canvas app that integrates directly with Dataverse without a $5/user/month for one app (10 for 2, 15 for 3), or $20/user/month for unlimited apps. Theoretically, you could license a single model driven app, create virtual tables with SharePoint to create free use canvas apps that could be integrated into the single premium model driven app. However this still requires you to have at least one $5/user/month license which you don't have now. So since its 10,000K users, i would wonder how many use it daily because if it's widespread access, but low use per user - then there is one more license option which is "Pay as you go".
Or how many people do you need to interact with the Dataverse table? or do only you want to do more sophisticated things with the Data. You could have a single Dataverse license for you and set up the SharePoint List as a Virtual Table. However, I do think there are limitations to how you can then Edit the sharepoint table that I need to look into. Maybe @lisacrosbie could do a video on how to make changes to an existing Virtual Table relationship between dataverse/sharepoint 😛
You need to weigh up the cost vs benefit here. If it's a simple app that everyone uses that doesn't need secure, scalable, complex data, then you won't be able to justify the cost. However if you are genuinely working with enterprise data - huge data set, mission critical data that needs to be secure, complex tables and relationships and business processes - and genuinely the entire company needs to use that - then you are at massive risk building that on a productivity platform, and you would be in the market for enterprise database systems, most of which will cost a lot more than $20/user/month. If you genuinely only need to build a single app, then you can get per app licensing which is much cheaper, or look at the PAYG licensing if many people are using it infrequently. Also if you have 10k users you should talk to whoever does your Microsoft enterprise licensing about actual cost for your organisation.
Good video. I have a question. I need to use the table called "Mailbox" that is already included in the default tables since I need the users and their emails and this table has them, however, it is not updated with the latest users added. Is there a way to sync it so it's up to date? I am using users that are added in Admins in Microsoft 365.
Hi! Thank you so much for such amazing videos on Power Apps. I was wondering what tool did you to visualise the connections between the entities (tables) form min 35 onward?
Thanks - it's Microsoft Visio
Interesting. I particulary like using Ms-Access front-end applications, split with data in Dataverse Tables on the back-end to enable the best of both worlds without needed a dedicated/standalone SQL server or Azure SQL server.
That's pretty much it. But also a lot more flexibility and nicer UI than you could do with Access
The use of choice fields I have found is on of the biggest fudges in DV and one of the things we have to unpick the most from amateur LDS/ERD designs, removing them and placing them in proper tables so they can be managed and archived without massive data loss.
Choices vs related tables is on my list of videos to make!
@@LisaCrosbie not just related tables but also those with a 1TM relation to allow cascading choice logic via the data structure. So far for yes/no, fine, choices ok, but long lists such as business units, which change endlessly, we are finding a related table with the ability to archive more precessional.
Yes related tables for choices is better than choice option But power apps does not want to display related tables more than one relationship away from the table you are displaying. For example. You have the asset table related to the supplier table and if you had a states table related to the suppliers you couldn’t display what state you supplier is in when displaying your assets
Would the ADD table (Azure Active Directory table) work when people leave the company? Just wondering about assigning an asset to somebody and then they leave. Does the record stay in ADD with an end date/status or are they removed? If they are removed then I guess it wouldn't work for your asset scenario with PC/phone assignments.
hey Lisa, is there a way to bring in related tables columns onto the form for the current table? in your example, columns from asset allocation onto the asset form?
Yes, you do that using a subgrid. Jump to module 5 in this video Power Apps Model Driven Apps FULL COURSE for Beginners
th-cam.com/video/LIC8DFW8fOE/w-d-xo.html
Great content. I cannot figure out which table to use to map out my company clients and its subcontractors/suppliers. Can you please advise how can I use "Accounts" table to achieve this. Thank you
The accounts table has its own built it hierarchy, you'll find a lookup column on that table called "Parent Account" so you can use accounts for both the company clients and the sucontractors. I'd suggest creating a choice column (or using the category column and adding your own options) so you know which is which.
Lisa, i love the videos. Our company is selecting a new CRM+ERP system to replace a deprecated old ERP system. We are down to Salesforce +Rootstock or Dynamics. I'm a huge fan of Dynamics capabilities from Sales to field service, supply chain, etc. But the local integrators (Western Canada) are terrible salesmen. Any suggestions on who we could work with that can sell/present dynamics to those who are less tech savvy?
You could try Forceworks - www.forceworks.com or RSM Canada rsmcanada.com/technologies/microsoft.html
Newby question here : If I have solution A and solution B in the same environnement, with both solution using table Contact. If I had a contact in solution A, will it also be added in solution B?
I am also interested in learning more about AAD Users. I want to be able to visualize and sort users by certain criteria (title, organization, position) and be able to pull up their contact info through visually clicking through relationships to get the phone number and email.
You can configure a view for AAD Users (the table is now called Microsoft Entra ID) and bring in the columns you want, and then sort and filter by those columns on the view. If you want something more visual than that, you could use that table in a Custom Page.
Hi. Great content.
For a future video can you provide examples of the 8 Security Permissions.
I'm trying to wrap my head around the Append and Append To permissions, it hasn't clicked yet.
Yes, it's on my list. Append and Append to are basically about being able to connect related records to each other. But yes, I agree it's confusing!
This was interesting though, following along I'm a bit confused over the best practise with some of the default fields that are brought in and not needed. For example the Asset Allocation table - which is effectively just a way to join Contact and Assets in a many to one way. It has a default (primary and required) Name field - you are not going to give a 'name' to each of these assignments are you? Its a primary filed and required though .. I can't delete it. can I safely make it not required somehow? Should I just make it an auto number and hide it on form ignoring it? is that smart .. or a date field .. but there are heaps of date fields included already.
You have to have a primary name field so it’s best to find a sensible way to use it. Auto number is a good idea. You can also make it not mandatory
I prefer to use the systemuser table as the users activity status is being shown in a model driven app form. AAD virtual table hast some limitations about certain properties not being available on the power platform...
Noted, thanks
Agree, AAD User has limitations as a virtual table. I think I will do a video comparing all these options properly
Thank you very much, Lisa. I love how you simplify complex concepts.
I would appreciate a video tutorial for those of us who started with Excel due to a lack of coding or database literacy, and now manage spreadsheets that have grown to hundreds or even thousands of rows.
Imagine a scenario where tables such as Asset, Contact, Allocation, and many more are already filled with hundreds to thousands of related rows and columns. How would you approach migrating all of that data to Dataverse?
Lisa, I have watched a few of your videos and have learned from both. Thanks for sharing. I do have a question regarding Lookups. Does the Lookup always go back to the primary name column? I have attempted several times to do a Lookup into another table and a specific column in that table. However, the Lookup values keep showing the primary name column data.
You can configure the lookup view to add other columns to search for (and to show when you do the search in the lookup) but it will always be the primary name displayed in that lookup field in the end. You can add a quick view form to display other fields from that related table. I need to do a video on this, it comes up all the time! (Stay tuned)
Thanks for the response Lisa@@LisaCrosbie
th-cam.com/video/ICLGQ1cIo9M/w-d-xo.html - my full tutorial on this is up now
Hello Lisa, after the creation of an object in powerapps dataverse (and published), how could it be visible in my dynamics 365 sales aswell? :) Thanks for your videos, really helpfull
As long as you've made the changes in the same environment as where your Dynamics 365 Sales app is, all you need to do is go in and make changes to the Sales Hub app - add your new table / form / view etc to that app. I've got some examples here th-cam.com/video/CrlxnlxVjJM/w-d-xo.html
Hi Lisa
Just viewed your tutorial getting to where I want to get to regards to managing multiple contracts, generating reports, automating approaches and sharing the date in a secure way. I still need more understanding of how to do those things and adopt them to the business. Thank you enlightening content and are there any recommendations you may put up in managing data in multiple contracts in an automated way? My data is getting large and cumbersome in to manage in excel.
Model driven apps are definitely the right solution for this scenario. It’s hard for me to tell where you’re stuck - you’d want to have a data model where a company or person has multiple contracts associated with it (one to many from account to contract). I’d create views that show the contracts with expiry dates and other key information so you can sort and filter and then Power Automate will help with whatever you need for automation - find all contracts expiring next month and send an email or whatever you need to do.
@@LisaCrosbie Thank you Lisa will do that and hope will solve my issues in managing contracts. ...definitely would love to buy you coffee/cups of them!!
If you want to create a Many-To-Many Relationship in Dataverse without using the guided ootb experience, how does that work? More specifically, at a high level, how do you work with your intermediary "join" table? Do you need some type of process to write to it? Thanks
If you use the many to many OOTB then you won't have access to the join table. If you want access to the join table then you create 2 x one to many relationships to the join table in the middle.
Hello Lisa, which tool do you use for drawing an ER-diagram?
Visio
❤❤
What about many to many relationship? And how to configure "Append" and "Append To"?
So overall which is better for powerapps, sql or dataverse
I believe they are almost equal and it comes down to where you want the data and the cost of maintaining your own SQL databases. That said, I'm sure there are limitations in Dataverse that SQL can be worked around in SQL. They both require premium licensing (per app/per user). You may need Dataverse to do something like "Virtual Tables". Theoretically, you could license a single model driven app, create virtual tables with SharePoint to create free use canvas apps that could be integrated into the single premium model driven app.
If you're starting from scratch right now, and you want to use Power Platform, I would choose Dataverse, because it's natively built right into all the other tools - so you can add a model-driven app, a portal (Power Pages) and more right on top of it quickly and easily. Dataverse actually uses SQL underneath (along with other services), with a layer of abstraction to make it low code.
For a minute I thought we have a new ERD tool for Dataverse!!!!
whats your go to ERD tool?
Hahaha, no, this is just using Visio
Visio
Hi, anyone could tell me what software Lisa use for ERD please? Thanks!
I’ve used Microsoft Visio here
@@LisaCrosbie Thank you so much ！
The "AAD User" table is no longer there. I guess it has been replaced by the table called "Microsoft Entra ID"
It’s the same table renamed.
how do I recover a record after deletion in model driven app record?
Unfortunately you can't (without doing a full recover of the database). Deactivating is an option that allows you to "undo" and reactivate but delete is permanent. (Sorry!)
what is for if i have 110000(more than hundred thousand) rows and 365 columns, if not dataverse?
For that amount of data you need an enterprise database of some kind. Dataverse can handle that and more. Otherwise you could use SQL
@@LisaCrosbie for website I store data in SQL, I want another set of data stored for automation via power automate.
If you take this example and you want to create a gallery in a canvas app based off these tables, how do you do it? I mean the gallery is sourced from a view and you can’t create a view that includes elements from all those related tables
Say the view was based off the asset table. You could see the supplier but not who it is allocated to. If you did the view from the allocated table you could see the asset but not the supplier. This has baffled me because it leads to avoiding setting up related tables .
You can put related tables in a view as long as it's a lookup from the table you are creating the view on. So in this instance, the asset has a lookup to supplier. I've set up a 1:N relationship for asset to who it's allocated to, because I want to track multiple allocations. I could create another lookup, and when the asset is allocated, write that current person into that lookup -then you could create a view on asset that had both the supplier and currently allocated to.
@@LisaCrosbie I have stared at this for awhile trying to get it to sink in. Are you saying create a second relationship between asset and asset allocation going in the other direction? But then it wouldn't be a 1:N anymore since there could be multiple rows in the allocation table for each per asset
I always call it ERD. I mean is it wrong to use that term?
That’s exactly right. I’m just breaking it down for people not familiar with that term.
Which ERD was that?
Visio
I used Visio
@@LisaCrosbie Nice, thanks 👍
I am an Access/VBA guy, change is challenging
Change is challenging for all of us. 7 years ago I was working in book publishing!
w
Why the very lengthy introductions? and the frequent interruptions that distract focus.
If I cut to the chase, 80% of this video is not required.
I feel you are promoting Power App then "it is not working" and it is demoting it.