Transdev unifies its many databases with SQL Server and Power BI

Without the proper setup, a diversity of systems means a huge headache to whoever needs to handle and analyze data. Fortunately, there is a remedy for that.
The client:

Transdev is a French-based public transport group operating in 20 countries. Transdev is a worldwide leader in subway and railway transportation, and one of the major players in bus transportation, employing 82.000 people and with a revenue of over €6.6 billion. Present in Portugal since 1997, Transdev is one of the largest bus and coach operators in the country, with a fleet of over 1.500 vehicles.

The problem:

Transdev’s Portuguese operation grew through a series of mergers and acquisitions but felt some inevitable growing pains. In order to extract reports, the company had to monitor some indicators, such as ticketing and fleet consumption, but faced a huge obstacle: with each assimilated operation using their own set of systems and databases, Transdev had no easy way to connect and cross the amount of information on their hands. The company needed to unify all data from these many different operations and have it working towards the decision-making process.

Transdev launched Citi Express in 2011
Transdev launched Citi Express in 2011
The solution:

Transdev’s relationship with DevScope dates back to 2010 when Rui Romano developed an HTML5 reporting platform that allowed employees to analyze and comment on some indicators that still live on their current Business Intelligence solution. When Transdev contacted him again, asking for a way to unify all their different operations, he was well aware of the company’s current situation. The project seemed challenging and presented another perfect opportunity to put Rui Romano’s “Start Small and Grow Big” work methodology to good use. In practice, it means implementing a solution in an Agile way with short and fast stages, modifying it to answer any of the clients’ problems and scale it according to their needs. Since this allows for a tailored approach, clients only need to implement solutions according to their business needs.

Stage #1 – Unifying different systems through a Data Warehouse

Nuno Miguel Silva, José Barbosa, and Bruno Ferreira, BI consultants at DevScope, designed a Data Warehouse – nicknamed Prometheus – that would feed on over a dozen of Transdev’s Ticketing (Datacar, Wayfarer, and Card4B) and Operation (ABC) databases.

Prometheus
Transdev’s ETL Framework

Due to the different nature of these systems, the DevScope team developed a fast and dynamic ETL Framework for Microsoft SQL Server Integration Services (SSIS), allowing for faster design and development of ETL solutions and making it easier to change configurations. Prometheus serves as an intermediary on the entire processing, receiving data from its original databases and transforming it to be used afterward.

Power-BI-Transdev-005

“The diversity of systems made the process a little bit more complicated. And, on top of that, we found that users would be using different methods, even when working on the same system. Because of that, we had to standardize processes.”

– Maria Gabriel, Application Manager and Data Scientist at Transdev

The Data Warehouse with DevScope’s ETL framework gave Transdev a central platform to extract, transform and load data. Whenever a new data source comes in, Transdev’s IT department has no trouble adding it to the data warehouse: all they need to do is configure a few metadata tables. The company also had to normalize the way their workers handled it. Prior to the introduction of Prometheus, Excel was Transdev’s tool of choice but individual edits to files made data management slow and figures unreliable. The entire decision-making process was compromised.

Stage #2 – Introducing Power BI

After regulating Transdev’s different systems through a data warehouse, the next step was to output such a large amount of information. Given its integration with SQL Server, Power BI was the logical choice for the task. Rui Romano is a well-known advocate of Power BI’s virtues and demoing it was enough for Transdev to realize how interactive reports and dashboards could dramatically change several areas of their operation. The company currently has 30 active Power BI Pro users and generates 10 different reports, ranging from ticketing to fleet consumption.

 

This slideshow requires JavaScript.

Prometheus is updated every day so Transdev technicians can always count on a fresh batch of trustworthy data in the morning when they arrive at the office. Real-time analytics is not necessary and because the entire process requires a lot of computing resources, updating outside working hours makes the process seamless.

Results:

Speed and information reliability were the first noticeable aspects of Transdev‘s data warehouse used in combination with Power BI. Understandably, some of Transdev’s methods of analysis completely changed to accommodate the new solution.

“Information is now more reliable, available, and has the granularity that allows us to make better decisions.”

