locked
SQL vs. Power Pivot RRS feed

  • Question

  • Hello. Everytime our IT team says they are going to create a report in SQL it takes them forever. Is SQL less efficient than Power Pivot? I feel like some of the stuff they do I could accomplsih in 20 minutes with the data set in Power Pivot. 
    Monday, November 21, 2016 6:24 PM

Answers

  • Hi,

    In my opinion you cannot really compare SQL and Power Pivot as their nature is very different. If you are to compare SQL to anything it would be Power Query.

    SQL is not inefficient (but it is if you use it with the wrong purpose) - it is intended to clean your data (or can be used to it), join tables, manipulate your data etc. It is row based, whereas Power Pivot is more column based.  

    I personally use SQL a lot to make views etc. where I can create tables/views I find suitable to import into a Power Pivot model. I other words to make the foundation for Power Pivot.

    Power Pivot is much more efficient if you are to make calculations, for instance YTD etc.

    Perhaps your IT department doesn't know the possiblities in cubes, tabular models etc? I have a couple of friends that are SQL diehards who think anything should be made in SQL eventhough the result would be much better in Power Pivot etc. 

    Monday, November 21, 2016 7:17 PM

All replies

  • Hi,

    In my opinion you cannot really compare SQL and Power Pivot as their nature is very different. If you are to compare SQL to anything it would be Power Query.

    SQL is not inefficient (but it is if you use it with the wrong purpose) - it is intended to clean your data (or can be used to it), join tables, manipulate your data etc. It is row based, whereas Power Pivot is more column based.  

    I personally use SQL a lot to make views etc. where I can create tables/views I find suitable to import into a Power Pivot model. I other words to make the foundation for Power Pivot.

    Power Pivot is much more efficient if you are to make calculations, for instance YTD etc.

    Perhaps your IT department doesn't know the possiblities in cubes, tabular models etc? I have a couple of friends that are SQL diehards who think anything should be made in SQL eventhough the result would be much better in Power Pivot etc. 

    Monday, November 21, 2016 7:17 PM
  • Hi AlexMartine,

    The SQL means either SQL statement or SQL server database. SQL is a standard language for accessing and manipulating databases. And the SQL server database is used to store data. 

    PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook. You can create relationships between heterogeneous data, create calculated columns and measures using formulas to calculate the desired result. Building PivotTables and PivotCharts to further analyze the data visually so that you can make timely business decisions . 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 22, 2016 9:28 AM
  • Hi Morten, 

    I thought you weren't supposed to make views in powerpivot...not sure why though haha. 

    Please do not use Views which have already joined the tables. Shameless plug for our new book which explains these concepts in detail, including the commandment “Thouh shall not flatten” Book: Power Pivot and Power BI”

    What are cubes and tabular models? 

    Thanks!!


    Wednesday, November 23, 2016 8:04 AM
  • Hi AlexMartini,

    Analysis Services provides several approaches for creating a business intelligence semantic model: Multidimensional, Tabular, and Power Pivot. Tabular models are Analysis Services databases that run in-memory or in DirectQuery mode, accessing data directly from backend relational data sources. For more details, please review the article.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 25, 2016 9:02 AM
  • What are cubes and tabular models?

    Power pivot is a cube in tabular mode, but just a client base solution, not a server base one like SSAS = SQL Server Analysis Services.

    Regarding views see your other post: power pivot on top of SQL


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 25, 2016 5:26 PM