Power BI vs Data Warehouse

[pbix]

Hi All, 

I was reading this great community thread on the pros/cons of a data warehouse vs Power BI. 

http://community.powerbi.com/t5/Desktop/Power-Bi-vs-Data-Warehouse/m-p/78650#M32787

I was wondering about what the latest perspective is on whether Power BI can/should be used as a replacement for a data warehouse?

Our business stores < 5 million rows of data and I'm interested in perspectives on whether similar sized organisations have adopted Power BI as their primary data management tool or whether they/you use it on top of a data warehouse? We're considering investing in a SSAS tabular set-up which seems great for highly normalised coprorate reporting but means we can't tap into some of the visualisation and integration tools available in the Power BI environment. 

Is there a road map, or indication, describing the future relationship between SSAS Tabular and Power BI? 

Thanks

pbix

[djnww]

Power BI/Qlik/Tableau VS Data Warehouse is probably the biggest misconception in the Business Intelligence space. Every few months, a new senior manager will ask this exact question. Let's save a million $$$ a year and stick with Power BI.

We shoud, in fact, be comparing 'Power BI/Qlik/Tableau to SSRS' as all of these products are for designing reports.

As someone else here said, a data warehouse and Power BI complement each other. You mentioned SSAS. Power BI/Qliksense/Tableau are pretty useless on their own in a large organisation. They do not have the capacity to interrogate, load and report on big data.

Instead of a data warehouse, perhaps a data mart may suffice for many, which will be a few databases that work specifically for reporting, but not necessarily have the same firepower as a data warehouse.

Here's the truth... once you give someone a taste of reporting, they will only want more and more as time goes on. Requirements will get more complex. Eventually, a data warehouse will be necessary anyway.

[Phil_Seamark]

Power BI should not be used as a data warehouse.  It works well with lots of data sources, including a data warehouse, but lacks some key functionallity around change tracking and incremental updates.  These are complementary technologies.

[pbix]

Thanks Phil. 

I agree, Power BI worked great with data warehouse, except perhaps for not being able to combine data from the DWH which you might quickly want to mash up with other data and drill through - we still cant drill through to row detail [see records menu] held in SSAS Tabular from Power BI (which you can do if you import data into Power BI).

Additionally with direct query we can query underlying data in SQL Server and build a model in Power BI rather than needing to build the model in SSAS.

At the moment it feels like Power BI development is outpacing SSAS Tabular which is making some SSAS Tabuar functionality redundant.

Do we have a roadmap about how SSAS Tabular will be developed and integrated with Power BI, or will Power BI eventually replace SSAS Tabular?

Thanks!

Pbix

[MattAllington]

As Phil has said, a Data Warehouse and Power BI are complementary.  A Data Warehouse is a storage database, Power BI is a reporting database.  

---

@pbix wrote:

I agree, Power BI worked great with data warehouse, except perhaps for not being able to combine data from the DWH which you might quickly want to mash up with other data and drill through

 I don't understand the point you are making here.  Power BI is explicitly designed to get data from anywhere and mash it up - that's what it does.  

- we still cant drill through to row detail [see records menu] held in SSAS Tabular from Power BI (which you can do if you import data into Power BI).

Yes you can.  SSAS Tabular has an identical reporting engine underneath as Power BI.  If you write the identical database in Power BI and SSAS Tabular you will get identical results.  If you can't drill down to see the row level detail then it is related to your design, not SSAS Tabular

Additionally with direct query we can query underlying data in SQL Server and build a model in Power BI rather than needing to build the model in SSAS.

 Direct Query does not have a data model in Power BI.  You either connect to SQL Server, or SSAS and use the source to determine what you can see, or you build a model in Power BI - you can't do both at once.

At the moment it feels like Power BI development is outpacing SSAS Tabular which is making some SSAS Tabuar functionality redundant.

 As I mentiond above, SSAS Tabular and Power BI use the same underlying reporting engine (Vertipaq/Power Pivot).  There is very little development in this technology in either product.  The development of Power BI is mainly in the area of data import (Power Query) and visualisations.  

What is becoming redundant in SSAS Tabular?

Do we have a roadmap about how SSAS Tabular will be developed and integrated with Power BI, or will Power BI eventually replace SSAS Tabular?

---

 SSAS Tabular is already integrated with Power BI.  No Power BI will not replace SSAS Tabular.  SSAS Tabular is only 1/3 of what Power BI is.  Power BI has a reporting database (Power Pivot), an ETL tool (Power Query) and a visualisation/reporting tool (Power BI Visuals).  SSAS Tabular is just the first one (effectively Power Pivot for Enterprise).

[jdt008]

Thank you for this thoughtful response.  I'm more of a lay user of Power BI and understand some basic technical explanations.  Can you simplify an answer for me?  We connect to a tabular model and I'm noticing that we cannot use some of the new features, such as the new Quick Calc functions (i.e. % of row, % of column).  Is this because we are connected to a tabular model? Is there any setting we can change to start utilizing these new features?

Thanks

[chienim]

Hi,

In my understanding when you do Direct Query, you literally send a query to your db (tabular in this case) when user access to the contents on Power BI, thus it is not possible you add any calculation further after db returns the data;

If you do Import for your datasets, you store the data you need in the in-memory DB in Power BI cloud which allows you to do the calculations you need for those data, as they are "in" your data model.

So to satisfy your need, simply build another workbook which uses import of your datasets.

Best Regards,

[Phil_Seamark]

Although I suspect the ability to create Power BI custom measures on a direct query data source like SSAS tabular might not be too far away 😉

[Phil_Seamark]