– João Soares, Director of Purchases and IT at Transdev

Transdev’s platform is a work in progress and a testament to the “Start Small, Grow Big” approach. After working out the basics – the data warehouse and business analysis platform – the DevScope team keeps introducing new features according to the client’s needs. Just recently, we added the option to import CSV files to the warehouse.

Transdev hopes that the system becomes increasingly dynamic and able to quickly react to changes in ticket prices and promotions from competing companies. Their long-term goal for the platform is to have it dictate the business itself, i.e., use data to launch new products and support the company’s evolution.

autocarros_transdev_23

Transdev is a multinational group, present in 5 continents, and their technicians meet regularly to see what each team has been doing and exchange some learnings. After watching Transdev Portugal’s Power BI dashboard in action, other teams were very impressed and keen on adopting these solutions.

What we did:
  • Unified Transdev’s radically different databases through a data warehouse
  • Developed a fast and dynamic ETL framework for SQL Server Integration Services that quickly adapts to new changes and data sources
  • Gave Transdev a state-of-the-art reporting tool in the form of Power BI, delivering accurate and up-to-date information to make educated business decisions
Products used:

Do you want to learn more about DevScope’s solutions? Contact us.
Are you a DevScope client and would like to have your case study featured here? Get in touch.

Pedro Sousa will be in Cambridge on September 8th for SQLSaturday

Our Microsoft Azure MVP and open source enthusiast Pedro Sousa will attend SQLSaturday Cambridge on September 8th to deliver a talk titled Open Knowledge Platform Use-Cases.

Pedro is scheduled to deliver a 60-minute talk covering Open Data implementations by the Portuguese Government and show a few use cases of Comprehensive Knowledge Archive Network (CKAN), namely its architectural features, obstacles and different approaches to overcome them.

CKAN is a web-based open source management system for the storage and distribution of open data. CKAN is Open Knowledge Platform’s best-known project, widely used by central and local governments, research institutes and other organizations who collect tons of data.

SQLSaturday is a free training event for Microsoft Data Platform professionals, and SQL Server, Business Intelligence, and Analytics enthusiasts. You can check the event’s schedule here.

If you’re attending SQLSaturday Cambridge on September 8th, be sure not to miss Pedro’s talk.

Embed Power BI reports and dashboards in PowerPoint, Word and Outlook

How SPAR’s sales rose by double digits with Power BI and Azure

Making the right decisions by gathering and centralizing data from over 100 stores.
The client:

Founded in 1932 by Adriaan van Well in the Netherlands, SPAR is an international group of independently owned and operated retailers and wholesalers who work together in partnership with the SPAR brand. There are over 12.500 SPAR stores in 42 countries worldwide, over 100 of which located in Portugal.

How SPAR’s sales rose by double digits with Power BI and Azure

The problem:

As with most retailers of considerable size, SPAR collected massive amounts of data regarding their operation but accessing and crossing it wasn’t always an easy task. The retail sector can be ruthless to those unable to identify and grab opportunities, and SPAR had a huge need to analyze pretty much everything from suppliers to warehouses, sellers, marketing, finance, operations, etc. Everyone at the company had their own perception of the business, but there simply wasn’t enough information to make educated guesses. SPAR scheduled a meeting with DevScope to find out how we could help.

There is only one chance to make a great first impression so, after a brief PowerPoint presentation, we put words into action and took the liberty to connect a few of SPAR’s databases and extract some data previously deemed unreachable.

SPAR wanted to build a Reporting and Analytics (RAP) platform that could enable its end-users to be decision makers. The company’s ultimate goal was to increase its teams’ efficiency but, in order to do so, they had to equip them with proper tools to not only analyze data but also act on it.

SPAR's average sales report on Power BI
SPAR’s average sales report on Power BI
The Solution:

DevScope’s proposed solution used Microsoft® Power BI™ to display data and was built on Azure Cloud Services, namely Azure Gateways and Service BUS to read and sync all business data stored in SPAR’s on-premise data sources.

