Mario (Dr.) Techera-Business Intelligence Solutions, Consultant Profile

• Establishing the Microsoft Fabric platform with the company as the central data warehouse and reporting system.
• The company migrated several operational systems including D365 to new version. The new data warehouse is based on a Fabric/Power BI architecture with Azure cloud Entra Authentication.

Tasks Performed:

• Full administrative responsibility for the Fabric platform F64 and F32 capacities, as well as two F8 capacities for development and prototyping.
• Integration of Fabric with Microsoft Entra.
• Data modeling for the data warehouse bronze, silver and gold layers using data modeling tools.
• Star Schema as well as entity relationship modeling.
• Data modeling for data marts and for dimensional modeling and Power BI (semantic model).
• Design of data models for Microsoft SSAS multidimensional and tabular OLAP cubes using DAX and MDX.
• Definition of design patterns for the ETL team for loading OLAP cubes (Tabular and MD), star schemas and data vault structures.
• Planning and managing team of 4 ETL and reporting developers.
• Performance Tuning of Power BI reports, particularly the semantic layer, as well as the Fabric notebooks
• SQL Performance Tuning.
• DB Design for Azure SQL.
• Reporting directly to project senior management.

Label: Power BI, Fabric, Analysis Services (multidimensional and tabular), D365, SSIS, Tabular Editor, SQL Server and Azure SQL, TOAD Data Modeler, DBSchema, Visual Studio Code, DAX Studio, SSMS, Visual Studio, Jira and Confluence.

• For the German subsidiary of one of the largest telephone operators and mobile network providers in the world with headquarters in Spain, the Customer Service & Sales department decided to standardize the tracking of a large number of KPIs related to sales and services as a single point of truth for the entire German market. The main reporting platform was established as Power BI with Fabric as the main ETL Engine.
• The role consisted of leading a group of developers and analysts in the establishment of the new data warehouse and a Power BI / Fabric-based reporting system based on the complete Microsoft stack, on-premise and in the cloud with Microsoft Fabric, as well as Oracle. The main development role was to design the required databases (landing zone, core, star-schemas etc.) to store the data coming from 12 different source systems including various instances of Salesforce, end-use Excel files, SharePoint files, and various other sources in order be optimized for the Power BI / Fabric reporting. The main reporting tool was established as the Power BI / Fabric platform which accessed the new centralized data warehouse.
• The role also involved coordinating the ETL development team and to provide guidance how best to load data into the various data models and Power BI Semantic models as required for the Power BI reports.

Tasks Performed:

• Sole responsible for all database design and development using TOAD Data modeler with target databases in SQL Server, Azure SQL and Oracle.
• Analysis of requirements with department users and co-ordination of requirements with counterparts in Madrid and other end-users.
• Detailed analysis of Salesforce data structures and implementation of entity-relationship models to store respective data
• Design and development of Kimball data-marts for reporting purposes with Power BI and Fabric, specifically the semantic modelling layer for Power BI Reporting.
• Performance tuning of Power BI reports and Fabric Notebooks.
• Administrator for Power BI / Fabric P1 capacity.
• Design of initial architecture including support in purchasing required infrastructure for centralized Power BI reporting within Germany.
• Power BI and Fabric Capacity monitoring.

Label: SQL Server 2019 / 2022, Azure SQL, Fabric, Oracle 19c, SQL Developer, PL/SQL, Redgate SQL Toolbelt, TOAD Data modeler, Power BI, Fabric, DAX, SSAS.

• The Reporting Data Warehouse of a Russian Bank located in Frankfurt is being moved from Oracle 11g to SQL Server 2017. This involved migrating all DB objects and structures from one platform to the other, including several hundred tables, views, procedures etc. The migration is also meant to tune the target structures so that they are optimized in the new architecture. The role involved migrating a series of highly complex views which were designed exclusively for external reporting to the German and European authorities.

Tasks Performed:

• Analysis of views in Oracle including required packages, procedures and functions (PL/SQL).
• Design and development of views in SQL Server including creation of necessary procedures and functions (T-SQL).
• Design and development of functional tests to compare record-for-record and field-for-field for the data sets of the source and target systems. Mostly written in T-SQL or in SSIS packages.
• Partial automation of test generation.
• Automation of testing process.
• Reporting discrepancies in Data Warehouse data to ETL team.
• Performance Tuning of DB objects in target system.

Label: SQL Server 2019, SSIS, Oracle 19c, SQL Developer, Redgate SQL Toolbelt.

• One of the largest German companies decided to establish the Microsoft BI platform (including Power BI and later Fabric) on a company-wide basis. The ramp-up process was to begin in 2017 and continue through mid-2019. The assigned role was Project Manager / Senior Architect (and part-time Scrum Master).
• As Project Manager, the role involved working with the Product Owner of the MSBI platform as well as supporting the requirements gathering phase of new projects that started using the MS BI platform.
• The Senior Architect role required designing the platform landscape to support many projects, as well as designing the solutions for individual projects. This also included direct development work to support the young development team located in India, Germany and Vietnam. As Scrum Master, the role involved managing the teams in a simple scrum, and later in a scrum-of-scrums to handle the large number of team members.
• Each of these roles was then handed over to senior internal company employees after the ramp-up process had been established.

Tasks Performed:

