Tag Archives: search

How to work with large lists in Office 365 SharePoint Online

What’s a large list? Anything over 5,000 items.

Why is this such a low limit?

Great question, and Microsoft does a good job explaining it in their help documents:

To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.

Do you think you’re going to exceed 5,000 items?

There’s a few things you can do before you exceed 5k, which will make life a lot easier.

Create indexes! Indexes are pockets of focused data that render results much faster (very laymen terms here). Searching for ‘bob’ against a name field is faster if that field is indexed.

Office 365 boasts that it will automatically create indexes, sadly I’ve come across some large lists without indexes, nothing created. Reached out to Office 365 support to create some, I’ll let you know if they actually do (requested a few weeks ago!).

Look for fields that you’ll be using to create views and index those. Think broadly like categories, dates, departments, etc. The ID field IS indexed automatically out of the box. Otherwise, create some more indexes.

I’m over 5,000 items, now what?

In the past, working with SharePoint installed on-premises, you could override the limit and go higher than 5k and keep on truckin. If you wanted to stay within Microsoft’s recommendations, there are opportunities to open up maintenance windows to allow bulk operations on lists larger than 5k.

In Office 365, we don’t have that flexibility. Microsoft actually has a list limit of 30,000,000 (30 million) items. 30M!?! That’s far over their definition of a large list. But the list view limit is 5k (.01%). Weird but I guess I understand why.

So what do we do? We have a few options, depending on your needs.

Users need to find records to update or reference

Create smaller collections of data.

If you’re fortunate enough to get your fields indexed, you’ll still need to keep your views under 5k items. You can have hundreds of thousands of items in your list, but you can’t serve up more than 5k items at a time to your users.

Using your indexed columns, create your filtered views. You’ll notice that your columns will be labeled as (Indexed):

indexed columns in a view

PLEASE NOTE that just because you filter on an indexed field does NOT mean you’ll be under 5k items. Also, you can filter on a non-indexed field IF you include a filter for an indexed field, which by itself would return less than 5k items. For example, consider a list with 10k items:

  • Filter State (Indexed) by ‘Connecticut’, and you get back 6k items. Filtering by City (not-indexed) ‘Hartford’ might return 400 items, but because the indexed filter returns over 5k, you can’t perform additional non-indexed filters against the data.
  • Filter State (Indexed) by ‘Massachusetts’, and you get 4,900 items, then you can filter by City ‘Boston’ and get your subset of 400 items.

The magic of the ID. The ID column is automatically indexed, out of the box, even though it doesn’t show it in filtering. Sometimes if you need to find specific data you can create a view with a range of IDs and get your items. You can also create a view that shows all data, sans filter, but sorted by ID, that will work because that column is indexed.

This is great, but if you don’t have indexes…

Search is awesome.

Search can handle your list, and a gazillion of other records of data. Once the item is indexed, you can search for it pretty easily.

For specific business cases, like a call center for instance, you can create a ‘search application’ in SharePoint to allow call agents to find exactly what they’re looking for, by searching for specific data points like casenumber:1234, or callername:Bob. Some of this functionality would usually be done at the list level using column filters and such, but due to the mass amount of data, search is the answer.

I’ll cover more on creating a search application in my next post, I promise. For now, just try searching for your content, should come back for you.

Users need to report on the data

This is the biggest pain in the butt by far. Managers and teams want to see some sort of reporting, like how many calls in the last week, how many from a certain state, etc. Normally we could fudge this with the out of the box list views, but alas, there’s too much data. Instead we can use additional tools to get this data and report on it.

Microsoft Access is an option, though I don’t like it. If you’re looking for a fast solution, connect your local Access database to SharePoint and suck down that list. You can create reports and even export easily to Excel. This option does require your users to have Access installed to view the data (<– the reason I don’t like it)

Power BI is by far the better option (I know, it all depends on requirements). You can point PowerBI to your huge SharePoint list, it will take time to import it all (I imported 300,000 items in about 45 minutes) but once it’s there, you’re golden. You can even setup a nightly refresh so your data is fresh every day when you come in. Awesome sauce right? See below

Reporting on large lists

Sharing PowerBI reports is easy through the standard Office 365 Share experience. According to Microsoft, PowerBI Pro is only available for E5 licenses, but I’m on an E3 and I can do a ton. E5 does offer more functionality but just pulling in data, creating reports and sharing, is doable on E3.

That’s a wrap! Let me know if I’m missing something, I’d love to hear your war stories. ‘Til next time, Happy SharePointing!

Advertisements

Before You Make SharePoint Public

Today I presented at the first 24-Hour SharePoint Conference! Thank you all for attending. My session was prerecorded, which was a little odd to record a session without an audience, but it was worth it. I had a lot of fun doing it and I had a great turn out. Thanks again for joining!

You can watch it in its entirety below. The deck is just below there, and I threw some of the questions below as well, along with the animated GIF from the video. Enjoy!