If you change your query mode to "Import data" rather than "direct query" you will have more functionality in Power BI.

[pbix]

Hi Matt, 

Great, thanks for your thoughtful explanation of SSAS vs Power BI - just picking up on your points:

---

@MattAllington wrote:

---

 I don't understand the point you are making here.  Power BI is explicitly designed to get data from anywhere and mash it up - that's what it does.  

---

V much agree - have I missed something though? Once I've connected to SSAS Tabular I can't see how to use Desktop Designer to mash this up with other sources in reports/datasets? We can mash SSAS Tabular data up in the Power BI service though. Great if I'm wrong on this though! 🙂

Yes you can.  SSAS Tabular has an identical reporting engine underneath as Power BI.  If you write the identical database in Power BI and SSAS Tabular you will get identical results.  If you can't drill down to see the row level detail then it is related to your design, not SSAS Tabular

Again, have I missed something basic here? I recognise that Power BI uses the same engine but when I import non-SSAS Tabular data into Power BI and visualise this in a chart I can right click on the chart, select 'see records' and I can then see underlying row-level data. If I direct query from  SSAS Tabular, put Tabular data in a chart then and then right click on it I can't see the 'see records' option any more. Again great if I'm missing something obvious!

---

 As I mentiond above, SSAS Tabular and Power BI use the same underlying reporting engine (Vertipaq/Power Pivot).  There is very little development in this technology in either product.  The development of Power BI is mainly in the area of data import (Power Query) and visualisations.  

What is becoming redundant in SSAS Tabular?

---

Yes, redundant was probably the wrong choice of words as it uses the same engine as Power BI. I suppose I meant that Power BI seems to offer lower barriers of entry to most users than compared with SSAS Tabular. From my novice perspective, it feels like Power BI has a USP whereas SSAS Tabular has a bit of an identity crisis between its older OLAP brother and younger Power BI brother.

No Power BI will not replace SSAS Tabular.  SSAS Tabular is only 1/3 of what Power BI is.  Power BI has a reporting database (Power Pivot), an ETL tool (Power Query) and a visualisation/reporting tool (Power BI Visuals).  SSAS Tabular is just the first one (effectively Power Pivot for Enterprise).

 Great, thanks for summarising this so concisely. 

Thanks for your perspective - really appreciate it. 🙂

Pbix

---

[MattAllington]

---

@pbix wrote:

V much agree - have I missed something though? Once I've connected to SSAS Tabular I can't see how to use Desktop Designer to mash this up with other sources in reports/datasets? We can mash SSAS Tabular data up in the Power BI service though. Great if I'm wrong on this though! 🙂

 Yes, you are confusing SSAS Tabular with a data warehouse. They are not the same thing. You can only have 1 data model and it is either in SSAS tabular or Power BI. You can bring in all the data you want from a data warehouse and mash it up in Power bi, 

Again, have I missed something basic here? I recognise that Power BI uses the same engine but when I import non-SSAS Tabular data into Power BI and visualise this in a chart I can right click on the chart, select 'see records' and I can then see underlying row-level data. If I direct query from  SSAS Tabular, put Tabular data in a chart then and then right click on it I can't see the 'see records' option any more. Again great if I'm missing something obvious!

 what you can see on a chart and what is beneath the numbers are 2 different things. I can create 2 charts that both show the number 4, but one can be made up of 4 rows worth 1 each and the other can be made up of 1 row worth 4.  You could then say the second one doesn't show the detail, but that is not correct. It shows the detail that is available. My assumption is that the 2 scenarios you are describing are not the same thing, otherwise you would Ben able to see the same detail,

Yes, redundant was probably the wrong choice of words as it uses the same engine as Power BI. I suppose I meant that Power BI seems to offer lower barriers of entry to most users than compared with SSAS Tabular. From my novice perspective, it feels like Power BI has a USP whereas SSAS Tabular has a bit of an identity crisis between its older OLAP brother and younger Power BI brother.

 Yes, power bi has a lower cost.  For many companies Power BI is a better choice than SSAS Tabular, but not for all companies. SSAS Tabular is an Enterprise scale tool and will have a long future in my view. Think of it like computers. It used to be impracticable for people to buy a computer for home because all you could buy was an IBM Mainframe for millions of dollars. Then the PC was invented and people started buying them.    A PC has never been a substitute for a mainframe and mainframes are still used widely. 

If something is going to die, it is SSAS Multi Dimensional. It would be very rare that a company would start a new green field SSAS MD project these days. If you already have a large sunk cost then that is different. But if you are starting from scratch I think there would be very very few instances where a company would choose MD over Tabular. 

---

---

[Phil_Seamark]

Hi Matt,

I think there is plenty of life left in SSAS MDX and it has it's place.  I see the data modelling engine as good for small to medium complex models.  Tabular covers the middle ground nicely, but if a large complex model then I'd go MDX every time.  

I'd like to see MS merge the two and offer a mixed mode data modelling environment in SSAS.  Essentially the ability to create a single cube where you can chose Tabular/MDX per measure group.

[pbix]

Hi @Phil_Seamark,

Yes, I mulled this over too. We have several MDX cubes and they're great! It just feels like the majority of Microsoft's new development is towards tabular. MDX has some great features that I can't wait for Tabular to adopt (and vice versa). 🙂

[Phil_Seamark]

I'm not an MS insider so can't speak for sure. But remember that Power BI is SSAS tabular under the covers (all be it a cut down version running in diskless mode). The main difference is that PBI desktop is on a more regular release schedule. So I would expect to see these feature appear in future releases of SSAS tabular. Perhaps SSAS Azure will see them first.