Technical white paper
iLytix
L Reporter
“The ultimate reporting tool for your ERP-system”
iLytix Systems
www.xlreporter.com
White Paper iLytix XL reporter
Date: December 1, 2002
Architecture
Scope of application iLytix XL Reporter extracts information directly from an ERP system database or a data warehouse without buffering or changing data. In addition to being a report writer, the powerful write-back functionality makes the product ideal for budgeting and forecasting. A Meta Data Object Model described in the iLytix Meta Data Repository defines the integration between iLytix XL Reporter and the source database(s). No hard-coded modules are needed for any specific ERP system integration. The main reasons that iLytix XL Reporter differentiates from other report writers are its powerful ability to combine data from many different parts of the source database and that it utilizes Microsoft Excel as the report definition and presentation layer. This ability even includes the possibility of reporting from more than just one data source. iLytix XL Reporter supports logical connections of multiple data sources, which assembles all data to a virtual data warehouse. iLytix Meta Repository The iLytix Meta Data Repository holds the information about the structure of the data source from which iLytix XL Reporter extracts data. The iLytix Meta Data Repository is a user-configurable database that stores a logical and physical model of the ERP system: The physical model describes the ERP database in terms of tables, views and columns. The logical model defines user-oriented objects like Dimensions, Measures, Reports, Report Definitions, Report Books, Tree Structures of Dimensions and more. The objects relate to each other using the concepts of association and aggregation known from UML (Universal Modelling Language), the de-facto standard in system modelling. A typical part of the model would describe the relations between dimensions like Accounts, Periods, Customers, Sales People, Products and general ledger measures like Amount, Budget Amount and so on. The description of the ERP system data model in the iLytix Meta Data Repository transforms the physical data model into a representation like the one described above. In some cases, there might be a need to apply a thin layer of database objects like database Views in the source database in order to adapt the physical model in the best possible way. However, it is important to understand that the ERP integration is completely defined by the iLytix Meta Data Repository description and, in some cases, some complimentary database objects. There is no need for special iLytix XL Reporter software modifications to support a particular ERP system. Experience has shown that with access to information about the source ERP system data model, an ERP integration can be developed very quickly.
Page 2 of 5
White Paper iLytix XL reporter
Date: December 1, 2002
iLytix XL Reporter
Meta Data extraction ERP Data extraction
ERP SQL Database
iLytix Meta Data Repository
Application Dimensions Accounts Periods Customers Products Facts Transactions Dimension Trees Account Trees
Customers
Periods
Views applied as a part of the integration process Accounts
Transactions Other dimensions....
Some database tables representing the ERP Data model
Accout Table
Ledger Transactions
Customer Table
Customer Transactions
Component Architecture iLytix XL Reporter is a 3-tier client/server application for deployment in an environment of application server computers and thin client computers. iLytix XL Reporter can also be installed locally on one computer as a standalone application. The clients must have Microsoft Windows 98, NT, 2000, or XP installed. Because iLytix XL Reporter uses Microsoft Excel as the user interface for the advanced report writer, Microsoft Excel 2000 must be installed. Also, the client must be allowed to install and run Active X components. All required iLytix XL Reporter client components are installed from the server using a separate client setup installation, or from a CD, from a local server, or from a web server. The parts of the iLytix XL Reporter user interface that do not use Microsoft Excel are installed as Active X components in HTML. The application can run inside a web browser or as a normal Windows application. The server part of the application may be installed on Microsoft Windows NT, 2000 or XP platforms. All user-defined reports are executed on the server and the resulting datasets are sent to the requesting client for presentation in Microsoft Excel. This technique classifies iLytix XL Reporter as an advanced report writer that does not have the known limitations of ordinary Microsoft Excel add-ins.
Page 3 of 5
White Paper iLytix XL reporter
Date: December 1, 2002
The data selection definitions and functions that constitute the report definitions are defined in the powerful and flexible iLytix Language and stored centrally in the iLytix Meta database together with the Microsoft Excel formatting information. The server-based report writer is also the engine for the easy and intuitive ad-hoc Report Composer module in iLytix XL Reporter. This tool makes it easy to drill down and slice and dice directly into the transactions in the ERP system without having to define selection criteria. Communication The clients communicate with the server using either DCOM or COM+, depending on the Windows platforms installed, or SOAP on top of the HTTP protocol. The application can use both communication techniques in the same environment. DCOM/COM+ can be used in LANs or WANs where security and firewalls do not cause problems for communication. SOAP can be used when clients are connected to the server environment using internet/intranet connections. Microsoft Web Services together with Microsoft Internet Information Server are used in order to expose the components via SOAP. All data transfer between components and external interfaces is based on XML.
Microsoft Excel Client Layer
iLytix GUI
iLytix XL Reporter Client SOAP COM+ File
Client Communication Service Providers
Local XML files for caching when offline
Internet/ intranet (http)
LAN/WAN
SOAP Server Application Layer
COM+
Server Communication Service Providers
iLytix XL Reporter Server
ADO/OLE DB Data Layer ERP SQL
Page 4 of 5
White Paper iLytix XL reporter
Date: December 1, 2002
Technology
Development The application is developed in modern object-oriented tools. Programming languages applied are: • Microsoft Visual Basic • Microsoft Visual C++ • Microsoft C# (.NET) Database platforms The database platform currently supported is Microsoft SQL Server. Operating systems The operating systems currently include: Server: • Microsoft Windows NT 4.0 and Windows 2000 Client: • Microsoft Windows 98, NT 4.0 and Windows 2000 Other software requirements Server: • Microsoft Transaction Server for DCOM • Microsoft .NET Framework on server (installed by iLytix) • Microsoft Internet Information server for SOAP communication Client: • Microsoft Excel 2000 • Microsoft Internet Explorer
Page 5 of 5