Data Warehouse Modernisation: Czech Television has streamlined its reporting

The public service evaluation is carried out using three methodological approaches. These differ according to the type of indicator determining how the public service has been fulfilled, which are based on:

1. Measurable public behavior and attitudes
2. Expert assessment of compliance with the principles defined by the ČT Code
3. So-called “hard data”, for example, from the database of broadcasts

The original system that furnished the third indicator, based on “hard data”, had to be upgraded due to higher demands on reporting. Realizing that storing data about broadcasts from all the channels, consolidating them, and preparing for regular reporting was a challenging undertaking, ČT required a replacement for the original system that would be open, dynamic, and modern.

New data warehouse from Adastra: Automated updates and 20 regular reports per month

As well as the original data, the data warehouse will also store additional data needed for other ČT departments, which use them for various agendas.

The data warehouse is updated automatically and allows the required reports to be generated easily. There are 20 regular reports every month. Further reports, based on historical data about broadcasts, are created according to incoming requests. The time necessary for preparing the data and for the reporting itself has been significantly reduced.

In cooperation with Adastra, Czech Television has constructed a new solution for data warehousing and reporting. It is built on Microsoft technology and contains approximately 35GB of data. Every month, the solution provides a set of predefined reports, while also allowing users to create their own reports ad-hoc. The reporting processes “live” data – if the input data are changed, the system automatically propagates the update to the new data warehouse and to all dependent reports. This significantly reduces the time spent on preparing reports.

In addition, the data warehouse automatically alerts users to errors in the data, according to predefined checks, on a daily basis. If an error does occur, a notification is sent to the analysts. CT thus improves the data quality right in the primary systems and, consequently, in all the reports and analyses that depend on it.

During construction of the new DWH and the reporting based on it, it was quite difficult to prepare a set of keywords according to which media literacy across all the channels would also be evaluated.

In addition to current data, the data warehouse also stores historical data, going back to the beginning of CT’s regular broadcasting in 1954. Analysts work with these data on special occasions such as anniversaries, public holidays, or when they need to answer statistical-analytical questions from other media – for example, how many times they have broadcast a particular program at Christmas or how often it has been repeated in a specific time period.

The key reports generated automatically by CT’s new data warehouse include, among others:

• Aiming the programs at various target groups (seniors, disabled people, religious groups, etc.),
• A list of CT projects and programs concerning the main national minorities living in the Czech Republic,
• The structure of genres of broadcast (the share of educational, documentary, religious programs, and so on),
• The proportion of domestic, European and nonEuropean programs among all the broadcasts organized by individual genre
• The country of origin of the documentaries
• The proportion of programs with subtitles for the deaf or hardof-hearing
• The percentage of CT’s own creations, Czech and foreign productions (in detail, for example, up to the percentage of current European productions)
• The proportion of each individual artistic movement across all art programs
• The proportion of each individual sport in CT’s entire sports broadcasting
• The percentage of representation from each CT studio (Prague, Brno, Ostrava) broadcast on CT as a whole, on CT1 and CT2

The input data are checked according to a set of predefined rules, and the user is automatically notified if the system detects an error. As a result, CT now effectively improves data quality right in the primary system.

Another important benefit of the new solution is that it can be used independently of the IT department and of Adastra as the supplier. CT analysts can prepare, update, and edit the relevant “cube” or report by themselves whenever a change is required in terms of data reporting. They can easily define a different data structure and, thanks to the solution’s high level of flexibility, get the requested results.