Control mobile phone costs within the organization
It will not be news that controlling communication costs in an organization with more than twenty SIM cards is not a simple matter and many prefer to simply pay without having to worry about cost analysis.
There are specialized products on the market that allow you to track costs mainly in manual mode.
I would like to share the experience of creating a cost control system for several hundred thousand SIM cards based on MS SSAS.
As a field for action, a company was selected which at that time had about 120 thousand SIM cards, 90% of which was a provider with a logo in the form of an egg and devices installed in m2.
Having delved a bit into working with mobile providers, the process looked like this:
I wanted to build the process like this:
The first thing to do was load the data into the database. The organization used Axapta, in its database and it was decided to store data from providers. I will not describe the program code with which the data from the html accounts got into the database, because did not write it, and the essence of the post is not that.
The file coming from the provider in html format is divided into two components: details for the past period and a summary of each accrual number. The summary as a result we will load in base.
As a result, we will get information from such a file:
In the database, create a table of this kind:
And load the data from our accounts there.
It turns out something like this:
The [typeconid] field must first be converted to the standard 8-10 value, otherwise we risk having several hundred communication types, because approximately the same type of connection with the provider can be called differently, for example: “outgoing” and “outgoing.” or "Content category .. (and category number) ..".
done using case:
As a result, we get a fact table for our cube.
Next, you need to design the measurements.
Each SIM card in the organization has the following properties:
We take part of the measurements from the fact table: personal account, provider, type of connection. We
extract the part from our database by joining the necessary tables.
Next, we configure the incremental replenishment of the measurement tables from the operating database to the repository. Here, someone like it, you can use timestamp, cdc, to retrieve only the changed data.
My tables are not big and I used the usual left join to get the difference of the two tables.
As a result, the parameters were summarized in one table with the primary key for the field "phone":
Where the parameters are not known, replace the values with 'n / a'.
Open BIDS and create a new data mining project.
As a data source, we connect the database with the created tables.
In the data source view, we add a fact table and dimension tables.
I made several more measurements by analogy with the described, and also the time table remained with me from another project.
We create links from the fact table to the dimension tables, while the keys in the dimension tables do not have to be physical, when they are created, they will be added as logical ones.
Next, create a cube:
Since the "Cube" is used in different regions, you need to distinguish between rights. I did this on the “contract sim” dimension, creating a separate role for each region and writing the following on the “measurement data” tab (example for the Urals):
And be sure to enable the “activate visual totals” checkbox
As a result, each region can only see its own sims cards and their expenses.
Analysts connect to the cube through Excel and view costs.
Below are a few screenshots of what the analyst’s eyes look like:
Interface - an ordinary summary table
Three-month
expenses by type of connection. Sim expenses for a month, clearly visible “peaks” that you need to deal with.
Traffic report for one of the SIM (numbers in minutes)
At the moment, the system is used in 7 regions of the Russian Federation.
In the first two months after the introduction of the system, expenses were reduced by about 20% and this is not the limit.
This topic is not intended to describe in detail the creation of an OLAP cube, but merely describes a concept that can be implemented in any company with a large number of SIM cards.
The system can be improved with the help of MDX queries, pulling out “interesting” cases and processing them.
If there is interest in the article, you can expand the technical details of the project.
There are specialized products on the market that allow you to track costs mainly in manual mode.
I would like to share the experience of creating a cost control system for several hundred thousand SIM cards based on MS SSAS.
As a field for action, a company was selected which at that time had about 120 thousand SIM cards, 90% of which was a provider with a logo in the form of an egg and devices installed in m2.
Idea
Having delved a bit into working with mobile providers, the process looked like this:
- we receive the electronic version of the invoice for communication by mail
- we get a paper original
- pay
- the mailbox is not rubber, delete electronic copies
I wanted to build the process like this:
- we receive the electronic version of the invoice for communication by mail
- load into the database, analyze
- we expose unreasonable expenses (to the provider or to the one who uses the sim)
- pay
Decision
The first thing to do was load the data into the database. The organization used Axapta, in its database and it was decided to store data from providers. I will not describe the program code with which the data from the html accounts got into the database, because did not write it, and the essence of the post is not that.
The file coming from the provider in html format is divided into two components: details for the past period and a summary of each accrual number. The summary as a result we will load in base.
As a result, we will get information from such a file:
In the database, create a table of this kind:
CREATE TABLE [dbo].[month_money_traffic](
[PERIODBEGIN] [datetime] NOT NULL, --отчетный период
[personalaccount] [nvarchar](20) NOT NULL, --лицевой счет
[phonenumber] [nvarchar](12) NULL, -- телефон
[VENDACCOUNT] [varchar](7) NULL, -- провайдер
[typeconid] [varchar](50) NOT NULL, --тип списания
[traffic] [numeric](30, 7) NULL, -- трафик
[money] [numeric](32, 14) NULL --деньги
) And load the data from our accounts there.
It turns out something like this:
The [typeconid] field must first be converted to the standard 8-10 value, otherwise we risk having several hundred communication types, because approximately the same type of connection with the provider can be called differently, for example: “outgoing” and “outgoing.” or "Content category .. (and category number) ..".
done using case:
case when [typeconid] like 'Контент категория%' then 'контент'
case when [typeconid] like 'исх%' then 'исходящая связь'
else [typeconid]
end
As a result, we get a fact table for our cube.
Next, you need to design the measurements.
Each SIM card in the organization has the following properties:
- location (in m2m device, issued to the employee, in servers, etc.)
- status (active, blocked)
- region service, sales
- provider
- contract (legal entity that pays the bills)
- etc
We take part of the measurements from the fact table: personal account, provider, type of connection. We
extract the part from our database by joining the necessary tables.
Next, we configure the incremental replenishment of the measurement tables from the operating database to the repository. Here, someone like it, you can use timestamp, cdc, to retrieve only the changed data.
My tables are not big and I used the usual left join to get the difference of the two tables.
As a result, the parameters were summarized in one table with the primary key for the field "phone":
Where the parameters are not known, replace the values with 'n / a'.
Open BIDS and create a new data mining project.
As a data source, we connect the database with the created tables.
In the data source view, we add a fact table and dimension tables.
I made several more measurements by analogy with the described, and also the time table remained with me from another project.
We create links from the fact table to the dimension tables, while the keys in the dimension tables do not have to be physical, when they are created, they will be added as logical ones.
Next, create a cube:
Since the "Cube" is used in different regions, you need to distinguish between rights. I did this on the “contract sim” dimension, creating a separate role for each region and writing the following on the “measurement data” tab (example for the Urals):
And be sure to enable the “activate visual totals” checkbox
As a result, each region can only see its own sims cards and their expenses.
Analysts connect to the cube through Excel and view costs.
Below are a few screenshots of what the analyst’s eyes look like:
Interface - an ordinary summary table
Three-month
expenses by type of connection. Sim expenses for a month, clearly visible “peaks” that you need to deal with.
Traffic report for one of the SIM (numbers in minutes)
At the moment, the system is used in 7 regions of the Russian Federation.
In the first two months after the introduction of the system, expenses were reduced by about 20% and this is not the limit.
This topic is not intended to describe in detail the creation of an OLAP cube, but merely describes a concept that can be implemented in any company with a large number of SIM cards.
The system can be improved with the help of MDX queries, pulling out “interesting” cases and processing them.
If there is interest in the article, you can expand the technical details of the project.