GoodData Developer Blog

Updating the Variable Using REST API

2012-02-15T00:00:00-08:00

Updating the Variable Using REST API

by Jiri Tobolka (@jirtob)

Do you use variables in your analytical projects quite often? If so, you surely need to update them from time to time. That means you’ll definitely be interested in how to update the variable values programmatically. As in other use cases, our Platform is fully covered by REST API, so this can also be done using API resources.

Variable API

As mentioned before, we’ll use our REST API with several API calls and payloads. By following the steps below, you’ll be able to update variable values. A variable value can be a numeric value or a filter. A numeric variable type is simple and contains only a single value. If you create a filter variable, you will need to select an attribute value that’ll define the filter. Just before we start the first API call, I would like to describe the concept of variable API. If you are not sure what the variable is, see the following documentation.

Remember that variables were formerly called prompts, so while using GoodData API, you might see both terms being used. Every variable is represented by a prompt object that can be associated with one or multiple prompt answers. A prompt answer is a specific variable value associated with the user or the project.

As you know, variables can be used in reports or metrics. In our example, we have the variable - Department - and we want to update a user specific value.

Step 1 - Get the list of variables/prompts:

As mentioned earlier, variables were formerly known as prompts. So, if you want to get list of all variables in the project, you will use GET call as specified below:

GET -> https://secure.gooddata.com/gdc/md/PROJECT_ID/query/prompts

the answer should look something like this:

{
    "query": {
        "entries": [
            {
                "link": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/256",
                "author": "/gdc/account/profile/19765",
                "tags": "",
                "created": "2012-01-11 10:25:45",
                "deprecated": "0",
                "summary": "",
                "title": "Department",
                "category": "prompt",
                "updated": "2012-01-11 10:25:45",
                "contributor": "/gdc/account/profile/19765"
            },
            {
                ...
                ...
            }
        ],
        "meta": {
            "summary": "Metadata Query Resources for project obv8xyny3dygpb10t2rpai4tjw5nfcif",
            "title": "List of prompts",
            "category": "query"
        }
    }
}

Step 2 - Find variable ID

To update the variable value, you need to know what the variable item ID is because you’ll need it in the next step. Grab the prompt URI from previous step (that uri inside the link tag) and use it to search the variable item ID in the following resource:

POST -> https://secure.gooddata.com/gdc/md/PROJECT_ID/variables/search

with following payload:

{
    "variablesSearch": {
        "variables": [
            "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/256"
        ],
        "context": [
            
        ]
    }
}

The API will give you the following JSON with all variable answers:

{
    "variables": [
        {
            ...

            },
            {
            "uri": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/variables/item/4",
            "prompt": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/256",
            "related": "/gdc/projects/obv8xyny3dygpb10t2rpai4tjw5nfcif",
            "level": "project",
            "objects": [],
           "expression": "TRUE",
            "type": "filter"
        },
        {
            ...
            ...
            ...
            },
            {
            "uri": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/variables/item/8",
            "prompt": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/256",
            "related": "/gdc/account/profile/30672",
            "level": "user",
            "objects": [
                {
                    "category": "attributeElement",
                    "title": "HQ Marketing",
                    "attributeUri": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11",
                    "uri": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11/elements?id=3"
                },
                {
                    "link": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11",
                    "author": "/gdc/account/profile/19765",
                    "tags": "",
                    "created": "2012-01-23 10:11:25",
                    "deprecated": "0",
                    "summary": "",
                    "title": "Department",
                    "category": "attribute",
                    "updated": "2012-01-23 10:12:28",
                    "contributor": "/gdc/account/profile/19765"
                }
            ],
            
            "expression": "[/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11] IN ([/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11/elements?id=3])",
            "type": "filter"
            }
    ]
}

The first part is the default variable answer and the rest of the JSON represents the user-specific variable answers. As you can see the default answer is “ALL” because the objects array is empty. It means the filter contains all attribute values.

The expression value is what describes which attribute value belongs to the specific user. The user uri is a value of the related field.

Step 3 - Updating the Variable value

This is the key part. From the previous step, we’ve found the item ID. In our example, we would like to update the Variable value that is identified by item ID = 8. By POSTing following JSON, you’ll update the variable to the new value. See the HTTP request:

POST -> https://secure.gooddata.com/gdc/md/PROJECT_ID/variables/item/8

{ 
 "variable": { 
 "expression": "[/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11] IN ([/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11/elements?id=5], [/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11/elements?id=3], [/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/11/elements?id=7])", 
 "level": "user", 
 "type": "scalar", 
 "prompt": "/gdc/md/obv8xyny3dygpb10t2rpai4tjw5nfcif/obj/256", 
 "related": "/gdc/account/profile/30672" 
} 
}

The structure tells us that we’ll update the variable with the value that is defined by the expression. The expression is the part where you need to put the new variable value. In this case, you’ll set the user related variable to the attribute values that have

ID = 5,3 and 7.

As you can see, you are able to set more variable options. The level specifies if the variable is set for the whole project or for a specific user. Type could have two values - scalar or filter. As you probably know, scalar is the constant number and filter is the variable that is defined by specific attribute value.

Well, we’ve successfully updated the filter variable value. Let us know how it works for you!

Determining the Attribute Value ID

2012-01-09T00:00:00-08:00

Determining the Attribute Value ID

by Jiri Tobolka (@jirtob)

Hello everybody,

Today we have a basic and useful tip. I would like to describe some common principles in GoodData. I would show you how and where you can find the attribute element IDs. The following workflow will help you use the GoodData REST API, especially when you create the Mandatory User Filter.

For those who are new to GoodData, every object in our platform has its own unique ID. That means that you can easily call

https://secure.gooddata.com/gdc/md/PROJECT_ID/obj/OBJECT_ID

and you will get the object definition. So every report, attribute etc. has its unique ID and you can quite easily get it by calling the API. This is the first type of ID in GoodData. You probably already know how to use it.

Today I would like to show you how you can identify the ID of given attribute value. Each attribute value is represented by unique ID and this ID is different to the metadata object ID.

Imagine that you would like to create new Mandatory User Filter. The question is - What is the easiest way to determine the attribute value ID?

You want to filter reports by a specific department (which is a project attribute) for a specific user. You know the department’s name, but you are not sure about the given department (= attribute element) ID. Let’s use our grey pages.

The first step is well known, login to the GoodData platform using your credentials and grey pages. Go to

https://secure.gooddata.com/gdc/account/login

Enter the username and password. After submitting, you will receive two links, don’t forget to click on one of them! This will set your Temporary Token cookie needed for authentication.

Secondly, go to https://secure.gooddata.com/gdc/md/ where you can see all your existing projects. Select your project, click on the link, and you are in the project metadata resource. Now, go to the https://secure.gooddata.com/gdc/md/PROJECT_ID/query/attributes , see the screenshot below

As you can see, all project attributes are listed here. Select the attribute that you want to use and click on the link again. It will bring you to the attribute description.

In the picture below, the attribute object ID and URI are highlighted by red arrows and the object element’s URI is highlighted by green arrows. Remember that attribute elements belong to a specific attribute display form. Keep in mind that you can set multiple display forms for one attribute.

Now the final part. Every attribute has some values and values may differ from various display Forms. These values are called elements and every element has its unique ID. See the element description for the Department attribute in the screenshot below

As you can see in the screenshot above, you can easily determine the given element ID. In our example, the ID of the Human Resources Department is 4.

Just before finishing I would like to show you the Mandatory User Filter expression based on what we have from the example above:

[/gdc/md/<project-id>/obj/<object-id>]=[/gdc/md/<project-id>/obj/<object-id>/elements?id=<element-id>]

Remember that attribute element ID is totally different ID than object ID (report, attribute, metric)!

As you can see, it is necessary to know the attribute element ID to create new Mandatory User Filter. Read the article that will describe you the whole Mandatory User Filter creation process.

Have a nice day!

Export selected Report using Grey Pages

2012-01-06T00:00:00-08:00

Export selected Report using Grey Pages

by Jiri Tobolka (@jirtob)

Hi Devs!

Our friends from Keboola recently wrote a nice article about how they integrated the OpenBrand app with some magic in the GoodData Platform and how to get the raw data file from it. Today, I would like to show you a similar use case. We are going to export a selected report into a PDF file using our so called grey pages. Let’s follow this workflow:

Find the Report Definition
Compute the Report using the xTab Report executor and Report URI
Get the new Report Result
Export the created Report Result
Download the Exported file

Remember that you can use this workflow to integrate this feature to your custom App. The sample API calls are shown in our API Documentation.

Well, what is the case…I really like one of my report and I would like to export it and send it via email as an attachment. See the example below.

I would like to show you how you can use this flow to get the report in PDF/CSV/PNG. First of all, you’ll need to identify your selected report. Then find it (using grey pages) and get the Report Definition URI. Let’s open up your browser and type in following address:

https://secure.gooddata.com/gdc/md/PROJECT_ID/query/reports/

In the url above, you can find a full list of your reports, choose the right one and you will get the Report description (in JSON format), where you can find the Report Result URI.

The report description is what we need. In the picture above, you can see the green and red arrow. The green arrow is Report Result URI and the red arrow is Report URI. One Report could have a multiple Report Results. Latest Report Results is in the end of this part of JSON. What we need to tell the xTab report executor to compute the report is the Report URL. Let’s copy it to a clipboard. We’ll paste it into the corresponding input field on following resource:

https://secure.gooddata.com/gdc/xtab2/executor3/

This is the Report executor. Here you can paste the previously copied Report URI and let the Executor to compute the latest Report Result. Again, you will get the Report Result JSON, but now with the updated results. This may take a while depending on report data size. During the report computation, API will return the 202 HTTP status. When computation is finished, copy the Report Result URI highlighted in the screenshot below.

Now, with the latest Report Results URI in your clipboard, go to the Report Exporter that is located in following address

https://secure.gooddata.com/gdc/exporter/executor

This resource represents the Report Exporter. It’s the place where you can insert the Report Result URI and choose your preferred format. The Exporter will do its job and will give you the result URI, and also it is the place where you can download your exported report. Again, the report exporting will take a while. During the exporting process API will, as in previous step, give you the 202 HTTP status.

Finally, the report in PDF is downloaded in your computer! Got it? Let’s use it!

Advanced MAQL Reference Guide RELEASED!!!

2011-12-12T00:00:00-08:00

Advanced MAQL Reference Guide RELEASED!!!

Folks,

We have something special for you today. For the last few weeks, we’d been preparing the complete MAQL Reference Guide, that covers following topics:

MAQL Basics
Operators
Functions and macros
Logical Expressions
Common MAQL DDL Clauses
Common MAQL DML Clauses
MAQL Queries and subqueries (Metrics)

It covers the whole MAQL topic, and the complete guide has around 30 pages. As of today, you can download it and have it at your disposal while developing your Analytics App. The Reference Guide is well structured with tons of useful examples along with highlighted syntax.

Download the MAQL Reference Guide

Let us know what you think! And feel free to share it!

Deleting Records from Datasets

2011-12-12T00:00:00-08:00

Deleting Records from Datasets

by Michael Stencl & Jiri Tobolka (@jirtob)

Not sure how to delete some data from your project? Well,we’ve already had an introduction to this particular topic, but by popular demand, we’ve been asked for some more examples. So, today we’ll show you some more tips and how-tos! The MAQL DML (Data Manipulation Language) is here for you! Also known by it’s powerful nickname - Destructive MAQL.

It should be used very carefully, because if you delete the attribute value, You won’t be able to roll-back it. We have several use-cases which explain how powerful the delete command is. The most important issue is the referential integrity corruption.

When deleting rows from an attribute table. You must keep in mind which of the affected values are directly connected to the fact through the facts of table (in our HR example LDM is the facts of table Salary). See the most general syntax of the delete statement below:

DELETE FROM {attribute.factsof} WHERE <condition>

Now, you basically know what you can do by using it and it’s to time to show you where to start. You want to start using it, right? GoodData doesn’t support running the DML statements from the UI, so you’ll need to navigate around the grey pages. The command line for running the DML statements is located in the following URL

https://secure.gooddata.com/gdc/md/<project_id>/dml/

You can alsi use our gray pages to use DML. See the screenshot below:

We also extremely recommend you to validate your model before and after each DML statement to be sure no referential integrity errors are produced after your deletion.

The following example deletes or destroys all of the rows in the Salary table:

DELETE FROM {attr.salary.salary}

This is a very unusual example because normally, we don’t like to delete all of the rows.

We start with the condition construct. The conditions support most of the rational (>, <, =) and logical (AND, OR, NOT) operators. The left side of the condition can be defined from the fact element or the label defined on the attribute. The right hand side provides a value expression.

The following example shows one of the elementary situations where we delete all the rows from Salary facts of table, where the value of payment is lower or equal to 10000. For now, the lower or equal sign (“=<”) is not supported in the DML conditioning.

DELETE FROM {attr.salary.salary} WHERE {fact.salary.payment} < "10000" OR {fact.salary.payment}="10000";

Another situation where the delete statement is very handy is when you need to delete all rows where the fact has zero value. This example is shown below:

DELETE FROM {attr.salary.salary} WHERE {fact.salary.payment}=0;

Think about a situation when you have some rows in fact tables where there are values of zero. In this situation, the facts Volume or Close price having zero value are affecting the resulting report. Then, we’ll need to delete all of the rows which have zero Volume or the Close Price is zero.

DELETE FROM {attr.salary.salary} WHERE {fact.salary.payment}= 0 OR {label.salary.dt_payday} = "2010-02-29";

Still in need of some more examples? OK, we have something for you. Another example on how to delete rows from an attribute is by using the defined attribute label. If we want to delete all rows where the name of the person (defined by label) is Ed or Lin, we use following statement.

DELETE FROM {attr.employee.employee} WHERE {label.employee.employee.firstname} IN ("Ed","Lin");

Another interesting use-case is deleting of the old data attribute values. For example when you need to delete all rows where the date is older than 1st January of 1999. See the example below

DELETE FROM {attr.salary.salary} WHERE {label.salary.dt_payday} < "2006-02-01";

The right handed side of the condtion (“2006-02-01”) must have the same format as defined by the label otherwise an error will be reported.

Note that if you, by mistake, try to use anything other than a label in the condition of our delete statement, then an error occured. Note that you are not allowed to delete any rows from the date dimension provided by GoodData.

Delete all rows where for the 2010 (all the dates are between 1st Jan and 31st Dec 2010). Again, we have an example. See below:

DELETE FROM {attr.salary.salary} WHERE {label.salary.dt_payday} BETWEEN "2010-01-01" AND "2010-12-31";

If you want to delete any attribute value, you have to start by deleting the referencing attribute values of the attribute we’d like to delete. If you won’t keep this workflow, referential integrity will be broken.

So, if you want to delete department with ID = 1, you first have to delete all the employees who work in that department.

Remember, after you finish all the delete operations, it’s important to validate your model again. The validation shows you the actual status and checks the referential integrity of the whole model.

The validation is located in the grey pages of your projects which you can find in the following URL:

https://secure.gooddata.com/gdc/md/<project_id>/validate/

Let us know how it works! And for more information about MAQL, download our new Advanced MAQL Reference Guide

Download the MAQL Reference Guide

Keboola Academy - hacking GoodData vol 01

2011-12-09T00:00:00-08:00

Keboola Academy - hacking GoodData vol 01

by Keboola

Hi, welcome to Keboola Academy!

We love playing around with the GoodData platform. Recently for our friends at OpenBrand.com, we implemented an “artificial intelligence” solution powered by the GoodData engine. OpenBrand needed to analyze their user behavior in their Brand Management application, apply some logic to it and use results as input data for their recommendation engine. The logic is quite complicated, so we let GoodData take care of that :-)

So, please allow us to share with you how we managed to integrate an application with the extraneous GoodData BI platform.
It’s a bit tricky, but just follow the steps in this article, and you can accomplish the same thing quite easily.

Firstly, for the purposes of this article, we need to assume that you already have a GoodData project loaded with data, and that you know the IDs (URIs) of the GoodData reports that you want to use. Also note that you must be authenticated prior to each API call as described here.

So, to get the report results from GoodData to our external application is a two step process.

First, we need to create a fresh report execution by using the GoodData “Cross-Tabulation” API. To do this, simply send a POST request to

https://secure.gooddata.com/gdc/xtab2/executor3

