Can't open .pbit file with Field Parameters in it

[AndreiRI27]

Hello,

Currently it is impossible to open a data model file (pbit and tabular editor file) with Field Parameters in it. Every attemp of opening a file ends with error message saying "Can't load a file".

[christianwade]

Hi Andrei, sorry for the delayed response. I've tracked down the cause and investigating how to work around it. I'll update when I have progress.

[christianwade]

Hi Andrei, latest release 5.0.33 should be fixed. Please let me know if it worked for you.

[AndreiRI27]

Hi Christian, Thank you for taking care of this. I've tested it on some sample dataset and it works! Currently I can't test it on any production datasets with XMLA connection (need to wait some time until new software is available in the organization). Did you test if the changes, including parameters can be deployed without any error using XMLA connection?

[christianwade]

Sounds good Andrei! I did some basic testing and AFAIK it's working.

[jonaskoeASYS]

Hello Christian, the fix works for me with .pbit files as well. Will this fix also be available for .bim files? When the column "relatedColumnDetails" from the field parameters feature is present in the .bim file I still get the "Can't load a file" error message.

I added a minimalistic sample model where the .pbit file works, but not the .bim file (extracted with tabular editor 2). Removing the relatedColumnDetails attribute from the .bim file, makes it possible again to load the file.

Model_with_parameter_fields.zip

[AndreiRI27]

I can confirm that it works now with .pbit files but the issue is still there if you use .bim file.

[christianwade]

Hi all, thanks for letting me know about this. I have it on the todo list to look at for next release. Just need to get to it as been maxed, but I won't forget it ...

[otykier]

@christianwade I believe I'm facing a similar issue with Tabular Editor. We need to explicitly specify CompatibilityMode.PowerBi in the call to JsonSerializer.DeserializeDatabase(...), when the source is a .pbit.

Since users of Tabular Editor might also save a Power BI model as a .bim file or a folder structure, it might be a bit more complex than just looking at the file extension. We'd have to iterate through the JSON to detect if the TOM includes any objects/properties that are Power BI only, such as RelatedColumnDetails. I'll get to work on that later this week, so I'm happy to share the code. Ideally though, it would be nice if the deserializer could automatically detect this...

[christianwade]

Thanks Daniel! I'll start a thread and copy you in. And of course, I'm open to code reuse 😀

[mthierba]

An easy "fix" would be to always invoke the deserializer with CompatibilityMode.PowerBi since in practice that's a superset of the other possible options (Excel/AS). That would give any client tool consuming TOM models the widest compatibility. Don't see much of a downside to this approach.

[otykier]

For Tabular Editor, always deserializing with "CompatibilityModel.PowerBI" is not desirable, as that would cause TOM properties to appear in the UI, which might not be supported on the SSAS/AAS instance that is the intended destination of the model.

Instead, I now scan the JSON of the model before deserializing, to detect if any PBI specific properties are present, and set the CompatibilityMode flag accordingly. This is the commit.

These are the relevant bits:

// If any of the following properties are present in the model, the JSON must be deserialized with CompatibilityMode = PowerBi:
public static readonly HashSet<string> PbiOnlyProperties = new string[] {
    "Sets",                                    // Pbi: 1400, Box: Unsupported
    "RelatedColumnDetails",                    // Pbi: 1400, Box: Unsupported
    "PerspectiveSets",                         // Pbi: 1400, Box: Unsupported
}.ToHashSet(StringComparer.OrdinalIgnoreCase);

private static readonly int[] analysisServicesStandardCompatLevels = new[]
{
    1200,
    1400,
    1500,
    1600
};

private static bool IsPbiCompatibilityMode(string tomJson)
{
    // Use PBI CompatibilityMode when model is one of the non-standard CL's, or if V3 metadata is enabled,
    // or if the model is using any PBI-specific TOM properties:
    using (var reader = new JsonTextReader(new StringReader(tomJson)))
    {
        while (reader.Read())
        {
            if (reader.TokenType == JsonToken.PropertyName)
            {
                switch ((string)reader.Value)
                {
                    case "compatibilityLevel":
                        reader.Read();
                        if (!analysisServicesStandardCompatLevels.Contains((int)((long)reader.Value))) return true;
                        break;
                    case "defaultPowerBIDataSourceVersion":
                        reader.Read();
                        if ((string)reader.Value == "powerBI_V3") return true;
                        break;
                    default:
                        if (PbiOnlyProperties.Contains((string)reader.Value)) return true;
                        break;
                }
            }
        }
    }
    return false;
}

And then when deserializing:

var mode = IsPbiCompatibilityMode(json)
    ? Microsoft.AnalysisServices.CompatibilityMode.PowerBI
    : Microsoft.AnalysisServices.CompatibilityMode.AnalysisServices;
var database = TOM.JsonSerializer.DeserializeDatabase(json, mode: mode);

[christianwade]

Thanks Daniel. All, just did release 5.0.39 with this change. Please retry.

[jonaskoeASYS]

Hello Christian, it works in my cases with .bim files, thanks!

[christianwade]

You're welcome!