Edgenet Realizes the Power of Dynamic IT and Self-Service BI

  • Article

Author: Carl Rabeler

Contributor: Steve Hord

Reviewers: Robert Bruckner, Kevin Cox

Dynamic IT and Microsoft Business Intelligence

The latest Microsoft® business intelligence tools and applications enable IT departments to operate with more agility and to operate more efficiently by optimizing people, processes, and technology. This article is a case study showing how the IT department at Edgenet uses the self-service business intelligence capabilities in Microsoft SharePoint Server® 2010 and Microsoft SQL Server® 2008 R2 to improve their agility and efficiency while delivering analytics to customers in an Internet-facing deployment. Furthermore, this case study illustrates some of the advantages for IT departments through the use of SQL Server Reporting Services in SharePoint mode compared with native mode.

Overview of Edgenet

Edgenet provides applications and services that help companies in multiple industries, regardless of size, sell more products through the use of data services, selling solutions, and Internet marketing. Typically retailers and distributors drive sales with Edgenet's online and in-store applications. Suppliers can specify products in applications and also offer their products to millions of online consumers through product placements and product listings.Edgenet provides analytics as part of its services that enable its customers to better understand how their customers interact and behave with the products that they sell. The Edgenet analytics consist primarily of Reporting Services reports that each customer accesses on the Internet.

Previous Environment

Edgenet utilized Reporting Services in SQL Server 2005 and SQL Server 2008 running in native mode for its analytics before they upgraded to SharePoint Server 2010 and SQL Server 2008 R2. Previously, for each customer, Edgenet configured a new Reporting Services instance and a set of standardized reports that were based on standard templates. This set of standardized reports consisted of 10 -20 reports, with up to 50 customized reports depending on the level of analytics the customer requested. Edgenet used separate instances of Reporting Services for each customer for security isolation; this ensured that each company’s reports were completely isolated. Each customer URL was unique and connected them to their own Reporting Services environment.However, the Edgenet IT and business departments identified the IT department as the bottleneck in delivering customer analytics. They identified the following causes for this fact:

  • Provisioning: The provisioning of analytics for a new customer required approximately two weeks from the initial request from the business department until the analytical environment for each new customer was provisioned. This two-week period included the elapsed time to get a time allocation from the IT department, the actual time spent by the IT department in provisioning the analytical environment, and reciprocation with the business department to ensure the provisioned reports were precisely what the business department wanted for each new customer. The actual time spent by the IT department consisted of approximately 1 – 2 days provisioning a new Reporting Services instance and then an additional 2 – 3 days to customize and deploy the required set of standardized and customized reports for the new customer. The IT department found that it was increasingly difficult to fit the typical 3 – 5 new customer provisioning days into their normal schedule of other tasks required of the IT department.
  • Customizing Standardized Reports: The customization of standardized reports by the IT department for existing customers was only permitted on a prioritized basis, due to the many other tasks required of the IT department. Many business department requests simply could not be addressed due to the IT department’s limited time resources. The reason that many requests for customization could not be addressed is that while the customization of a report might take only two hours of hands-on time, it might to take two weeks for the customized report to be completed – if it was approved at all – due to constraints due to other obligations of the IT department.
  • New Analytics: New types of analytics for existing customers were not even considered due to the IT department’s lack of capacity.

New Environment

To specifically address these concerns, the Edgenet IT department chose to upgrade their business intelligence environment to SQL Server 2008 R2 Reporting Services and to run Reporting Services in SharePoint mode with SharePoint Server 2010. This new environment resolved the first two bottlenecks discussed previously and enabled the consideration of new types of analytics because the IT department now has the time and capacity to investigate additional technologies.These technologies, as implemented by the Edgenet IT department, enabled the IT department to eliminate themselves as the bottleneck in delivering customer analytics in the following ways:

  • Provisioning: When the Edgenet IT department switched from Reporting Services in native mode to Reporting Services in SharePoint mode, the security isolation business requirement for each customer was realized by using a separate SharePoint Server 2010 site collection for all customers. With this new environment, the IT department can use Windows® PowerShell™ for SharePoint and WMI scripts to configure security groups in Active Directory® Domain Services (AD DS) and create the base site collection in just a few minutes. After the IT department completes these steps, the business user skins, or applies a theme to the portal and configures site security using Active Directory groups for a new customer in 30 – 45 minutes. Customizing and deploying the set of reports for each new customer still requires approximately 2 – 3 days, but it is now done by a business user instead of the IT department. This work-shift is made possible by the self-service business intelligence features in SQL Server 2008 R2 Reporting Services and the ease of use of Report Builder 3.0.
  • Customizing Standardized Reports: The Edgenet IT department is now able to offload significant portions of the customization of standardized reports to its business users, simply by using the new self-service reporting capabilities in SQL Server 2008 R2 Reporting Services. The new features used are shared datasets and report parts, consumed by Report Builder 3.0:

