Olivier Travers: Recent Episodes

View Details

Many Power BI developers come from an Excel background and have little to no SQL experience. Yet SQL databases are pervasive data sources and there’s not always a database administrador... Read more »

The post SQL Performance Tuning for Power BI Devs Who Don’t Know Database Architecture first appeared on Olivier Travers.

View Details

My post on which languages to learn as a Power BI developer resonated with many people so I thought I’d take a step back and write about how to navigate the various facets of the business intelligence job market. Where language selection is effectively a tactical tool choice, in this entry we’ll focus on the strategic framing within which you can make career orientation decisions. Both entries are meant to be read together.

A primary concern for any professional is to maintain a relevant skillset throughout their career, and that’s an even more pressing in the tech sector at large, and for data professionals in practicular. I aim to bring clarify to hybrid skillsets and ambiguous job titles that seem to mean different things to different people.

1. Multi-Specialists Working at Intersections Are Vital but Hard to Pin Down – The Case of the SappersAllow me to start with a sidebar on military organization and family history. Feel free to skip this intro but please indulge me, I promise it will make sense.

There’s a combat engineering tradition in my family, as my grandfather and father were Colonels in that Army specialty, and while I shunned the pursuit of a military career, I did ten months of military service in combat engineering and am a Reserve Lieutenant.

76th Bataillon Génie Légion (BGL)My granddad built an airport in a rice paddy with the Foreign Legion during the Indochina war in 1952, and my dad made tanks cross the Rhine river during Cold War training maneuvers, back when France had troops stationed in Speyer Am Rhein in what was known as West Germany at the time. I didn’t do any active-duty work, but I did learn how to blow up a bridge! I grew up hearing about military matters – you can’t escape it when you’re an Army brat – and that left an impression.

The captain who taught me during my military service served within Foreign Legion troops deployed in Lebanon in the early 80s and had an interesting perspective on doctrinal purity vs. practical effectiveness. Here’s what one of his NCOs reportedly used to say about how to weight explosives to blow up a bridge by the size of the truck transporting them:

“Small bridge, small truck

Big bridge, big truck”

Smartass NCO – inefficient maybe, but effective! Possibly apocryphal, still, I’ll always remember it. In the end, do the job!

Wait, what’s the deal with blowing stuff up, you might ask? The purpose of combat engineering is to clear the way for your troops and obstruct the way for the enemy, in other words, to provide and deny mobility. That entails a wide variety of specialty skills such as:

• Crossing rivers with mobile bridges and destroying constructed bridges
• Clearing and creating obstacles, including laying and removing landmines
• Building and destroying base infrastructure

AEV 3 Kodiak by Rheinmetall Defence
They might as well have named it
the Armored PlatypusCombat engineers are thus the first to go to a contested area as you don’t want to send infantry and cavalry through a landmine field. And they’re the last to leave as they clean things up, sometimes for years after a conflict is over to dig out unexploded ordnance and mines. There are even subspecialties involving trains or air force support.

A Combat Engineer is more of a technician than most other ground troops but is still a soldier as he might well end up in combat situations. Depending on the country and era you may find pioneers either in their own units – France for one used to have a bunch of Génie regiments – or more commonly embedded within larger Army or Air Force units. But they’re odd ducks that are hard to pin down, as is reflected by their weird vehicles that are literally hybrid tank/crane/dozers and boat/bridges.

So what’s the relevance of this long-winded intro? What to make of sappers within the military sounds similar to asking whether BI should be within IT or report to the CFO or be embedded withing various divisions (sales BI, HR BI, marketing BI etc.). My take is that here’s no right answer, it’s entirely contextual to the organization’s industry, size, and culture. Business intelligence is the combat engineering of the corporate world, it’s a hybrid job meant to solve problems for other people. A sapper is a soldier and an engineer. A BI professional is a business analyst and an IT technician.

However, there are infrastructure-focused sappers (more engineer than soldier, like my grandfather was) and combat-focused sappers (the opposite, like my father was), just like there are IT-first and business-first BI jobs. Does it really matter? To transition to our civilian concerns, I would fret less about who is nominally my boss, and more about whether they understand what my job is about and give me the right resources and support. But as a BI professional we’re about to see that you do want to determine whether your primary focus is on the engineering side or the business side, it’s unlikely to ever be a perfect 50-50 balance.

Like a data pipeline, but with vehicles across fast waters. I’ve done this, it’s fun!2. Picking Primary and Supporting Skills with IntentThis Palazard build is not quite the OP result I was expecting (kind of like this AI drawing)Let’s move on to another analogy where combat is now waged virtually and for fun. Again, bear with me, this is all relevant. Many role-playing games and other game archetypes using character classes offer the player the ability to combine two or more classes, leading countless kids to fantasize about building the ultimate paladin wizard who can prevail with brains and brawn alike. But because skill points are limited, this is more often than not the best way to gimp your character by making it a master of none, underpowered across the board jack of all trades.

However, well designed games do offer ways to create viable multi-talented character “builds”, usually through a combination of skill and equipment choices, provided the player shows restraint and acumen in picking complementary skills and combining them with just the right balance and optimal synergies. This can be very rewarding and fun when skill combos unlock big rewards. In the real world, you’ll see this in people picking smart combinations of university major and minor.

Dual classing can work as long as you plan your build coherentlyIn my entry on BI languages, I argued that hyper specialization had limited appeal, significant drawbacks, and a lack of real-world relevance. So I do think that BI career tracks will require you to multi class almost by design. But that doesn’t mean you can just spread around your skill points randomly – putting two points in Python, one in color theory, five in financial concepts, four in data modeling, and one in internet security, just because they sound cool.

Instead, it’s best to build up your skillset with intent and alignment with your purpose, along these dimensions:

• Learning order: some skills are fundamental and underpin everything else you’ll be doing, so they need to be acquired first. If you don’t know how to gather requirements or model data, you’ll be stuck to being a glorified data entry clerk.
• Internal coherence: some skills need to be combined for best effect. If you don’t understand data modeling, it’s hard to make the right ETL choices.
• External alignment: what’s your role within your team? Are you tanking on the front line or healing from a distance? If you’re primarily supporting other analysts by massaging data in the warehouse, you probably don’t need to go deep into visual design skills.
• Versatility: are you always solving the same kind of issues in the same settings, or do you frequently need to jump into different roles? How much of a Swiss Army knife you need to be depends on whether you work in one company or as a consultant, and how big your team and organizations are. Even a pure damage dealing class might be on crowd control duty once in a while, and even within damage dealing, sometimes you do it on a single target, sometimes on an entire area, sometimes you’re shooting at a static target, sometimes it’s moving… you won’t get away for long knowing only how to do a single narrow thing.

I can’t harp about this enough, these decisions are all about context, don’t seek absolute answers.

1. How to Choose Your Major: Dealing with People, Processes, Tools, and CultureOrganizations operate based on a combination of people, processes, and tools. Which one of those three dimensions you’re most comfortable with should help you determine what type of BI professional you’d like to be:

I bring you…
The Single Version of the Truth!
(audible gasp)If you’re comfortable dealing with the ambiguity, mood swings, occasional politics, but also the energy and creativity that flows from interacting with other humans, then you’re more likely to succeed on the business side, from gathering requirements to building visualizations to coaching other people. I call that role “CFO whisperer.”

At your best you may know better than your clients/users what it is they need, which is a very rewarding feeling when things just click. It’s even more rewarding when users realize this happened and credit you for it! One of my clients recently told me verbatim that I was able to articulate better than him his own use case for the purpose of hiring and briefing a developer. That’s because being bilingual business/tech puts you in the critical role of interpreter, in the absence of which people from different departments often talk past each other.

You may also in time become an executive yourself – VP of sales, CFO, CEO etc. – with a strong minor in data crunching, like many are.

Behold the power of ProcessMan!If you like processes and organization, then the project management, governance, and administrative side may be a better fit. Not necessarily the most fun part, but the BI trains do need to run on time. The reality of IT projects is that they are just looking for an excuse to fail. Goalkeeping is an important part of avoiding failure, if not guaranteeing success.

If tools and software are your favorite thing, then data engineering and other back-end duties such as data security will appeal to you. This may or may not involve actual software development, as no/low-code data tools are pretty pervasive, especially if you operate in a Microsoft shop. The “Modern Data Stack” favored by many start-ups tend to be more code-heavy and closer to software engineering DevOps practices with source control, Continuous Integration/Deployment, and so forth.

All that being said, you can’t max out in a single dimension and expect to succeed. Organizations have smartened up to the fact that brilliant assholes might be spectacular individual contributors but so toxic to the team and culture that one shouldn’t put up with their drama. So you can’t be a 10 in tech and/or process and a 0 in human. On the other hand, if you’re maxing out on empathy and soft skills without any technical knowledge or any regard for process, there might be roles where you can succeed, but BI is definitely not it.

Impatient dragon dealing with millennials and their feelings
Source: The Magicians S02E11I do find that younger generations tend to need to be coddled a bit too much, and many HR departments have been indulging them, with the media-driven obsession of accommodating Millennials and their successors. There’s such a thing as being too soft and self-centered, what with that tendency of portraying every first-world minor inconvenience as a “mental health” issue of high importance. Maybe read about the life of people enduring wars and spend less time navel gazing?

But we shouldn’t be longing for the gung-ho, hyper-aggressive culture that companies like Microsoft or Oracle had back in the 90s. Can you imagine company meetings where high-level execs ask you to “nail the coffin” on a competitor? Healthy organizations should aim for a balance between performance and empathic concern for genuine human needs. You can be driven without steamrolling your colleagues!

They spend a lot of time discussing subjectivity in this podcast, which is a good hint that the People dimension cannot be neglected4. Core Business Intelligence RolesWhat you think your grandma thinks you’re doing
“Computer, enhance!”Most BI projects follow a similar structure that starts with defining what the business end goal is, mapping how to get there in light of data sources and software used by the organization, getting and shaping said data, turning it into visualizations, deploying reports to end users, and most likely iterating through these steps as a series of cycles. This thus leads to roles that may or may not be handled by separate individuals depending on project size and complexity:

• Business analyst
• Software architect
• Data engineer
• Report designer
• Administrator
• Project manager

Job titles vary and new phrasings emerge all the time, such as the “analytics engineer” title mostly pushed by dbt. Industry participants love to fret on Reddit and the likes about what these semantic tweaks may mean, which can sometimes be useful but is too often existentialist thumb twiddling.

Greg Deckler covers these roles at length as well as how to approach BI career development in his Learn Power BI [Amazon affiliate link] book. I’ll give my own take below.

4.1. Business Analysis: The Key to the WhyHow do I tell them the discount rate in their DCF is wrong?In this category you’ll find a variety of roles and titles including data analyst, BI analyst, business analyst, or even functional consultant. Whether you’re applying to a full-time job or a consulting engagement, it’s important to dig below the title to find out what the recruiting organization really means: what are you expected to produce, who are your (internal) customers, how will you be supported, and what you’ll be evaluated on. Maybe the focus is upstream on gathering and clarifying requirements, which is a function necessary in any technology project, BI or not. Or maybe you’re building reports all day long based on someone else’s requirements.