and add your report URI as a parameter.

GoodData should respond with a “200 Created” code and return a URI object. Here is a sample request and response pair:

POST https://secure.gooddata.com/gdc/xtab2/executor3

Accept: application/json
Content-Type: multipart/form-data

reportURI="/gdc/md/{PID}/obj/{report_ID}"

Example result:

{
     "reportResult2" : {
         ...
     
          "meta" : {
         ...
               "uri" : "/gdc/md/{PID}/obj/{report_result_ID}"
         ...
                   }
     }
}

Now that we have the up to date report results URI, we can send another POST method to the GoodData “Export” API (https://secure.gooddata.com/gdc/exporter/executor) to retrieve the raw data.

For example:

Request:

POST https://secure.gooddata.com/gdc/exporter/executor

Accept: application/json
Content-Type: multipart/form-data

uri="/gdc/md/{PID}/obj/{report_result_ID}"
format="csv"

Response:

{   
     "uri" : "/gdc/exporter/result/{PID}/{ID_of_raw_data}"
}

The response contains the URI that holds our raw data, and we can retrieve it with a GET request. Note that if the response status is “202 Accepted”, the data is not yet available. In this case, wait a few seconds and try again.
When the data is ready, we receive a response with status “200 OK” and the response body contain the raw data we want.

Request:

GET /gdc/exporter/result/{PID}/{ID_of_raw_data}

Good Luck!
@Keboola

Let's get started with Mandatory User Filters

2011-12-07T00:00:00-08:00

Let’s get started with Mandatory User Filters

Hey, GoodData release #64 will be live pretty soon and you’ll definitely be interested in what we have in store for you. Mandatory User Filters…Not sure what to expect? Let’s imagine the following situation: Your company has multiple departments, for example: Engineering, Accounting, HR etc.

Let’s say, you’d like to create a report, but want to set a filter for a specific user. Imagine, we would like to allow one user to see only numbers for a specific department (i.e. Human Resources), because there is no need to let him check the other department’s numbers. How can we do that?

Go through the following example and after reading it, the new user filter will be created. In practice, we will filter the report by a specific attribute element (attribute value).

Attribute = Department
Attribute element = Human Resources

First of all, lets check the report without the Mandatory User Filter defined. The user will see the following:

Note: This time you can filter by attribute elements. It is not possible to filter report i.e. by metric.

The Basics

As always, we prepare a simple API, so you can play around and start integrating into your Apps!

So, let’s start. The Mandatory User Filter is a standard object in our platform. As any other object, it has a URI and you can set it up using API or find it using the gray pages at md/query/ context. The steps will be the following:

1) Create a new user filter object
2) Assign the user filter object to the user/users
3) Check the functionality

Creating new Mandatory Filter Object

To create a new Mandatory Filter Object, you must be logged as an Admin. Creating a new Mandatory Filter Object is very similar to our other API calls. You need to define json, and POST it to the specific resource. See the screenshot from our popular REST Client for Firefox.

The resource for creating new Mandatory User Filters is the following:

https://secure.gooddata.com/gdc/md/<project-id>/obj

Here is the specific json payload:

{
    "userFilter": {
        "content": {
            "expression": "[/gdc/md/<project-id>/obj/<object-id>]=[/gdc/md/<project-id>/obj/<object-id>/elements?id=<element-id>]"
        },
        "meta": {
            "category": "userFilter",
            "title": "User filter - HR Department"
        }
    }
}

They key part is in the expression statement, where you define an expression for the given user filter. You can not only use =, but also <>, IN or NOT IN inside an expression.

The only “tricky part” is that you need to know an attribute ID and attribute element ID. The easiest way how to get these identifiers is to use our “so called” gray pages. Go to following page:

https://secure.gooddata.com/gdc/md/<project-id>/query/attributes

Now, select your attribute and find the URI down in meta section. Then, you’ll need to specify the attribute element that corresponds to the Human Resources Department. This can be done by using the elements link that will give you a list of all attribute elements. You can find it and use it’s identifier in the json payload that is shown above.

The API response is 200 HTTP header and URI of newly created User Filter Object. As a next step, check the user filter existence by calling GET to the following resource:

https://secure.gooddata.com/gdc/md/<project-id>/obj/<created-object-id>

Assign the MUF to the User object

Once we have the new filter defined, it’s time to assign it to a user. Similar to the previous step, you’ll need to specify user ID, you can find it on /gdc/projects/<project-id>/users, where you can find all users in the project and select the corresponding identifier.

The following resource and payload will connect the user with the user filter.

Note: You can, for sure, specify multiple filters for one user.

https://secure.gooddata.com/gdc/md/<project-id>/userfilters

{ 
	"userFilters": {
        "items": [
            {
                "user": "/gdc/account/profile/<user-id>",
                "userFilters": [
                    "/gdc/md/<project-id>/obj/<user-filter-object-id>"
                ]
            }
        ]
    }
}

As a response, you’ll get json with the information that tells you whether the user filter was assigned to the user. Have a look at this example:

{
  "userFiltersUpdateResult": {
      "failed": [],
      "successful": [
           "/gdc/account/profile/<profile-id>"
        ]
    }
}

Everything went smooth, right? So let’s check the report numbers visibility for a defined user. See the report:

Well, that’s all for today. Let us know your opinion, ideas and feedback. We can’t wait to hear (and also see) how you, developers, are using the features that we are bringing to you. We are driven by your passion in using our tools and API’s. Stay tuned for next time!!!

Time over Time Comparison

2011-11-25T00:00:00-08:00

Time over Time Comparison

by Jiri Tobolka (@jirtob)

Hi everybody! No matter if you work on a killer Analytics App, or just started using GoodData, you’ll always want to learn something new. I hope I’ve got something for you. Today I would like to teach you how to use transformations in metrics.

First, let me introduce the problem. Imagine you are a data analyst and you would like to create a time over time comparison report for your Sales Director to have a better overview of the company’s sales data from your SalesForce CRM. Got it? Let’s do it!

Time over Time Comparison reports

What will you need? You’ll definitely need some good data, and some knowledge on how to create these type of metrics with MAQL (our query language). Starting with one of the simplest metrics in Sales Analytics - Amount[SUM]. This metric is predefined by default in our Sales Analytics App and gives you a total amount of all opportunities for the last snapshot. Remember that in Sales Analytics, you need to specify the snapshot. Not sure what snapshots are about? Read this article first to be sure how to use it!

Now, let’s tune it up to see a time over time comparison! See the metrics below:

SELECT Amount[SUM] FOR Previous(Quarter/Year(Closed))

SELECT Amount[SUM] FOR PreviousPeriod(Quarter/Year(Closed))

The first metric computes a number for a previous specific period of time. If you use this metric side by side with the basic Amount[SUM] and slice them by Quarter/Year and Month/Year. The metric is used in the report shown below.

The second metric is different from the first one. The FOR PreviousPeriod() is used there. What is the difference between the FOR Previous and FOR PreviousPeriod statement? The FOR Previous is connected to the date dimension and gives you the aggregation based on previous time period from the date dimension. In our example, it gives you the same month but from the previous quarter. The FOR PreviousPeriod gives you a previous value based on records in report. See the examples in a report and tables below.

Got it? Do you see the difference? The report above and metrics with Previous statements are based on Quarter level. In the tables below, you can see more examples to understand it clearly:

Anyway, what you can also use is a second parameter inside both statements. It’s a number that tells the engine to skip the given number of the period. Again, for better understanding, see the example below:

SELECT Amount[SUM] FOR PreviousPeriod(Quarter/Year(Closed),2)

As you can see, with additional parameter added to the metric specification, it skips two Quarters and gives you the corresponding number.

Alternatively, it is also possible to compare against future dates using two other special MAQL functions: FOR Next() and FOR NextPeriod(). Both of these constructs function in the same was as FOR Previous() and FOR PreviousPeriod() above.

Year to Date Reports

Another use that I would like to show you is Year to Date Reports and metrics. Using this special construct, you can create a metric with “This” as a day or “Yesterday”. For better understanding, let’s see a simple example

SELECT Amount [SUM] WHERE Quarter/Year(Closed) = {This}

The metric above gives you Amount of all opportunities that were closed this Quarter. You can also use {This} - 1 that is the same as {Previous} statement. Not enough? Let’s combine it

SELECT Amount [SUM] WHERE Quarter/Year(Closed) = {Previous} AND Day of Quarter = {This}

In this example, {Previous} would mean “Previous (Last) Quarter” and {This} would refer to “Today as a numeric Day of the Quarter” (i.e., Day 62 of the quarter).

So, let your imagination work! Use transformation, be creative and…data driven ;) See you next time!

New CL Tool v.1.2.43 released

2011-11-21T00:00:00-08:00

New CL Tool v.1.2.43 released

We would like to tell you that we released a new CL tool version 1.2.43. This version contains following fixes:

Many internal fixes improvements and code polishing from the GoodData engineering code review.
Improved gdi.bat script that correctly reports error to the calling script.
Improved compatibility with the Java 7.

You can download it from Github now! If you have any questions, feel free to ask us!

Add user to project with REST API

2011-11-07T00:00:00-08:00

Add user to project with REST API

by Jiri Tobolka (@jirtob)

Dear Developers,

We are quite often asked about how to add users to a project using GoodData REST API. We already have description on how to use the CL Tool to add users programmatically. We are very aware of the fact that some of you are not really well versed using our CL Tool and would prefer the REST API way. If the REST API’s are your cup of tea, this blogpost is for you.

Let’s go through a simple example on how to use our User Provisioning API. First, you’ll need to create the project where you want to add the user. Basically, we will go through following three steps:

create a project
add user to a domain
add user to a project

Just one more thing before we really start applying our API calls. You’ll need to set the Content-type header parameter to application/json and most of time you will send a json in the REST API calls.

Project Creation

No project - No place to add users. As mentioned before, this should be the first step because if there is no project, then we won’t have a place to add the users.

Firstly, you can call the API to get a list of your projects. This can be a bit tricky because the resource for getting all of your projects is not located on /gdc/projects but on gdc/md. That means by calling GET to

https://secure.gooddata.com/gdc/md

If everything is OK, you’ll get HTTP 200 parameter as a response.

Now, let’s create a new project. Send POST http request to following resource

https://secure.gooddata.com/gdc/projects

{ "project" : { 
     "meta": { 
         "title": "New Project", 
         "summary": "A new project"
      }, 
     "content": {
         "state" : "ENABLED" 
} } }

You should get a 201 Created header as a response with created project information in a body:

{"uri":"/gdc/projects/<project-id>"}

User Creation

We’ve got the project created, so now, we have a destination for the users we want to add. What we need to do now is to create a new user. There are several things to keep in mind here:

You need to have a domain set up
Only owner of the domain can use POST AccountSetting on this resource
Password and verifyPassword must match, otherwise the resource reports validation error

What does this mean? It means if you don’t have a domain created, you’ll need to ask our operations team to create it for you. You can ask them via our Support Portal.

The domain has already been created. You are authenticated. So now, It’s time to provision users to your domain. Again, this is done via the REST API call. Use POST request to the

{
 "accountSetting":{
    "login": "user@login.com",
    "password":"PASSWORD",
    "verifyPassword":" PASSWORD ",
    "firstName":"FirstName",
    "lastName":"LastName",
 }
}

This should be a response if everything was POSTed accurately:

(201 Created) % an uri of newly created user in format /account/profile/<user-id>

Nice! We’ve just created new user in our domain!

Add user to a project

Let’s continue with our story, this is not the end. Remember what we want to do? Sure, we would like to add user to a specific project. How do we do it? Use POST .json to following resource:

Following example will add new user that is identified by userId to the project in specific role (role and project are also specified by ID). Role ids are the following:

(1: Admin; 2: Editor; 3: Embedded dashboard only)

{ "user" : {
     "content" : {
           "status":"ENABLED",
           "userRoles":["/gdc/projects/<project-id>/roles/<role-id>"]
                 },
     "links"   : {
           "self":"/gdc/account/profile/<user-id>"
                }
    }
}

If the API gives you a Header 200 respond, that’s a reason to smile because you have successfully added the user! Now, you are ready to integrate these calls to your custom apps.

One more thing. If you use status: DISABLED in user provisioning json file (shown above), you are able to disable the user from the project.

Let me know what you think! Best Regards,

The Event Store Introduction

2011-10-26T00:00:00-07:00

The Event Store Introduction

by Jiri Tobolka (@jirtob)

Hello all you analysis ninjas! Today I’d like to start a huge topic which is very wide and will cover multiple blogposts. I would like to introduce you to a brand new data warehousing technology that we developed in the GoodData Platform. We have been using it internally some time and now, it’s time to release it to the public, so anyone of you GD developers, can start building analytics projects and apps using this new technology.

Snapshotting

Remember earlier Analyzing Changes blogpost? I hope so! If you want to use the snapshotting technique you need to store all your data somewhere. By storing your complete data, you’ll have access to all of the data snapshots in your storage and you will be able to analyze changes.

Imagine that you have, let’s say, about 100k rows of data in your SalesForce application and you would like to upload it on regular basis (i.e. weekly). You also want to store all weekly loads, because you’ll need to have the history of data values. This is very memory consuming process.

See the picture above. Only one row was changed, but we loaded all three rows. Until now, you’ve had to upload all of the data repeatedly which can be inefficient. In the classic data warehouse system, every record is loaded every time you upload your data. Every time you start loading data from the source system to your data warehouse, it will be a full load no matter if the data was changed or not. The size of your data warehouse increases very quickly.

We need something different. We need to use a better approach with more sophisticated technology. We need the Event Store!

The New Era

At GoodData, we’ve worked very hard to develop a better data warehouse. Something that will help you work with a huge amount of data easily and dynamically. We needed something that won’t be wasting the data warehouse memory and also help you with the whole ETL process.

What we’ve created here is brand new data warehousing technology. It’s called the EventStore. The EventStore decreases of the amount of data in your data warehouse but keeps all your historical data.

Imagine, you are using Salesforce as your CRM solution and you want to analyze your data to get some regular insights. You need to have your data up-to-date in your BI system. Also, you want to keep your historical data in order to look back in retrospect and as we have mentioned earlier, the classic approach fails here. How does the Event Store work?

The EventStore creates a special stream for every field. Every time you start loading your data that you had been previously downloaded from the source system (i.e. Salesforce, SugarCRM) the Event Store checks the corresponding stream. If there are some changes since the last upload, the Event Store will update them with a new record value. It also stores historic values.

As you can see, the size of your data warehouse will be increasing much slower, than if you use the classic storage.

The EventStore will enable you to load the data for a specific time period and with a specific granularity. You will be able to load for example all daily snapshots between beginning of the Quarter and Today. You can create another project and load a monthly snapshots from the EventStore here. As a result today, you will get a CSV file that you can upload to the GoodData Platform, create metrics, prepare reports and dashboards.

More Features to Come

Keeping your data warehouse at a reasonable size while, at the same time, keeping your data history isn’t the only feature. These are just basics. With Event Store, you will be able to make a transformation on an output and prepare pre-aggregated data or compute values. More features and integrations are coming up in a near future.

Stay tuned for the next blogpost of the Event Store series, where I will show you examples on how to start using Event Store in your projects. This is just a beginning.

How to use COUNT in metrics

2011-10-17T00:00:00-07:00

How to use COUNT in metrics

by Jiri Tobolka (@jirtob)

New to GoodData? Do you want to start creating some effective dashboards with your reports, then you’ll need to know how to create metrics. As mentioned earlier, in most cases, metrics are aggregated facts. However, sometimes you need to count non fact elements which are called attributes. Check out the following model, here I’ll explain how to create a COUNT metric.

The COUNT metric can be used with one or two arguments. Let’s see how it works in some common use cases.

Counting a number of salaries

Based on our model, Imagine that you are faced with the following scenario: you would like to create a simple metric that counts the number of salaries that had been paid. In this example, you’ll count the distinct values of an attribute (non-countable field). Therefore, you’ll need to use SELECT COUNT(Salary ID) as the metric definition in order to get the results you are looking for. This metric itself is not so useful but we can show the COUNT concept using it.

If you look on our model, you see that the Salary ID is not connected to other attributes and you are not able to slice and dice the metric that is defined as COUNT(Salary ID). Try to define a report with this metric now. You see that other attributes (Employee,…) are greyed out (see the screenshot below). The COUNT here focuses solely on the Attribute and always returns the same number.

