Currently we are in development phase of a new data warehouse for a telecom giant according to cLDM 12 data model.
Our next plan is to design & build a scemantic layer on top of this new data warehouse.
So basically I want to know, what is going to be done in Design & Development phase of this scemantic layer, as I dont have much idea on this.
And what is the importance & necessity of this scemantic layer?
I worked few projects where we built semantic layer on top of central or EDW or 3NFs. Semantic layer is more of aggregation.
The design of this layer is when we understand more of what the reporting layer is required. Sometimes, when multiple departments require different reports based out of huge EDW, then there maybe a need to build a common base semantic layer which is shared, rather than duplicating the same entities. This is my thought.
Pulling data for reporting from 3 NF if data volume is high is killing. So we build a semantic layer on top of 3 NF.
The idea of DWH, usually is to contain as detailed data as possible, so you can answer any future reports using the same detailed data. Hence we say DWH models actual business and it is as per the business runs.
SL on the other hand, is the layer which helps the answering of reports. There can be reporting requirements based on weekly aggregation, or monthly aggregation, etc. But as you have DWH at detailed level so you need to have a place to aggregate the information as per reporting needs .... that is where SL helps.
As per the system details and table-sizes and other considerations .... you can either keep SL materialized or non-materialized. Non-materialized is nothing but a bunch of views which fetch data on the fly as they are fetched. Materialized Sl is the actual tables that retain a copy of data/aggregated data.
The down-side of materialized SL is the loading-times of those tables, though they can give a better performance in some cases.