Results 1 to 4 of 4

Thread: SQL Query on PowerPivot Data Model through VBA?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SQL Query on PowerPivot Data Model through VBA?

    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)

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    835
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Query on PowerPivot Data Model through VBA?

    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.

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Query on PowerPivot Data Model through VBA?

    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

  4. #4
    Board Regular
    Join Date
    Jan 2012
    Posts
    835
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Query on PowerPivot Data Model through VBA?

    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).

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •