There are numerous ways to set up and implement a successful Business Intelligence environment. This document will provide an overview of the four main server configurations, describe the methodology behind each configuration, and highlight the pros and cons of each.
The four main server configurations that will be discussed in this document are:
- Single Server Configuration
- Isolated Database Server Configuration
- Dedicated Business Intelligence Server Configuration
- Multiple Environment Server Configuration
SQL Server Requirements
Jet Analytics utilizes the Microsoft Business Intelligence stack as the foundation for the back-end components of the platform. SQL Server Integration Services and Analysis Services components are required in order for the Jet Analytics solution to be properly deployed.
The Jet Data Manager supports all currently supported and public releases of Microsoft SQL Server. Newly released versions of SQL Server will be supported within 90 days of their release date. Versions of SQL Server that have reached their 'End-of-Life' status with Microsoft may work, but are not officially supported.
The table below outlines which versions of SQL Server are currently supported:
|SQL Server Version||Supported Editions|
|SQL 2008||Standard, Enterprise|
|SQL 2008 R2||Standard, Enterprise|
|SQL 2012||Standard, Business Intelligence, Enterprise|
|SQL 2014||Standard, Business Intelligence, Enterprise|
|SQL 2016||Standard, Business Intelligence, Enterprise|
|SQL 2017||Standard, Business Intelligence, Enterprise|
Single Server Configuration
The Single Server Configuration is generally the simplest and most cost effective solution on which to implement a Business Intelligence platform. All components of both the ERP system and the BI platform coexist on the same server. Because there are no additional hardware requirements and SQL Server is already present on the server there will generally be no additional hardware or licensing costs required. Because all of the processing for both the BI platform and ERP system is being completed on the same machine, however, it is to be expected that the overall load on the server will increase. Because all processing is done on the same server network traffic is kept to a minimum and data transfer speeds will be fastest. In this configuration, users that have been tasked to modify or customize the BI platform using the Jet Data Manager will need to be granted logon access to the server in order to make the customizations. This method is not recommended if the potential server is already near its maximum capacity.
Isolated Database Server Configuration
The Isolated Database Server configuration is ideal when the users that need to modify or customize the BI platform using the Jet Data Manager cannot be granted access to the server on which the physical databases reside. In this configuration, an installation of the Jet Data Manager and the databases will reside on the main server while another copy of the Jet Data Manager is installed on a separate machine that the development users will be able to access. This machine can be another server or their local machine. The users will utilize their local copy of the Jet Data Manager to make changes and during this process their machine will act as a conduit for all of the data flowing from the ERP system through to the data warehouse and OLAP cubes. Because everything is flowing through a separate machine this configuration may generate the most network traffic. If SQL Server Integration Services (SSIS) is to be used then SSIS must be installed on the machine that the users access. If this is not feasible then SSIS will need to be disabled for the project in order for users to deploy and execute the objects in the project.
Dedicated Business Intelligence Server Configuration
The Dedicated Business Intelligence (BI) Server configuration is ideal when it is desirable to have the BI components separated from the ERP system. In this configuration all of the BI components, such as the Jet Data Manager, the staging database, data warehouse, and the OLAP cubes all reside on a separate server. Because all BI components are separated from the ERP system there is no load on the ERP system when users are querying and consuming the data in the data warehouse or OLAP cubes. Also, during the data transfer process all subsequent processing of the data is offloaded to the BI server once the initial transfer of data from the ERP system is finished. This environment can also be more secure than the Single Server Environment configuration because all users who are modifying or customizing the BI platform are logging in to the BI server and not the ERP production environment. Oftentimes an organization will have a server with SQL Server installed in addition to their ERP production server and this secondary server may be a good candidate to act as the Dedicated BI Server. If the only server in the infrastructure with SQL installed is the ERP production server then implementing a Dedicated BI Server may require the purchase of additional hardware and/or software. This may not make it a cost-effective option.
Multiple Environment Server Configuration
The Multiple Environment Server Configuration is ideal for organizations who would like to have separate development and production environments for their Business Intelligence platform. These two environments are most commonly placed on different servers. Development work can then be done in a dedicated environment that includes separate development databases for the staging database, data warehouse, and OLAP cubes. Once development and testing has been completed the development project can be migrated to the production environment manually or through the use of the Development Deployment Toolkit (described in a separate document). The ERP production database can live on either of these servers or on another server altogether. While this configuration is the most flexible it is also the most complex to set up out of all of the configurations discussed in this document. Since all development is done on the development server there is no need for any users to log on to either of the BI or ERP production servers.