Microsoft Real Estate and Security (RE&S) needed a more efficient and reliable solution for gaining insights into the data in numerous business systems across the globe. RE&S used Microsoft Azure SQL Database to create a data warehouse and data mart to improve access to end-to-end business data, to create business insights for the organization, and to use data and business intelligence to enable digital transformation within RE&S. With the new business intelligence self-service platform, RE&S has a single source of data, a more secure and reliable data catalog, a more complete view of its business data, and an efficient and user-friendly method for accessing business intelligence.

Understanding business and data at RE&S

Microsoft RE&S manages a real estate portfolio of 580 properties in 112 countries/regions, comprising more than 33 million square feet. Operating and maintaining this amount of infrastructure is a huge undertaking, and it’s important for us to know the exact status of our facilities to be efficient and to serve the needs of our employees and customers.

A group of vendor teams manages all our facilities, from changing light bulbs to cutting grass to maintaining elevators to ensuring that heating and cooling systems are running, and the RE&S organization ensures that facilities management processes run smoothly for our entire company.

Examining the state of RE&S data

RE&S and our vendors use many systems to manage our business. In the past, our ability to understand the state of Microsoft facilities depended on the state of the system of record databases that contain our organizational data. We gathered reporting data from these data sources to provide insight into the various RE&S business areas. The vendor-managed data sources in these systems of record weren’t fully accessible by our RE&S business teams. Because these systems were largely owned and managed by vendors, it was often difficult to know who owned each data source and what data the source contained. We couldn’t provide a single source of truth to define end-to-end visibility for our RE&S partners. The data that was needed wasn’t available.

This represented a significant problem. RE&S is responsible for coordinating a massive global real estate and facilities portfolio, but we couldn’t accurately or efficiently capture the current state of that portfolio. Our reporting systems depended on data from vendor systems that weren’t standardized, and generating even simple reports required time and deep knowledge of these systems. Several specific factors limited our reporting capabilities:

• Users had limited self-serve reporting options. The procedure to generate reports involved human intervention and required report-creation skills. Report creators needed a complete understanding of the data to generate meaningful reports.
• We had data quality and reliability issues. Our data was based on a variety of different data sources from vendors in different regions. Data quality standards vary from vendor to vendor, and there was no easy way to confirm the quality or reliability of reporting data.
• Our data was siloed and not synced. With multiple vendors maintaining different data sources, many data sources contained parallel information that was never synced or made truly parallel. This resulted in multiple versions of the same data existing in different locations in slightly different variations.
• There was no master set of data. We couldn’t control how our data was defined across our organization. We also didn’t have a consistent method to define a universal source of truth in our data.
• We didn’t have a consistent definition of terminology or standards. RE&S data spans a multitude of datasets that cover all areas of our business. For example, we maintain records about the usability and size in square feet of our facilities; however, the methods to measure and record building area in square feet vary between regions and buildings. The same situation also existed for other terms and standards.
• Our data didn’t have structure. Because our data was in so many disparate sources, we didn’t have well-defined hierarchies within the data. We couldn’t reconcile our data with the organizational or geographical structure of our business.
• Our data was redundant and inconsistent. The data we had in place was not consistent. In our square footage example, different methods for measuring square feet resulted in different measurement results. We had several different values in different databases for the same room or building, and we didn’t know which was correct.
• The reporting pipelines were too slow. Pulling data from disparate data sources, cleaning it, then creating and generating a report took too long. The process was labor intensive, and we ended up with reports that took from one to four weeks to create. They were out-of-date by the time they were created and published.

Creating a single source of truth

We recognized the need to create a unified repository for our data. We wanted to extract the data that we reported against out of vendor data sources to create a single source of truth across all aspects of our business, which would enable us to create greater business intelligence for RE&S. We established several goals to guide us through solution development:

• Standardize our data taxonomy, model, and technology. We wanted to unify disparate data sources and definitions under a common platform that we could design and manage. Collecting all our data in a single repository to create a single source of truth was important to us.
• Maintain clean data. A lack of data consistency and hygiene was a problem in our earlier solution. In the new solution, we wanted to build in good data hygiene practices and processes to ensure that our data remains clean and consistent. We also wanted to help improve our vendors’ data hygiene practices. Improved data hygiene enables RE&S to generate more relevant and accurate business intelligence.
• Enable self-service business intelligence. We wanted to put control over report creation in the hands of our business users. The reporting process in our old solution was tedious and cumbersome. We wanted the new solution’s reporting system to require minimal technical knowledge, enabling our business users to access information quickly with easy-to-use tools.
• Enable modern engineering and DevOps capabilities. As part of our company-wide digital transformation, it was important to embrace modern engineering principles in our development process. We wanted our business to drive the report and data management process and use that same process to create value for our business.
• Gain valuable insights. Our root goal for the new solution was to provide business insights that make our business better. We wanted end-to-end reporting capabilities that enable us to capture any element of the business and better understand how we can refine and improve our business based on those insights.

