Showing posts with label BI. Show all posts
Showing posts with label BI. Show all posts

Friday, October 11, 2013

Power BI Data Management Gateway and Resources

Power BI continues to be a hot topic that many people have been asking me about.

As you may know, there was an announcement in July about the new Power BI in Office 365 that is coming. Here is more information about that announcement - http://www.astaticstate.com/2013/07/office-365-power-bi-announcement.html

A lot of people have been asking for more information.

First if you want to try out the Power BI Preview, please go here to sign-up for the preview - http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx

Second I have had many people ask me for some good resources. If you want to learn more about Power BI with Power Query, Power Pivot, Power View and Power Map here are two really good links - http://technet.microsoft.com/en-us/library/dn198234.aspx and http://technet.microsoft.com/en-us/library/dn198234.aspx. If you follow the sub links of these articles you will find a ton of good information.

Third, the biggest question I still get is does the new Power BI support the ability to connect to line of business databases. This is probably one of the most exciting things being introduced because up till now because Excel Services could only work with the data contained in the spreadsheet. The answer is the new Data Management Gateway. It requires to you to install a gateway on-premise that will make a secure connection to SharePoint Online. It push data from on-premise (on demand or a schedule) to your spreadsheets stored in SharePoint Online to render the reports through the browser. You still have the ability to open the Excel client with the add-ins for Power Query, Power Pivot, Power View and Power Map.

Here is a really good reference for you to start reading and all of its associated articles - http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/power-bi-admin-center-help-HA104078330.aspx?CTT=5&origin=HA104078557. There are articles that cover what the Data Management Gateway is, how it is installed / configured, how to create data sources using OData and then how to connect them into Excel. There is even a really interesting article about how SSIS can be utilized as a data source for Power BI. As well is a good article on health reports and utilization of the Data Management Gateway.

Thursday, July 11, 2013

Office 365 Power BI Announcement

Introduction

There was a really exciting new announcements made this week by Microsoft for Office 365. This week they announced Power BI for Office 365. This is a huge leap forward to self-service BI. This is something I talk with a lot of people about. Organizations know Excel, and with Excel Service you can create powerful dynamic reports and dashboards in the same tools that organizations have expertise in.

The Office 365 BI Power tools

If you know SharePoint Online, you know there is Excel Services which provide a powerful BI capability today. Additionally today you may know about Power Pivot and Power View. New additions are Power Query and Power Map.

  • Power Query – is a new capability which allows people to easily search and access data that may reside publically or within their organization with Excel.

clip_image002

  • Power Map – is a new visualization tool that good for mapping, exploring and interacting with data.

clip_image004

  • Power Pivot – is a solution in Excel to build complex data models. This supports the ability to in-memory analytics and modeling against large volumes of data.

clip_image006

  • Power View – is a powerful solution in Excel to build interactive charts, graphs, KPIs, etc.

clip_image008

Together these tools can be brought together build a BI experience right in Office 365.

But there is more…

Wait, there is more exciting stuff here.

  • New Refreshing you data – This is long been a discussion topic I have had with organizations reviewing Office 365. Up to this point, Excel Services in SharePoint Online work great but it only has the ability to work with data in the Excel spreadsheet itself. There were some creative solutions out there to fresh your Excel spreadsheets with data in SharePoint Online (http://www.astaticstate.com/2013/03/sharepoint-online-bi-and-connecting-to.html). Now there will be a new capability that will use the Data Management Gateway which allows you to refresh data. This is super EXCITING! Now you can have Excel Services running in the cloud that securely access your line of business data repositories.

clip_image010

  • New BI Sites – There have been dashboard and reporting site templates around for a while now. With this new release of Power BI, there will be a new site template available to support these activities. The really cool thing this does is allows you to centrally manage your reports and data connections. You can then re-use these data connections across reports. Remember you can still add in other SharePoint capabilities to create dashboard that incorporate social, content and business processes.

clip_image012

  • New Natural Language Query capability – There is a new natural language capability is available that will allow every data end users to enter statements and then query the reports, without having to use a bunch of fields and controls. This is really interesting as it will allow end users to go in and discover data without having to engage a developer.

clip_image014

  • Easy to Access – Additionally these new Power BI solutions will be available through apps and browser to allow users access to the reports regardless of the device they are using.

clip_image016

Commitment to the Evergreen Service

This is yet again another example of how Office 365 makes good on its commitment to being an Evergreen Service. I speak with organizations about this all the time. You do not have to wait for a major release to get new features and capabilities. With the Office 365 cloud, organizations can get new capabilities quickly and start seeing immediate returns on their cloud investment.

References

Thursday, March 15, 2012

Updated Independent Analysis on Microsoft Productivity Solutions for Prem and Cloud

There have been a lot of updates with Gartner analysis for the Microsoft Productivity stack and other related stacks that I work with. Specifically SharePoint, Office 365, Lync and BI have all been getting great independent reviews. The important take away that I discuss with my customers is that all these top quartile rated technology is delivered on a single, integrated solution stack. They are NOT hodgepodge solutions brought custom integration.
Many of the reports and newest reports can always be accessed from here - http://www.microsoft.com/presspass/itanalyst/default.mspx
SharePoint and Office 365
As you can see we have been great assessments for enterprise content management, enterprise search with FAST for SharePoint 2010, great recognition as portal provider, social computing and business process analysis with Visio Services and SharePoint workflow. Much of this capability is delivered through the Office 365 cloud as well.
SQL Server and BI
As you can see these are very recent reports giving us good reviews in the areas of BI and Data Warehouses. I think the big take away is again total cost of ownership (TCO). We give strong BI solutions and customers do not need to make significant investments to deliver BI through the same SharePoint investments discussed above.
Exchange and Office 365
For email with Exchange either delivered on-premise or through the Office 365 cloud we continue to be evaluated as a leader. This really speaks to our versatility in allowing customers to select the most appropriate place and get the same level of end user experience for business users.
Lync and Office 365
Microsoft is again recognized as a leader in all these types of solutions with Lync which can additionally be delivered through the Office 365 cloud. Our Unified Communications solutions are completely integrated in Office, Outlook and SharePoint allowing users to quickly transition between tasks they are working on making them highly productive. These capabilities are extremely useful for telework or communications between people spread across large geographies.

Wednesday, January 4, 2012

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.
image
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?
image
However within a few months (after some very heavy business and user adoption) we have the following.
image
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.
image
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.
image
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.
image
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.
image
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.

Friday, August 26, 2011

SharePoint 2010 Independent Market Analysis

Updated - 3/2012 - View new blog posting here

I get asked a lot about reasons to move to SharePoint 2010. I simply state, look at the how the market evaluates SharePoint.
Here is a great link managed by Microsoft that showing Gartner and Forrester reports - http://www.microsoft.com/presspass/itanalyst/default.mspx

As of right now, I recommend reading the following:
I suggest stay up to date with this site to find the latest reports.

Sunday, August 7, 2011

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”.

clip_image001

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.

clip_image002

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.

clip_image003

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.

clip_image004

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.

clip_image006

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

clip_image008

I even created another report that shows how employees perform.

clip_image009

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

Saturday, May 15, 2010

New SharePoint 2010 Features Part 5 – Office and BI Features

SharePoint Office and BI Features

In this part of my new features of SharePoint 2010 blog series, I am going to focus on Office and Business Intelligence features of SharePoint 2010.

Excel Services (Reference and Reference)

Excel Services was introduced in SharePoint 2007 to much fanfare. It provided a great initial release however there were several limitations to it which hinder the highly advanced Excel power users when they tried to place their Excel Spreadsheets into Excel Services. However there have been several new improvements that will make Excel Services for SharePoint 2010 even more compelling. Two of the biggest changes are first we can now edit Excel workbooks in Excel Services programmatically. Second users can now edit workbooks within SharePoint and edit the data directly in the browser.

Excel Services Thoughts

It is great to see Excel Services mature as it is the second version of this solution offering by Microsoft. What we are going to start seeing is a shift of the standard Microsoft Office desktop being available over the web. It will never be as fully robust as running Excel locally as a client but what this does enable is better communication of business data. There are so many businesses that have mission critical data stored within Excel with no way to distribute or control it in an efficient manner. I have heard the condescending remarks before by software consultants who say "how can you be using Excel instead of a custom application to manage such mission critical data". My response is, live the day of life in the corporate world where IT resources may not always available. In these many cases, business users (Information Workers) are forced to come up with solutions with readily available software and one of them is Excel. I am really glad to see how this is being used.

One thing I think is needed to ensure success of Excel Services within an organization is an effective governance strategy for rolling it out across the enterprise. This would include, providing the proper infrastructure, correct SharePoint logical architecture, information worker training, rules for deployment, maintenance, etc. I think the biggest thing is lack of education of business users that they have such a powerful solution available for them to expose their data.

Access Services (Reference and Reference)

The goal of access services is basic – to provide the ability to make Access database available over the web. Access has been a tool that helps enable non-technical business users the ability to create business applications. Now users can continue to create these applications with Access 2010 on the client and then publish them to SharePoint 2010. The full functionality of Access 2010 on the client is not available over the web through SharePoint as there is only a limited subset of full client object functionality.

Access Services Thoughts

I am really excited about this new feature and I cannot believe I am saying that. I say that because from a professional services perspective we have had a lot business where have moved Access solutions to .NET/SQL Server based solutions because Access applications could not scale.

I think the vision of this is great but since it is a first release and there is only a limited subset of functionality initially available it will suffer from the same issues Excel Services in SharePoint 2007 suffered from. The biggest issue is that Access developers are very advanced and require full features which will not be available right now. That notwithstanding, I can name more than two past SharePoint 2007 clients of mine who could use this even though it is lightweight. Their business users know MS Access and they do not mind functionality limitations if they maximize the investments they have made in their current staff and they build simple applications.

I see down the road this as another option to allow business to create codeless applications that solve tactical business challenges that companies face every day.

InfoPath Form Services (Reference and Reference)

In SharePoint 2007 we were given the ability to use InfoPath within the browser and we subsequently saw a lot of adoption of InfoPath to create data capture forms for business processes. SharePoint 2010 has expanded the features of InfoPath to include the following:

  • Enhance SharePoint List Forms – This new capability allows you to very easily replace create, edit and view list item forms in a SharePoint List with a completely custom InfoPath form. This effectively allows you to add additional rules, load in data from other locations, incorporate business automation into lists, etc.
  • One Click Publish – You no longer have to click through the entire publishing wizard after you publish the form one time.
  • SharePoint Workspace 2010 – As part of this series I introduced SharePoint Workspace 2010 which is the replacement for the MS Groove solution. SharePoint Workspace uses InfoPath 2010 as the editor for all SharePoint list and library data that entered while offline. When SharePoint Workspace is brought back online, the data in the forms are synchronized back to SharePoint.
  • SharePoint Designer 2010 Integration – InfoPath 2010 is now integrated with SharePoint Designer 2010. We now have the ability to define and publish InfoPath forms along with a business process that is designed in SharePoint Designer 2010.
  • Business Connectivity Services (BCS) Integration – InfoPath 2010 has direct integration with BCS to expose enterprise data through InfoPath allowing for read, insert, update and delete operations.
  • InfoPath Web Part – There is a new InfoPath Web Part that allows you to embed and InfoPath form directly into a web part page. Previously we had to go to a special web page that could render an InfoPath form over the web or we had to write a bunch of custom code. Now the InfoPath form can be displayed along with web parts that may provide complete different functionality.
  • Deploy .NET Managed Code to Sandboxed – Publishing InfoPath forms with .NET managed code no longer require an administrator to publish the form through Central Admin. You can now simply publish the form using as a Sandbox Solution which I introduced this earlier in this series.
  • New Web Form Controls – With the previous version InfoPath, there are several controls which were not available when the InfoPath form was web enabled. With InfoPath 2010 we have nine new controls we can use.
  • Better Support for Open Standards.
  • Better support for digital signatures.
  • Integration with SharePoint PowerShell cmlets
  • Integration with SharePoint 2010 health and analysis tool called SharePoint Maintenance Manager which automatically checks for configuration, performance and usage issues. There are specific rules for SharePoint 2010 for monitoring.

InfoPath Form Services Thoughts

If you read my blog or talk with me you will find out I have been a huge proponent of InfoPath since Office 2003. Obviously I have some important rules that I follow, most notably if .NET managed code is needed stop using InfoPath move to another platform. However InfoPath is such a great tool because:

  • It is XML based.
  • Can be rendered over the web through SharePoint with no client desktop footprint.
  • It is very easy to create simple to medium level data capture forms.
  • Does not require a developer.
  • Very cost effective.
  • Very easy to hook into web services to persist data externally.

I really believe developers just get too wrapped up not have a full IDE to work with and just cannot architect a solution well.

I am really very very very excited to see these new features and expect great things from InfoPath for SharePoint 2010. I really think this is a game changer for organizations to build much more powerful applications and solutions within the SharePoint platform.

Visio Services (Reference)

For SharePoint 2010, there is a new service called Visio Services which provide the ability to load, display and interact programmatically with Visio 2010 documents published into SharePoint.

From a programmatic perspective you have two options. First you can create a data provider for Visio which can be used to load in external data in the Visio form. Second ECMAScript Object Model provides programmatic access to the objects and data within the Visio diagram allowing you to create mashups, create overlays on the diagram, respond to mouse clicks, allow for panning and zooming, etc.

Visio Services Thoughts

I will be very interested to see use cases on how Visio Services will be used with SharePoint 2010. I have had clients ask me in the past, how they can make their Visio diagrams available over SharePoint without having to deploy Visio to every desktop. Well that is now solved. However this release goes well beyond that in that Visio is being made into a data driven application that can graphically display data. I think this again just adds to the richness of the SharePoint platform as another technology we can use to solve business demand.

Word Automation Services (Reference)

Word Automation Services is a new application service to SharePoint 2010. What this service provides is unattended, server-side conversion of documents into formats that are supported by Microsoft Word on the desktop. What is basically does is replicated the Save As functionality that you are used to in Microsoft Word client.

Word Automation Services Thoughts

At first glance as a developer you may not see the immediate value of this however there are several scenarios (Reference) where this can be very helpful. For instance let's say every week or month a large set of invoices need to be generated from a custom database and saved out as Word documents on SharePoint, this service could be utilized to support that. Another scenario would be once a Word document has gone through approval process, when it is complete PDF of that Word document needs to be generated.

PerformancePoint Services (Reference)

When SharePoint 2007 first came out they provided some light weight but very effective tools for Business Intelligence (BI) with SharePoint. Microsoft acquired a company called ProClarity and rebranded their dishoarding BI solution as a product called PerformancePoint which was eventually made part of the SharePoint eCAL. This was done because everyone who wanted PerformancePoint wanted it integrated within SharePoint.

For SharePoint 2010, PerformancePoint has been completely integrated as an enterprise service. PerformancePoint services allow users to create interactive dashboards for displaying KPIs, scorecards, reports, filters, etc. As well there is a full API that developers can use to create highly customized solutions. The architecture of PerformancePoint (Reference) is pretty robust and like I mentioned completely integrated into SharePoint 2010.

PerformancePoint Services Thoughts

I openly admit this is one of the areas of SharePoint I have little knowledge of and feel bad about because I know PerformancePoint is a pretty good solution with lots of features for providing dashboarding solutions. I personally have not used much of PerformancePoint in SharePoint 2007 and do know that most of the challenges associated with getting it to work were around configuration. Our SharePoint team would get tons of questions in regards to Kerberos configuration and such. Now that PerformancePoint is integrated within SharePoint many of these challenges will go away.

Plus given this is the second generation of Microsoft's solution offering I highly anticipate that it will be a very strong offering. Microsoft has made a large investment into this and Dashboard solutions pretty much go hand-and-hand with SharePoint as this is where users want to access their data.