This series of blog posts will trace a complete business intelligence project from end-to-end, using Microsoft Power BI. It’s based on a fictional telecoms company, Full Telecommunications – Fultec for short.
Fultec provides network services globally to large multinational corporations. One of its customers, again fictional, is Springwater Services. Springwater buys telecoms services from Fultec and deploys them worldwide to its own customers in the financial services sector.
Rather than ordering physical network connectivity directly from Fultec, Springwater instead orders logical service packages. Each comprises a maximum guaranteed bandwidth, and a logical network configuration required to service each of its own customers. Fultec combines all the service package requirements for an individual Springwater customer site, then designs and installs the physical network required to deliver them:
- a local access circuit connected at one end to the Springwater customer site and at the other to a local point-of-presence (PoP)
- a router or switch at the Springwater customer site (usually referred to as Customer Premises Equipment, or CPE for short)
- a port in the PoP
Sometimes Springwater only orders a single, non-resilient physical link for a customer site. More often than not, however, it orders a backup access circuit to ensure resilient fail-over if there is a fault.
We can further subdivide resilient services based on whether resilience is delivered to a single Springwater customer site or shared between two sites. In the latter case, we call this split resilience.
Each physical components has a set of associated non-recurring or monthly recurring costs. In some countries, Fultec provides access circuits directly itself; in others, where Fultec has no network of its own, it subcontracts access delivery to a local telecoms carrier. Fultec owns and operates all of the PoPs, irrespective of the country.
The three components of the access network are collectively referred to as an infrastructure set (whether non-resilient or resilient).
Fultec bills Springwater each month for the total bandwidth delivered to each customer site. In some complex sites, multiple infrastructure sets deliver different services, which it bills separately to Springwater.
In addition to the customer access network described above, Fultec provides backhaul connectivity between the PoPs and Springwater using a core network. However, the costs and billing associated with the core network are outside of the scope of this case study.
Order management and billing
Fultec manages provisioning of the network using a proprietary in-house order management and billing system. Springwater has access to the system so that it can place service package orders. Underlying the system is an OLTP database hosted by Microsoft SQL Server, as shown below:
The database comprises 34 tables grouped into four schemata:
- client: Springwater’s customers, including customer name, sites and site locations
- set: infrastructure sets deployed at customer sites
- sp: service packages ordered by Springwater, including the identity of each service package ordered, required bandwidth and service package inventory
- circuit: physical infrastructure deployed to support service packages, including access circuits, CPE and ports
I need to create a set of business intelligence reports in Microsoft Power BI to help Fultec’s commercial and business management teams manage the network efficiently, ensuring that they minimise cost whilst delivering the required level of service expected by Springwater and its customers.
In the next post I’ll introduce the target data model designed in Power BI and explain the rationale for some of the key modelling decisions made in arriving at the optimal model. Further posts will then explain in detail how the model was implemented, using Power Query and the DAX language in Power BI.