Power BI data modeling/data visualization tip: When you need a ‘chimera’, create it!

➡ Do not hesitate to append (do not merge) data with different granularity/different facts into a single table. You’ll need a column that identifies what kind of data is stored in a row (e.g. Row Type = “Fact 1” or “Fact 2”), you can use some of the columns for different purposes (assuming the data type is the same).

ℹ Such a table doesn’t make much sense outside of a visual or inside of a Power BI core visuals, but really useful inside of some advanced custom visuals (Icon Map, Deneb). In some cases, good data visualization is just not possible without these kinds of ‘chimera’ tables. From a pure data modeling point of view it’s an ugly ‘chimera’, but it’s an important data visualization workaround.

👉 Keep the original tables for other purposes and create a new combined table (an additional star in the star schema) for the specific visualization that requires this kind of data. This is important, do not compromise the entire data model because of what is required in a single (even though an insightful one) visual. If you need deviations from a proper data model add them, but do not convert the core of the model into a monster.

⚠ A simple attempt to merge data with different granularity/different facts (in the data model or inside the visual by adding columns from multiple tables) won’t work. In the best case, it will increase cardinality to the level of performance failure. Appending does not increase cardinality significantly; you will just get a lot of nulls in some columns, a small price to pay for the insightful visualization that allows you to see correlations between different facts.

🎯 I use ‘chimera’ tables sometimes with the Icon Map custom visual (to show multiple layers of different objects on a map (see https://icon-map.com/blogs/blogs.html#multiobjects) and with the Deneb custom visual to create complex, but more insightful, visualizations.

💡 Vega (outside of Deneb) supports multiple data sources, and probably future versions of Deneb will support them. However, for now in Deneb, it’s just a single “dataset” (https://deneb-viz.github.io/dataset). This data modeling trick solves the problem. Get a single table into the visual, then use Vega transformations to split the “dataset” table into multiple tables (using the Row Type identifier I mentioned earlier).