This internal network was set up using several Excel files, handled and updated by SPAR’s management teams, and SQL Server, namely:

  • MaxiRetail SPAR’s business info is synched and stored in a central SQL Server with data from POS and BackOffice systems at each of their stores;
  • Power BI Data Warehouse – A SQL Server database was set up in order to speed up the insertion of thousands of daily registries to Power BI™;
  • Microsoft Dynamics NAVSPAR’s ERP feeds the BI SPAR model with info as well;

SPAR Cloud Services with Azure and Power BI

There were a few obstacles along the way, though. Right off the bat, real-time analytics for Big Data became a challenge given SPAR’s data complexity and quantity.

SPAR has several departments (marketing, commercial, logistics, operations, finance, etc.) and the company isn’t centralized in several parts of the country. Each team had used Excel spreadsheets to manage their daily work, and crossing information between them to get a sense of their daily activity was nearly impossible.

SPAR processes thousands of daily transactions on thousands of items multiplied by 365 days throughout the year, all accumulated and spread across several PCs and servers. This kind of chaos made decision-making in a timely way very difficult.

Identifying which products’ sales were down during a given period could only be done if the server was updated daily and could take SPAR between 2 to 3 hours. After that, they would have to figure out where that scenario had the biggest impact, which could take an additional 3 hours. One person alone could spend a whole day providing a couple of numbers.

SPAR's YToY store sales report on Power BI
SPAR’s YoY store sales report on Power BI

We would have a new problem on our hands the following day without taking care of the previous ones. We were driving without any orientation, and a lot of speed and no guidance is a disaster. Small teams with the proper tools are able to do what large teams can’t when if they don’t have access to centralized data.

– Francisco Portugal, SPAR Administrator

We managed to deliver and implement the proposed solution on time, and our Business Intelligence team’s dedication was full, so much that a strong connection between both companies persists to this day. DevScope still keeps a dedicated consultant on site at SPAR who has effectively become part of the company.

Results:

SPAR’s sales grew by double digits in just a few months. DevScope’s Power BI™ solution allowed the retailer to have a more detailed and informed view of their sales, prices, margins, and stock, and identify some key opportunities, such as products with a large profit margin but low in stock.

SPAR's store performance report on Power BI

 

DevScope and Microsoft® Power BI™ have become an integral part of the management team at SPAR, and the company stopped depending on a SQL manager to extract data from queries. Pedro Fardilha, our consultant working with SPAR in loco, closely monitors the company’s activity on a weekly -and often daily – basis.

Today we have one single source, one single way of doing business based on data!

– Francisco Portugal, SPAR Administrator

SPAR no longer needs to go with its gut about business – decisions are now based on facts and backed by numbers.

How SPAR’s sales rose by double digits with Power BI and Azure

What we did:
  • Centralized SPAR’s information by connecting SQL Server and Excel databases to Azure Cloud Services;
  • Used Power BI™ to give SPAR a more detailed and informed view of the company’s teams’ activities;
Products used:

Do you want to learn more about DevScope’s solutions? Contact us.
Are you a DevScope client and would like to have your case study featured here? Get in touch.

Send Emails, PDFs or broadcast from Power BI with PowerBI Robots

Contidis gets the data it needs to open a superstore chain in Angola.

Connecting Power BI to SAP HANA to make sure a $400M superstore chain in Angola always makes the right decisions.
The client:

Contidis is an investment group building the Candando superstore chain in Angola. With over US $400 million in investment capital, Candando means not only big business but also thousands of new jobs, the livelihood of local producers and general access to groceries in a struggling economy. Contidis plans on opening 10 Candando superstores by 2021 but its drive is built on the back of a cautious philosophy: open a store, learn what works and what needs to change, adjust and repeat.

The problem:

Before opening its first Candando store, Contidis started building a business intelligence (BI) model with tools in its SAP for Retail solution. However, IT staff was taking too long to build reports and data was so scarce business users weren’t able to make informed decisions. The application was far from matching Contidis’s ambition and holding the entire business back.

The challenge:

Far from pleased with the setbacks caused by their BI model, Contidis asked DevScope to design a Power BI solution that could handle all their data needs: an agile platform that could grow as their users move from one challenge to the next.