Unifying business data with Azure SQL Database

We used Azure SQL Database as the core of our business intelligence solution. Azure SQL Database provides a repository for our business data outside of vendor data sources where we can combine and connect disparate data sources. This enables end-to-end data analysis and a centralized source for the majority of our reporting. Built in the cloud, Azure SQL Database gives us a resilient, scalable, and elastic platform that provided business and technical benefits by default, including:

• A high-performance, scalable infrastructure.
• Integrated security and identity management.
• Integration with structured, unstructured, at-rest, and streaming data sources.

Building business intelligence processes

Building processes to ingest data and ensure its quality, consistency, and validity was a big part of our plan and deployment. We engaged business partners and application managers within RE&S to assess the states of the applications that contained our business data. Then we created a series of steps to perform against each of these applications to ensure that we were pulling the best data possible into our solution. The steps were to:

• Discover data within system databases.
• Analyze data relationships and quality.
• Create processes and rules for cleaning and importing the data.
• Run these processes and reiterate as necessary.

Within these steps, we evaluated the reporting needs of each application with respect to data load frequency, data quality rules, and data delivery frequency. After establishing the process, we ensured that business processes were enforced to maintain future data hygiene and quality.

Managing data orchestration with Azure Data Factory

Azure Data Factory manages much of our data movement, and it enables us to extract, transform, and load all our data sources. Using Azure Data Factory pipelines, we pulled data from disparate data sources into our data warehouse where different data staging and transformation steps are performed. The data is then loaded into a customer-facing data mart, with additional transformations if necessary, for consumption by reporting processes.

Creating actionable results with Power BI

We’re using Power BI to provide our users access to RE&S reporting and data visualization. Power BI enables users to create self-directed reports and visualizations from Azure SQL Database data without requiring advanced reporting skills or a deep understanding of the underlying data. We also created several Power BI dashboards that supply end-to-end views of some of our most important business data and processes. Because Power BI is cloud-based, it’s accessible from anywhere, highly available, and scalable to meet our needs. We’re also using metadata-driven reporting within Microsoft Excel to create easily navigated drill-down reports for our users that enable them to quickly focus on specific parts of RE&S business processes.

Realizing business benefits

We continue to receive benefits from the RE&S data warehouse every day. Many of our business processes and operations have been transformed by the availability of important insights that we didn’t have in the past. For example, we now have:

• Instantly actionable business data for Transportation Services. Transportation Services shuttles our employees between building locations and provides other transportation services for our employees. Without our Azure SQL Database–powered business intelligence insights platform, transportation data such as seat occupancy and route usage were available only every quarter. With the business intelligence insights platform and Power BI, our Transportation Services employees can instantly access dashboard information about how routes are used daily, or they can quickly generate reports with more specific or targeted information. With this information, they can immediately recommend route changes or additions.

• Accurate occupancy results for building management. How Microsoft employees use our buildings directly affects how we manage our real estate portfolio. Whether owned or leased, many costs are associated with our buildings. The data warehouse solution extracts peak average attendance data from our facilities data sources and combines it with wireless network triangulation data to accurately assess building usage. Reporting against this dataset give RE&S teams deep insights into building usage, which enables them to accurately assess and plan lease renewals for buildings and potential employee relocations.

In addition to examples like these, we’re also receiving other benefits from Azure SQL Database and Power BI, including:

• Improved data and service quality. Our data is cleaner and less redundant. We created a single source of truth in Azure SQL Database, and all our business intelligence is more accurate and useful as a result. We also established service level agreements and dialogs with service owners to continue improving data quality and consistency.
• A consistent and dependable reporting experience. We now have true self-service reporting. Power BI provides relevant visualizations and drill-down capabilities that enable our users to access information when they need it.
• A more efficient reporting process. We reduced the time it takes to generate reports with timely data from months to days, or even hours.
• Increased cost savings. The scalable nature of the cloud provides the exact level of resources that we need, which saves us money over time. We don’t have capital expenditures for hardware, and we don’t need to hire personnel to maintain infrastructure.

Moving forward

We’re excited about exploring more ways to expand data discovery and usability at RE&S. We’re currently working on gaining better insights from our data by using machine learning and enhanced data science techniques. We’re also working toward migrating to Azure Data Lake for even greater big data insights and integration capabilities.

Summary

Many organizations and their departments are being transformed into digital businesses in today’s world. Data and business intelligence are critical parts of our digital transformation. Cleaning up and normalizing data into a single taxonomy, a single source of the truth, and having that data in a single trusted location so that it can be discovered and reported in real time are crucial for our success. With our data warehousing and data mart solution, RE&S benefits every day from the clarity, insight, and data-driven decision making that this technology brings to our business, which enables digital transformation today and lays the foundation for future transformation.