Page tree
Skip to end of metadata
Go to start of metadata

Sometimes a business analyst needs to create a report with a field that does not exist in the GoodData Portal. As a technical user, you can pre-populate this data in the Agile Data Warehousing Service (ADS) output stage so that business analysts can use the Data Explorer to add them to GoodData portal. This has the following benefits:

  • Keeps the GoodData portal free of unnecessary fields.
  • Enables business analysts to add fields to the project as needed, and without requesting individual changes to the ETL or ADS. This allows them to build the reports they need without delays.
  • Enables the technical users managing the ADS to prepare all needed data from a data source at one time, instead of adding data incrementally as the needs of the business analyst change.

Here’s how it works:

  1. You identify fields that a business analyst may want to add to the GoodData portal, and add the data for these fields as columns to the ADS output stage tables. You do not add these columns as fields to the LDM.
    These columns become prepopulated fields that the business analyst can add using the Data Explorer.
  2. Business analysts can see the fields that you have prepopulated in the Data Explorer on the GoodData portal and select one or more fields to add to the project.
    The selected fields are added to the LDM and data loading for all project data is triggered. After data loading is completed, the fields become available for use in reports.

Limitations

  • Data Explorer works only with the ADS.
  • In this first version, the Data Explorer works only with data already prepared in the ADS.
  • Only full load of data from the ADS into a data mart is supported.
  • All tables and views in the ADS instance set as the output stage for a project are visible in the Data Explorer.
  • Only standard date dimensions are supported. Fiscal dates are not supported.
  • Exceeding the following limits degrades performance when using the Data Explorer on the GoodData portal:

    ParameterValue
    Maximum amount of data in ADS for loading data into the project5GB
    Maximum size of the LDM500 facts and attributes (including date dimension attributes)
    Maximum number of ADS columns available to add via the Data Explorersoft limit: 50 new columns per table, 1000 new columns total
    hard limit: 250 fields (new and existing) per ADS table

Prerequisites

For the project where you want to set up Data Explorer:

Steps

  1. Log in to the GoodData project where you want to set up Data Explorer. Navigate to this grey page and copy the schema URI.
    https://secure.gooddata.com/gdc/datawarehouse/instances/{ADS_id}/schemas/default
  2. Associate the ADS output stage with the LDM for the project. Go to this grey page and add the schema URI.
    https://secure.gooddata.com/gdc/dataload/projects/{project-id}/outputStage/
  3. Navigate to:
    https://secure.gooddata.com/gdc/dataload/projects/{project-id}/outputStage/diff
    The grey page displays SQL DIFF that reconciles the ADS output stage to the LDM.
  4. Use the DDL statements in the DIFF to analyze and optionally modify the ADS output stage using your database management tool.
    • ADD COLUMN or CREATE TABLE statements – Identify LDM objects with no corresponding data source in the ADS. Either add the corresponding ADS columns for these objects or delete them from the LDM.
    • DROP statements – Identify columns in the ADS that have no corresponding LDM object. These columns are the fields that the business analyst can add with the Data Explorer. Use the provided DROP statements to remove any column that the business analyst should not be able to add with the Data Explorer.
    • Any ADS columns that contain validation errors are listed at the bottom of the DIFF, and will not be available in the Data Explorer. Use the listed DDL statements to resolve these errors.

      When you analyze the DIFF you may see columns with DROP statements that you want available as fields in the GoodData Project immediately. You can add these fields to the GoodData Project yourself by adding corresponding objects to the LDM.

  5. Refresh the DIFF and verify that the ADS Output Stage is correct:
    • Any columns listed in DROP statements will be available for the business analyst to add.
    • If any ADD COLUMN or CREATE TABLE statements exist, return to step two and resolve them.

      You must add and populate columns or tables for all ADD COLUMN or CREATE TABLE statements that are returned. Data Explorer can load data only if all LDM datasets have corresponding ADS tables.

  6. Ensure that all columns have a data source on this gray page:
    https://secure.gooddata.com/gdc/dataload/projects/{project-id}/outputStage/metadata

    Data sources organize data into the groups in Data Explorer, and are case sensitive. You can specify data source both at the table level (defaultSource) and also at the column level (sources). Column sources override table sources. If no source is specified for a column or the column’s table, it is listed in the “Unknown data source” group in Data Explorer. Because data from unknown sources may be confusing for business analysts, we recommend always specifying a data source.
    The ADS columns you specified become available to add using the Data Explorer.
    Example JSON:

    { 
      "outputStageMetadata":{  
          "tableMeta":[  
             {  
                "tableMetadata":{  
                   "table":"invoice",
                   "defaultSource":"salesforce",
                   "columnMeta":[  
                   ]
                }
             },
             {  
                "tableMetadata":{  
                   "table":"customer",
                   "defaultSource":"salesforce",
                   "columnMeta":[  
                      {  
                         "columnMetadata":{  
                            "column":"a__facebook_id",
                            "sources":[  
                               "Facebook"
                            ]
                         }
                      },
                      {  
                         "columnMetadata":{  
                            "column":"l__facebook_id__facebook_name",
                            "sources":[  
                               "Facebook"
                            ]
                         }
                      }
                   ]
                }
             }
          ]
       }
    }
    
    
  • No labels