Cloud based Analysis Platform to consolidate information

Cloud Based Corporate Data Warehouse Creation
David Pocero

Technical Department Manager , Ferrovial Services

Country or region:
Spain & Latin America

Destination Industry:
Infrastructure

Introduction

Ferrovial is a multinational company operating in the infrastructure industry. Their business is based around four main divisions: service supply, motorways, construction and airports. They are present in 25 countries including Spain, US, Canada and the UK. Ferrovial has deposited their trust in SolidQ’s knowledge and experience as designers of corporate and department specific information usage solutions.

“Ferrovial Services sees innovation as a method to achieve competitive advantages and setting ourselves apart from our competitors. Our intention is to use the most advanced methods to store and analyse all the information we produce” – commented Pocero.

Following the help provided, Ferrovial now has available a cloud environment capable of analysing data, which they may continue to grow in a simple manner depending on their own needs, as well as the tools required to obtain fast, efficient answers to their information usage needs. Furthermore, we have provided solutions and training via the
Office suite and Power BI to enable them to independently grow the applications provided by this platform.
David Pocero added that “By using the BI tools, we improved our productivity and quality, ensuring that we are now correctly managing the large amount of information produced by each project. In a highly competitive environment, this has resulted in a great advantage.”

Current Situation
  • We need a system to consolidate all available information in several, large Excel files stored in a single location.
  • Furthermore, it is required that all results for any searches made on the system shall be fast in order to avoid the main performance issues with the current Excel files that they currently use.
  • There is no consolidated historical department data due to the difficulties of handling information stored in Excel files
  • They do not have any type of adequate infrastructure to integrate the new system.
  • It’s their first approach to the world of Power BI by Ferrovial
Solution
  • We proposed creating a cloud based Azure infrastructure featuring replicas for production and  pre-production.
  • We need a logging system to upload files which must provide fast, reliable information to confirm if the process was completed successfully or whether there has been any error concerning the entered data type or value.
  • Designing a datamart to integrate the business rules with an intermediate staging area.
  • An Analysis Services solution to be created on top of the datamart with the correct initial size and the capacity to grow depending on need.
  • We provided small start-up guides to describe the platform login process and how to use it.
  • Ferrovial remained informed at all times regarding the current development stage and were provided with prototypes for data validation as an added value because they ready to use. They are also helpful in comparing ideas, providing better solutions, expanding the initial requirements or modifications to increase usability. In this way, we avoid developing a system whereby the end product is not compliant with the criteria or requirements initially proposed by customer.
  • Finally, they were provided with a Power BI model to carry their BI staff duties and multi device information usage.  “We must stress how simple it was to manage such a complex project thanks to SolidQ’s attitude, knowledge and professionalism” – David Pocero.
Advantages

Ferrovial have now made their first approach to the Power BI world. With these short developments and well defined scope, we have firstly achieved to add value to the corresponding department while establishing strong foundations to continue building upon, and expanding the department’s BI area.

When working with Excel workbooks, you must be careful because if there are users working on them all the time, it’s very easy to mistakenly alter a cell with values that do not correspond, or to change the value’s format, which would therefore require a robust validation layer to check whether all values and formats are correct, and to highlight those that are not and why. In addition to this, information is spread across several files and it can be extremely difficult to consolidate them in order to obtain an analytical review of the entire dataset.  From that moment, all information is consolidated and centralised within the datamart, and it can be checked in several ways, either directly or via the cube.

Evidently, there is a great advantage to having all above data which was previously spread across several files in a single Excel sheet. Without mentioning all the previous performance issues and several seconds’ delay to carry out any task, whether it be only checking some information, as opposed to the current speed provided  by the pivoting tables connected directly to either the cube or the Power Bi model. And all above within an environment that users are already familiar with, without requiring training to use new tools, and only having to learn a few , easy new Excel concepts.