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

Mdb23310

New Member
Joined
Mar 25, 2017
Messages
1
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

New Member
Joined
Jul 6, 2017
Messages
1
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.
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top