SQL Query on PowerPivot Data Model through VBA?

ProjectThree

New Member
Joined
Mar 21, 2019
Messages
4
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)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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

New Member
Joined
Mar 21, 2019
Messages
4
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

Rules violation
Joined
Jan 21, 2012
Messages
912
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,992
Messages
5,447,783
Members
405,464
Latest member
Jayne125

This Week's Hot Topics

Top