In any case to be effective in business-first positions, you need to develop functional and industry domain knowledge. There’s not a single BI project I’ve delivered where I haven’t used my background in business operations and my business school education. You’re not supposed to be as deeply knowledgeable as, say, a CFO with 20 years of experience in a very specific industry, but you need to know how to ask the right questions and drink from the firehose to quickly know not just what your users want you to build, but more importantly why they need it and how they’re going to use it to conduct business.

That way you’ll even be able to deliver more than what they say they want, but what they actually need. If you’re working with commodity traders you may need to understand what contango and backwardation mean, while a CFO might ask you to calculate Days Sales Outstanding, just to name two random examples. In some cases you’ll actually be the one explaining KPIs to your users. You’d be surprised how many small business owners get confused by the difference between markup and gross margin.

Always Be Closing, or No Coffee, OK?
Source: the amazing Glengarry Glen RossYou can be more valuable and successful in this area by developing your knowledge of systems of record in software categories such as ERP, CRM, or project management, because this is where most of your data will be coming from. Understanding the key entities, workflows, and processes where all this data is coming from will help you both to define metrics and pursue the inevitable data hygiene initiatives that will need to address data quality issues you’ll no doubt run into. Building a report out of, say, Salesforce, means you’ll have to understand how data flows from Leads to Accounts and Contacts to Proposals to Quotes to Deals. And be practical, don’t stay in the abstract. I always recommend that analysts have at least some exposure to the user interface of key source software.

4.2. Data Engineering: The Essential Plumbing That Consumes Most of the ResourcesHarry Tuttle, Data Engineer at your service
Source: Brazil, one of the best movies everData engineering is in charge or extracting, transforming, and loading data from sources to destinations (aka sinks), via patterns commonly known as:

• ETL (Extract, Transform, Load) – the data schema is established on write as you load data in its destination after transforming it. This is typically associated with data warehouses and implies a serious data modeling effort.
• EL(T) – the data schema is established on read, meaning you load the data as is and transform it later, which is associated with data lakes and lakehouses. Data modeling may or may not be performed at some later stage.
• Batch vs. Streaming – toolsets for discrete ingestion after the fact in batch mode vs. continuous streams are different for the most part, so handling data streams is effectively a skill subset in its own right.

You may also hear about “data wrangling” or “data shaping”, they’re all pretty much synonyms. Functionally it doesn’t matter whether you’re setting up a data pipeline by clicking on a GUI or writing code, as long as you eventually land data where you need it to be, in the state you need it to be. But some people call the former “ETL developer”, which they’d use for someone who uses GUI tools such as Alteryx, Talend, Pentaho, or ADF, while they’d reserve the term “data engineer” to people who manually write code.

While it’s currently pervasive, the phrase “data engineering” is actually fairly recent and is taking inspiration from modern software engineering. It is thus leaning towards the code side, as well as modern DevOps practices such as using source control and automated testing for Continuous Integration/Continuous Delivery (CI/CD).

Seems Dall-E thinks people are vampires?To coordinate discrete ETL tasks you’ll find the concept of orchestration, as data pipelines often need to be executed in a sequence of dependencies. A senior data engineer lives in that realm, with a good sense of the entire lineage of data across pipelines and all the dependencies and potential failure points along the way.

Real world data is never 100% clean, thorough, up-to-date, free of ambiguity, or ready to be integrated with other sources. Data engineering is thus an essential skill without which only the most trivial of BI projects can be delivered. It commands some of the highest salaries in the BI space, is stubbornly resistant to full automation, and will probably never go completely out of style. In my opinion you won’t go very far in BI without having a modicum of data engineering skills and proficiency with at least one GUI tool and/or ETL language. You might table orchestration concerns until a few years into your career, but any project that involves more than a couple of sources will likely require going beyond simple, isolated ETL tasks.

A practical perspective needs to be informed both by points of view coming from inside and outside of Microsoft4.3. Data Modeling: Giving Data Its Analysis-Ready Shape and MeaningData Modeling is closely ties to data engineering, as it determines the shape of, and relationships between, your data tables. People have been going back and forth for 30 years between enterprise data warehouse architectures from the like of Bill Inmon or Ralph Kimball versus using wider tables that mix facts and dimensional attributes. Taking that latter approach needs to be a conscious decision, meaning that you need to understand modeling even if you’re not going to do much of it.

Not this kind of star modeling
Source: ZoolanderLately even people using Tableau – a tool that historically was all about joining everything in wide “frankentables” – have been warming up to dimensional modeling, the approach that is universally recommended in the Power BI world. Does everyone need to know the subtle distinctions between types of Slowly Changing Dimensions or how to manage surrogate keys? Maybe not. Or do you need to know how to set up OLAP cubes? Again, it depends on your environment, but precomputed cubes are no longer the must-have tech layer they used to be. On the other hand, I’d venture that you can’t call yourself a BI professional if you don’t at least know star schema basics.

Don’t lose sight of what data modeling is ultimately about: whatever the shape and structure of your collection of tables, you’ll need to calculate the metrics required by your clients and guarantee they’re accurate. Domain knowledge and integration concerns are not going anywhere, regardless of technical implementation choices.

Some organizations create a distinction between data engineers importing data from outside sources into the analytical stack while analytics engineers to subsequent transformations within the analytical realm, typically to create “gold” datasets ready for analysis and visualization. In this case, the former role is more focused on orchestration and automation, while the latter would typically need to align data with business requirements via data modeling. Again, your mileage may vary in terms of who does what and what their job title is.

If you’re a leader, then all the skill mapping we’ve been talking about at the individual level applies to your team composition and dynamics.

4.4. Visualization: A Minor That Deserves Respect, But It’s Not “The Job”Ohhh, shiny!People who don’t know data might think BI is mostly a visual design job, as if you were drawing shapes in Photoshop or PowerPoint. Software vendors contributed to that misunderstanding because they want everything to sound braindead easy. BI professionals obviously know better: the visual part is just the top of the iceberg.

Don’t get me wrong, we do need to pay attention to visual design, from gestalt principles to color theory, white space, composition, and more. I do see many reports out there that are, quite frankly, very poorly designed and deliver such a dismal user experience that nobody uses them. And beyond the strictly visual part, paying due attention to accessibility concerns is also part of the job.

On the other hand, don’t let contests organized by vendors fool you. The purely visual part is 10% of the job, nobody is a “BI report designer” who can get away with never doing any ETL, modeling, or admin. In the corporate world, people don’t have time to engage with fancy visuals that require a long explanation, and if you over-visualize it, they’ll be quick to ask for plain tables, or worse, an Excel data dump.

4.5. Administration, Governance, Adoption, People Leadership: Making Sure It SticksGovernance, a four-letter word?And now the fun part: dealing with people to get them to use your BI solutions, and do so responsibly. At a junior level you might be stuck to doing administrative tasks like managing secure access and users. As you grow more senior, non-technical BI roles shift to handling organizational and even cultural concerns. Your organization should not neglect these responsibilities if they don’t want to leak confidential data, end up with an unmanageable hell, or a BI desert that nobody cares about. As a BI professional, if you don’t bear these responsibilities yourself, you’d better know who does in your organization and work closely with them, otherwise your pristine data pipelines and brilliant dashboards won’t see much production use.

More broadly, a few years into their careers, most people will face a fork where they have to choose whether they want to keep working as an individual contributor or start managing teams of other people. Excelling as a data engineer doesn’t mean you’ll be good at managing a data engineering team, let alone an entire analytics department, just like being a quota-shattering sales rep doesn’t mean you’ll enjoy or perform as a VP of Sales.

There’s no right answer here. Go back to your self-assessment – hopefully coherent with how others perceive you – of where you fit in the people-tools-processes triad. Avoid pursuing a path that sounds more prestigious if it’s going to make you feel miserable. There are also different ways to work with other people as a senior. One path is hierarchical, where you’re responsible for hiring, firing, evaluating and so forth. Another path doesn’t have these clear responsibilities and instead is more focused on elevating other people’s skills through mentoring, which can be very rewarding in its own right.

4.6. Architecture: Putting Everything Together (Indoor Sunglasses Optional)Totally Realistic Rendering of Real Person Doing Actual JobAll these moving parts are necessary, but someone needs to make them fit together. This job is commonly known as Software or Systems Architect, a fairly senior role that combines good technical fundamentals across the OSI model with understanding of human organization and dynamics.

The Architect gives everyone a common sense of direction while making sure that software components complement each other well without leaving big functional gaps or obviously failing to meet functional requirements. That latter part is hard, especially in terms of scaling, as the proof is in the pudding.

If you like to go back and forth between the strategic big picture and tactical implementation details and have a fetish for flowcharts, then this might be the job for you. Did I mention I love flowcharts?

4.7. What About Big Data, Data Science, Machine Learning, Artificial Intelligence?In the ~~future~~ present, AI will generate drawings mocking human stupidityI’ll make this short as I’m irritated by the confusion fueled by clickbait media, clueless HR departments, and grifting bootcamps, colluding to give the impression that somehow everyone is going to make six figures overnight working from home by becoming a “data scientist” working with “big data”, whatever that means. The “data scientist” title was created to make a data analysis job sound more appealing! The truth is, technology has always been subject to hype/bust cycles, so always look at the breathless pronouncements with a grain of salt, at least in the short term.

Is there a place to develop machine learning algorithms and apply statistical methods to predict the future by using humongous amounts of data? Sure. Is that place your department? Probably not. Most companies, even those selling all these fancy new technologies, are still struggling with more prosaic business intelligence challenges. I’m very much in the “walk before you run” camp. Moreover, BI and DS/ML are adjacent and complementary, they’re not the same.

Down with ‘data science’Coming Soon – Axes of Growth: How to Plan & Structure Your CareerAnything or anyone that stagnates will sure enough start to shrivel and die. To keep your work trajectory on a healthy track, you need to ask yourself whether you keep learning things that will remain relevant, sustain your interest, and hopefully, get you more money as well. If you’re “quiet quitting”, you’re not being smart, you’re quietly quitting on future you.

Stay tuned for a follow-up entry that will cover things to look for in your current or next job that will serve as building blocks for your evolution further down the road.

Related Reading* Which Languages Should You Learn as a Power BI Developer? * Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity

The post Business Intelligence Job Profiles – How to Play to Your Strengths, Find Your Niche, and Build Your Career first appeared on Olivier Travers.

View Details

Pre-computing aggregates from granular data to improve live performance for end users by using computing resources at scheduled times is a well-established software pattern. In this entry we’ll look at how to set up aggregations across Microsoft business intelligence products. This functionality breaks the pattern of how most features have flown through the genealogy of Microsoft’s BI offering, so I thought it would be worth clarifying which one does what.

Most people reading this entry probably use Power BI, but performing a bit of software archeology to know how we got to the present state is always enlightening. The point of this entry is not to restate what’s already in the documentation, which I’ll point to repeatedly. Rather, this is an overview to help you navigate your options.

