Filter by joined data from different data source

littlewall · January 27, 2022, 11:45am

Hello,
We have web analytics data in BigQuery (unique visits, click events, etc.). As part of those data, there is an ID of items (i.e., Products, Companies,…) that are stored in the different data sources (MongoDB).

We followed this example - https://cube.dev/docs/recipes/joining-multiple-data-sources - and it seems to work well. We can use dimensions from both data sources, and they are joined correctly.

However, filtering query by field from the joined tables from the different databases seems not to work. For example, in BQ, there are blog posts unique visits, and in MongoDB, we have post data, including the author. We can query both visits and post data (joined on authorId that is present on both tables), but we cannot filter query to include only some of the authors (filter "author equals [array_of_ids]). Query ignores it and returns the whole set regardless of set filters.

Is this even possible in current version of CubeJS?

Thank you all,
Milan Z.

Ivan_Vdovin · January 27, 2022, 3:45pm

Hi @littlewall and thanks for posting your question!
Could you share your schema and config file so we can try to reproduce that or at least take a look, please?

littlewall · January 27, 2022, 5:17pm

Hello,
thank you for your quick reply. This is where I get stuck.

cube('FileHostingAnalytics', {
    sql: `SELECT * FROM ${getDBName()}.filehosting`,

    dataSource: dataSources.bigQuery,

    preAggregations: {
        fileHostingRollup: {
            type: 'rollup',
            external: true,
            measures: [CUBE.count, CUBE.totalCount],
            timeDimension: CUBE.timestamp,
            dimensions: [CUBE.filehostingId],
            granularity: 'day',
            indexes: {
                categoryIndex: {
                    columns: [CUBE.filehostingId],
                },
            },
        },
        combinedRollup: {
            type: 'rollupJoin',
            measures: [CUBE.count, CUBE.totalCount],
            dimensions: [
                FileHostingData.author,
                CUBE.id,
                CUBE.filehostingId,
            ],
            rollups: [FileHostingData.fileHostingDataRollup, CUBE.fileHostingRollup],
        },
    },

    joins: {
        FileHostingData: {
            relationship: 'belongsTo',
            sql: `${CUBE.filehostingId} = ${FileHostingData.id}`,
        },
    },

    measures: {
        totalCount: {
            type: 'count',
            sql: 'id',
        },
        count: {
            type: 'count',
            drillMembers: [id],
        },
    },

    dimensions: {
        id: {
            sql: 'id',
            type: 'string',
            primaryKey: true,
        },

        filehostingId: {
            sql: 'filehostingId',
            type: 'string',
        },

        timestamp: {
            sql: 'timestamp',
            type: 'time',
        },
    },
});

cube('FileHostingData', {
    sql: 'SELECT id,author FROM `filehostings`',

    dataSource: dataSources.mongobi,

    preAggregations: {
        fileHostingDataRollup: {
            type: 'rollup',
            dimensions: [CUBE.id, CUBE.author],
            indexes: {
                categoryIndex: {
                    columns: [CUBE.id],
                },
            },
        },
    },

    measures: {
        count: {
            type: 'count',
        },
    },

    dimensions: {
        id: {
            sql: 'id',
            type: 'string',
            primaryKey: true,
        },
        author: {
            sql: 'author',
            type: 'string',
        },
    },
});

With this setup, I can do something like:

{
  "dimensions": [
    "FileHostingAnalytics.filehostingId",
    "FileHostingData.author"
  ],
}

And the result seems to be correct:

Those empty cells in some rows are correct (we don’t have data in MongoDB for that ids).

But when I try to add some filters:

{
 "filters": [
    {
      "member": "FileHostingData.author",
      "operator": "equals",
      "values": [
        "R6t8eyP0IXiPJFOsPn_LV"
      ]
    },
  ],
  "dimensions": [
    "FileHostingAnalytics.filehostingId",
    "FileHostingData.author"
  ],
}

The result is the same (expectations are just two rows in the result but got six)
I tried to add the filter “FileHostingData.author” with operator isSet, but It remains the same.

Thank you very much, it’s possible you’ll see something I don’t.

Ivan_Vdovin · February 21, 2022, 6:40am

@littlewall
I’ve found the following issue and it seem to be the same one:

github.com/cube-js/cube.js

Filtering by dimension in rollupJoin does not work with cubestore

opened 11:10AM - 02 Sep 21 UTC

assembler

backend:cube-store

Given two tables (e.g. reports and accounts), where 1 account can have multiple …daily reports (1:N), when filtering by dimension property (e.g. account.name), the filter is completely ignored. Schemas: ``` cube(`Accounts`, { sql: `SELECT * FROM accounts`, dimensions: { id: { sql: `id`, type: `number`, primaryKey: true, shown: true }, name: { sql: `name`, type: `string` } }, refreshKey: { every: `6 hour` }, preAggregations: { aRollup: { dimensions: [CUBE.id, CUBE.name], }, }, }); cube(`Reports`, { sql: `SELECT * FROM t_campaign_reports`, joins: { Accounts: { relationship: `belongsTo`, sql: `${CUBE.account_id} = ${Accounts.id}`, }, }, measures: { impressions: { type: `sum`, sql: `impressions`, title: `Impressions`, format: 'number' }, }, dimensions: { id: { sql: `id`, type: `number`, primaryKey: true }, account_id: { sql: `account_id`, type: `number` }, date: { sql: `date`, type: `time` } }, refreshKey: { every: `6 hour` }, preAggregations:{ crRollup: { measures: [CUBE.impressions], dimensions: [CUBE.account_id], timeDimension: CUBE.date, granularity: `day` }, jRollup: { type: `rollupJoin`, measures: [CUBE.impressions], dimensions: [Accounts.id, Accounts.name], rollups: [CUBE.crRollup, Accounts.aRollup], timeDimension: CUBE.date, granularity: `day` }, }, }); ``` Query: ``` { "measures": [ "Reports.impressions" ], "timeDimensions": [ { "dimension": "Reports.date", "granularity": "year" } ], "order": { "Reports.date": "asc" }, "dimensions": [], "filters": [ { "member": "Accounts.name", "operator": "contains", "values": [ "Test" ] } ], "limit": 5000 } ``` Generated SQL: ```sql SELECT date_trunc('year', `reports__date_day`) `reports__date_year`, sum(`reports__impressions`) `reports__impressions` FROM dev_pre_aggregations.reports_cr_rollup AS `reports__cr_rollup` LEFT JOIN dev_pre_aggregations.accounts_a_rollup AS `accounts__a_rollup` ON `reports__account_id` = `accounts__id` WHERE (`accounts__name` LIKE CONCAT('%', ?, '%')) GROUP BY 1 ORDER BY 1 ASC LIMIT 5000 ``` **Expected behavior** The query should return yearly data for all accounts whose name contains the "Test" keyword. However, cubejs ignores the filter completely and totals across all accounts are returned. If we run this query without pre-aggregations, everything works as expected. The generated SQL query is also correct. **Version:** 0.28.29

Feel free to add your comment and subscribe for updates there.