The customizing of standard reports by business users was previously bottlenecked by the IT department allocating the time to perform the following tasks:

    • Generate the Transact-SQL query for reports.
    • Build the actual report (including the reciprocation with the business department). The IT department spent the majority of this time building the actual report (including the reciprocation with the business user and the customer). In this new environment, the IT department converted the standard reports into reports with shared datasets and report parts, and it now creates new shared datasets whenever existing queries do not meet the needs for customizing either existing reports or generating new reports. The business users create reports from these shared datasets. Early in the evaluation process of this new environment, the business department brought an example of a new report that required a new Transact-SQL query that needed to be written by a DBA with intimate knowledge of the source schema. The business department was surprised to see that the IT department was able to generate this new query in under 15 minutes. After the new query was generated and published as a shared dataset, the business department was immediately able to use Report Builder to generate the new report. Because the business department can now do some of the work by using report templates, report parts, and shared datasets, the IT department’s role no longer creates a bottleneck.

  • New Analytics: By reducing the time required to provision a new user and offloading much of the customization of reports and the SharePoint site to business users, the IT department is able to consider the implementation of new types of analytics, including PerformancePoint dashboards and PowerPivot workbooks.

New Topology

The following diagram shows the physical topology of the current, new Edgenet environment. (In the diagram, RS stands for Reporting Services, SQL 2008R2 stands for SQL Server 2008 R2, and DMZ stands for perimeter network, also known as demilitarized zone and screened subnet.) topology

 

Extranet Domain Configuration in the New Environment

In the new Edgenet environment, there is an extranet domain for external users and an internal domain for Edgenet users. There is a two-way forest trust between the domains that enables basic Kerberos delegation between the domains, rather than simply a one-way trust. While basic Kerberos delegation is not required for Edgenet’s configuration of Reporting Services in their SharePoint 2010 farm (security is discussed in greater detail later in this document), it is required for other applications in their environment.  

Note: Constrained Kerberos delegation does not work across domain boundaries.

To facilitate secure operation of the SharePoint Server 2010 farm for each customer’s SharePoint site, the IT department configures the following Active Directory groups in the extranet domain:

  •   [Customer] Owner group: The user account for the internal coordinator from the internal domain from the business department for the customer is placed in the Owner group in the extranet domain.
  • [Customer] Members group: The user account for the internal contributors and report developers from the business department for the customer are placed in the Members group in the extranet domain.
  •   [Customer] Visitors group: The consumers of the reports from the customer are given accounts in the extranet domain and placed in the Visitors group.

SharePoint Server 2010 Configuration in the New Environment

