Kasper de Jonge BI Blog

Andrew Brust made a great blogpost about opening a Azure database with gemini:

http://www.brustblog.com/archive/2009/09/03/gemini-does-cloud.aspx

We recently released the first version of an operational, near real time, BI platform for monitoring packages in a package sorter for a dutch company. This blog post will describe how we solved their problem using Reporting Services, Integration Services, ASP .Net, hosted in the cloud using Amazon Elastic Compute Cloud.

The scenario

There are 4 sorting centers in the Netherlands where parcels are collected from the region where it is in. Parcels receives a unique code (Bar code) on collection.  The sorting centre then has to determine by postal code the destination of a parcel, parcels are eventually delivered by regional delivery stations. The Parcels are grouped by the sorting centre and during the night shipped to the delivery stations, the delivery station that is the farthest away gets its parcels first. When a parcel destination is closer then another sorting center it is send to that sorting center first, so parcel not within the range of the sorting center are coarsely sorted by region and parcels that are in range are finely sorted by street. Parcels are loaded upon a assembly line of a sorter machine in the sorting center, the postal code is scanned and translated to a digital postal code that will be attached to the bar code. The sorter then determines what the destination assembly line is, called chutes.

The current information is brought by reports full of numbers that are hard to interpret and an excel sheet full of VBA code that is mailed around the organisation. Each sorting centers hosts it’s own server running Reporting services 2000 and a Visual Basic service.

The challenge

The challenge the client had for us was:

• Consolidate data from different sources on one report
• Replace local machines with one central machine
• Bring the right data to the right people, data from sorting center to a manager, data from a team to a team lead
• Show the data as soon as it is available, near real time BI
• The data has to be extendable with data from other sources
• New ad-hoc reports have to be created in a short development cycle

How we did it: Project Startup

The project started with some brown paper session with different users from the organisation.  In these sessions we determined the essential data needed to manage the sorting centers. Focus of the first release was replacing the current reports and excel sheet with the new reporting  environment. The data will be delivered to our system by text files, every minute new files will be created and have to be imported and transformed by the application, estimate is 4 files per minute.

After designing reports for the different user groups, determining the MoSCow and analyzing the different input files we decided for the following architecture:

• Loading and transforming data with SSIS
• Database with SQL Server 2008, data in report tables with different aggregation levels, some data is needed at the lowest grain, but most data is over a larger time dimension.
• Reports with Reporting Services 2008
• Reporting portal with ASP.Net 3.5, reportviewer control and ASP.Net dynamic data for master data tables

While brainstorming with the client it appeared they would like to host the system outside their own infrastructure, we already had some experience with hosting at the Amazon Elastic Compute Cloud and decided to try to determine the feasibility of using Amazon for our bi system. Since the data is delivered in small files every minute a solution was quickly found. Servers hosted in the cloud files can fetch files as easily as local servers.  The server in the cloud fetches data using SFTP. The Amazon cloud is very flexible that let you quickly scale capacity, both up and down, as your computing requirements change.

Implementation

Below is a graphical representation of the architecture used:

The application consists of the following steps:

• Data from the system is enriched with describing master data, this describing data is stored in a relational datamodel that can be maintained by pages that are generated by ASP.Net dynamic data
• Files are transferred to the system by a SSIS package that calls a SFTP application that will move (copy and remove) the files to the BI server, this package is scheduled every minute.
• Files are loaded by two SSIS packages per filetype. One package fetches all files currently in a configured directory using a for loop that traverses the directory. These files are passed through to a package that will import the file into the system. The data from the file is loaded into the SSIS memory and enriched using the lookup component, when describing data is not found these rows will be written to an error table. The remaining data will be written to a table, with data on minute level. These filewatching packages are scheduled to run every minute. The current system performs excellent with 30 files in one minute.
• A lot of data is added to the lowest grain table so to keep performance acceptable some aggregation is applied. Every minute a SSIS package is scheduled that selects the last 2 hours of data from minute table into a temporary table grouped by hour and summing the measures. The data is then merged into an Hour aggregation table using the SQL Server 2008 MERGE statement,  with an update of the measure when the row existed and insert when not. This made sure the data is incrementally increased by each run.
• Reports also show live data from the last 15 minutes so a package is run every minute that fetches data from the last 15 minutes and places it into a table, this table always show 15 minutes of data.
• There are different reports, one for each user in the organisation. The sorting center manager gets a report showing all teams at work, he can then click on a team and drill down to a report with data of that team.
• Users who have opened the report want to see the new data loaded, we used the AutoRefresh function in RDL to automatically refresh the report every minute
  

Finally a screenshot of one of the SSRS reports hosted in the ASP.Net application:

Overall a great project to work on and we are very pleased with the result I hope you get a good idea of how we used the MS BI stack to create this operation BI tool.

Microsoft announced earlier this week that a CEP/stream processing product will be included in SQL 2008 R2.  Complex Event Processing, or CEP, is primarily an event processing concept that deals with the task of processing multiple events with the goal of identifying the meaningful events within the event cloud. CEP employs techniques such as detection of complex patterns of many events, event correlation and abstraction, event hierarchies, and relationships between events such as causality, membership, and timing, and event-driven processes.

Microsoft called out four reasons to me why CEP might be needed in addition to ordinary database processing. Two are the standard reasons for data reduction:

1. Without CEP, you can’t bang the data into the database fast enough.
2. You don’t want to keep most of the data past a short time window anyway.
The other two are also fairly standard reasons for using CEP:
3. Standard SQL isn’t all that great for time series anyway.
4. CEP use cases often call for incremental processing and/or parameterization of queries, something CEP engines are commonly better designed for than are DBMS.

However, Microsoft seems to be taking a somewhat different approach to time-based SQL extensions than some other vendors. To quote email Microsoft sent today:

Microsoft Research (MSR) introduced the temporal extensions to relational algebra based upon a notion of application time that is independent of system time. It matters when the event originated instead of when they arrived at the processing system. Further it treats each event as being associated with an interval of time as opposed to a point in time. This helps in modeling certain real life phenomenon naturally. [StreamBase et al.] also reason about multiple streams. Both the approaches are extensions to relational algebra. The MSR approach took the algebra as the starting point while StreamBase took an existing language over the algebra – SQL as the starting point. The MSR approach consequently avoids having to rework other elements of the SQL surface. The primary language extensions through which this algebra will be exposed initially is LINQ.

What are the implications of this? Can we use the CEP algorithm to monitor real time data from the cloud and extract only the necessary data to our datawarehouse ? or am i going to far with this ?

Found at: http://www.dbms2.com/2009/05/13/microsoft-announced-cep-this-week-too/