VARCHAR vs NVARCHAR when using DirectQuery in Power BI

[sundeep579]

I am currently designing reports in Power BI using DirectQuery to connect to Azure SQL Server. The reports that I am designing are performance sensitive since I need to show all the rows in a table visual. While designing the database tables, we have used VARCHAR to yeild better performace since they consume less space when compared to NVARCHAR columns. But in Power BI, there is just one string datatype called 'TEXT'(as specified in the below link) which stores unicode characters. Will all my string data from SQL Server which is in ASCII be converted to Unicode when it is read in Power BI ? If this is the case, then using VARCHAR in Sql Server can negatively impact performace.

Please let me know your thoughts on this and what datatype should I use in Sql Server to yield better performance in Power BI reports.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/

[RosaIsela06]

Hi 

I'm facing same problem, Did you find any solution?

[HelloWorld1]

any update about this issue ? thank you !

[v-qiuyu-msft]

Hi @sundeep579,

Choose VARCHAR or NVARCHAR data type based on which kind of data you want to stored on database, the data type defined on SQL database side will not have big affect when query data from the Power BI.

In your scenario, as you use DirectQuery to get data, you can take a look at this article to consider performance.

Best Regards,
Qiuyun Yu

[sundeep579]

Hi Qiuyun,

Thanks for your response. The reason for raising this post is that the client has asked us to improve the performance of a particular table visual in the report. The table visual loads about 100,000 - 200,000 rows depending on a slicer selection. So, I looked at the SQL Server table from where data is being loaded for the visual. The table had couple of NVARCHAR columns and few INTEGER monthly sales columns. I converted both the NVARCHAR columns to VARCHAR columns since they will always store english characters in order to reduce the size of the table and in-turn reduce the size of the data that the report has to read from the database. The NVARCHAR to VARCHAR conversion reduced the size of the table by about 25%(from 480 MB to 360 MB). But after making this change, the performance of the report has come down instead of improving. This is when, I came across the below article which says that Power BI supports only Unicode TEXT datatype. So, Is Power BI trying to convert ASCII strings to Unicode strings which is what is causing the performance degradation in my case ? Is there anyway to avoid this so that I can yield better performance using VARCHAR columns ?

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/

[RosaIsela06]

I'm facing same issue. DId you find any solution?

[danm]

Hi sundeep579,

By any chance did you get any answer for this question ? maybe you test it, please share