Counting Employees that were paid

Now let’s say you want to do something a bit different. Let’s say that you want to COUNT number of Employees that were paid a salary. Then you will need to explain what you want to count. If you put an Employee together with a Salary in a report to count the number of employees with salary, then this is the time when you would need to use the second COUNT function parameter.

The Records of Salary attribute is needed to count the number of Employees that were paid a Salary. The metric syntax is COUNT(Employee, Records of Salary) as shown above.

You have two options how to create this type of metric. The first option is to add new metric from report dialog. Here, select the COUNT operation, then choose any attribute and the count metric with two arguments is created. If the attribute that you want to compute is connected to more than one dataset, you have also specify, which dataset you want to compute.

The second option is to use the Advanced Metric Editor to create this metric. Click the “advanced” link in the WHAT dialog menu, then follow the Custom metric link, make sure you name your metric (e.g. # Employees with Salary) and insert the metric definition:

SELECT COUNT(Employee,Records of Salary)

Advanced examples

And the last but not least, we would like to show you some more advanced tips that you can use with the COUNT metric to improve your reporting. Keeping in mind our model, we would like to know how many employees from the store management were paid a salary greater than $5,000.

You can create a COUNT metric with a filter to find it out.

SELECT COUNT(Employee,Records of Salary) WHERE Department = Store Management AND Salary < 5000
SELECT COUNT(Employee,Records of Salary) WHERE Department = Human Resources AND Salary < 7500

etc…

Again, you’ll need to use the Advanced Metric Editor to create these metrics.

Happy counting!

MongoDB Behind the Scenes

2011-10-06T00:00:00-07:00

MongoDB Behind the Scenes

by Roman Pichlik

Some time ago, Jiri Tobolka wrote a nice introduction to the Notifications API on the GoodData platform. I would like to share with you the technical details behind the scenes, especially some details about the storage engine we’ve used for the notifications data…

There are three main entities in the Notification domain model - ChannelConfiguration, Subscription and Trigger. Their relationships are on the next picture.

We had been facing the issue of how to persist these entities. In the GoodData platform we keep most of data in the good old relational database (RDBMS), but we are all aware of the constraints. One year ago, we did a proof of concept for storing the data out of the relational database because the characteristics of the data didn’t fit into the relational model. Let me explain it in the Notifications model example.

We mentioned that the Notifications consisted of three entities. If we had chosen a relational database for storing them, we would have probably ended up with a normalized schema as you can see in the next picture.

There are seven tables if we count the join tables required for many-to-many relationships. Such a normalized schema would cause a couple of problems. We would have to add a new table for every new channel. We would also have to use the left outer join for querying subscription with associated channels and use cascade deletion on a database level or in application logic because of referral integrity.

We could store these entities as BLOBs but in this case, it wouldn’t be the best option because we wouldn’t be able to query such documents e.g. get me all notifications for project X.

Document Oriented Databases

The mental shift here is to realize that we deal with documents. We store, get, update, query and delete documents. We have been looking for a document oriented database. There are great NoSQL databases like Riak, Cassandra, Redis, HBase but most of them are key/value oriented. That means that if we were to use some of these NoSQL options would have to transform our documents to key/value structures with almost zero benefits over the conventional RDBMS.

Even with a document oriented database some concepts are similar to RDBMS. Documents are organized in collections. Collections are something like database tables in RDBMS. A collection allows you to store documents with a different structure or even type which is opposite to a database table where the structure is determined by columns and their types. We can say that document fields correspond with table columns, but there are differences such as, no strict type or schema. In general, there are no schema requirements for your documents, fields or collections. We call these databases schema-less.

It brings some advantages and disadvantages or things that should be kept in mind. When you want to add new fields in a new version of a document, you can migrate the old versions on the fly in runtime and reduce server downtime, for example. On the other hand, there are no data consistency constraints on the storage level and you can enforce them only in an application logic.

Let’s go back and see how the Notifications documents can be stored in a document-oriented database. There are two collections for channels and subscriptions. Documents are self-contained. A subscription document contains trigger definitions and meta definitions, for example. The relationship between channels and subscriptions are expressed weakly. Only an ID of a related channel document is held. There is no foreign key constraint. Documents can be updated or deleted independently and consistency is checked in an application logic.

If we are talking about a document oriented database then there are two well known solutions MongoDB and CouchDB. The fundamental difference between MongoDB and CouchDB is in querying and accessing the data. The CouchDB queries are expressed as Map/Reduce functions. Queries are special documents called views that contain defined Map/Reduce functions. You won’t be able to query data unless you define a view. The MongoDB queries are expressed as structured JSON-objects. MongoDB’s approach is more declarative opposite to CouchDB. You can define indexes over stored documents and a get performance boost when querying over fields in these indexes. MongoDB allows dynamic queries where an index does not exist. That’s very similar to RDBMS world. In addition MongoDB supports Map/Reduce as well, but it’s intended for data processing, not querying.

Documents in CouchDB are accessed over HTTP and REST interface. MongoDB uses a proprietary binary protocol on top of TCP/IP and therefore language specific driver is required.

MongoDB

After considering all requirements and technological constraints we chose MongoDB over CouchDB. The main reasons are:

querying capabilities - In general, querying data in MongoDB is more of a natural way than thinking in Map/Reduce.
driver - performance, very handy for clients
scalability - MongoDB supports (no hacks) sharding functionality by default. That’s very important for a SAAS provider such as GoodData.
tools - backup/restore/monitoring utilities, administration console, JSON and JavaScript as a first class citizen

MongoDB was successfully deployed on the production servers and developer machines. We use MongoDB with enabled journaling even if it has some performance penalties. This is not problem since we have mostly read operations. The second option would be deploy MongoDB in a cluster with replicating nodes. MongoDB supports auto-sharding, so we can shard data on a project base in the future.

Migrating Selected Objects between Projects

2011-09-26T00:00:00-07:00

Migrating Selected Objects between Projects

by Jiri Tobolka (@jirtob)

Hi there! Do you have multiple projects based on one project template or multiple projects cloned from one master project? Do you dream of a simple way how to copy new metrics or reports between projects?

Selected object migration is a new feature that has been implemented in GoodData since our latest release which was Release 60. So this is the way how to start!

Project Cloning

You probably know that it is possible to clone (export/import) the whole GoodData project. If not, find out more about how to clone project in our previous blogpost.

Export/Import Selected Objects

First of all, before we start migrating objects, keep in mind a few things.

Every object has its unique uri.
Projects must have the same logical data model if you want to migrate objects between them.
Migration works with metrics, reports and dashboards.

Imagine that you have a project template and several child projects (projects based on that template) that you want to populate with new reports or metrics or dashboards. Sounds simple enough, doesn’t it? Let’s see how you can do it!

Here’s a simple example. We created two HR projects (based on our well known CL Tool example, with the same LDM). Let’s say the first one is a sandbox project where we want to play around with metrics, prepare reports etc. The second one is customer’s project where we want to copy only objects (reports, metrics, etc.) that are prepared for the customer.

What we are going to do is to prepare a nice report in our “master” project and then we will copy this report to the child HR Demo Copy project. Follow this example and you’ll know how to do it via the so called grey pages and also with the CL Tool!

We will skip the report creation part (you can read more about creating reports here) and will continue on with exporting. As you can see below, we’ve created two reports in the HR project (which is our sandbox) and we’ve decided that the Total Payment by Quarter report is ready and we want to copy it to the HR Demo Copy project.

As you can see below, the HR Demo Copy is empty.

Note: You have to be logged in as one user when you do export/import. It is not possible to export metadata, send token to other user to import metadata.

Exporting objects

Our report is prepared and now we need to export it from the HR project.

We have several options how to export reports (or dashboards, metrics) from the project. The first option is to use the newly implemented CL Tool commands. The second option is to use our grey pages. Firstly, let’s see how to do it with CL Tool:

UseProject(fileName="...");
ExportMetadataObjects(tokenFile="...", objectIDs="...");

Note: This functionality is available in the CL Tool 1.2.40 or higher.

This command exports metadata objects with all of its dependencies. The tokenFile is a file where the import token will be stored (you’ll need it!). In objectIDs you will paste the comma separated list of the metadata object IDs. The Object ID is a number which is located on the end of i.e. report URL, as you will see below.

Remember that before the ExportMetadataObjects you must use the UseProject or the OpenProject command to open the source/master project.

If you are not familiar with the CL tool, choose the second option. You will need to go to the grey pages partial metadata export section which is located in the following url:

https://secure.gooddata.com/gdc/md/<project-id>/maintenance/partialmdexport

Here you can select the metadata objects that you would like to export based on it’s uri.

Wait a second! Where can we find the object uri? It can be found in the url address. For example, if you copy the end of the open report url address, you will see something like this:

/gdc/md/<project-id>/obj/<object-id>

The object metadata information is stored in this url.

Remember: When you use CL Tool for migration, you have to specify <object-id>, but when you want to do the same via grey pages you must specify the uri, which is the part of the url as you can see above.

In this example, the report metadata contains the author, title, uri etc. See the metadata page below, in our example ID of the exported object is :

Now, just simply paste the uri (you can paste multiple uris, one on each line) into the text input field and submit the request. The task that is invoked by you will generate an import token. You’ll need to store it somewhere in order to import the report.

Importing objects

It’s time to import our exported objects. Let’s see how it can be done with the CL Tool and with the grey pages. With CL Tool, use the ImportMetadataObjects command:

UseProject(fileName="...");
ImportMetadataObjects(tokenFile="...", overwrite="<true|false>", updateLDM="<true|false>");

The command imports metadata objects from the token. The tokenFile is the file which was generated during the export. The import command has two options: updateLDM - if true, the attributes, facts names and descriptions are updated. The second option is overwrite - if true, the existing metadata are overwritten. Currently only overwrite = true is supported.

Remember that you must call the UseProject or the OpenProject command before the ImportMetadataObjects to specify the destination where you want to import it.

If you don’t want to use the CL Tool for this purpose, you can use the grey pages. Go to the

https://secure.gooddata.com/gdc/md/<project-id>/maintenance/partialmdimport/

Here you paste the token that was generated during the export. (see the screenshot below)

After submitting, you’ll see the link that takes you to the page with the import task status. Here you can see if the import was successful or if something went wrong. If the import was successful, you can log into the HR Demo Copy project, go to the Reports section and check if the report is there.

As you can see the report was successfully migrated from one project to another. Remember that export/import should be done using one user account.

So, that’s the selected object migration. Isn’t it easy?! Do you like it? Let us know what you think!

Setting up the Notifications using API

2011-09-13T00:00:00-07:00

Setting up the Notifications using API

by Jiri Tobolka (@jirtob)

Hi everyone! Today we’ll take a look at a new feature that helps you being up-to-date with your data. Cory has already mentioned it in the end of his Building an Analytics App post. If you want to know what’s going on in your Analytics App but don’t want to login to GoodData every time you want to check your KPIs, then the Notifications feature is a must have.

How do GoodData Notifications work? You set the condition for a metric (i.e. number of Chatter posts per Opportunity < 16 ). Every time the condition is fulfilled, the application sends the notification to the specified channel. The specified channel can be for example the SalesForce Chatter or Twilio, for example.

Using the GoodData API via REST Client

Before we start setting up new notifications, we will show you how to easily work with our API. For this purpose you can use the Rest Client for Firefox that is extension for managing API requests.

Remember that you have to be logged into GoodData to use API. It is possible to log in via the UI or on

https://<hostname>/gdc/account/login

which is the authentication page of the API. See the screenshot below. The REST Client picks up the authentication (both from /gdc/account/login and from the UI) so you can log in just once. Follow the process that is shown below to set up new notification.

Introducing the Notification API

So, you want to start using the Notifications. That means you will need to set it up correctly. The Notifications API consists of two REST resources - Channel Configuration and Subscription Configuration. The Channel defines where the notifications will be sent to and the Subscription Configuration contains details about what and how often will the notifications be sent.

The Channel Configuration is located on following URL

https://<hostname>/gdc/account/profile/<profile>/channelConfigurations

Note: You have to use your profile ID in the request URL. This doesn’t work with profile Email.

and is used to configure delivery channels. GoodData currently supports two Notifications Channels - SalesForce Chatter and Twilio. To configure the notification that will appear on your SalesForce Chatter, you need to POST the payload that is similar to what is shown below.

{
   "channelConfiguration":{
      "configuration":{
         "sfdcChatterConfiguration":{
            "username":"your@email",
            "password":"passwordSecurityToken"
         }
      },
      "meta":{
         "title":"SFDC Channel"
      }
   }
}

As you can see you have to specify your SFDC username and password. The password is a combination of your SFDC password and generated security token.

Let’s see the picture below and learn how it works with the REST Client for Firefox. You have to define request type, request url, set the header content type to application/json, and paste the request body with json file as we defined it above. Then you can send the request. If everything is correct, you will receive 201 Created HTTP status.

Otherwise, if you want to set up Twilio as a channel, your POST should look something like this:

{
   "channelConfiguration":{
      "configuration":{
         "twilioSmsConfiguration":{
            "username":"username",
            "password":"AUTH TOKEN",
            "from":"+14086457515",
            "to":"+420724000000"
         }
      },
      "meta":{
         "title":"Twilio Title"
      }
   }
}

To use Twilio for the notifications, you need to define your Twilio username, password, source phone number and finally phone number the notification will be sent to.

Setting up the Subscription

So, we’ve set up the channel and now it’s time to move on to the second step of configuration. We’ll need to specify the notification condition, message and some other details. The Subscription Configuration resource is located on

https://<hostname>/gdc/projects/<project>/users/<user>/subscriptions

and we will set it up once again by POSTing following payload (sure not exactly this one):

{
   "subscription":{
      "triggers":[
         {
            "timerEvent":{
               "cronExpression":"0 0/5 * * * *"
            }
         }
      ],
      "condition":{
         "condition":{
            "expression":"f:executeMetric('/gdc/md/ChatterDemo/obj/130001729') < 16"
         }
      },
      "message":{
         "template":{
            "expression":"Average Chatter posts per Opportunity has fallen to ${f:executeMetric('/gdc/md/ChatterDemo/obj/130001729')} ..."
         }
      },
      "channels":[
         "/gdc/account/profile/1/channelConfigurations/4e313f3d300406ad568d3bec"
      ],
      "meta":{
         "title":"Avg Posts per Oppty < 16"
      }
   }
}

Now let’s describe the file above. First part of config file describes the triggers. It’s possible to set up multiple triggers, but in this moment only the timerEvent is supported. The timerEvent needs to have a CRON expression defined. The granularity of notification is driven by platform configuration. In other words, even if a user specifies that he or she wants to receive a notification every minute, the user will probably receive notifications every 15 minutes (depends on configuration). It’s also possible to specify a timezone by adding “timezone”:”yourtimezone” to the timerEvent element. Timezone format is described here.

As we mentioned earlier, the notification is sent only if the condition is fulfilled. The condition is based on a JEXL expression that has to result in a true/false value. In our example /gdc/md/ChatterDemo/obj/130001729 < 16 means that condition is true if the metric (which is identified by unique ID) is smaller than 16.

The template describes the notification message, that contains the JEXL template with the metric value. The channels element describes which channel the notification will be sent to, based on the configuration. Finally, the meta element contains the title of the notification.

NOTE: You can also apply GET, PUT or DELETE to the configuration resource to retrieve, update or delete the configuration. Also, all requests has to contain Content-type: application/json request header.

The Result

We’ve set everything up, so now it’s time to see the result. Every time the Average Posts / Opportunity falls below 16 the notification will be sent to our SFDC Chatter. Check out the screenshot below!

The notification is sent:

Yes, That’s it. I hope you will be up-to-date with your data after reading the blogpost!

Incremental Data Loading

2011-08-30T00:00:00-07:00

Incremental Data Loading

by Jiri Tobolka (@jirtob)

Well, you want to do some analytics. You will need to have complete and updated data. That means continuously loading your data which can seem difficult. However, this process can be fully automated using the GoodData CL Tool. Imagine you have a lot of data. It would be nonsense to do the full data load every time you want to update it. It would take too much time and can be very expensive. So what do we do? The solution is to use incremental data loading.

How Incremental Data Loading Works

Incremental data loading is a feature which is already implemented in the GoodData CL Tool. You can run it by adding parameter into the TransferData(incremental=”true”) command. When you are uploading data using the Incremental Data Loading, the existing data won’t be deleted. The existing data will be merged/replaced with the new data that are being uploaded and new data will be appended to the existing data. The merging/replacing existing data with the new records is possible thanks to the CONNECTION_POINT of every record. How does it work?

The data that is being uploaded may contain records with the CONNECTION_POINT value that already exists in your project. As you probably know, the CONNECTION_POINT is a CL Tool’s description of a column of your dataset (alternative to ATTRIBUTE and FACT). The data loading script simply compares the CONNECTION_POINT of existing records and replaces them with the new values. However, please keep in mind that if the incremental parameter is false, data loading script will delete all of the existing data and then load new data that you want to upload.

Setting up the CONNECTION_POINT

Now, let’s describe the CONNECTION_POINT functionality in more detail. The CONNECTION_POINT is basically the primary key of the data set. It can provide connections with other data sets (through the REFERENCE ldmType). If you are not familiar with joining data sets together, you can learn more about it here or see the XML schema example below.

<schema>
  <name>store</name>
  <columns>
    <column>
      <name>storeid</name>
      <title>StoreID</title>
      <ldmType>CONNECTION_POINT</ldmType>
      <folder>Store</folder>
    </column>
    <column>
      <name>store_name</name>
      <title>Store name</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Store</folder>
    </column>
      <name>employees</name>
      <title>Employees</title>
      <ldmType>FACT</ldmType>
      <folder>Store</folder>
    </column>
  </columns>
</schema>

The basic condition is to set it up correctly. Every single record of your data must have a unique value of the CONNECTION_POINT. If it doesn’t, records with the same value will be replaced during the loading. That means when you have three rows with the same CONNECTION_POINT value only the record that was uploaded last will be in your data. (replacing those two rows with the same value that was uploaded before the last record) As you can see on the example above, where the StoreID column is the CONNECTION_POINT of the dataset.

How to use Incremental data loading

Let’s see two examples of how to use the incremental data loading. As we said in the beginning, you are probably in a situation where you need to update the data daily and you don’t want to keep uploading all of your data repeatedly.

1st Example (Loading data for the last three days)

The first use case is simple. You have the analytics application and upload the data 3 days back till today every day. So every day you use incremental data loading that includes the data from the last three days. What will the script do during the data load in this case?

It loads the data and if some of it already exists (has the same CONNECTION_POINT value) it will replace it with the new values. If the records you are uploading are completely new, the script will append them.

This use case has another advantage. If the data load fails on any given day, the records will be added the next day. In this situation, the problem will be if data load fails 3 days in a row. If this occurs, the data will be missing from the application.

2nd Example (Google Analytics)

Another example is little bit more complex. It’s based on the specific behavior of Google Analytics. The most recent data from Google Analytics isn’t usually up to date, so typically you will need to load the data from the day before everyday.

For example, yesterday we used the Google Analytics API to get the page views and visits by referrers. The API may return something like this:

2011-08-10,Google,500,30
2011-08-10,Bing,50,15

Now, by adding the unique connection point to the records, our data load will look something like this:

ea3d273d6296684da21d3dc2aa82d0b7,2011-08-10,Google,500,30
f4583c4214d22677d991e25d041336ea,2011-08-10,Bing,50,15

In this case, the CONNECTION_POINT is MD5 hash which is computed from the attributes and its values. It can be easily done with the IDENTITY <transformation> element. If you add the IDENTITY transformation to the dataset, the column that is transformed is automatically filled with the MD5 hash of every non-fact column (attributes, labels etc.). The transformation column is added to the dataset and uploaded regularly. See the definition of the transformation column on the example below.

<schema>
  <name>store</name>
  <columns>
    <column>
      <name>storeid</name>
      <title>StoreID</title>
      <ldmType>CONNECTION_POINT</ldmType>
      <transformation>IDENTITY</transformation>
      <folder>Store</folder>
    </column>
  ...
  </columns>
</schema>

The next day, we use the same query and it returns larger values which is normal because yesterday’s data may not have been completed. The result will be:

2011-08-10,Google,509,33
2011-08-10,Bing,52,19

When we add our hash based CONNECTION_POINT to the data once again, we will get:

ea3d273d6296684da21d3dc2aa82d0b7,2011-08-10,Google,509,33
f4583c4214d22677d991e25d041336ea,2011-08-10,Bing,52,19

Finally, we’ll use the incremental data load to update yesterday’s incomplete data. This is very useful if you need to replace incomplete data with complete data.

See you next time!

Building a Project in GoodData

2011-08-17T00:00:00-07:00

Building a Project in GoodData

by Jiri Tobolka (@jirtob)

So you want to start analyzing your business data using the GoodData Platform and don’t know where to start. Well, you have two different options to choose from to help you get started - use Apps that are based on prepared project templates or build a custom project. If you are not sure about how to start building your own custom project on the GoodData Platform, use our Apps or read about building an app non-programmatically. This is very easy and you can start analyzing your data within minutes. The second option is more technical and powerful.

Let’s move on how to create a custom project. Imagine you have multiple data sources that you want to analyze using the GoodData Platform. Is it possible? Yes, it is. So, what are the steps to be taken?

You will start with defining a Logical Data Model. The Logical Data Model describes the structure of the data you’ll report on. It contains one or more datasets that can be connected together. What is in a dataset? Each dataset consists of Facts and Attributes. Facts are numbers that we will aggregate later in metrics. Attributes are names and properties that describe your Facts. For example you can compute the SUM of the Payment fact and slice it by the Department attribute in the HR demo that is outlined on the figure below.

The big advantage of GoodData platform is the fact that the Physical Data Model (database tables and columns) is automatically generated from the Logical Data Model. Read more in this article.

Once you define a Logical Data Model, you will need to load your data in your new project. You can use the CL Tool for the data loading. The CL Tool is powerful command-line tool for managing GoodData projects. It automates all common tasks that you’ll need to perform on your project including the LDM creation, data loading, user provisioning etc. Read this documentation to find out how to Create, Update and Clone your Project, or upload the data.

The CL Tool is built on top of connectors that allow you to transfer data from CSV files, database (JDBC) or many SaaS applications connectors (Google Analytics, Pivotal Tracker, etc.). Find out more about basic usage of the CL Tool in one of our previous blogpost.

Once you have your data inside the platform you are ready to create metrics. Metrics and Attributes are the foundation of every report that you build. Metrics are, as mentioned above, aggregated (SUM, AVG, MIN, MAX) Facts. We developed powerful multidimensional query language called MAQL that you can use to create any metric you’ll need. Read the metrics documentation or snapshotting blogpost to get more insight.

This brings us to the key part of every project - Reports and Dashboards. Reports are built on top of three important concepts: aggregation (metrics) by attributes, filtering and visualization. Every time you build a report you have to specify WHAT (which metric) you want to analyze, and HOW (attribute) you want to slice it. See the picture below. The Filter button helps you to filter your report data.

The last but not least step is to choose a suitable visualization for your aggregated data. GoodData offers a lot of visualization options including pie charts, bar charts, pivot tables and so on. You can also use the custom number formatting to visualize your data. See this example and the documentation for more information about this topic.

The final step is to organize your reports in a dashboard that presents them to a specific audience (e.g. your CEO). The best practice is to identify 3-5 most important company’s KPIs (metrics) and put them on the first tab as headline report (one single number). The subsequent tabs usually show the KPIs sliced by time, departments etc. See the marketing dashboard example below.

The dashboards and reports are often embedded into the web applications and portals that your users frequently use. You’ll first need to create and invite your users to the new GoodData project. It can also be done programmatically using the CL Tool. Then you can embed the dashboards and reports to your existing web app. Check out blogpost about embedding. You can even setup a Single Sign On (SSO) mechanism that will allow your users to log into their GoodData project once with your web app’s credentials.

Now, let’s sum up the whole process once again. To create an analytical project you have to do following steps:

Develop Logical Data Model
ETL process (Getting your data in)
Create metrics
Create reports
Create dashboards
Embed reports and dashboards
Maintain your project: e.g. add users to your project

That’s it. Start building your own project and let us know how it worked for you!

Analyzing Change

2011-07-29T00:00:00-07:00

Analyzing Change

by Jiri Tobolka (@jirtob) & ZD (@zsvoboda)

Analyzing the latest and the greatest data in GoodData is easy. You simply add up all sales opportunities in a revenue metric and then slice the number by sales stages, status, region, sales rep, or whatever you like. Sometimes your users start asking questions like: “What was my pipeline at the beginning of this quarter and how it has improved since then?”. You quickly realize that you need to keep the history of your opportunities and understand how any opportunity changed over time.

This article introduces the analytic technique that we call snapshotting. We’ll stay in the realm of sales and describe the snapshotting on a simple sales automation example. Welcome to the world of opportunities, sales stages, and change.

NOTE: Keep in mind that you’ll hear similar questions from your help desk guy, quality guy or a head of your engineering. Snapshotting help you in these cases as well. Follow this article and substitute case, bug or feature for the opportunity.

Sales Analytics

Sales Analytics is all about sales opportunities that are generated by your business activities. Each opportunity has a status that describes if the opportunity is Open (Pipeline), Won (Revenue), or Lost (Lost Revenue).

Now our hypothetical VP of Sales, lets call him Michael asks his million dollars question: “What was my pipeline at the beginning of this quarter and how it has improved since then?”

Snapshotting Basics

Snapshotting is straightforward and easy to use technique that helps answering the Michael’s question. The key idea is that we keep transferring all opportunities from your CRM to GoodData every week (or day or month - it depends on your requirements). We call the weekly set of opportunities the snapshot. Each snapshot is associated with a date and an unique ID.

So if we have a project that accumulates snapshots for 118 weeks, most of our opportunities will be duplicated 118 times in the project. Few of them that have been created after we started snapshotting have less than 118 versions. Each version is associated with a snapshot date (perhaps Monday of each week) and the snapshot ID. It is very beneficial to use snapshot IDs in sequence without gaps. We can then simply identify previous/next snapshots as the current ID minus/plus one.

And here is the first catch. A simple SELECT SUM(Amount) WHERE Status = Open metric measures your pipeline returns a number that is roughly 118 times higher than what we expect. No wonder, because we are adding up all ten versions of each opportunity. We need to be smarter here. We can define metric that shows the total amount for individual snapshot only:

Pipeline [118 weeks ago]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = 1

Pipeline [Now]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = 118

Nice, this was easy. But you’ll need a new metric next week and another one week after next week. A metric that returns the total amount for the last snapshot would be handy. Lets start creating such a metric with identifying the last snapshot:

Snapshot [Most Recent]: SELECT MAX(SnapshotId)

This definition sounds easy enough, however it has certain problems. Imagine that we fired a Sales Rep. John during the snapshot 25 (not nice but he well deserved it). We kept all opportunities that he has closed associated with him and re-associated all open opportunities to other sales guys.

Now if we break down the Snapshot [Most Recent] metric by the SalesRep, we will see that the metric returns 118 for all Sales Reps except John who gets the Snapshot [Most Recent] = 25. This is problem because we would compute our total on the snapshot 25 for John and on the snapshot 118 for everybody else. We need to improve the definition of the Snapshot [Most Recent] metric:

Snapshot [Most Recent]: SELECT MAX(SnapshotId) BY ALL IN ALL OTHER DIMENSIONS WITHOUT PARENT FILTER

This metric returns the MAX snapshot regardless any dimensions. The last snapshot (= 118) for all Sales Reps including John, for all regions, for all Products etc. When you add the BY ALL IN ALL OTHER DIMENSIONS statement to your metric it returns the grand total (MAX) of all the time and all dimensions. It returns a constant.

What is the WITHOUT PARENT FILTER clause for? Imagine that you place the metric into a report that contains the SalesRep = John filter. Applying this filter would lead to the same troubles that we’ve eliminated with the BY ALL IN ALL OTHER DIMENSIONS. The WITHOUT PARENT FILTER clause simply ignores the higher level filters.

NOTE: Find out more examples and aggregation concepts in this documentation.

Then we can use the Snapshot [Most Recent] metric in our pipeline metric and get the latest amount this way:

Pipeline [Now]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = Snapshot [Most Recent]

Similarly we can compute the pipeline for the first snapshot (the oldest one). The metric is:

Snapshot [Oldest]: SELECT MIN(SnapshotId) BY ALL IN ALL OTHER DIMENSIONS

and use it in the following pipeline metric:

Pipeline [Oldest]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = Snapshot [Oldest]

Measuring Pipeline Change in a Quarter

The oldest and the most recent snapshots are great but we want something slightly different. We want to compute the pipeline for the first and the last snapshot in a quarter. We can achieve this by following metric definitions:

Snapshot [First in Period]: SELECT MIN(SnapshotId) BY ALL IN ALL OTHER DIMENSIONS EXCEPT SnapshotDate WITHOUT PARENT FILTER

Snapshot [Last in Period]: SELECT MAX(SnapshotId) BY ALL IN ALL OTHER DIMENSIONS EXCEPT SnapshotDate WITHOUT PARENT FILTER

Let’s focus on the BY ALL IN ALL OTHER DIMENSIONS Except statement. Using this concept it allows you to compute total MAX/MIN of SnapshotId regardless any dimension but with exception of the specific attribute. The exception is an attribute after the EXCEPT expression. So the resulting number is not going to be a constant anymore. It will depend on the value of the SnapshotDate. In other words this metric returns the maximum SnapshotId for the SnapshotDate period.

If you put the metrics above to a report with the Snapshot Quarter, you will see the first and the last snapshot ID that we have for each quarter. See the report below:

So then the pipeline at the beginning of a period is:

Pipeline [First in Period]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = Snapshot [First in Period]

And the latest and greatest pipeline number is:

Pipeline [Last in Period]: SELECT SUM(Amount) WHERE Status = Open AND SnapshotId = Snapshot [Last in Period]

Now you just put these two metrics into a report with the Snapshot Quarter and you’ll get the pipeline at the beginning of each quarter and the last known pipeline each quarter.

You can divide or subtract these numbers to get the absolute or relative growths, whatever you need…Easy enough, isn’t it?

Creating Analytical Project with MAQL DDL

2011-07-26T00:00:00-07:00

Creating Analytical Project with MAQL DDL

by Jiri Tobolka (@jirtob)

Today we will show you the relationship between the GoodData Logical Data Model (LDM), Physical Data Model (PDM) and MAQL DDL language. MAQL DDL is GoodData proprietary data definition language. We will outline the basic MAQL DDL concepts in this article.

The LDM (Logical Data Model) is necessary for creating your reports. It consists of attributes and facts that GoodData users add to their reports.

The PDM (Physical Data Model) is used for the data storage and query. It is de-facto a DBMS schema (tables, columns, primary/foreign keys etc.).

The DLI (Data Loading Interface) is used for loading data to the GoodData projects.

LDM describes the logical structure of organization’s data in terms like datasets, attributes and facts (check out this documentation for more info). Most analytical tools and platforms will force you to develop both LDM and PDM that are perfectly aligned. Unlike the average tools, GoodData interface with user only via the LDM. The corresponding PDM and DLI are automatically generated from the LDM. The LDM model is created and modified via the MAQL DDL language statements, that are kind of similar to the SQL DDL (Data Definition Language).

NOTE: We’ll use the “Employee” dataset example to show you the relationship between the LDM, PDM and DLI. A similar dataset is part of the HR demo.

Lets start with a simple LDM model that you can see on the figure below. As you can see, the LDM contains one fact and two attributes in a hierarchy (department and employee).

As we have mentioned above, we’ll use the MAQL DDL language to define the LDM. The rest (PDM and DLI) will be generated automatically via the SYNCHRONIZE MAQL DDL command.

The PDM is standard DBMS schema with tables, columns, primary and foreign keys. A LDM ATTRIBUTE is represented by a database table with primary key and couple text columns (one for each LABEL) in the PDM. A LDM FACT is mapped to a column in a PDM fact table. The figure below outlines the PDM that has been automatically generated from the LDM above.

The last piece of the puzzle is the DLI that is necessary for data loading. GoodData platform loads data in self-describing packages. The data package is a ZIP archive that contains the data (delimited file) and a manifest that describes how the data map to the project’s LDM and PDM. The figure below shows the data file only. As you can see this is de-normalized (flattened d_employee_department and f_employee PDM tables) version of the PDM.

Now, we’ll inspect the MAQL DDL statements that create the LDM model above. We’ll create the Employee dataset with the Department and Employee attributes and the Salary fact.

Creating the Employee dataset

First, we have to create the Employee dataset. Here is the corresponding MAQL statement:

CREATE DATASET {dataset.employee} VISUAL(TITLE "Employee");

That creates the Employee dataset.

Creating the Salary fact

CREATE FACT {fact.employee.salary} VISUAL(TITLE "Salary") AS {f_employee.f_salary};

ALTER DATASET {dataset.employee} ADD {fact.employee.salary};

The first statement creates new Salary fact (LDM) and the corresponding {f_salary} column in the f_employee table (PDM). Remember that the fact is represented as a database column.

NOTE: All PDM tables, columns, keys etc. are automatically generated after calling the SYNCHRONIZE MAQL DDL command that is usually the last command of a MAQL DDL script.

Creating the Employee attribute

CREATE ATTRIBUTE {attr.employee.id} VISUAL(TITLE "Employee") AS KEYS {f_employee.id} FULLSET;

ALTER ATTRIBUTE {attr.employee.id} ADD LABELS {label.employee.ssn} VISUAL(TITLE "SSN") AS {f_employee.nm_ssn};

ALTER ATTRIBUTE {attr.employee.id} ADD LABELS {label.employee.name} VISUAL(TITLE "Name") AS {f_employee.nm_name};

ALTER DATASET {dataset.employee} ADD {attr.employee.id};

The first command line creates the LDM attribute named Employee. The LDM attribute maps to a PDM table that has the primary key {f_employee.id} (FULLSET). We can also add the foreign key, but we are not going to do it so right now. Stay tuned, we will show it later. As mentioned above, the attribute is defined as a PDM table. The attribute itself is the auto-generated ID f_employee.id.

The second and the third row add labels to the Employee attribute. As you’ve guessed already, labels are text representations of the attribute. For example, the Employee attribute has two: the SSN and the Name. Later you’ll be able to choose which label is more suitable for your report. Both labels and ID map to the columns in PDM table ({id}, {nm_ssn_} and {nm_name}). You’ll see these names later in the DLI.

Creating the Department attribute

Now, we will repeat the exercise and create the Department attribute. The only difference is that we’ll create the foreign key to the f_employee table now. Note that the first MAQL DDL statement contains two keys. The first one d_employee_department.id is the primary key and the second one f_employee.department_id is the foreign key that ties the d_employee_department table to the f_employee table.

CREATE ATTRIBUTE {attr.employee.department} VISUAL(TITLE "Department") AS KEYS {d_employee_department.id} FULLSET, {f_employee.department_id};

ALTER ATTRIBUTE {attr.employee.department} ADD LABELS {label.employee.department.name} VISUAL(TITLE "Department") AS {d_employee_department.nm_department};

ALTER DATASET {dataset.employee} ADD {attr.employee.department};

In the second line of the script, we again added the label to the Department attribute. Label is defined as nm_department column in the {d_employee_department} table.

Finally we add the Department attribute to the Employee dataset.

Generating / Synchronizing PDM and DLI

This is the magic statement that generates the PDM and the DLI.

SYNCHRONIZE {dataset.employee};

NOTE: Remember that SYNCHRONIZE command will erase all data and require to load it back.

The figure below describes the relationship between the MAQL DDL, LDM, PDM and DLI.

Loading data

We have called the SYNCHRONIZE command and we’ve ended up with an empty GoodData project. The last step is to populate it with some useful data. We can use the DLI REST API to do it. The DLI API is a combination of a private WebDav storage where we upload the self-describing data package and a simple asynchronous call (/etl/pull) that starts the data loading process. You find more in the DLI documentation.

The self-describing data package is a ZIP archive, that contains the data file with predefined columns and a simple manifest that describes the mapping between the data file and the project’s PDM. The data package template reside on this URL:

https://secure.gooddata.com/gdc/md/<your-project-number>/ldm/singleloadinterface/dataset.<your-dataset-name>/template

You can simply download the template, unzip it, populate the data file with your data (you’ll need to preserve the header row and the column sequence), re-pack it, upload it to the DLI API’s WebDav location, and call the etl/pull API to start the asynchronous data loading process. That’s all about the MAQL DDL, PDM, DLI and LDM for today. See you next time!

Adding User to Project Programmatically

2011-07-14T00:00:00-07:00

Adding User to Project Programmatically

by ZD (@zsvoboda)

The CL Tool 1.2.35 adds a new AddUsersToProject command that allows you to programmatically add an existing user (previously created by the CreateUser command). No confirmation e-mails are required. The user who is adding new users must be the admin in the project where the new users are added. Moreover the user must be the domain admin. Let us know at support@gooddata.com if you need your own domain.

Once your account is associated with the domain, you can simply run the command from a CL Tool script:

CreateUser(domain="MYDOMAIN", username="admin@domain.com", password="secret", firstName="John", lastName="Doe", company="John.Doe@acme.com", ssoProvider="SALESFORCE", usersFile="users.txt", append="true");

AddUsersToProject(usersFile="users.txt", role="DASHBOARD ONLY");

Each new user created by the CreateUser command is identified by an URI that can be accumulated in the usersFile. The usersFile can be overwritten (append="false") or appended (append="true").

The users represented by the URIs in the usersFile can be added to an existing project using the AddUsersToProject command. This command requires a project context (_CreateProject_, OpenProject, UseProject above). Then it wants the usersFile that contains a user URI on each line and the role parameter that specifies the role (_admin_, editor, dashboard only) that all users will have in the project.

As always the AddUsersToProject command invokes corresponding REST API. You can always check out the API invocation in the CL tool source code.

Let us know what you think!

Defining column with transformation element

2011-07-11T00:00:00-07:00

Defining column with transformation element

by Jiri Tobolka (@jirtob)

As you certainly know when you work with GoodData CL tool for managing projects and data you have to create schema XML config file for defining your dataset. You know there are several elements associated with column element for example <name>, <title>, <ldmType> and there is also <transformation> element. What would be probably new for you is how you can also use this element for defining the column.

The main purpose of this blogpost is to show you an option of using JAVA language syntax inside <transformation> element and create “computed value” in column based on other columns in your XML config file. For basic example imagine you want to join together user’s First name and Last name into one Name column. You can join these two text fields and the rows in column is automatically filled by “computed value” with the definition in <transformation> element. You should use the <![CDATA[...]]> element inside because of non-XML code. As you can see below:

<transformation><![CDATA[ lastname+", "+firstname]]></transformation>

The feature loads all columns by name and you can then use them for creating “computed value”. This is done by JEXL technology and you can use any of JEXL statements.

More detailed example using Java

As you can see below we have XML config file for the Opportunity dataset which is prepared for SugarCRM Analytics. Besides “standard” column stage (won’t be uploaded because of IGNORE ldmType) which defines stage of every sales opportunity (final stage would acquire value Open, Closed Won and Closed Lost) we’ve prepared also three columns which will be computed from the stage column using <transformation> element.

<schema>
  <name>Opportunity</name>
  <columns>
     ...
     ...
      <column>
        <name>stage</name>
        <title>Opportunity Type</title>
          <ldmType>IGNORE</ldmType>
      </column>
      <column>
        <name>status</name>
        <title>Opportunity Status</title>
          <ldmType>ATTRIBUTE</ldmType>
          <folder>Opportunity</folder>
          <transformation><![CDATA[ (stage.indexOf("Closed")>=0)?((stage.indexOf("Won")>=0)?("Won"):("Lost")):("Open")]]></transformation>
      </column>
      <column>
        <name>isclosed</name>
        <title>Opportunity Is Closed</title>
          <ldmType>ATTRIBUTE</ldmType>
          <transformation><![CDATA[(stage.indexOf("Closed")>=0)?("true"):("false")]]></transformation>
          <folder>Opportunity</folder>
      </column>
      <column>
        <name>iswon</name>
        <title>Opportunity Is Won</title>
          <ldmType>ATTRIBUTE</ldmType>
          <folder>Opportunity</folder>
          <transformation><![CDATA[(stage.indexOf("Won")>=0)?("true"):("false")]]></transformation>
      </column>
  </columns>
</schema>

What we did is fill the <transformation> element with Java code that do the job. In status column we would like to have the status information which could be Won, Lost or Open. Following Java code secure that

<![CDATA[ (stage.indexOf("Closed")>=0)?((stage.indexOf("Won")>=0)?("Won"):("Lost")):("Open")]]>

Finally we’ve got the iswon and isclosed columns that show us simple true or false values based on the stage column. Here is the code with an easy condition

<![CDATA[(stage.indexOf("Closed")>=0)?("true"):("false")]]>

respectively

<![CDATA[(stage.indexOf("Won")>=0)?("true"):("false")]]>.)

