Any way to access the JsonConvert or similar tools in an Advanced Script?

[o-o00o-o]

Hi Daniel,

I would like to build an automatic derivative measure creator to save a lot of repetitive work creating the long list of derivatives that we have ytd/mtd/wtd/yoy etc.

My plan is to define a bunch of derivative patterns in json with the DAX, format string, data dictionary definitions etc. and store that json in the model annotations (just like you do for BPRules), Then I would have an optional annotation on each measure that can list the derivatives that should be created. At build time these derivitives are automatically created.

Loads of benefits including keeping the number of files down in the model, allows changing the derivative dax patterns centrally etc.

First hurdle is getting the derivitive patterns json available in the advanced editor. I can see you have Newtonsoft calls in the code itself but I can't seem to get to them (ideally doing something dynamic like shown in the first answer using dynamic array so no class required - https://stackoverflow.com/questions/13297563/read-and-parse-a-json-file-in-c-sharp ).

Could you help me get this? Once I have this into a loopable set of objects I think it should be pretty straightforward to do.

Thanks!

[otykier]

I don’t have access to my PC right now, but it should be possible to copy the Newtonsoft.Json.dll into the folder where TabularEditor.exe resides, and then on the Advanced Scripting tab, I believe there’s a “References...” button, that should let you include the .dll. Once you do that, your script should be able to access classes from the .dll but you have to fully qualify them. Eg.

dynamic stuff = Newtonsoft.Json.DeserializeObject("{ 'Name': 'Jon Smith', 'Address': { 'City': 'New York', 'State': 'NY' }, 'Age': 42 }");

string name = stuff.Name;
string address = stuff.Address.City;

[otykier]

I’ll work on adding better JSON support in a future build. In fact, in addition to Annotations, Analysis Services now also supports something called Extended Properties, which are similar, but allow you to store pure custom JSON within the model metadata, instead of having everything encoded into a string.

[o-o00o-o]

I can't find any references... button? in either the toolbar

or in the menu or in the preferences anywhere. Does that mean it isn't going to be possible until you add direct support for this?

[o-o00o-o]

Further on this, I have used a regex to parse the json for now. I am bringing the values into anonymous classes but it seems that these are not supported very well in dynamic mode in advance scripts?

var blah = new {hello = "world"};
blah.hello.Output(); // works

//// trying a typed list
//System.Collections.Generic.List<Object> collect = new System.Collections.Generic.List<Object>();
//collect.Add(blah);
//collect[0].hello.Output(); // errors with: 'object' does not contain a definition for 'hello' and no extension method 'hello' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
//
////trying dynamic
//System.Collections.Generic.List<dynamic> collect;
//collect.Add(blah);
//collect[0].hello.Output(); // errors with: Predefined type 'Microsoft.CSharp.RuntimeBinder.Binder' is not defined or imported
// trying Enumerator
var collect = Enumerable.Empty<object>().Select(r => new {hello = ""}).ToList();
collect.Add(blah);
collect[0].hello.Output();

The bottom one works (thanks to this post) but is pretty ugly. Any better ideas?

[otykier]

Sorry, I'm confusing an earlier build that had the "References..." button, but it was removed due to a security concern. Currently, there is no better way to parse JSON, but I'll make sure to include the Newtonsoft.Json lib as part of the libraries that are accessible from within the script engine with the next release.

On the longer term, I plan to support CSX scripting, which allows much more flexibility in terms of adding external libraries, defining classes and methods, etc.

[otykier]

I've added Microsoft.CSharp.RuntimeBinder.Binder as well as Newtonsoft.Json as included libraries by default. In addition, Newtonsoft.Json and System.Collections.Generic are part of the "namespace usings" for the script execution, meaning that with the next release, the following code will work:

var blah = new {hello = "world"};
var collect = new List<dynamic>();
collect.Add(blah);
Output(collect[0].hello);

Note that the Output(...) method can not be used as an extension method: collect[0].hello.Output(), so you'll have to use the syntax shown above. This is due to a limitation in C# where dynamic objects can not have extension methods.

[otykier]

Fixed in 2.8

[o-o00o-o]

@otykier I've just got back to this old code and wanted to start using the Newtonsoft methods using the above but can't find the right command

dynamic stuff = JObject.Parse("{ 'Name': 'Jon Smith', 'Address': { 'City': 'New York', 'State': 'NY' }, 'Age': 42 }");

Output(stuff.Name);

I tried deserialise also.. is Newtonsoft still available to custom actions?

[o-o00o-o]

Found it.. it's

dynamic stuff = Newtonsoft.Json.Linq.JObject.Parse("{ 'Name': 'Jon Smith', 'Address': { 'City': 'New York', 'State': 'NY' }, 'Age': 42 }");

Output(stuff.Name);

[otykier]

Yep - remember you can now put using Newtonsoft.Json.Linq; at the top of your script, so you don't have to repeat it when you want to access the classes in that namespace (JObject, JArray, JToken, etc.):

using Newtonsoft.Json.Linq;

dynamic stuff = JObject.Parse("{ 'Name': 'Jon Smith', 'Address': { 'City': 'New York', 'State': 'NY' }, 'Age': 42 }");

Output(stuff.Name);