Data Warehouse, Oracle, OLAP, SQL Server, Analysis Services, MDX, DB Design, PowerDesigner, arcplan, dynaSight, Panorama, NovaView, Performance Tuning Large DB, Business Intelligence

The controlling department of a large, top-line German fashion retailer uses a Microsoft-based data warehouse and OLAP platform to consolidate and analyze their data from various source systems. The retailer owns several stationary stores and also has a large internet presence including a very trendy web-shop. The data warehouse platform contains financial, sales, logistics, purchasing customer related data from eight different sources including Navision, Hybris and Omniture. The data is extracted and transformed on a daily basis and is used by the senior management to make operation as well as strategic decisions in steering the company.

The assigned role of Technical Project Lead included full technical responsibility for the entire data warehouse platform. The role included responsibility for 5 developers working on and off site. Further responsibilities included all decisions for architecture and software, data warehouse data model, as well as the role of interim Scrum Master.

The work was performed with an Agile methodology using Scrum daily meetings.

Tasks Performed (excerpt):

• Dimensional data modeling and physical data mode (Kimball methodology). Included partitioning (relational and OLAP) and several tables with over 10 billion records.
• Prototyping Data Vault design as alternative and comparison with Kimball dimensional model.
• Analysis of business requirements and design of solutions (data model, ETL, data warehouse, OLAP).
• Source code review of all code by team, including T-SQL, PL/SQL, MDX and all SSIS packages.
• Development of procedures, triggers etc., to support the team. Development of unit testing modules for all T-SQL code. Performance tuning of queries. Various other database development related tasks.
• Design and development of new OLAP cube including 21 dimensions with 147 attributes, several of which with more than a million members. The cube contained over 40 measure groups with more than 200 calculated members.
• Support in the design and development of reports in SQL Server.
• Communication of concepts and progress to senior management.
• Co-ordination of project content and activities with Business Analysts and QA team of the project.
• Scrum task definitions and distribution.
• Various other technical, organizational and administrative activities not listed here for sake of brevity

The world’s largest provider of Tax Free Shopping services designs and implements a new transactional system to support its core business processes of providing travelers with a tax free shopping experience. The new transactional system consisted in replacing over 200 legacy systems and sub-system.

The assigned role of Interim Technical Project Lead in charge of Data Modelling and Data Migration, involved a managing a team of several on-shore and off shore developers (Vienna, Bangalore, Chennai, Singapore, New York). This included direct headcount responsibility of more than 20 developers. The team is a mix of both internal company employees and externally outsourced developers and consultants. The technical work involved coordinating the tasks amongst the on and off-shore development team through several time zones and languages. The outsourcing partner providing the development is one of the largest outsourcing companies worldwide. The position also involved negotiations in name of the client with the outsourcing provider.

Amongst other interesting topics, the project had to deal with sensitive credit card and banking data from millions of consumers.

The work was performed with an Agile methodology using Scrum daily meeting and a two week sprint cycle with storyboarding including planning poker.

Tasks Performed (excerpt):

• Analysis and design of various technical topics including the transaction data model (conceptual, logical and physical), the complete data migration of the legacy systems, definition of scaffolding system states for the final roll-out which requires 3 years. The work is done mostly using SSIS packages and stored procedures.
• Database design (E-R / Kimball / Data Vault): conceptual, logical physical including XML data exchange interface design and storage.
• Design and part implementation of Change Data Capture process including archiving of CDC tables and data.
• Source code review of all code by team.
• Development of procedures, triggers etc., to support the team. Development of unit testing modules for all T-SQL code. Performance tuning of queries. Various other database development related tasks.
• Support in the design and development of reports in SQL Server.
• Design of a Business Component Model for enterprise wide services to supply Master data throughout the company based on the data model of the new system.
• Communication of concepts and progress to senior management.
• Co-ordination of project content and activities with Business Analysts and QA team of the project.
• Scrum task definitions and distribution.
• Various administrative responsibilities including personnel assessment and review of hours by external service provider consultants.
• Various other technical activities not listed here for sake of brevity

For the German subsidiary of an international telecommunications company the project consisted of making a vendor-neutral analysis of several OLAP and Relational reporting front-end tools. The goal was to make a series of recommendations as to which products would be most suitable for the company infrastructure and architecture as well as fulfilling end-user requirements.

