Power Bi vs. Data Warehouse

[ammartino44]

Hello. Everyone says that a data warehouse and Power Bi are complementary and that the better you get with Bi the more a data warehouse can be put to good use. I almost see that Power BI is a replacement for a data warehouse, no? If you have databases with the data, can't you just get the queries that you need from those database directly into power BI?  I guess the only issue would be the amount of data that you can work with is more in a data warehouse tool (Pentaho). Even the creation of metrics is much more flexible in power bi. In our data warehouse, a lot of the output is at the summary level. I guess we can adapt the warehouse but every iteration seems to take a lot of time. Thoughts? 

[ankitpatira · Accepted solution]

@ammartino44 we're doing exactly that using power bi on top of data warehouse. Good thing is power bi has ability of DirectQuery feature which means power bi connects live to your data source and doesn't import any data from the warehouse as data warehouse has millions of rows of records. I would suggest you use DQ feature of power bi and connect straight to data warehouse and that would also eliminate your problem of memory issues.

[Quentin]

Seems to me that the new Composite model feature and the previously available dataflows kills the adventages a datawarehouse could have against powerbi cloud service...

Well done microsoft you truely killed a monster in my understanding

[a_mixed_life]

As noted by others, it is not meant to replace Data Warehouse. Maybe to be used if you don't have a data warehouse or can't afford and you're a very small (startup) company it may work.

I use Data Warehouse and Cubes with PowerBI. Using PowerBI specifically only for Visualization however quick measurements is easy on PowerBI versus me writing or rewriting SQL.

[ammartino44]

@a_mixed_life I still don't get how it wouldn't replace a data warehouse. Maybe I'm struggling to see what a data warehouse is (the full potential). I CANNOT get granular data in my data warehouse. In power BI, when I can get data dumps I can build the data into whatever angles/views I want it to be. Per IT, we can get the data warehouse to where it will add all the necessarry metrics but that's always the next iteration, so it seems that anything new or outside of the scope of the stock metrics and looks in the data warehouse tool, would be better to use power b. 

Also, do you use powerpivot on top of the data warehouse? How do you get the data for visualization? 

[Greg_Deckler]

Well, for smaller datasets, Power BI could theoretically be used as a data mart or data warehouse. The data model behind it is SQL Server Tabular after all, so the same basic technology that is in SQL Server Analysis Services.

[KHorseman]

Our data sources are mostly... not great for reporting. We have a small data warehouse managed by a third party that covers a few things but does not include most things that desperately need one. One of the things we're using Power BI for is a sort of stopgap between what we have and the data warehouse we (well, I anyway) want. I do all sorts of wild transformations and merges in my queries (go find some of the threads I have started), which produces fairly large datasets that have a lot of the flexibility of a data warehouse. So when people come up with crazy new things they want added to existing reports, I can often just throw together a quick visual or two, maybe a slicer, and they have what they want, where that data formerly would have been essentially inaccessible. It's not a real data warehouse but it gets us by amazingly well for now.

That said, the time is fast approaching where I'm going to stop suggesting and start demanding a real warehouse. There are very real disadvantages to using Power BI in place of one. Most limitations come from the basic fact that everything is stored in a one file. The more things you try to make a single data model do, the better your chances of producing something that's unstable or at least hard to debug or significantly alter without producing a cascade of errors. Lots of table transformations can produce a large file with slow refresh times. You are also forced to use Import rather than DirectQuery, so you need to do multiple refreshes per day to stay somewhere near realtime, which puts a load on your data source.

Conclusion: if you just can't have a good data warehouse right now, you can go a long way by faking it in Power BI. Power BI is not a substitute or replacement for a warehouse, nor is it meant to be, but you might be surprised how much it can manage. But if you think you need a data warehouse, you're probably correct.

[mim]

@KHorseman pleased to read your reply, i am in the same situation with a Big difference is that we don't have a datawarehouse at all, to be honest after working 17 years in 4 countries, 3 continents, maybe 5 companies, I have yet to see one, although my job is reporting coordinator which require a central database to host all the difference source of data :).

I work in construction, for some reason we are technology averse:) a typical IT infrastructure would be a lot of operational database that don't talk to each others, and dozen of people reporting the same **bleep** thing using different tools ( MS access, Massive excel file with vlookup, ok sometimes SQL Express).

last year I discovered PowerPivot, it was a life changing experience, I manage to connect all those data source with all those crazy transformation in one semantic model,  and it was properly documented,  ok i got even a promotion 🙂 a couple of months later the data keep increasing, PowerPivot did not scale well, the cloud is not an option for contractual reason, I tried all kind of workaround, then I read this Blog from imke  it turn out PowerBI desktop engine is a local SSAS server that works only in the local PC ( for obvious reason), and you can export the data to Excel, I moved my Model to PowerBI Desktop, and it running beautifully since three months ( main fact table 5 Million rows and counting) 

now I know what's next, in six months with M integration with SQL Server Vnext, I am going to buy a standard license, even with my own money, Microsoft created an awesome technology, it democratized Data, I have experienced that and I am grateful 

[steveo250k]

@KHorseman I'm not sure if you are saying you would require a central database or if you have one.  In anycase, that central database would be a data warehouse.  It may not be well build or conform to a "dimensional architecture", but a data warehouse none the less.

