Using Excel with Power Pivot and Power Query as a simple Phone Management System Expense Reporting Tool

I have devised a straightforward method for reporting phone expense data obtained from your telecom provider or phone call management system. If you can organize your data as illustrated below, you can effortlessly utilize the provided Excel document to generate reports within a matter of minutes.

Introduction to Power Pivot and Power Query
Power Pivot and Power Query are transformative tools integrated into Microsoft Excel, empowering users to use their data analysis capabilities. Power Pivot acts as a robust data modelling tool, enabling the creation of sophisticated relationships and calculations, while Power Query serves as a dynamic data transformation tool, allowing users to import, clean, and shape data effortlessly.

Together, they form a powerful duo, providing Excel users with advanced features for efficient data processing and analysis, making complex tasks simpler and enhancing the overall business intelligence experience.

Data model
To establish the reporting system outlined in the introduction, we have partitioned the data into three tables: “Users”, “PhoneCallData”, and the “Date” table.

• The “Users” table, (dimension table) contains information about all users who incurred the specified phone expenses. The table includes data such as PhoneNo, OrgUnit, ValidFrom (the date when the user started to use the phone), and ValidTo (the date when the user ceased using of phone).

• Table “PhoneCallData” (fact table), encompasses a single record for each call within your phone management system. It includes information such as the calling phone number (PhoneNo), the called phone number (PhoneNoCalled), the date of the call, the cost incurred, and the quantity of the phone call impulses.

• Table “Date” (dimension table), also called Calendar table, is a specialized table in a database that contains a single record for every date that you might use in your analysis. Generally speaking, you’ll choose a start date (in the past) and an end date (in the future) then create a record for every date in between. This table contains additional details about each date. For instance, it’s typical to include the number and name of the month, the day name, the year, and many other features related to each date.

Understanding the Problem
The challenge in the data model lies in connecting the “Users” table, which follows the Slowly Changing Dimension Type 2 (SCD2), with the “PhoneCallData” table to correctly pivot the data model. To achieve this, it is necessary to implement a surrogate key to establish a connection between these two tables.

Data Cleaning and Transformation with Power Query
By using Power Query, we transformed the “Users” and “PhoneCallData” tables. Consequently, we have successfully implemented a surrogate key, allowing us to establish a connection between these two tables. Now, we have “Users_2” and “PhoneCallData_2” tables, in addition to the “Date” table, all of which are integral components of the Power Pivot data.

This represents a fundamental data warehouse model, where the table “PhoneCallData_2” is referred to as the fact table, while the tables “Users_2” and “Date” are designated as dimension tables.

The details regarding how the data is transformed using Power Query can be observed in the Excel file, accessible within the “Phone Expense Reporting” folder on my GitHub repository:

https://github.com/jhadzigrahic/Excel-Projects

Screenshot of the Power Pivot Report

Tips for Effective Phone Expense Analysis
Before start using this Excel file, to be able to produce precise reports, please be aware of:

1. Update information about the Users.
2. Ensure that data is appropriately copied to “PhoneCallData”.
3. Ensure that the “Date” table has records created at least up to today’s date.
4. Before starting analyses, reload all data (Excel Data tab, “Reload All”).

While utilizing the reports, if you encounter [blank] information for filed EmployeeName, it indicates that your Users table is not up to date or something is missing. The simplest way to identify the missing data is to go to “PhoneCallData_2” and check the data with “null” values for ID. Compare the found PhoneNo with the Users table and correct any missing information.

Afterwards, perform a “Reload All” data operation and start using the report.

Conclusion
I discovered that billing/reporting systems for phone call management systems can be both expensive and challenging to implement and maintain. If you require reporting that is not overly complex and detailed, this could be the best starting point for you.