The products tested included arcplan Enterprise, Tableau, Panorama Necto, QlikView, Business Objects.

The role of technical project manager involved the definition and management of a prototype based on real company data from the areas of marketing and finance. Each software vendor was then requested to produce a series of reports that demonstrated various functionalities including data input, bubble-up exceptions and the ability to use user-defined MDX and stored procedures. As technical project manager it was necessary to perform code-review as well as co-ordinate resources with the vendors and internal personnel.

Tasks Performed:

• In charge of 4 people (2 IT plus 2 analysts)
• Gathering of technical requirements for the prototypes.
• Close contact with key business users to make sure the vendors understood the business processes.
• Source code review of all prototypes.
• Technical analysis of the various software architectures with respect to the company’s own IT landscape.
• Head-to-head comparison of product technical features.
• Presentation to executive management with analysis and recommendation

One of the world's largest pharmaceutical market data providers uses OLAP as its major technology for distributing data in its offerings to its clients. These clients include all the major pharmaceutical companies in the world. The process of producing on a regular basis (daily/weekly/monthly etc.) hundreds of completely diverse OLAP cubes is much too time consuming to do by hand. Our team conceived and built a cube builder that takes several complex parameters as input and generates the required OLAP database (including cubes, security etc.) and then processes it upon the arrival of new data into the relational data-mart.

Tasks Performed:

• Gathering of process requirements for the cube builder.
• Definition of the process of building cubes for end-customers including a QA procedure to verify the quality of the OLAP data.
• Implementation of the dynamic cube building process based on XML meta-data to define relational as well as OLAP structures.
• Data repository for the meta-data required for the dynamic cube builder.
• Extensive dynamic MDX programming.
• Implementation of an interface to modify the XML meta-data in order to modify final cube structures without directly using a development environment

One of the world's largest pharmaceutical market data providers uses OLAP as its major technology for distributing data in its offerings to its clients. These clients include all the major pharmaceutical companies in the world. Worked in several roles in various projects as expert to develop many OLAP databases for various offerings of the marketing data provider. Some of the cubes were accessed directly through an ad-hoc query tool and others were accessed with the use of Microsoft Reporting services.

Tasks Performed:

• Gathering user requirements.
• Defining required data for ETL team to extract data from various source systems.
• ETL packages with Integration Services to import required data into data mart for OLAP database.
• Defining required data structures and tables for SQL Server in order to process cube.
• Extensive cube design using BIDS (Business Intelligence Development Studio) and MDX.
• Extensive MDX query development for various reports that accessed the OLAP databases.
• Design of a QA and rollout process for the OLAP databases.
• Design and implementation of various security concepts for various client requirements.
• Migration of various Analysis Services 2000 OLAP databases to Analysis Services 2008 / 2012.
• Migration of Oracle relational data sources to SQL Server 2008 / 2012

The project was conceived as a series of input screens to enter budget data for a sales force of 200 employees. The sales and contracts of approximately 60 000 points of sales (POS) are budgeted and planned through the application. The application uses data from the in-house CRM system as well as detailed customer information from SAP. The application is designed to help the sales plan the coming year according to the yearly sales strategy including cash rewards, material rewards, on-site visits and POS marketing material. The planning data is entered, aggregated and presented in several complex reports for both management as well as the sales reps.

The application is adapted every year to the business process due to changes to the yearly sales strategy.

As of the year 2012 the application has been completely re-programmed to take into account major business process changes as well as infrastructure changes. The same work is being performed for this new project.

Tasks Performed:

• Responsible for a team of 7 developers.
• Gathering detailed requirements and functionalities.
• Design and Development of the relational DB and OLAP Cubes including security concept.
• T-SQL Development (procedures, functions, triggers etc.) to support the business processes in the database.
• Performance Tuning des SQL Servers 2005 / 2008 / 2008 R2.
• ETL from base systems.
• Front-end development with arcplan Enterprise: Input screens and reports

