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.
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.