Luckily, DevScope’s approach was not that different from Contidis and Rui Romano, our BI Team Manager, even follows a MO he termed “Start Small and Grow Big”.

“We like to start small, listen to users, modify the solution in order to solve their problems and scale it to ensure stability.”

– Rui Romano, BI Team Manager at DevScope

Excel was the tool of choice to connect Power BI with data from Contidis’s SAP application, which ran on Amazon Web Services (AWS). DevScope proceeded to connect Power BI to the SAP HANA warehouse in AWS and, within 1 month, over 100 employees at Contidis – including managers and store personnel – were using the BI solution to include data from store inventory, product pricing, purchases and promotions in their reports.

11-candando-compras-3

There was still plenty of work to do, though: users were experiencing slower response times, file location was making mobile access to reports a hassle and Power BI couldn’t handle data comparison between Excel files and those in the SAP HANA database. In order to increase performance and circumvent these issues, Contidis needed a single source of information for business intelligence. DevScope opted for building a data warehouse with Azure SQL Database and create a custom PowerShell ETL framework to move data to the cloud. This solution also allowed to configure refresh rates for every table in the warehouse so Candando could access sales data by the hour.

In order to control data access, DevScope implemented a dynamic security model with Azure Analysis Services and Azure Active Directory, ensuring customized BI-access privileges for every user. We also introduced standardized reports and dashboards that help optimize operations (such as sales comparisons that help determine how discounts drive revenue) as well as alerts for unusual balances in order to detect fraud.

Competitive pricing can make or break a new player in the retail market and in order to understand how Candando’s value stacked against its competitors, DevScope built a handy Power BI solution that allowed employees to scan a product’s barcode at any retailer and instantly compare its price to Candando’s using their smartphones. In the near future, this feature will also be able to send data to the Azure warehouse and immediately integrate comparative pricing on a sales report.

08-candando-servicos

These reports allow users to filter views and add the information of their choice such as store location, date, transaction, product, inventory levels, and product orders. Since the solution is so scalable, Contidis can easily add more users and reports, and save data for the long term, gaining useful insights on trends.

“The first Candando store has been open for less than a year. We already have 14 million records in Azure and adding information about 10.000 sales transactions every day. Even though our data is growing exponentially, we can continue to scale the cloud-based Power BI model to meet long-term requirements.”

– Renato Correia, CTO, Contidis

Automated alerts for IT staff and business users can also be configured with Power BI. For example, if a product isn’t selling, the appropriate people will be alerted in order to act on it as soon as possible. Among the many advantages of using Power BI and Azure Analysis Services is the solutions’ scaleability. Just like Contidis’s expansion plans, DevScope’s solutions started small and scaled up as needed. This allowed Contidis to pay for only what they needed and save money in the process.

“We noticed people weren’t using the service in the middle of the night so we just deployed a script that automatically scales Azure services up and down and turns them on and off depending on traffic and usage. Just by turning our BI service off for 4 hours a night, we’ll save an estimate 3.600€/year. This may seem insignificant to some, but it’s very relevant to us. This change supports our energy-efficient culture and in the long run these savings will be substantial.”

– Renato Correia, CTO, Contidis

The results:

Contidis opened the first Candando superstore in May 2016. Its success has been replicated twice and over 1.000 new jobs were created in Angola in the process. Contidis’s plan is to open an additional 7 stores by 2021.
Thanks to Power BI and Azure, Contidis’s IT staff spends less time creating reports and business users can access all the data they need in order to run their stores and fulfill the company’s ambition.

07-compras-candando

“Since we implemented our Power BI solution in Azure, we’ve noticed a big improvement in business insight and efficiency. Our IT staff spends less time creating reports and our business users have a lot of autonomy, accessing and creating BI whenever they need to, be it from our portal or using mobile devices, including phones and tablets. Our continued growth is due to many factors – and Power BI is one of them.”

– Renato Correia, CTO, Contidis

What we did:
  • Implemented a connected, agile and scalable platform that grew according to the client’s needs;
  • Used Power BI to give Contidis access to the data analysis it needs to run a successful venture;
Products used:
References:

Do you want to learn more about DevScope’s solutions? Contact us.
Are you a DevScope client and would like to have your case study featured here? Get in touch.
Send Emails, PDFs or broadcast from Power BI with PowerBI Robots

SQL Saturday is back in town!

October 28th is SQL Saturday day and once again Porto will host one of the most important meetings for users of this platform and for all of those who wish to know more about SQLServer’s ecosystem.

Pre-Conference – October 27th (Friday)

Pedro Sousa and Rui Quintino will carry out an awesome workshop on “Data Science with Azure Machine Learning Workbench & Docker Containers for Data Platform” and you are more than welcome to join them. To register for the training go to Weventual.

Rui Quintino Lead Data Researcher at DevScope. Rui works with Microsoft data related technologies since 1998 and currently leads Data Research team at DevScope building and improving products powered by Data, Machine Learning, Deep Learning & Artificial Intelligence. Beyond data technologies, Rui also has past background in web, mobile  & security and he likes to mix all of these to get the most out of Microsoft’s Data Platform and make the world a better place through the use of data.

Pedro Sousa lives in Porto, Portugal and he currently works as a Systems Engineer at DevScope. Started working with SQL Server at version 6.5 and although DBA is not his primary role, always had a particular interest in it. Nowadays one of his main focus is Microsoft Azure and integration with on-premises networks, working from the copper wire to the Cloud.

Conference – October 28th

We will be attending many other sessions, and also leading some other. Please join Rui Romano, Joana Barbosa, and Pedro Sousa at their respective lectures (Full Schedule)

http://www.sqlsaturday.com/685/eventhome.aspx

See you there!

Congratulations Rui Romano, our new Microsoft MVP

Rui-Romano-Microsoft-MVP-Award-2017.png

We are happy to confirm that in our team, we have a new Microsoft Most Valuable Professional (MVP): Rui Romano, awarded as Data Platform MVP for his contributions to offline and online Microsoft-related technical communities.

We are thrilled to have such outstanding technical professional in our team and we would like to congratulate Rui for this MVP nomination. He will now join an exceptional group of individuals from around the world who have demonstrated a willingness to reach out, share their technical expertise with others and help individuals maximize their use of technology.

“I’m proud to see Rui receiving this award, he is a brilliant technician and it has been a pleasure to see him grow both at a professional and personal level in these last 11 years that we work together. Thank you for putting such an effort helping others succeed and for being an inspiration for many others in our team. Congratulations Rui.”

Márcia Teixeira, Human Resources Manager at DevScope

Rui requested us to share his acknowledgment here:

“I’m deeply honored and humbled by this award that recognizes my work around Microsoft Data Platform Communities.

Although this award is viewed as a personal achievement it couldn’t be done without the help & backup from my fellow team mates at DevScope especially the BI & Data Analytics Team!

Thank You!”

Rui Romano, Manager & Senior Architect at DevScope

Most Valuable Professional is an annual award given to outstanding members of Microsoft’s technical communities. And we have the honor to have currently 3 Microsoft MVPs amongst our team:

Thank you all for their community efforts, showcasing their passions and drive to offer customers most valuable solutions in their business areas.

Sector público reúne no Porto

Moving-Innovation-2016-Porto2

A Microsoft está sempre em movimento para desenvolver as tecnologias mais inovadoras e para ficar mais próxima das necessidades das comunidades locais.

Desta vez irá estar no Porto para apresentar as últimas inovações direccionadas para o sector público e focadas no desenvolvimento da região e das cidades.

Mais uma vez a DevScope foi convidada para partilhar da sua experiência e casos de sucesso. Rui Quintino [Data Plataform MVP] e Manuela Álvares (Domus Social Porto) estarão a representar as respectivas perspectivas num dos mais pioneiros projectos de Azure (cloud) e PowerBI.

O evento irá ocorrer no próximo dia 16 de Março na Biblioteca Municipal Almeida Garrett no Porto entre as 9h15 e as 13h00.

