cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
sundeep579
Frequent Visitor

VARCHAR vs NVARCHAR when using DirectQuery in Power BI

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/

6 REPLIES 6
RosaIsela06
New Member

Hi 

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

HelloWorld1
Frequent Visitor

any update about this issue ? thank you !

v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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/

 

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

Hi sundeep579,

 

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors