-1

I am using SQL Server as my source. These are my configuration settings:

  • OTLP Server Level Collation setting: SQL_Latin1_General_CP1_CI_AS

  • OTLP Database Level Collation setting: SQL_Latin1_General_CP1_CI_AS

  • SSAS Server Level Collation setting: Latin1_General_CS_AS

I have created a table with one column with column collation of SQL_Latin1_General_CP1_CS_AS. This table have the following values:

trEE
PlAnT
TREE
PLANT
Plant
Tree
plant
tReE
tree
pLaNt
tREE

Each of these records are unique because of case sensitive collation. It works well in OTLP Server, however when I try import to SSAS it says my dimension table have duplicate values. All values are replaced with first variation of the case sensitive value. See below for values after import.

trEE
PlAnT
trEE
PlAnT
PlAnT
trEE
PlAnT
trEE
trEE
PlAnT
trEE

Does anyone have a solution to this issue?

I have tried changing OTLP Server Collation and OTLP Database Collation settings. This did not resolve the issue.

I would like to import all the values as an Unique Value for Dimension Table.

desertnaut
  • 57,590
  • 26
  • 140
  • 166
Jason Cheung
  • 13
  • 1

1 Answers1

0

Power BI and Analysis Services are case-insensitive by default.

Read more here: https://www.sqlbi.com/articles/letter-case-sensitivity-in-dax-power-bi-and-analysis-services/

If you are working on your private SQL Server Analysis Services instance, then you can choose the collation style to use during setup. Although you could use case-sensitive collation, we advise you not to do it. Instead, find another way to handle the issue – for example by replacing those internal codes with a new integer key. If you rely on the local engine being case-sensitive, you incur the risk that users export data from your model and process it using their Power BI Desktop instance. At that point, the problem would arise anyway, and it would be outside of IT’s control. These data problems need to be solved as early as possible in the data supply chain to avoid serious issues at the end of the chain. Users are not expected to even realize that incorrect results might be linked to case-sensitivity.

TheRizza
  • 1,577
  • 1
  • 10
  • 23
  • Thank you for your answer @TheRizza. The reference link describes my situation very well. The issue is that I would like to see all the different variation of name with different casing (mixed with Upper and Lower case). Because each should be a unique key in the system. I also tried adding a integer key to each unique value, but all the values still imported as trEE PlAnT. Is there a work around for this? – Jason Cheung May 19 '23 at 13:33
  • You need to use a binary collation in SSAS. https://learn.microsoft.com/en-us/analysis-services/languages-and-collations-analysis-services?view=asallproducts-allversions#bkmk_collationtype – David Browne - Microsoft May 21 '23 at 14:01