Solved

How to Create a Consolidated Pivot Table "Dashboard" from Multiple Pivot Tables?

Posted on 2013-12-06
4
756 Views
Last Modified: 2013-12-07
I have 5-10 separate "Dashboards" that are compiled together from multiple Pivot Tables whose data is taken from multiple Datasource (20-30) Types (Excel, SQL Server, SSRS, .TXT, .CSV, etc.).

So for example I have Dashboard #1 is derived/compiled/displayed using data from 10 different Pivot Tables whose data is from 20 different Datasources.

Then I have Dashboard #2 which is derived/compiled/displayed using data from 7 Pivot Tables whose data is from 25 different Datasources.

Then Dashboard #3, derived/compiled/displayed using data from 9 Pivot Tables whose datra is from 22 different Datasources.

Then Dashboard #4, etc,..etc....

QUESTION:  What is the best solution or "path-to-take" if my requirement requires me to take the SUMMARY DISPLAY DATA from each of my 5-10 Dashboards and then compile/consolildate them into a SINGLE DASHBOARD that will be used to COMPARE/ANALYZE the totals of each individual dashboard to one another and also provide performance variances, KPIs etc.

Does Excel 2010 using POWER PIVOT have any limitations especially in regard to DATASOURCES BEING USED? Is there a "maximum" number of Datasources issue to be aware of?

Please let me know if you need more info, I'll reply asap.

Any input/feedback is greatly appreciated?

THANKS!
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39702981
This may be an over simplification...   but why not use a series of GETPIVOTDATA formulas to extract the information from the individual pivot tables on each unique dashboard into a Summary Dashboard worksheet.   Then, those values can be used for further analysis for performance variances, KPIs etc.   You already have everything in place so no need to recreate it in another technology...   Just extract what you need to a SUMMARY worksheet.   As the data sources change and the pivot tables change, so will the GETPIVOTDATA values.

In the cell location where you want the result to be...   press the equal sign and then click on a SUM TOTAL or SUBSET TOTAL on the pivot table.   This will generate the GetPivotData formula for that sum.    Any "value" from a pivot table can be extracted in the same way.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39703061
This recommendation sounds promising.

So another caveat in my delima is this; we hbe another 100 or so Measures that need to be added to the final new Single Dashboard that are NOT already in existance.

Question: So are there any concerns in regard to the number of different datasource that are used in a single POWER PIVOT Project File? Is there a "Maximum" Limit Restriction?

Thanks for your input.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 39703342
I am not aware of a limitation on the number of data sources for a PowerPivot project but you may run into problems with the amount of data and memory on the PC where it is running.   My laptop start becoming unusable at 4M rows and 15 columns.   More memory will solve that but if you are writing for a group of users then their hardware limitations may come into play.  Also, with too many data elements the data model becomes overwhelming to a user.  If this is only for you then perhaps it is OK.  If for a group of user then simplicity is key.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39703367
This will be deployed to a "Sharepoint Server" environment so memory and space should not be an issue. Consuming will take place in this type of Enterprise setting.

Thanks again for all of your input and feedback.

We'll see how it goes...!  :-/
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question