How to optimize data retrieval with power pivot and power bi

[ammartino44]

Hello.

I'm loving power pivot. I'm trying to see where IT fits in this world. A couple questions:

1) IT currently is a bottleneck in the sense that they are creating a bunch of SQL and crystal reports. How does this change with Power Pivot? I'm trying to figure out what to ask them for in terms of SQL queries and how that whole process works.

2) Can a data warehouse tool eventually offer the same thing as power pivot in terms of granular access that allows for the building of metrics and "angles" on the fly?

[Greg_Deckler · Accepted solution]

This is a very good question. Generally the way that I see this get rolled out into an organization at a more "enterprise" level is through Organizational Content Packs. Essentially, IT takes the time up front to connect to different data sources and build out models, metrics, etc. This content pack is then published to the organization. End users can then use this content pack to create their own reports, dashboards etc. In this way the data lineage is preserved for the parts of the original data model. In other words, everyone is using the same metrics calculated the same way for example. This puts reporting more into the realm of a self-service model in that places IT more into the data modeling area and business into the reporting area.

I have also started seeing people use Azure Data Catalog to publish out data sources and then just allow users to connect with Power BI and create reports, but that is a steeper learning curve because now the business is responsible for both data modeling and reporting. 

I would assume that a data warehouse tool could eventually offer similar functionality, or you could just connect Power BI to your data warehouse and use Power BI for that.

I think that this is an interesting discussion and would like to hear other people's thoughts on this.