(Note: Remember to use <![CDATA [...]]> element for non-problem parsing.)

Well and this is it! Now you know how to basically use the <transformation> element. See you next time.

Filtering Embedded Reports & Dashboards via HTTP Parameters

2011-06-29T00:00:00-07:00

Filtering Embedded Reports & Dashboards via HTTP Parameters

by ZD (@zsvoboda)

NOTE: This article previews the functionality that we are going to release in the Release 56 (~ 7/18/2011). Let me know if you are interested in beta testing this functionality. I can invite you to a beta server.

The embedded reports can be filtered via a their IFrame URL parameters. This blogpost demonstrates the new functionality on a simple example.

We will start with a simple Salesforce report that shows opportunity amount sliced by the lead source and the opportunity stage.

Let’s grab the report’s embedding URL from the embed dialog. We will just cherry-pick the URL from the IFrame src attribute for now as we’ll put it directly to the browser.

https://secure.gooddata.com/reportWidget.html#project=/gdc/projects/Project4&report=/gdc/md/Project4/obj/20887

We put the IFrame URL to the browser and the report appears in a separate tab.

Now we want to filter the report by the StageName attribute. We need to first determine the unique identifier that we’ll use for identifying the attribute in the filter. To be precise, we’ll filter by something what we call label. The labels contain the human readable names. The attributes are technically just unique numbers. Most attributes have just one label. However there might be attributes with multiple labels (e.g. Person with a Firstname, Lastname, and SSN). The StageName has two labels and we will filter by the label that contains the stage name.

