SharePoint Online BI and Connecting to On-Premise Data

Refreshing Data with PowerPivot and Excel Services

I came across this really good whitepaper on how to refresh data in PowerPivot in Excel Services in SharePoint Online. The most important part is “in SharePoint Online”.

If you know much about SharePoint Online Business Intelligence, may know we provide Business Intelligences features in SharePoint Online with limitations. The most important being something like Excel Services cannot make a live connection to a database. If the data resides in the Excel spreadsheet itself no problem Excel Services works great with that data. The challenge has always been how to get the data refreshed into the spreadsheet if the data needs to be pulled from a line of business databases?

The solution described in the spreadsheet takes advantage of several new capabilities of SharePoint Online. It takes advantage of the new SharePoint Online PowerShell commands, the Sync capability of SkyDrive Pro and the new PowerPivot solution now supported in SharePoint Online. The script is very simple. All you do is schedule it, it will check out the Excel workbook, refresh the data, check it in, and then sync the file right back into SharePoint Online. Nice!

Before this new release of SharePoint Online, organizations would have to manually do this action. If an organization got crafty they could use web services to accomplish the same thing but it would be a cumbersome solution to manage and maintain.

This new solution is just so easy and straight forward.

Refreshing Excel workbooks with embedded PowerPivot models from SharePoint Online (white paper) - http://technet.microsoft.com/en-us/library/jj992650.aspx

SQL Reporting Services