Confira a agenda abaixo:

As inscrições são limitadas, se ainda não recebeu o seu convite, contacte-nos para info@devscope.net?subject=ConvitePubAdmin.

Congratulations Rui Quintino, our new Microsoft MVP

mvp-rui-quintino-01

It is with great pleasure that we announce another Microsoft Most Valuable Professional recognized in our team: Rui Quintino, awarded as Data Platform MVP.

The Microsoft MVP Award is an annual award given to outstanding members of Microsoft’s technical communities based on contributions made during the previous 12 months to offline and online Microsoft-related technical communities. These technical communities foster the free and objective exchange of knowledge, thereby creating a reliable source of independent, real-world expertise that benefits everyone. The Microsoft MVP Award recognizes exceptional technical community leaders worldwide who actively share their knowledge with other technical community members.

Rui requested us to share is acknowledgment here:

I feel deeply honored with this award, and want to thank my DevScope coworkers & team mates for all the support, dedication, deep expertize & truly amazing team work. Above all, thank you for teaching me so much through these years & being an inspiration every single day on making the world a better place through the use of data and Microsoft Data Platform.

Rui Quintino, Manager & Senior Architect, DevScope S.A.

It takes two, or more…

According to Scott Guthrie, MVP’s are community leaders and they have been an essential part of Microsoft and Microsoft culture for many years. – and we are pleased to have now three on our team.

This way, we also like to congratulate Sandro Pereira for his sixth renewal in the MVP program, initially as BizTalk, but recently as an Azure MVP.

Rui Romano in Paris (Sep 5th, 2015)

SQL Saturday Paris - Rui Romano

SQLSaturday Paris 2015 (or SQL Saturday #420) will be held September 5th 2015 in the neighborhood of Montparnasse in the center of Paris. This is an international conference held by the French SQL Server Community dedicated to SQL Server professionals and those wanting to learn about SQL Server. This edition will have the collaboration of Rui Romano, with a session about “Power BI for Developers“:

Are you a developer? Want to learn what PowerBI has for you? Then come to my session where you will learn how to enhance your applications with PowerBI experiences…

This event has more than 25 speakers from 9 different nationalities, that offer you more than 20 sessions on SQL Server and its ecosystem.

Entries are limited, there is no registration fee but attendees are required to reserve a place here. Check the full agenda of the event program.

Can’t make it to Paris this year? You can always join us to the next SQLSaturday #429 Oporto 2015 on October 3th, 2015.

Azure Stream Analytics e PowerBI no ITPro Airlift

IT-Pro-Airlift-Data-Platform-logoA equipa da DevScope contribuirá com dois oradores para o primeiro evento técnico do ano dedicado às plataformas de Dados e Cloud (Azure).

Este evento pretende demonstrar o que poderá fazer com a nova geração de soluções de dados da Microsoft nos seus projetos, a Microsoft Data Platform. Ao longo do dia irá conhecer o que pode fazer com o novo SQL Server, as novidades na área de Business Intelligence e Data Warehousing, e o potencial de novos serviços na Cloud para Business Analytics suportados no Microsoft Azure.

Rui Quintino vai estrear um tema novo com várias demonstrações de “Azure Stream Analytics” aplicado ao processamento de dados recolhidos via equipamentos espalhados pela internet (IoT, EventHubs). O processamento desses eventos, juntando outros dados analiticos, é depois encaminhado para Real-Time Dashboards ou até mesmo PowerBI.

À mesma hora, o Rui Romano apresenta um tema mais dirigido ao Excel Power-User com  o “Self-service BI: Excel & Power BI”. Êsta é uma sessão muito prática com excelentes dicas de PowerQuery, Power BI, PowerPivot, PowerView, entre outras coisas muito boas no Excel.

O evento irá ocorrer no próximo dia 22 de Janeiro no Microsoft Lisbon Experience em Lisboa. As inscrições são limitadas, não tem custos de inscrição mas é obrigatório que reserve o seu lugar aqui.

Confira a agenda abaixo:

IT-Pro-Airlift-Data-Platform-agenda