First, we need to enumerate all labels associated with the StageName attribute. Let’s go to the Manage section of the GoodData UI, select the Data tab and find the StageName attribute.

Note the /gdc/md/Project4/obj/738 suffix of the page’s URL. This is the GoodData metadata server’s URL of the StageName attribute. Every object has its own unique URL. Now we will take a look at the StageName’s definition in the gray pages. We’ll just append the StageName unique URL to the GoodData server’s URL https://secure.gooddata.com/gdc/md/Project4/obj/738 and open the result in the browser:

We can see that the StageName attribute has two labels (attributeDisplayForms) and that the identifier of the stage name label is label.opportunity.stagename.

Yes, I know that we need to display this information on the attribute’s page to avoid the harakiri above and we’ll do this ASAP.

Now once we have the label’s identifier, we can use it in our report embedding URL. The usage is pretty straightforward:

https://secure.gooddata.com/reportWidget.html?label.opportunity.stagename=Qualification#project=/gdc/projects/Project4&report=/gdc/md/Project4/obj/20887

We can also filter by two StageName values. Please note that Closed Won value must be URL-encoded because it contains the space.

https://secure.gooddata.com/reportWidget.html?label.opportunity.stagename=Qualification&label.opportunity.stagename=Closed%20Won#project=/gdc/projects/Project4&report=/gdc/md/Project4/obj/20887

Multiple conditions with the same label are joined with the OR logical operator. Multiple conditions with a different labels are ANDed.

https://secure.gooddata.com/reportWidget.html?label.opportunity.stagename=Closed%20Won&label.opportunity.leadsource=Partner#project=/gdc/projects/Project4&report=/gdc/md/Project4/obj/20887

And least but not last, the same trick works with the embedded dashboards too!

Too Many Date Dimensions? Try Events!

2011-06-24T00:00:00-07:00

Too Many Date Dimensions? Try Events!

by ZD (@zsvoboda)

Time can have many different meanings in analytical projects. For example an opportunity can be associated with the time when it was created and the time when a sales guy closed it. I saw quite a few models with way more times associated with a single entity (e.g. helpdesk models). Here is a typical example of a simple sales model that uses created and closed date dimensions.

However end users have sometimes difficult time to figure out why there are two date dimensions and which one they should use in their reports. “There is only one time” they think. There is one more problem except the end user usability.

You can easily create a report Revenue by Time that is sliced by the Closed Date.

You can also create a report Pipeline Generation by Time that is sliced by the Created Date.

But as the first report uses the closed and the second report the created time dimensions, you can’t put both metrics to a single chart:

You need to model your analytical project differently to achieve the report above. We are getting to something what we call event data models. Here you go:

We need to create a special Event dataset that contains all events related to a specific opportunity. In our case there are two events per each opportunity: created & closed . The Event dataset then has twice as much records as the original Opportunity dataset. We obviously need to adjust the metrics to not return all amounts multiplied by two. Here are few metrics definitions:

Pipeline [Sum]: SELECT SUM(Event Amount) WHERE Event Type = created
Revenue [Sum]: SELECT SUM(Event Amount) WHERE Event Type = closed AND Status IN (Won)
Lost Revenue [Sum]: SELECT SUM(Event Amount) WHERE Event Type = closed AND Status IN (Lost)

These metrics are relatively simple and self describing. The metrics that compute that last opportunity value are a bit more complicated. We need to always find the ‘latest’ available event for each opportunity in the Event dataset. This is why we have the Event ID FACT in the model. This fact is a unique ID of each event. The later an event occurred, the higher the Event ID is. So the maximum Event ID for a specific opportunity identifies the latest event for that opportunity. So we need to first compute this MAX ID.

Last Opportunity Event ID: SELECT MAX(Event ID) BY Opportunity ID ALL IN ALL OTHER DIMENSIONS

then the current (latest) opportunity amount, revenue, and pipeline are:

Current Amount [Sum]: SELECT SUM(Event Amount) WHERE Event ID = Last Opportunity Event ID
Current Revenue [Sum]: SELECT Current Amount [Sum] WHERE Status = Won
Current Pipeline [Sum]: SELECT Current Amount [Sum] WHERE Status = Open
Current Lost Revenue [Sum]: SELECT Current Amount [Sum] WHERE Status = Lost

Think about this data model design when you are creating the 6th data dimension in your project. ;-)

Using GoodData CL tool - Part 2

2011-06-22T00:00:00-07:00

Using GoodData CL tool - Part 2

by Jiri Tobolka

In the previous part of this series we’ve created a new project defined incorrect XML config file and finally we’ve updated that file correctly. This is second part of the series in which we’ll be using basically the same data model and we’ll update it. Again we have two Examples - first example is about adding new field to the model. In second example we will upload new dataset.

Example 1 - Updating a file (adding new field)

Let’s get down the business. We have actually updated the original data.csv file and we added Customers FACT field. So updated CSV file should looks as follows:

…
Date, Revenue, Count, Store, Customers
01/01/2010, 807.84, 816, SFO, 47
…

(Note: file has five fields - DATE field, ATTRIBUTE field and three FACT fields )

What we do next is manually updating the XML config file adding new column to the end of an XML file. (order is critical!) Here is the updated XML file after adding new column:

...
...
    <column>
      <name>store</name>
      <title>Store</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Sales</folder>
    </column>
    <column>
      <name>customers</name>
      <title>Customers</title>
      <ldmType>FACT</ldmType>
      <folder>Sales</folder>
    </column>
  </columns>
...

We actually have all parts prepared for updating the data model and for uploading data. We just need the CL script which has again several parts. It will update data model (GenerateUpdateMaql() for updating MAQL file) and load the data:

#1 - CREATE OR RETRIEVE PROJECT ID
RetrieveProject(fileName="myProject.pid");

#2 - UPDATE DATA MODEL
UseCsv(csvDataFile="data.csv", hasHeader="true", configFile="schema.xml",separator=",");
GenerateUpdateMaql(maqlFile="file1.maql");
ExecuteMaql(maqlFile="file1.maql");

#3 - LOAD DATA
TransferData(incremental="false");

