Exploring Microsoft BI Options - Excel power pivot with sharepoint vs ssas data cubes RRS feed

  • General discussion

  • So we are looking at options for building dashboards.  One team member has built some neat looking "reports" in Excel using power pivot.  So then, the discussion turns to how to how to deploy these spreadsheets, the impact on production systems, access levels etc.  Im sure I am not the first to go down this road.

    So here are a few options with (my thoughts) the pros/cons

    (1) SSAS cubes with SSRS reports that use the cubes as their source
    pros: allows one to fully seperate the data, the model and the dashboard front-end
    cons:  time involved in setting up the plumbing, ETL process, maintenence, seperate databases etc

    (2) Excel powerpivot published to Sharepoint (using no intermediate database or etl)
    pros: fast to build, no ETL to manage
    cons:  excel spreadsheets all have embeded sql queries, many of which might overlap in function, resulting in redundant work that might not be kept in sync.

    My consensus at this point is that to deliver BI to a team that might easily grow beyond a handful of people to dozens to several dozens, the first option is better.  If one starts off with the 2nd, I can only imagine the nightmare of excel spreadsheets that will result.

    Am I about right?

    • Edited by shiftbit Saturday, April 15, 2017 12:11 PM ncvbnvb
    Saturday, April 15, 2017 12:10 PM