I had an interesting request from a Data Scientist in our team recently. He’d been extracting some data from one of our tabular models, however he was having some trouble getting his predictive model working reliably.
We already had a query in the form similar to the following where we had a couple of group by columns, one or two filters and a handful of measures:
EVALUATE
SUMMARIZECOLUMNS(
'Table1'[Column1],
'Table2'[Column2],
FILTER(ALL('Table1'[Column3]), 'Table1'[Column3] = "Value",
"Measure1", [Measure1],
"Measure2", [Measure2]
)
Up until this point we had been manually adding measures that we though may influence the behaviour we were trying to predict, but this was a slow, trial and error based process. So the Data Scientist rang me and said “You know what? Why don’t you just give me an extract with all the measures?”. “You do realise we have over 1,000 measures” I said, “because we have lots of time intelligence variations like Current Month, Previous Month, Month over Month variance, etc.” and . “That’s fine he replied, I can always ignore any that I don’t want or that are not significant – you can just do something like a SELECT * right?”.
So if you’ve ever written your own DAX queries you would know that you can do a query like the following to get all the columns in a single table
EVALUATE 'Table'
But that will not get you any measures, you have to list out the measures manually one, by one. At this point I knew that I really did not want to spend hours to hand type a query with over 1,000 measures so I starting thinking what options I might have for generating this query.
I knew I could probably build some sort of foreach loop in Powershell using AMO/TOM. Or I could maybe use the Advanced Scripting in Tabular Editor. But I also knew that I could easily get a list of all the visible measures by querying the $SYSTEM.TMSCHEMA_MEASURES or $SYSTEM.MDSCHEMA_MEASURES DMV’s using DAX Studio
After a bit of experimenting I ended up with the following expression which builds a list of all the visible measures in the model in the "Name", Expression format that is needed for SUMMARIZECOLUMNS
SELECT '"' + [Name] + '", ' as [Caption], '[' + [Name] + '],' as [Name]
FROM $SYSTEM.TMSCHEMA_MEASURES
WHERE NOT [IsHidden]
ORDER BY [Name]
Then I was simply able to paste in the output from this query after the filters in our existing query and run it – Job done.
I like it, nice solution!
There is one important difference between those MD and TM DMVs which is that the MD one can be accessed by a non-admin connection, TM only by admin. Any idea why that is?
It is a problem for us because for some reason the MD version has stopped returning DEFAULT_FORMAT_STRING from a Tabular source and our user facing app does not connect as admin. TMSCHEMA_MEASURES has all the info but we can not get at it.
I don’t know the reason why the TMSCHEMA DMVs are all restricted to admins. But the MDSCHEMA views were originally built to support MDX clients and the formatstring is not important for those as the resultset includes the formatted values. My guess is that for tabular the MDSCHEMA views have largely been superseded by DISCOVER_CSDL_METADATA. This is the view that DAX Studio uses to populate it’s Metadata tree and this has the format strings for both columns and measures. So if you are generating DAX and you allow the creation of implicit measures you can also get the format string for columns.
Thanks for the reply. We are already using CSDL for some other Tabular metadata not provided by the Adomd interface but for us while it provides formatString for Columns it does not for Measures. E.g. for the [Sales] measure it just shows:
PropertyRef Name=”v_Sales_FormatString”. Follow that ref and it is the name of a property but still no formatString there.
It obviously works for you but I’ve just been through the DAX Studio code from github and can not see anything different in the CSDL call.
Incidentally we have a few admins who insist on using PowerPivot on a Tabular source (I cant stop them) and this has broken their queries too. They now have to edit the MDX and remove the Cell Properties: format_string and formatted_value to make it work.
This is something odd going on server side.
I have patched something in the application for now but will keep digging.
That does sound like an issue with your model. Are you using calculation groups with dynamic format strings? This would prevent the DMVs from returning a fixed format string. Have you tried running Power BI Desktop against this model and either using the All Queries trace in DAX Studio or using the Performance Profiler in Desktop to extract the generated DAX. If the format strings are dynamic you will see an extra measure generated to get the format string for each cell.
Thanks for that great clue. I am using calc groups but not dynamic format strings. However, I am using static format strings on some calc group items. From experimenting I find that if I take those off MDSCHEMA_MEASURES returns default_format_string correctly but with any kind of format string in place on a calc group that breaks it. I wondered if it was the % in “0.00%” but no, its any kind of format string even just “0” .
That is rubbish. Calc Groups can override the default in a specific cell context but that should not change the default in the metadata. Thats true for static or dynamic .
Oh well, at least I know now, thanks for your help.