Previously when querying metrics in the Query interface, we required a dimension to be present and we would not allow you to remove time.

In many cases, it is useful to (1) aggregate a metric over time without explicitly grouping by time and (2) Query metrics with only categorical dimensions. The Query interface now allows you to do this.

By default, we'll select and query with time for a dimension, but you can unselect that using the toggle. You can query one or multiple metrics without dimensions, or only categorical dimensions (no time).

Previously when querying metrics in Transform's Command Line, we required a dimension argument. In many cases, it is useful to aggregate a metric over time without explicitly grouping by a dimension, and the CLI interface now allows you to do this.

For example, you can run:

mql query --metrics revenue

This query will aggregate revenue over all time. You can optionally pass in a time constraint which will aggregate over just the time period you specified.

You can now download a PDF report of your Board or Filtered View to share with others by clicking the download icon in the top right-hand corner of your Board or Filtered View page.

We’re excited to announce the next generation of Transform’s Query UI - Query v2!

Transform’s Query product allows technical and non-technical users alike to answer data questions around their metrics easily. It allows them to build charts and save content to be shared with the organization, all with the guarantee of correct metrics from MetricFlow.

Query V2 is the next version of our query UI (after Query v1). It has an entirely new look and feel. It includes a single page that displays the chart, table of data, generated SQL, and a user-friendly menu on the right hand side that your teams can use to build queries. Most importantly and different from the first version of this product, it also includes the ability to query multiple metrics and dimensions together and allows you to plot two metrics on a single chart.

Access Query through this url: https://app.transformdata.io/metrics/queries/create

We released two updates to our Google Sheets integration:

1) You can now query your metrics without a dimension. Previously, we required at least one dimension. To query your metrics without dimensions, simply remove all dimensions from the selection list. Note: For certain cumulative metrics, we require Time, so you will see an error if you try to remove Time.

2) We now support dynamic date presets for time constraints. Under the “Time Constraint” option after you select metric time, scroll to the bottom of the list where you see “Metric Time is (Date Presets)”. Choose an existing preset or a custom one. Note that these presets are dynamic, so if you select “Last 7 days” and save the query and revisit it, the last 7 days will increment based on the current date.

We’re excited to announce Boards v2.3. It contains the following features:

• Default Filters — if you're a Board owner, you can now set default filters that get pinned to the global filter bar and become the default state of your Live Dashboard.
• Filtered Views — if you have access to view a Board, you can now create custom views of the Board with the set of filters you prefer. You can save and share these views for easy access and faster team collaboration.
• Favoriting Filtered Views — you can favorite a Filtered View by clicking the star icon at the top of the Filtered View page. Once you favorite a Filtered View, it will show up in the Favorite Views section of the Boards homepage.
• Updated Global Filter Logic — previously, the "Add Filters" modal on Boards only surfaced filter dimensions that were common across all of the charts on the Board. We've updated the logic to be less restrictive (i.e. from intersection to union), so now the "Add Filters" modal surfaces every dimension that is available to each chart on the Board. Therefore, some charts may display an error if the filter dimension or values are not applicable.

Learn more in the documentation here.

You can now add tags to your metrics in both YAML as locked metadata or in the Transform app under the metric metadata menu. Tags will help you further organize your metrics. You can filter with tags in metric search to narrow down to only relevant metrics, as well as see associated tags next to the metric on the search page and other locations in the app.

Read more on how to use tags here.

You can now save queries that are accessible to every editor of the Google Sheet.

Previously, you could save a query in Google Sheets and only you would have permission to view it. Now, we allow you to save a query so that all Editors of the sheet will also have access to it. When you save queries, you will now have the option to save it as a public versus private query. Public queries will be able to be viewed by any Editors of the Google Sheet. Furthermore, if your query is set to refresh on load, all Editors can benefit from this.

With this change, your saved query tab is now split into Public Vs. Private queries. You can also toggle the privacy of queries any time if you are the creator.

Note: Query creators are the only ones that can edit or delete the query.

We resolved a bug that caused errors when filtering on booleans when using BigQuery as your data warehouse.

We now support the ability to denote whether a metric is private in the YAML configurations under locked metadata. Previously, we only supported this in the Transform App as a configuration option.

See the example below on how to set this option. If you don't want the metric to be private, you can simply omit this option.

  metric:
  name: revenue_mtd
  description: revenue mtd
  owners:
    - support@transformdata.io
  type: cumulative
  locked_metadata:
    value_format: "$$,.2f"
    private: true

We fixed a bug where the total value of the metric was not showing up in the chart tooltip (from hover) when a grouping was applied. The "Total" should be present when looking at additive metrics.

We fixed some bugs that relate to Transform's metric time in Google Sheets:

• Metric Time was not showing up as an option to slice your metric by
• We fixed inconsistencies in the date for dates not in the MM/DD/YYYY format when using time constraint.
• When using the "between" clause, the time constraint was not showing up in the resulting custom function

The new transform-tools version 1.1.0 comes with a big new feature: support for dbt configs.

To begin using dbt configs as your model for Transform, you'll need to do the following:

1. Add a profiles.yml to your dbt project repository if you haven't already, and check it in​

Note: any profile values which are sensitive will need to be replaced with loading from environment variables. Here is an example of our profiles.yml for our internal testing:

transform_dbt:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"

      # User/password auth
      user:   "{{ env_var('DBT_DEV_USER') }}"
      password: "{{ env_var('DBT_DEV_USER_PASSWORD') }}"

      role: SYSADMIN
      database: TRANSFORM_ANALYTICS_DB
      warehouse: TEST
      schema: "{{ env_var('DBT_DEV_SCHEMA') }}"
      threads: 1
      client_session_keep_alive: False
    prod:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"

      # User/password auth
      user: "{{ env_var('DBT_PROD_USER') }}"
      password: "{{ env_var('DBT_PROD_USER_PASSWORD') }}"

      role: SYSADMIN
      database: TRANSFORM_ANALYTICS_DB
      warehouse: TEST
      schema: prod_dbt
      threads: 1
      client_session_keep_alive: False

2. Add a GitHub workflow (or equivalent) like the following:​

name: Commit Configs
on:
  push:
    branches: [master,main]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - uses: actions/setup-python@v2
        with:
          python-version: '3.8.5' # arbitrarily chosen
          architecture: 'x64' # optional x64 or x86. Defaults to x64 if not specified
      - name: Install dependencies
        run: |
          python -m pip install --upgrade pip
          pip install "transform_tools[dbt-snowflake]"
          dbt deps
      - name: Run commit script
        run: python -m transform_tools.validate commit
        env:
          REPO: ${{ github.repository }}
          TRANSFORM_API_KEY: ${{ secrets.TRANSFORM_API_KEY }}
          IS_DBT_PROJECT: "true"
          TFD_DBT_TARGET: prod
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          DBT_PROD_USER: ${{ secrets.DBT_PROD_USER }}
          DBT_PROD_USER_PASSWORD: ${{ secrets.DBT_PROD_USER_PASSWORD }}

Some notes:

• We’re installing a dbt adapter because it is required. Here, we’re installing dbt-snowflake.
  • Currently, we support dbt-snowflake, dbt-postgres, dbt-redshift, and dbt-bigquery.
• We’re loading our sensitive environment variable information from GitHub secrets.
• We’re overriding the dbt target with TFD_DBT_TARGET to prod because the default target for our profile is dev.

3. Add sensitive information to GitHub secrets (or equivalent)​

4. Push the changes from steps 1 & 2 to your repository​

There are likely certain metrics in your organization that should only be visible to particular individuals or teams. Private metrics give metric owners the ability to manage the visibility permissions of metrics for users in your organization.

This is currently an opt-in feature, meaning that we are only turning it on for customers who request it. If you would like us to enable the private metrics feature for your organization, please let us know.

For more information about private metrics, see the documentation here

Discovering relevant Boards just got even easier. Under each metric page, you will now see a section that displays a list of Boards that contain that metric. Clicking into a Board card will open the Board page.

For more information, see the documentation here

We’re excited to announce Boards v2.2. It contains the following features:

• Favorites — you can now favorite a Board by clicking the star icon at the top of the Board page.
• New homepage sections — there are now even more sections to make discovering Boards easier: Favorites, Recently Viewed, and Popular Boards.
• Multiple Board Owners — you can now add other individuals and teams to help own and manage the Board.
• Visibility — you can now set your Board's visibility to either (1) Board Owners only or (2) Organization.
• Time Granularity — you can now apply a time granularity to the charts on your Board via the Time Granularity button on the global filter bar.
• Text Tiles — you can now drag-and-drop text tiles onto your Board to add any relevant context (under the More Content tab in the edit mode sidebar).
• Explore in Query — if you click on the three dots of any chart, you'll see an option called "Explore in Query". Any changes you apply while on the Board page—applying filters from the global filter bar or changing the chart type—will get carried over into the Query editor.
• Favorite Boards Section on Transform App Homepage — you'll now see a new section at the top of your Homepage for easy access to your favorite Boards.

