This chapter mainly explains how to export data efficiently from the server based on your specific needs. There are various methods available in our portal to export the necessary data, including via Email, FTP/SFTP, Power BI Connector, REST API and SQL Query.
1.1 Email
Step 1 - Login to Analytics Manager V9 portal via: https://v9.footfallcam.com/
Step 2 - Navigate to System > Data > Import & Export.
Step 3 - Click on 'Create a Job' button at the upper right corner of the page.
Step 4 - Enter a preferred job name and select '1136 - Data Export' as the job type.
Step 5 - Fill in the necessary details required for the export, and do not leave blank for the mandatory field. To export data via email, you must select 'Email' for Export Mode field.
| Category | Field | Description |
| Data Extraction | Data | The available data that you can export. |
| Entity Level | Entity level of the data to be exported. Available entity level: - Site - Site Group - Area - Area Group - Device |
|
| Entity | Specifies the entity to be exported, dependent on the Entity Level selected. You must input the 'Entity Level' field before you can select the entity, and multiple selection is supported. |
|
| Time Granularity | Specifies the time granularity of the data to be exported. Availability may varies based on the 'Entity Level' field, available selection: - Daily - Hourly - Minute |
|
| Metrics | Specifies the metrics to be used for export, default selecting all available metrics. | |
| File Format | Specifies the file extension of the output file. Available format: - Excel - CSV - Text |
|
| Export Mode | Specifies how the export mode. Please select 'Email'. | |
| Recipient(s) | Specifies the recipient group(s) that will receive the exported output file. To setup recipient group, please refer here. | |
| Data Transformation | Transformation | Specifies the method of transformation for the data. Available transformation: - Default Transformation |
| Data Load | Mode | Specifies if the recursiveness of the data export. Available data load mode: - One-off - Recurring |
| Date Range | For 'One-off' export, specifies the range of the dates for the export data. | |
| Frequency | For 'Recurring' export, specifies the frequency of the data export. Available frequency: - Daily |
|
| Next Running Date | For 'Recurring' export, specifies the next running date time, which need to be at least 1 hour after current time. |
Step 6 - Once all required fields are filled, click on 'Create Job' button to start the job.
1.2 FTP / SFTP
Step 1 - Login to Analytics Manager V9 portal via: https://v9.footfallcam.com/
Step 2 - Navigate to System > Data > Import & Export.
Step 3 - Enter a preferred job name and select "1136 - Data Export" as the Job Type.
Step 4 - Fill in the necessary details required for the export, and do not leave blank for the mandatory field. To export data via 'SFTP' or 'FTP', you must select 'SFTP' or 'FTP' respectively for Export Mode field.
| Category | Field | Description |
| Data Extraction | Data | The available data that you can export. |
| Entity Level | Entity level of the data to be exported. Available entity level: - Site - Site Group - Area - Area Group - Device |
|
| Entity | Specifies the entity to be exported, dependent on the Entity Level selected. You must input the 'Entity Level' field before you can select the entity, and multiple selection is supported. |
|
| Time Granularity | Specifies the time granularity of the data to be exported. Availability may varies based on the 'Entity Level' field, available selection: - Daily - Hourly - Minute |
|
| Metrics | Specifies the metrics to be used for export, default selecting all available metrics. | |
| File Format | Specifies the file extension of the output file. Available format: - Excel - CSV - Text |
|
| Export Mode |
Specifies how the export mode. Please select 'Email'. You will need to specify the hostname, port, path, username, password and encryption for the protocol selected. These are found right below the field under a category 'Connection Settings'. Refer to your network administrator for details for the fields in the category. |
|
| Data Transformation | Transformation | Specifies the method of transformation for the data. Available transformation: - Default Transformation |
| Data Load | Mode | Specifies if the recursiveness of the data export. Available data load mode: - One-off - Recurring |
| Date Range | Specifies the range of the dates for the export data. Only applies when 'One-off' is selected in 'Mode' field. | |
| Frequency | Determines the frequency in which the data will be loaded. Only applies when 'Recurring' is selected in 'Mode' field. | |
| Next Running Date | Specifies the next running date. Only applies when 'Recurring' is selected in 'Mode' field. |
Step 7 - Once all required fields are filled, click on 'Create Job' to start the export.
1.3 Power BI Connector
Step 1 - Open Power BI and navigate to Get Data → Database → PostgreSQL database.
Step 2 - Then you will be redirected to PostgreSQL login page. Fill in the details below:-
- Server : beta-cube.footfallcam.com:6543
- Database : postgres
- Data connectivity mode : DirectQuery
- Then click ok, and you will be directed to the user login page.
- Please contact FootfallCam Support via Support Portal to request the user credential.
1.4 Data Manager
1.5 REST API
This section requires an API client such as Postman. You may get Postman here. The steps that are described in this section will be done via Postman, but you may utilize other API clients to perform the requests as well.
1.5.1 Generate an Access Token (AToken)
Access Token is required to verify the identification of the user and the data accessible.
Step 1 - Set Request Method to POST.
Step 2 - Insert "https://v9.footfallcam.com/account/GenerateAccessToken" to the URL field.
Step 3 - Click on the 'Body' tab below the URL field. Select "raw" at the option below the tabs. You will need the parameters "email", "password", and "expiration". Consult your administrator for a valid credential to access.
Below is a sample of the three parameters. You might need to edit the expiration parameter to prevent immediate expiration of the Token.
{
"email": "[email protected]",
"password": "123456",
"expiration": "2024-03-15"
}
Step 4 - Click on Send.
Step 5 - The Access Token (AToken) will be generated and displayed at the output tab at the bottom of the application. Copy this token key as you will be using it for the next requests.
1.5.2 Get Site Details
Optional Step - You may open a new tab to create a new request. If not, you may reuse your current tab but you will be replacing fields in your request.
Step 1 - Set Request Method to GET.
Step 2 - Insert "https://data.footfallcam.com/api/Sites" to the URL field.
Step 3 - Move to the Headers tab.
Step 4 - There will be a table under the tab. Put in 'AToken' under the 'Key' column, and paste in the copied token key that was generated previously into the 'Value' column.
Step 5 - Click on Send.
Step 6 - The list of Sites and their details will be displayed in the output tab. For each Site there will be a "BranchId", "BranchName" and "BranchCode". Find the branch that matches your company's Branch Name or Branch Code. Once located, copy the respective "branchId".
1.5.3 Export Data via API
Step 1 - Set Request Method to POST.
Step 2 - Insert "https://cube.footfallcam.com/API/v1/load" to the URL field.
Step 3 - Move to the Authorization tab.
Step 4 - Select Bearer Token for Type field. There will be a Token field that appears right next to it.
Step 5 - Paste your Access Token into the Token field.
Step 6 - Move to the Body tab.
Step 7 - Select "raw" and select "JSON".
Step 8 - Copy and paste the following content as the Body:
Step 9 - Within the Body, look for the "filters" section. Inside the section contains "member", "operator" and "values", this section aims to filter the result based on branch Id. Replace the value with the branch ID that you have copied in the previous query, it is highlighted in the sample code provided in Step 8.
Step 10 - Click on Send. The output of the query will be long. Look for the "data" section within the output. Details regarding the company name and ID, branch name and ID, along with the associated metrics will be shown. Above is a sample output.
Step 11 - There are 2 ways to save the output:
- On the right side of the output panel, click on Save Response > Save to a file. This allows you to save the output of the query into a JSON file for future usage.
- Alternatively, you may click on the Copy icon right below the "Save Response" text to copy the output into the clipboard.
For the list of Cube available, kindly refer to Appendix: List of Cube.
For a list of Metrics that are available in Cube, kindly refer to Appendix: Metric Documentation V2.
1.6 SQL
This section requires a database administration tool such as DBeaver. You may get DBeaver here. The queries that are described in this section will be done via DBeaver, but you may utilize other tools to execute the queries as well.
For a list of Cube available, kindly refer to Appendix: List of Cube.
For a list of Metrics that are available in Cube, kindly refer to Appendix: Metric Documentation V2.
| Sample 1 - How to get Area Footfall Count IN and OUT daily data by Site | |
| Description | To retrieve Area Footfall Count IN and OUT daily data by Site. |
| Function Name | area_1d_summary |
| Sample SQL | SELECT Time, A01, A02, AreaName FROM area_1d_summary WHERE BranchId = 'xxx' and Time BETWEEN '2023-09-21' and '2023-09-26' ORDER BY Time |
| Sample Result |  |
| Sample 2 - How to get Site Footfall Count IN and OUT hourly data by Site Group | |
| Description | To retrieve Site Footfall Count IN and OUT hourly data by Site Group. |
| Function Name | site_1h_summary |
| Sample SQL | SELECT Time, A01, A02, , BranchName as SiteName FROM site_1h_summary where SiteGroupID = 'xxx' and Time BETWEEN '2023-09-21 10:00:00' and '2023-09-26 16:00:00' ORDER BY Time |
| Sample Result |  |
| Sample 3 - How to get Area Footfall Count IN and OUT minute data by selected Area | |
| Description | To retrieve Area Footfall Count IN and OUT minute data by selected Area. |
| Function Name | area_1m_summary |
| Sample SQL | SELECT Time, A01, A02, AreaName FROM area_1m_summary where AreaId = 'xxx' and Time BETWEEN '2023-09-21 13:00:00' and '2023-09-21 14:00:00' ORDER BY Time |
| Sample Result |  |
| Sample 4 - How to get Live Area Footfall Count IN and OUT data by Site Group | |
| Description | To retrieve Live Area Footfall Count IN and OUT data by Site Group. |
| Function Name | site_live_summary |
| Sample SQL | SELECT A01, A02, AreaName FROM site_live_summary where SiteGroupId = 'xxx' |
| Sample Result |  |