1. A Very Quick Intro to BI AggregationsWhen OLAP cubes emerged at the end of the 90s as a performant pattern to manage multidimensional analysis of facts and measures, it became apparent that these systems were performing poorly when they had to do live queries at the highest grain from “tall” fact tables with many rows. One of the main solutions that emerged was to precompute and cache aggregates for cube cells at the (lower) grain of the cube’s dimensional leaves, vastly reducing the number of rows in the process. A typical example is to summarize transactions at the monthly or yearly level. This is well explained in further detail in Aggregations and Aggregation Designs. This approach is also described as a roll-up and is not unique to Microsoft, you’ll see it in other products such as Oracle BI Server.

Aggregations can be built on top of each other, meaning that a yearly aggregation can be easily calculated off a monthly one, instead of crunching the high grain fact table all over again. They’re hidden from end users since they’re a backend performing tuning tool that’s not meant for human consumption.

Just to rule out any misunderstanding, “aggregate functions” in tabular models have nothing to do with aggregations and refer instead to the “aggregate function to be used by reporting tools to summarize column values”, i.e. these are the summarizations you can choose in Power BI Desktop for implicit measures.

1. Aggregations in SQL Server Analysis Services MultidimensionalMany Power BI developers coming from the business side don’t know about the product’s roots and predecessors, but it’s to their loss as enterprise BI patterns haven’t changed that much over the past 20+ years. When Microsoft comes up with an excited announcement that Power BI has a “new” feature, especially on the Premium side, take it with a grain of salt as they may mean “new in Power BI” as opposed to “completely novel”. Case in point, aggregations had been in SSAS for a long time already by the time they were added to Power BI.

SSAS MD’s aggregations are tightly coupled to that platform’s underlying OLAP cubes. They can be set up using two tools:

• The Aggregation Design Wizard for the initial manual design
• The Usage Based Optimization Wizard for automated resource allocation based on query logs

Aggregation design requires the developer to find the sweet spot where the resources consumed by aggregation computation and storage are not out of line with the performance gains:

“The goal is to design the optimal number of aggregations. This number should not only provide satisfactory response time, but also prevent excessive partition size. A greater number of aggregations produces faster response times but it also requires more storage space and may take longer to compute. Moreover, as the wizard designs more and more aggregations, earlier aggregations produce considerably larger performance gains than later aggregations.”

Designing Aggregations

There’s quite a lot going on here, including re-usable “aggregation designs”, the interaction of aggregations with partitions, and other advanced considerations beyond the scope of this overview.

The Wizard of Aggz.3. Aggregations in SSAS/AAS TabularThis section is going to be very short: aggregations do not exist in SSAS/AAS tabular models! This is a surprising break from the usual enterprise BI feature inheritance at Microsoft, which usually starts with SSAS MD, then has an equivalent in SSAS tabular, then makes its way into AAS and eventually into Power BI Premium. There are also some features that only exist in SSAS MD, but then they don’t exist anywhere else (e.g. there’s simply no writeback in tabular products). And there are some features that exist only in Power BI Premium. So this AS Tabular gap is a bit unusual.

Later in this entry we’ll talk about a programmatic option that can also be used for AS tabular models.

1. Aggregations in Power BIManual aggregations were introduced in September 2018 and went GA in July 2019. Automated aggregations followed in Public Preview in August 2021 and went GA in May 2022. They were one of the first steps to take Power BI beyond its initial self-service positioning to also scale to enterprise payloads.

4.1. User-Defined Aggregations for Pro and PremiumUnlike most other enterprise features, user-defined aggregations can be 1) defined in Power BI Desktop rather than requiring the use of third-party tools, and 2) used in Pro too, not just Premium.

Of course aggregation tables are going to be stored in Import mode since the entire point is to leverage the in-memory cache. But a significant drawback of user-defined aggregations is that Microsoft forces you to have their underlying fact tables in Direct Query mode. Josh Caplan said back in 2019 that being able to keep detail tables in Import mode was “coming”, but it’s nowhere to be seen in 2023. Fear not, we’re about to introduce a workaround.

Coming SoonThe user interface to manage aggregations does offer an easy way to define aggregation tables with their summarization (Sum, GroupBy, Avg etc.), source detail table, and detail column (dimension key or fact to be aggregated). However I find that the required hodgepodge of Direct Query and Dual Storage constraints is not the most pleasant data modeling exercise, even if composite models themselves are a great idea.

A very good intro to Power BI aggsBear in mind that you also have to generate an aggregated data source, either upstream of the dataset – typically in a data warehouse’s SQL view or stored procedure – or within the dataset via Power Query, a simpler shortcut that I wouldn’t recommend for production. So in practice these user-defined aggregations are not always an end-to-end no-code solution, but it’s easy enough to get started. If you have a data warehouse, then you’ll have to spend some time architecting and optimizing how your aggregate queries are processed and possible materialized. Here’s an example in Azure Synapse dedicated pools where the right table distribution method has a dramatic impact on performance.

In production you’ll have to take into account the potential compute costs and refresh times of your aggregate sources and where you want that compute/storage to occur (DWH vs. Power BI dataflows vs. Power BI dataset).

Stack Your AggsThe initial setup aside, the end result is great, as this “aggregate awareness” means that DAX measures built on top of detail fact tables will automatically hit the aggregation tables instead whenever possible. In other words, you don’t have to rewrite any DAX to benefit from aggregations.

For bigger models you might want to “stack” aggregations of various grains on top of each other, which is done via their precedence setting. You’ll want to test to make sure you’ve properly set up your model so that you’re actually hitting the aggregations using tools such as DAX Studio or SQL Performance Analyser.

You’d better hit ’em4.2. Automatic Aggregations for PremiumAutomatic aggregations are a newer addition to Power BI Premium (i.e. not available in Pro) that reminds me of SSAS MD’s usage-based automation wizard, since they derive from query usage which aggregations are worth computing. Of course in the 2020s this has to be marketed as fancy “machine learning training”! And it is a bit of a black box compared to SSAS. I haven’t used them and haven’t heard much about their real-world value, feel free to comment below if you’ve had exposure to them.

1. Programmatic AlternativesMichael Kovalsky, a Microsoft employee who’s worked on some their large internal data models, came up with an interesting way to roll your own aggregations in a semi-automated way. He wrote AutoAggs C# scripts in Tabular Editor to create aggregation tables and their partitions, tables, and relationships, then making DAX measures aware of the aggregations. Michael also created a user interface dubbed Agg Wizard with a CLI option that can be triggered from cloud orchestration tools such as ADF of Azure Functions. You’ll still have to write your source SQL queries.

This makes aggregations available to SSAS/AAS Tabular and allows the use of Import storage mode for underlying fact tables. And it’s not going to consume compute resources unlike the official automatic aggregations. The main drawback of his approach is that it’s limited to Power BI Premium because of its XMLA endpoint dependency. There are a few other foibles such as the fact partitions of the base fact table must be of ‘provider-type’ flavor, i.e. defined with a SQL query which excludes the M expression created by default by Power BI (since any table must have at least one partition) or calculated tables.

Overall, I like this approach a lot even though it’s not the officially sanctioned path, but it’s clearly more involved. I have a growing interest in programmatic dataset management and am currently testing these tools. Speaking of which, the TMSL/TOM scripting options for the official user-defined and automatic aggs are very limited at the moment, and while they’re supposed to be on the roadmap since mid-2022, there’s no public timeline for doing so. Another Coming Soon situation…

1. Aggregation Patterns & Further ReadingIf you’d like to dive deeper into aggregations, including some non-conventional patterns, read Phil Seamark’s blog post series:

2. Creative Aggs (2019) – look in particular at the “shadow model” approach to keep fact tables in Import mode

3. Power BI Aggregations (2021 – work in progress unfortunately stalled)

A final note is that Power BI aggregations can be defined using relationships or GroupBys, as explained in Architecting Aggregations in PowerBI with Databricks SQL. By default you’ll probably leverage relationships as that conforms with the general ethos of using star schemas in Power BI, but GroupBy has its uses depending on source data structure, typically when you have degenerate dimensions in your fact table rather than separate dimensions).

This frames my conclusion: aggregations are a core performance optimization technique that has to be seen as a moving part of your data architecture and modeling, as they deeply integrate with compute/storage trade-off considerations – i.e. where and when to calculate aggregate measures – and are typically either part of OLAP cube or tabular star schema design.

The post Aggregation Options in Power BI and SQL Server Analysis Services first appeared on Olivier Travers.

View Details

This entry is a follow up to How to Find & Open Any File In a Jiffy with Launchy + Everything + Dopus, applied specifically to searching by title through an entire ebook library managed in Calibre. Here I’m assuming you already have these software packages installed, this is not a 101 entry but rather an advanced integration showcase.

Quite the advanced bookmark settings you got there!The first step is to create a bookmark in Everything which saves a shortcut to your ebook library, optionally restricted to certain file extensions to exclude extraneous files (e.g. covers and calibre metadata) and folders from search results, as per the screenshot on the right. This is the most straightforward way to do it, we’ll see a fancier setup using Everything filters in a minute. If you’ve found the book you want to read within the results, you can open it right from the Everything search window, either in the Calibre ebook reader or another reader of your choice.

But what if you want to further search or filter this initial list, based on Calibre metadata rather than just file names? To achieve that, install the Drop Search Results plugin in Calibre. You can now drag and drop the search results from Everything in Calibre (via the DSR popup, be careful not to re-add your books to your library!) where only these selected books will be displayed and marked as true.

You can then do a full-text search scoped to only these files you had selected from your initial file search by checking the “Restrict searched books” checkmark:

Scoped full-text searchUsing Everything Filters and Bookmarks from LaunchyLet’s take it up a notch by integrating the above with Launchy. If you want to trigger an ebook title search from Launchy, you can define a custom search filter in Everything based on file extensions:

then assign it its own keyword in Launchy’s runner:

-filter ebooks -search "$$" However after a bit of additional tinkering I think the more elegant and flexible solution is to use:

1. a bookmark just to save your library’s location – i.e. no file extension filter is defined in the filter – and give it a macro name (e.g. “cal”);
2. a custom filter to narrow down to ebook file extensions, with another macro (e.g. “ebk”);
3. a third macro (e.g. “ebooks”) that combines the bookmark and filter macros.

If you do so then you don’t need to create a separate runner keyword, you can prepend your generic Everything query with the macro’s name, like so:

You can also use these filter and bookmark macros alone or in various combinations, which can come useful if you have several Calibre libraries, or documents outside of Calibre that you’d like to occasionally search separately.

Note that some of that stuff is a bit cutting edge and may only work with Everything 1.5 alpha. It’s easy enough to import any existing bookmarks and filters you might have created in 1.4. Everything 1.5 provides full-text indexing but last time I tried it it effectively killed my PC so I’m a happy camper with Calibre 6.x’s full-text search for the time being.

If you don’t want to use Everything, Calibre has a URL scheme that can be used to trigger searches. It works, and you could use it from Launchy by firing up a web browser, but it won’t feel quite as blazingly fast as Everything.

The post Searching Through an Ebook Collection with Everything and Calibre first appeared on Olivier Travers.

View Details

Back around 2015, Microsoft introduced the current Power BI platform to replace its “Power BI for Office 365” SharePoint/Excel-based offering that most people have long forgotten, with a focus of offering a self-service experience to business users. This product became so successful that a few years later Power BI Premium was positioned as “a superset of Analysis Services“, i.e. Power BI was to also become the future of Microsoft enterprise BI offering. But now came a conundrum, with two very distinct target audiences, toolsets, and approaches, living under one roof.

While working one dataset at a time can be done with Power BI Desktop – a piece of software clearly inspired by PowerPoint and Excel – Power BI Premium features inherited from Analysis Services, such as Object Level Security or Partitions, are by and large not supported in that tool. A perhaps even more crippling limitation is that Power BI Desktop is handling one dataset at a time, with no scripting abilities that can scale across several datasets, let alone an entire enterprise.

We’ve already covered in depth how various third-party dev tools have stepped in to complement Power BI Desktop. In this entry we will introduce in a logical sequence what are the programmatic options in the Power BI ecosystem. The intended audience is people coming from the self-service space to ease their way into enterprise tooling, as people coming from enterprise BI have already been using that stuff for a decade or more.

1. There’s a REST API for That, Right? Right?!The First Rule of APIs: Randomly partial functional coverageMicrosoft has done a good job promoting the fact many things can be done with Power BI via its set of REST APIs, which they’ve made in part available to “citizen developers” via Power Automate UI connectors. This provides an accessible path and learning curve from performing “clicky draggy” UI-driven manual steps in Power BI Desktop to a workflow-based automated approach, within reach of power users that are not professional developers or IT sysadmins.

So people first thinking of automating Power BI tasks will be tempted to believe these APIs cover all possible automation scenarios. Let’s cut to the chase, that’s not the case. The scope of these APIs covers embedding, admin/governance, and content management. What they do not cover however are the inners of a Power BI dataset, so if you want to script data modeling steps such as creating a relationship between tables or establishing an RLS rule, you’re out of luck with the REST APIs as of 2023.

1. PowerShell to the Rescue?The first tool that the adventurous power user is likely to run into past the REST APIs is PowerShell, which Microsoft also has promoted to some extent to its Power BI user base. But here’s where things start to be confusing, as “PowerShell” means different things:

“PowerShell is a cross-platform task automation solution made up of a command-line shell, a scripting language, and a configuration management framework. PowerShell runs on Windows, Linux, and macOS.”

documentation – let’s same three things the same way, because we’re Very Smart People

Citizen developer discovering an ancient inscription that reads “PowerShell”.
Carbon14 dates it to 8.000BC?PowerShell can run on desktops but also in Azure and third-party clouds, which opens up automation and orchestration possibilities, albeit less in a less user-friendly way than UI-based Power Automate. For Power BI purposes, see these modules, and for modeling purposes more specifically, this list of cmdlets. Among them, the Invoke-ASCmd cmdlet lets you “execute an XMLA script, TMSL script, Data Analysis Expressions (DAX) query, Multidimensional Expressions (MDX) query, or Data Mining Extensions (DMX) statement against an instance of Analysis Services.”

We’re about to talk more about TMSL and XMLA, if you’re interested in reviewing the alphabet soup inherited from Analysis Services then refer to this entry. PowerShell is definitely not a business user-friendly option, but it has its place for people used to admin automation via a command line, as it’s been around for a long time.

1. Better Luck with Tabular Model Scripting Language (TMSL)?With TMSL we’re fully transitioning to the Analysis Services toolset, with this mouthful:

“Tabular Model Scripting Language (TMSL) is the command and object model definition syntax for tabular data models at compatibility level 1200 or higher. TMSL communicates with Analysis Services through the XMLA protocol, where the XMLA.Execute method accepts both JSON-based statement scripts in TMSL as well as the traditional XML-based scripts in Analysis Services Scripting Language (ASSL for XMLA).”

documentation – they sure love their jargon over at Microsoft

TMSL: The Matrix Silicon Layer?
Not quite.TMSL can be executed from SSMS, SSIS, SQL Agent, or the aforementioned PowerShell, the latter being how TMSL can be executed from cloud platforms. SSMS can generate basic TMSL scripts for you from its UI. With TMSL, we can unlock access to what’s going on within a tabular model and its tables, models, and relationships. It’s relatively accessible if you’re familiar with JSON files. Yet, it’s not the programmatic Graal because of functional and performance limitations. In my opinion TMSL’s best fit is to execute ad hoc operations via SSMS, rather than full-fledged automation.

1. Tabular Object Model (TOM): Now We’re Talking with Scalable, Powerful C# ScriptingDear Power BI citizen developer, you’re not in Kansas anymore:

This guy models!

“The Tabular Object Model (TOM) is an extension of the Analysis Management Object (AMO) client library, created to support programming scenarios for tabular models created at compatibility level 1200 and higher. As with AMO, TOM provides a programmatic way to handle administrative functions like creating models, importing and refreshing data, and assigning roles and permissions.

TOM exposes native tabular metadata, such as model, tables, columns, and relationships objects. A high-level view of the object model tree, provided below, illustrates how the component parts are related.”

documentation – what a way with words, these writers are such elegant poets!

With TOM you can build entire datasets programmatically from scratch, including advanced features such as custom partitions, translations, or perspectives that many Power BI users don’t even know exist. But to use it you’ll have to learn at least a modicum of the C# language, set up a desktop and/or cloud environment with the adequate libraries (i.e. .Net DLLs), and learn the syntax and scope of the various AnalysisServices namespaces.

By conquering the TOM super power, we’re now morphing into the final form of the enterprise developer end boss! Read Programming Power BI datasets with the Tabular Object Model if you want to dive in, and watch the video below:

1. If the TOM is the Bees Knees, What About Tabular Editor Scripting? And Why All These Overlapping Options?Citizen Developer morphed into BI Final Boss, ready to shred complex data modeling problemsWait, there’s more? We’re almost there, but yes there’s an optional twist to TOM scripting. Tabular Editor is primarily known as a desktop GUI tool, but it is built on top of its own API using libraries that can be scripted. The TOMWrapper.dll namespace is very similar to the underlying TOM but adds features needed by Tabular Editor and aims to offer more convenience and abstraction than Microsoft’s libraries. See this video for more details.

Scripting in Tabular Editor also uses C#, so if you’re upskilling for TOM you’re prepping for TE and vice versa.

C# for complete newbsSo what to make of all these developer options? Microsoft has this to say:

“Although both TMSL and TOM expose the same objects, Table, Column and so forth, and the same operations, Create, Delete, Refresh, TOM does not use TMSL on the wire. TOM uses the MS-SSAS-T tabular protocol instead […]

The decision to use one or the other will come down to the specifics of your requirements. The TOM library provides richer functionality compared to TMSL. Specifically, whereas TMSL only offers coarse-grained operations at the database, table, partition, or role level, TOM allows operations at a much finer grain. To generate or update models programmatically, you will need the full extent of the API in the TOM library.”

More documentation – what a joy!

And here they state explicitly that these tools are not mutually exclusive:

“TOM represents a new and powerful API for Power BI developers that is separate and distinct from the Power BI REST APIs. While there is some overlap between these two APIs, each of these APIs includes a significant amount of functionality not included in the other. Furthermore, there are scenarios that require a developer to use both APIs together to implement a full solution.”

Programming Datasets with the Tabular Object Model (TOM)

I hope this clears things up. In conclusion, I think it’s unlikely that Microsoft will invest in adding advanced scripting in Power BI Desktop, as they seem content with relying on third-party tools while neglecting to keep SSMS/SSDT tooling up to date. If you want to manage Power BI datasets at scale, there’s no way around learning and using the APIs and XMLA endpoint scripting, as well as the cloud-based orchestration platform of your choice to make these API calls.

The post Making Sense of Power BI Programmatic Options: REST APIs, PowerShell, TMSL, TOM, Tabular Editor Scripting first appeared on Olivier Travers.

View Details

Plex Media Server can be a pain in the neck to get fully working with a direct connection between your clients, whether on your local network or remote, and your server. Here’s a quick checklist to diagnose and troubleshoot these connectivity issues. This entry is not meant to go much into details, but rather to give a high-level sequence from lower to higher layers, i.e. from networking to the Plex application to (optionally) its container.

1. What’s Wrong with Not Connecting Directly and How to Know for Sure?Direct Play – This is the (a)way
   (streamed at 36Mpbs from a mounted cloud drive /flex)The telltale that you’re not connecting directly is forced transcoding at a horrible rate of 1Mbps (free Plex users) or 2Mbps (Plex Pass) during playback, even locally. Skipping or moving within the video during playback may also be very slow, whereas it should be pretty much immediate if you have a decent server (mine is a beefed up Synology DS920+) client (I like the Nvidia Shield), and network (I wired my entire house with Cat6A Ethernet cables). Failing to establish a direct connection to your PMS means your client is accessing it through Plex’s (throttled) Relay.

If you can’t trust your lying eyes, you’ll know it for a fact by going to the Dashboard of the Status section of your server’s settings.

You absolutely want to ensure you have a direct connection to your server otherwise your 80′ 8K OLED TV will look like it’s playing VHS from the 80s. Don’t settle for such a horrible experience!

1. How do I fix it in PMS and/or My Local Network?The next step is to go in PMS to Settings > Remote Access and check that remote access is enabled and working. I like to manually set the public port, which I then forward in my router and open in my firewall. Until and unless you get a green checkmark here, as per the screenshot introducing this entry, don’t expect to get a direct play or stream going on, even on your LAN. Don’t worry if the checkmark is red when you first go to that page, what matters is whether it turns green after you tell Plex to check.

If you’re struggling, try (temporarily!) disabling your firewall(s) – and you might have one on your router and another one running on your server – and/or use uPnP or NAT-PMP to try and isolate the root cause of your connectivity problem: is it faulty IP address routing, or is it too aggressive port blocking? Is only a single client failing to connect directly, or all of them? Don’t forget to roll back these changes after you’re done to make sure your security is tight.

1. What If I Use Docker, a Reverse Proxy, or Other Fancy Tech That Makes Troubleshooting Harder?If you’re running Plex in Docker, that opens up additional potential connectivity issues. Long story short, you’ll either have to set up your Bridge network in the container’s settings (more involved) or use it in Host mode (easier). Remember that while you can change the outward-facing port, Plex’s internal port should always be 32400.

Similarly, if you’re running a reverse proxy and/or your own domain name maintained via DDNS, you’ll have to troubleshoot specifically those parts of your setup. For instance, can you access your server from app.plex.tv, your internal IP address, but not the self-owned domain name you might have assigned to Plex? Do you face similar issues with other apps or containers, or is this only happening with Plex?

The whole idea when you’re facing a hairy technical issue that might have a very long list of possible causes is to divide and conquer by making a list of what works as intended until you manage to isolate the source(s) of the issue. You can’t fix what you don’t know is broken.

For further details and related content, see:

• Plex, Docker, and the problem of always appearing as “Remote”
• Troubleshooting Remote Access
• Home LAN + NAS Administration & Security 101 and Beyond for Remote Work and Media Management
• Docker Networking

The post Plex Server Direct Connectivity Checklist first appeared on Olivier Travers.

View Details

In a previous entry I explained at length the various ways to secure data in business intelligence products. In this follow-up I want to elaborate on which method(s) to use... Read more »

The post Row Level Security, Object Level Security, Data Masking: What Are the Business Use Cases? first appeared on Olivier Travers.

View Details

This week it just happened that two of my consulting clients needed a date dimension table so I started revising my base Power Query template to make sure it was... Read more »

View Details

Recently I wrote about things you can’t do on the Power platform when querying an on-premises SQL database. Specifically, I wanted to recreate views in a database that gets wiped... Read more »

View Details

I’ve been spending time lately trying to get a deployment pipeline to work for one of my clients and ran into a couple of non-intuitive limitations that I didn’t expect,... Read more »

View Details

On the surface it may look like Microsoft’s data gateway provides an even playing field between IaaS and PaaS approaches with regards to SQL hosting, i.e. you can run your... Read more »

View Details

Power BI’s DAX language is often described as “simple but not easy.” While visuals such as tables and matrices generate a lot of DAX behind the scenes, such as subtotals... Read more »

View Details

The main appeal of Power BI’s new Datamart capability is possibly the fact it’s creating a fully managed SQL Server (optimized for analytical purposes) while still exposing it to the... Read more »

View Details

With today’s preview launch of Power BI datamarts, there is yet another data entity to contend with in the Power BI ecosystem. I plan to update this entry as datamarts... Read more »

View Details

On most weeks I need to schedule meetings in several time zones that literally go from the US West Coast to the Australian East Coast, and I got into the... Read more »

View Details

This entry fills in the gaps of Microsoft’s official documentation as I had to go through a fair amount of trial and error to get things going. It assumes general... Read more »

View Details

Data-centric jobs are sitting by definition at the intersection of business and technology, and that affects who performs these jobs, their tools, and workflows. Traditionally data analysts tend to be... Read more »

View Details

Power BI is strongest for its built-in Power Query ETL and powerful Vertipaq tabular engine, but it’s not renowned as the crispest visualization tool on the commercial BI scene (most... Read more »

View Details

I recently had a blast talking with Rob Collie and Tom LaRock on their Raw Data podcast, covering some of my eclectic obsessions from international arbitrage and public deficits to... Read more »

View Details

Unlike Office desktop applications, Power BI Desktop doesn’t keep track of more than one account at a time, which is quite annoying for people like myself who work across several... Read more »

View Details

The ability to filter data by user is critical whenever sensitive information is shared, whether internally to an organization or even more so externally. This is often known as “row... Read more »

View Details

In a previous entry we saw how to use Cascading Style Sheets with Power BI’s HTML Content visual do apply dynamic visual effects outside what’s commonly expected within the platform.... Read more »

View Details

Microsoft Power BI rose to its leading position in the BI market on the back of its top-shelf PowerQuery self-service ETL and VertiPaq in-memory engine as well as its familiarity... Read more »

View Details

While the initial consumer-grade NASes sold during the late 2000s/early 2010s had fairly weak CPUs and little memory, newer models priced in the $200-$500 range from about 2019 forward are... Read more »

View Details

I’ve had NAS devices for almost 15 years and have grown our local network to 40+ devices for a family of 4, as the PCs, laptops, smart TVs, tablets, phones, voice assistants, and even light bulbs have piled up. I’ve been working remotely forever, but our kids are now stuck with us studying from home. Having stable internet access and reliable services has become more and more important, whether for work, education, or entertainment. And while it can be fun to tinker with technology, at some point you want to have a stable state where “it just works.”

If like me you’re facing these growing needs and expectations, you can’t be complacent, and you do need to invest in your own education to get the best out of your network devices. If you expect your Internet Service Provider (ISP) to take care of all of this for you, in most cases you’ll run into a wall of arbitrary limitations, incompetent tech support, and overall disregard for customers.

Meanwhile bad actors keep finding new ways to break into everyone’s infrastructure. Do not think you’re protected by the obscurity of being just a random home user: most attacks are massive brute force efforts against thousands if not millions of devices at a time. With even the biggest companies in the world now massively using remote work, this is only going to get worse. You don’t have to become a full-time cybersecurity expert, but you need to know how to “drive defensively” in the online world.

If you use more than the very basic features of your Network Attached Storage [NAS], be prepared to become a part-time system administrator in charge of:

• The NAS with its files and services, which you’ll likely run in Docker containers (why and how to do so will be the object of a separate post).
• Your entire local area network (LAN), from your upstream broadband provider to your Wifi and Ethernet LAN, as well as all the connected devices.
• And in many cases, secure access to local resources from the outside.

This guide is meant to help you get back in charge. You don’t want to copy everything I’ve done verbatim as your mileage may vary, but the gist of it should be useful regardless of the exact equipment you’ve purchased. I won’t explain every technical term at length, but I will link to resources that do so. Read on, take what you can use, discard what doesn’t apply to your circumstances, and enjoy!

1. General Principles and Tips To get a grip on your local infrastructure without opening huge security holes or turn your network into an unmanageable mess, at a minimum you need to:

2. Make conscious decisions about which device on your network will handle firewall, port forwarding, and DHCP duties, which you may want to complement with custom DNS and DDNS services. I’ve chosen to handle all of these on my main router – a dual WAN Synology RT2600ac with two fiber optic providers at 900/400Mbps each – except for DDNS which is handled by a container on the NAS.

3. Avoid opening ports you’re not using and be as narrow as possible in your port forwarding rules. If you’ve done a good job forwarding ports manually you might as well disable UPNP, that’s one less security liability to worry about.
4. Disable default admin and guest accounts on all routers and NASes.
5. Use two-factor authentication on routers and NASes.
6. Anything related to remote access has to be buttoned up with extra precautions. Disable SSH access if you’re not going to use it, and if you do use SSH, change its default port.
7. Keep firmware up to date as new vulnerabilities get patched.
8. Make your local devices easier to administrate from one central place by assigning them IP addresses via DHCP reservation. I also keep track of devices in a spreadsheet where I have their brand, model, IP address, MAC address, and a few other items of interest.
9. Set your ISP modem to bridge mode and disable as much of its extra features as possible so that it’s just a modem and not a crap router/AP. If you don’t do this, you’ll have double NAT problems to access your LAN from the internet. ISPs tend to provide underpowered devices that you have to fight with to be able to administrate. You might also need to contact your ISP to move away from CGNAT to have your own public IP address. Be warned, this can be an uphill battle with some ISPs. Some ISPs can also provide IPv6 addresses but to be honest I haven’t looked at whether I could make use of it as an end user.

To make your life easier, use a single router for routing and set other routers as dumb access points, i.e. just wifi with no routing nor DHCP. If you’re in a large house, use either a mesh network (more convenient but more expensive) or a collection of traditional APs set with the same wifi name but on different channel (to avoid interferences). This will let you move around the house with your mobile devices staying connected to the stronger signal without having to switch manually to a different AP.

If you intend to stream 4K remux movies (i.e. perfect copies of BluRays that can take 60+GB for a single movie) you’ll want to run Category 6 cable around your house if possible. Wireless speeds are getting better and better but wired is tried and true for stability and reliability regardless of how thick your walls are or interfering signals from the neighborhood. Ethernet over the powerline or coaxial are decent alternatives if you can’t get Ethernet cables everywhere you’d like to.

There are some other, more advanced networking concepts that you may also want to learn, depending on your exact needs:

• Virtual Private Networks (VPN): whether you’re contracting a service provider such as Private Internet Access, or hosting your own home VPN, this is often in the mix for secure remote access.
• Virtual Local Aera Networks (VLAN), this lets you segment your LAN, for instance if you want certain devices (guests come to mind) not to see the rest of the LAN.

For more on this general topic, read:

• How to enhance the security of your Synology NAS – basics to start with.
• How I over-engineered my home network for privacy and security – some more advanced concepts.

On self-hosting more specifically:

• A self-hosting lifecycle

• DNS & DDNS for Secure and Convenient Access to Outside and Self-Hosted Domains There’s a lot going on behind the scenes to turn you typing oliviertravers.com in your browser to this site actually loading up. If you want to a) have a say about how domain names are resolved for requests coming from your network, and b) host some domain names locally, that can entail taking quite a few steps. While you could rely on your ISP’s DNS servers, which is the default behavior if you don’t do anything, these are often fairly badly maintained so it’s often worth taking these over with the DNS service(s) of your choosing.

There are many ways to approach this, here’s what I do to resolve both external domain names and those domains I self-host:

• The DNS Server on my router resolves self-hosted domains. If you don’t set up your own DNS server, you’ll be able to access your self-hosted services from the outside but you’ll have a loopback problem and they won’t resolve locally. You can also run this on your NAS but in my opinion, better run it on the router if you can.
• Handling of self-hosted domains is done with a dedicated zone where I created A records (even for subdomains) pointing to my NAS IP address, as explained here.
• The DNS server then forwards requests for all domains outside of my self-hosted domains to a public DNS resolver such as Cloudflare’s 1.1.1.2 / 1.0.0.2 to get fast resolution as well as a layer of protection from malware (an extra benefit above their standard 1.1.1.1 server).
• Cloudflare also provide DNS services for your own domain. I have an A record for each top level self-hosted domain, and CName records for their subdomains. To do this you need to open a free account with them and create an API token.
• My two ISPs, like most residential broadband providers, don’t market static IPs to consumers, so I run a Docker container to handle DDNS with the aforementioned Cloudflare DNS account, meaning I don’t have to use a third-party service such as DuckDNS.

Were Cloudflare to have a massive outage, I’d need to change the forwarding DNS servers (e.g. to point to Google’s 8.8.8.8) in one place, and we’d again be able resolve domains throughout the LAN in just a couple of clicks.

External DNS requests can be sent over HTTPS (aha DoH), also via Cloudflare, but it doesn’t look like Synology’s DNS Server knows how to forward to anything but IPv4 addresses. This is tentative and needs to be further investigated, but for more advanced options like this, I may need to use something like PiHole. I’ll probably revisit this at some point and update this entry accordingly.

Have lots of subdomains? Back ’em up. A fancier alternative for DNS resolving that I’ve started testing is Cloudflare for Teams’s Gateway (free for up to 50 users) which lets you define policies to resolve, block, or override domain name requests based on the rules of your choosing. This can be done via DoH too, but again I need a solution for DoH forwarding.

Using your own DNS server is vastly preferable to editing hosts files as 1) you don’t have to maintain the latter on each device, and 2) good luck even being able to do that on non-rooted mobile devices. Also make sure to export your zone settings to a file as backing up your general router or NAS settings won’t include them.

1. Make Access to Your Apps Easier with Your Own Domain Name, SSL Certificates, A Reverse Proxy, And Redirect Rules As you inevitably add more and more containers to your NAS, you’ll find that all these http://ipaddress:port URLs become harder and harder to remember. The ideal format instead would be https://subdomain.domain.tld. That involves a few steps:

2. You can buy a cheap domain name such as a .top domain for $2 then renew it for $4/y. Once you have your domain name, set up short, memorable and easy-to-spell subdomains. How to handle the corresponding DNS records is explained above.

3. You’ll want to secure the domain and all its subdomains in one swoop with a free wildcard SSL certificate from Letsencrypt.
4. With a reverse proxy, you can then forego port numbers, so as promised you end up with something like https://books.mydomain.top or https://movies.mydomain.top that your friends and family can actually remember. In some cases (e.g. AirDC++) you might need to add custom headers for authentication to work properly.
5. Add redirect rules from http to https, and your users will just need to type books.mydomain.top without specifying the protocol or port, which is a much better user experience on a phone.

To recap, all the above will load subdomain.domain.tld via https from the right container at its local IP address and port, whether you’re loading the URL from your LAN or from the internet. Pretty cool huh?

Calibre Web with own domain via https and the whole shebang This can be a bit technical so take it one step at a time, but in my opinion it’s well worth it and these three awesome tutorials by the extremely helpful Luka Manestar will hold your hand all along the way:

1. Let’s Encrypt + Docker = wildcard certs (automated creation and renewal)
2. Synology Reverse Proxy (under the hood it’s a bunch of Nginx rules)
3. http to https redirects

Once it’s all set up, switch your SSL/TTL encryption mode to “Full” in the Cloudflare settings (in “flexible” mode I ran into the dreaded 522 timeout error), and switch DNS proxy status to “Proxied” to avoid exposing your public IP. You can test your SSL web server if it’s accessible on the public Internet.

If you mess up your redirect rules and are struggling to test the edited ones because of browser caching, test in incognito mode or do a hard refresh via the browser’s developer mode as explained here.

Further out I plan to explore these articles:

• Routing containers through a VPN

• Easy access to Docker containers inside VPN

• Know What’s Going On with Your LAN & NAS with Grafana & Friends Now we’re admittedly going beyond strictly 101 topics, but don’t be too intimidated as there are many great blogs and videos to make new technical concepts and tools approachable. It all revolves around Grafana, a powerful querying and dashboarding solution that can be fed with logs and data streams respectively. If you’re not a hardcore IT person this might sound daunting, but once you get the hang of using Docker containers you’ll find it’s actually a fairly quick setup process.

The stacks suggested below complement each other and will be less involved and more adapted to (power) home use than complex enterprise solutions such as the Elasticsearch/Logstash/Kibana (ELK) stack, Splunk, or Prometheus. But the beauty of containers is that you’re always only minutes away from testing something new, and if you don’t like it, get if off your NAS like it was never there in a matter of seconds.

4.1. Handle Logs with the Promtail / Loki / Grafana stack Promtail collects logs generated by apps and containers, then Loki ingests them, thus making them available to Grafana to visualize them. I recommend that you set these containers first as your default logging solution for all your containers, as this seems to work only with newly-created containers.

You end up with a mini-Google search engine of all your logs from one search box, which is so much more convenient that accessing individual logs from Portainer or the command line. Since you’ll likely have some troubleshooting to do to get other containers working, you’ll get the most benefit by, again, starting here as opposed to rushing to install your media servers et. al. If you’re just getting started with your system, set up PLG as a first order of business, you can always add Telegraf+InfluxDB later once your container stack starts to gel.

See:

• Loki configuration for Docker container logs – if you do one thing, do this!
• Loki tutorial for Synology
• Deploying Loki and Promtail together with the TIG stack

If this all sounds overkill, consider using Dozzle as a self-contained real-time log viewer.

4.2. Handle Data Streams with the Telegraf / InfluxDB / Grafana Stack Where the PLG stack takes care of log files of discrete events, the TIG stack does the same for continuous streams: Telegraf collects, InfluxDB stores, then Grafana visualizes ongoing data streams generated by hardware and software. This can give you very detailed insights into the usage and health of your NAS or router, such as memory or CPU load, via Simple Network Management Protocol (SNMP).

For details, see:

• Monitor ESXi, Synology, Docker, PiHole, Plex and Raspberry Pi and Windows using Grafana, InfluxDB and Telegraf (includes a demo)
• A beginner’s guide to SNMP

Grafana Dashboard Setup for your PLEX & NAS 5. Other Entries of Interest I started this blog in 2000 and have been writing about plenty of different topics, but if you were interested in this entry, I bet you’ll like these:

• Getting the Most Out of Your Synology Networked Attached Storage: Did You Know It Can Do That?
• How to Bend Plex to Your Will to Handle Complex Libraries Without Losing Your Mind
• Things I Found Out the Hard Way to Get the Most of the Nvidia Shield
• How I Save Time with the Right Shortcuts, Handpicked Apps, and Finetuned Hardware

This is the initial version of this entry, which I’ll revisit in the months to come as I explore some of the more intricate topics. While I’m fairly technical, I’m not a networking engineer and I’m learning all that stuff through google / trial / error. Constructive feedback welcome in the comments below.

View Details

It’s very easy to get started with Plex, but as you add different types of content and grow your libraries, it becomes more and more complicated to keep everything tidy and easy to use. I have 150TB+ in Plex organized in a dozen different libraries, learned a lot along the way, and will share my notes in this entry.

Over the past decade I went back and forth between Plex and Emby as my media server of choice but eventually settled on the former after I switched my server from my desktop PC to an Nvidia Shield, then to a Docker container in a Synology NAS. It’s been a long journey and I hope to accelerate your own learning curve.

The ways we are interacting with a tutorial, audiobook, movie, or TV series are all very different, and it takes work to bend Plex to be a good fit with all these scenarios. If you have to take anything from this post, it’s that each type of content needs its own library with an approach that’s finetuned to it.

As much as I try to use automation and rely on Plex’s native features, to achieve the best results be prepared for some grunt work to get more obscure content organized neatly and in line with how it’s meant to be consumed.

I try to steer away as much as possible from organizing or tagging content via the Plex UI, as that metadata won’t survive if you have to rebuild a library from scratch, which over the years is likely to happen eventually. Instead, metadata is best conveyed via 1) your folder/file organization and naming conventions, 2) by metatagging source files themselves, and/or 3) with helper text/json/xml files to be parsed by Plex agents or scripts. But there’s some genre and collection metadata that has to be input via the Plex UI.

If you’re not willing to make that effort, well, garbage in garbage out, you’ll get out of your libraries what you put into them. And the more content you add, the more you’ll need it to be organized or the whole thing will collapse under its own weight.

As a caveat before we move on to the meat of this entry, this post is not about streaming or transcoding. I’m using Plex in Direct Play on my LAN and do not care about serving low-quality transcodes to the entire neighborhood as some people seem keen to do. (I’m not judging, it’s just that I’m not interested nor experienced with this use case).

1. Matching Recalcitrant Documentaries, TV Shows, Movies, Sports etc. Provided you apply the recommended naming conventions (TV, movies), Plex is usually good at auto-matching and fetching the right metadata, especially since the new movie scanner and agent were introduced with PMS 1.20. However, some content types can be tricky and tiny punctuation variations can throw the agents off (e.g. you can’t put colons in Windows file names), as well as titles in other languages than English. Follow these guidelines to save you a lot of grief, depending on the type of content you plan to collect.

1.1. Documentaries Are Not All the Same, Or Are They? Documentaries can’t be all dumped in the same library. Instead, make sure to put standalones (e.g. Tropicália) in a Movie library while series (e.g. Chef’s Table) should be in a separate TV library. Some are tricky as you may think they’re standalones but they’re actually part of an extended TV series of sorts (e.g. content from National Geographic). Look up these loose ends in TheTVDB and ~~IMDB~~ TMDB then decide whether to put them in your TV or Movie documentary libraries accordingly (edit: someone pointed out I made a mistake, it looks like IMDB is used by Plex just for ratings, like Rotten Tomatoes).

After I initially posted this entry someone brought Colima to my attention. I think I’ll stick to my approach, but it’s good to have options:

“Combined Library Metadata Agent (Colima) in combination with Absolute Series Scanner (ASS) allows you to have movies and tv shows within the same library, something that Plex sadly not supports out of the box. Common scenarios to use this agent for are documentaries, western and Japanese animation libraries.”

Colima.bundle README.md

1.2. TV Shows & Movies: It’s All About Matching What’s in Meta Databases To automatically enforce Plex’s naming convention on TV shows, use a tool such as Sonarr, Filebot, TV Rename, or Rename My TV Series. TV shows should match in most cases if they’re properly named, but in the rare case that they won’t, find the offending series in TheTVDB then force a match by ID. With the new Plex TV Series agent, you might need to append “thetvdb-” to the ID.

You can do the same ID matching with IDs from TMDB in movie libraries. Movie libraries also work for stand-up comedy shows, which I chose to set up a separate library because for me it’s a different mood, time commitment, and audience than watching a regular movie. This is why I also have a separate library for family movies but from a technical perspective you don’t have to use separate libraries in these cases, it’s more of a convenience. (I made that structural choice before the addition of Smart Collections, which we cover later in this post).

1.3. Support for Music: OK, Not Great Plexamp is a pretty cool player for Pass subscribers, but it’s limited by Plex’s limited metadata support on the backend. The lack of support for composer and conductor cripples classical music collections, while the absence of producer and remixer fields may leave electronic and rap collectors wanting (related thread). Regular albums in mainstream genres will get picked up, often with covers and lyrics, provided you stick to the naming convention.

Dedicated music server Navidrome should eventually address these shortcomings, but as of September 2021, this “will equire a pretty major refactoring of the database […] and will affect a lot of the UI as well, we don’t have a timeline for that yet.”

1.4. Sport Events: Welcome to the Big Leagues Your mileage may vary with sports depending on whether individual events can be found in Tmdb or TheTVDB. UFC fights for instance are recognized if you set them in a movie library. Here’s a reddit thread discussing successfully handling Formula 1 seasons and here’s a thread about NBA/NFL, and another about NBA. A similar approach may or may not work for your sport of choice depending on how mainstream it is.

1.5. Anime, Music Videos, and Other Miscellaneous Content Types That People Somehow Want in Plex It’s impossible for me to track all the ways people use Plex, as there are many niches I have no interest in. Some people want to handle family videos, travel pictures, radio shows, podcasts, ebooks, comics, movie trailers, etc., including things that I think are much better handled by other dedicated content servers such as Calibre Web or Komga. There’s a whole subreddit and website just for preroll videos!

I load Anime in a regular TV Shows library but then I have a tiny library, if it’s something you’re more serious about there an agent called HAMA.

For long music videos I use a Movie library, this works well for most operas and concerts. If you want to collect short MTV-style music videos, your guess is as good as mine, possibly look into the Shorts section later in this entry.

1. Getting the Best Experience for Tutorials: Use mp4 Tagging or a Custom Agent Multi-part video tutorials is the content type I found most tricky to handle, as the big public databases don’t have metadata about them. There are two main approaches to this challenge, each with their pros and cons. Again, you want to refrain from doing a lot of work via the Plex UI as you’d have to redo it all over again in case you need to rebuild your library for whatever reason. With computers, nothing is permanent so plan accordingly.

