8

We have a large database of 20,000+ items on a local database that we need to get into Sharepoint Online 2013 in O365 to use as metadata for various documents in multiple locations. New entries and changes need to be synced into the choice list. Can anyone offer some insight on the best way to accomplish this?

Current Options:

1) BCS External Content Type: but how to get past the 2000 item throttle limit?

2) Managed Metadata: manual updates only? This offers the best functionality but seems like a nightmare to administer.

3) 3rd party tool? Still subject to 5000 throttle limit?

4) Use a JQuery data table and a custom edit form to "select" from an xml extract of the database and inject it into a text field (however this would not apply any "updates" if data changes).

I am at my wits end in seeing how this might be possible in O365.

6
+50

I had a similar issue this morning, with an external dataset with around 100,000 records. The way I worked around it was to create a SQL stored procedure to do the Read List operation, rather than querying the data directly. The Read Item operation can still be linked directly to the list, rather than requiring a second stored procedure.

On the Read List operation, you'll need to specify a filter like this one:

Filter specification

To simplify searching, my procedure has only one parameter, but the query it runs searches for the parameter in five different columns. This gives the user maximum flexibility for their search, without complicating the input.

Also, note the Default Value on the filter. You need to have something in there, and an asterisk is just fine.

The output for your procedure should output all the columns that your items contain. You also need to mark columns as Identifiers on the Return Parameter page as appropriate. In my case, I had three identifier columns. (If you're a SQL developer, think of this as the primary key.)

For your Read Item operation, you'll need to link up the identifier columns. As long as your Read List stored procedure and Read Item source object (a view in my case, but a table would work fine too) outputs the same columns and has the same identifiers, you should be good to go.

  • A couple quick questions (im not a database guy so bear with me): Does this concept work on a non-SQL DB? We are using an oracle DB that we are planning to expose using an OData web service and Visual Studio to create the External Content Type in O365. Is my lamens understanding of this accurate: The Read List stored procedure lives with the database. The Read Item operation lives with the ECT and calls the stored procedure. The stored procedure (which is just querying and narrowing down the data locally) still has the requirement to return less than 2000 items. – Brent Ellis Feb 27 '14 at 20:42
  • 1
    To consume OData, you will need to build your model in Visual Studio, so you wont get that fancy dialog. :) But since OData allows the $filter parameter, you can get the same experience. – Paul Schaeflein Feb 27 '14 at 23:54
  • That makes sense. Yes, the general approach would be the same: Filter the list at the source before loading it in SharePoint. Besides working around the item count limitation, you should also get much better performance this way, since you're sending much less data between servers. – Ben Wyatt Feb 28 '14 at 0:50
1

BCS. The item throttle applies to a single query operation. So, in your BCS model, be sure to include filters on the appropriate columns. These filters will show in the Entity Picker (the button on the list form that looks kind of like the people picker).

You won't necessarily get an autocomplete experience like the Bing/Google search box, but your users can certainly search for the item from your database.

  • (Completely new to BCS)So BCS will successfully bring in all 26,000 entries? it is the picker filters that must be built to be below the threshold? – Brent Ellis Feb 26 '14 at 19:57
  • No, BCS will not "bring in all #### entries." You define queries that are executed on demand. In your use case, that demand is the Entity Picker control used to assign a value to a list (library) column. You are still subject to the limits imposed by the throttle (2,000 items per query on-prem. I couldn't find anything in the O365 service description that would change that limit). – Paul Schaeflein Feb 26 '14 at 20:14
  • SharePoint Online Service Description: technet.microsoft.com/en-us/library/jj819267.aspx – Paul Schaeflein Feb 26 '14 at 20:14
  • Software boundaries and limits for SharePoint 2013: technet.microsoft.com/en-us/library/cc262787.aspx – Paul Schaeflein Feb 26 '14 at 20:15
  • 1
    It is time for you to read. :) msdn.microsoft.com/en-us/library/office/… – Paul Schaeflein Feb 26 '14 at 20:44
0

I just came across this discussion and it has solved my issue. My data set was far smaller. It was annoyingly just over the 2,000 item at ~2,400 items. The options were to increase the list item limit which affects all lists so is not acceptable or try this solution. This article was EXACTLY what I was looking for so I am reposting it to increase it's visibility.

http://msdn.microsoft.com/en-us/library/office/ff798274%28v=office.14%29.aspx

This are other great resources:

BCS and SQL http://blogs.msdn.com/b/jysaikia/archive/2010/12/15/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure.aspx#Create

Paging and sorting with BCS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.