My Problem With Dataverse 

Great Video Paul, just starting to use Dataverse and I'm running into all of these problems. I feel I need to come up with a pro's and con's sheet ahead of our PA developers migrating from SP lists.

The account and contact addresses are actually stored in the address table. if you look in the address table you will see the addresses for the customer. It's a convenience to show the first two addresses on a table form. there is an address sequence field in address, and the first two addresses show up on the account or contact record.

That is shocking that you can only do a join across two records. And the lack of SQL querying/updating is going to make the support of systems built in this an absolute nightmare if the data model is in any way complex (and frankly even if it isn't that complex).

Hi Paul, I know your feelings; these are the same when I entered the Dataverse world a long time ago. Dataverse is originated from model-driven apps. Learning about model-driven apps & Dynamics 365 apps will give you much more info why things are like this. In model-driven apps you will be given much more out-of-the box functionality then canvas apps, which will out-ways the negative points you mentioned. You need to do some of the work that normally a RDBMS does, which sucks, but it will grow on you. Account has two addresses (and Contact even has three), which are being keept in sync with the Address entity. Thats why you see all those address fields because they are the same as in the Address entity. In the Address entity you can add more addresses, if really need to. This solution is because historically in model-driven apps, editing related records was not that user friendly (it's improved now-a-days). Most organization agree which type of address is stored in Address 1 and which in Address 2. For example, Delivery address is always Address 1 and Post Address is Address 2. This makes searching easier. If you want to search in both addresses, you can also search the Address entity. Looking at all the phone numbers point: The Address entity has three phone numbers, which are specific for that address. Those Address phone numbers also exist on Account, to keep al the address fields the same. Often these are not used, but for companies in logistics, they can be handy. Views are different from Database Views! Microsoft should rename these to Lists or List Views, because they are connected to the entity and are primarily used for viewing in Lists. I agree that it is annoying you can only query one step further. I highly requested feature by Dynamics 365 consultants. Multi-valued fields are added recent because consultants really needed a solution for this much requested feature. Too bad that Microsoft created a not so great solution (not proper database-design, Power BI problems, etc.), and the general advice is to only use them when you have no other option. Dataverse indeed hides the many-2-many relationship implementation. I can understand your worries about this, but I have to say that this feature is one I think is a desirable choice by Dataverse. I practice you almost don't need to query this table. In contrast I have seen enough developers struggle with this in custom apps connecting to a RDBMS. Many-2-many relationship is a hard concept to grasp for a lot of people. If you really want to, you can still create your own many-2-many entity. Dataverse is indeed creating multiple columns, for like the Customer datatype, which can connect to Account or a Contact. This used to be explicitly (you can still do this), but connection to Account or Contact is used so much in Dynamics 365, that they made it implicit. I agree that experts should do data modeling if it concerns important business data. But sometimes data starts as a small side project by one person or a department: in this stage correct data modeling is not the most important thing. When it evolves it should be taken over by IT and be proper modeled.

Glad to see this. I learned data modelling back in the early nineties and I've always put that knowledge into any database I've created. It ensures that your data is right and also provides the flexibility at the database design level to achieve anything you want to. I'm brand new to Davaverse and I still have loads of unanswered questions. My thoughts are that Microsoft has produced this interface in order to reduce a lot of the work involved when building the data model e.g. lookup table in many to many relationships. My employer is one of the largest broadband providers in the UK and they are encouraging everyone to develop their own apps for local use at a department level. We've historically used Sharepoint lists and, for most applications, they are OK but with the Power platform being encouraged, I can see Dataverse becoming used more and more, particularly with it being available to anyone with a Teams account. For most of these "little" apps with only a few tables, the data-modelling problems may not be too problematic. For very large development projects however, Iit is vital to get it right from the start In the example you gave where an account table that is related to a line items table, wouldn't the account primary key be a foreign key in the line items table so it would be part of the child record? Perhaps I've misunderstood what you were saying. I look forward to any future videos you post in relation to Dataverse.

E.F. Codd and Bill Inmon are likely smiling on you - thank you for the pragmatic sense and risk assessments. May Microsoft product roadmap and architecture executives be listening.

Hi Paul, I believe you can see all the related fields in the related tab - next to 'invoice details' - I think that gives you a list of all the lookups available.

Paul, "they" will not listen.

Paul, thank you for taking the time to share your thoughts. I've be doing extensive research on a tool that will fill the needs for our small company to replace a MS Access database and numerous Excel spreadsheets containing data and reports that have been created as our company has been growing. Immediately after watching your video, it had me second guessing my choice on Dataverse. Thanks for scaring the hell out of me that I chose the wrong product for my company! ;). I continued to do much more research. There are not a whole lot of people on RU-vid that deeply understand the Dataverse product, not to mention the other Microsoft tools, that cannot be overlooked. We cannot take just one product in isolation in the current state of software and data management. Smaller, more nimble programs are being created to handle data online. (In the cloud for the new terminology). On that note, have you looked into Microsoft's Power Bi and the depth of querying and visualizations of this tool. It is also intended to be a presentation tool that is meant to be put into apps, like Dataverse, as dashboards, etc., for the specific situations that you are stating limits Dataverse. I believe Dataverse fully supports data normalization. I agree some of the standard tables are not set up to most effectively control this, but setting up your own tables with one-to-many relationships, as well as creating your own tables for many-to-many relationships is the correct way to go. I also agree that it looks as if you cannot easily access the self created many-to-many tables that are created behind the scenes when using lookups that cause a many-to-many relationship directly; hence the reason you create the many-to-many tables yourself and stick to only using the one to many fields in all other tables, views and forms. Honestly, why would people be looking up which products they've sold in a particular city, using a "data input system"? That is a data reporting system, such as Power Bi, which uses Power Query to dive deep into the relationship paths. You may be thinking, which is understandable, that "views" should be the same as queries in database products, but they are not. (Future improvement? Maybe some, but I doubt it, as I will state in business decisions below) Please keep in mind, this is not what we OG programmers are used to with a database....but Dataverse is not a database as we've always known it, with powerful querying features, like SQL and most other databases. Microsoft makes a lot of effort to not present it as a database. My opinion is that Dataverse is a very nice data input system and is easy to create and use, with very little coding knowledge required. It is a database from the view that it does, in fact, securely store your data and can definitely ensure data normalization, but it just doesn't have the powerful querying options we are used to in a database. Why did Microsoft leave the powerful querying tool out of Dataverse, that is usually in a database, as we've always known it? I believe for two reasons. One-a business model to sell additional querying and reporting tools seperately. Two- to be able to have a team dedicated to advancing a tool, like Power Bi, to handle all kinds of data sources and put the focus on it's ability to do some, ever improving, data manipulation and visualizations, without bloating the actual data input side of the business processes. Good business, genius, or evil genius, you decide. :) Thanks again for scaring me, but I have worked through that and am confident Dataverse, and the suite of Microsoft products, is the way to go for our small company as we continue to grow for a long time. Hope this helps!

Thanks Paul, I would love to see you make a video about the Normalization, if you haven't made one.

Tks for the video Paul! Would you reccomend build Canvas App against SQL Data Base in order to avoid the problems in Dataverse related by you? If yes, it would be nice to us having you making a video giving some instructions

Good video Paul and a very important topic! So what you're saying is that it's very difficult to enforce Referential integrity. True? We're a year down the road now. Did you do any research on this later. I was planning on starting with dataverse.... but... So that's not such a good plan. But then what is an affordable data model for power apps? surely deploying Sharepoint or Excel is not a good plan.

Export the data to ADLSGen2 or SQL Server and query the data there?

make sense.. that Dataverse is not support normalization..

Hi Paul! It's been a year since this video and I would love to connect with you to see if there has been any evolution of these thoughts, and/or if we can chat and document feedback. Please reach out to me on LinkedIn if possible. Thank you again for your transparency.