All Forums Data Modeling
egetts 1 post Joined 10/11
02 Dec 2011
What is a building block approach to building semantic data models?

The idea is that modeling constructs could be pre-defined and developed to promote rapid time to value, design flexibility, data consistency, and user-friendliness. These building blocks could be collected into libraries, customized, and reused. The building blocks could consist of star schema, analytical data set, data lab, and other types of building blocks along with guidelines for use and mappings to pre-defined data models, such as the Teradata industry logical data models. Different variations and recommended BI tools could also be defined for various building blocks.
What are your thoughts on using a building block approach to build semantic data models?

BarryW 2 posts Joined 07/11
05 Feb 2012

Hi Elisia

Thanks a lot for your very interesting question.

To start the discussion, I have added a page on my Database Answers Web Site that summarises my current thinking :-




Barry Williams



mjasrotia 66 posts Joined 08/11
14 Feb 2012

Hi Barry,


Thanks for the insight. Even I am very keen to understand the need and use of Semantic layer modellling in Teradata world and what I understand about that is Teradata generally prefers to model the base layers for Data Warehouses using a 3NF model thereby isolating the reporting needs from the DW loading needs which is a reasonable approach. Once the 3NF model is in place then we can start thinking about more user centric layers on top of the 3NF base layer.

These layers are either built as Materialized views (using JI) or physically denormalized layers which are given to the users for their reporting and these are the so called Sematic layers which are named as close to the business names which are easily understood to the users. Bottom line is that the Semantic Layers are more de-normalized in nature.


Please correct me if I am wrong.




mjasrotia 66 posts Joined 08/11
14 Feb 2012

And just forgot to mention that I am one of the followers and learners of the wealth of Data models you have posted on your website :)

Tonner 1 post Joined 10/11
02 Mar 2012


Your thoughts on building the semantic layer in a Teradata enviornment are very similar to mine. 

Given that a warehouse has been implemented in a normalized fashion, how might one create a dimensional model or analytical model or virtually any other kind of model to presents the data in an alternate fashion which might be optimized for data mining or other kinds of analysis?

One way would be to create alternate views of the data.  For example, several normalized tables may be joined in a "dimension" view that the client can then access. If the view needs to perform better, Join Indexes on the base tables may be all that is required.  

16 Sep 2012

I was once was involved, with a previous employer, on developing pre-defined semantic models optionally provided upon purchase of our product.  These models were composed of components similar to such building blocks intended to be mainly re-usable, though at that time they weren’t thought to be easily customizable given that they were implemented as physical tables.   The main value was notably for faster time to value.
I am just wondering given that users are now provided with flexibility on design and configuration, then is this approach getting into the area of Agile BI, if not Agile Datawarehousing?

sbarreda 3 posts Joined 11/09
17 Sep 2012

I think that building blocks are really a good idea for Semantic Modelling. In my opinion, it is posible to have a good 3NF as Core and have one or more Semantic Models laying on top of it, because there could be many different ways of seeing the same information. And Semantic Models har more to do with "navigating" the information.
I also think that most business see the same kind of basic information, for example what I mean is that most of retailers want to see "daily sales", "monthly revenue", "stocks turns", etc. And using Teradata's iLDM as the 3NF Core Model could improve the implementation of these Metrics with the utilization of Building Blocks. Maybe a little of customization would be needed but the basics can be delivered in a more standarized and effective way.


mjasrotia 66 posts Joined 08/11
08 Nov 2012

