Creating a Rich, Secure, and Centralized Employee Dashboard in SharePoint

Introduction

One of the most powerful abilities that SharePoint possesses is its ability to take data from multiple different data sources and display it in a single centralized area.  This way, users do not have to log into separate systems or have permissions configured to those systems in order to access data that applies to them.  So, instead of having to log into a payroll system to get paystubs, a vacation system to find out vacation status, an entitlement system to find out the status of entitlement claims, profile info to ensure your contact information is correct, you can view all of this data in one place, with convenient links to the request forms on that same dashboard page.


This has the added advantage of not having to manage user access to all of those seperate systems.  No need to manage accounts and access to multiple different applications as they are all exposed through SharePoint.


However, there is a key risk here that prevents the development of such systems in some organizations.  The data exposed by these systems is of a sensative nature, we have to ensure there is no way for any users to be able to access another users private information.  This post walks through the steps necessary to expose that information in a secure mannner.


It does not dig deeply into any of the technical components of the system, it is an overview of the considerations and processes used. 

Employee Dashboard Vacation Page

Step 1: Restrict the information exposed

Rather than directly exposing data tables through to SharePoint for access by employees, we instead expose just the data that employees will access.  One way to accomplish this is through the usage of views and stored procedures built on the database of the hosting systems.  These data objects can then be accessed by the secure store service within SharePoint to pull the data through to SharePoint. 


If we are accessing the data from multiple systems however there is a better way.  Create a sql server hosted database independent of the source systems, and design a small database with only the tables and data repositories necessary to host the data we are choosing to expose.  This gives us the advantage of having an independent database accessed by SharePoint, rather than SharePoint directly accessing any systems core database.  So no load on the host systems database, no access by SharePoint to any source systems database.  The secure store service is instead given access to this stand alone independent database.
 
This independent database can be populated by a routine data transform built using Visual Studio BIDS.  Depending on how current the data needs to be, it can be a nightly transform, or a more frequent data transform for those areas that require more current data.


This of course restricts our data retrieval actions to read only, we won't be leveraging the abilities of SharePoint to also directly update data within an external applications database.  In real world terms, we don't usually want to be able to modify the data in a database associated with a specific application such as payroll anyway, the payroll application itself is used to update and modify data. This maintains the data integrity managed by the payroll system, but still allows us to expose key data to the end user through SharePoint.

Step 2: Map user data to windows login

One key element of the independent database is the ability to map the current users Windows Account login to that users data.  SharePoint will pass through the current users domain and username, we need to be able to use this information to extract that employees records.  Designing a sql server table in the independent database that maps the login to a universal identifier within your systems, such as employee number, may be the best approach.  Of course, if your external systems already map the users login to their data, then you just need to extract that data as part of your transform.


The end result of this step is an independent sql server database that holds all of the information from disparate systems that we want to expose to the end user, as well as some way to map the users windows login to the appropriate data for that user.


This mapping is a critical part of ensuring only the data that should be viewed by the end user is viewed.

Step 3:  Create stored procedures return filtered user data

We only want to expose the currently logged in users data through to SharePoint, regardless of that users SharePoint based permissions.  This is done by having all data filtered by username.  We will be binding the retreived data to a list in SharePoint that is viewable by the end user.  So, when creating the stored procedures, we need to keep a number of things in mind:

1. Each must accept the username as a parameter, and return only data that relates to that username.
2. Each set of data retrieved needs both a read list and corresponding read item stored procedure.  The read list stored procedure is filtered by username, and must not only return rows that match the current users username, but also a primary key or identity value.  The read item stored procedure takes this primary key and must only return a single value from the data source.  You can use two fields combined as a primary key if necessary.
3. Name your stored procedures clearly so that when you expose them through designer, it is clear which are related read list and read item stored procedures, and what data they retrieve.
4. If a large number of rows could potentially be returned for a single user, you may wish to restrict the data returned to the 100 most recent records, or another appropriate restriction (last year only).  Remember, we are trying to expose only truly useful information to the end user, so, if they are attempting to access data from 8 years ago, the SharePoint dashboard may not be the best choice for this.  We want current useful data.
5. If you split dates into month and year, such as pay dates, entitlement dates, and return those values, you can use those values to group the results in a SharePoint list.  This is preferable to filtering by date range which is not only tricky to set up but also not the most efficient way of retrieving data from the user end.  See also this post, and this one.

Step 4:  Add Auditing to your Stored Procedures

Since the data stored in this system is sensitive, you may want to audit all access to this data.  Create a database table for each system that your database exposes, and add auditing sql to the beginning of each stored procedure that records:

1. The stored procedure being called.
2. Any parameters passed including username.
3. The data being returned.

This allows quick tracking of any user that accesses data in the system, and can be used to track potentially unauthorized data access.  It is also userful when setting up the system as you can review exactly what is called by SharePoint when various lists/pages are populated.

Step 5:  Create database access account and configure secure store services

Once we have the independent database set up with all tables for our data, we will need to create an account that can access that database and retrieve the data.  We will be using stored procedures to retrieve the data, so the account will need execute permissions on these stored procedures as well as the ability to connect to the database.


Once this account has been created and tested, set up a meeting with your SharePoint farm administrator to configure the secure store service.  The farm administrator can pull up the configuration screen that creates a new target application in the secure store service.  You will need to configure the target application to use a sql user name and password for access (mask the password), and add the specific group of users that will use this credential for accessing the data through SharePoint.  If you are configuring this system for all of your employees, the "all users" group is used.  Basically, this is the group that will use this credential to access the independent database.  This is completely transparent to the end users, is encrypted for security, it is used instead by SharePoint for data retrieval.


Also add yourself, or the person who will be configuring external content types through designer as target application administrators.  This is necessary to allow SharePoint designer to access the secure store service through designer when creating external content types.


Once you have created the target application, you encrypt the connection information using the key management components, and then set the credentials (username and password) of the account you set up previously to access the independent database. 


Since security is key, the farm administrator for your SharePoint farm does not need to know the password to the external independent database.  The dba or other technical expert that set up the external database can instead enter the masked password in the "set credentials" form in SharePoint Central Administration and save it (this is why a physical meeting is necessary, otherwise, other settings could be entirely handled by the farm admin with the dba giving connection information). 


Just because we are exposing this data through SharePoint does not mean that we need to increase the number of people who can directly access the data as a whole, this ensures that even a farm admin cannot access the connected database directly.


Make note of the name of the secure store service target application as we will be using it within SharePoint Designer.

Step 6:  Create external content types in SharePoint Designer

If you have configured your target application correctly, and you have set yourself up as a target application administrator, then you should be able to open SharePoint designer and add a connection to that target application using the external content type interface.  Once you have added this connection, you can use the data source explorer to open that connection and see the tables, views and routines you have exposed in the independent databases.  Since we used stored procedures, you will see them listed in the "routines" section. 


If you have named your stored procedures well you should be able to set up your read list and read item operations for each content type you create.  Remember to set up each read list operation using the user context filter (this is what passes in the current users account name) and identify the identity value within the Designer interface.  The read item operation uses this identity value to retrieve individual records.

Step 7:  Connect to the external content types in SharePoint Client

You will want to create a page or pages to host the dashboard elements of the various systems exposed through SharePoint.  Add the external lists (Business Data Lists) to your pages, and configure the web part components to hide menus and other options.  Use content editor web parts and other components to add instructions and data context information to your dashboards.


This stage takes a great deal of tweaking and experimenting in order to get the data to display in a meaningful and useful way.  If the basic SharePoint list options available in the SharePoint client are insufficient, you can use SharePoint designer to build highly customized views of the data using the business data list web part.  Be aware however that while using this method can give you great control over the row by row appearance of the data being displayed using xslt, html and javascript elements, it is also time consuming and really only the domain of a SharePoint Designer expert. 


Once you have exposed this data to the end users, they will inevitably detect potential errors or issues with the data (wrong vacation dates, incorrect entitlement amounts).  This can be supported by linking to request forms associated with those systems for updates, and/or by providing phone numbers or other contact information to allow confirmation of information.  You can even create standard internal SharePoint lists with the key data needed to make an update, expose the add item form, and use a workflow to send this information to the appropriate group.


You can also use standard SharePoint document libraries to include employee manuals, system instructions, vacation and sick leave forms and other components all in the same site.  Think of what a typical employee may need to do through the course of a year, and centralize all of the information, instructional material, and request forms in one place.

Step 8: Optional, Expose SSRS reports through to SharePoint for charts and other visualization elements

If you have an SSRS report server it is possible to deploy SSRS reports through to SharePoint directly through embedded report libraries.  Any reports created against the independent database can be relatively easily exposed through SharePoint if the Reporting site features are enabled.  Set up a report library and a data connection library on your dashboard site, ensure the reports also filter by username (report server side), and embed the reports using the report viewer web part in your dashboard pages.

Conclusion

The end result of this effort both provides a centralized area for end users to find the majority of their employment data in one place, no need for multiple logins and searching disparate systems, and, if you expose sql server reports, you can also create visual dashboard elements that simply describe the data in a quick to understand and intuitive manner. 


And, by using these five security considerations:

1. Expose only the data that is needed.
2. Use an independent database separate from the source databases.
3. Filter ALL results by username on the DATABASE side (including reports if you use them).
4. Audit every call to the database. 
5. Do not provide the SharePoint farm admin the password to the external database used in the secure store service.

We can ensure the data exposed to the end user is only ever the data associated with currently logged in user account, even users with elevated permissions up to farm admin level cannot retrieve data other than their own using the client.


End Result?  We have achieved a rich, secure centralized area for key employee data along with supporting information and links to request and other forms, and have reduced the amount of independent system management and training on those systems needed.  A real increase
in efficiency from both the user and the support end.

Document Content Type not saving multi line fields when Editing Properties

We recently ran into an issue with Metadata columns in SharePoint used by document based content types.


A custom document content type was created with multiple fields for both classification and workflow tracking in the system.  Two of these fields were multi line fields to allow comments during the review cycle.


When a document library was associated with this content type, we could correctly update all of the fields using the "Edit Properties" and see them reflected.  All except the multi-line fields.  You can fill out these fields, click save, and never see the fields updated with the values entered.


Luckily, the fix ended up being pretty simple.  Within the DOCUMENT LIBRARY that is hosting the content types (not the content types themselves), change the multi line text fields to single line of text fields and save.  Then revert those fields back to multi-line fields, and you can now enter data into those fields and have them save.

Why I Don't Recommend Using Custom .NET Code in Most SharePoint Projects

Introduction

Modifying SharePoint with custom .NET code is a powerful tool indeed, allowing the auto generation and customization of SharePoint sites and toolsets to a deep degree.  However, in practical terms, I have yet to find a system in the 8 years and numerous clients/projects I have worked on that I would recommend this approach.  Why?

Disclaimer

IF you have a full time dedicated SharePoint support team in your organization that includes a SharePoint farm administrator, various SharePoint experts in each of the specializations (Design, Business Intelligence, Architecture, Business Analysis) then the long term hiring and usage of a SharePoint Developer with deep experience will pay dividends for you.  The problem being of course, this does not represent the majority of SharePoint clients and projects, at least in my experience of 14 years in the field, starting with coding .NET followed by deep SharePoint consultation since SharePoint 2007.

The SharePoint Complexity Stack

One of the key values of SharePoint is its ability to adapt over time as both understanding of system capability and client needs evolve.  It is not a static platform where, once configuration is done, it will typically continue to serve your needs without assessment and further configuration over time.  In fact, its adaptability is one of its key strengths. 


