SQL Query on PowerPivot Data Model through VBA?

[ProjectThree]

Hi all - I'm in the process of developing an MI dashboard for a set of business users, and I often get requests from these users for basic reports for the granular data.

I want to build a self service reporting tool within this dashboard; The solution that I envisage is a worksheet with form controls allowing a user to specify the data they want, and the filters that they would like to apply and a button then reading "get report". Depending on the options selected it would formulate a SQL query string using VBA, and then run that query against the Powerpivot data model within the spreadsheet. Once complete it would create a new workbook and populate it with the query data allowing the user to save as required.

What I wanted to know is if it actually possible to run a SQL query against a power pivot data model, and then how I would go about exporting the query data to a new workbook? Any code snippets or links to relevant threads would be much appreciated. (Though I couldn't find any relevant threads)

 

[theBardd]

Why not just add a pivot table? All fields exposed, filters, slicers, all you could need. You can even add DAX measures and get really smart.

 

[ProjectThree]

I've considered doing that, but the data schema is more similar to a snowflake schema with multiple fact tables. I think I'd need a couple of different pivot tables, and not all users are comfortable in even basic pivot table use. I figured a tool like this would be more intuitive, flexible but also controlled. There are also around 47 different dax measures, and often requested by these business users so it's essential that I can pull there through

 

[theBardd]

As far as I am aware, whilst you can do a lot of stuff with the connections in VBA, the actual model is not exposed to VBA (and remember, it is an OLAP cube!).

You could build your dashboard using CUBE formulas, they work nicely against a cube (now there is a surprise ), and you can get dynamic using CUBESET and CUBERANKEDMEMBER. It would take a fair bit of work, but I am guessing from your comments so far that you are up for a fair bit of work to make it simple for your users, but it should be possible. You could even approach it by adding the selection criteria on a sheet using form controls and the like, and then build the dashboard with CUBE formulas in VBA (in fact that sounds like a nice project, I think I will play with

that​

as well).