So here is my attempt to help you quickly translate your model to multiple languages. Initially just for the measures (and only the names of those, not descriptions and display folders), but the same approach could be used for all texts that can be translated in the model.
WARNING
By using the scripts in this blog post, you will be sending your measure names to a 3rd party (OpenAI). Do not use it if your measure names contain confidential information. Be sure to check OpenAI’s Terms of Usage before using it with this script or the scripts in the posts that I link to.
Preparing your model
To begin with, you should open up your model with Tabular Editor and create the desired languages under Translations
Next, you should open a new C# script and copy/paste this block of code in there:
#r "System.Net.Http"usingSystem.Net.Http;usingSystem.Text;usingNewtonsoft.Json.Linq;// You need to signin to https://platform.openai.com/ and create an API key for your profile then paste that key // into the apiKey constant belowconststring apiKey ="<YOUR API KEY HERE>";conststring uri ="https://api.openai.com/v1/completions";conststring question ="In the context of an analytical data model, could you please translate the measure name \"{0}\" in the table \"{1}\" from English (en-US) to {2} with short precise business terms?\n\n";constint oneMinute =60000;// the number of milliseconds in a minuteconstint apiLimit =20;// a free account is limited to 20 calls per minute, change this if you have a paid accountbool dontOverwrite =true;// this prevents existing descriptions from being overwrittenusing(var client =newHttpClient()){
client.DefaultRequestHeaders.Clear();
client.DefaultRequestHeaders.Add("Authorization","Bearer "+ apiKey);int callCount =0;// if any measures are currently selected add those to our collectionList<Measure> myMeasures =newList<Measure>();
myMeasures.AddRange( Selected.Measures );// if no measures were selected grab all of the measures in the modelif( myMeasures.Count ==0){
myMeasures.AddRange(Model.Tables.Where(t => t.Measures.Count()>0).SelectMany(t => t.Measures));}else{
dontOverwrite =false;}foreach(var m in myMeasures){foreach(var culture in Model.Cultures){var currentCultureName = culture.Name;var currentCultureDisplayName = culture.DisplayName;// if we are not overwriting existing descriptions then skip to the next measure if this one is not an empty stringif(dontOverwrite &&!string.IsNullOrEmpty(m.TranslatedNames[currentCultureName])){continue;}// Only uncomment the following when running from the command line or the script will show a popup after each measure//Info("Processing " + m.DaxObjectFullName);var body ="{ \"prompt\": "+ JsonConvert.SerializeObject(String.Format(question, m.Name, m.Table.Name, currentCultureName))+",\"model\": \"text-davinci-003\" "+",\"temperature\": 1 "+",\"max_tokens\": 2048 "+",\"stop\": \".\" }";var res = client.PostAsync(uri,newStringContent(body, Encoding.UTF8,"application/json"));
res.Result.EnsureSuccessStatusCode();var result = res.Result.Content.ReadAsStringAsync().Result;var obj = JObject.Parse(result);var desc = obj["choices"][0]["text"].ToString().Trim();
m.TranslatedNames[currentCultureName]= desc.Trim('"');
callCount++;// increment the call countif( callCount % apiLimit ==0) System.Threading.Thread.Sleep( oneMinute );}}}
If you haven’t already done so when playing with Darrens example, you now need to create your own API key and replace in the script. See his blog for more info on how to do that. Now I suggest saving as a macro for easy reuse with any model you open – you can also just run it from the C# script editor.
TIP
If you use a backslash in the macro name, you can build a folder hierarchy of macros. I called mine: AI\Translations\Translate measure using GPT3. More info about macros available in our documentation here.
Now all you need to do, is to right click your measure(s) and click AI -> Translations -> Translate measure using GPT3:
Thank you to Darren & Erik for sharing their thoughts. I hope you like this expansion to their great work.
6 thoughts on “Translate your measures using Tabular Editor and GPT3”
konstantin
Could this also be done by using copilot with a MSFT api?
Maybe a strange question and a bit out of scope, but is it possible to add translations based on a script or duplicate a translation to multiple new cultures.
Not sure i fully understand your question, but you can definitely add translations from a script – you can see in line 57 of the script how the translation is set thru C#.
You can also add new cultures to your model using:
var culture1 = Model.AddTranslation(“es-ES”);
Glad you liked it. You can the same look by the window menu, opening the last item (XXX palette – probably default palette) and choosing Plastic Space under the Light grouping.
Could this also be done by using copilot with a MSFT api?
Hi Konstantin
Yes I suppose so as long as the endpoint work similar to the ChatGPT, but as I have not experimented with Copilot APIs I can’t say for sure.
Maybe a strange question and a bit out of scope, but is it possible to add translations based on a script or duplicate a translation to multiple new cultures.
Hi Daan
Not sure i fully understand your question, but you can definitely add translations from a script – you can see in line 57 of the script how the translation is set thru C#.
You can also add new cultures to your model using:
var culture1 = Model.AddTranslation(“es-ES”);
BR David
Hey! I love it.
I have to ask… How did you get that UI / palette N TE3? I cannot seem to find that particular theme..
Hi Tommy
Glad you liked it. You can the same look by the window menu, opening the last item (XXX palette – probably default palette) and choosing Plastic Space under the Light grouping.
BR David