Making tabular models more sensitive

Case sensitive, that is.

Last year, Denny Lee posted about PowerPivot’s case insensitivity problem. Tabular models have the same insensitivity problem by default. As I mentioned in my previous post on language and collation settings, when creating a new tabular project, the collation used by default is picked by the engine for you based on the language setting provided by SQL Server Data Tools (BIDS). For example, if you are running ENU tools, new tabular model projects get a Latin1_General_CI_AS collation by default.

You can see the effects of this using a simple example. Create a new tabular model on your ENU Visual Studio installation. Copy the following table:

Now paste this table into your tabular model. Because the engine decided that you wanted a case insensitive collation, your pasted table now looks like this:

You see that the case of your pasted data was not preserved, which is the expected behaviour given the collation used by the engine. The case of the first encountered piece of data is preserved, and all other matching data uses that casing from there on out. And because the collation of the database is read-only, there is nothing you can do inside of VS to make the tabular model case sensitive. What to do?

Hand editing BIM files is not supported, so you can’t just view the code of the tabular model and replace the language with a collation. Altering the workspace database from SSMS is definitely not supported, so you can’t update your model on the fly. You can, however, create an empty database with the desired collation using a CREATE DATABASE command on the server. And you can import databases into VS from the server, thus creating a new model that you can edit. This is the same approach Marco Russo took for setting a drillthrough action. Let us take that approach for making a case-sensitive database.

Step 1: Fire up SSMS. Connect to your tabular server. Create a new XMLA query. Paste in the following script:

 <Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="https://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="https://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="https://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="https://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="https://schemas.microsoft.com/analysisservices/2011/engine/300/300">
            <ID>Collation test</ID>
            <Name>Collation test</Name>
            <Language>1033</Language>
            <Collation>Latin1_General_CS_AS</Collation>
            <DataSourceImpersonationInfo>
                <ImpersonationMode>Default</ImpersonationMode>
            </DataSourceImpersonationInfo>
            <ddl200_200:StorageEngineUsed>InMemory</ddl200_200:StorageEngineUsed>
            <ddl200:CompatibilityLevel>1100</ddl200:CompatibilityLevel>
        </Database>
    </ObjectDefinition>
</Create>

Execute this script. You now have an empty database with a case-sensitive collation.

Step 2: Go back to VS. Create a new project using the Import from Server (Tabular) template. Import the database from step 1. Inspect the properties of the newly created Model.bim file. Behold, a case sensitive collation:

Step 3: Copy the table above and paste it into your model. Behold, a case sensitive pasted table:

A bit hacky, but it will do for now.