cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbix Member
Member

Power BI and Data Warehousing strategy

Hi All,

I'm about to start writing an analytics strategy for my organisation. I'm (more) classically versed in Oracle and SQL Server so my thinking so far has been towards creating an enterprise data warehouse that would ETL data from multiple fragmented databases into a single platform (likely Azure SQL Server or Azure data warehouse) for integrating and re-modelling data. However, Power BI's abilities have made me reconsider how much our organisation should look to invest in a classic data warehouse. However (again) Microsoft is clearly continuing to invest in both the new Power BI platform as well as Azure SQL Server/data warehousing strategy. So, a couple of open questions:

1) What's the difference in product placement between SQL Server (as a data store/warehouse)/Data warehouse and Power BI?

2) When should one look to use Power BI  for integrating data over a SQL Server (as a data store/warehouse)/data warehouse and vice versa?

Thanks!

Pbix

6 REPLIES 6
Super User
Super User

Re: Power BI and Data Warehousing strategy

Complex question on the one hand and very simple on the other. In terms of placement, SQL Server is still very much the tried and true enterprise approach to data warehousing and enterprise data management. Power BI is more positioned as an end-user self-service tool for dashboard and reporting. Could you in theory essentially build a "data warehouse" or "data mart" using Power BI. Sure...for relatively small shops that might be all that is required. You could ingest all of the data, build out your relationships, etc. Is that a good enterprise play? Not really.

 

The likely scenario for the enterprise is that all of the enterprise data goes into a data warehouse in SQL Server or Azure. You then layer Power BI on top of this in order to provide ad-hoc reporting and dashboard capabilities to the business, also providing the business a way to integrate their own "hidden" stores of data. But, with the Enterprise Gateway, you then begin to gain insight into these hidden data stores, etc.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

pbix Member
Member

Re: Power BI and Data Warehousing strategy

Thanks smoupre, that's helpful.

 

My thinking has been similar so far - ETL data from business applications into a SQL Server/Warehouse and plug both SSRS/Power BI into this new data store/warehouse. 

 

However, I started to wonder how redundant this step is if Power BI itself can just ETL data from multiple data sources too and then these Power BI files can be made available as sources for other users/Power BI reports?

 

Preferably I'd prefer to centralise OLAP/tabular-based reporting models in SSAS (making these models available to many people) but (it looks like) I'd need to invest in an Azure virtual machine on top of an Azure SQL Server/data warehouse to model these in Azure - whereas this functionality is native (and therefore ~free) to Power BI. 

 

As usual with microsoft, it feels like alot of these products cannibalise each other! 

 

Thanks,

 

pbix

Super User
Super User

Re: Power BI and Data Warehousing strategy

@pbix I just commented on a simliar thread today, but I think one of things you gloss over here is that a large scale implementation is going to have different concerns than something that can be thrown together in Power BI for a small company/implementation.

The cross functionality between products is great, because the cost of entry is low, but that doesn't mean that because the functionality is there that it fits the needs of all organizations.

Is the Desktop tool great because it can do alot and offers that capabilities to model/mash up data? "Yes"

Because it can do these things would I create a large scale BI implementation in the Desktop? "No"

 

Some of the major differences are:

No size limit in SSAS solution as opposed to 250mb in Desktop file

Backup / recovery / version control ( all features that can be leveraged in a full SQL DW, SSAS, TFS solution)

Having an entire model and all logic in a Desktop file (which has shown can randomly corrupt) is not a good choice

 

Just my 2cents.


Near SE WI? Join our PUG Milwaukee Brew City PUG
pbix Member
Member

Re: Power BI and Data Warehousing strategy

Thanks Eno, again that's helpful. Do you have the link to your other post? I'd be interested to read more of your thoughts on this.

Are you talking about anything specific when you mention different concerns for a large scale implementation? I think the points you make about resilience/recovery, size etc are key though - our customer database(s) contain over 500,000 client records so scalability is key here (though still a shame that we need to buy additional functionality in Azure to use SSAS!).

Are you aware of any Microsoft documentation that outlines strengths/weaknesses of Power BI Vs traditional server/ data warehouse technologies?

Thanks

Pbix



Super User
Super User

Re: Power BI and Data Warehousing strategy

@pbix This is the other thread I was refrencing. The "concerns" I pretty much list out in the differences of my previous post. Size limitations, speed, version control, backup, scalable, re-usable - are all factors I think of when implementing larger solutions.

 

I'm not aware of any specific documentation, but I don't think Power BI is meant to replace those technologies. I think it is an offering that allows individuals, and small and mid-size companies to get analytics in front of themselves like never before. Traditional server/warehouse/SSAS, or some flavor of that, is still advisable for larger scale implementations for all of the reasons I list above.

 

 


Near SE WI? Join our PUG Milwaukee Brew City PUG
John_D Frequent Visitor
Frequent Visitor

Re: Power BI and Data Warehousing strategy

I'm very new to Power BI, so I may be missing a technique here, but it seems to me that Power BI is great at aggregating raw data, and drilling through it, but once the source data has been pre-aggregated, I've found that difficulties can arise (like I say, maybe I'm missing a technique).

 

If the source data has already been averaged (as it has in my case), then you can't just let Power BI combine let's say team-level averages to get a department-level average, because the team sizes may differ significantly.  We already have the department-level figures in our source data, but there doesn't seem to be a simple way of conveying the department-team hierarchy to Power BI, in order to drill up and down it.

 

Is this something to be considered when sourcing data from a data warehouse, or is there a way of dealing with this that I haven't encountered?

 

Cheers

John

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 182 members 1,785 guests
Please welcome our newest community members: