Create an arbitrary order of elements in the list of dimensions and measures of the Excel pivot table for the tabular model of the SSAS cube
- Tutorial
If you had to deal with a cube in which the number of measures and dimensions over9000 and three screens are not enough to fit it, then you probably also heard users moaning about the inconvenience of working with this monster. After all, users often work with the same dimensions, without which almost no sample can do. However, due to the peculiarity of Excel, who loves to sort alphabetically all the elements that are in the Pivot Table Fields area , these most popular objects are often scattered throughout the list, mixed with the other (rarely used) elements.
You have to scroll up and down the list ten times while trying to install a filter on three (Date, Product, Customer) fields. Working with this every day, no nerves are enough.
The solution is banal and not new - to add a symbol or a number at the beginning of the dimension names, thereby affecting the order.
But users are one side of the coin, this approach is convenient for them. But what about the developers?
After all, it is as it should be: you start to write the name of the measurement in the formula, and the studio prompts, right? That's just in the case of dopsymbols, it all looks in the code, let's say ... not so much. In VS2017, we have already done a search by entry, and in previous ones there was no such thing and I had to write the Calendar not with the letter K , but with the number 5 , because 5 Calendar. Requests in other programs have to be written without prompts and remember which digit in which dimension or field is another quest.
When you need to change the order, then, in fact, you need to rename the table or field, and if this field is used in a formula, the studio will correct the formula. When there are dozens of formulas (and there are always dozens of them), half of the model will be highlighted in Git, because the studio has really fixed all the formulas, while I just wanted to swap fields in sort order. Requests in other sources (outside the studio) will break down altogether. Extremely uncomfortable!
But there is a way out.
In the tabular model, however, as in the multidimensional, it is possible to add translations. And we will use them.
Translation is done by unloading a .json file with text from the .bim model, editing it and then loading it back into the model. But Visual Studio does not allow you to upload a file for the Russian language if the model is originally Russian (in general, the translation cannot be unloaded for the base language of the model). But you can unload any other! The downloaded file needs to be edited, using any text editor that supports Unicode, replacing en-US with ru-RU in it . Next, using the special program SSAS Tabular Translator
to edit the names (but you can also in a notebook, if you like, it’s up to date). Importing our file back to the Vujal project . The translation has another significant advantage: From the .json file, only modified (translated) lines are loaded into the model, not the entire file, and only the translation is stored. Therefore, even if a new dimension or measure appears, and you forget to “translate” it, nothing will break, it will simply be displayed in Excel as it is. You can, of course, not do all these manipulations with the substitution of en-US for ru-RU, but then users will have to reconfigure the connection to the data source by adding the English language locale option. There is a feature
After we add a translation for the base language to the model, it cannot be deleted without prior preparation.
Most likely, it is unlikely to be needed, but, as is known, if it is not possible, but it’s very desirable, then it is possible.
Open the Solution Explorer , right-click on the model .bim file and select Go to Code
In the code, look for the ru-RU line , but not the one at the beginning of the file, but the one near the end, and fix it to en-US
We fix, save, reopen the model in design mode and check it.
As you understand, this is an undocumented function and you decide on use at your own peril and risk.
You have to scroll up and down the list ten times while trying to install a filter on three (Date, Product, Customer) fields. Working with this every day, no nerves are enough.
The solution is banal and not new - to add a symbol or a number at the beginning of the dimension names, thereby affecting the order.
But users are one side of the coin, this approach is convenient for them. But what about the developers?
After all, it is as it should be: you start to write the name of the measurement in the formula, and the studio prompts, right? That's just in the case of dopsymbols, it all looks in the code, let's say ... not so much. In VS2017, we have already done a search by entry, and in previous ones there was no such thing and I had to write the Calendar not with the letter K , but with the number 5 , because 5 Calendar. Requests in other programs have to be written without prompts and remember which digit in which dimension or field is another quest.
When you need to change the order, then, in fact, you need to rename the table or field, and if this field is used in a formula, the studio will correct the formula. When there are dozens of formulas (and there are always dozens of them), half of the model will be highlighted in Git, because the studio has really fixed all the formulas, while I just wanted to swap fields in sort order. Requests in other sources (outside the studio) will break down altogether. Extremely uncomfortable!
But there is a way out.
In the tabular model, however, as in the multidimensional, it is possible to add translations. And we will use them.
Translation is done by unloading a .json file with text from the .bim model, editing it and then loading it back into the model. But Visual Studio does not allow you to upload a file for the Russian language if the model is originally Russian (in general, the translation cannot be unloaded for the base language of the model). But you can unload any other! The downloaded file needs to be edited, using any text editor that supports Unicode, replacing en-US with ru-RU in it . Next, using the special program SSAS Tabular Translator
to edit the names (but you can also in a notebook, if you like, it’s up to date). Importing our file back to the Vujal project . The translation has another significant advantage: From the .json file, only modified (translated) lines are loaded into the model, not the entire file, and only the translation is stored. Therefore, even if a new dimension or measure appears, and you forget to “translate” it, nothing will break, it will simply be displayed in Excel as it is. You can, of course, not do all these manipulations with the substitution of en-US for ru-RU, but then users will have to reconfigure the connection to the data source by adding the English language locale option. There is a feature
After we add a translation for the base language to the model, it cannot be deleted without prior preparation.
Most likely, it is unlikely to be needed, but, as is known, if it is not possible, but it’s very desirable, then it is possible.
Open the Solution Explorer , right-click on the model .bim file and select Go to Code
In the code, look for the ru-RU line , but not the one at the beginning of the file, but the one near the end, and fix it to en-US
We fix, save, reopen the model in design mode and check it.
As you understand, this is an undocumented function and you decide on use at your own peril and risk.