Solved

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

808 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