sql server - Dashboards with multiple warehouses

Question

I'm looking for feedback on possible approaches. I'm trying to put together a least a dozen different dashboards that look at a wide variety of topics with overlapping dimensions. The issue I'm having is the data that is requested is stored across at least 4 very large data warehouse all with different levels of normalization and data standardization. Almost all the data in each of these warehouses pretty much use the same data from the same operational data store and are just stored, standardized or aggregated in different ways.

The question I have is would it make sense to stand up a new data warehouse that combines the necessary data from these warehouses and stores it specifically for use in Dozen dashboards I need to create? Or would a better approach be to rely on the dashboard applications blending tool and have each dashboard connect to each of the already established data warehouses?

Thanks for the feedback!

question from:https://stackoverflow.com/questions/65853724/dashboards-with-multiple-warehouses

Answer 1

Unless your new datawarehouse is going to add a new dimension over the existing data that the other stores are already aggregating, or your visualisation layer cannot easily access the other stores, then a new data warehouse is going to add another layer of management and complexity to your solution that might be simpler to achieve in your dashboard directly.

Approach this pragmatically, do what works with the least amount of effort. If the data is available and you can access multiple stores from your dashboards, then do so, that is often the power that we expect from dashboards and BI visualisation layers, we don't have to aggregate the data into a single store first.

Take PowerBI as an example or proof of the concept, the visualization layer in that product is heavily focused on creating many data connections to disparate data sources and even allows you to perform analytics from data across these connections.

If you can make it work, down the track you might be able to identify optimisations that could be realised by adding another layer in the form of a new data warehouse, but that decision would largely depend on the platform or framework that you choose to use for your visualisations and how many people need to collaborate on the design of these dashboards.