There are couple of reports which the client analyzes only at month ends, so those dashboards should be refreshed only then. However, the dashboard which they require for their top management should get refreshed on a daily basis.

One report is needed by top management to analyze a few sales KPIs and they want to see fresh data every two hours.

With all these inputs, we can start on designing the data architecture.

If we analyze the previously listed inputs properly, we can easily make out that there are three basic points which we should look at:

• There is a need for a detailed transactional dashboard, which will be used by middle management. So for this requirement, we need to fetch the raw transactional data from the source system. 
• For top management dashboards, the data needs to be aggregated. Thus, the aggregation layer is also needed, which should be created using the raw transactional data.
• A couple of tables which give data for sales KPI (key performance indicators) need to be refreshed at intervals of two hours.

So as per the analysis, the data architecture should be a three-layer QVD architecture.

The  following is the data architecture for the requirement of AB LLC:

As you can see in the preceding diagram, there are three main applications: one for top management, which will take data from aggregated QVDs; second for middle management, which will take data from transformed raw QVDs; and third for top management, which will get data from the raw QVDs.

One thing to note here is that for real-time application, we have taken the data from raw QVD because an additional layer of aggregation QVD may take time, which may not fulfill the requirement of getting data every 2 hours. Thus we have taken the data from raw QVDs and we will aggregate them as per the dashboard requirements.

Note: The execution of data fetching is serial in nature, that is, at first layer 1 will generate QVDs, then layer 2, then layer 3, and so on.