Whenever I have a project upcoming that I need to do an assessment on, I consider the SharePoint complexity stack.  It starts with the simplest to configure and maintain components, and then, as business needs and assessment of current practices inform, we add complexity only where needed and required to meet those requirements.  The stack, from simplest to most complex, follows.

Client Level Configuration

A surprising amount of a typical SharePoint project can be successfully completed using only the SharePoint client.  Custom lists, binding and relating lists, pages, document libraries, content types, views, filtered views, minor look and feel modifications, built in workflows, SSRS report exposure can all be completed without any toolset beyond the SharePoint client.


If a SharePoint project can be completed that is entirely generated using only the client level tools, you not only have a project that is rapid to develop, you have a project that, and this is key, is client maintainable after the project has been released.  The clients will require some basic SharePoint training on permissions, lists and page content updates, but this is well within the range of many client users and training for this specific skillset is widely offered.


Even a project that has other customizations that go deeper in the complexity stack benefit from having as much as possible accessible and maintainable through the SharePoint client.

SharePoint Designer Based Configuration

Once the client based toolset is exhausted, and business requirements force deeper functional needs, the next step in complexity is to use designer.  The typical areas I use designer for are deeper custom workflows that are not "out of the box" level workflows, and the generation of custom look and feel interfaces for various elements (master pages, custom css code), and customization of SharePoint form control behaviour, particularly the forms associated with custom lists that need to behave in a very specific way (get different data from different users, cascading drop down lists, data sequence enforcement).


SharePoint Designer 2013 is forward compatible with SharePoint 2016 so is still a valuable toolset to know and understand.


Even with this toolset at hand, we still continue to focus on having as much of the system as possible end user maintainable through the SharePoint client interface.  That way, changing data needs and requirements can be handled by experienced end users without needing external expertise or expensive updates to the system.

InfoPath and Custom Web Forms

In some cases, the interface with data required by SharePoint for client usage requires multiple SharePoint based data sources and dynamic forms that change depending on the prior field web control selection of clients (the form provides a different question depending on the result of the prior question).  While much of this can be accomplished with the form customization tools within Designer, InfoPath is also a valid choice for creating these complex forms and is fully supported up to SharePoint 2016. 


InfoPath however is typically fairly intimidating for end users, setting up and configuring data connections has gotten to the point where you require real technical expertise, the views and conditional components of the forms are beyond most end users capability, so an InfoPath forms based system needs to meet requirements and be mostly static after release to the clients, unless they plan to have a long term InfoPath forms expert at their disposal. 


And InfoPath can be tricky to deploy between multiple environments.  So, yes, if there is no way to accomplish functionality with the first two toolsets available in the technology stack, InfoPath is a valid choice.  But it is not typically end user maintainable.

SharePoint Designer and Central Administration Configuration

Certain SharePoint customizations require components to be configured at both the designer and SharePoint Central Administration level.  A typical example of this is the Business Data Connectivity Service and Secure Store Service.  If we want to surface data within SharePoint from one or more external SQL Server based data sources, then this is the best option.  This requires more coordination between the SharePoint farm administrator than simply turning off and on features, and deeper knowledge of both Designer and SharePoint Central Admin.  Customizing and using the many services provided by SharePoint can meet business needs in many circumstances, but the deep knowledge and coordination required between the Client/Designer/Central admin components does add complexity.

JQuery based SPServices

Here is where the SharePoint customizations available through .NET start to overlap with the capabilities provided by the SPServices toolset.  SPServices is a rich client based set of components that allows the generation of lists and libraries, along with related components, rich web form based interactions, and completely custom web forms that enhance or allow new functionality over those available in the client interface.  One such example is on this blog:


Adding a User to Multiple Groups