(Note: if you don’t understand some script commands see our CL tool documentation )

After execution we can see the result in GoodData. It wasn’t so complicated, was it? So review the model and it’s here with new FACT field:

Example 2 - Adding new dataset

This example is ostensibly more complicated but it won’t be a big problem at all. So…let’s do it! Imagine you need to add new dataset to your model. You have two CSV files containing the data. First is our well known data.csv as mentioned above with five fields (Date, Revenue, Count, Store, Customers), second is new store.csv lookup file:

… StoreID, Store Name, City, State, Employees SFO, San Francisco Airport Store, San Francisco, CA, 18 …

As you can see there are 5 fields. StoreID is the CONNECTION_POINT that is referenced by Store REFERENCE in data.csv, Employees is FACT field and the rest are ATTRIBUTES fields.

(Note: remember the functionality of CONNECTION_POINT and REFERENCE ldmType. CONNECTION_POINT has same function as primary key and REFERENCE is analogy to foreign key.)

What we do next is generating store.xml config file for our new lookup file containing the store information. We’ll do this with CL script again:

#1 - CREATE OR RETRIEVE PROJECT ID
RetrieveProject(fileName="myProject.pid");

#2 - GENERATE XML FILES (DATA MODEL) - DO THIS STEP ONLY ONCE!!!
GenerateCsvConfig(csvHeaderFile="store.csv",configFile="store.xml",separator=",");

Now we have to manually check and update the store.xml file that should looks as follows with StoreID as a CONNECTION_POINT ldmType:

<schema>
  <name>store</name>
  <columns>
    <column>
      <name>storeid</name>
      <title>StoreID</title>
      <ldmType>CONNECTION_POINT</ldmType>
      <folder>Store</folder>
    </column>
    <column>
      <name>store_name</name>
      <title>Store name</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Store</folder>
    </column>
    <column>
      <name>city</name>
      <title>City</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Store</folder>
    </column>
    <column>
      <name>state</name>
      <title>State</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Store</folder>
    </column>
    <column>
      <name>employees</name>
      <title>Employees</title>
      <ldmType>FACT</ldmType>
      <folder>Store</folder>
    </column>
  </columns>
</schema>

Last thing you have to do with XML files before executing final CL script is to update Store column in original schema.xml file. You should update it as follows adding schemaReference attribute:

...
<column>
  <name>store</name>
  <title>Store</title>
  <ldmType>REFERENCE</ldmType>
  <schemaReference>store</schemaReference>
  <reference>storeid</reference>
  <folder>Sales</folder>
</column>
...

Now we have all files prepared to finish this example with the CL script execution. We’ll retrieve project id, then create new data model for store.xml lookup file, update existing data model for schema.xml file. Finally we’ll load data. Remember that you must load lookup file first!

Here is the script:

#1 - CREATE OR RETRIEVE PROJECT ID
RetrieveProject(fileName="myProject.pid");

#2 - CREATE DATA MODEL FOR LOOKUP FILE - store.xml
UseCsv(csvDataFile="store.csv", hasHeader="true", configFile="store.xml",separator=",");
GenerateMaql(maqlFile="store.maql");
ExecuteMaql(maqlFile="store.maql");

#3 - UPDATE DATA MODEL FOR EXISTING MODEL - file1.xml
UseCsv(csvDataFile="store.csv", hasHeader="true", configFile="schema.xml",separator=",");
GenerateUpdateMaql(maqlFile="file1.maql");
ExecuteMaql(maqlFile="file1.maql");

#4 - LOAD DATA (MUST LOAD LOOKUP FILE FIRST)
UseCsv(csvDataFile="data.csv", hasHeader="true", configFile="store.xml",separator=",");
TransferData(incremental="false");

UseCsv(csvDataFile="data.csv", hasHeader="true", configFile="schema.xml",separator=",");
TransferData(incremental="false");

And…That’s it. We’ve added new dataset to our model. You can review the resulting model in GoodData:

So that’s all for today. Stay tuned for next blogpost and enjoy coding!

Using GoodData CL tool - Part 1

2011-06-22T00:00:00-07:00

Using GoodData CL tool - Part 1

by Jiri Tobolka

Today we will show you some useful scenarios for our GoodData CL Tool. This blogpost contains two use cases so you can learn how to correctly upload data file and how to update it later if you’ve made a mistake.

Example 1 - Uploading file (with incorrect ldm file)

So let’s start with first, simple but useful use case. You will need to have simple data file (ours has four fields) which we are about to upload to GoodData project (with incorrect ldmField attributes). We have data.csv data file:

…
Date, Revenue, Count, Store
01/01/2010, 807.84, 816, SFO
…

It consists of one DATE field, one ATTRIBUTE field and two FACT fields. We’ll create a project with this simple data file, generate an XML config file to define a data model and finally upload the data using CL script tool. The point is creating incorrect XML file with only one FACT type and three ATTRIBUTES which is incorrect.

(Note: You have to manually edit XML file after generating it to define the model schema before you create the data model.)

You can see the pre-prepared CL script down here:

#1 - CREATE PROJECT
CreateProject(name="My New Project");
StoreProject(fileName="myProject.pid");

#2 - GENERATE XML FILES (DATA MODEL) - DO THIS STEP ONLY ONCE!!!
GenerateCsvConfig(csvHeaderFile="data.csv",configFile="schema.xml",separator=",");

After second section you have to manually edit the XML file to define schema. Here is the final XML file:

...
<columns>
    <column>
      <name>date</name>
      <title>Date</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Sales</folder>
    </column>
    <column>
      <name>revenue</name>
      <title>Revenue</title>
      <ldmType>ATTRIBUTE</ldmType>
    </column>
    <column>
      <name>count</name>
      <title>Count</title>
      <ldmType>FACT</ldmType>
      <folder>Sales</folder>
    </column>
    <column>
      <name>store</name>
      <title>Store</title>
      <ldmType>ATTRIBUTE</ldmType>
    </column>
  </columns>
...

(Note: as we define above you edited the schema.xml file incorrectly. It has wrong ldmType (as mentioned above) and also only two columns have folders assigned to them.)

Now execute the last two sections to create a data model with data XML config file and CSV data file, and finally load the data:

#1 - CREATE OR RETRIEVE PROJECT
RetrieveProject(fileName="myProject.pid");

#2 - CREATE DATA MODEL
UseCsv(csvDataFile="data.csv", configFile="schema.xml", hasHeader="true", separator=",");
GenerateMaql(maqlFile="file1.maql");
ExecuteMaql(maqlFile="file1.maql");

#3 - LOAD DATA
TransferData(incremental="false");

You can now see the resulting model in GoodData:

Example 2 - Updating the file and defining fields appropriately (same file as in example 1)

In following example we’ll work with the same file but we’ll define the XML config file correctly. So what we do.

Firstly we’ll edit the XML config file with setting correct ldmType (Date as DATE, Revenue and Count as FACT, Store as ATTRIBUTE). Secondly we’ll add the <folder> to the XML for all fields that miss this attribute to place field to appropriate folder on front end. Thirdly we’ll create Date MAQL files generate the Date dimension and finally we’ll run the CL script to update MAQL files.

So after first step you will get the correct XML config file as you can see below:

...
<columns>
    <column>
      <name>date</name>
      <title>Date</title>
      <ldmType>DATE</ldmType>
      <datetime>false</datetime>
      <format>MM/dd/yyyy</format>
      <schemaReference>Date</schemaReference>
    </column>
    <column>
      <name>revenue</name>
      <title>Revenue</title>
      <ldmType>FACT</ldmType>
      <folder>Sales</folder>
    </column>
    <column>
      <name>count</name>
      <title>Count</title>
      <ldmType>FACT</ldmType>
      <folder>Sales</folder>
    </column>
    <column>
      <name>store</name>
      <title>Store</title>
      <ldmType>ATTRIBUTE</ldmType>
      <folder>Sales</folder>
    </column>
  </columns>
...

You see that all columns contain <folder> attributes and all ldmType fields are correct. We’ve also added the date schemaReference to the Date column to connect the dataset to Date dimension. Now we’ll move to the final step of this example using following GoodData CL script:

	
#1 - CREATE OR RETRIEVE PROJECT ID
RetrieveProject(fileName="myProject.pid");

#2 - CREATE DATE DIMENSIONS - DO THIS STEP ONLY ONCE!!!
UseDateDimension(name="Date", includeTime="false");
GenerateMaql(maqlFile="date.maql");
ExecuteMaql(maqlFile="date.maql");
TransferData();

#3 - UPDATE DATA MODEL
UseCsv(csvDataFile="data.csv", hasHeader="true", configFile="schema.xml",separator=",");
GenerateUpdateMaql(maqlFile="data.maql");
ExecuteMaql(maqlFile="data.maql");

#4 - LOAD DATA
TransferData(incremental="false");

(Note: Remember the difference between GenerateMaql and GenerateUpdateMaql. The first one generates script describing data model from local config file. Second generate alter script that creates the columns available in the local configuration but missing in the remote GoodData project.)

After executing the whole CL script you can review the resulting model in GoodData. This is correct model and should looks as follows:

Well this is all for today, in Part 2 we’ll show you how you can add field to the model and how to add a new dataset. Stay tuned for the next part!

Handling User Hierarchies in GoodData

2011-06-22T00:00:00-07:00

Handling User Hierarchies in GoodData

by ZD (@zsvoboda)

Tomas has published two nice articles about handling user hierarchies in GoodData. He uses variables for smart implementation of aggregating facts in user hierarchies. Here are the articles:

User hierarchies in GoodData outlines various techniques for implementing user hierarchies
How to set up a user hierarchy in GoodData picks one method from the article above and explains the implementation

Modeling Technique - Pushing FACTs Down the Hierarchy

2011-06-21T00:00:00-07:00

Modeling Technique - Pushing FACTs Down the Hierarchy

by ZD (@zsvoboda)

This article shows a special data modeling trick that leads to simpler and more usable data models. This technique is applicable to data models that perform computations on facts in different datasets. Let me demonstrate the technique on a simple data model.

We need to combine the Quarterly Bonus and the Payment facts in a single report. One option is to follow the M:N modeling technique that we have mentioned earlier. This technique is suitable for situations when we can’t change the data model of the project. The option that I’m introducing in this article require the project’s data model change. However it leads to more usable metrics and overall better performance. The first step is that we need to push the Employee dataset’s facts to the Salary dataset. Here is the new model:

Let me emphasize that there are 205 employee records and 3876 salary records in my test data. This means that the 205 different Quarterly Bonus values are somewhat duplicated (de-normalized) in the 3876 salary records. We need to handle the duplicities in our metrics definitions. Here are the metrics:

Salary Paid : SELECT SUM(Payment)
Employee Quarterly Bonus : SELECT MIN(Bonus (Quarterly)) BY Employee, Quarter/Year (Payment)ALL IN ALL OTHER DIMENSIONS
Bonus Paid : SELECT SUM(Employee Quarterly Bonus)

The MIN aggregation in the Employee Quarterly Bonus metric converts all duplicate Quarterly Bonus values to a single value per Employee and Quarter. As all these values should be the same, we could also use the MAX or AVG aggregation.

Here is the report with all metrics computed on my test data:

New User APIs

2011-06-17T00:00:00-07:00

New User APIs

by ZD (@zsvoboda)

The CL Tool 1.2.30 adds a new CreateUser command that allows you to programmatically create a new GoodData users. No registration forms, no confirmation e-mails. We can’t obviously allow an ordinary Joe to start creating thousands of new GoodData users. This is why we came up with the concept of the GoodData domain. GoodData domain is identified by it’s name (usually a company name of a GoodData partner). It is associated with an authorized GoodData account (username) that can create the new users in the domain. Initially the users can be only created. Later we will allow user modifications, association to a project etc.

You’ll need to request a new domain from GoodData before you can use the new CreateUser command. Let us know if you need one. Then you can simply run the command from a CL Tool script:

CreateUser(domain="...", username="...", password="...", firstName="...", lastName="...", company="...", phone="...", country="...", position="...", ssoProvider="...");

I think that the most of the command’s parameters are self-explanatory. Just in case:

domain - the GoodData users domain. The domain needs to be created by GoodData admins and associated with your GoodData account
username - the new user’s username
password - the new user’s password
firstName - the new user’s first name
lastName - the new user’s last name
company - (optional) the new user’s company name
phone - (optional) the new user’s phone
country - (optional) the new user’s country (e.g. ‘cz’ or ‘us;ca’)
position - (optional) the new user’s position
ssoProvider - (optional) the new user’s SSO provider (e.g. SALESFORCE)
usersFile - (optional) writes the new user’s URI to the specified file
append - (optional) should the users file be appended (default is false)

There is obviously a nice REST API under the new command. Feel free to dig into the CL tool source code to figure it out.

Project Cloning

2011-06-06T00:00:00-07:00

Project Cloning

by ZD (@zsvoboda)

The CL Tool 1.2.28 introduced the new project cloning capability. There are two new project export and import commands:

ExportProject(tokenFile="...", exportUsers="...", exportData="...", authorizedUsers="...")

and

ImportProject(tokenFile="...")

As you can guess the ExportProject exports the whole content and stores it on a protected storage (you don’t have direct access to it). The exported project is identified by a token that the command stores in the tokenFile. You can also define if you wish to export the source project’s users (exportUsers="true") and data (exportData="true"). The optional authorizedUsers parameter can specify other GoodData users (comma separated) who will have access to the exported content. The user who is exporting a project has access to it by default.

The ImportProject command imports a previously exported project to a new empty project. The command needs the tokenFile file that contains a valid import token generated by the ExportProject command. The import tokens expire after 48 hours.

Both ExportProject and ImportProject work in a project context. You can create, use or open a new project with the CreateProject, OpenProject, or UseProject commands. Here is an example of the complete project cloning script:


# Open an existing project 
OpenProject(id="<your-project-md5-here>");

# Export the project with data and users. Save the import token to importToken.txt file 
ExportProject(tokenFile="importToken.txt", exportUsers="true", exportData="true");

# Create new empty project
CreateProject(name="Clone");

# Import the project metadata, data, and users to the new project.
ImportProject(tokenFile="importToken.txt");

The logged user must be associated with the admin role to be able to invoke the API.

Cloning without the CL Tool

If you are not sure in using the CL Tool, you can do the same thing using our so called grey pages. Go to the project maintenance section that is located on the following url:

https://secure.gooddata.com/gdc/md/<project-id>/maintenance

Here you are able to export the project. The system will generate a unique token that you will use lately to import project (store that token somewhere!). You have to create new, empty project and go to the same maintenance grey pages section, but this time choose import. You need to paste that unique token and the exported project will be imported to the new empty project.

You can choose from same options as you can using the CL Tool.

Project Validation

2011-05-05T00:00:00-07:00

Project Validation

by ZD (@zsvoboda)

The project validation API checks a GoodData project for the most common errors:

Referential integrity issues. For example an employee dataset references some non-existent record in the department dataset.
Missing attribute values check tries to find an attribute values that are used in report or metric filters and that are no longer present in the data.
Ambiguous or Transitive references between datasets. Report computation results are unpredictable when a report is computed in project where there are multiple ways how to traverse the project’s LDM from an attribute A to an attribute B.

The project validation API can be invoked at the following URL

https://secure.gooddata.com/gdc/md/<your-project-id>/validate

The logged user must be associated with the admin role to be able to invoke the API.

Deleting Data from Project

2011-05-03T00:00:00-07:00

Deleting Data from Project

by ZD (@zsvoboda)

The new data deletion API has been introduced in GoodData Release 51. The API is located at the following URL.

https://secure.gooddata.com/gdc/md/<your-project-id>/dml/manage

You can also use the CL tool ExecuteDml(maql="...") command instead of invoking the API directly.

Both ExecuteDml and the API require a MAQL DELETE statement that performs the data deletion. Lets start with a few examples:


DELETE FROM {attr.opportunity.id} WHERE {fact.oppoprtunity.probability}<0 OR {fact.oppoprtunity.amount}<100;
DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd}<"2009-01-01";
DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd} BETWEEN "2010-01-01" AND "2010-12-31";
DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.salesrep} IN ("SMITH","CLOONEY");

So the DELETE syntax is


DELETE FROM <attribute> WHERE <condition>;

where

<attribute> - determines the data that will be deleted. If you want to delete whole record from a dataset, use the dataset’s CONNECTION_POINT attribute.
<condition> - identifies the records that will be deleted.

