Solved

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

Posted on 2013-12-06
4
781 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

632 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