Linking to Dataverse - I have questions (1 Viewer)

[tmaccabe]

I've learned enough about building and querying SharePoint lists to be dangerous. It's all about the cache and its size, and whether you are updating many rows. It's also about understanding the impacts of changing list settings and of dropping new front-ends while users are connected. The bigger the list(s), the more this all matters. If your lists are too big, and you can't get Azure SQL, is Dataverse the answer?

I've seen posts that say seemingly conflicting things: "Dataverse is a relational database just like Microsoft SQL", "It's impossible to build complex views that traverse multiple tables", "Use Dataverse views when possible. A view with the required join or filter criteria helps reduce the overhead of using an entire table."

I'm still not clear if the SharePoint cache (or something similar) is used when linking to Dataverse.

If caching, why would I connect to a Dataverse view? And, is there an off-line mode?

If not caching, is the connector an ODBC connector and can you thereby run pass-through queries? (Note, there is a 3rd party ODBC connector, so this makes me wonder if they beat MS to market but is now OBE?)

And finally, regarding Dataverse custom tables versus pre-defined tables, are custom tables preferred when linking? Why?

 

[CJ_London]

Not sure which dataverse you are talking about. You don't link to an access dataverse (which is a subset of sql azure), you synchronise with it automatically. It basically mirrors the contents of your access tables - or at least the ones you want to put on dataverse. Because it is a subset of sql azure, certain datatypes were not available initially - this was an issue being resolved, not sure if it has yet or not.

 

[tmaccabe]

Not sure which dataverse you are talking about. You don't link to an access dataverse (which is a subset of sql azure), you synchronise with it automatically. It basically mirrors the contents of your access tables - or at least the ones you want to put on dataverse. Because it is a subset of sql azure, certain datatypes were not available initially - this was an issue being resolved, not sure if it has yet or not.I am asking about linked tables, with Dataverse tables as the source.

I'm talking about linked tables with Dataverse tables as the source.

 

[tmaccabe]

Just tested, in the Access Query Designer, if you create a query of a Dataverse table, press Pass-Through, and then Run, it pops-up an ODBC data source selector. This implies the Microsoft connector is not ODBC. Bummer. Isn’t that just like MS? Advertise an ODBC layer, and then not make an Access connector for it.

 

[GPGeorge]

Not sure which dataverse you are talking about. You don't link to an access dataverse (which is a subset of sql azure), you synchronise with it automatically. It basically mirrors the contents of your access tables - or at least the ones you want to put on dataverse. Because it is a subset of sql azure, certain datatypes were not available initially - this was an issue being resolved, not sure if it has yet or not.

Nope. Dataverse is not a subset of SQL Azure. It's part of Power Platform in your MS 365 account.

It's a whole 'nother thing. It's way too complex to attempt an explanation here; this link gives you an introduction and general overview.

Behind the scenes there are elements of Microsoft Azure involved, and ultimately, the Dataverse tables are, I believe, stored in SQL Azure databases. I understand the same is true of SharePoint lists.

If I had to choose, my order of preference would be:

1. SQL Azure
2. Hosted SQL Server
3. Dataverse
4. Most others
5. SharePoint
6. Excel (and only if you hold my family hostage)

If you have a version of MS 365 which supports it, the connector to Dataverse is available"

In my limited experience, Dataverse is slower than other datasources for a comparable data size.

The tables are laden with fields representing meta data needed to manage them in the online environment, which makes them less than intuiive.

On the bright side, they DO support off-line synching, at least when used in a PowerApps application. I have never tried that with Dataverse tables linked to an accdb, though, so I don't know one way or the other.

 

[CJ_London]

Dataverse is not a subset of SQL Azure.

I stand corrected

 

[GPGeorge]

I've learned enough about building and querying SharePoint lists to be dangerous. It's all about the cache and its size, and whether you are updating many rows. It's also about understanding the impacts of changing list settings and of dropping new front-ends while users are connected. The bigger the list(s), the more this all matters. If your lists are too big, and you can't get Azure SQL, is Dataverse the answer?

I've seen posts that say seemingly conflicting things: "Dataverse is a relational database just like Microsoft SQL", "It's impossible to build complex views that traverse multiple tables", "Use Dataverse views when possible. A view with the required join or filter criteria helps reduce the overhead of using an entire table."

I'm still not clear if the SharePoint cache (or something similar) is used when linking to Dataverse.

