Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-06
4
Medium Priority
?
838 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 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

971 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