A data warehouse generally uses an ETL process to Extract data from the operational systems, Transform that data, and Load it into another repository (a.k.a., the data warehouse).  From the conversation here, at best PowerBI is being used as an ETL tool with the loading part being the subsequent reports or analyses.  But, Power BI was created as a tool for reporting and analysis using a data source such as a data warehouse.  Though it can connect to any type of data source.  It is absolutely NOT a data warehouse.

A data warehouse provides a number of advantages, the top 3 being: 1) Getting users out of the operational systems, 2) Providing a more efficient mechanizm for handling large sets of data and big queries, and 3) Providing a more user friendly layout of the underlying data structures.

[KHorseman]

@steveo250kyeah you're pretty much reiterating what I was saying. Power BI is not a data warehouse but it has some ETL capabilities that allow you to fake it to a certain extent. I still don't have a data warehouse. In fact the one small limited use warehouse I was formerly using  for part of my data has been abandoned at this point. I just go to the source database and do my own ETL now. But at least I've gotten my boss to agree that we need a data warehouse. Unfortunately I am not a department so it will be a while until I have time to build one.

[ThomVF]

When you build a Data Warehouse (loading source/operational data with ETL), you model it in a way that reflects the true relationships of the data objects to each other.  You can do this in either normalized or dimensional fashion (2 main schools of thought).  And you would typically include data at its most granular level possible - this way, you can present it at any level of summarization you like.  A Data Warehouse with only summarized data is of little value, unless that's all your consumers want. Ever.

You can also add historical tracking into the Data Warehouse model, relieving the source systems of that burden.  But what if the source overwrites everything and doesn't track objects historically?  It must be manufactured.  You may need ETL for that.

PowerBI can then be used to smartly present this data any way the consumer wants.  You can also service multiple consumer types, requiring data at different levels of granularity:  a manager wants to perform detailed analysis of current data every day, while an executive wants high-level, summarized data on a weekly basis with an historical perspective.

Your business process could change - if this happens, you could adjust or build new PowerBI applications to change with it, leaving your Data Warehouse intact (remember, the Warehouse is designed based on the data object relationships, not necessarily the business process).

In my current case, I have a Data Warehouse that sources from Salesforce but also merges in 8 years of history from Excel.  I use SSIS to feed my SQL Server Data Warehouse daily.  And I have a view layer on top of the Data Warehouse, which lets me abstract and transform the original data in support of my PowerBI presentation layer.  When I change the View layer, it can affect all PowerBI reports, saving me maintenance time (most views are shared across all PowerBI reports).   But if I have a specific change, affecting only one PowerBI report, I can do that as well.

This architecture adds complexity (and cost) but if your client has advanced data analysis needs, it provides the most flexibility and power.

I will add that because of PowerBI's ability to transform data, I find myself doing less of it in the Data Warehouse and ETL pipeline.  I just need to carefully decide which transformations I want to "institutionalize" (put in the DW/ETL) versus "localize" (put in PowerBI).

Another thing: since my primary source data is Salesforce, I use the ETL to perform cleansing and conforming of the data as it goes into the Data Warehouse - because we all know that unless the Salesforce application is tight, the data can be very inconsistent and dirty.  I would not want to try doing all that with PowerBI.

So, if you have a single, moderately-sized, fairly clean data source and only a couple BI reports, a Data Warehouse might be overkill.  But if you have multiple sources, some with dirty data, and need to manufacture history along the way, I would suggest the Data Warehouse approach.

[ammartino44]

@KHorseman

How do you get your data to manipulate in power bi? SQL? What is import vs. direct query? Thanks. 

[v-qiuyu-msft · Accepted solution]

Hi @ammartino44,

Power BI is cloud-based business analytics service that enables anyone to visualize and analyze data. In my opinion, the ultimate goal of Power BI is to visualize data. It's not a full replacement of data warehouse. But Power BI desktop also provide features for us to model and shape data:

Modeling

Power BI for Data Modelling

By the way, the big difference between DirectQuery and Import mode is no data is imported or copied into Power BI Desktop in DirectQuery mode. For more information, please refer to this article: Use DirectQuery in Power BI Desktop.

Best Regards,
Qiuyun Yu

[Greg_Deckler]

Remember that you can create an Organizational Content pack and manage everything kind of centrally. But, I agree, Power BI is not a substitute for a real data warehouse but it is quite surprising what it is capable of and it has a lot of the same base functionality and is built on top of some of the same technologies.

[KHorseman]

At this point I'd almost rather build a data warehouse with DAX than SQL code...

[Sean]

---

KHorseman wrote: the time is fast approaching where I'm going to stop suggesting and start demanding

[ankitpatira]

@ammartino44 You shouldn't compare power bi and data warehouse. Data warehouse is an enterprise need that will store current and historical data for the enterprise while power bi is a visualisation tool. You use power bi for visualising, analysing your data and share it with business users.

[ammartino44]

@ankitpatira. We use Pentaho (have you heard of it?) I believe it is the tool ON TOP of our data warehouse, but it is not very good in terms of analysis and vizualisation. Would you ever put power BI on top of the data warehouse instead? Not really sure how that works.....Also, everyone touts the memory of power pivot and power bi, but almost everything that is output from a data warehouse is at the summary level, so the need for visualization tools is somewhat muted. 

[ankitpatira · Accepted solution]

@ammartino44 we're doing exactly that using power bi on top of data warehouse. Good thing is power bi has ability of DirectQuery feature which means power bi connects live to your data source and doesn't import any data from the warehouse as data warehouse has millions of rows of records. I would suggest you use DQ feature of power bi and connect straight to data warehouse and that would also eliminate your problem of memory issues.