Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

821 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