Pull Data from OLAP cube into separate pivot tables and then make into master pivot table

[Mdb23310]

I currently have a workbook designed that uses data from several reports within our POS system. The problem is I have to run reports and export to excel, merge some reports so they are in one clean table, then copy and paste into my workbook. From there my pivot tables automatically refresh. The pivot tables uses a ton of formulas (calculated fields) to combine or analysis the data in the tables!

This has worked for me but it is time consuming!

Therefore, I have gained access to the POS systems OLAP cube to speed up data gathering. Fair warning, I am very new with cubes but have already figured quite a bit out.

So I go to excel, connect to it and it generates a pivot table. I add my dimensions and measures and build the same report I get from the POS system. The problems that arises is it is in pivot table and not a normal table. I can't pull all my data into one pivot, I have to use several.

How do I combine data from several pivot tables generated from a cube to one pivot table that I can use calculated fields to analyze?

Or

How can I bring data from cube without using a pivot table?

Or

Is there a way to bring data from cube in a table format to then put into power pivot?

Bonus question- how do I calculate data from the cube- i.e. Calculated field in normal pivot

 

[cagtbd]

Can you import the date you're trying to use at power pivot?

Another option that works with me is use named ranged so they dynamically update and i can use new pivot tables with them.
If you've got the problem solved with a roundabout, you can automate the process with vba (macros) which will make it faster as you'll be able to refresh your data model, pivot tables and final data instead of doing everything manually and from scratch everytime.

If you're not familliar with macros, you can post here whatever you're using as sheets and cell references so we can help you build one and in the meantime you learn how to make them.