question

RickSawtell avatar image
RickSawtell asked

DAX query issues SS2012 post SP1

Using SQL Server 2012 prior to SP1 (any CU), I could run something like the following and get results back. After SP1, I get no data back. (I could use a CROSSJOIN, but that returns invalid results, but at least it returns results. Does anyone know what happened with the GENERATE command after SP1? EVALUATE GENERATE ( SUMMARIZE ( 'Sales Territory' ,'Sales Territory'[Sales Territory Group] ) , SUMMARIZE ( 'Product Category' ,"Internet Sales",SUMX(RELATEDTABLE('Internet Sales'), 'Internet Sales'[Sales Amount]) ) ) Thanks for your help.
queryservice-packanalysis
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Sorry we've been unable to help so far.
0 Likes 0 ·

1 Answer

·
RickSawtell avatar image
RickSawtell answered
I have been doing some analysis of the query plans. Unfortunately, I cannot currently reach my non-SP1 server to verify if what I am seeing is correct. According to BOL, the GENERATE statement works in a fashion similar to a CROSS APPLY in T-SQL. From BOL, Return Value "A Table with the Cartesian product between each row in table1 that results from evaluating table2 in the context of the current row from table1." This implies that some sort of relationship (or relationship chain) must exist between the row in Table1 being evaluated in Table2. Prior to SP1, it appears that the relationship chain would be followed regardless of the depth. Post SP1, I'm not so sure. I will do some testing and see if using RELATEDTABLE, or USERELATIONSHIP functions make a difference. Regardless, it is disconcerting to have this "breaking change" after applying the service packs and cumulative updates.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.