I absolutely agree with sbarreda. Semantic layers are a great way of isolating the load process than the usage of the data which is generally designed together :( A 3NF data model gives better performnce while loading the data using Teradata load utilities and the Semantic layers gives better performance in data usage using Teradata best practices like Soft RIs, and other techniques.
People generally counter argue that when we have join indexes then why to use Physical tables in the semantic tables. I still prefer the physical ones given the reasons below.
1. JI take space and so do the physical tables.
2. Physical tables gives me more control on where to use them rather than relying upon the stats everytime to chose JIs.
3.  Unlike the Join Indexes I can always query a physical table to get the data if required.

gh3350 4 posts Joined 11/12
28 Nov 2012

Working with consultants in developing the core DW model and haven't started the semantic layer yet, however, they indicated that they are more familiar (skilled) in creating physical tables from dimensional modeling (star schemas, extended star schemas, snowflakes, etc).  Are there any good examples, courses, reading material, etc. where this practice can be transitioned to start creating views in lieu of physical tables in the data access layer of the warehouse?  I understand in a few cases you may want to create aggregrate tables to satisfy requirements, but for the most part, this is a performance consideration.  Any guidelines on taking this route as well? :-)        

Raja_KT 1246 posts Joined 07/09
27 Sep 2013

I always feel that creating physical tables will be creating duplicate data again. Using views makes more sense I feel. Say for example in a big organization where there are different departments.
Sales department, accounting department, HR department etc  may want to see the same data in a different way. A closed business for sales maybe a booked business for accounting. Some departments want data at a bi-weekly  ect and others at half-yearly or yearly . Similarly, Things deparments look at products, geography, time etc differ. To answer unanswered questions, it is easy to ahve views.
I advocate using ROLAP rather than MOLAP. Also, existing MOLAP be replaced by ROLAP. Teradata is strong enough to handle it. Of course, it depends how well-versed and business-case fitting a designer/ developer work it out.
Please let me know if you agree with me or not.
Thanks and regards,

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

guptarenu2707 1 post Joined 08/13
03 Jan 2014

I am working on migration to Teradata and in a data modelling phase. I am interested to undrestand why it is recommended to have access the DW through views than directly accessing the base tables. If this is true, I need to have 1-1 view creation for each base tables. What are the key benefits of doing this.
Please elaborate.

Raja_KT 1246 posts Joined 07/09
04 Jan 2014

Views are nothing but can only be subset of what you want. The ultimate game is only what you want and security. However, there are times when we can't get what we want with views alone. There may be high chances that we may resort to using physical tables , example JIs- substituting MVs , may be a performance issue like collecting stats many a time.Physical tables can be played and moulded in anyway we want , unlike JIs.


Just to share my views:

We can realize cost-cutting when we have flexible ready-made models, that can be tailored in any fashion we want. Don't get scared. Start building 3NF coz even unanswered questions can be obtained later that you don't even realize(ROI,TCO etc) and branch out from there, maybe using Elastic Data Mart  analytic etc. Many organizations don't have a good technology like TD AND balanced  techno-functional or techno-business guy. Either a guy is lopsided to technical or leaned towards business and the worst thing is being adamant to his/her own view- no see-saw :). It is difficult to get  seasoned Pizzaioli who knows all flavours of Pizza. It is one of the reasons , why people start going to Dominos,Pizza Hut, Pizzeria etc  :)

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

Adeel Chaudhry 773 posts Joined 04/08
05 Feb 2014

It is recommended because you have better security, and can expose only what user wants .... to a multiple set of users .... like its a possibility that a user A might require 3 columns for table-A and another high profile user B might require those 3 plus 2 columns containing sensitive information such a phone-numbers, social security numbers, etc.
With views approach, you can simply create 2 views and give access to both seperately. Its about ease of use.

-- If you are stuck at something .... consider it an opportunity to think anew.

sasanka_g 2 posts Joined 06/14
28 Jun 2014

Analytics is write once read multiple times. Creating semantic layer with View the problem is though logically it may give dimensional flavour but ultimately when the query will run it will use the underlying base tables which are normalized , foundation area or core is in 3nf or it is actually entity relationship model. it's purpose is different. denormalized physical semantic layer is much better and should be followed. i believe.

Raja_KT 1246 posts Joined 07/09
28 Jun 2014

I feel that denormalized physical semantic layer can be much better when we form it as core or base semantic layer, serving maybe other topup semantic layers, where organizations, see that different business units are using common entities  serving their KPIs. I feel that understanding the Enterprise wide (business) requirements and streaming the data in the best optimized way, using Technology is the main end to serve KPIs, be it volatile.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

sasanka_g 2 posts Joined 06/14
29 Jun 2014

precisely the approach i was trying to say.  need at least one denormalized physical layer and one physical notmalized  layer.  then  using  both of those physical layers any semantic layer can be built using views etc. depending upon requirements.

You must sign in to leave a comment.