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)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top