We added the ability in MetricFlow to specific dimensions that are non additive in a measure, otherwise known as a semi-additive measure. This is used when we can aggregate a measure on certain dimensions, but not on other dimensions.

Please refer to the MetricFlow pull request and changelog for more details.

We modified the look and feel of our single value charts by centering the value and adding arrows to flip through values associated with a grouping.

We fixed a bug where the y-axis did not start at 0 when only negative values exist for a given metric.

We made an improvement around persisting the chart type that was applied on the metric page to the query page when you click “Create Query” from a metric.

We fixed a bug where typing dimension names in the filter and segment by menus was not working.

We’ve made a few updates to Google Sheets connector!

1) We now have the concept of Saved Queries in Google Sheets. This allows users to save queries they issue with the custom menu, edit them, and refresh on load of the connector. Note currently Saved Queries in the Transform App are independent from Saved Queries in Google Sheets.

2) We’ve added the ability to retrieve the custom function command from the custom menu selections.

3) Instead of users needing to select granularity before Time over Time, we default to the most granular option available for the metric (for most most metrics this is daily).

4) We now automatically select metric time when users make selections.

5) When adding WHERE constraints, we now automatically format the input to fit with accepted parameters.

6) We added validation into the limit field so we now do not allow you to input text in the limit field.

Read more documentation about these features here.

Users can now define constraints on input measures for a metric. For example, to define a ratio metric for fraction of frequent purchasers, in the past you’d have to define a separate measure using a custom expr like SUM(IF(…)), or possibly even split one measure into a separate data source with a data source level constraint. Now, users may choose to apply the constraint directly to the measure, like so:

  metric:
  name: frequent_purchaser_ratio
  description: Fraction of active users who qualify as frequent purchasers
  owners:
    - support@transformdata.io
  type: ratio
  locked_metadata:
    value_format: ".2%"
  type_params:
    numerator:
      name: distinct_purchasers
      constraint: is_frequent_purchaser
      alias: frequent_purchasers
    denominator:
      name: distinct_purchasers

Note the new constraint and alias parameters for the measure referenced in the numerator. The constraint parameter defines the constraint the user wishes to apply. That constraint filter will be applied to the measure it is attached to, and no others.

The alias parameter is only necessary if the same measure is used more than once in the metric but with different constraints, as shown above. This is necessary to avoid column name collisions in the rendered SQL queries. If all input measures are already distinct, the alias parameter may be omitted.

This may be used with any metric type. In expr type metrics the expr should always refer to the alias, if it is set, as below:

 metric:
  name: purchase_rate_90
  description: Fraction of purchases with < 90 pct chance of being returned
  owners:
    - support@transformdata.io
  type: expr
  locked_metadata:
    value_format: ".2%"
  type_params:
    expr: "(total_purchases - predicted_returns_90_pct) / NULLIF(total_purchases, 0)"
    measures:
      - name: total_purchases
      - name: predicted_returned_items
        constraint: prediction_score > 0.9
        alias: predicted_returns_90_pct # alias not required, but you may always include one for clarity

We fixed a bug where you could not filter on any non-string dimension values when querying a metric. This includes null, booleans like true, false, and any undefined fields like nan.

We fixed a bug where Saved Query creators couldn’t make changes to the metadata of the query (name, description, etc) unless they made a change to the query parameters themselves.

We're excited to announce table view as a chart type in Transform. You can toggle to the table view using the menu at the top-right hand corner of every chart in Transform.

The table feature includes the ability to order the data in the columns in your view, autofit the columns, and copy data out (using keyboard shortcuts) into spreadsheeets.

P.S - we're even more excited about this because introducing table view sets the foundation for us to allow users to query things that cannot be plotted - like multiple dimensions and metrics at once. Stay tuned for these updates!

We're excited to release v2.1 of Boards!

This release includes:

• The ability to set a board as a "Favorite" using the star icon on the Board page next to the Board name.
• The Boards landing page now includes a few more sections to help you find Boards you’re looking for: (1) Favorites, (2) Boards you’ve recently viewed, and (3) Popular Boards in your organization.
• All saved query charts on Boards now have an "Explore in Query" button which takes you to the Query page with the parameters of the query you were looking at on the Board. So, if you’ve applied global filters on your Board, those filters will get carried over to the Query page.

We resolved an error where results tab on the MQL server log page wasn't rendering properly.

We resolved an error where changing the time range to a custom selection would not let you update the chart after selecting.