Case insensitive Search in Pivot possible with case sensitive SSAS Tabular?

Question

Good morning,

following setup:

• SSAS Tabular on SQL Server 2019 Enterprise (Comp level 1500)
• Collation Latin1_General_CS_AS
• Case sensitive option checked in SSAS
• Client: Excel Pivot / Power BI

I have some texts in my dimension table which represent the text (posting text for entries in our P&L). Those texts are case sensitive and should be displayed as case sensitve, e.g. "EMP Branding 04/21" should be displayed exactly like this, and if there's another Entry named "emP branding 03/21" it should also be displayed exactly like this. It's working as intended (EMP != emP) (posting texts all have a distinct ID, so that's no problem)

BUT when I search for the posting text in my Excel pivot connected to the SSAS instance (no import) it's also Case Sensitive, which I assume is "correct". So searching for "emp" returns nothing, searching for EMP returns the one entry seen above and so on ...

BUT with our old setup (not made by me) using a Multidimensional model searching for "emp" retrieves all of the possible upper/lower case combinations stored. Dragging the posting text item from Filters to the pivot lists the entries using their original upper/lower case entry (in out multidimensional cube)

My question now: Can I change the behaviour in Tabular so that search is case insensitive, but the items dragged on the pivot still are case sensitive? just like in multidimensional, or isn't this possible at all?

Best regards
Lukas

Answer 1

No this is not possible in Tabular.

Multidim had a feature where you could use one (or more) column(s) for the "key" of a given attribute and a different column for the name. So your dimension could look as follows:

ID Name
123 EMP Branding 04/21
456 emP branding 04/21

and the ID would be mapped to the key property and the Name would be mapped to the name. So even though the model was using the case insensitive collation the uniqueness of the attribute was driven by the ID column so you would get both rows visible and you could then do a case insensitive search of the name.

In tabular this is not possible as a single column in your source maps to a single column in your tabular database.

Answer 2

Hi dgosbell. Thanks very much, so I can stop searching :)