Sql-server – Link DAX OpenQuery Output to SQL Server Temp Table

daxsql serversql-server-2016ssas-2016tabular-model

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server.

I have created a linked server from my SQL Server instance to my SSAS instance.
I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models.

My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and
I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name…".

E.g.

UPDATE temp
SET temp.[Current Contract Count] = cte.[Contract Count]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey]

The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone].

I may well be missing a simple trick here?

How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

Best Answer

If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]') 
-- Result: [[awful column name]]]

Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets.

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]

Related Solutions

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT 
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM 
    ServerB.DATABASE.dbo.package p
    INNER JOIN 
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY 
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT 
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM 
        DATABASE.dbo.package p
        INNER JOIN 
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY 
        p.package_uuid
    ) AS OQ;

How to calculate/store Top 10 in a tabular model

I had a similar scenario and used the following DAX query...

First, to make it simple, I defined a measure to use inside the DAX so I don't have to repeat the formula. Then I used the generate to iterate on the TOPN formula:

define measure TableInTabular[NameOfTheMeasure] = COUNTAX(FILTER('Stats', ISBLANK([DeactDate]) = TRUE), 1)
evaluate
 (
  addcolumns
   (  
    filter
     (  
      generate
        (  
         VALUES(DatesTableName[Month]),  
         TOPN (10, VALUES(TableInTabular[ParentID]),TableInTabular[NameOfTheMeasure],0)
        ),
        TableInTabular[NameOfTheMeasure]>0
      ),
      "ActiveCount (or how you want to call this Column)",
      TableInTabular[NameOfTheMeasure]  
    )  
 )  
order by DatesTableName[Month] asc, 
TableInTabular[NameOfTheMeasure] desc

With the above you should have a top 10 ParentID and the Measure by each month. just replace the "TableInTabular" with your tabular table name where you have the data and the "DatesTableName" with the name of the dates table.

Please let me know if I misunderstood your question and hope it helps...

Best Answer

Related Solutions

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

How to calculate/store Top 10 in a tabular model

Related Question