locked
power pivot vs data warehouse RRS feed

  • Question

  • Hello. I had a couple of questions about powerpivot vs. data warehouse. Could someone give me some insight?

    Advantages of Powerpivot vs. Data Warehouse tool

    Advantages of Data Warehouse tool vs. Powerpivot

    How can Power complement a Data warehouse tool?

    Should you have everyone using Powerpivot? 

    Tuesday, September 27, 2016 5:51 PM

Answers

  • Hi AlexMartini,

    >> Advantages of Powerpivot vs. Data Warehouse tool.

    1. Smaller File Size. Putting the data in PowerPivot, then doing pivots is both faster and results in smaller file sizes, while in excel 600,000 rows and multiple columns, it’s a very large file and unstable.
      Please refer to the following steps which show the process of filtering the table data prior to importing.
    2. PowerView in Power Pivot. PowerView is a highly visual way of showing off all that data in PowerPivot report.
    3. Connect to SharePoint Lists Programmatically.
    4. Relationships Between Random Data Sources. My reports tend to be a patchwork quilt of data sources, so the ability to create relationships between them and then build meaningful pivots makes job much easier.
    5. CALCULATE Formula in PowerPivot. When I do get some data I can’t quite fit into a one-to-many format, I can still make it work with CALCULATE(). Almost All Excel Formulas are Applicable.

    >> Advantages of Data Warehouse tool vs. Powerpivot.

    A data warehouse is typically read-only, with the data organized according to business requirements, rather than by computer processes. The information stored in a data warehouse is historical, spanning transactions that have occurred over time.

    For this reason, warehoused data is often summarized or aggregated to make it easier to scan, access and query. Redundant data is often included in a data warehouse in order to provide users with multiple views of information that present it in logical, easily understood groupings. The benefits includes Better decision-making, Quick and easy access to data and Data quality and consistency.

    >>How can Power complement a Data warehouse tool? Should you have everyone using Powerpivot?

    Power Pivot and Data warehouse tool are both have their advantages. Customer can use Powerpivot according to their requirements. From what I know, the simple and flexible usage, visualization of Power Pivot may complement a Data warehouse tool.

    If you have any more questions, please don’t hesitate to ask.

    Regards,
    Angelia




    Wednesday, September 28, 2016 8:31 AM
  • I believe the answer to your questions require an assessment of your organization's abilities.

    • How complicated is your data? Searching for the right elements in a structure with hundreds of tables can be very daunting and time consuming. A data warehouse can prepare structured files with the usually desired fields all laid out saving time and effort. On the down side, there's always that one field not in the mix.
    • Acceptable LAG. a warehouse is a copy of other data. Typically is has a scheduled update cycle, perhaps a little as hourly to weekly (which we have at the hospital I work at). The larger the dataset, the more time to generate it and copy it. If you need close real time data, this is a consideration.
    • Should everyone use PowerPivot? Actually, I would bet that at some point that will be automatic and it will migrate into regular Excel. We see that happening in part now. The syntax of the formula engine is more difficult to learn as is understanding things like context. This one really depends on relative staff level expertise. I do not foresee lots of C suiters spending time in it unless they have a propensity to enjoy that sort of thing. But there are publishing processes to put your work out there.
    • PowerPivot/Query can definitely compliment a warehouse. First it brings to the table flexible analysis using the expanded formula engine. It lets one quickly bring in outside the warehouse information and/or relate unrelated files (combinations not thought of before) into the analysis. A well designed warehouse will greatly reduce the work needed to assemble the data elements one may want using the warehouse tables that usually contain most of the common elements in their prebuilt datasets.
    Thursday, September 29, 2016 5:51 PM

All replies

  • Hi AlexMartini,

    >> Advantages of Powerpivot vs. Data Warehouse tool.

    1. Smaller File Size. Putting the data in PowerPivot, then doing pivots is both faster and results in smaller file sizes, while in excel 600,000 rows and multiple columns, it’s a very large file and unstable.
      Please refer to the following steps which show the process of filtering the table data prior to importing.
    2. PowerView in Power Pivot. PowerView is a highly visual way of showing off all that data in PowerPivot report.
    3. Connect to SharePoint Lists Programmatically.
    4. Relationships Between Random Data Sources. My reports tend to be a patchwork quilt of data sources, so the ability to create relationships between them and then build meaningful pivots makes job much easier.
    5. CALCULATE Formula in PowerPivot. When I do get some data I can’t quite fit into a one-to-many format, I can still make it work with CALCULATE(). Almost All Excel Formulas are Applicable.

    >> Advantages of Data Warehouse tool vs. Powerpivot.

    A data warehouse is typically read-only, with the data organized according to business requirements, rather than by computer processes. The information stored in a data warehouse is historical, spanning transactions that have occurred over time.

    For this reason, warehoused data is often summarized or aggregated to make it easier to scan, access and query. Redundant data is often included in a data warehouse in order to provide users with multiple views of information that present it in logical, easily understood groupings. The benefits includes Better decision-making, Quick and easy access to data and Data quality and consistency.

    >>How can Power complement a Data warehouse tool? Should you have everyone using Powerpivot?

    Power Pivot and Data warehouse tool are both have their advantages. Customer can use Powerpivot according to their requirements. From what I know, the simple and flexible usage, visualization of Power Pivot may complement a Data warehouse tool.

    If you have any more questions, please don’t hesitate to ask.

    Regards,
    Angelia




    Wednesday, September 28, 2016 8:31 AM
  • I believe the answer to your questions require an assessment of your organization's abilities.

    • How complicated is your data? Searching for the right elements in a structure with hundreds of tables can be very daunting and time consuming. A data warehouse can prepare structured files with the usually desired fields all laid out saving time and effort. On the down side, there's always that one field not in the mix.
    • Acceptable LAG. a warehouse is a copy of other data. Typically is has a scheduled update cycle, perhaps a little as hourly to weekly (which we have at the hospital I work at). The larger the dataset, the more time to generate it and copy it. If you need close real time data, this is a consideration.
    • Should everyone use PowerPivot? Actually, I would bet that at some point that will be automatic and it will migrate into regular Excel. We see that happening in part now. The syntax of the formula engine is more difficult to learn as is understanding things like context. This one really depends on relative staff level expertise. I do not foresee lots of C suiters spending time in it unless they have a propensity to enjoy that sort of thing. But there are publishing processes to put your work out there.
    • PowerPivot/Query can definitely compliment a warehouse. First it brings to the table flexible analysis using the expanded formula engine. It lets one quickly bring in outside the warehouse information and/or relate unrelated files (combinations not thought of before) into the analysis. A well designed warehouse will greatly reduce the work needed to assemble the data elements one may want using the warehouse tables that usually contain most of the common elements in their prebuilt datasets.
    Thursday, September 29, 2016 5:51 PM