Analyzing SDL Tridion CMS Data

Introduction

One of the challenges we have when evaluate an existing SDL Tridion implementation is the difficulty of having a high level overview of all the items involved in the system, and identify which relations between which item types can lead to identify existing or future problems.

This Article provides some information that can help in order to evaluate/audit an existing SDL Tridion implementation based on the interpretation of data extracted from the CMS Database.
This is applicable to several versions of the product.
Personally I have used this methodology for the versions 5.3, 2009, 2011 and 2013.

Also can be applicable to reporting and keep track of the evolution of the system.  

Each Section explained here covers the interpretation of a data set that can be extracted with a SQL statement.
Depending of the implementation, design decisions, data volume, context, etc... those metrics can have different interpretation.

I won't share the SQL statements in this Blog, but I will give an overview of which tables are involved for extracting the Data.

Relation between Schemas
This area covers the relation between schemas in the system

The Tables involved in the data extraction are:

• ITEM_REFERENCE_PUBLICATIONS
• PUBLICATION_PRIORITIES
• ITEMS
• ITEM_REFERENCES
• ITEM_ASSOCIATIONS
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	SCHEMANAME	SCHEMAUSEDNAME
010 Schemas	ARTICLE	PARAGRAPH
010 Schemas	ARTICLE	IMAGE
010 Schemas	ARTICLE	DOCUMENT
010 Schemas	ARTICLE	ARTICLE BODY

Sample of data analysis:

• Schema Article is created in the publication 010 Schemas
• Schema Article is referencing the schemas PARAGRAPH, IMAGE, DOCUMENT, ARTICLE BODY

The analysis of this data will give us information about:

• General overview of how our content model is designed
• Distribution of schemas across our Blueprint Model  
• Identify an abnormal number of relations beween schemas
• Identify circular references (Schema A pointing to Schema B and Schema B pointing to Schema A)

Relation between Schemas and Categories
This area covers the relation between schemas and categories in the system

The Tables involved in the data extraction are:

• ITEM_ORG_ITEM_ASSOCIATIONS
• ITEMS
• ORGANIZATIONAL_ITEMS
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	SCHEMA	CATEGORY
010 Schemas	ARTICLE	TOPICS
010 Schemas	NEWS	COUNTRY
010 Schemas	NEWS	STATE

Sample of data analysis:

• Schema ARTICLE uses the Category TOPICS
• Schema NEWS uses the Categories COUNTRY and STATE

The analysis of this data will give us information about:

• Relation between Schemas and Categories
• Identify an abnormal number of relations Schemas and Categories

Relation between Schemas and Component Templates
This area covers the relation between schemas and component templates in the system

The Tables involved in the data extraction are:

• ITEM_REFERENCE_PUBLICATIONS     
• PUBLICATION_PRIORITIES          
• ITEMS                           
• ITEM_REFERENCES                 
• ITEM_ASSOCIATIONS                
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	SCHEMA	COMPONENT TEMPLATE	ALLOWEDONPAGE	DYNAMIC
020 Design	ARTICLE	CT ARTICLE INTRO	1	0
020 Design	ARTICLE	CT ARTICLE FULL	1	0
020 Design	BANNER	CT BANNER	1	1

Sample of data analysis:

• Schema ARTICLE has two component templates associated CT ARTICLE INTRO and CT ARTICLE FULL. Both component templates are static and can be used for assemble Articles in pages  
• Schema BANNER has one component templates associated CT BANNER. This component template is static and can be used for assemble Banners in pages 

The analysis of this data will give us information about: 

• Distribution of component templates across our Blueprint Model 
• Relation between Schemas and Component Templates   
• If the current implementation has templates that you can't use when assembling components into a page  (ALLOWEDONPAGE=0)  
• If the current implementation is using an Static (DYNAMIC=0), Dynamic (DYNAMIC=1) of Hybrid publishing Model 
• If a Schema is used in both Static and Dynamic Component Templates

Relation between Schemas and Components
This area covers the relation between schemas and components in the system

The Tables involved in the data extraction are:

• ITEMS 
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	SCHEMA	COMPONENTS
030 Content English	ARTICLE	100
030 Content English	BANNER	50
040 Content Spanish	ARTICLE	30

Sample of data analysis:

• There are 100 components based on the schema ARTICLE in the publication 030 Content English  
• There are 50 components based on the schema BANNER in the publication 030 Content English  
• There are 30 components based on the schema ARTICLE in the publication 040 Content Spanish
• Those 30 components can be localized or can be new components. This information can also be extracted if required  

The analysis of this data will give us information about: 

• Distribution of components across our Blueprint Model 
• Volume of components 
• Usage of schemas when creating content  

Relation between Pages and Page Templates
This area covers the relation between pages and page templates in the system

The Tables involved in the data extraction are:

• ITEMS
• PUBLICATIONS 

Sample of Data Extraction:

PUBLICATION	PAGE TEMPLATE	PAGES
050 Web Site English	PT Main	70
050 Web Site English	PT Article	30
050 Web Site Spanish	PT Main	15
050 Web Site Spanish	PT Article	10

Sample of data analysis:

• There are 70 pages based on the page template PT Main in the publication 050 Web Site English  
• There are 30 pages based on the page template PT Article in the publication 050 Web Site English  
• There are 15 pages based on the page template PT Main in the publication 050 Web Site Spanish  
• Those 15 pages can be localized or can be new pages. This information can also be extracted if required 
• There are 10 pages based on the page template PT Article in the publication 050 Web Site Spanish  
• Those 10 pages can be localized or can be new pages. This information can also be extracted if required  

The analysis of this data will give us information about: 

• Distribution of pages across our Blueprint Model 
• Volume of pages 
• Usage of page templates  

Relation between Pages and Components

This area covers the relation between pages and components in the system.

The Tables involved in the data extraction are:

• PAGE_CONTENT 
• ITEMS
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	PAGE	CP's > 6
050 Web Site English	Article Index	9
050 Web Site English	Home	12

Sample of data analysis:

• There are two pages with more than 6 component presentations assembled 
• Article Index uses 9
• Home uses 12   

The analysis of this data will give us information about:

• Number of Components assembled in a page  
• Identify an abnormal number of Components added to a Page
• Identify potential bottlenecks during publishing
• Example: A page contains 30 Component Presentations using Static Component Templates 

Relation between Components and Pages

This area covers the relation between pages and components in the system

The Tables involved in the data extraction are:

• PAGE_CONTENT 
• ITEMS
• PUBLICATIONS

Sample of Data Extraction:

PUBLICATION	COMPONENT	Pages > 4
050 Web Site English	BANNER MORTGAGE	12
050 Web Site English	QUICK LINKS	8

Sample of data analysis:

• There are two components used in more than with more than 4 pages in the system 
• BANNER MORTGAGE used in 12 pages
• QUICK LINKS used in 8 pages

The analysis of this data will give us information about: 

• Usage of components across pages  
• Identify the heavily utilized components
• Identify potential bottlenecks during publishing
• Example: A component is used in 15 pages assembled with a static component template 

Some General Tips when Analyzing the Data

Evaluate the information from a global point of view

Is important when we evaluate an implementation we consider all the different data sets extracted. 

Although each one can be interpreted separately, understanding the relation between those data sets provide a lot of useful information related to the system.

Be careful when you interpret the Data

Don't make quick assumptions related to an implementation. There are guidelines/best practices that any implementation should follow but sometimes the reality is different than the theory.

Ensure you collect and interpret the Data properly before making wrong assumptions. Also communicate with the people that has been working with the system for a while, they will give you very valuable information that can help in the interpretation.

Examples:

Example 1:

If we observe a bottleneck in the publisher when publishing a banner named "BANNER HOLIDAYS", based on the schema "BANNER", based on those metrics we could see that that component is used in 20 pages and the Component Template associated to the schema "BANNER" schema is static, so the republishing of that banner will trigger the republish of 20 pages.

Example 2:

If we observe that there is a reasonable number of Page Templates and  Component Templates localized in the system across several publications, that means that any changes in Page Templates and Component Templates will take longer than if those were not Localized.