How to create a 3D analytics app for iPhone?

There are many trends and directions in data analysis, and we are all familiar with the popular words Business Intelligence, Big Data, Machine Learning, etc. These words are now familiar even to those who do not know how to write an SQL query, are not familiar with the rules for visualizing and displaying information, and even more so will not be able to write a Python script. But there is always an unshakable desire in us, the realization of the dream of any leader and / or even a data analyst: “press the key” and after milliseconds we already receive the answer to the question “What to do?”, And preferably “The company's profit is increased immediately”.

One of the most basic problems in data analysis remains the involvement of a person in this process - the decision-maker. He has little time, or even critically little time. How to involve him in the work process of a large data analysis machine?

The key to understanding data fast is visualization.

The basic tenet that is advancing in the use of IDVP software is "gamification." Even the busiest and most important person finds time on the plane or between meetings to play “2048” or “Tanks”.

But creating a game is always a long and complicated process.

To break this stereotype, we came up with a way that would allow the analyst to create a “Data Game” for his leader in a couple of hours.

Little experiment

In anticipation of an important event - the Presidential Election of the Russian Federation on March 18, 2018, we decided that it would be nice to try to do such a trick with the data on the voting results.
After analyzing the information on the Internet, we found the official source of voting data - the site of the Central Election Commission (CEC) izbirkom.ru . On this site you can find the results of all the elections. Also, not so long ago, we came across an article on Habré, in which an analysis of the data on the distribution of votes in the country was carried out before the elections of deputies of the State Duma of the Federal Assembly of the Russian Federation, which took place on September 18, 2016.

First, we found data on the results of the presidential elections in 2012, and there are preliminary voting results. Through long calls and communication with the CEC support service, we were able to find out that during the counting of votes across the country, this particular tab is periodically updated in order to promptly inform voters about the progress of the work performed.

Here is a table with the results of the election of the President of the Russian Federation in 2012. To visualize the indicators, we used the IDVP.Cartogram tool. This is a service that allows you to display data on an interactive 3D Map of Russia. An example application for iOS devices, which displays some socio-economic indicators, is available for download in the AppStore. It looks like this:







To build such an application you need to go through several steps:

• Data connection to IDVP;
• Application display settings;
• Publish an application.

Next, I will describe these steps in more detail, but to make them understandable, I will immerse you in the general terminology of the application IDVP.Cartogram.

Internals IDVP.Cartogram

iDVP is a platform that allows you to create and manage the further life cycle of applications designed for data visualization. Visualization can be different, from interactive reports on the web, to desktop or iOS-applications with 3D graphics, and the latter is IDVP.Cartogram. IDVP applications work like a web browser. Any web browser contains an engine that downloads resources from the server (html, js, css, etc.), data and forms a graphical user interface using standard visual elements such as a picture, button, text, table and others. Like the browser, the iDVP client application includes a visualization engine (called iDVP Player) and sets of visual components (in this case, a 3D map and binding).

All application logic and user interface configuration are described using templates that the iDVP engine downloads from the server. By analogy with html, the template is an xml document (remotely similar to xaml), which in addition to describing the interface contains data binding logic and event handlers for user actions. The visualization layer of the iDVP client application is implemented using the Unity game engine, thanks to which it is possible to connect 3D components for data visualization.

The application receives data from the iDVP.Data module, which simultaneously acts as a Data Integrator, an ETL tool and a tool for creating web services that return data in JSON format. Excel and CSV files, as well as separate databases and REST / SOAP services can be connected to the iDVP.Data module.

Interaction of individual components in the “Elections 2018” application:

Steps for creating the Election 2018 application in iDVP.Cartogram:

Step 1. Upload three Excel files to IDVP.Data, which we previously received from the CEC website (in the figure, these are 3 blue circles):

• election_service_settings_raw - a file where configuration data is stored (such as the names of regions);
• election_service_raw - a connected service, with data on preliminary voting results;
• election_service_people_visit - a connected service, with information about the turnout.

To connect the source, it was enough to drag the desired file into the window: Step 2. For the visualization that we need, we need the following services:

