Can we integrate the data returned by $system.MDSCHEMA_MEASURES with Tabular Model Actions based on the attribute selected?

Topics: Standing Discussions
Sep 8, 2014 at 1:47 PM
Hi Team

I am currently exploring all the features available in Tabular Model actions (via BIDSHelper) for an upcoming project.
Is it possible to link $system.MDSCHEMA_MEASURES table with the Tabular Model columns through actions such that I can display the description in there. I am aware that we can use Synch Descriptions option to synch MSSQL extended properties with Tabular Model.

Also could you please give an example setting for Action Type: Statement.

Thanks & Regards
Sep 8, 2014 at 9:21 PM
Great suggestion!

I added an example usage of $system.mdschema_measures to the bottom of this page:

Incidentally, I thought that you had to be an admin to call $system DMVs, but I tested this one under a regular reader user and it seems to have worked.

Please let me know how this example works for you?

By the way, since Tabular models in AS2012 and AS2014 don't allow .NET assemblies to run on the server, we can't make the "self documenting cube" quite a slick as we can with Multidimensional models as described here:
Marked as answer by furmangg on 9/8/2014 at 2:23 PM
Sep 8, 2014 at 9:23 PM
Also, to answer your question about Action Type=Statement, I don't know exactly what that's used for and how that would be any better than a Rowset action. Maybe someone else will post an answer.
Sep 8, 2014 at 11:02 PM
I have not seen anyone use Type=Statement in a solution. But my understanding is that this type of action was targeted at running OLEDB commands. So maybe you might fire a stored procedure on a SQL Server to trigger the backordering of some stock or something like that. I believe that Statement types do no expect a response, they are meant to trigger some sort of action on another system.
Sep 8, 2014 at 11:42 PM
I don't think Excel supports Statement actions anyway.
Sep 9, 2014 at 3:53 PM
Thank you very much furmangg and dgosbell! :)
The example worked perfectly, but I was not able to implement something similar for Levels & Attribute members.
where [CUBE_NAME] ='<CUBE_NAME>'

Is it possible to find the selected member in a generic way with Target Type set as cell.

I was in an assumption that "self documenting cube" for Tabular Models work with MS Excel 2013 (Powerview). I have not got a chance to try them as yet. Thanks again for the link.

Thanks & Regards
Sep 10, 2014 at 11:35 AM
NilimaRao wrote:
Is it possible to find the selected member in a generic way with Target Type set as cell.
No, there is no easy generic way to find the selected member when using a target type of cell with a tabular model. Due to the fact that there could be multiple filters and multiple columns cross-joined there is often no single "selected member" for a cell. But Tabular models don't really even have a good generic way of finding the set of current members. With Multi-Dimensional models you can kind of get close by using a .Net stored procedure.
Sep 10, 2014 at 12:34 PM
Thank you!!