In the new Edgenet environment, there are currently three server computers in the SharePoint Server farm within the perimeter network, plus the SQL Server computer hosting the SharePoint content databases and the Reporting Service catalogs, which is behind the firewall. One server in the farm hosts the SharePoint Server Web front-end and the central administration site. The other two servers in the farm are in a software Network Load Balancing (NLB) Reporting Services cluster (RS NBL VLAN). This RS NLB cluster is in its own VLAN to limit flooding on the network. If the SharePoint Server Web front-end becomes a resource bottleneck, an additional Web front-end can be configured and placed into its own NLB cluster on its own VLAN. For more information about NLB configuration, VLANs, and flooding, see Preparing the Network for NLB 2008 (https://go.microsoft.com/fwlink/?LinkId=203908\&clcid=0x409) and Network Load Balancing (https://go.microsoft.com/fwlink/?LinkId=203909&clcid=0x409). 

Important: Placing multiple software NLB clusters on the same VLAN causes massive network traffic flooding and takes down the network segment. 

When a new site for a customer needs to be set up, the IT department creates the new Web application, creates a new site collection (using the Blank Site template), and then activates the Reporting Services features for the new site. Additionally, the Reporting Services service account is granted permissions on the Web application content database (this step is required because different application pool identities are used). The following commands are run from the SharePoint 2010 Management Shell:

  • $w = Get-SPWebApplication -Identity https://<site address>
  • $w.GrantAccessToProcessIdentity("<domain>\<service account>")

For more information, see Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products (https://go.microsoft.com/fwlink/?LinkId=203910\&clcid=0x409).The new site is then skinned with a custom look and feel for that customer. The default SharePoint security groups are utilized as follows:

  • Owners: The extranet Owners group is added to this SharePoint group.
  • Members: The extranet Members group is added to this SharePoint group.
  • Visitors: The extranet Visitors group is added to this SharePoint group. By default, members of this group can open or download any content type uploaded to the document library.

To complete the security setup for the SharePoint site, the IT department performs the following additional steps:

  1. It adds the Reporting Services content types to the document library. Reporting Services provides predefined content types that are used to manage shared data source (.rsds) files, report models (.smdl), and Report Builder report definition (.rdl) files. By default, these content types are only enabled automatically to the Business Intelligence Center site template. Adding a Report Builder Report, Report Model, and Report Data Source content type to a library enables the New command so that you can create new documents of that type. For more information, see How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) (https://go.microsoft.com/fwlink/?LinkId=203911&clcid=0x409).
  2. It modifies the permissions associated with the Visitor role for all document libraries containing Reporting Services artifacts. By default, the Visitors role uses the Read permission level. This permission level allows users to view pages and list items, as well as download documents. To protect the Edgenet intellectual property within their published Reporting Services artifacts, the permission to download is removed. To accomplish this, the Open Item permission must be removed from the Visitors role. Edgenet initially tried to just modify the Read permission level, but this prevented the cascading of their custom style sheets on the site. So, instead of modifying the Read permission level, a copy of the Read permission level is created. The Open Item permission is removed from the copy of the Read permission level and then saved as a new permission level. This new permission level is used for the Visitor role for all document libraries containing Reporting Services artifacts, rather than the original Read permission level, and the inheritance of permissions from the parent site is removed. This second step disables the downloading of the content types, for which the document library is configured, which includes Reporting Services reports, models, and data sources based on the first step.
  3. It adds custom Reporting Services content types to the document library. The predefined Reporting Services content types discussed in step one do not include shared data sets and report parts. As a result, in order to disable the downloading by extranet users of these Reporting Services artifacts, which contain Edgenet intellectual property, Edgenet created custom content types. For more information, see Content type and workflow planning (https://go.microsoft.com/fwlink/?LinkId=95966&clcid=0x409).

Responsibilities of the IT Department and the Business Department in the New Environment with Respect to Report Development

In the new environment, the responsibilities of the Edgenet IT department and the business department have changed:

  • IT Department: The IT department now assists the business department in developing and publishing report templates as full reports based on shared datasets and publishing their components as report parts for consumption in other reports. The IT department also now develops and publishes shared datasets, upon request by business users, for the development of new reports. In addition, if a report is extremely complicated, the IT department steps in to aid the business user.Note: Initially, the IT department was also involved in teaching business users how to work with the report templates, report parts, and shared datasets. This involvement is diminishing over time.
  • Business Department: The business department is now responsible for developing and publishing the following:o Report templates for customization for new customerso Standardized reports for new customers based on the published report templateso Customized reports for new and existing users based on the published report parts and existing as well as new shared datasets

Summary

The new self-service capabilities in Microsoft SharePoint Server 2010 and SQL Server 2008 R2 enabled the Edgenet IT department to eliminate themselves as the bottleneck in the delivery of customer analytics and to empower their business department to better serve their customers.