If caching, why would I connect to a Dataverse view? And, is there an off-line mode?

If not caching, is the connector an ODBC connector and can you thereby run pass-through queries? (Note, there is a 3rd party ODBC connector, so this makes me wonder if they beat MS to market but is now OBE?)

And finally, regarding Dataverse custom tables versus pre-defined tables, are custom tables preferred when linking? Why?

Okay, although I probably have spent more time looking at Dataverse tables than most Access developers outside large corporate environments (and possibly there as well), I do not consider myself an expert.

Dataverse is "relational"; just not quite the same as a true relational database like SQL Server. To be honest, I suspect that somewhere in the guts, it is built on SQL Azure and relationally designed. My impression, however, is that the parts exposed to "Citizen Developers" are dumbed down, in the manner of Lookup fields and Multi-Value fields in our beloved Access. If you don't want to learn how to work with a relational database, and you are willing to turn over a lot of the internals to Dataverse, it has ways to facilitate tables. The good news is that it can guide you, the bad news is that the path is narrow and, well, guided, if that makes sense.

I don't know how caching works in either SharePoint or Dataverse, but it can be enabled, at least for PowerApps applications. I've tested it and found it pretty solid on minor amounts of data.

Dataverse tables are things like "Customer", or "Products" which are found--in one form or another--in many different databases. So, it makes sense to have a "template-like" table already available. But see above, you're on the narrow path offered.

So, you could use a combination of their tables and your own tables to build out a usable solution. In all cases, though, using them with Access is a matter of linking to them, regardless of which type you want to use.

 

[GPGeorge]

Oops. I missed the question about views. I "think" you can create views, but my experience is too limited to comment on it otherwise.

My only relevant experience was getting into a mess one time because a field I wanted to replace in a DV table had been included in one of a number pre-built views I didn't know existed. I had to delete the entire DV view to free up that field so I could swap it out. The experience didn't inspire a lot of desire to try DV views again. That said, I'm sure in the hands of a more knowledgeable developer, they can be useful.

 

[tmaccabe]

Thanks George. For all the talk of scalability, a cached set of tables will just get slower and slower the bigger they get. So is DV scalable for Access, not so much. Seeing that CData ODBC driver sure gave me hope that MS would provide the same. I’m running lists that I expect will grow to 100,000 rows. I guess they don’t want Access to be that good without a way to get more money from you.

 

[tmaccabe]

Been doing some testing with an Access table of 60k rows. It took roughly about 45 min (each) to upload this table to both a List and a DV4T table. After an initial 2-3 minutes to cache, select queries on these were equally responsive. Copying one text column to another (for all rows) required 90min for DV4T and 70min for the SharePoint List. So, ballpark, it seems updates to a List are about 20% faster than a DV table. @GPGeorge, this seems to confirm your advice to stick with Lists over DV tables (if Access is you're front-end and Power Platform tools are not a consideration).

 

[GPGeorge]

Thanks for investing time in testing. I appreciate the feedback. I'm somewhat relieved that you confirmed my assumptions in this case.

If it were up to me, I'd go straight to SQL Azure (~$5 US/month for the lowest service level) or a hosted SQL Server database ($ depending on hosting fees), and keep cautious eye on developments with SP and DV.

Unless offline caching is critical to the success of an application, the advantage lies in more traditional relational databases, IMO.

 

[tmaccabe]

Just a small Hobbit in a big world, learning to work with what I have. I'm going to have to learn Power Apps.

 

[Minty]

Another + 1 vote for Azure SQL , we use it extensively with Access front ends.

 

[tmaccabe]

Another + 1 vote for Azure SQL , we use it extensively with Access front ends.

Can you explain why/how Access queries are faster connected to Azure SQL that they are with a SharePoint List or DV table? It seems that both a List and a DV table are cached solutions, so I'm sure that's part of it. But I don't know why.

 

[Minty]

I'll caveat this answer first - they possibly aren't always, but my limited exposure to Sharepoint lists indicated they are painfully slow by comparison.

I suspect this is primarily to do with the inherent structure of SQL tables vs Lists, and the power of the ODBC drivers that over the years have been honed to a very high level of performance and efficiency with SQL data sources.

As you say the way things are cached is probably a factor, but SQL deals with things like record locks incredibly efficiently, which almost certainly makes things much more responsive. Add in that SQL is 100% data orientated, whereas SharePoint is really designed as a multi-document store, and you are really comparing apples with banana's (IMHO)