Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbix
Helper III
Helper III

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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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



@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.

 

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors