Around a year ago I left Spotify and started using my locally hosted Plex Media Server to feed my daily music consumption. Since I spent much of the last 30+ years building an extensive CD and MP3 collection, the transition was surprisingly smooth, and the excellent Plexamp client app feels like an upgrade over Spotify in many ways.
Now it’s time to start digging into the data that my year of music listening has produced.
I blogged last year about how I report on my Spotify listening history. It’s a bit of a hack because Spotify doesn’t have a database or API you can use, so you need to make a GDPR request and wait a few weeks to get a JSON archive… but I made it work. Plex uses a SQLite database to keep track of its data and metadata, and it’s relatively easy to start using it as a data source for your own Power BI reports. Since this is a largely undocumented transactional database I’m still figuring out some important details and locating important data points, but I wanted to share what I’ve learned.
Here’s how to get started.
Installing Plex, setting up libraries, listening
For this post to be useful to you, you need to run a Plex media server and use it to listen to your music collection. Head over to plex.tv, download the software, install it, set up your libraries, and start listening to the music you love.
This is all well-documented by the fine folks at Plex, so I won’t share any additional details here.
Download a database backup
As mentioned above, Plex uses SQLite as its database, and the Plex web UI provides an option for downloading a copy of the database file. Although it may be possible to report directly on the “live” database, I’m erring on the side of caution and using Power BI to connect to backup files.
Here’s how to download the database:
- Open the Plex web UI at https://app.plex.tv/ and sign in to your server
- In the upper right, click on the “wrench” icon to open the settings
- In the lower right, select “Troubleshooting” in the “Manage” category of the settings options
- On the troubleshooting settings page, click the “Download Database” button
After a few seconds your browser will download a zip file containing a SQLite database file with a long system-generated file name.
Copy this file to folder and give it a more useful name for my project the database file is \Plex DB\plex.db.
SQLite driver and ODBC DSN
Power BI doesn’t have a native SQLite connector, but SQLite does have ODBC drivers. It looks like there are multiple options – I used this one mainly because it was the first one I found.
Once you’ve downloaded and installed the ODBC driver, create a DSN that points to the database file you downloaded earlier. The connection string is just the file path.
With the DSN created, you can start working in Power BI Desktop. Create a new PBIX, select ODBC from the Get Data dialog, and select the DSN.
Click OK and you’re ready to go.
Key Plex database objects
As mentioned earlier, the Plex database is largely undocumented. It has a somewhat normalized schema, which is slowing my exploration and discovery. When a table is mostly integer IDs and obscurely-named values, exploration can feel more like archaeology.
With that said, there are two tables that are probably going to be most useful: metadata_items and metadata_item_views.
The metadata_items table contains one row for each item (track, album, artist, episode, season, show, etc.) in your Plex media library. This table implements an implied three-tier hierarchy by including an id column and a parent_id column that define a relationship between metadata items and the items that contain them.
The metadata_items table also includes vital data such as the title (track name, album name, artist name, etc.) of each item as well as index (track number on the album, episode number in the season, etc.) and such. Not all fields are used for all metadata item types, so you’ll see a lot more nulls than you might like.
The metadata_item_views table contains one row for each time a user has watched a video, listened to a track, and so on. This table includes the titles for the viewed item, as well as the parent and grandparent items, so you can easily get the track, album, and artist without needing to join other tables. You can join with the metadata_items table if you need additional details like the album release year or the record label.
The metadata_item_views table includes a viewed_at column to track when a given item was viewed. This column is stored as a Unix timestamp, so you’ll need to convert it to datetime before you can start working with it. Plex tracks times using UTC, so if you want to work with data in a different time zone you’ll need to handle that in your PBIX.
Refreshing the data
Since your PBIX is connected to a database backup, it won’t automatically show your current library contents and listening history. If you want more current data, just download a new database backup and replace your plex.db (or whatever you named it) file with the new one. The next time you refresh your report, you’ll have new data.
I don’t have a use case for “real time” Plex reporting, so this approach works for me. It’s certainly easier and more timely than the hoops I had to jump through to “refresh” my Spotify data.
I’m still just starting to get my hands dirty with this fun data source, so my next steps are mainly to keep exploring. Using my Spotify listening report as a target, I’m planning to duplicate that report and its insights with Plex data. Ideally I’ll have one report where I can see my legacy listening history in Spotify and my ongoing listening history in Plex… but that might be a few months away.
If you’re using Plex and have been thinking about exploring your data – or if you’re already doing this – I’d love to hear from you in the comments about your experiences.
 To the best of my knowledge after a year or more of occasionally googling to answer my questions, there is no official documentation for the database. The Plex community support forums have a few bits and pieces here and there, but most of what I’ve included in this post is the result of my own exploration. Honestly, it’s a real joy to have a side project with this type of work involved.
 Plex also has great features for managing and streaming TV shows, movies, and photos – it’s not just about music. This blog post is going to focus on the music side of things because that’s what’s most interesting to me today, but there’s a lot more to love in Plex.
 It’s worth noting that there are no “real” FK > PK relationships in the database, or if they are they’re not exposed through the client tools I’ve used. To figure out where the data relationships exist you need to explore the data.