The project consisted in building a financial reporting system for a mid-size German construction and real-estate company. The company is a holding with over 50 subsidiaries world-wide. Financial data is gathered on a monthly basis from various system including DATEV to generate a consolidated as well as a company-by-company analysis. The system is used by the Board of Directors as well as the Supervisory Board for reports including Return on Invested Capital, Statements of Cash Flows, Balance Sheets and Income Statements (Profit and Loss Statements). The FIS is also used to enter and track budgets for all companies. It is used by approximately 100 users.

Tasks Performed:

• In charge of team of 4 people.
• Gathering of specifications and definition of application functionalities.
• Design and development of complete data warehouse as well as security concept.
• Extensive work with procedures in the data warehouse to define appropriate business logic within the Data Warehouse.
• Led and held end-user workshops to define requirements.
• Writing procedures in T-SQL (SQL Server 2005 / 2008 R2) to automate the loading and scrubbing process of the data.
• Performance tuning of SQL Server 2005 / 2008 R2.
• Front end development with arcplan 5 / 7.1 (dynaSight / arcplan Enterprise). Input screens and reports

Analysis Design and development of a Data Warehouse base on Operations data from production machines for cigarettes. The data comes from various production sources including cigarette assembly, pack, bundle, carton, case, and pallet assembly. The idea of the ODWH is to provide timely ("near real-time") information based on data extracted from the base production systems.

Tasks Performed:

• Analysis and definition of requirements with end-users.
• Lead developer and database designer for team with 4 developers.
• Versioning of dimensional data with Kimball methodology using Type 1 and Type 2 Slowly-Changing Dimension.
• Design and development of database and OLAP cubes, as well as security concept.
• Extensive work with procedures to define appropriate business process (T-SQL procedures, triggers for versioning of parent data, etc.).
• Data extraction from Oracle database with PL/SQL.
• Data extraction from SQL Server database with T-SQL.
• Front end development with Reporting Services, PowerPivot, SharePoint

A multi-national insurance company with a large call-center in Germany decided to build a new data warehouse to report, in near-real-time, on the call-center activities. The call-center consisted of up to 150 operators working 24 x 7 in order to accept calls from insurance claimants from around the world.

The role of Technical Project manager involved coordinating and designing all technical aspects of the project including 6 developers and 3 analysts. The role also involved very close contact with senior and executive management and also making recommendations on how to proceed with the project as well as software products to use for various facets of the project.

In addition the role involved being the lead database designer for the Data Warehouse using a Kimball methodology.

Tasks Performed:

• Technical Project Manager, 6 developers and 3 analysts.
• Reporting to Senior and Executive Management.
• Analysis and definition of requirements with end-users.
• Database design (PowerDesigner and TOAD Data Modeler).
• Versioning of dimensional data with Kimball methodology using Type 1 and Type 2 Slowly-Changing Dimension.
• Support with the development of T-SQL procedures/triggers/functions.
• Support in the development of reports using arcplan Enterprise and Reporting Services.
• Performance tuning.
• Design and some development of ETL architecture from telecommunications system’s central database (Avaya) to enter into the data warehouse.
• Design and development of database and OLAP cubes, as well as security concept.
• Data extraction from Oracle database with PL/SQL.
• Data extraction from SQL Server database with T-SQL

The system is used for the daily tracking of all article stocks based on production and sales. Stock forecasts are made with the system using planned production and sales forecast values to warn upcoming stock shortages or surpluses. The system is accessed by 50 users including production planners and plant managers. The data is gathered from four different factories in Munich, Berlin, Dresden and Warsaw on a daily basis and the projected stocks values and stock durations are calculated for the next 6 month period on a rolling basis. The reports are then available to users through a web browser.

Tasks Performed:

• Gathering of specifications and definition of application functionalities.
• In charge of project team with 5 people.
• Design and development of complete data warehouse as well as security concept.
• Extensive work with procedures in the data warehouse to define appropriate business logic within the Data Warehouse.
• Led and held end-user workshops to define requirements.
• Support in the data extraction from the SAP systems.
• Writing procedures in T-SQL (SQL Server) to automate the loading and scrubbing process of the data.
• Performance tuning of SQL Server 2005.
• Front end development with arcplan 5 (dynaSight).

This project was purely of a technical nature. It consisted of the migration an OLAP database from Microsoft Analysis Services version 2000 to Analysis Service 2008. Included in the migration were several arcplan Enterprise applications as well as migration of Panorama NovaView front-end.