If you have read the new SharePoint Online Service Description on Insights (http://technet.microsoft.com/en-us/library/jj819252.aspx) you will have noticed several new capabilities. One of these new capabilities is SQL Reporting Services reporting web parts are now supported. SSRS reports running out of a document library in the cloud can utilize data in a SharePoint lists. You could use a similar solution using the new SharePoint Online Client Side Object model to refresh data in a SharePoint list that the SSRS report would use. The only limitation to consider is the amount of data you are moving into the cloud. I am not suggesting that a SharePoint List cannot handle the data however we all know that SharePoint Lists are not meant to replace something like a SQL Server database. I would recommend that you do as much processing and aggregation of the data before sending up into SharePoint lists. It may even make sense to de-normalize the data a little.

Still you should be able to create some really nice reports that can be centrally managed and access through SharePoint Online.

Visio Services

Visio Services has always been available in SharePoint Online. I have in the past successfully used it to connect to data in SharePoint lists and then create both reports and dashboards. As usual the limitation was how to get the data on-premise.

The way I see it, you have two approaches for getting Visio Services in SharePoint Online to work with a database on-premise.

First, you can do what I just described above where you use the Client Side Object Model to get data into SharePoint Online lists.

Second is leverage BCS! This is new. If you have read what is new with Visio Services (http://msdn.microsoft.com/en-us/library/jj164027.aspx) you will see that SharePoint Online supports BCS. Additionally if you have SharePoint 2013 on-premise, it is now possible to implement a SharePoint Hybrid solution (http://technet.microsoft.com/en-us/library/jj838715.aspx). In this case you can have live on-premise data securely connected to SharePoint Online which Visio Services can build reports off of.

Conclusions

As you are seeing with the introduction of SharePoint Online PowerShell, Client Object Model, SharePoint Hybrid, SSRS, PowerView and SkyDrive Pro along with new improvements to BCS, Excel Services and Visio Services we now have some better options for BI in SharePoint Online. I see the gap starting to close and hope with time, more gaps to be closed.

SharePoint Online Hybrid BCS and Search

Introduction
With the new release of SharePoint Online and SharePoint 2013 we have some new SharePoint scenarios that can be supported. I have always told customer the definition of hybrid sometimes murky. Why because SharePoint can be implemented in so many ways. In many cases ADFS provides a unified user experience providing the user experience they need. Doing application integration using APIs, remote libraries, etc. provides all that is needed. The new SharePoint Apps model is going to really introduce a lot of new ways to create customer hybrid solutions.
Some other new hybrid scenarios that are going to be supported with the new SharePoint Online and SharePoint 2013. Specifically there is the ability to configure BCS and Search to support some real hybrid solutions. This is really exciting.
Please review the following whitepapers that are located here:

• http://technet.microsoft.com/library/jj838715.aspx
• http://www.microsoft.com/en-us/download/details.aspx?id=35593

Hybrid BCS
With the Hybrid BCS, organizations are going to have some real flexibility in you can expose data from the enterprise to SharePoint Online. In the prior version of SharePoint Online based on 2010 technologies, we were limited to working with web services. There is nothing wrong with that, but we would have to create some web services, make it available to SharePoint Online, and then use it. Here are some facts:

• This supports the ability to view, edit and delete data.
• All data sources must be published using OData.
• Using this solution, no data needs to be published out in an unsecure way, require any new ports to be opened, etc. The on-premise SharePoint 2013 server will basically connect, using a reverse proxy, and will publish out an end point which SharePoint Online can connect to.
• ADFS with Federated identities is required.
• There are a few configuration steps that you will need to go through outlined in the document. Additionally there is some configurations that you will need to make on the SharePoint Online administration screens.

Doing this will allow you to bring your line of business data into SharePoint Online. Users can access that data through External lists, External columns, developers can use it through code, workflow can use it to help make decisions through using the external lists, Visio Services has a new feature that allows it to user external lists, etc. I am frankly just happy to see this level of integration and facilitation versus what we had to do in the past to expose data to SharePoint Online.

For more details read here - http://technet.microsoft.com/en-us/library/dn197239.aspx

Hybrid Search
Even more exciting is our new support for setting up Hybrid Search for SharePoint Online. SharePoint 2013 must be installed on-premise and you must again be using federated IDs with the SharePoint Online. I would say this is one of the primary use cases for facilitating a hybrid deployment. Why because we want to allow users to be able to initiate and search for data regardless of where it resides.

Remember that the new SharePoint Online in the cloud does utilize the FAST search technology. However just because that capability is delivered in the cloud, it does not provide the ability to index on-premise locations. We rely on SharePoint 2013 on-premise to basically do a federated search. This means that SharePoint Online Search does not directly index on-premise content; it relies on those on-premise servers.

There are two approaches for configuring Hybrid Search:

• One-way Hybrid – In this scenario you have the ability to perform a search from your on-premise SharePoint 2013 environment to SharePoint Online. This way users will have the ability retrieve content from the SharePoint Online anytime they do a search from on-premise. This is nice if you have a search portal set up on-premise that is indexing your entire enterprise and SharePoint Online is just another content source to index. All those intranets, team sites, My Sites, etc. in SharePoint Online are accessible.
• Two-way Hybrid – In this scenario you will be configuring both your SharePoint 2013 on-premise environment and your SharePoint Online environment to perform search of each other. This is really powerful to allow the end user to find content across the enterprise regardless of where the user is.

For more information read here - http://technet.microsoft.com/en-us/library/dn197172.aspx

Architecture Considerations for Moving SharePoint to Office 365 and SharePoint Online

Introduction
Why are customers looking to come to the Office 365 cloud? Customers want an environment that can scale on-demand. Customers do not want to be in the business of managing and patching software . Customers want a solution that give better business continuity and service level agreements to their users. Customers want solutions that are better governed and that will force them to do better governance. Customers want to be in the business of building solutions (i.e. an airplane company should focus on building planes; not writing enterprise applications from the ground up).
When I talk with customers about SharePoint Online, this is where they want to move to.
In this blog I am going to talk about how an organization should be looking at moving to SharePoint Online.

1. In first half we are going to analyze SharePoint Architecture and Governance and review what potential blockers people see when moving to the cloud.
2. In the second half I am going to have a more detailed discussion around how to architecturally plan to move to SharePoint Online.

Why the decision is not always straight forward?
Coming to SharePoint Online in Office 365 may not sometimes be the most straight forward decision for organizations that have made a large investment in SharePoint. Going to SharePoint Online is really easy for organizations that are in it for document management, intranets, OOB features, SharePoint Designer, etc. It gets harder for organizations that have had poor governance of their on-premise environments, made signification investments with custom code, utilize features not available in cloud, have a heavy reliance on third party solutions, etc.
There is a solution to these issues but it requires an organization to take a step back and look at what they have.
Poor governance is probably one of the largest changes organizations have faced with SharePoint. These challenges could have been avoided thought with good forethought and planning. There is not always a one size fit all solution either; SharePoint is powerful platform that can be used to be used to implement a very broad set of business requirements.
SharePoint Architecture Foundations
The following a Venn diagram that I always draw on whiteboard with customers.

I typically say that that:

• Information Architecture – Is content types, taxonomy, topology, site collections, sites, libraries, lists, and solutions that drive the management of this content. The information architecture should be driven by business requirements.
• Logical Architecture – Is the architecture of SharePoint services, web apps, databases, etc. to support the information architecture.
• Physical Architecture – Is the configuration, deployment, farms, etc. that actually host the SharePoint logical architecture.

I admit this is open to interpretation but let’s use this as a foundation for discussion.
Here are some of the biggest problems organizations that have come up when I discuss the diagram above:

• Started with Physical Architecture – This is the first mistake that many seem to make. Smart IT are concerned about how many servers to buy and configure they forget to actually create an environment needed to support their business requirements. What usually happens is the environment is not scaled or organized to support the actual utilization; this is where the trouble starts. This pretty much goes away if you go to the cloud.
• No business requirements and software design best practices – One of the biggest, consistent challenges I see many organizations have with SharePoint is no business requirements. I typically just see organizations create very light documents (a bullet list) of requirements and just start building. I very rarely see organizations actually do software design best practices. There are no use cases, data models, ERDs, UI wireframes, data dictionaries, architecture documents discussing what SharePoint elements they plan to use, etc. Why; because SharePoint is such an easy platform to start building with. I personally believe SharePoint is one of the best solution platforms on the market but sometimes organizations need architect a solution fist. This does not go away with SharePoint Online.
• No Governance – Organizations usually forget to put Governance plans to manage content and solutions running in SharePoint. A solution can be anything from a group of team sites or a complex .NET application in SharePoint. I have even seen organizations create Governance plans but then not manage nor adhere to them. An analogy would be a organizing a ton of boxes in their basement really well (with labels too). However as time goes on, they just keep on stuffing and stuffing the room with more boxes so that entry to the room is blocked. A good SharePoint environment needs care and feeding. The information, logical and physical architectures need to have policies and procedures to manage it. Policies, procedures, service level agreements, etc. must be put into place. With SharePoint Online several of these Governance woes are removed because the Office 365 cloud manages the environment. Still Governance needs to be but in place to manage content and solutions deployed in Office 365.
• Over Regulation – I have also seen the term “SharePoint Governance” used as a crutch. I have seen organizations lock down SharePoint too much. My response to that is was there a requirement to lock it down? If so, the right thing was done like a publishing intranet or Internet site – no user should have rights to do what they want. There should be a locked controlled publishing and branding process. However it is perfectly acceptable to have areas of SharePoint that are pure collaboration. Your information architecture and governance plans will drive business users to put content and solutions in the appropriate areas.

There are more things to think about but I am getting a little preachy J
A Not So Unusual Situation
Here is a very common scenario that backs up what I just described. It is very common for an organization to start a SharePoint environment like the following. As you can see they start with a top level SharePoint site. They create some department level sites and some sub sites for their intranet. All in all this is a pretty good start; right?

However within a few months (after some very heavy business and user adoption) we have the following.

We see such things as:

• Team sites starting to sprawl underneath some of the sub-sites within a department. The challenge is being able to support collaboration in what was meant to initially be a publishing site.
• Navigation, presentation, branding and user experience is not consistent.
• We see custom applications either built from scratch or third party solutions purchased and embedded in sub-sites of a department. The challenge is should these custom solutions be hooked directly here?
• Department level project sites are created. The challenge is that as project sites grow and take on new responsibilities for the business, they need to be elevated and made accessible like other sites.

There tons more. And I have had customers tell me this is not an issue with “SharePoint”. They currently have these same exact challenges with their legacy intranet, portals, etc. So why do organizations keep on having these challenges? I usually point to a lack of attention to Governance and Information Architecture.
So How Should You Be Thinking About This?
What if content and solutions that are to be deployed are delivered within a framework? Not a “novel” idea either. Hopefully we can make it as simple as we can. Instead of just adding more and more sites and applications; have rules that drive where we put things.

For example:

• The Intranet site should remain a dedicated publishing site collection. This would be geared towards business users having read-only access with only a small set of content owners. Content is delivered in a consistent and clean fashion so that every site is the same giving the user a unified user experience.
• Create a separate site collection for team sites with appropriate service level agreements. In the team site collection, sites should be can be dynamic generated from pre-define site templates. Users are given the ability to do pure collaboration, standing up lists, libraries, etc. They should be able to share information to complete everyday business tasks. There would also be an expectation that this area is not uniform and that users can do what they want with these sites. Retention polices can be put in place to discard these sites if they have not been used for a pre-defined period of time.
• Create another site collection for business project sites. These have different support SLAs and Governance. These sites are a little bit more formal in nature with only a subset of people that would have access to manage them. Maybe there are designers from the IT department who have responsibility for building and supporting them. There could be policies and automated procedures to move content out of them to other site collections. Retention policies on these sites would be completely different than team sites and these sites may only be deleted when the project is over (or never deleted).
• For custom applications, instead of embedding the application into a sub-site, place them into their dedicated site collections. Navigation links can be made to the application from other site collections. This would give significantly more flexibility to move it or provide it more resources.
• Instead of creating one massive site collection where all scanned documents or records create multiple site collections and then route content to them based on business rules.

This list I provided is not meant to be an all-encompassing list of possibilities and/or solutions. Hopefully you will see that you need to start vertically and horizontally partitioning solutions and data based on such simple things as business rules, security access, data characteristics, etc. Knowing this will help you identify the types of site collections, sites, features, content types, managed metadata, site templates, permission levels, etc. that you need to configure SharePoint with.
The net result is you would take the previous diagram and start creating management boundaries based on the characteristics of the solutions and data managed in SharePoint. This is why Information Architecture is so critical for Enterprise Content Management system like SharePoint.
Putting everything into a single site collection really puts an organization into a tough spot to scale with the business. Microsoft has been publishing great technical diagrams for SharePoint 2010 (http://technet.microsoft.com/en-us/library/cc263199.aspx) and SharePoint 2007 (http://technet.microsoft.com/en-us/library/cc263199(office.12).aspx). Please review and get to know these diagrams well because they accurately tell everything I am talking about here.
Now some people counter this whole thing with why does SharePoint not give this to me? Why does SharePoint not auto-govern itself? SharePoint absolutely comes with a ton of features and capabilities that support a good governance model like Features, site templates, sandbox, managed metadata service, permission levels, and the list goes on. All of the configuration settings are there and organizations just need to set them as appropriate for their business.
I promise; we are coming back to the SharePoint Online cloud but we need to finish setting the stage.
How You Should Change Your Thinking
The following is a diagram a colleague (http://blogs.msdn.com/b/edhild/) and I commonly discuss with customers. I am shamelessly using it because this simple diagram really helps customers with a basic understanding of Information Architecture with SharePoint. Plus it supports everything that I just discussed. This is what we call the “Arch of SharePoint Data”. This is not all encompassing list, however there are different extremes.

There is publishing which are sites that are managed by a small group of users and read by a large community of read-only users. While on the other side is something like MySites which is my personal area to manage information and data. In between these two extremes are tons of different types of sites. Department sites, project sites, organization sites, custom application sites, team sites, etc. There are too many to even try to draw. Each one of these types of sites has different security, retention, data usage, transaction management, business rules, automation, etc.
I tell customers is that is perfectly ok to have a team sites area that are for pure collaboration where business users can spin-up a site, do some work on it for two months and then move on. Some people call this the “wild wild west” and that is ok. Just do not allow pure collaboration in you publishing area which is probably one of the most common mistakes J.
Coming full circle, you can see this is all about solution and data management. An Information Architecture is going to tell you the data characteristics. This will drive you to put content in one area versus another.
This is the beauty of SharePoint. It allows you to create and manage business workloads based on your specific business and mission. Once you have the Information Architecture nailed down, you can determine the Logical Architecture (services) you need and then the Physical Architecture needed to support this. By doing this, you know that both your Logical and Physical Architectures will be driven off real business requirements.
Where Does this All Fit with SharePoint Online?
Once you build an Information Architecture you will see that many of these partitions can be moved to the cloud. Intranets, team sites, project sites, my sites, light weight custom solutions, etc. can all be moved to the cloud. If you look at the “Arch of SharePoint Data” diagram, depending on your scenarios, there is a good chance that 80% to 90% of your solutions can be moved to the SharePoint Online cloud. For some organizations, they will be able to move everything up to the cloud. For some organizations they will have a hybrid.
Regardless the re-architecture should be a primary task in your SharePoint 2007 migrations to SharePoint 2010 anyways. Going through this exercise will put you in a position to move pieces to the cloud and with time, more and more.
So what are the Gaps with SharePoint Online?
If you want to know the exact answer it is clearly spelled out and completely available for you. Read either the Multi-tenant SharePoint Online Service Description (http://www.microsoft.com/download/en/details.aspx?id=13602) or the Dedicated SharePoint Service Description (http://www.microsoft.com/download/en/details.aspx?id=18128).
However really understanding these gaps depends on the perspective and approach you are taking with SharePoint. As I say a lot, “SharePoint means a lot of different things to different people”. I have seen customers extremely happy with using SharePoint with out of the box features and SharePoint Designer. While I have seen other customers use SharePoint as a full application development platform writing thousands lines of code. Knowing what SharePoint means to you will dictate your approach to the SharePoint Online cloud. The approach that I outlined in the first part of this bog will really help you with that decision process for moving to the cloud.
When you read the SharePoint Online service descriptions for both the multitenant and dedicated you will see that the gaps are really small. However there are some ones you must be aware of as they will affect your decisions on how to move to the cloud. The big ones that most people bring up are full trust code, business intelligence, FAST search, PowerShell, and Central Administration. This list will quickly change and become outdated because more and more features will be released with time. However let me address each one as it stands today:

• Full trust code is usually the first one the first challenges. Right now, the only way to deploy custom code to the SharePoint Online Multitenant cloud is using a Sandbox solution. For the SharePoint Online Dedicated cloud, full trust code is supported but it must adhere to a strict set of rules (which are publically available) and the code will only be deployed within set windows. Why such restrictions and strong governance? Well, for all the obvious reasons that would come up if you were tasked with having to run an extremely large SharePoint environment on-premise. What has been one of the biggest issues with SharePoint 2007 Governance? It was developers writing complex code on SharePoint and disregarding the fact that an error they write may take down other sites in the farm (like the content query web part that retrieved too much data on the home page of the intranet <g>). We need to ensure that there is strong security and good performing code and that there is no possibility the Company A can take down Company B. The only way to achieve that service level agreement is to have an environment for running governed code. The net effect is that there will be limitations but you will have that guaranteed uptime. My golden rule is that all SharePoint development (on-premise and cloud) should begin with Sandbox solutions and only when the customizations cannot run in the Sandbox then build as full trust solutions. Doing this will ensure you have agility to move to the SharePoint Online cloud when you are ready. If you really need to do complex operations and manage data structures consider using services Windows Azure integrated through Business Connectivity Services (BCS), Silverlight, etc. But still that may not always suffice and that is why SharePoint Hybrid implementations will be commonplace for organizations with mature SharePoint deployments. I will cover this in more detail shortly.
• Business Intelligence today in Office 365 is Excel and Visio Services. Other SharePoint business intelligence services like PerformancePoint, SSRS, PowerPivot and Chart Parts are not available right now. Another limitation today is Excel and Visio services only utilize data that is within the SharePoint Online context; it is not able to reach outside (i.e. to back-end databases). More and more capability will be released through the Office 365 cloud; just for the time being Silverlight and Windows Azure can be used to satisfy these requirements.
• FAST Search is not available in the SharePoint Online cloud right now. It is possible to integrate a local FAST farm with SharePoint Online Dedicated; but not Multitenant. Still take comfort in the fact that SharePoint 2010 search made significant jumps forward from the SharePoint 2007 offering and provides a very strong search experience. With time more and more advanced search capabilities will be released in the cloud.
• PowerShell currently is not fully available with SharePoint Online. There are a lot of PowerShell commands available for Exchange Online and user subscription management; however the full set of SharePoint PowerShell commands is not available today.
• Central Administration Site is not available and this should be expected by anyone who understands what cloud architecture. There are administration screens available to some operation that you would normally perform in Central Admin however it is limited and does not give you the granular control. Why? Well this is the cloud. Customers want to come to the environment so they do not need to be in the business of managing every little configuration of SharePoint.

And the reality is with more and more releases of SharePoint Online this gap is going to continue to close. Still, even if it were to completely close, there will be perfectly valid reasons why some SharePoint may remain on-premise thus, creating a SharePoint Hybrid environment.
What is the SharePoint Hybrid Architecture?
SharePoint Hybrid is as simple as it sounds; it is some SharePoint delivered through the cloud and some SharePoint delivered through on-premise. What will drive you to have SharePoint on-premise? All of that has pretty much been covered to this point. Anything from a specific feature to a business policy may keep some SharePoint on-premise. However using the approach I laid out, you will be able to significantly reduce your footprint of SharePoint on-premise and gain the advantages of the cloud.
The great thing about using SharePoint on-premise is that it is the same software being run in the cloud. This means it is very easy to deliver a consistent user experience between on-premise and the cloud. Branding, navigation, security groups and single sign-on can be configured in such a way that the user can go between these two environments and not know it.
What Type of Cloud is SharePoint Online?
One other thing I want to discuss is what type of cloud is SharePoint Online. I really like this picture it really spells it out for people whom are not fully aware of the multiple delivery models for cloud computing.

Starting on the left side, you see on-premises and this is how most organizations run SharePoint. You must own the entire solution; all the way up the computing stack. Next is Infrastructure as a Service (IaaS) which is the cloud environment that is managed all the way up to the virtualization layer. The company is responsible for everything else; including the management of the operating systems. From a SharePoint perspective that means all the software installation, configuration, management, patching, adding new servers to meet demand, load balancing, etc. needs to be managed by the you. IaaS gets you of the business of hosting virtual SharePoint servers.
Platform as a Service (PaaS) is running the environment all the way up to the application and data tiers. Windows Azure is a PaaS cloud. In this cloud you build custom applications, data models and run them through a highly available environment.
Software as a Service (SaaS) is the entire stack delivered in the cloud and this is the delivery model for SharePoint Online. You do not have to install software, manage patches, availability, etc. This environment gets you out of the business of managing software and into the business of building solutions. SaaS does not provide the granular level of control of the SharePoint environment (which we have discussed).
The reality is that organizations and companies need to save costs and SaaS is the cloud delivery framework they want for the long-run. IaaS can run SharePoint 2010 and can be leveraged as a replacement for on-premise complex SharePoint computing. Still it is well recognized by industry that companies want more SaaS solutions.
Supplement with SharePoint Online with Windows Azure
Windows Azure (PaaS) and SharePoint Online (SaaS) can be used together to deliver end-to-end cloud solutions. Companies that have mature SharePoint deployments commonly have:

• Code that runs in full-trust
• Are managing complex data
• Require the ability to do back-end systems integration.

One thing I have been talking with customers about is offloading that code out of SharePoint and into to the Windows Azure (PaaS) cloud. The Windows Azure cloud allows you to develop and deliver custom code, services, complex data (SQL Azure) and connected back-end integration (AppFabric). These complex operations can be connected through Business Connectivity Services (BCS) in SharePoint Online.
This makes a lot of sense too when you take a step back. I have already said that you should develop SharePoint code to the Sandbox first and when there is good reason to deliver outside the Sandbox. Here is a similar question. At what point do you know you should be developing in SharePoint? I fully recognize there is a gray area here.
I say good software development patterns and practices should drive that decision. This is why Windows Azure is so interesting with Office 365 because we can move complex code that cannot run in the SharePoint Sandbox to the Windows Azure cloud. I recognize this is not a perfect rule because some code needs to run in SharePoint as full trust. However this should be part of your design analysis to reduce to your SharePoint on-premise footprint.
There are lots of different ways Windows Azure can be utilized with SharePoint Online. There may be situations where you need:

• To work with data in SharePoint but you have complex relationships in the data model that are not right for SharePoint lists. Use SQL Azure to manage that data and build services and connect via BCS or Silverlight.
• To integrate with line of business applications on premise use custom services deployed in Windows Azure or AppFrabic. Again BCS or Silverlight can connect Windows Azure which is conduit to line of business applications.
• To use custom web services to perform complex computations and logic. Again offload that to Windows Azure.
• As well, reverse the direction. There may be solutions and applications delivered in Windows Azure can utilize SharePoint Online services. There are SharePoint web services, REST services for data, SharePoint Client API, Javascript APIs and Silverlight APIs that can be used as integration points. A simple scenario could be a web page deployed in Windows Azure that needs to manage documents. Instead of building that up from scratch in Windows Azure just connect through SharePoint Online APIs and deliver SharePoint Online services through that custom web page.

At the end of the day both SharePoint Online and Windows Azure can be used to complement each other in the delivery enterprise business solutions through the cloud.

Conclusions
Why did I go through all of this? The answer is simple; to give SharePoint Architects ideas on how to move forward with SharePoint Online. There really should be no blockers as long as you take a realist look at your Information Architecture and assess how you use SharePoint. There will be lots of stuff which can clearly be moved to the cloud, there will be some stuff where it is not appropriate (hybrid) and then there is that gray area. However I really hope that approach I put forth will help you with your thinking into how to significantly reduce, if not completely remove, your SharePoint on-premise footprint.
Thanks
Special thanks to Chris Geier and Stephen Cawood for providing me feedback and advice as I wrote this.

SharePoint Online Service Descriptions Updates Fall 2011

Service Descriptions
Some of you may be aware but many of the service descriptions associated to Office 365 have been updated in the past month. It is really easy to find all of the Office 365 Service Descriptions – just search on “Office 365 Service Descriptions”. I highly recommend that you bookmark these locations because this is where the new service descriptions are always released. To save you a few seconds:

• Multi-tenant service descriptions - http://www.microsoft.com/download/en/details.aspx?id=13602
• Dedicated service descriptions - http://www.microsoft.com/download/en/details.aspx?id=18128
• Dedicated Federal service descriptions - http://www.microsoft.com/download/en/details.aspx?id=23910

Now the reason why I say bookmarks these is when there are changes to the service descriptions this is where you can get the updated documentation. This Fall has seen a few updates to service descriptions and for readers of this blog (whom are generally SharePoint people) there have been some updates.
SharePoint Online Multi-tenant Updates
In November 2011 there was an update to the SharePoint Online service description. There are a couple updates that you should be aware of:

• Business Connectivity Services (BCS) – is now available. Yes we heard about it at the SharePoint conference but now it available in the service descriptions. It supports WCF Web Service End Points (basically you need a WSDL web service available). This is great if you are trying to go completely cloud and you want to use Azure (PaaS) to use such things as Azure services, SQL Azure, App Fabric, etc. with SharePoint Online. So if you have complex data structures you want to manage and integrate directly in SharePoint use SQL Azure. If you can use App Fabric to connect premise back end applications, making those services available through SharePoint Online.
• A new capability available for partner accounts that are using Windows Live ID. Please read the service description for specifics. I personally think this is a huge new offering because SharePoint Online in general is a great solution of Partner Portals (Extranets). Nothing against doing an extranet on premise (and they work well), but it just takes more configuration and management than what system administrators what to take on responsibility for. With this partners can be given secure access to areas of content in SharePoint Online and they do not have to manage accounts in AD, Forms Auth, etc. This is absolutely huge and when I sit back and look at what SharePoint Online is offering as a whole, SharePoint Online has so many redeeming qualities for Partner Portals.
• There are a lot of new “Notes” that have been added to the service description that clearly communicate some of the most common questions that I have had over the past few months. I highly recommend reviewing these.

SharePoint Online Dedicated Updates
For the SharePoint Online Dedicated plan was updated in October 2011; there have been some updates that you should be aware of.

• Increased storage the max storage limit to 120 TB (which is 4 times more than previous). That is a whole lot of storage available to an organization. All the rules associated to purchasing extra TBs of storage to get to that 120 TB still hold the same.
• There is a new Administration portal for you to manage configuration requests, a new reports area providing analytic reports on usages and such, and support documentation.
• Active Directory Rights Management with IRM is now available for document libraries. SharePoint Online (both dedicated and multi-tenant) to store documents that had rights added prior to upload in SharePoint (and it is still supported). However the challenge is the document cannot be indexed. Now for SharePoint Online Dedicated, the document library IRM configuration is available so you can set rules on a document library and when the document is taken out of the SharePoint Online site, the IRM rights will be applied based on the setting in the document library. Documents put in a library using this new way are searchable.
• There is new support for Data Loss Prevention (DLP) solutions to scan SharePoint Online for sensitive content, PII, etc. Read the documentation for details.

As well there have been October 2011 updates to the “Custom Solution Developer’s Guide” and “Custom Solution Policies and Process”. I would review these. As well in the Custom Solution Developer’s Guide there is a listing of the SharePoint 2010 third-party solutions that are approved to run in the dedicated cloud. This is an evolving list and addition to this list is accomplished by having a customer basically take a third party solution through the approval process.
Finally there is a new document called “Content Migration Policy and Storage Design for SharePoint 2010”. This is a really interesting document because it actually gives you some real insight into the storage architecture and how we manage quotas for site collections and such. I would even say a premise deployment of SharePoint 2010 should read this document take some best practices from it to implement on premise.

BCS Connection to WCF Web Service

Introduction

I recently needed to connect BCS to some WCF services. The goal was show how I can then use the data out of those WCF services as a data source for FAST. Creating a WCF service and publishing it out through IIS was a pretty simple task. For search you only really need two web methods. First you will need a method that returns a full collection of the dataset which will be used as part of the indexing process. The second method you will need is a get item method that will get the details of a record in the data set.

Issue I ran into

However I ran into a challenge with getting the connection to work initially. When filling in the WCF connection window I would get errors like:

In the Event Logs I had some more detailed errors which really did not help much.

Could not obtain a proxy to WebService for LobSystem 'Default' in App Domain 'ConnectionDomain'. The full exception text is: Metadata contains a reference that cannot be resolved: 'http://demo2010a:8000/SoftwareService/SoftwareService.svc'.. The inner exception text is: <?xml version="1.0" encoding="utf-16"?><Fault xmlns="http://www.w3.org/2003/05/soap-envelope"><Code><Value>Sender</Value><Subcode><Value xmlns:a="http://schemas.xmlsoap.org/ws/2005/02/sc">a:BadContextToken</Value></Subcode></Code><Reason><Text xml:lang="en-US">The message could not be processed. This is most likely because the action 'http://schemas.xmlsoap.org/ws/2004/09/transfer/Get' is incorrect or because the message contains an invalid or expired security context token or because there is a mismatch between bindings. The security context token would be invalid if the service aborted the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase the Receive timeout on the service endpoint's binding.</Text></Reason></Fault>.

Could not obtain a proxy to WebService for LobSystem 'Default' in App Domain 'ConnectionDomain'. The full exception text is: There was an error generating the XML document.. The inner exception text is: The type System.Web.Services.Discovery.DiscoveryDocument was not expected. Use the XmlInclude or SoapInclude attribute to specify types that are not known statically..

Resolution

After playing around with the configuration I finally found the following worked. Notice the “mex” extension added to the end of the Service Metadata URL. Once I had that done, just wire up the web methods like you would anything else in BCS.

Now here is another way to do this. Any service that has a wsdl description can be integrated as well. Notice here how I put the wsdl address for the Service Metadata URL.

Part 4 – Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow

· Part 1 - Pattern for Building Stateful Workflows with SharePoint Designer 2010
· Part 2 - Custom Action for Workflow Initiation
· Part 3 - Delays for Workflow Initiation Action
· Part 4 - Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow
· Part 5 - Custom Visio Services Reports with SharePoint 2010 Workflow

Introduction

If you have been reading this blog series closely I mentioned that I will be discussing how we can do reporting for our workflow solutions in SharePoint 2010.

As you may know when you configure a workflow in SharePoint there is a Workflow History list that must be associated which will stored information about the execution of a workflow instance. I have typically viewed this as more of a log. It is not something to be used for reporting. Here is a quick reference about it and I suggest you read it - http://technet.microsoft.com/en-us/library/ee662522.aspx

The reasons I do not use it for reporting is because:

· This list can potentially store a lot of data from a lot of different workflows.

· It will be purged after a period time. Even though you can disable a job to ensure logs are not deleted, doing this would disable the job for every workflow across an entire web application which is not good practice.

· Personally I like it for its logging capability but I would not use it for a reporting capability.

Solution

My approach will be to store data in a SQL Server table using Business Connectivity Services (BCS). With SharePoint 2010 it is so easy to support this from a workflow without doing a ton of code. Now I can do inserts and updates operations to reporting tables and then build reports off of them using the either Excel Services, SSRS, etc.

I am not going to go over the steps on how to create a SQL Table or how to create an external content type using BCS. What I will cover is how to use the external content type from a workflow and discuss one tricky configuration you may not know about.

For this solution you need to:

· Create any sort of SQL tables you need.

· Create external content types to connect to those tables.

· Create external lists on the site to use the external content types.

· Finally I will cover how the workflow will call the external list as a proxy to make transactions against the SQL tables that you will use for reporting.

Insert a Record from a Workflow

There is really nothing special to this at all. Add a Create List Item action into my workflow that references the external list. In my case, the external list I created was called “Review Actions Table”.

Note that I created a variable called ReviewActionID, where I store the SQL generated ID of the new item that was created. If you need to update this SQL record as part of a different workflow process, I recommend updating this value into a column on the item where the workflow is running against.

Updating a Record

Now this is the tricky part. Let’s say later on in the same workflow process you want to update the same SQL record you just created. To accomplish this just add an update action like the following.

The tricky part is the configuration of the update – look at the screen shot below. Notice how I have to select the BDC Identity Field to get the primary key value to perform the update. If you do not do this the update will fail (sorry do not have the error log handy).

Initially I had set it to use the ID Field on the external content type thinking it would have the generated key from the SQL table as part of the original insert I did early. However that was not the case.

Note you do not need to create this BDC Identity column; it is auto-generated for you.

One more note, I had to do this for a SQL Server table with an auto-generated primary key. I have not tested this out for other scenarios like a web service or another DB platform.

Finally let’s say I need to update an external list item on a record that was created prior to the workflow being started. Add an Update List Item action to the external list like the following. Notice here for the Field I use the ID column from the external content type. We do not have to do what we did earlier.

Conclusions

Why do I get so excited about this? Now I am able to start dumping data out into SQL server to do reporting with SharePoint 2010 Business Intelligence solutions. I can create reports with Excel Services or SSRS. In this example I used SSRS using Report Builder 3.0 to create reports about the execution of my business process.

For instance I created an SSRS report that shows the average duration of steps in the business process. I embedded the reporting into SharePoint and I was off and running.

I then created another report that shows the duration by business process instance.

I even created another report that shows how employees perform.

I just embedded these reports into SharePoint 2010 and I was done. Now I can provide some real business insight into process execution.

BCS Column and Document Set Limitations

Just figured I capture another little tidbit about an environment that I was creating. I wanted to add a column to a central content type that would connect to an external data source using the Business Connectivity Services (BCS). Right now with SP 2010, I cannot achieve this because a BCS column can only be defined at the list level and not at the Site Collection level. So I cannot centrally create the column definition.

This had a cascading effect because I wanted to define this column on a Document Set Content Type I created. There is no problem with adding the external data column to my Document Set definition at the list level. However I have set up rules on my Document Set definition at the site collection level to synchronize the data to all content types within the Document Set. So I will not be able to get that to work unless I define that rule on the Document Set definition at the list level.

Access ULS Log Data in BCS

I found this really awesome solution that a developer or administrator should know about for SharePoint 2010. Many of us has had challenges in the past with accessing, scanning, drilling and flat wasting tons of time digging through tons of ULS logs in SharePoint 2007. Now with SharePoint 2010 there is an easy way to solve this problem many of the out of the box components and services.

The solution is to:

• Use the new SharePoint 2010 feature to store logs in SQL Server database instead of digging through ULS log files on the server.
• Use SharePoint Designer 2010 to build an External Content Type that points to the table.
• Add a BCS web part to allow users to filter through logs – for instance search for a Correlation ID.
• Use ECT list to display all data if you want.

This solution was written by Scott Hillier and can be accessed here - http://www.shillier.com/archive/2010/08/05/Using-Business-Connectivity-Services-to-Display-SharePoint-2010-ULS-Logs.aspx

New SharePoint 2010 Features Part 2 – Enterprise Data Access and Management Features

• Introduction
  
• Enterprise Data Access and Management Features
  
• Development Features
  
• Enterprise Content Management Features
  
• SharePoint Office and BI Features
  
• Administration Features
  

Enterprise Data Access and Management Features

In this section I will discuss many of the new features associated to working with enterprise data with SharePoint 2010. SharePoint 2010 is much more compelling with than SharePoint 2007 with more robust out of the box functionality that allows you to integrate with enterprise data.

Business Connectivity Services (BCS)

Business Connectivity Services (BCS) is probably one of the top five most important new releases for SharePoint 2010. BCS is the enhancement to SharePoint 2007's Business Data Catalog (BDC) which was used to provide read-only access to Line of Business (LOB) data across the enterprise into SharePoint 2007. Note if you see BDC mentioned in the context of SharePoint 2010, it now means Business Data Connectivity.

BCS for SharePoint 2010 supports the follow:

• Supports full Create, Read, Update, and Delete (CRUD). SharePoint 2007's BDC just supported only read operations.
  
• Supports integration with databases and web services and additionally WCF, DLLS and custom domain services.
  
• Provides an extensible model provider which provides a pluggable framework for which developers can write to other data source types that are not provided out of the box with BCS.
  
• BCS supports batch processing for read operations unlike its predecessor which could only support processing a single item at a time.
  
• Can now work with BLOB data type (i.e. streaming BLOBs of data from external systems).
  
• It can support read/write of complex data types and not just primitive types.
  
• Rich API that can be used by custom applications to use data services provided by BCS.
  
• Tools like SharePoint Designer 2010 can be used to build solutions using a declarative model which does not require a developer to write code.
  
• Users can effectively consume and work with data from multiple external data sources in a user interface that they are familiar with such as SharePoint lists, web parts, Outlook contacts/tasks/appointments, MS Word documents, etc.
  
• BCS has a robust cache mechanism for supporting users to work with data provide by BCS offline. Solutions written in correlation with MS Outlook 2010 and SharePoint Workspace 2010 and work with data while offline and then be synchronized when back online.
  
• BCS can be integrated with SharePoint Enterprise Search to allow users to search external SharePoint data.
  

Business Connectivity Service Thoughts

I am really excited to see BCS leveraged as a solution to build composite applications that expose enterprise data instead of building expensive, custom applications. Typically the cost of building these custom composite applications come in the form of heavy design, development and maintenance. In many instances the demise of these custom applications is that a significant investment is made in engineering of the solution but the flexibility and scalability does not meet the future business demand. BCS removes some of these challenges in that it is a framework provided by SharePoint to expose enterprise data so organizations, so they can focus on solving existing and reacting to new business requirements instead of trying to build an enterprise data access framework.

On a side note - I also believe that SharePoint 2007's BDC did exactly what it was supposed to do. SharePoint 2007's BDC was originally intended to be an enterprise search solution that could expose data from web services or databases in the result search of a search. However this was confused when BDC web parts were created to expose enterprise data. When these web parts were utilized business users typically asked to perform full CRUD operations (FYI, it was actually possible to support CRUD with SharePoint 2007 BDC but it was not great solution). Microsoft stepped up to the plate and solved both problems. First they acquired FAST and integrated it in the SharePoint solution architecture and we now have a true best in class enterprise search solution to work with which SharePont 2007's BDC cannot even compete with. Second they created the Business Connectivity Services which can support full CRUD operations which is fully integrated with the SharePoint Foundation.

Enterprise Search (Reference)

SharePoint 2010 includes significant improvements in regards to enterprise search. The biggest is Microsoft's acquisition of FAST (one of the top three enterprise solutions in the marketplace) which has subsequently been integrated into SharePoint 2010. FAST is an extremely rich and deep application that should be dived into separately. Basically you now have two options SharePoint 2010 Search and FAST for SharePoint 2010.

Let's first understand at a glance what in general the improvements to search for SharePoint 2010 are:

• Better Keyword Query Syntax has been incorporated into SharePoint 2010 Search. Such new features as Boolean, proximity, synonym, wildcard operators, prefix matching for free-text, property restriction queries and property operators for non-text fields have been incorporated. What this basically means is creating complex queries to run against the search service is much easier in SharePoint 2010. Note that the new Keyword Query Syntax is supported for both SharePoint 2010 Search and FAST for SharePoint 2010.
  
• The search web parts are unified so they support both SharePoint 2010 Search and FAST for SharePoint 2010. This will ensure there is some consistency in configuration, management and user experience regardless of which search engine you use. Note that the search web parts are built upon the federated search object model.
  
• The search results page new includes a new refinement panel that gives the user a summary about the search results and provides them the ability to quickly filter results based on the taxonomy of the data.
  
• New connector frameworks for both SharePoint 2010 Search and FAST for SharePoint 2010 have been incorporated to support indexing data external to SharePoint. SharePoint 2010 Search utilizes the new Business Connectivity Services (BCS) while FAST has its own highly scalable connector framework. Both of the connector frameworks are significantly better than the BDC used for SharePoint 2007.
  
• Relevance has been enhanced for SharePoint 2010 Search while FAST provides an extremely rich and advanced relevance model. For SharePoint 2010 Search, we now have the ability to create customized ranking models to improve result relevancy by collection, content source and data verticals.
  
• Federated Search is again supported by both search options for SharePoint 2010. If you are not familiar with it, what Federated Search supports is the ability to display search results that were crawled by other search services such as Bing, Google, etc.
  

Enterprise Search Thoughts

I personally believe that FAST for SharePoint 2010 is a game changer and one of the compelling reasons why organizations should upgrade as soon as possible. The features and functionality of it are unparalleled and it is a mature product that has been injected into SharePoint. Content and data stored in or out of SharePoint will be easily accessible utilizing highly advanced relevance models. To learn more about Enterprise Search and FAST in general to understand the value proposition of it, please read this blog series.

List Enhancements (Reference)

There are several new features that have been introduced into SharePoint 2010 to support their initiative to better handler larger quantities of data:

• List throttling – To ensure that open ended queries for list data to not effect performance of the site for other users. Thresholds can be set to ensure web application level.
  
• Referential Integrity – In previous versions of SharePoint it was possible to use lookup fields or custom events to enforce referential integrity betweens items between lists. With SharePoint 2010, referential integrity can be set up directly between two lists and SharePoint will manage it from there forth.
  
• Enforcing Uniqueness in Column Values – There is now the ability to select which column in a list will define uniqueness. This does not have to be the internal ID column of the list.
  

List Enhancements Thoughts

The ability to efficiently and effectively handle list with large amounts of data is a very important new feature to SharePoint 2010. In SharePoint 2007 it was possible to create efficient solutions that had SharePoint lists with more than 2,000 items, however complete custom code solutions had to be written. I still have some hesitancy to make sure that data management be well thought out before dumping extremely large amounts of data into a SharePoint list (my old blog entry). If data is highly relational or must be reported on, it is probably best to use SQL Server and then expose the data to SharePoint using the new Business Connectivity Services.

Still the new ability to manage very large quantities of data is not just for SharePoint Lists, it is also for managing large values of documents in a document library. It is very exciting to see this and will be very interesting to see how SharePoint lists evolve as a new enterprise resource for data storage.

Microsoft Sync Framework and SharePoint Workspace 2010 (Reference)

Microsoft Sync Framework is a unified synchronization architecture that allows for bidirectional communications between SharePoint and Line of Business systems. This framework as a specific focus on synchronizing SharePoint data stored in either lists or document libraries. This architecture is used as the backbone for SharePoint Workspace 2010 which is the replacement for Microsoft Groove 2007. What this tool supports is the ability to work with data stored within SharePoint in a disconnected environment. This includes lists, document libraries and even data within BCS.

Microsoft Sync Framework Thoughts

This again is another very exciting release for SharePoint 2010 and provides a tool that no CMS competitors are providing. Users now can work while travel, we can ensure they have the latest information locally, etc. Groove was an initial release given this is Microsoft's second release of this technology I have high expectations that it will be a very effective tool.

User Profiles and Social Data (Reference)

There are several aspects to the new features that have been integrated into SharePoint 2010 to support social computing. There are new APIs discussed in this article about activity feeds, social data and user profiles. Activity Feeds API supports the sharing of data between users. Social Data is a new API that has been created to facilitate social tagging, rating and comments for content that is stored within SharePoint. The User Profiles API has been augmented to support both organization and user profiles.

The new social computing features of SharePoint 2010 support scenarios such as (this is not an entire list):

• The ability to find commonalities between two user profiles.
  
• Providing the ability for users modify profile data
  
• Adding links to your profile.
  
• Adding colleagues to your profile.
  
• Social Data Statistics Web Part that displays social statistics such as each URL that has been tagged and their terms, the number of times that term has been used in a tag, each user who has added a social tag.
  
• The ability to approve who is marking you as a colleague.
  
• Ability to send content to colleagues.
  
• Ability to pull in external social computing data like from LinkedIn.
  
• Ability to create organizational charts based on enterprise data.
  
• Ability to rank, tag and comment content within SharePoint and use that information to the most relevant information to users based on the data's actual usefulness.
  

User Profiles and Social Data Thoughts

At first glance this does not really seem like much however this new foundation provides a foundation for building social computing business solutions that was significantly lacking in SharePoint 2007. I cannot tell you how many clients wanted to incorporate social computing into their Internet, Extranets and Intranets. Common use cases were who is the SME for what I need and who do I contact them? What content is considered to have the best information? And so on. To support this in SharePoint 2010 typically highly customize solutions had to be created.

I know when I first started doing SharePoint 2007 projects I was little skeptical of social computing and how it applied to the business world. However client after client I saw that this was significantly important to companies and I even saw how it would be important to me at my company. Today I rely on the fact that I have been at a company for several years, I know everyone, I have seen people come and go, I have seen lots of projects and I know where to go find something or who I need to call to find it. But if I am new, how do I find what I need? We have talked about search, but that is only one piece of the puzzle. There is still an element of finding information which cannot be gleaned by a search engine. The data indexed is only as good as the metadata that supports it, and if there is no metadata, it is not relevant.

Another scenario is very geographically dispersed or remote environment can highly benefit from the adoption of business social computing. We are so used be able to crowd around the water-cooler, well we can now do that with SharePoint. With the new Social computing features we can find out who is doing what and when and if you should chime in with some information that stop that person from potentially making a costly business decision.

Now we are empowered with SharePoint 2010 to come up with these new business social computing solutions and I am truly excited to see how this is used over the few months.