2

I have an existing SSAS 2014 multidimensional project which uses a case sensitive SQL Server database as a source (Latin_1_General_CS_AS collation). The attributes in the SSAS project are also specified as case sensitive and it works just fine with SSAS 2014. After deploying to SSAS 2022 multidimensional, I get "duplicate key" errors when processing a SSAS dimension when one of the attributes has values in the source that differ only by case. I've constructed a minimal workable example with one dimension:

CREATE DATABASE CaseSensitiveColumnDB COLLATE Latin1_General_CS_AS;
GO
USE CaseSensitiveColumnDB;
CREATE TABLE MyDimension(Id INT, Name VARCHAR(100));
INSERT MyDimension VALUES (1, 'SOMENAME');
INSERT MyDimension VALUES (2, 'somename');

Create a datasource view with a table provided above in SQL, create a dimension based on this table and try to deploy and process it on a SSAS 2022 instance.

Expected: Processing is successful (as on SSAS 2014) Actual: Processing breaks with an error "Duplicate attribute key has been found when processing: Table: 'MyDimension', Column: 'Name', Value: 'SOMENAME'. The attribute is 'Name'."

I've already tried specifying the case sensitivity explicitly on the attribute itself (was already there), in the dimension properties, in the SSAS Database properties (right click the project node in the VS Solution Explorer -> Edit Database) and even in the properties of the SSAS instance with no difference. Am I missing something or is it broken indeed?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Andrej Kuklin
  • 41
  • 4
  • 2
    Don't post links to file sharing sites; the users you are asking for free help from are often well versed in computers and knows the dangers of downloading unknown content from an unknown person. Plus if the link ever goes dead then your question becomes useless to future readers. Post *all* the relevant information in the post. – Thom A Jul 03 '23 at 19:55
  • Aside from the other comment: highly doubtful that a commercial product is "broken indeed" - but if you do think there is an actual product issue, then this is a Customer Support issue, not something for Stack Overflow. Most importantly, edit your question to show all relevant details (as the other comment mentioned), including database/table setup, expected vs actual behavior, etc. – David Makogon Jul 03 '23 at 20:07
  • @ThomA Posting the code of a SSAS project (8KB zip file from the link above) in text form is not helpful. I added the steps to create the necessary objects in the question. – Andrej Kuklin Jul 04 '23 at 06:54
  • @DavidMakogon DB/Table setup is already in the question. I've added the description to expected/actual behavior for a case it's not obvious. – Andrej Kuklin Jul 04 '23 at 06:57

1 Answers1

0

SSAS 2022 has a change in string comparison function to default force no case comparison of string regardless of collation setting. The change comes from a request in tabular model, but is also used for multidimensional model.

Acton plan:

add 
<CollationForcedNoCase>0</CollationForcedNoCase> 
under the <Feature> tag in the msmdsrv.ini file , then restart SSAS
Ricky
  • 1
  • 1