Help with Dynamic Pivot (SQL) in DirectQuery

[kishengandhi]

Hi all, 

Wondering if someone can help me here. 

We currently have a table in the format: (Direct Query, data source Azure SQL Database)

col 1  |  col 2  |  Name     |  Str Value |  Datetime Value  |

1       ID-1      header1     value1       null

1       ID-1      header2     value2       null

1       ID-1      header3(DT) value3       value3datetime

1       ID-2      header1     value4       null

1       ID-2      header2     value2       null 

and so on

We need to Pivot this so that col 2 (IDs) are the rows, the columns are the values in 'Name' and the values are 'Str Value' or 'Datetime Value' (doing the pivot within PBI requires an import rather than DQ). So:

ID    | header 1 | header 2 | header 3(DT)

ID-1  value1       value 2      value3datetime

ID-2  value 4      value 2      null

This needs to by dynamic as there can be N number of column headers to pivot. The reason we're trying to use datetime rather than the string representation of headers that contain date values is because we would need to filter on this in reporting. Every header name that is a date will have a string representation and a datetime representation of the value in the initial table. If it isn't a date header, the datetime value in original table will be null. 

One solution I thought of was to create a view within the database itself however there were challenges with that. The other solution was to use a SQL statement when connecting to format the data into the format we require. 

This would be my first real attempt at more advanced SQL than just selects and i've got the statement below:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME("Name")
            FROM dbo.table
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT "col 2", '+ @cols +'
            FROM
            (SELECT "col 2", "name", "str value"
            FROM dbo.table
            ) s
            PIVOT
            (MAX("str value") FOR "name" IN ('+ @cols +')
            ) p '

EXECUTE(@sql)

This works in theory, it pivots the data correctly. However when trying to apply it to the report, it fails as it seems PBI can't handle declare statements. It also doesn't take into account the datetime values so a datetime column (such as created date for example) would still be represented as text. Changing the column type AFTER getting the data is not supported by DQ. 

As we are using PBI Embedded we need to use DQ to keep the data up to date. 

Any help would be appreciated! Is this even possible? 

Kishen

[v-yulgu-msft · Accepted solution]

Hi @kishengandhi,

---

 However when trying to apply it to the report, it fails as it seems PBI can't handle declare statements. 

---

This is currently working as designed.  The "Decalare" statement in SQL Query is not supported by DirectQuery connection. Please check Vicky_song's reply in this thread.

---

It also doesn't take into account the datetime values so a datetime column (such as created date for example) would still be represented as text. 

---

That case, you can directly change the data type of the date column in Report View after getting data to desktop. Reference: SQL Server DQ Date data type

Best regards,

Yuliana Gu

[kishengandhi]

Thanks @v-yulgu-msft.

Is there any indication on whether declare statements would ever be accepted? I have worked around this by creating multiple tables and creating multiple different relationships.

We currently have a large amount of data and as I understand it, using SQL statements on this data to create multiple different tables is going to have a negative impact on the performance of DirectQuery. We are finding that on our embedded reports we are getting time outs and visuals not loading. When they do load it's taking a very long time. I have a few questions around this:

• Aside from imposing filters to limit the sample of data, is there any other way to improve performance of DQ?
• Is there a speed difference between embedding reports or viewing them through the web app? 
• Would it be better to use the import method and trigger a refresh using the REST API?

Thanks, 

Kishen

[jvirgi]

@kishengandhi  I had the exact same issue.  Can you explain a bit more how you accomplished this pivot with directquery by using multiple tables and relationships?  Any suggestions would be appreciated.