Video

[youtube http://youtu.be/5a-9BRIQU-E]

Presentation

Great Questions

I had some great questions during the session, thanks to all who attended. Some questions I received during the session:

Jared:  who’s moderating this beast? Christian Buckley again?
Mark:  this one is non anchored
Mark:  Keep the questions comign though
Jared:  That’s poor planning, David Lozzi will definitely require supervision
Mark:  lol
Mark:  let see…
Me:   I’ll be ok

 

Amy:  David – Do you only need licensing for the person or persons building the site?
Me:  if hosting on-premises?
Amy:  online
Eduard:  online limit: 1 person – 1 public site
Eduard:  online 100 person – 1 public site
Me:  SharePoint Online or O365 will have the licenses included, however i’m not sure… thanks Eduard
Me:  i have some links in the references for comparing the versions of O365 and SP ONline
Eduard:  so you need only 1 licence to run online public facing site
Jared:  Amy – for public facing site you just need 1 license
Jared:  That’s what we have for our User Group site.. Just need licenses for those that are logging in to make changes.

 

Eduard:  friendly urls is only on prem?
Me:  Not on Public site for SharePoint Online, you can use it on your private sites
Eduard:  sure

 

Pavan:  Question: Intend to convert a public facing .net site to a SharePoint public facing site, currently we have SP 2010 Enterprise, so my question is do i need any extra licenses to have anonymous access available?
Jared:  Pavan – if you’re hosting it then yes.. You’ll want to talk about SharePoint for Internet with your MS Rep
Me:  Great question Pavan. I completely forget the licensing model for SP2010. I remember in 2007, maybe 2010, that there was an Internet Connector license for making SharePoint public. MS has gone away with that for 2013, maybe for 2010?
Me:  I recommend chatting with your MS rep to clarify

 

Eduard:  Are there ana advantages in terms of SEO between on prem and online?
Me:  @Eduard page SEO is the same, sitemap is there. Online doesn’t have managed navigation
Me:  For me that’s a big loss for SEO
Eduard:  a meen to remeber that managed navigation have also drawbacks to SEO
Me:  @Eduard, what drawbacks?
Eduard:  as far as I remeber, if you have a content like wiki, that is highly interconnected – and now you start build up a managed navigation – the links are different
Me:  Ah, good point.
Me:  just a little more tedious to manage
Eduard:   yeahh

 

Oh right, and in the video I used this Working On It animation:

Working On It

 

Thanks again!

My Users Don’t Like SharePoint because they can’t find what they’re looking for! Part 1

This is Part 5 of my series on ‘My Users Don’t Like SharePoint…

I’ve actually heard, at a customer site, while the speaker was unaware their SharePoint consultant was in the next cube, the following:

Don’t put it in SharePoint, you’ll never find it again.

It broke my heart… A fairy somewhere lost its wings… The clausometer dropped…

Clausometer

Credit Elf, the movie. (3rd best Christmas movie ever!)

I wasn’t saddened because my solution for the company failed, nay, we were there to fix these issues. Instead, I was saddened because SharePoint was being thrown under the bus due to a poor implementation.

The older I get, the more I forget. I use to use bookmarks a lot, tracking everything I could. This worked great for a while, until I started to forget that I bookmarked something, so I’d go searching for it anyway. Nowadays, I only bookmark something if I want to reference it, say for a blog post, otherwise I rely on the search engines to find it for me. This has bit me in the butt a couple of times, as the item I’m searching for was difficult to find the first time, and proved to be even more so a few months later. Otherwise, this method of tracking my stuff via searching has worked well for me.

I even do it with Windows, I rarely use shortcuts, I just hit the Windows key and start typing to get my app. This is even more prevalent with Windows 8. Search is a big piece of Windows 8, and is by far the fastest method of finding apps. So obviously Microsoft is onto something…

Set it and forget itSharePoint Search: Set it and Forget It

Search within SharePoint is often a feature that’s turned on, and forgotten about. Unfortunately, this will work. As long as services continue to run, search will function: it will crawl and index your data and return search results. But there is so much more you can do with search to greatly improve your user’s experience (and drive up adoption).

In addition to search, users find their data in a few other ways:

  • Healthy navigation and taxonomy is crucial. We covered this some in a previous posts (a complete mess), and we won’t revisit it here. If you taxonomy is a mess, users will rely on search even more.
  • Paired with search is the concept of good old data, or rather good old CLEAN data. I’ve split this post into two parts, the latter will cover making sure your data is clean for search. Maybe I should cover that first… naah.

We’re going to jump into Central Administration now. If you don’t have access to Central Admin, that’s okay, read on, there’s more down below. Send this to your IT dept to get the info you need.

One more important note. Improving search will require a search center site, based on the search site template. This template includes the best bet functionality which we’ll leverage.

Search Administration

Within Central Admin, go to your Search Service Application (Application Management > Manage service applications > Search Service Application).

SharePoint Search Service Admin

First, let’s start with what we can see on the first page, Search Administration. Under Crawl History, note the start time and duration of your jobs. Make sure these are running optimally. If the duration is under 5 minutes, and the start time is every hour, then I’d say you can set your start time to run more frequently. Maybe every 15 minutes. If the duration is 15 minutes, and start time is every 15 minutes, then I’d say you should set the start time to run less frequently.  There may be some performance considerations when the crawl is running, so make sure you monitor performance across your servers if you do modify the start time.

What’s a crawl? Crawling is when the search service looks at your sites, one page at a time, and reads each page and loads the content into the index for searching. There’s a full crawl, which will index everything, and then an incremental crawl which will only index what’s changed since the last crawl.

Also under Crawl History, look at the Errors column. If you see anything over zero, 0, take a look and resolve the issues. Errors can impede performance and could be related to user data. Click that row’s Content Source link to find out more.

What are your users searching for?

Next, let’s click on Web Analytics Reports in the left quick launch, near the bottom. (Click here for info on the Administration Reports). These reports will provide some insight into what your users are searching for, and how successful they are. Going down the list on the left:

These reports are by default showing the last 30 days. Click the Change Settings link in the status bar above the report to change the date range. I’d recommend looking at the preceding 90 days, depending on the amount of data and searching in your farm.

  • Summary. This report shows a basic summary of what’s going on, and how much search is being used.
  • Number of Queries. This report shows the number of searches performed. Use this to identify trends and when search is used the most and least. This may not directly benefit your users, but with this information you can know when users are in SharePoint, and actively searching.
  • Top Queries. This report shows the most popular words searched within your farm. Write down what is most frequently searched for, we’ll use this info a little later.
  • No Results Queries. This report shows searches that returned no results. Write down the top handful of search terms, we’ll use this info a little later.

Now armed with your Top Queries and No Results Queries results, let’s improve your users’ experience with search. Let’s make it a tool they can use to find everything they want. Yes, let them find what they want, which may be what’s not available  on your sites (ala the No Results Queries report).

Search Result Analysis.

Don’t let the word analysis scare you, this is really simple. Taking the list you collected from the search analytic reports, figure out what results you want users to see. Analyze your results and determine what users are really searching for. This might require you to chat with a few users to understand the terms and how they fit into your organization, ok, that might be the scary part.

A few examples:

  • The word ‘review’ was used a lot, usually regarding employee performance, but didn’t return any results. The ideal target for ‘review’ is the HR site.
  • The phrase ‘telephone numbers’ was searched for but returned no results. This is kind of an old phrase, nowadays it’s called ‘contacts’, so we’ll want to search for ‘contacts’ whenever ‘telephone numbers’ are searched for. Also, we want to target them to the customer contact list.
  • Searching for product name (if you’re a product company) or a service offering can be improved by specifying where to target the result. Maybe to the product site, if there is one, or to a marketing site.
  • You may find that a target location doesn’t exist. Encourage users in those departments to create the content, maybe HR should make a FAQs page that can answer when reviews occur, and marketing should move the product information into SharePoint so users can find it.

Update Search.

Plain SharePoint Search Results

The above illustrates a typical search result page. Searching for the word review returned a PowerPoint file and some other files, but nothing about employee or performance reviews.

Now that we know: what users are searching for; when they’ve been successful or not; where they should go; let’s spruce up the search results to get something a little more useful.

Go to your SharePoint site, specifically the home site, the root site in the site collection. Go to Site Actions > Site Settings.

Under Site Collection Administration, if you see Go to top level site settings, click that, then continue.

Click Search keywords under Site Collection Administration. Click Add Keyword.

SharePoint Search Keywords

  • Keyword Information – enter in the keyword or phrase users have searched for (from the reports above). If you want to offer an alternative keyword, like searching for ‘telephone numbers’ offer up ‘contacts’, specify these values in the Synonyms box, separating your keywords with a semicolon. These will appear under Related Searches.
  • Best Bets – click Add Best Bet to target they keyword to specific pages in your site. You’ll specify the target URL, title and a description. This information will be available to users in the search results.
  • Keyword Definition – Optionally, you can specify additional information to help the users make an informed decision by defining what the keyword means to the company. Also, you can add a contact and some dates to publishing, expiring and reviewing this keyword.

Click OK. That’s it! Go and search for the keyword and your search center should show your best bets, like:

SharePoint Search Results with Best Bets and Keywords

Pretty neat, eh? You should notice two big changes: First, at the top of the search results is your keyword and best bet. Secondly, on the right is related searches. Click a related search to perform another search.

Want More?

Check out TechNet’s article on how to Manage settings to improve search results. There’s a lot more tuning you can play with like custom dictionaries, thesaurus and stop words.

Stay tuned for the next post as we’ll discuss ensuring your data is ready to be searched.

Til next week, Happy SharePointing!