Online Ad-hoc Analysis Interface for Users

[vickyd]

Not necessarily a PBIRS questions but, just like a user can connect to a SSAS model from Excel and pull fields/measures into a pivot, I want to know if it is possible to provide some kind of online interface that users can use to create ad-hoc views? 

Maybe some kind of acustom visual where report creator can add a bunch of fields/measures that a user can see/use (or a perspective in SSAS) and drop them into a pivot whichever way they want. 

Is there anything in Power BI that can do that or is there any other solution out there that can help achieve this?

[Anonymous]

If you are using Direct Query to SQL or Tabular then this is already pretty straight forward you can just attack the same datasources.

For PBIX files where the data has been imported a simple-ish method of achieving this is for the user to download the PBIX file and open it in PBI Desktop locally. If you then open DAX Studio (www.sqlbi.com) the PBIX file will be listed as a connection source. Connect to it and in the bottom right hand corner you will see you are connected to a SSAS instance running on localhost on a random port number. (like this)

  

You can connect to a SSAS data source in Excel using these details and you will "see" the PBI data model. You can close DAX Studio but you need to keep PBI Desktop running. Be warned that any analysis you do will not refresh once you close PBI Desktop as the captive SSAS instance will also be shut down. Also note that the next time you start that PBIX up it will get a different port number so you would need to edit the connection in the Excel to link them back together.

So it's not impossible but it is fiddly. Oh if only Excel just figured out there were captive instances like DAX Studio does and just join to them. This would be way simpler...

Theoretically you might be able to do this via a server based report. The same process of spinning up a captive SSAS instance behind each PBIX report is taking place on the PBI-SSRS server, you would just need to have someone open the report in a web browser and then figure out the port numbers concerned and connect using those. I have no idea if this is secured in any way as I haven't actually tried this, but I will, when I get some time, now I've thought about it.  

S