Do we need an additional layer on top of Datamart?

Hi Everyone, I need your thought on an architectural design solution.

Right now we have pretty classic DWH architecture with landing, atomic and datamart layers. The datamart layer is used across the company in different departments, and contains a lot of fact and confirmed dimensions. Also there are few BI tools that are being used at different departments, for example the department I belong to uses the Tableau. So, now I have an issue that the BI team can create the queries in the reporting tool that can exceed the database threshold limits or these query can have a low performance.

Unfortunatelly we can't always optimise the query with only SQL (that can be used by BI tool directly) and now we have few queries that should be implemented with DB's programming language. At the first time we considered to create new precalculated tables in the Data Mart layer, but finally declined it. The main reason is that as I see it, it is some kind of denormilised datasets that will contain data only for some particular report and it will not be used by any other team/report/user. So we need an other layer that will contain datasets with precalculated results. These tables will not keep history, because there no sens of that, we all have it in the datamart layer. The only we need is to create some snapshot/result set that will be used in the report.

So my question is how this kind of new layer on top of Data Mart fits to the DWH "standards". Is there some other approach that we should consider? Or mayby you can suggest, how you'd implement it.

Thank you.

Have you considered an OLAP layer to address the performance issue?

http://en.wikipedia.org/wiki/Comparison_of_OLAP_Servers

For example, if you're using SQL Server as your DBMS, adding an OLAP layer with SSAS could be a significant performance boost, and still conform to your architecture.

Ron.

Creating purpose-built aggregates as you describe is certainly one approach to addressing those issues. But I would use that as a last resort as it can get you into a situation of building a multitude of such tables every time someone isn't happy with response times.

After identifying what is causing the problem, I would consider the following in order of precedence:

1. Database Tuning: Could changes to the physical tables help? i.e. partitioning, indexes, etc…
2. Would a summarized fact table help?
3. Would adding a bridge or some other structure aid the query?

Basically, I would first aim for a more generic solution that could benefit all users rather than a one-of solution.