hi I'm will Johnson also known as power nerd insights I work as a senior Power Platform specialist for V 665 and I'd like to welcome you to this power nerd presentation welcome to uh this week's session today we're going to be looking at data verse versus SharePoint we're going to be looking at what's the difference we have a bit of a packed agenda today so first things first introduction that's where we are now but we're going to be covering quite a lot in terms of what the difference is between datae and and SharePoint so we're going to be covering licensing capacity constraints permissions models security features data modeling file storage any other major differences uh we're going to provide a bit of a summary at the end and as we did last week which we found really useful we're going to have a discuss discussion section at the end um where anybody can speak uh and we can have that kind of discussion piece to you know see what experiences you've all had um and obviously share that knowledge and and help each other out but first thing before we launch straight into the licensing Rules of Engagement I always do these um and I try to keep it as clear as possible so um during the course of the session if you have a question please do post it in the chat I'll try to get to them in terms of um when they're relevant but we will um kind of see when we go Rules of Engagement yes so uh like I said any questions pop them in the chat there is no no such thing as a stupid question I have a four-year-old daughter she tests that assumption pretty regularly um and we haven't found one yet so feel free to ask we're all here to help each other out and uh we're all looking for kind of the answers and the knowledge um that being said obviously we do have the discussion Q&A section at the end if you do want to speak up rather than put your poping your question in the chat that's your opportunity to do so I might cover your question later in so we'll get straight into it we've gone through our Rules of Engagement um very first thing data verse versus SharePoint we hear a lot of this a lot of talk about data verse versus SharePoint um in terms of uh what should we use when do what do we first learn um and a lot of question like that well actually well I'm hoping this session will um kind of maybe clear that up a bit so starting with the question that we always get um licensing so when we look at licensing it's what's what licensing is available what licing gives us access um and and kind of how do we how do we deal with that so let's start with the simple one SharePoint so access to SharePoint is generally gained through uh a Microsoft 365 plan and the licensing that that comes in for example E5 and E3 licenses they will immediately give access to SharePoint online okay so that's that's very simple it's one that we all tend to use day-to-day in terms of uh our work and and business um we'll have access the SharePoint line and actually it's one of the very basic things that comes with uh with a kind of an Enterprise license or a business license you even get it I believe with business basic when we get into Data verse things get a bit more tricky um purely because data verse is essentially got two kinds of license um we'll talk about the second one a bit later um but it's generally afforded as its own part of specific licensing and it's generally part of the Power Platform licensing access is generally gained through the purchase of either the per app or per user plans for licensing um her app is good if you have a a few users that use a single application um but once you get into the realm of multiple apps backed in data ver with users uh using a combination of these apps maybe one two three you know per user becomes much more best beneficial in terms of a cost benefit analysis um but obviously as you can see from the slide really dataverse is that you're purchasing a per app or per user plan um and we have those two different access access licenses uh I said obviously that the licensing for data ver does have another type of license um which actually leads us quite cleanly onto our next bit capacity constraints now you might be thinking why what what's why is capacity related to licensing well actually with data ver um Your Capacity is pulled across the tenant now what that means is um you will have a certain amount of capacity for your tenant for data ver storage and then you assign that capacity from a central pool into each environment as you can see there capacity pulled across tenant each environment is assigned a specific capacity however your total pool is impacted by the number of licenses you have so for instance um let's say and this there's there's no clean documentation on this but let's say you you start with uh one terabyte of data ver storage and you have 10 users who have a per user license each of those might give you 10 gabt extra on top of your base capacity um and at that point you're starting to look into oh okay so the more users I have who are licensed or the more apps I have that are licensed the more total pool and total capacity I have however this second type of license then comes in with data and this is why I call it capacity constraint these are called capacity add-ons so you have two choices essentially for purchasing additional capacity for data verse you have either you increase the number of users you have licensed using the per user or you increase the number of apps you have licensed using the per app license or you buy additional capacity add-ons um from there um now when we look at capacity constraints I have only been in two situations in four years two clients who have um basically come up to the dataverse capacity um these are both in situations where they were uh basically just using one per app license um they had the Baseline amount of storage one per app license and it did that was running a kind of a business critical application now obviously they were then having to purchase capacity add-ons to to increase that um but that's dat of us obviously in terms of the cost cost amount in terms of capacity constraints um there is another kind of capacity with datae that makes it a little bit more complex again um but we'll actually discuss that a bit later when we look at covering the kind of file storage element of data verse versus SharePoint that being said sharepoint's capacity because strain so a little bit easier to understand so when you first get access to SharePoint you get a base storage amount of one terabyte you get an additional 10 GB per uh kind of license now it says on their A3 E5 license this is actually outdated so you actually get an additional 10 GB per license apart from any licenses that are F1 F2 or F3 um so let's say you have again 10 users you're looking at one who all have E3 licenses you're looking at 1.1 terabytes worth of storage now there are some other capacity constraints that come with it so you are allowed up to 25 terabytes of storage maximum per site collection um if you manage to fill out a site collection with 25 terabytes of data more than likely it's videos um you're going to have to spin up another site collection as long as you're not breaching your your kind of overall storage to manage the anything above 25 terabytes um there are again capacity add-ons available for SharePoint that can increase your capacity and the amount that you're allowed to create or store um so yeah sharepoint's very simple in terms of capacity constraints um in terms of working how how much capacity you have and in terms of outlining uh kind of how much you're allowed to store in each site collection obviously data verse is a bit more flexible in that we have a limit on how much we're able to store in each environment but that's decided you know at the environment kind of settings level in terms of how much are you going to give it out of your total pool um so moving on permissions model the big one uh there is a significant difference between SharePoint and dataverse when it comes to permissions model so we'll start with what a lot of us know SharePoint right so SharePoint runs an inherited permissions model this essentially means that you set the permissions at a site collection level um and then items that beneath that like lists document libraries and things at lower levels inherit the unique permissions for that parent level site you can obviously break that inherited permissions um but you have to do that on a Case by case basis when we look at site Library list and item level permissions uh they're typically controlled via SharePoint groups or Microsoft 365 groups uh this normally leads to a site being permissioned and this deciding the permissions model for all items within the site okay that means if a user want if you want a user to only have access to specific records you then have to break the inheritor permissions for each item you want to individually permission and this can become quite tiresome and obviously we do have access to power automate and SharePoint rest API which can do that work for us but then from a security perspective it becomes very difficult to track who has access to what unless you have some quite heavy Tooling in terms of tracking that which items have broken inherited permissions and other bits however one of thing that uh SharePoint does have quite well out of the box is it has multiple predefined uh permission levels ranging from full control which essentially if you sign full control permission level to a user on a SharePoint site they have the ability to edit the SharePoint site create new document libraries create new lists um and do everything they need to do all the way down to view only and they're really easy for someone who is potentially not an administrator to just assign them to a specific group um SharePoint permissioning can be can be relatively simple in terms of that inherited permissions you know what you're dealing with you're setting at the top level and it's going to Cascade down unless you decide to break that inheritance okay but one thing it doesn't do well is uh kind of Ro level security and field level security okay so when we look at um SharePoint one of the things we can't really do in SharePoint is if we had an item in a list and we say only wanted the uh the let's say Finance team to access column one two and three and the uh audit team to access columns four five and six on a specific item in the list it's it's pretty much impossible to do that in terms of permission the item in that way okay and this is where dataverse really excels so dataverse operat a rule-based security model uh this allows for quite complex security when we look at things like business units teams security roles and and hierarchy based permissions now what that means is I can decide that hey for this specific item in a in a list I want to be able to um make it so that this specific team have uh the permission to view it or delete it um but their managers when we look at hierarchy have permission to view it edit it delete it uh reshare it and and do a number of other things if you've done work with SEC with security rules in datae you you know that they're very very extensive um you have the capability to really quite granly Define what a user is able to do dependent on what role they fall in within the business or what position they fall within the business or you know whether they have staff underneath them in terms of managerial hierarchy there's there's quite a lot you can do with data verse and that role based security model is very very robust and very uh kind of very flexible the one thing uh dataverse permissions model does really well is kind of Ro level security this includes the ability to not only permission just an individual item like we can do with SharePoint and lists but actually The Columns within that item um that's like I said something you just can't do with SharePoint so in the case where we wanted Finance to see columns one two and three and order to see columns four five and six data verse has the capability to do that by marking a specific specific column as secure and then assigning a security rule to that column to identify hey anybody that has this security rule they need to be able to do XYZ with this column they need to be able to view it they need to be able to edit it they need to be able to uh you know delete data from it that kind of piece right so again that record level and and kind of not just row level but column level security that field security is really really good now just like with SharePoint uh we have out of the box some pre predefined security rules however these are basic security rules applied to each environment um so these are things like the system administrator the environment maker role the basic user role these are basic security roles that are applied to each environment but what we've got to realize is when we're Building Solutions with dataverse a lot of the time we're creating custom tables and whenever a custom table is created this will only be accessible to specific rules if you want users to be able to access the custom table you need to create your own security rules to be able to Define that and actually when we look at uh kind of data verse versus SharePoint in terms of ease of permissioning at least when it comes to creating new items as long as you've defined it at the site level it's not too much of an issue if user comes into a document library for the first time that's only just been created it's already got the permissions from the site that's great if we create a custom table in data verse and we don't Define that security role or we don't add that somewhere that user is not going to be able to access access that data uh so we need to be able to do that but that is the permissions model piece so security features because this is one thing that I get asked every single time I come to a new client and they go you know what what what's data what's data ver got that SharePoint doesn't and it's always the security team that are going to ask the questions to say yes no so both SharePoint and datae use Microsoft entry id/ aure active directory as it used to be known for authentication now that means that out of the box both of these are enabled to use conditional access policies which prevent things like impossible travel um someone let's say I log in in the UK and then all of a sudden my same account tries to log in you know in Australia 30 seconds later that's an impossible travel conditional access policy it can stop that which means that we can stop the kind of access to SharePoint data verse data that we potentially may not want um things like multiactor authentication and the other features that ENT ID enabled in terms of security features are both applicable to both SharePoint and database and that's always something to wor worth noting because from an aspect of obviously security we want those to be applicable to our data but well there's a few things that SharePoint does the data verse doesn't and actually the the security features around both of these are are very different what we've got to remember is that SharePoint was designed for file storage a lot of the time um it's designed for large files unstructured data um rather than structured data so the way to secure that is slightly different one of the things that SharePoint has is something called the information Rights Management System now the information Rights Management can be enabled in the admin Center and can then be applied to a document library or List through the document Library settings what the information Rights Management kind of security feature does is it allows ows you to provide protection to downloaded documents by applying things like encryption and usage restrictions to that downloaded document it means that for instance if you were to download let's say I don't know an Excel file that contained all of the permit all of the permissions uh locations and personal information for the company actually if we had information Rights Management on there we could prevent you from re-sharing that document attaching it to a a um Outlook uh email to send it somewhere else or we can prevent you from kind of being able to actually use it through the encryption piece the other thing that SharePoint does is version history uh while this is not technically a security feature I've put it in here uh it could be very helpful when roll backs are required due to um you know data being deleted or removed or maliciously kind of um kind of tampered with it's worth noting that the version history in lists specifically in SharePoint is actually really good in terms of showing content the content in terms of what the old version of the item was and what it has changed to but that's not the case for documents in a document Library it doesn't show that level of content for you to be able to see that you have to restore it to the old version and then go in and look at it now there's two on here um these are relatively less known I find um sensitivity labels and potential labels these are something that while not inherently inside of SharePoint is is designed to work with SharePoint just permanently uh sensitive level sensitivity labels and retention labels can both be applied via Microsoft purview um used to be this security Center I believe um but they both do slightly different things so sensitivity labels are essentially a way for you to label items uh dependent on how sensitive the content is uh when a sensitivity label is applied to an item you can configure that kind of sensitive sensitivity label policy to apply things like encryption uh restrict actions inside of the document like copying and pasting or printing or even apply watermarks to Word documents um if that sensitivity was applied it's really useful from a uh a security perspective because at least then you've got the ability to restrict actions on stuff that might be sensitive retention labels do something slightly different again not entirely a um a security feature but actually way of stopping data that is required to be retained by law in some cases or in most cases where this is used um from being deleted so again these are enabled through Microsoft purview um they help you retain data and prevent important data from being deleted especially helpful when dealing with public records so um within the UK we have a public record system that means that certain types of Records have to be retained for up to seven years or or or even longer in some cases um retention labels can be used to prevent people from deleting that item when we need to retain it uh with retention labels there's a few things you can do um you need to specify when you set up a retention label the period that you want the document to be retained for in months days years uh when the start of that retention period is I when the item was created is that when our retention period starts when was it when the item was last modified um if you select that option every time the document's modified the count of resets and then after that you choose what happens after the retention period now these can be things like hey we want you to automatically delete that document or hey what we want to happen is we want to uh essentially have a a compliance per person within the compliance team be notified um and then they have to review the document now that is doable via power automate but SharePoint has this built in um so it's worthwhile using so there's some of the secur features that SharePoint have and you'll notice that a lot of it is centered around not the list items but the documentation than the document libraries okay and that's purely because like I said SharePoint was a lot of the time designed for that that unstructured that file storage piece now in terms of data verse we have this does massively focus on the structured data so we're looking at table structures where we've got rows and columns right right so what has data verse got in terms of its security features well field level security we've kind of already touched on that so like I said certain columns can be marked as secure further restricting users's ability to view or edit a field in a rule based on their security rule uh it offers that kind of more granular control the security of a specific record than SharePoint would allow within a document library or List um next one down this is something that is actually baked within the kind of Power Platform as a whole but it's really useful for data verse data loss prevention policies now these data loss prevention policies allow administrators of an environment to decide what connectors are available to users within a specific environment these policies can also change how da data in data verse can be shared with external connectors and and ensure that sensitive data isn't compromised by putting it through let's say a third party connector that maybe isn't secure the other thing we have in there business rules again another one that while it isn't technically a security feature it does help maintain data integrity and can Aid in data compliance right business rules enable us as users to and makers to define a rule that sets specific Fields as business required and restricts restricts either the update of a record or the creation of a record um if certain conditions aren't met now in terms of an element of compliance in a secur feature I find that's really really useful because when we look at how what data might be putting in incomplete data can be fed into feed reporting um that may lead to poor business decisions right so those business rules are a barrier to that if configured correctly and so that's why I put it in the security features area so the next one down this is this is one of my personal favorites auditing um for anybody that's seen the auditing in dataverse you'll know that it is extensive um it can track when an it is accessed created updated deleted it tells you what the changes were when they were done what the old version was what the new version is and who did it and it retains that for a long time um now the ording capability is huge because it allows admins to see who accesses who changed what data and when they did it and it means that we can then roll it back it's relatively similar to the version history but a lot more detailed um it can even capture things like a plugin interacting with a specific record rather than a user which is really really useful and really really powerful but those are the security features that kind of sit within SharePoint datae the difference obviously overall between the two as as we're seeing with this is now we're starting to divert away from um are we looking at items of data well actually SharePoint seems to configure its security for um documents rather than SharePoint list items uh data verse seems to do it for our structured data for you know records and rows um one of the other bonuses I've got it on here is a bit bonus Solutions nice big capital letters for those of you who have um kind of worked with me and I I've I've helped teach in certain ways in terms of um how you start a Power Platform project you'll know that I I always say to you once you start building build it in a solution uh data verse actually enables that solution the reason I've got it in here as a security feature is because Solutions allow you to uh conduct proper application life cycle management okay it also means that you can use managed Solutions so that you can deploy uh your Power Platform Solutions and not have them be uh changed or tampered with um without kind of some level of effort that's why they're in here as a security feature because of that that bit around the manage Solutions it makes life a lot easier and it does protect you from unwanted changes to your Solutions okay moving on data modeling uh this is this is always a fun topic I do love a bit of data modeling um so what's the difference between uh SharePoint and dataverse um now when we look at it I've just said on the previous side that SharePoint wasn't designed for uh Rel full full kind of relational data okay in terms of it seems to be more geared towards document storage um and manipulation than it does towards dat modeling we starting to see that Divergence a bit more so what's the difference well SharePoint is list based databasing databasing whereas data verse is relational database and what that means is with SharePoint um like I said SharePoint was never designed to act as a full relational database we don't have any Rich relationships type Rich relationship types in there such as you know uh one to many many to one and are many to any we can kind of make it do it in terms of we can add a lookup column um or we can add a foreign key column take the ID from another record in there but it's not there inherently as an option okay it's something we have to configure and set up in such a way to get it to do that um it can be used for light structured data that's going to forelow the the kind of limit um so when I call when I call out the limit it's the the 5,000 records you might have seen this or 2,000 records depending on how um Power Platform is set up but you'll end up with pesky delegation errors if you go over that amount and and SharePoint could be quite tricky for that but you know your ideal use case for SharePoint will be a document Centric or simple data scenario such as things like task lists uh basic trackers um that isn't to say just being fair here don't try and push the limits of what SharePoint can do um but speaking from recent experience with a client uh once your SharePoint structure gets to a certain level of data you start to sign experience significant performance degradation okay um and we've seen it with a with a client recently where basically people would just unable to load the app at all it was just WID screening um they had a significant amount of date and we had to basically account for archiving a large amount of that date to make it not available to them anymore when it looks when we look at data data verse mentioned obviously about that is relational databasing rather than list based now what that means is dataverse allows us to have those Rich relationship types that one to many many to many kind of relationships we have a full Suite of tools available to us in terms of relational databasing and that's purely because data versus actually backed by Azure SQL okay um it's a very nice skin over Azure SQL that allows us to do a few other features uh it's a true relational database because we can Define cascading behaviors to enforce relationships so that's the the reason why SharePoint isn't a relational database it's because if I were to create a uh a lookup column in in data in SharePoint and I delete the record that it's related to that's going to break that relationship whereas in data verse we can Define the uh the behavior for deletion of a child item or related item to stop it we have to stop it from deleting that item okay that's what defines data verse as a relational database we can Define that cascading behavior in terms of items that are deleted and how the relationship between the two records uh kind of works now we look at obviously SharePoint I mentioned about the The Limited relational um capabilities like I said you can create lookup columns it does look like a a relational database you can create that lookup column to reference date in another list but there's no enforcement of that relationship you can delete a referenc item without restriction and this will a lot of the times just break the look up okay um there are a few options now starting to sneak in that I'm seeing on lookup columns where it does restrict delete and a few others but that doesn't always work it's temperamental but the other thing kind of major difference uh between SharePoint and dataverse in terms of data modeling uh the data types so SharePoint holds some basic data types where it's things like single line of text mul line text um and a few others uh what's missing is things like uh calculated or rollup Fields right these are columns that do specific things within data verse that dataverse has um that SharePoint just does does not have uh while you can create calculated Fields within SharePoint they can be very temperamental I've got a good example of this we have a calculated hyperlink field in uh SharePoint for a client at the moment um and it's based upon the value in another column um if that column is not present in the view the calculated column completely completely breaks um we don't know why it shouldn't but it's there and and because of those issues because it's not again designed to essentially do that uh We've we've we we call them as basic data types but data verse has Rich data types but what does that mean well Rich data types are things like obviously we've got our text our number date and time currencies option sets where we look at single or multi select we've got lookups we've also got calculated Fields rollup fields and a load of others and the reason why they're called Rich data types is because they follow the kind of data typing for SQL right again we're going back to the fact that dataverse is just SQL on the back end it's a your SQL on the back end with a good skin over it that makes it easy to use right it's got a few other features in there um in terms of kind of business logic which is actually the the next main difference around the data modeling okay a lot of people don't think of business logic as part of the data modeling but it is it's a really important part so when we look at business logic SharePoint uh you can Implement business logic in SharePoint but it has to be through Power automate um it's not inherent to SharePoint it's an addition that you then create and modify yourself it's not out of the box dataverse has business logic out of the box these are things like our business rules our our um business process flows uh things like calculated and and rollup fields are technically classed as B business logic when we look at roll up fields that look at an activity table and go hey what's the duration of every single activity that's happened against this par parent record that's a rollup field that we can use to Cal we can use to calculate that and that's a bit of business logic all these bits of business logic are actually at the database level they're not something additional that you build on top like they are in SharePoint and again it's another thing to consider when we look at data modeling that's a major difference between SharePoint dat verse is that data logic is inherently there in dataverse it's not in SharePoint okay so file storage now this is a simple one and this is one where uh actually SharePoint wins out uh SharePoint was designed to work as file storage and while you can store files in dataverse using things like the file column or attachments on notes you end up consuming a very specific kind of capacity within dataverse called file capacity uh I mentioned that there was another type of capacity in in the capacity constraints bit further up this is it uh file capacity in data verse is is treated separately to your your main capacity and it can fill up very quick and it's expensive and so when it comes to this category when we look at file storage there's only one thing I really need to say about that is if your solution is going to deal with large files be it video files or you know uh Mass amounts of data in an Excel spreadsheet um or you know some some really really large uh kind of resolution photographs use SharePoint to manage that those files don't use data verse um like we see we can see on the screen SharePoint can store and manage large files on data verse files could be stored in file column or as attachments on the Note um one thing to note the file column um if I remember correctly the last time I used it was about two months ago um actually stores the file as base 64 um and actually has a top end character limit uh which you then have to adjust if your file doesn't upload correctly um so that's another thing to to kind of note is that there are some very specific settings for that file file column um but again when I'm looking at a solution personally if I'm looking at storing files I'm doing it in SharePoint Okay so other I had to add this in here because I I nag on about it consistently to to um kind of my colleagues um and anybody I can get to to listen uh application life cycle management that's what Alm stands for SharePoint when we are building solutions that are backed in SharePoint it becomes very difficult to manage application life cycle management unless you use environment variables to manage references to your SharePoint Point lists and in that case what you end up having to do is to create three separate SharePoint sites and then three separate copies of your lists if you're running let's say Dev test and production and then changing that as you go through dataverse is brilliant for application life cycle management because the tables that you create are solution aware they are contained within the solution that you create your uh kind of uh objects in that's your your power apps your your your power autate flows all of that and it means that rather than having to recreate SharePoint sides change environment variable references and all that uh what you end up having to do is just import the solution data verse has that benefit as well and alongside that if we look at uh kind of integration into Azure devops pipelines and Power Platform pipelines dataverse becomes a lot easier to use when we look at that kind of continuous uh Improvement continuous development pipelines piece um that's why it's here so in summary both technologies have their merits I I say this very clearly I started my Power Platform Journey with SharePoint I made it do some wonderful things it was really really good but when you get to dealing with Enterprise level data large amount of Records large file storages uh you kind of have to learn to use data verse again both of them have their limit itations as well we've said that datae can't deal with large files where SharePoint can so in my experience you're going to end up using both of these Technologies to create Solutions so it's good to be comfortable with both be it SharePoint lists or document libraries or SharePoint as a whole um and be comfortable with data verse and how to use it but overall if I'm building a relational database and I have the licensing I'm going to use data verse because it's designed for relational databasing however conversely if I have to deal with large files in my solution I'm going to use SharePoint to manage those because it's designed for that so overall my my point on this is it's great to know the differences between the two but you're going to be using both if you're going to be working with Power Platform at one point or another you're going to run into document Centric solutions that you SharePoint consistently or you're going to run into you know a ticketing system that only needs to use database so be comfortable with both and they are both good thank you for listening I hope you find it useful and got to learn something new and I'll see you in the next one