Uploading Data from your Website using PHP

In this article we are going to show a simple way of uploading small datasets directly from your web pages. We use an open-sourced library phpgdc providing simple GoodData client written in PHP to upload quarterly targets into GoodSales project.

Installation

The library is downloadable from Github at where you can find the latest stable version of the php classes. Since it is an open source you may use it in your applications without restrictions, or you may contribute and improve it to cover more features. To run it you will need PHP5 with zip support and Httpful library which can be downloaded from Github also. Just copy the code to your server, set correct paths into require statements and you can start.

Example - GoodSales

We are going to modify our standard GoodSales project. Suppose you want to set financial targets for each quarter. A manager should be able to set the target in your company intranet, and the number will be resent to the GoodData platform.

Modify the model

First of all we need to add a new dataset into the GoodSales project. Since we want to create a report showing our revenue and the target by opportunity closed weeks, the change can be done with the following MAQL DDL and /gdc/md/<project>/ldm/manage2 resource.

CREATE ATTRIBUTE {a.target} VISUAL(TITLE "Target") AS KEYS {f_target.id} FULLSET;
CREATE FACT {f.target.quota} VISUAL(TITLE "Quota") AS {f_target.f_quota};
CREATE DATASET {ds.target};
ALTER DATASET {ds.target} ADD {a.target}, {f.target.quota};
ALTER ATTRIBUTE {closed.date} ADD KEYS {f_target.dt_closed};
SYNCHRONIZE {ds.target};
SYNCHRONIZE {closed.dataset.dt} PRESERVE DATA;


Here we created attribute Target with fact Quota, and connect it to the date dimension Closed Date. The model now looks like:


Upload Data in PHP

We are ready to start using the phpgdc library. Suppose we get data from an HTML form with fields:

  • Quarter : Specify quarter
  • Year : Specify Year
  • Quota : Specify the Sales Quota for a given time period

Since uploaded data to GoodData platform should contain date, we need to convert quarter and year into a date.

$month = 1 + 3 * ( intval( $_GET['quarter'] ) - 1 ); // 1, 4, 7, 10
$year = intval( $_GET['year'] );
$quota = floatval( $_GET['quota'] );
$date = sprintf( "d-d-01", $year, $month );


Then we may start with the GDC class. To be able to upload data you need to provide admin access to the project.

$gdc = new GDC;
$gdc->login( 'your.name@example.com', 'password' );
$gdc->set_project( 'your-project-id' );


We logged into GoodData platform and selected working project. It is also possible to list all projects, and to list all datasets in the project. But we are going to manipulate dataset ds.target only.

$ds = $gdc->get_dataset( 'ds.target' );


The variable $ds now points to an instance of GdcDataset class. First we need to download dataset information and uploading template.

 $ds->read_sli_template();


It is possible to get a list of all dataset columns where there are only two columns in our case.

$ds->get_columns_sli(); // returns array ( f_target.dt_closed, f_target.f_quota )


We want to load data incrementally to protect existing data, and since there is a relation to a date dimension, we run automatic constraint setup.

$ds->set_sli_incremental();
$ds->auto_set_date_format();


The order of the columns is very important for us, since in this order we should put the data into the following function.

$ds->prepare_load( array( array( $date, $quota ) ) );


We have added our data in two-dimensional array. Clearly, inner arrays represent rows. Now we just start to load of the data set.

$ds->do_etl();


And we are done. An ETL process is running, and in a while we will have the data in the platform. Report it This is the final step of this tutorial. Let us start with a very simple report showing quarterly revenue status and target. We create a metric like

SELECT MAX( Quota )


And put it into report together with prepared metric Amount and filter Quarter/Year (Closed) = This. Display the report as a Bullet chart, and you get a very nice graphical visualisation of your sales status to be pasted into a dashboard.

Be careful

Remember that the PHP script runs on your web server, and there are usually limitations on memory or execution time which can be used by PHP scripts. So use this technology to load only small amount of data and not very often, since there is a lot of relatively time consuming communication with the GoodData platform, and you need to keep all the data in memory during the execution. Use cases like in our example are exactly what the tool was designed for: from time to time to load a few lines into a small dataset.