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

Luki Z 116 Reputation points
2021-05-11T06:01:07.927+00:00

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

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,231 questions
0 comments No comments
{count} votes

Accepted answer
  1. Darren Gosbell 2,371 Reputation points
    2021-05-11T10:37:15.183+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Luki Z 116 Reputation points
    2021-05-11T10:38:39.853+00:00

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

    0 comments No comments