The migration involved over 200 dimensions and more than 50 cubes as well as Microsoft Integration Services for processing the cubes.

Tasks Performed:

• Complete plan and analysis of migration.
• Re-design of dimensions and cubes to adapt to new OLAP structures in Analysis Services 2008.
• Re-design of DTS packages in Integration Services.
• Re-design of many dimension and fact tables to use the new features of SSAS 2008.
• Re-design of OLAP security.
• Performance tuning (SQL and MDX).
• Front end development with arcplan Enterprise.
• Panorama NovaView migration to version 6.
• Performed all the above work directly.

For the existing Portfolio - Analysis Tool which is in use since couple of years the periodical administration tasks are done. This includes user administration and the periodical import and export of data. Because of a new financial administration software part of the system is new defined. Important advancements are in cooperation with the corresponding departments drafted, transferred and implemented. This includes additional interfaces to newly acquired service providers including SAP interfaces and complex prospective analysis of areas (rental spaces), rents and a 10y-Cash-flow. The demands of the departments always have to be implemented to reflect market and process changes.

Highlights of the Tool:

• The Tool allows complex and prospective analysis of areas, rents and 10y-Cash-flow.
• Is used for the planning, budgeting and analysis of the entire real estate portfolio.
• Up to 30 concurrent user from all departments within real estate- and portfolio-management.
• Up to 130 reports, including 30 printing and 30 multiple used reports, on a basis of an Oracle database with 150 tables.

Tasks Performed:

• Responsible for collecting end-user requirements and acting as interface between end-users and development teams.
• Design and development of data warehouse database structures (tables, views, store procedures etc.) including security concept for data and application access.
• Development of functions and procedures in PL/SQL to implement large portions of the business process in the relational database.
• Performance Tuning of application and database.
• Data comparison between source systems and data warehouse for user analysis (SAP RE FX).
• Monitoring and manual data corrections during ETL processes from SAP BW.
• Development of inputs screens and reports with arcplan Enterprise software

The project consisted of building a reporting system to model all KPIs from the SCOR model (Supply Chain Operations Reference Model) for one of the largest diesel engine producers worldwide. The data is extracted from 3 different SAP systems (Denmark, Germany, UK) at the table level (using over 100 different SAP tables) to model the inbound and outbound flows of the SCOR model. The data is then moved into a data warehouse where it is made available every two weeks for end-user analysis (including the board of directors).

Tasks Performed:

• In charge of team of 3 people.
• Gathering of specifications and definition of application functionalities.
• Design and development of data warehouse model as well as security concept.
• Extensive work with procedures in the data warehouse to define appropriate business logic within the Data Warehouse.
• End-user workshops to define requirements and analysis of data for KPI definition alongside subject matter experts.
• Data extraction from SAP and BW tables.
• Cube design for SCOR model KPIs.
• Writing procedures in T-SQL (SQL Server) to automate the loading and scrubbing process of the data.
• Performance tuning of SQL Server 2005 / 2008.
• Front end development with arcplan Enterprise (dynaSight). Input screens and reports

Project consists of designing an analysis and reporting system based on OLAP technology for a sales force (300 employees) working in the tobacco industry in Germany. The data is extracted from their Siebel CRM and SAP systems and complimented with data from various additional sources. The data is then put into a relational data warehouse where it is available for Business Objects reporting and for OLAP cubes with Microsoft Analysis Services. The data from the cubes is then accessed through various tools Panorama NovaView, MS Reporting Services, dynaSight (arcplan), Excel (Excel add-in for OLAP) and other tools for purposes of detailed analysis by sales personnel and controllers.

Tasks Performed:

• Gathering of specifications and definition of application functionalities.
• Design and development of all data warehouse and OLAP cubes as well as security concept on both data sources
• Daily cubes loading and processing with Data Transformation Services and through DataStage (Ascential).
• Extensive work with procedures in the data warehouse as well as MDX with the Analysis Services to define appropriate measures and business logic within the OLAP cubes.
• Led and held end-user workshops to define requirements for OLAP cubes.
• Support in the data extraction from the Oracle SAP and Siebel databases.
• Writing procedures in PL/SQL (Oracle) and T-SQL (SQL Server) to automate the loading and scrubbing process of the data.
• Performance tuning of SQL Server 2000 as well as Analysis Services (OLAP)
• Working closely with Nigel Pendse (most important international OLAP “guru”) in tool selection for front-end.
• Front end development with NovaView (Panorama), dynaSight (arcplan), MS Reporting Services and Excel add-in for OLAP.

