Explain BI to me, an advanced PivotTable user
June 5, 2020 1:35 PM   Subscribe

I'm trying to learn more about business intelligence tools like Power BI or Tableau. I get that you can use these tools to explore datasets and build nice dashboards. But, everything I've seen in examples is also possible in Excel with pivot tables/charts, data models, and slicers. What am I missing?

I'm pretty familiar with Excel's new abilities around data models, the ability to bring in and clean data with PowerQuery, and then to do sophisticated analysis with PowerPivot, adding in measures and calculated items. Add in some charts with good slicers, and it seems like you can do the same kind of exploration that I've seen in demos of Power BI or Tableau. What am I missing from the capabilities of BI tools?
posted by philosophygeek to Computers & Internet (9 answers total) 13 users marked this as a favorite
Best answer: In my experience, Power BI and Tableau allow multiple users to easily create custom filters and dashboards, without disturbing the underlying data. Also, they both can be set up to get live data feeds at whatever frequency makes sense (daily, hourly, etc). All of this is from experience of corporate installations. This enhances collaboration, and allows people to use the data in ways meaningful to them.
The biggest difference is data integrity and security though - they require licenses, and sign-in, and privileges are controlled. Excel (and it's cousin - Access) have barely any controls on data integrity, unless the sheet is locked, but that then locks out other users from extracting data useful to them.
posted by dbmcd at 1:42 PM on June 5, 2020 [2 favorites]

Best answer: Additionally, the quantity of data that is the focus of Power BI or Tableau is more on the order of millions or billions of rows, and multiple tables. The joins between tables that is under the hood for visualization software are (hopefully) created by a person with an understanding of database models in general, and that specific database in particular... rather than somebody who doesn't have that knowledge.

Additionally, the use of things like "dashboards" allows a collection of reports to be built, and while some adjustments are possible, the user doesn't have to build the question from scratch each time they want to answer it. They can "just" change the date, or the customer number.

Of course, if you don't know how to find the query, and can't check it for correctness, you have no way of knowing if the visualization is correct.
posted by bilabial at 2:12 PM on June 5, 2020 [1 favorite]

BI tools offer speed and interactivity to the experience of working with data. It’s a game changer for exploring and blending datasets across different systems.
posted by oceanjesse at 3:04 PM on June 5, 2020

Best answer: yep +1 to everyone who said that sharing the development and dissemination of an analysis across an org is the big reason

I'm a data scientist, my current org uses Looker. The main reasons are:

1) we have a mix of skill across the org; I can write SQL to do complex pivots in my sleep but ask me to do it in Excel and I'm lost for anything more than say, three variables and a non-complex operation like a sum. There are others who are the opposite of me - Excel wizards who can't code. I also do most stuff like that in Python in my own work, but I can do the SQL stuff in Looker and define a complex aggregation, share that with another person who doesn't have to know SQL, let them build a dashboard and a scheduled report on top of it with a drag and drop interface. we could never share this work effectively with Google spreadsheets or something

2) we don't have huge data but we have solidly outgrown spreadsheets which are only a solution for the smallest organizations. we have event data, thousands of people visit our site daily, it's too much when you need to look at it at many levels of granularity all at once

3) we desperately need single source of truth. there is some complex logic on how our raw revenue is rolled up, for instance, and it's both more efficient and safer to let people consume the view on the same data that is at the proper level of aggregation for their needs

4) modern tech stack integration; Excel is a separate piece of software from any database/data store even if you can kinda sort connect to stuff from inside it, but Looker lives directly on top of our database and calls the raw data as needed, and it tries to be sensible about caching and it also has the idea of version control/git integration built in which is a must in modern tech workplaces - I can experiment on a safe copy of someone else's report and not freak out the CEO while I do so, and not push my changes until I know they are good. every other BI tool out there at least tries to do some of this with varying degrees of success and all of them are at least a minimal improvement on Excel that does close to none of this, especially the version control part
posted by slow graffiti at 3:46 PM on June 5, 2020 [8 favorites]

You are probably in the top 1% of Excel users and it takes a You to make Excel work the way you want it. Tableau or Looker could accomplish this with someone not nearly as skilled as you are.

Excel maxes out at what, like a million rows nowadays? That is nothing to Tableau or Looker. A light stroll. A walk in the park.

If you want to build a dashboard in Excel, it might take you a bit but it would look nice. But keeping that excel sheet fed with data is a chore. With Tableau or Looker, you could make a 10x sexier dashboard and it would be integrated into the data lifecycle (maybe real time, maybe ETL'd at some interval - but still infinitely less in the way than Excel). Plus instead of going to OneDrive or sharepoint or whatnot, your execs are going to a website, something they do constantly all the time.

Excel is great. There are almost no limits to what it can do. But Looker and Tableau and their ilk are built for big data and for building dashboards and reports. Excel has evolved to sorta do that because Excel has evolved to sorta do anything...
posted by cmm at 4:43 PM on June 5, 2020 [1 favorite]

Enterprise DBA here. We use Power BI to present dashboards and metrics to non-technical/exec users. All kinds of sexy graphs and infographics, web presentation, clicky drill downs, cross-report correlations. We like it a lot. Disconnects the viewer from the raw data, makes the information super obvious/easy to grok (depending on your design decisions, of course - anyone can make a bad graph!) and you can add multiple data sources (couple excel books, database, custom data tables etc) and seamlessly update on schedule or on demand.

We used to use Excel but simply could not get the polish or detail or intuitive behaviours we wanted, and having to jam in ALL THE DATA was kind of a mare.

I dislike Tableau very much. I frequently have to deal with the fallout from horrible Tableau queries knackering the back end db*. It provides a similar look/feel to PowerBI though from the consumer POV, and similar benefits.

(*Fairness compels me to add - no database is gonna cope well with inexpert adhoc user-created queries over million row datasets; the problem is with allowing them, not solely with the bad code Tableau generates. However, the scheduled monthlies are also bad, so. YMMV.)
posted by Ilira at 3:08 AM on June 6, 2020

Best answer: Much of what Tableau can do cannot be done in excel, just in terms of displays and dynamics. But the biggest advantage is that I can produce a dashboard with filters and parameters and I can publish it to our server where hundreds of people can log on and interact with the data and make discoveries for themselves. There is data security, meaning a middle school teacher only sees students data from her middle school. And there is deep interactivity. It's also fast. I can create a multi-axis display of a billion rows of data, viewed from five different angles, with multiple filters that can be changed by the end user, in a few minutes.

Excel can't match tableau on those things. But I still open excel quite a bit because it does some things very well.
posted by crapples at 5:57 AM on June 6, 2020

In my experience, users like the PowerBI clicky-clicky interface more than anything useful. PBI is pretty slick, I admit. But you have to really understand the numbers to make it more than a fancy PowerPoint.
posted by SPrintF at 1:18 PM on June 6, 2020

Best answer: ...because it's the future: Excel's mode is non-networked, unmanaged data sources and critical to the way you (singular) do your job, but BI tools are networked, allow access control/data integrity and is critical to moving data around a business so that many people can do their jobs. (Excel files have been better recently but are still a single point of failure where a BI system done right will have version control on its files as well as centralised backups.)

A central repository of dashboards and report templates allows you to peer-review colleagues' work, or take it and extend it, saving you time. Live-updated and publicly-visible dashboards cut down the feedback loop for business decision-making which avoids you spending time and not making money from doing the wrong things. Telemetry (in near-real time) and data-driven decision making become a reasonable option when you're not spending 50% of your time fighting the data to make the case -- you get to make small steps with verifiable hypotheses on short timescales/low opportunity cost.
posted by k3ninho at 2:14 AM on June 7, 2020

« Older Where can I download historical stock prices?   |   Did guards at the Hermitage give empty-frame tours... Newer »
This thread is closed to new comments.