Lets explain the <attribute> in more detail on an example. The attribute {attr.opportunity.id} is the CONNECTION_POINT of the Opportunity dataset. The dataset also contains the {attr.opportunity.status} attribute. The {attr.opportunity.status} has the {label.opportunity.status} label. Then the statement


DELETE FROM {attr.opportunity.status} WHERE {label.opportunity.status}="Open";

deletes the single Open value from the {attr.opportunity.status} attribute. The dataset’s records are preserved. This statement most probably breaks the referential integrity of the project as the records with the Open status no longer reference any value in the {attr.opportunity.status} attribute.

The statement


DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.status}="Open";

deletes all records with the attribute {attr.opportunity.status} equal to Open .

WARNING: The MAQL DELETE command is translated to a corresponding SQL command. This might have certain unwanted side effects. For example, the following statement:


DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd}>"John Doe";

deletes all records from the Opportunity dataset because the value “John Doe” gets converted to a NULL date. Similarly, the statement:


DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.salesrep}>"CLOONEY";

deletes all opportunities that are associated with sales reps that come after “CLOONEY” in the alphabet.

And the last one. You need to be the admin to invoke this API.

GoodData turned 50 in just 32 months!

2011-04-20T00:00:00-07:00

GoodData turned 50 in just 32 months!

by Jaroslav Gergic (@jgergic)

We launched Release 50 last week. (Disclaimer: I swear that launching GoodData Release 50 on the 50th anniversary of the first human space flight was a pure coincidence.) Let me recap how we got from Release 1 to Release 50 in such a short period of time.

The first public beta was launched on July 28 2008 out of a code branch labeled stable-1. We used to have 4-5 weeks iterations back in 2008 and so we managed to release only 3 additional releases by the end of 2008 bringing the total to 4 releases.

The first version of report UI

We started 2009 on a 4-week iteration but switched to bi-weekly release trains by mid year. This shorter release cycle allowed us to be much more disciplined about release cut-off and lead to more regular releases, because the pressure to delay the whole release train due to an individual slipping feature was much lower, when our product management knew, that the next release is coming in just two weeks rather than a month.

A 2009 report UI

We created 17 stable- branches during 2009, but only 15 of them actually reached the production platform. We skipped two releases, because their stabilization took longer than expected, so their features were rather included in the next subsequent release.

We continued with bi-weekly release and iteration cycle throughout 2010 leading to 23 stable- release candidates, 19 of them actually reaching the production platform.

We eventually switched to Kanban production methodology in Q4 2010, tuned our planning cycle down to one week in order to decrease feature lead time while keeping bi-weekly release trains in place we have had 6 successful releases to date in 2011.

Release 50 and the Sales Analytics App

We plan to keep increasing the pace of innovation throughout 2011. Stay tuned for even better and faster GoodData!

Server side Charting - a case for Node.js and Erlang

2011-02-22T00:00:00-08:00

Server side Charting - a case for Node.js and Erlang

by Jan Pradac (@prajanq) & Standa Opichal (@opichals)

GoodData Release 46 sports a long awaited chart export capability. In this post, we won't focus on the feature itself, but rather on the interesting stuff going on under the hood. Since this is a completely new piece of functionality in our platform, we chose it to be a testing ground for the new infrastructure we are experimenting with.

GoodData Charting Engine

Let’s start from the beginning. Two years ago we were facing the need to implement a client-side charting capability. We started by exploring what was available at the time and subsequently chose a 3rd party commercial library. However, it soon turned out that the 3rd party library wasn't able to meet our longer term requirements so we decided to dedicate resources to develop a charting library in-house. Because of demands for high user interactivity we developed a native JS charting library. We anticipated the exporting feature would be needed later so we were thinking about either maintaining parallel code base on the server side, or maybe running our client JS library under Rhino.

As new features and chart types kept piling up and our charting library became more complex, it became obvious that the possibility to maintain a feature-identical server-side code base was no longer an option. When the chart exporting feature appeared on our short term roadmap, we started exploring options on how to deploy our JS charting library on the server side. The first idea on how to deal with this was the nodejs-yui3 project by YUI developer Dave Glass. Nodejs-yui3 enables execution of YUI3 which we use as our primary JavaScript framework under Node.js. We started experimenting with this in February 2010. After a while Dave's endeavor got public mainly via his great post 'Running YUI 3 Server-Side with Node.js'. The second trigger was David Padbury's blog post 'Using NodeJs to render JavaScript charts on the server'. After considering our options, we decided to go ahead and use this technology in production.

Server-side Charting Execution Chain

Exporting charts is generally a multi-step process. First, the data needs to be pulled from the data warehouse. The next step, is rendering the data into a vector graphics representation, the step which is performed by our JS charting library. The last step, involves converting the vectorised graphics into another output format like PDF or PNG. In general this process is what is usually called a multi-part long-running operation - a workflow.

Meet TaskMan - the asynchronous task management middle-ware

Some sort of an orchestrator - an oversight entity - taking care of scheduling, monitoring and error handling of all stages of the process is necessary. Erlang with its OTP platform and the inherent supervisor concept seemed to be an ideal choice for this portion of the middleware. Since we already built some prototypes of what we call TaskMan (a task manager) in Erlang earlier, we decided the server side charting feature would be an ideal candidate to battle test Erlang in GoodData in production deployment. The TaskMan is a distributed long-running operation scheduler/manager with REST API. It employs Erlang’s integrated distributed transaction DBMS - Mnesia which allows us to brace for multi-node deployments and Yaws a high performance web server for fast REST API tasks management. The initial TaskMan design was intentionally kept minimal. It receives a task definition and manages a pool of workers to process it.

Execution Workflow Implementation

There are workers configured for each of the steps of the chart export process. The first worker manages the data retrieval. It uses our standard REST APIs and fetches all the data necessary for a particular chart export job.

The second worker is for the actual chart rendering, a process analogous to what is happening in the web browser when rendering a chart on the client. Using YUI on both sides allowed us to share the browser built code on the server side in Node.js with suprisingly, almost no additional work.

The final step is the conversion of the rendered SVG to a PDF document or PNG image file. We built a worker based on the good old Apache Batik Rasterizer using Apache FOP (for generating PDF). The only extra step was adjusting the SVG markup to avoid generic SVG->PDF conversion transparency issues.

Conclusion

We have been very pleased by the result and are getting the exact same chart rendering using the browser as well as in the exported documents. We are looking forward to exploiting the opportunities this great piece of infrastructure provides. The idea of sharing the JavaScript code beyond just the browser environment is very powerful. Using the Erlang-built TaskMan enables us to distribute the worker load with minimal effort.

Modeling Many-to-Many relationships in GoodData

2011-01-21T00:00:00-08:00

Modeling Many-to-Many relationships in GoodData

When using GoodData, constructing a good data model is cruicial to the healthy functioning of your project. How you create your model directly influences the way you construct your reports and metrics - particularly how GoodData aggregates your data (and assumes aggregations). Typical 1:N relationships are fairly easy - represented as oriented edges in your model graph - each Year has multiple Months, each Month has multiple Days:

However sometimes you need to try to model M:N relationships. A typical usecase like this is tagging. There are typically multiple tags associated with one object and multiple objects tagged with one specific tag.

The Data Model

To take a specific example, let’s take support tickets - the Ticket and Tag attributes. As in relational databases, in order to create 1:N relationships, a third, joining attribute TicketTag must be introduced. This attribute contains unique Ticket-Tag pairs (ie. if ticket X is tagged with tag Y, there is an XY entry in this ticket). For each entry in this attribute, there is a 1:N relationship with tickets (each one Ticket can have multiple TicketTag pairs, but each pair corresponds to only one ticket) and 1:M relantionship with tags.

Let us now assume that each ticket has some facts associated with it - for example ResolutionTime (the number of hours it took to close it) - and we would like to display a report showing the total resolution time of tickets marked with a certain tag. Additionally, for implementation reasons, let’s also create a Dummy fact associated with this TicketTag attribute. It can really be just a column of values “1” or anything.

Thus, our data model now looks like this:

Naive Approach

Using a straightforward approach would be to define a metric AVG(ResolutionTime) and then create a report with that metric and Tags. But this is what happens:

As you can see the Tags attribute is disabled. This happens because GoodData does not know how to split the ResolutionTime by Tag - if a ticket has tags (red,hot,chili) and a resolution time of 10 hours - how do the hours divide between the 3 tags?

Enforce Aggregation

Fortunatelly, there is a way in advanced MAQL to override this default behavior via enforced aggregation rules. First, let’s modify our metric like this:

SELECT AVG(ResolutionTime) BY Ticket, ALL IN ALL OTHER DIMENSIONS

The BY section now specifies that the metric should be aggregated on a per-ticket level and should ignore any attributes from other dimensions when being computed. Now it is already possible to add the Tag to the report, but still the result is still not what we intended:

The metric behaves exactly as - it aggregates ResolutionTime for each Ticket, but as tickets are not directly connected to tags, it ignores any attributes from other dimensions (ALL IN ALL OTHER) - and produces a average of all data in your warehouse.

Wrap In a Different Aggregation

If we were to add Tickets to the report above, we would see each ticket containing all tags - since for each ticket-tag combination, the metric just computes value over the whole warehouse. Now comes the trick - use this as a sub-metric and add a fact at the TicketTags level (and multiplying it by 0 we eliminate it from influencing the result):

SELECT AVG(
	(SELECT SUM(ResolutionTime) BY Ticket, ALL IN ALL OTHER DIMENSIONS)
	+
	(0 * Dummy)
)

Now it’s useful to view the resulting report split by both Tickets and Tags:

As you can see, the metric is still identical for all tags in one specific ticket (since the internal metric is aggregated by Ticket). However only pairs of tickets-tags that actually have a Dummy fact associated with them are listed. Removing the Ticket attribute from the report now finally results in our intended report:

Summary

In retrospect, let’s have a look how we achieved M:N modeling in GoodData:

we split the Ticket-Tag M:N relationship into two 1:N and 1:M relationships using a “joining” TicketTag attribute and included a “dummy” fact with this attribute
we defined an “inner” metric with enforced aggregation by the Ticket (and by ALL IN ALL OTHER DIMENSIONS)
we wrapped the inner metric in an outter metric, used the Dummy fact to filter out just the ticket-tag pairs that exist and “repositioned” the aggregation to TicketTag level
we constructed the final report sliced by Tag

You can read more on this topic in our documentation on MAQL metrics. Examples of setting up hierarchies using GoodData CL can be found in the HR example.

Adding Hyperlinks to Your Attributes

2011-01-13T00:00:00-08:00

Adding Hyperlinks to Your Attributes

When analyzing data from online applications (Salesforce, Zendesk, etc.), it makes a lot of sense to set up some attributes to be clickable hyperlinks leading back to the original data. Take this example of Susan’s open support tickets in Zendesk, along with the age of the tickets:

Wouldn’t it be nice if you could click on the ID of the individual support ticket and open it directly back in the original application? Well let’s have a look at how we can get this done.

First, we’ll need to extend our original data with a new column URL containing the link for each ticket:

Ticket ID, Asignee, URL, ...
...
764613,Abbott Susan B., http://yourcompany.zendesk.com/tickets/764613
765427,Abbott Susan B., http://yourcompany.zendesk.com/tickets/765427
...

Now we need to edit our XML schema to add information about the new column. We will set this up as a LABEL ldmType, adding it as another view to our original attribute that should have the hyperlink (in this case Ticket ID). Note the <reference> field containing the <name> of the attribute:

...
<column>
  <name>url</name>
  <title>URL</title>
  <ldmType>LABEL</ldmType>
  <reference>ticketid</reference>
</column>
...

Next, we’ll need to modify the data model in our project. The easiest way to do this is by using the GenerateUpdateMaql command, which analyzes changes to your XML schema (compared to the current status in your project) and produces an “update MAQL” that will modify your model accordingly:

OpenProject(id="<project-id>");
UseCsv(csvDataFile="data.csv",configFile="schema.xml");
GenerateUpdateMaql(maqlFile="update.maql");

You can review the update.maql file at this point. Before you run it in your project however, we need to add one extra line specifying the newly created LABEL as a hyperlink. You can insert it just above the SYNCHRONIZE line at the end:

ALTER ATTRIBUTE {attr.dataset.ticketid} ALTER LABELS {label.dataset.ticketid.url} HYPERLINK;

(Note: the MAQL identifiers {attr.dataset.ticketid} and {label.dataset.ticketid.url} will need to be replaced by your own identifiers, to be found in the same update MAQL file.)

All that remains is to run this modified MAQL script in your project and upload the new data including hyperlink labels:

OpenProject(id="<project-id>");
UseCsv(csvDataFile="data.csv",header="true",configFile="schema.xml");
ExecuteMaql(maqlFile="update.maql");
TransferData();

The same report as before now shows hyperlinks on each ticket ID that can be clicked to open a new window with the original ticket in Zendesk.

(Note: If the window doesn’t open for you, your browser is probably blocking pop-up windows and you might need to allow secure.gooddata.com to open popups.)

Defining Custom Sorting Order For An Attribute

2011-01-10T00:00:00-08:00

Defining Custom Sorting Order For An Attribute

I am often asked how to define a custom sort order in GoodData. Today I’ll show you how to setup custom sorting for an attribute using MAQL DDL and an additional sorting data column.

Alphabetical or numeric ordering isn’t always the most appropriate way to sort data. This occurs especially in situations where data has some custom, inherent order that people understand. One example could be customer stages in a CRM system or ticket status in a support/bug tracking system:

1. New
2. Open
3. Pending
4. Closed

These items have an inherent, obvious order which is not alphabetical. Let’s take a simplified dataset in the form of this data.csv:

Status,Age
New,140
Open,182
Pending,198
Closed,270

You can imagine yourself what the schema XML file looks like (Status is an attribute, Age is a fact). By default GoodData is going to create a report like this:

The intuitive way to sort this is to start dragging rows around in the report to sort the data Excel-style. However this isn’t a good idea in an analytical system - data can change, new statuses can emerge etc. Instead, the appropriate place to solve this sorting issue is in the data layer. We’re going to use another column in our dataset called Status Ordering. Let’s change the data.csv to look like this:

Status,Status Ordering,Age
New,1,140
Open,2,182
Pending,3,198
Closed,4,270

Correspondingly, we need to add one more column definition to the schema.xml file, setting up the Status Ordering column as a LABEL to the Status attribute:

<column>
  <name>statussort</name>
  <title>Status Sort</title>
  <ldmType>LABEL</ldmType>
  <dataType>INT</dataType>
  <reference>status</reference>
</column>

Note: To set up sorting correctly you need to specify INT datatype for the label. If you don’t do that sorting will be based on alphabetic principles. (12 comes before 2 and so on…)

As always, when modifying the schema XML, we now need to project these changes into our project. We can create a new project from scratch, but sometimes that’s not practical. Or we can use the little known GenerateUpdateMaql command:

OpenProject(id="<project-id>");
UseCsv(csvDataFile="data.csv",configFile="schema.xml");
GenerateUpdateMaql(maqlFile="update.maql");

This generates the update.maql file describing the changes to your data model that add the new LABEL column. Before following through with applying the changes to the project and loading the new data, we will make one more change specifying the new LABEL column as a sorting column for the Status attribute. You can add it at the end of the file just above the SYNCHRONIZE command: (Note that the specific MAQL identifiers can be different for your model - you can find them by inspecting the generated update.maql)

ALTER ATTRIBUTE {attr.dataset.status} ORDER BY {label.dataset.status.statussort} ASC;

Save the file and run the following script to make the model changes and load the new data:

OpenProject(id="<project-id>");
UseCsv(csvDataFile="data.csv",configFile="schema.xml");
ExecuteMaql(maqlFile="update.maql");
TransferData();

Now our Status attribute sorts naturally as expected with GoodData sorting all rows behind the scenes according to the Status Sorting column in your source data:

Major release - GoodData CL v1.2 - Migration & New Features

2010-12-10T00:00:00-08:00

New GoodData CL Version 1.2 Is Here

Starting today, when you download the latest version of GoodData CL, you will get the new v1.2. It has been in preparation for weeks. Here is a run-down of new features and important migration information for existing projects:

New Features:

much faster in loading data into your projects
does not use local MySQL/Derby database anymore (details in previous post)
this version also supports multiple profiles when loading into Google Analytics and has a separate tool for pushing notification streams
you can export all your metrics/reports/dashboards, back them up, and load them into another project (allowing you to clone projects, migrate your dashboards to a new project etc.)
you can now use the built-in date dimension with time data

Migration of Older Projects:

Your projects can either be used exclusively either with CL 1.1 or with CL 1.2. Existing projects created with CL tool 1.1 and lower will need to be migrated to be used with CL 1.2. It is important you understand the changes. CL 1.1 used a local database (Derby or MySQL) to store lookup tables of IDs for your attribute string values. When new data was loaded (both incremental and full-replace) identical IDs are assigned to the same attribute values so that filters defined in your reports keep working.

The local database has been removed in 1.2 and is now kept on GoodData servers. If you have a v1.1 project, you will need to run the MigrateDatasets command. This command will transfer your local lookup tables to GoodData servers and sets up the project for CL v1.2. After this change, the project should not be used with CL 1.1 anymore. The command looks like this:
```
gdi.sh -u ... -p ... -e 'MigrateDatasets(configFiles="list-of-files");'
```
where list-of-files is a comma-separates list of paths to XML schema files that represent datasets that are to be migrated.
Additionally, you’ll need to modify your project model to adhere to the way the new version constructs data models. Only projects that contain datasets without any facts require this migration. If you try to load data into such a project, you’ll most probably receive an error reading “Unknown import mode” or complaints about missing factsof attribute. If your project requires migration, you’ll need to execute a migration MAQL script via the ExecuteMaql command. The migration script deletes the ambiguous factsof attribute that has been created for the dataset’s CONNECTION_POINT.

You’ll need to run the ALTER ATTRIBUTE statement on the line 5 for every REFERENCE to the migrated dataset. You’ll also need to SYNCHRONIZE all REFERENCE datasets.
```
ALTER ATTRIBUTE {attr.<dataset-name>.<connection-point-name>} DROP KEYS {f_<dataset-name>.id};
ALTER DATASET {dataset.<dataset-name>} DROP {attr.<dataset-name>.factsof};
DROP {attr.<dataset-name>.factsof};

# You need to add this statement for each REFERENCE to the migrated dataset
ALTER ATTRIBUTE {attr.<dataset-name>.<connection-point-name>} ADD KEYS
    {d_<reference-dataset-name>_<reference-name>.<dataset-name>_id};

SYNCHRONIZE {dataset.<dataset-name>};
# Also you need to synchronize all REFERENCE datasets
SYNCHRONIZE {dataset.<reference-dataset-name>};
```
For more details and further examples, see this older blog post about the issue.

CL Commands Changes

New Commands: MigrateDatasets, RetrieveAllObjects, StoreAllObjects (see Metadata Management Commands)

Changed Commands:

UseDateDimension now supports time (includeTime = <true | false>), you need to use the <datetime>true</datetime> element in your XML config to activate this and you must use TransferData command to load the time portion of the date dimension - see the XML and cmd files of Forex example.
support for the <dataType> key directly in your XML schema
new IDENTITY datatype creates a new column with a unique ID for each row of your dataset. It creates the ID as an MD5 hash of the whole row.
DATE format supports time, see more

Renamed Commands: We’ve simplified/consolidated names of the CL tool commands. Old names are still working, however they will be deprecated in the future:

Load* -> Use*
DropProject -> DeleteProject
StoreProject -> RememberProject
RetrieveProject -> UseProject

Discontinued commands: DropIntegrationDatabase, DropSnapshots, Transfer*Snapshot (use TransferData instead)

Three reasons why you should join Cloudstock hackathon and use GoodData

2010-12-06T00:00:00-08:00

Three reasons why you should join Cloudstock hackathon and use GoodData

You need to work with at least two Cloudstock partners, and GoodData already has connectors to Salesforce and Google Analytics. We can show you a demo mashup with Twilio. We are running on Amazon Web Services. It just cannot be easier.
ReadWriteWeb wants to see the best data vizualization. I believe it’s not about the most fancy chart, it’s about a great idea what to visualize. Once you have the idea, coding it in GoodData will be a pleasure. You just need to be prepared for the fame.
You can win the latest iPod Touch 32 GB. We are sponsoring the Best Analytic Innovation award. Think out of the box–you’re developer, after all.

Our top developers are here to help you. Feel free to ask us! We will not code your app but we help you keep moving.

If you decide to be a hackathoner, follow us on Twitter and don’t forget to register your team by 1:15pm!

CloudStock Session Slides

2010-12-02T00:00:00-08:00

CloudStock Session Slides

One Stop Shop for Analytics: GoodData APIs and Developer Tools

Jakub Nešetřil has presented a high-level overview of GoodData developer tools and APIs.

GoodData: One Stop Shop for Analytics

View more presentations from Jakub Nešetřil.

Business Intelligence Platform as a Service: Introduction to GoodData

Peter Olmer presentation about GoodData Platform as a Service.

Business Intelligence Platform as a Service: Introduction to GoodData

View more presentations from Petr Olmer.

Cloudstock talk: BI Platform as a Service

2010-12-02T00:00:00-08:00

Cloudstock talk: BI Platform as a Service

Our evangelist Petr Olmer will talk about GoodData at Cloudstock on Monday, December 6. If you wonder what will be covered, here’s the revealing mind map. Are you thinking about working with GoodData? Be at the session or talk to us at our demo pod.

Cloudstock Hackathon - Start Your Keyboards

2010-12-01T00:00:00-08:00

Cloudstock Hackathon - Start Your Keyboards

Next week 5000 developers are going to descend on San Francisco to take part in CloudStock - the premier event for cloud application developers. And to amplify the fun, you can compete in CloudStock hackathon for one of many prizes.

Start hacking now!

You can win a iPod touch 32GB for the “Best Analytic Innovation” prize sponsored by GoodData. So get working now. Here is a short jump-start guide to get you running quickly:

register for a free account
we’ve created a command-line utility called GoodData CL to help you use GoodData APIs
watch this quick screencast showing you a setup of a sample analytical project (follow the text along, download all the sample scripts)
here’s a bit more background on what’s happening under-the-hood

Give us a shout at @gooddata_dev if you have any questions, or our Developer Forum. We’ll be focusing heavily to help you move quickly.

And of course, come see us on CloudStock. We’ll be both manning the booth and roaming the hackathon, some of our best developers giving you hands-on support on the spot. We’re excited to see what you come up with!

Automated Notifications and Messages from GoodData

2010-11-02T00:00:00-07:00

Automated Notifications and Messages from GoodData

We’ve created a new tool designed to monitor GoodData reports - and if certain conditions are met, to send a message to another system. It was design for the purpose of Salesforce Chatter integration, but could conceivably be used with Yammer, Twitter or other messaging systems.

Here is a beta download: (tar.gz, zip). The tool has many similarities with GoodData CL. Documentation and examples are included in the download, however here are links to view them online:

Installation
Config XML Documentation
Salesforce Chatter Example

Building Your Own Messaging Connectors

The core of GoodData Notification tool is shared with GoodData CL. If you want to try building your own connector into a different messaging service, follow the instructions on how to build GoodData CL, but in step 6, instead of doing cd cli-distro, use instead the notification-distro: cd notification-distro and then resume the original instructions with mvn assembly:assembly.

Google Analytics Connector Now Supports Multiple Profiles

2010-10-26T00:00:00-07:00

Google Analytics Connector Now Supports Multiple Profiles

Based on popular demand, the new CL tool v1.2 contains an updated version of Google Analytics connector which supports multiple Google Analytics profiles (and thus the ability to analyze multiple different websites).

The new connector uses the same old Google Analytics project template. Thus, once your create the project from the template, you’ll need to run a simple MAQL DDL script to extend the data model with the multi-profile extensions. You’ll also need to create a simple CSV that lists all profiles that you want to load to GoodData and load it to the project as additional dataset.

For more detailed instructions, see the end of the GA connector README. Note: you have to use the GoodData CL v1.2 – currently in alpha build.

profileId	profileName
ga:7654321	Example Site
ga:1234567	Second Site

The Google Analytics dashboards need a bit work to better support multiple profiles. Can you help us improve it? Drop us a note.

GoodData CL 1.1 to 1.2 Migration

2010-10-25T00:00:00-07:00

Upgrading projects created with CL tool 1.1 to CL tool 1.2

Note: This is a follow-up blog post to our recent post about new SLI APIs and new CL tool version.

The new 1.2 version of CL tool changes the way data models are being created. This new version changes the way data models are being created. This requires a simple migration of projects created with CL tool 1.1 and older.

Only projects that use datasets without any facts require the migration. If you try to load data to such project, you’ll most probably receive an error reading “Unknown import mode” or complaints about non-existing factsof attribute.

If your project requires migration, you’ll need to execute a migration MAQL script via the ExecuteMaql command. The migration script deletes the ambiguous factsof attribute that has been created for the dataset’s CONNECTION POINT.

You’ll need to run the ALTER ATTRIBUTE statement on the line 5 for every REFERENCE to the migrated dataset. You’ll also need to SYNCHRONIZE all REFERENCE datasets.

ALTER ATTRIBUTE {attr.<dataset-name>.<connection-point-name>} DROP KEYS {f_<dataset-name>.id};
ALTER DATASET {dataset.<dataset-name>} DROP {attr.<dataset-name>.factsof};
DROP {attr.<dataset-name>.factsof};

# You need to add this statement for each REFERENCE to the migrated dataset
ALTER ATTRIBUTE {attr.<dataset-name>.<connection-point-name>} ADD KEYS
    {d_<reference-dataset-name>_<reference-name>.<dataset-name>_id};

SYNCHRONIZE {dataset.<dataset-name>};
# Also you need to synchronize all REFERENCE datasets
SYNCHRONIZE {dataset.<reference-dataset-name>};

Let me illustrate this on the HR demo project. There are two datasets with no facts: department, and employee. The following figure highlights the factsof attributes that we need to delete and reconnect.

and here is the new model that we need to create

Here is the script that updates both department and employee datasets.

# Disconnect the department attribute from the department factsof that we are going to drop
ALTER ATTRIBUTE {attr.department.department} DROP KEYS {f_department.id};
# Remove the department factsof from the department dataset
ALTER DATASET {dataset.department} DROP {attr.department.factsof};
# Drop the department factsof
DROP {attr.department.factsof};

# Disconnect the employee attribute from the employee factsof that we are going to drop
ALTER ATTRIBUTE {attr.employee.employee} DROP KEYS {f_employee.id};
# Remove the employee factsof from the employee dataset
ALTER DATASET {dataset.employee} DROP {attr.employee.factsof};
# Drop the employee factsof
DROP {attr.employee.factsof};

# Reconnect the department attribute to the employee (bridge the non-existing factsof)
ALTER ATTRIBUTE {attr.department.department} ADD KEYS {d_employee_employee.department_id};
# Reconnect the employee attribute to the employee (bridge the non-existing factsof)
ALTER ATTRIBUTE {attr.employee.employee} ADD KEYS {f_salary.employee_id};

# Synchronize the model changes with the underlying storage
# Please note that currently all data will be deleted after running the SYNCHRONIZE command!
SYNCHRONIZE {dataset.department};
SYNCHRONIZE {dataset.employee};
SYNCHRONIZE {dataset.salary};

Caution: The SYNCHRONIZE statement will delete all data from the given server-side data set. You will need to perform a full load after issuing the statements above!

You need to create your migration script, save it in a migrate.maql file, and create the migration commands file that opens the migrated project and execute the migration MAQL.

# Open the migrated project
OpenProject(id="<your-migrated-project-hash>");
# Execute the MAQL
ExecuteMaql(maqlFile="migrate.maql");

Finally, you will need to run your data loading script to re-populate the data sets as their contents have been deleted by the SYNCHRONIZE MAQL statement.

The other option is to start from scratch and create entirely new project from the new CL tool version. The new project obviously doesn’t contain the reports and dashboards that you might have in your current project. You can use the new RetrieveAllObjects CL tool command to retrieve all objects from the old project to your disk and then CopyObjects command to copy these objects to your new project. You’ll need to delete the factsof objects from the disk before the import.

Numbers & Arrows in Dashboards

2010-10-19T00:00:00-07:00

Numbers & Arrows in Dashboards

Some of you have written in and asked how we achieved our nice GetSatisfaction health dashboards with large numbers visual and progress indicators. Since this is a bit of a hack, I decided this would be a good audience to explain the trick. We do intend to implement this feature properly - in about a month. However, in the mean time, some clever tricks / a bit of wrestling can get you this effect today:

Large Font Numeric Report:

Create a report showing a single number (remove all attributes in the How section)
Convert the report to a stack bar chart
In the right column > Advanced Configuration > Y Axis > Primary Axis, set the max of the axis to something several orders of magnitude larger then your data - for example 1000000000 (depending on your number), and untick the Name and Labels checkboxes
In Advanced Configuration > Global Settings, make sure Data Labels is checked, set the font size to 18, turn off Values and Boxed, but keep Totals

Trending Colored Arrows:

To show a positive/negative trend, you need to create a second metric showing a difference in time. Here is a MAQL example for a metric called Revenue, showing trend between last month and the month before it (attribute Month):
```
SELECT (SELECT Revenue WHERE Month/Year = {Previous} )
 - (SELECT Revenue WHERE Month/Year = {Previous}-1)
```
You will need to create this as a Advanced Custom Metric. See more documentation on floating date metrics.
Create a customized formatting for this metric. Here’s where the trick get’s a bit hairy, full documentation is available here. We’re using both conditional formatting (different for negative and positive numbers) and changing the font color. For the final trick, we provide a Unicode symbol for up/down arrow. The final formatting string looks like this:

[green]▲;[red]▼

You can choose your own symbols/arrows, for example by copy/pasting them from Wikipedia (shapes, arrows).

Update on Data Upload APIs (and CL tool v1.2)

2010-10-15T00:00:00-07:00

On Data Upload APIs (and CL tool v1.2)

We’ve been really busy in GoodData in the past couple months reshaping our upload APIs - simplifying them, speeding them up and opening them up to more flexibility. Our v1.0 API allowed you to upload a single CSV file and our product would try to infer the data model from it (giving you a few couple options to choose from along the way).

We soon realized that we needed a more powerful data framework - but more importantly that we need to turn the table and allow you to specify a data model first and for the data interface to follow later. We have created v2.0 of our APIs that allowed you the tremendous flexibility to create your own data model explicitly.

To speed things up and get this out of the door ASAP, we had to sacrifice a bit of simplicity. Our APIs would accept data only in a normalized form, each lookup in a separate data file. We called these APIs “DLI” (shorthand for data-loading interface) and quickly built a bit of magic into CL tool to produce these normalized files automatically for you. CL tool was either using built-in Apache Derby or external MySQL to transform and normalize the data.

Today we’ve finally finished our last piece of the Data Upload API puzzle - SLI (single-file loading interface). You can still specify your own data model, but we give you a single data file to load and do all the normalization inside the product. This both simplifies the upload and speeds it up significantly (compared to the existing CL tool with DLI APIs).

Currently, the new version of CL tool using these APIs is a special build. Once we test everything works as expected, we’ll turn on SLI APIs for everybody.

What’s changing with SLIs:

CL tool doesn’t need any DB backend (Derby or MySQL)
Uploads processing should be significantly faster
GoodData CL (and other API clients) can be significantly simplified
You can upload into one project from different computers (no single CL tool instance necessary)
You cannot mix&match DLI and SLI in single project. You can only use one or the other.
You need a special CL tool build (currently, eventually we’ll migrate all projects)
-b, -c, -d, -m (or --backend, --dbusername, --dbpassword and --memory) CL flags are meaning less

We’d like you to help us prototype the new APIs. If you’re interested, download this special build. If you run into issues, let us know on Developer Forum (and please indicate you’re using SLI APIs).

Note: for existing projects created with CL tool 1.1, a migration process might be necessary. Read the follow-up blog post for details.

How to Load Date Dimension into GoodData

2010-10-14T00:00:00-07:00

How to Load Date Dimension into GoodData

GoodData allows you to use it’s built-in provided date dimension. Using this dimension organizes days into weeks, months, weekdays/weekend etc. Here is a step-by-step tutorial on how to setup data model for the dimension, load it into your project and map your data to it.