2.1. The Embedded Metatag Approach A first round of trial and error led me to using collections with the following settings:

• Library type: Movies. If you use Other Videos you’ll have to merge video parts manually in Plex, which would need to be redone if you ever have to reset said library, which in my experience is bound to happen.
• Scanner: Plex Movie Scanner.
• Agent: Plex Movie (legacy).
• Hide items belonging to collections (stacked content is based on Collections).
• Rename files with a tool such as Flash Renamer, following the “- partx.ext” convention.
• Group parts of the same tutorial under the same “album”, by tagging files with mp3tag. This works on mp4 video files so if you have AVIs or MKVs you’ll want to convert them with VLC or Handbrake first.
• For categorization I don’t use tags, which never got any love in the Plex UI, but rather self-defined genres.
• You can add specific artwork for each video segment (i.e. if you have different files within the same tutorial), which will make it easier to pick the right one among the 20 parts of a long tutorial. Some media players such as mpv make it very easy to save screenshots in one keyboard shortcut.
• You might need to generate your own posters as tutorials about underwater archery or Roblox origami are not necessarily going to come with something good looking for Plex purposes. The expected aspect ratio is 1:1.5.

This turned to be a manageable but not entirely satisfying workflow in the backend, and the end result in the frontend is a decent but not optimal user experience since navigating a large library will mostly have to be done via the Collections tab. There must be a better way!

2.2. The Agent Approach with Helper Metadata Files As an alternative, a neater organization may be obtained by following the TV show/season convention, and even more control can be supplied by custom agents. It’s a shame that Plex stubbornly lacks support for long-established .nfo files, but people have been complaining about it for years to no avail so I’m not holding my breath. I tested four options and have adopted one that ended up being as close to my ideal requirements as it’s going to get.

• Personal Shows Metadata Agent – this is the best option I found for tutorials. Its meta.json format supports genre tags, studio, and cast, but it could be more thorough as there was initially no support for some fields such as collections, tagline, or originally available date… I edited the Python source code to add these fields, a tweak that the author then officially added to his repository. If you’re willing to do the legwork to conform to the TV show structure and naming convention required by this agent, then the result is very satisfying.
• Extended Personal Media Shows Agent (forum thread). Like the previous one, it unfortunately doesn’t leverage agents that harvest mp4 tags and there’s no way to set cast (actors/directors), Genre, or Studio. This agent relies on following a specific naming convention with optional .summary and .metadata files.
• XBMCnfoTVImporter – This plugin is meant recognize the type of nfo files used by Kodi, but documentation is nonexistent so while this is probably a good option, prepare to fumble in the dark.
• AvalonXmlAgent – inspired by XBMCnfoTVImporter, but with a different XML file format. Looks good but doesn’t handle season titles. My first attempt with it didn’t work too well though, I think I’ll stick to the Personal Shows Metadata Agent.
• Local Assets-Metadata Double Agent (forum thread) – similar in spirit to the above but with the added twist of letting you save Plex Metadata locally (i.e. it does import/export).

The agent-based approach is also what I’d recommend in case you want to organize your own home photos or videos. For photos there’s an autotagging option but it’s calling a third-party service – which you may not want to do for privacy reasons – you need Plex Pass to be able to use it, and it’s reportedly no match with Google Photos’s face recognition.

For the best result with custom cast members, save square .jpg pictures of close-up mugshots in a dedicated web folder. Common cloud services such as Google Drive or OneDrive make this way too hard for some reason, I serve these images via a local web server on one of my Synology NASes.

1. Audiobooks: One of the Most Challenging Use Cases with Intricate Solutions Dedicated support for audiobooks is one of the top open feature requests but so far, they still require that you jump through similar hoops as tutorials for the most part, except as a Music library and using a special-purpose agent. The main drawbacks of using Plex for audiobooks are that:

2. On the frontend side, the official Plex clients are not the best to keep track of progress time across devices and other UX niceties specific to listening to audiobooks.

3. On the backend side, Plex doesn’t provide an agent/scanner combo for audiobooks like they’re doing for TV shows or movies.

For details on the intricate workflows necessary to get the best outcome, see:

• Audiobook Guide 1 – macr0dev
• Audiobook Guide 2 – Seanap – more recent than the previous one, among differences it now leverages the new audnexus data aggregation API via this agent.
• Play audiobooks outside of Audible on Alexa devices
• Alternative approach for multipart audiobooks using mp4s in a Shows library.

To listen to audiobooks on mobile devices, check out:

• Chronicle Audiobook Player for Plex – Android only, decent option.
• Prologue – I hear good things about it but I wouldn’t know firsthand as iOS only
• Bookcamp: app for iOS and Android with Plex backend support, it’s in early access as of the end of 2021 and looks slick. It’s a paid subscription. I haven’t been able to get it to find my audiobook library even though my Plex server is accessible from the outside and I successfully linked the app to my Plex account.

A significant limitation of audio libraries is that unlike Movies and Shows they don’t support collections.

1. Movies with Commentary; Collections by Genre and Audience; Shorts; The Elusive Playback Speed 4.1. Commentary Tracks is Finally a Solved Problem Thanks to a Powerful Python Script There’s no built-in way to tell apart movies with a commentary track at the library level because the audio stream metadata is not leveraged for navigation. Some people use MKVToolNix or ffmpeg (using ISO codes) to set the commentary soundtrack to a rare language such as Icelandic, while others suggest Tautulli but that seems clunky at best. Alternatively, you could use sharing labels, which require Plex Pass, or create a “Commentary” tag within Genres or Collections. I elected to do the latter, but didn’t want to maintain it manually through the Plex UI, so for a long time was stuck without an acceptable solution.

That changed in March 2021 when the author of Plex-Meta-Manager (more on that tool further below) added support for filtering by audio_track_title at my request, which takes care of the commentary track. In your Movies.yml metadata file, have a section like this (be careful with indentation as the dastardly YAML language is looking for excuses to fail):

collections: Movies with Commentary: plex_all: true filters: audio_track_title: Commentary sync_mode: sync collection_order: release summary: Movies with commentary audio tracks from the director or critics.

If you wanted to create collections by audio track codec (AC3, DTS etc.) as someone asked in the comments below, this is also the way to go.

4.2. Use Smart Collections to Dynamically Categorize Content According to Search Filters For all the metadata exposed by the Plex search user interface, you don’t need the aforementioned script. Simply configure an advanced search and save it as a smart collection, a feature added in April 2021 that was a great improvement to Plex.

I’ve used smart collections to categorize documentaries by genre: Arts, Music, History, Nature, Science, etc.

Smart Collections will automatically add new content matching their criteria I have hundreds of documentaries so I wanted to be able to browse them by theme 4.3. Manual collections Are An Effective Way to Handle Different Audiences Within the Same Account I also set up collections manually (i.e. not based on filters) by audience, i.e. content for the entire family, that only my wife watches (mystery and police procedural galore!), that I watch together with her, or that we watch with our daughter but our son is not interested in. That way, depending on who’s in the mood to watch a TV episode or movie, we can narrow the selection down to that specific audience. And my wife can start watching new shows I downloaded for her without accidentally watching something I intended to watch too. I already have enough libraries as it is without creating libraries by genre or audience!

4.4. Short Movies Are a Conundrum I haven’t started collecting shorts yet, but knowing myself, I’ll probably get into it at some point. The main challenge is that you might have hundreds or thousands of shorts if you get into collecting old cartoons, and you’re likely not to like overwhelming your regular TV or Movies libraries with them. My initial research led to this thread and others like it and led me to believe that the main approaches are:

• Use a TV library, as some shorts (mostly cartoons such as Pixar shorts) are in TheTVDB
• Use a movie library for those shorts that are in TheMovieDB
• Use manual collections
• Use smart collections based on Genre = Short

4.5. Playback Speed: Only on Devices That Support Browser Extensions Playback speed, while not strictly about library management per se, is something that people often want to control for audiobooks and tutorials, and to a lesser extent sports, but despite popular demand it’s still not found in any Plex clients. There’s a third-party Chrome extension – which means it also works in Edge Chromium – but that obviously won’t work from your TV or tablet app.

If you must have playback speed control in mobile devices then you’re better off using Emby, at least for the library for which you have that need.

1. Adding Chapters to Make Long Videos Easier to Navigate Around Some long tutorials can be cumbersome to navigate, while other types of content, from sport events to concerts may also benefit from having delineated chapters. If you’re ripping DVDs or BluRays, ChapterGrabber will help you get chapter information from the source, and there’s also the ChapterDB archive for (some) movies.

When chapter information is not already available, you can add your own chapter information within videos with software such as Handbrake or Drax. In any case this needs to be done in the video files before importing them into Plex.

The power user approach to this task relies on ffmpeg, best combined with a helper script so that you can write a simple text file listing your chapters then writing them back to the video’s metadata. The python dependency makes this overkill if you have just a few videos to “chapterize”, but who doesn’t like to spend 5 hours to automate a 15-minute manual job anyway? If like me you’re running on Windows, make sure to save your chapters.txt file with Unix end of line characters (LF), which is easy with Notepad++, otherwise you’ll end up with ugly rectangles at the end of each chapter’s title. Here’s a recap of the procedure tweaked to my own preferences, I do at least a few at a time to get into the groove of things:

*[File Explorer] Make a copy of the video to be chapterized to your work directly and rename it input.mp4
[Notepad++ and mpv.net] Write down chapter timestamps in chapters.txt while speed-watching through the video. Doublecheck there are no typos.
[Command prompt] ffmpeg -i input.mp4 -f ffmetadata ffm.txt
[Visual Studio Code or Python-aware CLI] run helper.py
[Notepad++] Open ffm.txt and convert EOL characters to Unix
[Command prompt] ffmpeg -i input.mp4 -i ffm.txt -map_metadata 1 -codec copy output.mp4
[mpv.net] Open output.mp4 and navigate to each chapter to make sure the timestamp is exactly right and triple check for typos
[File Explorer] Rename output.mp4* and move it to the library folder where it’s supposed to go

While you can set art for seasons and episodes, thumbnails for chapters can’t be set manually with local images, they’re automatically generated by Plex based on each chapter’s exact timestamp (related thread). Meaning that you’ll have to tweak your chapter timestamps almost by the exact frame to get the best results. Well, that’s not entirely true, you could edit the files generated by Plex in the Media folder in the backend, as explained by Noel Plum in the comments below. He’s been doing it for months without issue, but there’s no telling whether future changes in the PMS code won’t mess with that eventually.

With MediaInfo GUI you can quickly check whether any given video file already has chapters, and export that list to a text file. You may want to do so in order to edit it and reload it back to its video source in case the existing timestamps don’t quite work for you (e.g. bad preview thumbnails), or to remove or add to the list. The equivalent with the MediaInfo CLI is:

mediainfo.exe input.mp4 > metadata.txt