For a major cigarette and tobacco company, this tool is used to track budgets and material for a team of 50 sales employees that visit approximately 11.000 Legal Aged Meetings Points (LAMPS) including restaurants, bars, clubs and discotheques. The sales team budgets every year several million Euros using the tool which they then give to clients as rewards or use to stage marketing events such as Formula 1 racing. The team also tracks material placed at customer’s sites such as ashtrays, parasols, cigarette lighters etc. In total over 5.000 different articles for marketing purposes are tracked with the ENBMT. The data is collected through a Siebel CRM system and then extracted from the Oracle database and placed into a Data Warehouse. The system allows both data input for budgeting purposes as well as analysis by the Event & Nightlife team to track progress with the 11.00 customers.

Tasks Performed:

• In charge of team of 3 developers.
• Gathering of specifications and definition of application functionalities.
• Design and development of complete data warehouse as well as security concept.
• Extensive work with procedures in the data warehouse to define appropriate business logic within the Data Warehouse.
• Led and held end-user workshops to define requirements.
• Support in the data extraction from the Siebel system and Oracle.
• Writing procedures in T-SQL (SQL Server) to automate the loading and scrubbing process of the data.
• Performance tuning of SQL Server 2005.
• Front end development with arcplan 5 (dynaSight). Input screens and reports

A web-based reporting system for the analysis of cash-flow and liquid assets permitting using to view daily details of all operating contributions to cash-flow. The system consists of two components: i) application generates complete official monthly reporting to the German Bundesbank according to Liquidity Principle II legal requirements (Grundsatz II) and ii) internal liquidity reporting by branch office, client and business type which is used by Board of Directors for analysis and strategic planning (short and long term up to 40 years). The system is bi-directional and allows for corrections of data at all levels. Major highlights of the system:

• Available world-wide through corporate intranet
• Currency analysis
• Client analysis
• Bi-lingual system (German and English)
• Top-N analysis
• Automatic generation of Principle II (Grundsatz II) reports for German Bundesbank
• User permissions at a branch by branch level
• Both OLAP and Relational data access
• 24*7 access with no interruption for data loading
• Supports around 30 concurrent users, with an installed user base of over 250 users.

Tasks Performed:

• Principal architect and developer for system; design and development in a team of 5 developers.
• dynaSight development.
• Principal SQL Server 2000, Analysis Services (OLAP and MDX) developer.
• Application architecture & Module development.
• Relational database analysis, development and design; load procedures and a T-SQL procedures and packages
• SQL Server and Analysis Services tuning, maintenance

Project consists of a web enabled reporting interface for the analysis of the consulting market for an international consulting company. The system allows executive management to perform basic data mining queries regarding the IT consulting industry and to keep track average market rates in different consulting market segments on a weekly basis. The system is available through the internet on any browser.

Tasks Performed:

• Gathering of specifications and definition of application functionalities.
• Design and development of front end for reporting and data-mining using dynaSight and Crystal Reports.
• Benchmarking application prototypes in dynaSight vs. Crystal Reports.
• Database Analysis and design using E-R modeling as well as OLAP and cube definitions (MDX).
• Creation of data model using MS Analysis Services (OLAP and MDX) based on data coming from Oracle 9i and SQL Server.
• Writing procedures in PL/SQL (Oracle) and T-SQL (SQL Server) to automate the loading and scrubbing process of the data.

A database for worldwide market analysis of agricultural products. Information includes direct farmer polling as well as institutional panel data. The system interfaces to several standard sources of market data as well as allows the input of new information acquired by the sales force and product managers. The data is entered or gathered in local currency and then converted to several standard currencies. The system also provides a comprehensive reporting component with a user-friendly interface for analysis of data at various levels of summary including products, crops and development stages. Major highlights of the system:

• Main strategic marketing tool with input and comprehensive reporting.
• Supports around fifty concurrent users, with an installed user base of over one 300 users.

Tasks Performed:

• Principal analyst and architect for system design and development in a team of 15 developers and analysts.
• Business process analysis – study and analysis of pre-existing data input process, analysis of requirements from reports, analysis of required interfaces to other marketing data packages such as AgroWin and Kleffmann.
• Application architecture & Module development.
• Principal Oracle developer (packages, triggers functions etc) and dynaSight report development.
• Oracle tuning, maintenance, troubleshooting and support in production environment

A bi-directional web-based system that enables users to input worldwide pricing information for all products in the crop protection branch. The system permits the budgeting, analysis and competitive pricing reporting as required by product managers. The data is entered in a country’s local currency through a web interface and the reports are displayed instantly for further analysis in various standard currencies. Major highlights of the system:

• Used as the main tool for pricing of products in a multi-billion Euro market
• Supports around 30 concurrent users, with an installed user base of over 250 users.

Tasks Performed:

• Principal analyst and architect for system design and development in a team of 8 developers.
• Business process analysis – study and analysis of pre-existing data input process, analysis of requirements from reports, analysis of required interface.
• Principal Oracle developer.
• Application architecture & Module development, dynaSight developer.
• Application documentation and rollout
• Oracle, tuning, maintenance, troubleshooting and support in production environment

The project was to implement major system modifications and improvements for the financial reporting and budgeting system, and the capital expenditure tracking and reporting system.  The financial reporting and budgeting system includes complete Balance Sheets and Profit & Loss, Cash Flow, Sales Analysis, and Currency Conversions Statements, as well as, consolidation reports at the holding company level, Return on Invested Capital reports, and Balanced Scorecard information. The capital system enables the Investment Planning and Reporting Division of the client company to track capital expenditures by project, business unit or company based on monthly inputs by project controllers at worldwide locations. Reports are prepared on an automated based and give controllers information at the group, company, business unit levels. Major highlights of the system:

• The financial reporting system is the complete worldwide reporting system for consolidation and individual subsidiary reporting.
• Enables capital expenditure tracking at the level of individual projects and accounts.
• Used by the supervisory board to decide on project funding.
• Supports over fifty concurrent users with an installed base of over a thousand users.
• The systems services more than 100 international subsidiaries.

Tasks Performed:

• Principal software architect and analyst for business process analysis and system design and development.
• Business process analysis – study of project approval and financial planning process, analysis of requirements for financial reports and checks.
• Application architecture and design as well as Software development and testing
• Physical and SQL Oracle tuning of database and UNIX server.

Development of inSight / dynaSight reporting and ColdFusion input screens

A system that enables the Investment Planning and Reporting division of the client company to track capital expenditures by project, business unit or company based on monthly inputs by project controllers at worldwide locations. Financial reports are calculated automatically based on user inputs. These reports give controllers  a birds-eye view of projects at the appropriate level (group, company, business unit) by comparing actual expenditures with those projected (budget, long-term plan etc.). Major highlights of the system:

• Enables capital expenditure tracking at the level of individual projects and accounts.
• Used by the supervisory board to decide on project funding.
• Supports over fifty concurrent users with an installed base of over a thousand users.
• Pure HTML / JavaScript entry forms

Tasks Performed:   

• Principal software architect and analyst for business process analysis and system design and development.
• Business process analysis – study of project approval and financial planning process, analysis of requirements for financial reports and checks.
• Application architecture and design as well as Software development and testing
• Physical and SQL Oracle tuning of database and UNIX server.
• Development of inSight / dynaSight reporting and ColdFusion input screens

Web-based application provides worldwide inventory status for all products (over 300.000) to product managers across the corporation. Existing application had become too slow due to architecture and data volume (more than 1 TB raw data). Task was to redesign the application and database to improve performance.. Major highlights of the system:

• Daily worldwide inventory status of over 300.000 products with interfaces to several systems such as SAP.
• Real-time reporting with latest inventory and logistics data.
• Financial analysis and key figure calculations for inventory value and changes
• Supports around fifty concurrent users, with an installed user base of over one 500 users.

Tasks Performed:

• Principal analyst and architect for system design and development in a team of 30 people.
• Database design for Oracle 9i AIX cluster.
• Application architecture & Module development, principal Oracle developer (packages, triggers functions etc).
• Oracle tuning, maintenance, troubleshooting and support in production environment