Offline Reporting Solution

Sales representatives frequently travel to places where there is limited or degraded network infrastructure but still need access to up to date information in order to make business decisions. Offline reporting solutions provides reports with customized data which can be viewed anytime, anywhere and are unaffected by the quality of the network infrastructure, thus giving key decision makers actionable information in the field to make timely and informed decisions.

For example a manufacturing sales representative traveling to remote facilities in foreign a country where the network infrastructure is unreliable may need to know the actual amount of products purchased as compared to the planned amount. Pharmaceutical sales representative may need to know the latest prescription trends while waiting in a physician's office inside a large hospital with limited network access. In both these cases, an offline reporting solution can provide information that is detailed and robust enough to make timely and informed decisions, but also provide that information at a time when the network infrastructure is limited or is not reliable or fast enough to support large data transfers.

The diagram below illustrates the process to provide offline reporting to users with limited network infrastructure.

The data for the offline reporting solution is staged in a data mart which will serve as the source of the offline reporting solution. This solution can be adapted to any new or existing data mart.

A SQL Server Integration Services (SSIS) package is used to create SQL Server CE (Compact Edition) databases for each employee that will contain data that is relevant to that employee. The table structure is identical to the source data mart, however the file size of each SQL Server CE database is only a fraction of the source data mart since the data is being filtered to include only data that is relevant to that employee. For example, if a sales representative is assigned to the East region, the sales representative will see only data from the East region while a sales representative assigned to the West region will see data only from the West region.

The SQL Server CE database files can be synchronized to a user's mobile device when a network connection is available using a push or pull method. Users can be directed to download the file from SharePoint or the files can be pushed directly to the users using Sybase's Afaria or Microsoft's ActiveSync when new data is available. Microsoft Excel can then connect to the SQL Server CE database and be used as a front end reporting template. The Excel report templates should be prebuilt to be functional, but also to provide a rich and familiar user experience.

This offline reporting solution gives the users the ability to view customized data anytime, anywhere and are unaffected by the quality of the network infrastructure.

During implementation of this solution I have learned a few important lessons that are worth mentioning.

1. NLS Sort – SQL Server CE relies on the National Language Support (NLS) collation tables of an operating system. Different operating systems (Windows 2003, Windows 2008, Windows XP, or Windows 7) have different NLS sort orders. Because SQL Server CE is designed to be lightweight and run on a mobile device there are some tradeoffs. NLS Sort is one of them. The server edition of SQL Server uses a different architecture to support different sort orders and therefore has no l dependency on the operating system. The difference in NLS sort comes in to play when a SQL Server CE database is created on Windows 2008 and then sent to a user's mobile device which uses another operating system. It is assumed that the user's mobile device is another operating system such as Window XP, Windows 7, or Windows CE and not Windows 2008. The server operating system, Windows 2008, and the operating system of the user's mobile device would have different NLS Sort orders. This difference requires that SQL Server CE rebuild all the indexes in the database the first time the database is opened. This action only happens when there are different NLS sort orders. This can lead to lengthy opening times (up to 8 minutes when opening a 1GB SQL Server CE database) when an end user first opens the report which is reading the SQL Server CE database. There are viable workarounds and ways to minimize opening times.
   
2. 64bit vs 32 bit – Most new SQL Servers today are 64bit which is great. SQL Server CE runs is a 64bit environment which is great too. So what is the issue? The issue is that if you are going to use SSIS and connect to SQL Server CE you will need to use the 32bit OLE provider. This means your SSIS package must run in 32bit mode on a 64bit server, which is not ideal. Further complexity is added if your data mart is not SQL Server and need to install a 32bit provider to connect to your Oracle, IBM, or Sybase data mart. Hopefully Microsoft will release a 64bit OLE provider for SQL Server CE.
   
3. Multithreaded approach – Consider early on to run to design this process to run multiple SSIS packages simultaneously. I have discovered that running 8 of the same SSIS packages (with different parameters) gave the fastest end to end completion time when creating 2000+ SQL Server CE databases. Running sequentially would have taken 24 hours but running 8 SSIS packages in parallel reduces the end to end turnaround time to 3 hours.
   
4. Limitations of SQL Server CE - Keep in mind that SQL Server CE has a 4 GB limit and supports most standard SQL, there are some limitations in the SQL that is supported. For example, the PIVOT statement is not supported.