The key advantage I see in using SPServices over custom .NET based code is that the web page functionality is all client side, and, other than the initial download of the Jscript libraries when the user first encounters the functionality, has no effect on the efficient performance of the SharePoint Server itself.  No need to worry about memory leaks, sandboxing, testing customizations on every SharePoint service update done on the server side.  Optimizing a SharePoint Farm to serve tens of thousands of clients is difficult enough without adding the additional considerations of the wide scale custom code deployment.

Customization using .NET Based Modifications

Extremely precise or detailed business requirements may require the time, effort, and expertise necessary to develop custom .NET code.  But before any client or project should go down this path, there are three key considerations:

Expertise Availability

A deeply experienced .NET developer already has a number of employment options and, if their resume has a number of successful large based projects on it, they typically do not have trouble finding work.  SharePoint can be tricky and adds multiple additional layers of complexity to an already deep and complex .NET framework.  And, in addition to learning the new toolsets and deployment options of working with SharePoint, the developer needs a deep understanding of the SharePoint client model, its capabilities, and its restrictions.  It is not surprising that many .NET developers choose to stay as strictly.NET developers, and only a small portion end up being SharePoint developers (in my experience).  If you do manage to find an experienced SharePoint developer who has both at least 5 years foundational skill in .net, and at least 3 years experience in creating customizations for SharePoint using .NET (the minimum I would recommend to "hit the ground running"), then it is most likely going to be expensive.

Maintainability and Migration

Unless, as suggested before, you hire a full time SharePoint developer to both build and update your .NET based customizations in your company/institution, then the majority of what they build is not going to be able to evolve in a significant way over time, a key component of SharePoint's value.  And, when you decide to upgrade to the next version of SharePoint, it will take significantly more expertise to migrate and test these customizations to the new platform than any of the other four customization options available to us.  I have seen reasonably well developed .NET SharePoint based customizations completely abandoned due to the underestimation of the complexity of version migrations, and have seen .NET based customizations completely re-done using the other approaches outlined above because the cost of recreating in the new platform was too prohibitive.

Is SharePoint the Best Choice?

If you do have a system that requires the deep customizations only available through the .NET framework, then the final question you need to ask is, is SharePoint even necessary here?  If you need custom forms to integrate with data schemas that are more complex than provided in SharePoint lists, if you need to integrate data from multiple data sources and provide that data in very specific ways to the client, then would the .NET framework itself meet those needs?  The .NET framework provides rich interface with the various components of office and can allow completely controlled and custom interfaces with that data to meet very restrictive client needs.  And includes workflow capabilities as well.


It is also possible to have the custom .NET developed components linked to SharePoint by sharing data sources between the two, where simplified views of the data are provided by the Business Connectivity Services, reports and dashboards are provided by the Business Intelligence Services available with SharePoint, but the complex data interactions are provided by completely independently developed .NET components. There are even tools that allow you to create an independent .NET application that appears to the end users to be a part of SharePoint creating a seamless experience.


In other words, we have lots of options for creating deep custom applications that meet business requirements that do not require using the SharePoint object model and deep knowledge necessary to create true SharePoint development code based modifications.  If we can keep the system simpler to maintain over time, particularly when considering the farm level implications of custom code, then keeping the .NET customizations and SharePoint as separate as possible is the best choice to make.

Conclusion

I am sure there are SharePoint development teams out there with deep expertise and established deployment and update processes that feel I am overstating the barriers to developing within the SharePoint object model with .NET framework based code.  And I have no argument with that, if you have the expertise and full support of your enterprise, then by all means, fully leverage those capabilities.


However, within my experience of companies using and wanting to leverage SharePoint, the costs and issues outlined above with .NET framework SharePoint based customizations is simply too high.  I believe that, if you do detailed client driven requirements analysis of needs, and then work through the complexity stack outlined above to see how the functionality will be achieved, then in the majority of cases, you won't need code based customizations, or the additional complexity, overhead, and maintenance challenges associated with them.