1. Home
  2. Import and Export App

Import and Export App

1. What are Data Import and Export?

For better data analysis, data integration will always be required to consolidate the data from different teams and different systems into the same platform. Data Import and Export is a centralise application which make the data transfer convenient for the data analysts.

2. Features

This application included:

  1. Data Import: Integrate data from other system (such as sales data, staff data, etc.) into Analytics Manager v9 portal.
  2. Data Export: Migrate footfall related data to another platform via data export.
  3. BI Plugins: Direct connection to FootfallCam V9™ database for real-time data review.

2.1 Import

User can import various type of data into Analytics Manager v9 portal, to obtain secondary metrics. Our system is capable for various types of data import, for more details kindly refer to Data Import.

2.2 Export

User can export the footfall data collected in Analytics Manager v9 portal based on your specific needs. There are various method available in our system, for more details kindly refer to Export Data from Server.

2.3 BI Plugins

BI (business intelligence) Plugins are software that can connect directly to the database of FootfallCam V9™ to retrieve data for Business Intelligence purposes. FootfallCam V9™ supports the integration of three BI plugins, namely Microsoft Power BI, Microsoft Excel and Tableau.

The advantage of BI plugins lies in their direct connection to the database, allowing users to get their latest data in real-time to construct graphs, charts and other visualisations however they see fit.

Without these plugins, users are limited to the various forms of data visualisations on FootfallCam V9™ or have to routinely export past data from FootfallCam V9™ to construct their visualisations. Even so, the data obtained will not be as updated as that from the plugins, as the latter is synchronised with the FootfallCam V9™ database.

2.3.1 Power BI

We are currently using Power BI Desktop to retrieve and visualize the data. Please refer to the following steps to begin using Power BI. For more information on the list of cubes and their corresponding dimensions/measures, please refer to Cube Documentations.  The list of metrics can be obtained from here.

Step 1 - Find and click the "Import and Export" app. If the app is absent among your available apps, kindly contact "[email protected]".

Step 2 - In the left navigation bar, click "BI Plugins" and then "Power BI".

Step 3 - Observe the Username and Password fields. If no credentials have been generated yet, click the "Get Credential" button.

Step 4 - Click the "copy" icon to copy your credentials to the clipboard.

Step  5 - If you haven't installed Power BI yet, click the "Install" button to be redirected to the official Microsoft site to download the software.

Step 6 - Observe the instructional video to connect your Power BI to the FootfallCam V9™ database (cube.footfallcam.com:6432). When prompted to enter the credentials, use the credentials obtained from Step 4.

2.3.1.1 Using SQL Queries to get HOURLY data

If you want to retrieve specific data, use an SQL query to obtain the data. Please follow the video instructions up to 0:22, which covers connecting to the PostgreSQL database. Before clicking the "OK" button, follow these steps to complete the configuration:

Step 1 -  Click on "Advanced options"
 
Step 2 - Enter the following SQL query in the SQL statement field. Please add the expected column name.
 
select date_trunc('hour',Time)Time ,A01, A02, ......  from ffc_device_summary
 
Step 3 - Then, uncheck the "Include relationship columns" option and click the OK button.

2.3.1.2 Power BI Application

If you prefer to use Power BI Service, it is necessary to configure an on-premises data gateway or a personal gateway. This setup is required to support encrypted connections when building and publishing reports. As the gateway configuration involves access to internal systems and sensitive credentials, it is preferred that the setup be configured from your end.

For detailed instructions, please refer to Microsoft's official documentation on setting up and troubleshooting the on-premises data gateway.

In addition, you may find the following resources helpful: Install View Guide (ZIP file) and Setup Video Guide (ZIP file)

2.3.2 Excel

To utilise Excel for data visualisation, please follow the steps below. For more information on the list of cubes and their corresponding dimensions/measures, please refer to Cube Documentations.

Step 1 - Find and click the "Import and Export" app. If the app is absent among your available apps, kindly contact "[email protected]".

Step 2 - In the left navigation bar, click BI Plugins > Excel to open the Data Integration with Excel page.

Step 3 - Observe the Username and Password fields. If no credentials have been generated yet, click the "Get Credential" button to get the username and password.

Step 4 - Click the "copy" icon to copy your credentials to the clipboard.

Step 5 - To connect to the database, you need the Npgsql driver. If you haven't installed it yet, simply click the "Install" button. This will redirect you to the related website where you can download the driver. Follow the provided video tutorial for a step-by-step installation guide.

Please refer to the video for instructions on installing the Npgsql Driver (If needed): 

Step 6 - Download the Excel file that has been preset with a connection to the FootfallCam V9™ database.

Step 7 - Please follow the instructional video to activate the connection to the database (cube.footfallcam.com:6432). When prompted, enter the credentials obtained from Step 4. After retrieving the data, you can use Excel's charts, graphs, and other visualization tools to analyze and present your data effectively.

2.3.3 Tableau

Section Coming Soon...

Updated on April 18, 2025