The output will need to be cleaned up and reformatted a bit if you want to reinject it as per Kyke Howell’s helper script mentioned above, but it’s not too bad. Alternatively you can use ffmpeg to extract that metadata as also shown by Kyle, with the major inconvenience that timestamps are shown as an integer based on a timebase, or you could use mp4box from the command line, which will generate a ttxt XML file (which then needs to be scrubbed) with hh:mm:ss timestamps (that’s the good part):

mp4box.exe "FilePath" -dump-chap >chapters.ttxt

If need be, you can also scrub existing metadata and chapters with ffmpeg.

Also, it seems you can’t force the generation of chapter thumbnails so you’ll have to wait for your scheduled process to kick in. Once they’re rendered, here’s what that looks like in the web UI (according to what I read chapter support varies among Plex clients):

This guy is a fountain of grappling knowledge, but also talks slowly and repeats himself constantly. Trust me, you want to chapterize him! 6. Miscellaneous Library Customization & Management Tips * If you want your library to look ultra clean, consider using custom posters and fanart such as those found on The Poster Database. I’ve used this for my thematic collections but for individual movies I’m waiting for that site to add a Plex agent and direct integration into the Poster tab shown when you edit an item. Some people even go through the trouble of customizing episode cards, among other reasons to avoid spoilers (“what, this character in the thumbnail died two seasons ago?!”) * You’ll probably want to customize which libraries are part of global search and featured in dashboards, i.e. On Deck and Recently Added. That’s done in the Advanced Settings of each library.

Collections Automatically Created with a Script, Looking Good with Custom Posters 7. Library Organization Wrap-Up: Be Deliberate & Organized As a summary, to get your content properly organized with the minimum amount of friction, it’s essential that you:

• Choose the right library media type for your content, which can sometimes be counter intuitive. This means your source content needs to be properly organized, and you can’t drop everything in one morass of a Plex library.
• Organize your content by type and follow the official naming conventions for your source folders and files.
• Figure out whether readily available scanners and agents will do the job, or if you have to handle metadata yourself. There’s for instance an agent to handle Youtube downloads.
• Set up each library’s advanced settings deliberately as some of the defaults can have big consequences. This is best done when you first set up your library, though that can be edited later.
• Use collections within and across libraries for TV remote-friendly access to categorized content.

• Summary of the summary: RTFM, there’s more to Plex than first meets the eye and it’s well worth understanding what’s going on beneath the hood.

• Choosing What to Watch Next with Trakt, Moviewatch 8.1. Keep Track of Watch Status and Wishlists with Trakt Integration One of the primary criteria to choose something to watch is to filter out content you’ve already watched. Plex does keep track of that, but if you ever have to reset your library, that status is gone. Do you want to manually recheck hundreds of series and movies? If not, Trakt.tv gives you a way to effectively back up your watched status to a third-party service, as well as help you discover other content you might like. There’s built-in “scrobber” integration between Plex Pass and Trakt VIP, but that’s two subscriptions you need to keep paying. If you have a bunch of distinct Plex and Trakt users, that’s the way to go.

In my case, I just have one master Plex account and one Trakt account, so here’s what I ended up doing to get the most out of the Plex-Trakt combo:

1. Install the Trakt.tv (for Plex) plugin via the Applications section of the Unofficial App Store (UAS) in Web.Tools. Contrarily to popular belief, Plex plugins are not entirely dead, I’ll get back to that in a minute.
2. Install Kitana.
3. Do an initial full sync via Kitana (unless your Trakt account is brand new). Now Plex and Trakt should agree with each other on what you’ve already watched then stay that way as you watch new content.
4. Create private watchlists in Trakt to add interesting TV shows and movies as I’m browsing that site.
5. Connect these watchlists to Sonarr v3 and Radarr to automate their downloading.
   • I found that I had to nudge Sonarr into processing a new list which is easy enough via its API.
   • Radarr will only handle your watchlist via the “Trakt User” list option, “Trakt List” won’t work for some reason.
6. With the Web to Plex browser extension you’ll see whether you have a movie/show in your server while browsing Trakt or Imdb.

Find out more about the *arr automation apps in this post. To do the above prior to Sonarr/Radarr v3 you had to use Traktarr.

In summary:

• Content watched in Plex will automatically be marked as watched in Trakt.
• Whenever you watch something outside of Plex, just flag it in Trakt and Plex will know.
• Trakt watchlists are a good way to funnel content back into your Plex libraries if you’re willing to set up the *arr infrastructure (again see my NAS entry, this is such a powerful stack).

A side benefit is that I was able to resync my Plex and Trakt accounts that had grown out of sync.

The only thing that I was able to do via Kodi that I can’t do in Plex itself is the ability to rate content right after I watched it in the media player. Looks like that’s not possible via a Plex plugin.

8.2. Make Movie Night Fun Again with Moviewatch A common refrain from people with large libraries is that it takes them forever to find something to watch as they’re overwhelmed by the sheer amount of choice they have. You can play with filters to narrow down options, but a very first-world solution to this first-world problem is Moviewatch, which gives you and your co-watchers a Tinder-like interface to swap through movie posters until there’s an agreement from all parties, at which point you’re one click away from launching the consensus movie in Plex.

1. Managing & Extending Plex Beyond the UI: Plugins, Maintenance, Data Analysis 9.1. Plugins: Unsupported But Not Dead While plugins such as Web.Tools are no longer supported in the Plex UI, they still work in practice. As I just mentioned, one big piece of functionality they allow is free bidirectional integration with Trakt.

As a complement, Kitana provides a web frontend to these plugins (how to install Kitana via Portainer) which comes handy in some cases.

9.2. Maintenance & Backups: Do It Or Else Scheduled backups: A Good Idea ™ Plex Media Server occasionally stops for obscure reasons such as version updates. Scheduling server maintenance may save you hours of setup time if your Plex database becomes irremediably corrupted, which can happen in case of abrupt shutdowns, so it’s highly recommended to put your server behind a UPS.

Restoring from a backup is very easy.

9.3. Programmatic Access for Large Scale Management There are several ways to automate functions of the Plex server:

• Plex-Meta-Manager is “a Python 3 script that can be continuously run using YAML configuration files to update on a schedule the metadata of the movies, shows, and collections in your libraries as well as automatically build collections”. I’ve used it to build a variety of collections such as Movies with commentary, Oscar winners, or Imdb 250. This is a game changer if you have more than 2,000 movies like I do.

Note that collections kind of span across libraries, even if they have different types, so you can for instance have a Star Trek collection that includes movies and TV shows.

If you use the same name for collections in different libraries, Plex will cross-reference them across libraries. Pictured here, Top Rated across the main movie library and the family movie library. You could use this to tie soundtracks to movies, or file TV series and movies in the same universe. * Plex-auto-genres: in the same spirit that the previous script, less involved but less powerful. * Gaps: “Find the missing movies in your Plex Server.” * PlexAPI, a set of unofficial Python bindings whose goal is to “match all capabilities of the official Plex Web Client”, including navigating libraries, performing library actions such as scans, remote control, and listening to notifications. A collection of scripts that use this library can be found in this Github repository. * Python-PlexLibrary, a “command line utility for creating and maintaining dynamic Plex libraries and playlists based on ‘recipes’.” * An API that I haven’t tested yet. * Webhooks that require Plex Pass. * RPA software that captures how you interact with a website or desktop app, such as Power Automate. I’d pursue this only if all else fails, but it’s an option.

Collection automatically generated with Plex Meta Manager 9.4. Querying the Plex Database for Advanced Analytics Plex stores its data in an SQLite file, which has this schema that you can query like so. I’ve used this to visualize my library in Microsoft Power BI as per the screenshot below. If you’re going to do this, work off a copy of the database just to be safe.

Plex data in Power BI – two of my favorite things together! A low-tech alternative is to use ExportTools to generate a CSV.

Finally, many people use Tautulli to monitor their Plex server (I do so via a Docker container on my Synology NAS), though it’s focused more on analyzing viewership than library content.

1. Related Entries
2. Getting the Most Out of Your Synology Networked Attached Storage: Did You Know It Can Do That? You’ll learn how to run Plex and its friends (Sonarr, Radarr, Overseerr, Tautulli, etc.) in Docker and much more.
3. Things I Found Out the Hard Way to Get the Most of the Nvidia Shield. The Shield is an excellent Plex player and a decent entry-level Plex server.

I started a thread on Reddit mentioning this post, lots of good feedback in there that I reflected above through many iterative edits.

View Details

When Microsoft introduced Power BI dataflows at the end of 2018 to separate no/low code cloud ETL from BI modeling and visualization, many people were initially confused about how refreshes would work. Contrarily to what might seem like the intuitive behavior, dataflow and dataset refreshes are separate and unconnected. However, it does make sense to tie them somehow, as you’ll most likely want to refresh datasets after their source dataflows were themselves just updated.

I wrote the high-level outline for how to do so with the Power BI APIs and Power Automate (then Microsoft Flow) in the Power BI forums a while ago. A few months later Microsoft added a Power Automate action to execute a dataset refresh, but the dataflow equivalent remains to be seen. You thus still need to register a Power BI app and create a Power Automate connector for dataflows, following the procedures explained in these entries:

• Chris Webb: Calling The Power BI Export API From Power Automate, Part 1: Creating A Custom Connector
• Konstantinos Ioannou: Refresh PowerBI dataset with Microsoft Flow

The main benefit of using a custom connector is to ease authentication. If we were using an anonymous API we could just use the HTTP request Power Automate action. You can follow the steps above almost verbatim for dataflows, whose refresh POST call is documented here. But bear in mind these two specific points to be added to your custom connector:

• You need to add RefreshRequest: “y” in the body of your request definition. Otherwise, the test will work in the custom connector settings, but the actual calls from a flow will fail.
• Optionally you can use NotifyOption: “MailOnCompletion” as well to get a notification upon refresh failure or success.

refreshRequest is compulsory Note that Custom connectors are now found under Data in the Power Automate sidebar, not under Connectors (that would make too much sense) and no longer from the Settings wheel as you’ll see in posts from 2018/2019.

Some goodies you can add to your flow to better integrate refreshes in your users’ workflow:

• Let your users trigger the whole refresh process from a Power Automate button / link, put a refresh button right in your Power BI reports, or set up a schedule.
• If your dataflows or datasets need to be routed through the data gateway, assuming said gateway is in an Azure VM, you can start/stop them via Power Automate actions. There’s no native Power Automate action to check the existing status of a VM though.
• Wrap things up with notifications by email, to Teams, or otherwise, there’s a slew of competing Power Automate actions for that. If you want to be thorough and accurate, you may put these notifications in a separate flow that’s triggered by the parsing of refresh notifications as set up in the custom connector, do error handling etc.

In the screenshot opening this entry you may guess that I hardcoded the group and dataflow IDs in my custom connector calls, which I had looked up manually from the dataflows’ URLs. That’s not really a scalable or maintainable process. I assume one could use the Get Dataflows call, dump the results in a spreadsheet, and iterate over the group/dataflow value pairs in the flow, but I didn’t get around to doing so yet.

Finally, if you feel adventurous you can hack your way into retrieving a dataflow refresh history.