Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Waseem Dar]

?Hello

We have an ERP system that has specific collation settings in her MS SQL database server. We have another system that has her own collation settings in her MS SQL database server. Now we are setting up Power BI Report server (on-premises). Appreciate if someone guide me about the collation settings for MS SQL server which will have Power BI Report server – should I use the standard settings Latin1_General_CI_AS or SQL_Latin1_General_CPI_CI_AS or of ERP system or it really makes no difference what the SQL Server collation settings are where Power BI Report server resides? Could there be any risk if collation settings are different from our ERP ones? Thanks

——————————
Waseem Dar
Business Analyst

——————————

[Hasham Niaz]

HI !

Please ask your ERP vendor why they are using non-standard SQL collation. They might have a strong reason for using it.

It sound like you seems there is no difference in using any of the collation, but it will not only effect your system performance but sometimes simple join queries unable to match due different collations.

Choose default SQL collations if your ERP vendor says its fine or during ETL transform those to standard SQL collations.

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Waseem Dar]

?Thanks Hasham. I did ask them and still waiting for the reply. It was actually a question whether using or not using default collation in SQL server for Report Server will make a difference. To me, SQL server is just keeping the published report data.

——————————
Waseem Dar
Business Analyst

——————————
——————————————-

[Hasham Niaz]

HI Waseem !

The question would be similar to as i keep my collation same for SQL & SSAS.

I would suggest to keep SQL, SSAS & SSRS collations same as a bit of uniformity among different layers.

Although i have seen in many cases these layers have different collations. So the decision relies on you, mostly SSAS & SSRS uses windows default collation which is [Latin1_General_CI_AS]

Regards

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Audrey Abbey]

Collation settings usually don’t cause an issue unless you have a case sensitive collation in the mix.
That is an absolute nightmare.

Fortunately, you can override/set collation at query level.

SELECT ColA, ColB
FROM Table
ORDER BY ColB COLLATE Latin1_General_CI_AI;

You can also put the COLLATE piece on one side of a join if you are trying to join across collations and need to force one side to match.

Hope that helps.

——————————
Audrey Abbey
SR. BI Developer/Analyst
LeapFrogBI
Portland OR
——————————
——————————————-

[Waseem Dar]

Noted, thank you

——————————
Waseem Dar
Business Analyst
——————————
——————————————-