Hi there!
Is it possible to develop a cube in Excel (perhaps with VBA) and Power BI? Or must the Power BI and Excel cube be built using SQL Server and SSAS?
Thanks!
@powerbideveloper I didn’t get your question, all 3 use the same technology. Power Pivot and Power BI are kind of supersets of SSAS.
Yes they are, but, in general, I don’t think we talk about cubes when talking about Excel and Power BI modeling and reporting, or do the architects talk in those terms? So, when I hear talk about getting the data (using VBA) from the cube in Excel, what exactly could they be talking about?
Cube = Model in SSAS/Power Pivot/ Power BI, it is terminology derived from Multidimensial SSAS model where the data was stored in cubes, so just like Rubik’s cube, every tile represented an intersection of dimension and fact.
Excel has cube formulas to directly query your data model in the worksheet itself, without having to use Pivot Tables.
Okay, now it’s making some sense. I would like to get familiar with this and how VBA is being used to access the cube in Excel.
Do you have any suggestions (tutorials) on how VBA or Excel cube formulas are being used to query a “data model in the worksheet itself”?
Download a excel file with the option Analyze in Excel from power bi services
Create a power pivot with all the filters and measures that you needed and use convert to formula.
- Make sure your cursor is within the Pivot Table
- Click on the the contextual tab called Analyze . (Note: this tab only appears when you cursor is within the Pivot Table)
- Click OLAP Tools → select Convert to Formulas
This formulas is your start point to built your report.
This read this article.
1 Like
@jbressan and @AntrikshSharma,
Jose, your suggested article covers the Excel cube part of my question. Do you perhaps have any suggestions (good links/tutorials) on how VBA in Excel is used to access a cube?
1 Like
Bumping this post for more visibility.
1 Like
Hi @powerbideveloper! We noticed that your inquiry was left unsolved for quite some time now. Looks like your inquiry was out of the experts and users’ bounds.
We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!