Any idea on how to solve it?

Thank you in advance,

CodePudding user response：

I'm not sure how helpful this suggestion is but could a possible work around include having two queries.

Query 1: Existing query without transactions
Query 2: The same dimensions with transactionId included

The idea would be to use the SSIS Aggregate component to group by the original dimensions and count the transactions. You could then merge the queries together via a merge join.

Would that work?

CodePudding user response：

The API supports what it supports. So if you've attempted to pair things that are incompatible, you won't get any data back. Things that seem like they should totally work go together like orange juice and milk.

While I worked on the GA stuff through Python, an approach we found helped us work through incompatible metrics and total metrics was to make multiple pulls using the same dimensions. As the data sets are at the same level of grain, as long as you match up each dimension in the set, you can have all the metrics you want.

In your case, I'd have 2 data flows, followed by an Execute SQL Task that brings the data together for the final table

• DFT1: Query1 -> Derived Column -> Stage.Table1
• DFT2: Query2 -> Derived Column -> Stage.Table2

Execute SQL Task

SELECT
    T1.*, T2.Metric_A, T2.Metric_B, ... T2.Metric_Z
INTO
    #T
FROM
    Stage.T1 AS T1
    INNER JOIN
        Stage.T2 AS T2
        ON T2.Dim1 = T1.Dim1 /* etc */ AND T2.Dim7 = T1.Dim7

-- Update you have solid data aka 
-- isDataGolden exists in the "data" section of the response
-- Usually within 7? days but possibly sooner
UPDATE
    X
SET
    metric1 = S.metric1 /* etc */
FROM
    dbo.X AS X
    INNER JOIN #T AS T
    ON T.Dim1 = X.Dim1 
WHERE
    X.isDataGolden IS NULL
    AND T.isDataGolden IS NOT NULL;

-- Add new data but be aware that not all nodes might have 
-- reported in.        
INSERT INTO
    dbo.X
SELECT
    *
FROM
    #T AS T
WHERE
    NOT EXISTS (SELECT * FROM dbo.X AS X WHERE X.Dim1 = T.Dim1 /* etc */);