• election_region_view - to display the names of regions;
• election_tables_view - to output data to the table to the left of the map;
• election_widgets_sum - to display general data indicators for candidates;
• election_widgets_all - to display the names of candidates;
• election_projects_view - to display a list of indicators in the header.

To transform data, we create entities called DataSet (datasets). By creating a chain of such entities, we can transform the source data to the format we need, as well as conduct analytical calculations using SQL scripts.

In order to prevent accidentally creeping into the data, we created a dataset for each data source and there we coerced the types of all fields.

An example script in the main dataset “election_information_raw”:

with elect as ( 
select
k.*,
s.region_code as region_code    
from (
 select
 	CONCAT(project, type, id) as widget_id,
 	project,
 	RTRIM(widget_name, ', %') as widget_name,
 	region_name,
 	dense_rank() over (order by region_name) as region_id,
 	max(param_1) as param_1,
 	max(param_2) as param_2
 	from (
 	  select 
 	  CASE WHEN id < 10 THEN CONCAT('0',id) ELSE CAST(id as VARCHAR) END as id,
 	  `row` as widget_name,
 	  `column` as region_name,
 	  CASE WHEN STRPOS(`row`, ', %') > 0 THEN CAST(RTRIM(`value`, '%') as FLOAT) ELSE CAST(`value` as FLOAT) END as param_1,
 	  CASE WHEN id > 18 THEN 1 ELSE 2 END as project,
 	  CASE WHEN id > 18 THEN '01' ELSE '02' END as type,
 	  CASE WHEN STRPOS(`row`, ', %') > 0 THEN CAST(RTRIM(`value`, '%') as FLOAT) ELSE CAST(null as FLOAT) END as param_2
 	  from election_service_ds_raw
 	)
 	group by
 	id,
 	type,
 	RTRIM(widget_name, ', %'),
 	project,
 	region_name
 	order by project, widget_id 
 	) k
left outer join election_service_regions_raw s on k.region_id=s.region_id   
)
select 
	CAST(a.region_name as VARCHAR) as region_name_table,
	CAST(a.region_code as VARCHAR) as region_code,
	CAST(a.project as VARCHAR) as project,
	CAST(a.param_1 as INTEGER) as param_1,
	CAST(a.param_2 as VARCHAR) as param_2,
	a.widget_id,
	CAST(a.widget_name as VARCHAR) as widget_name,
	CAST(s.param_1_name as VARCHAR) as param_1_name,
	CAST(s.param_2_name as VARCHAR) as param_2_name    
from elect a
left outer join election_service_widgets_raw s on CAST(SUBSTR(a.widget_id,4) as INTEGER) = CAST (s.widget_id as INTEGER)
order by project, widget_id

The query results are as follows: Step 3. Creating a web service (or, as we call them, storefronts) is also an SQL query, which contains output parameters in the select section and input parameters declared in the where section. After we connected the data, the matter remained for small. Step 4. We cloned the draft “Cartogram” application, calling it “Online Elections 2018”. Further, by uploading locally the templates adapted them to new storefronts. We connected visual components to new data services and verified that everything was working. Here is an example of a top panel template, a switch between results and indicators:

        -->

After making all the changes, the templates were updated and the project was rebuilt. As a result, in a very short time, we created an application that visually displays information about the results of the 2012 Presidential Election.

We did it all

When publishing the preliminary, and then the final results of the presidential elections in Russia in 2018, we automatically publish the data in the publicly available application “Elections 2018” as soon as they appear on the CEC website. If you are interested, be sure to download the application in the AppStore and GooglePlay . Follow what is happening with us.
Analysts and developers in the near future will be able to get free-demo access to the IDVP platform in order to learn how to create the same applications and much more. Report yourself on the IDVP website or write to Telegram .

If we manage to involve you in the process of monitoring the election results, then you will succeed in further involving your boss in the data analysis process. But we are sure that you can do better. The data of the Presidential Election is just one of the reasons, maybe next week we will monitor not only the election results in our application, but also the statistics on the use of IDVP.Cartogram throughout the country;)

Data analysis is not just an automatic process of getting an answer to a question is a process of interaction between people and data. Only simple and intuitive tools will tie everything together.