• Establishing architecture and development standards for all Microsoft BI stack components (Power BI, later Fabric, SS, SSIS, SSRS, SSAS, Power BI, .NET/C#).
• Development work included: SSIS packages, T-SQL (DB Design, procedures, functions etc), SSRS and Power BI reports (Semantic Modelling and Report Design), SSAS cubes (DAX, MDX, partitioning etc).
• Establishing data interfaces to many source systems within the company using XML files, CSV files and direct connectivity to various databases (Oracle, SQL Server, SAP BW, SAP ERP), in particular for the Power BI cloud reports, specializing in semantic modelling for Power BI.
• Selecting a team of 40 developers (mostly off-shore: India and Vietnam) to start the development effort.
• Organizing the development team into technology groups.
• Establishing an Agile process with 2-week sprints, user story definitions, tasks, planning poker and retros.
• Establishing a scrum-of-scrums workflow due to the large team sizes.
• Training plan for technology areas as well as for future team members.
• Introduction of database design and development standards (Kimball, Data-Vault 2.0, T-SQL standards etc.). CASE Tools. SPARX Enterprise Architect for all DWH development.
• Design of Data Warehouse Automation via T-SQL scripts to generate DB structures.
• Implementation of various GDPR requirements within the database design including encryption of data and deletion of data from all infrastructure when necessary.
• Introduction of OLAP development standards.
• Introduction of ETL development standards using various technologies: SSIS, T-SQL, C#, XML.
• Source code review.
• Introduction of development tools: Redgate and ApexSQL.
• Introduction of monitoring tools for the BI stack.
• Coordination with project management of team effort and direct reporting to executive management.

Label: SQL Server 2008 R2, 2014 and 2016, SQL Server Management Studio, SPARX Enterprise Architect, TOAD for SQL Server, Power BI / Fabric, Azure SQL, SQL Server Data Tools, SQL Server Reporting Services, SQL Server Analysis Services, Integration Services, Redgate SQL Toolbelt, ApexSQL Universal Toolset

• The controlling department of a large, top-line German fashion retailer uses a Microsoft-based data warehouse and OLAP platform to consolidate and analyze its data from various source systems. The retailer owns several physical stores and also has a large internet presence including a very trendy web shop. The data warehouse platform contains financial, sales, logistics, purchasing and 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 senior management to make operational 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-site 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:

• Dimensional data modeling and physical data model (Kimball methodology). Included partitioning (relational and OLAP) and several tables with over 10 billion records.
• Pilot project in Data Vault 1.0 methodology as an 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.
• Coordination 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.

Label: SQL Server 2008 R2 and 2014, SQL Server Management Studio, TOAD for SQL Server and Oracle (Hybris), Business Intelligence Development Studio (BIDS), SQL Server Data Tools, SQL Server Reporting Services, Tableau version 9.3, Enterprise Architect, Mercurial, Analysis Services OLAP (same versions), Integration Services, Oracle, SQL Developer, Jira, Confluence, Hybris, Navision, Omniture

• 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.

Label: arcplan Enterprise, Tableau, Panorama Necto, Business Objects, QlikView, Oracle 11, SQL Server 2014, Analysis Services 2014, Reporting Services 2014, SQL Server Data Tools, TOAD for Oracle and SQL Server, Confluence

• 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.

Label: SQL Server Management Studio, TOAD for SQL Server, Business Intelligence Development Studio (BIDS), PowerDesigner, ErWin, Visual Studio, SQL Server 2008 / 2008 R2 / 2012, Analysis Services OLAP (same versions), Analysis Services 2012 Tabular Modeling, Integration Services (same versions), SQL Server Data Tools, Oracle SQL Developer, Oracle RDBMS in version as of 10, Strategy Companion Analyzer Enterprise Suite.

• 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.

Label: PowerDesigner, TOAD Data Modeler, MS SQL Server 2008 R2 (T-SQL), SQL Server Integration Services, Reporting Services, Oracle 10g, Microsoft Analysis Services (OLAP), arcplan Enterprise.

• 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.

Label: PowerDesigner 12, MS SQL Server 2005 (T-SQL), Oracle 10g (PL/SQL), SQL Server Integration Services, SQL Server Analysis Services, arcplan Enterprise 5 / 6.

• For the existing Portfolio Analysis Tool which has been in use for several years, the periodic administration tasks are done. This includes user administration and the periodic import and export of data. Because of new financial administration software, part of the system was redefined. Important improvements are drafted, transferred and implemented in cooperation with the corresponding departments. This includes additional interfaces to newly acquired service providers, including SAP interfaces and complex forward-looking analysis of areas (rental spaces), rents and a 10-year cash flow. The departments' needs always have to be implemented to reflect market and process changes.
• Highlights of the Tool:
• The Tool allows complex and forward-looking analysis of areas, rents and 10-year cash flow.
• Is used for the planning, budgeting and analysis of the entire real estate portfolio.
• Up to 30 concurrent users from all departments within real estate and portfolio management.
• Up to 130 reports, including 30 printing and 30 multi-use reports, based on 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, stored 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 input screens and reports with arcplan Enterprise software.

Label: Oracle Designer, Procedures and Warehouse Builder, SQL*Plus, Oracle SQL Developer, SQL Server Management Studio, TOAD for SQL Server and Oracle, PowerDesigner, arcplan Enterprise.

• 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.

Label: PowerDesigner 12, MS SQL Server 2005 / 2008 R2 (T-SQL), SQL Server Integration Services, arcplan 5 / 7.1 (dynaSight).

• 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.

Label: PowerDesigner, Panorama NovaView, MS SQL Server 2000 / 2005 (T-SQL), MS Analysis Services (OLAP and MDX), NovaView (Panorama), MS Reporting Services, Data Transformation Services (DTS), IIS, dynaSight (arcplan), Oracle 9i (PL/SQL).

• 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.

Label: PowerDesigner, inSight/dynaSight, Crystal Reports, TOAD, Oracle 9i, SQL Server 2000 (T-SQL), Analysis Services (OLAP and MDX), Data Transformation Services (DTS), IIS

• A database for worldwide market analysis of agricultural products. Information includes direct farmer polling as well as institutional panel data. The system interfaces with several standard sources of market data and also allows the input of new information acquired by the sales force and product managers. Data is entered or collected 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 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 the existing data input process, analysis of report requirements, 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 the production environment.

Label: Oracle 8i & 9i (PL/SQL, SQL), inSight / dynaSight, ErWin, TOAD, IIS

• 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 locations worldwide. Financial reports are calculated automatically based on user inputs. These reports give controllers a bird's-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 the database and UNIX server.
• Development of inSight / dynaSight reporting and ColdFusion input screens.

Label: Oracle 7.3.4 & 8i (PL/SQL, SQL), TOAD, PowerDesigner, ColdFusion, JavaScript, inSight / dynaSight, IIS & Apache, Dreamweaver, Homesite.